Spreadsheets That Are Very Stable?

Questions on how we spend our money and our time - consumer goods and services, home and vehicle, leisure and recreational activities
Post Reply
Topic Author
leo383
Posts: 569
Joined: Thu Nov 29, 2007 8:36 pm
Location: Durham, NC

Spreadsheets That Are Very Stable?

Post by leo383 »

My wife's small consulting company uses Excel for much of their research work, and the spreadsheets get very big. One she is currently working on has 250,000 lines of data.

She is worried about the stability of these sheets; they hang up, take a long time to load and compute anything, and sometimes crash.

Are there more stable options out there? I know Unix based programs have a rep for being more stable than their Microsoft counterparts. Is it warranted?

Any suggestions are welcome.
gkaplan
Posts: 7034
Joined: Sat Mar 03, 2007 7:34 pm
Location: Portland, Oregon

Re: Spreadsheets That Are Very Stable?

Post by gkaplan »

Why not break up the spreadsheets?
Gordon
pshonore
Posts: 8212
Joined: Sun Jun 28, 2009 2:21 pm

Re: Spreadsheets That Are Very Stable?

Post by pshonore »

Is that a s/s or should it be a database app? Too many people try to use spread sheets when they should be using relational databases instead.
bourg
Posts: 63
Joined: Fri Mar 16, 2012 9:26 am
Location: Indianapolis, IN

Re: Spreadsheets That Are Very Stable?

Post by bourg »

Just don't go from Excel -> Access. Yuck - Access is so horrible.

If the company can afford it hire a Software Consulting firm to create a web application using a relational database to perform the functions. It will pay dividends in the long run and if the firm is any good can automate many of the processes.
sscritic
Posts: 21853
Joined: Thu Sep 06, 2007 8:36 am

Re: Spreadsheets That Are Very Stable?

Post by sscritic »

leo383 wrote:My wife's small consulting company uses Excel for much of their research work, and the spreadsheets get very big.
Others have suggested a database. Before going that route, perhaps you could explain the meaning of "research work." Does this mean doing extensive "what if"s on a relatively small set of data? If so, I don't think a database will help since it is not the data that is extensive, but the manipulation.
pshonore
Posts: 8212
Joined: Sun Jun 28, 2009 2:21 pm

Re: Spreadsheets That Are Very Stable?

Post by pshonore »

bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.

If the company can afford it hire a Software Consulting firm to create a web application using a relational database to perform the functions. It will pay dividends in the long run and if the firm is any good can automate many of the processes.
Agreed - although Access can be a good user friendly front-end to a SQL D/B. 250K records is way too big for Access
Topic Author
leo383
Posts: 569
Joined: Thu Nov 29, 2007 8:36 pm
Location: Durham, NC

Re: Spreadsheets That Are Very Stable?

Post by leo383 »

The main formulas she uses are VLookup, Countif, LEN, Concatenate, and some IF statements.

Does this help the discussion?
User avatar
nisiprius
Advisory Board
Posts: 52211
Joined: Thu Jul 26, 2007 9:33 am
Location: The terrestrial, globular, planetary hunk of matter, flattened at the poles, is my abode.--O. Henry

Re: Spreadsheets That Are Very Stable?

Post by nisiprius »

This may seem dumb, but if it is not on the latest and greatest hardware, a good temporizing move would be simply to move to a new computer with more RAM, preferably a lot more... like quadruple the present amount. I would say that well-written software should always be stable and never crash, but lots of RAM can definitely improve the stability of bad software.

I would not go the UNIX route--that is to say, I would not try to migrate from Excel to (say) LibreOffice, for several reasons. I have nothing against open source software, but I am not willing to overlook what I perceive to be its defects. First, there are always compatibility issues, always always always, and you can't afford to have them. In a huge spreadsheet you'll never figure out where the subtle difference is. I'm imagining you're talking about a spreadsheet that incorporates VBA programming, and I would be very leery about the possibility of subtle incompatibilities there.

I personally have experienced serious issues with formatting when doing something as trivial as importing a Word document into LibreOffice Writer--lines and pages don't break at the same place, etc.

It is not exactly the fault of open source, but it is within Microsoft's power to make things difficult for would-be cloners, and whether by intention or not, they do. It's not as if Microsoft published a full formal specification for Excel and provided a validation suite for open source clones to test against.

Also, of course, if you try LibreOffice and find problems with it all the OpenOffice people will say you should have used OpenOffice, and if you find them with OpenOffice all the LibreOffice people will say you should have been using LibreOffice.

I think the most promising routes are to try to stabilize your present spreadsheets by throwing more RAM at them, or to bite the bullet, say you've outgrown the spreadsheet, and start de novo with some other kind of environment altogether. I don't think you are going to find a spreadsheet that's "just like Excel only stable."
Last edited by nisiprius on Tue Jul 10, 2012 12:19 pm, edited 1 time in total.
Annual income twenty pounds, annual expenditure nineteen nineteen and six, result happiness; Annual income twenty pounds, annual expenditure twenty pounds ought and six, result misery.
User avatar
prudent
Moderator
Posts: 9085
Joined: Fri May 20, 2011 2:50 pm

Re: Spreadsheets That Are Very Stable?

Post by prudent »

Agreeing that it sounds like a database is the better choice. I am not aware of any spreadsheet software that is viewed as more reliable than Excel for giant spreadsheets.

If that's the typical operations they are doing in Excel, a relational database will blow them away at how fast it is (given well-planned schema, indexing and queries).
jebmke
Posts: 25474
Joined: Thu Apr 05, 2007 2:44 pm
Location: Delmarva Peninsula

Re: Spreadsheets That Are Very Stable?

Post by jebmke »

Sounds to me like it is a db application rather than spreadsheet.
Don't trust me, look it up. https://www.irs.gov/forms-instructions-and-publications
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

Re: Spreadsheets That Are Very Stable?

Post by serbeer »

Agree about eventual need to switch to the database.

However, as short/medium term solution, make sure you are running 64-bit version of MS Office (which, in turn, requires 64 bit version of Windows7 OS installed) on high end PC (Intel i7 processor, high-perfomance HD) with as much RAM as they can install (bare minimum of 8Gb).

Upgrade to high end PC is likely to be much less expensive than efforts and cost of porting existing Excel-based system to database. 64bit version of the OS/Office on PC with much RAM will increase stability of Excel as much as possible and high-end PC hardware with quad-core i7 processor will increase performance.
User avatar
daytona084
Posts: 909
Joined: Mon Feb 01, 2010 9:47 pm

Re: Spreadsheets That Are Very Stable?

Post by daytona084 »

bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.
Not sure what you mean by this. Access is a good, powerful relational database product. But it must be learned. The only people I know who echo the "Access is horrible" sentiment are people who sat down in front of it and expected to know what to do with it without any training.

I agree with others who wrote that it sounds like this application requires relational database software, not a spreadsheet. Excel has some database functionality but it only goes so far.
User avatar
TomatoTomahto
Posts: 17158
Joined: Mon Apr 11, 2011 1:48 pm

Re: Spreadsheets That Are Very Stable?

Post by TomatoTomahto »

leo383 wrote:The main formulas she uses are VLookup, Countif, LEN, Concatenate, and some IF statements.

Does this help the discussion?
Of these, VLookup and Countif are the two whose runtime depends most directly on the number of rows being processed.

As a middle ground, rather than porting to a DB, judicious use of VBA (Excel's macro language) might save some running time. As I know nothing about the application, it's just a guess. Having said that, those functions are the bread and butter of SQL.
I get the FI part but not the RE part of FIRE.
pshonore
Posts: 8212
Joined: Sun Jun 28, 2009 2:21 pm

Re: Spreadsheets That Are Very Stable?

Post by pshonore »

wjwhitney wrote:
bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.
Not sure what you mean by this. Access is a good, powerful relational database product. But it must be learned. The only people I know who echo the "Access is horrible" sentiment are people who sat down in front of it and expected to know what to do with it without any training.

I agree with others who wrote that it sounds like this application requires relational database software, not a spreadsheet. Excel has some database functionality but it only goes so far.
Access is fine for low volume work with small databases but it is not an industrial strength app. I once wrote an app that was used over a network by 100+ people and a relatively small Access DB with 100000 or so records. It would just randomly lock up and go into "clock mode" for no apparent reason. Maybe once a week, etc. Extensive detective work could find nothing (no network bottlenecks, including my code which was not complex). We finally re-architected the whole app but my guess was converting the DB to SQL and leaving the Access front end would have worked as well.
bourg
Posts: 63
Joined: Fri Mar 16, 2012 9:26 am
Location: Indianapolis, IN

Re: Spreadsheets That Are Very Stable?

Post by bourg »

wjwhitney wrote:
bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.
Not sure what you mean by this. Access is a good, powerful relational database product. But it must be learned. The only people I know who echo the "Access is horrible" sentiment are people who sat down in front of it and expected to know what to do with it without any training.

I agree with others who wrote that it sounds like this application requires relational database software, not a spreadsheet. Excel has some database functionality but it only goes so far.
This is from a software engineer/architect perspective. We have to maintain full Access applications. We perform migrations of the data from Access to SQL Server once Access limitations are met and compact and repairs are having to be performed daily to keep it stable. Access is a developers nightmare compared to a robust C#/ASP.NET/MVC/SQL solution. Too many companies (especially the smaller ones) get suckered into thinking Access can solve their problems cheaper then a custom application and by the time they realize it can't scale and be maintained like they hoped as they grow they are looking at very expensive migrations to keep their data intact yet still move to a stable platform.

Granted, some of the problem isn't in Access itself but in the Access developers. The skill/experience required to be an Access developer is less then a C# application developer - many are self taught. So best practices are not followed and shortcuts are taken that compound with Access's innate limitations to create a mess of an software solution. In my 10+ years of software architecting, I have yet to see an Access application that has been well built and scaleable. Going from Excel->Access is a short sighted approach in my opinion. A steady stream of income for my company is being asked by our IT partners to "save" their client from an Access nightmare.

To be done correctly, the OP should look at their entire business processes and see where this piece of software fits into it. Does it pull data from other sources? Is there a lot of manual entry? What does the company do with the results - type it in other pieces of software? Likely the company could benefit from integrations linking other pieces of software used by the company to this database automating manual processes. Access is quickly going to struggle to keep up as the needs of the company expand.
User avatar
CaliJim
Posts: 3050
Joined: Sun Feb 28, 2010 7:47 pm
Location: California, near the beach

Re: Spreadsheets That Are Very Stable?

Post by CaliJim »

Have you looked into ms PowerPivot?
-calijim- | | For more info, click this Wiki
User avatar
magellan
Posts: 3489
Joined: Fri Mar 09, 2007 3:12 pm

Re: Spreadsheets That Are Very Stable?

Post by magellan »

In addition to replacing computationally intensive excel functions with VB, you might also consider disabling automatic calculation and instead using a 'calculate' or 'update' button that's tied to a simple VB script.

In general, Excel gives you lots of knobs and dials for tuning performance and it might be worth digging into that aspect of excel programming. OTOH, with 250k rows in some spreadsheets, you may truly be at the outer edge of excel's capability.

Jim
KyleAAA
Posts: 9498
Joined: Wed Jul 01, 2009 5:35 pm
Contact:

Re: Spreadsheets That Are Very Stable?

Post by KyleAAA »

It should definitely be a database application. I wouldn't use Access, though. There are plenty of better free options out there for a database that size. It doesn't have to be particularly complicated so I don't think it would cost much to develop.
User avatar
JupiterJones
Posts: 3623
Joined: Tue Aug 24, 2010 3:25 pm
Location: Nashville, TN

Re: Spreadsheets That Are Very Stable?

Post by JupiterJones »

I agree that something other than Excel would probably be a better choice, but it really depends on what's being done with the data.

If you're doing a lot of statistical analysis, maybe a stats package (such as R) would be the way to go.

If it were me, I'd probably diddle around with it in Access first, just to see if the database approach is the way to go. I mean, you probably have Access already installed on the computer--you're good to go. Will it be slow at 250,000 records? Maybe. But it should suffice for prototyping.

JJ
"Stay on target! Stay on target!"
User avatar
3CT_Paddler
Posts: 3485
Joined: Wed Feb 04, 2009 4:28 pm
Location: Marietta, GA

Re: Spreadsheets That Are Very Stable?

Post by 3CT_Paddler »

What about trying to navigate to open source database applications like MySQL or PostgreSQL?
User avatar
daytona084
Posts: 909
Joined: Mon Feb 01, 2010 9:47 pm

Re: Spreadsheets That Are Very Stable?

Post by daytona084 »

bourg wrote:
wjwhitney wrote:
bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.
Not sure what you mean by this. Access is a good, powerful relational database product. - (edited for brevity) -
This is from a software engineer/architect perspective.... - (edited for brevity) -
Thanks for the detailed explanation. Now I understand what you mean. Best wishes to the OP.
boglestan
Posts: 193
Joined: Wed Mar 30, 2011 1:52 pm

Re: Spreadsheets That Are Very Stable?

Post by boglestan »

A few hundred thousand rows might be too much for Excel or even Access (I wouldn't know), but it's really not that much data. You don't even need Postgres or MySQL -- you could most likely use SQLite. SQLite is at least vaguely approachable by non-IT people, because it's an embedded DB library and you don't need to run or administer a DB server. It has some Windows frontends, like this one: http://sqlitebrowser.sourceforge.net . Might be worth a shot.
campy2010
Posts: 1252
Joined: Sun Nov 28, 2010 4:01 pm

Re: Spreadsheets That Are Very Stable?

Post by campy2010 »

MS Excel 2007 allows for 1.4 million rows. She just needs to upgrade her Office suite.
bourg
Posts: 63
Joined: Fri Mar 16, 2012 9:26 am
Location: Indianapolis, IN

Re: Spreadsheets That Are Very Stable?

Post by bourg »

campy2010 wrote:MS Excel 2007 allows for 1.4 million rows. She just needs to upgrade her Office suite.
"allows for" and "performs well with" are two different things though. :)
User avatar
TomatoTomahto
Posts: 17158
Joined: Mon Apr 11, 2011 1:48 pm

Re: Spreadsheets That Are Very Stable?

Post by TomatoTomahto »

bourg wrote:
campy2010 wrote:MS Excel 2007 allows for 1.4 million rows. She just needs to upgrade her Office suite.
"allows for" and "performs well with" are two different things though. :)
WORD

(I could not resist the ms-office pun. I think the kids say that to mean "ain't that the truth!")
I get the FI part but not the RE part of FIRE.
rustymutt
Posts: 4001
Joined: Sat Mar 07, 2009 11:03 am

Re: Spreadsheets That Are Very Stable?

Post by rustymutt »

bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.

If the company can afford it hire a Software Consulting firm to create a web application using a relational database to perform the functions. It will pay dividends in the long run and if the firm is any good can automate many of the processes.
I second Access as horrible. I used Sybase in college and it was ok. OpenOffice Base has some advantages.
Even educators need education. And some can be hard headed to the point of needing time out.
tibidabo
Posts: 9
Joined: Mon Jul 18, 2011 12:09 pm

Re: Spreadsheets That Are Very Stable?

Post by tibidabo »

As a quick fix, she could try saving her files as .xlsb. It can make a huge difference for large files.

http://pcsupport.about.com/od/fileexten ... sbfile.htm
campy2010
Posts: 1252
Joined: Sun Nov 28, 2010 4:01 pm

Re: Spreadsheets That Are Very Stable?

Post by campy2010 »

bourg wrote:
campy2010 wrote:MS Excel 2007 allows for 1.4 million rows. She just needs to upgrade her Office suite.
"allows for" and "performs well with" are two different things though. :)
A spreadsheet in Excel 2007 with 250k rows is much, much better than Excel 2003 with 250k rows. Does it take a bit of time to do a vlookup on 250k rows? Yes, but it isn't unstable in Excel 2007. That said, I use SAS for most of my large computations.
User avatar
serbeer
Posts: 1304
Joined: Fri Dec 28, 2007 1:09 pm

Re: Spreadsheets That Are Very Stable?

Post by serbeer »

Another thought: if the spreadsheets used for modeling/simulations/solving, then performance can be improved dramatically (hundreds or thousands of times!) using Microsoft Excel-compatible Polymorphic Spreadsheet Interpreter. Frontline Systems developed this technology to create a spreadsheet recalculation engine.

This technology is used to power Extreme Speed simulations and optimizations in Oracle Crystal Ball simulation software (that I used to use).

It is transparent to the end user: spreadsheet is read in by the tool, and re-calculations can be run extremely fast on certain set change to spreadsheet's variables.

http://www.solver.com/products-overview.htm
Oddlot
Posts: 62
Joined: Sat Apr 28, 2012 9:01 pm

Re: Spreadsheets That Are Very Stable?

Post by Oddlot »

Since it sounds like these are "ad hoc" files (different for each project), a database may be overkill. I routinely work with an Excel 2010 file that has just under 200K rows; advanced filters and the types of functions you mention run almost instantaneously. I don't know what its practical limits are, but Excel 2010 is designed for just under 1.05 million rows. As noted by others, current software and hardware are needed.

I also work a lot with Access, and it's fine except that it has limited scalablility and some quirks in how it handles SQL, so the enterprise guys don't like it. It works great as a "personal" database until you need to run queries from tables upwards of several million rows. Then it tends to choke.
User avatar
Random Musings
Posts: 6770
Joined: Thu Feb 22, 2007 3:24 pm
Location: Pennsylvania

Re: Spreadsheets That Are Very Stable?

Post by Random Musings »

serbeer wrote:Another thought: if the spreadsheets used for modeling/simulations/solving, then performance can be improved dramatically (hundreds or thousands of times!) using Microsoft Excel-compatible Polymorphic Spreadsheet Interpreter. Frontline Systems developed this technology to create a spreadsheet recalculation engine.

This technology is used to power Extreme Speed simulations and optimizations in Oracle Crystal Ball simulation software (that I used to use).

It is transparent to the end user: spreadsheet is read in by the tool, and re-calculations can be run extremely fast on certain set change to spreadsheet's variables.

http://www.solver.com/products-overview.htm
I second this thought.

RM
I figure the odds be fifty-fifty I just might have something to say. FZ
ourbrooks
Posts: 1575
Joined: Fri Nov 13, 2009 3:56 pm

Re: Spreadsheets That Are Very Stable?

Post by ourbrooks »

If you have multiple projects, there's nothing at all to prevent you from setting up a separate database for each one.

I'd recommend Microsoft SQL Server, Express Edition. To start with, it's cheaper than Excel (unless you also got Excel for free). There are a zillion books, online tutorials and videos about how to work with it, and it's very easy to load data from it into an Excel spreadsheet when you want to graph it, etc.

Reliability is a major design goal of relational databases. They continuously save your entries so that if a crash happens, you never lose what you entered. Further, they support transaction structures, so that if something bad happens during the middle of an update, the database is never left in an inconsistant state. They also have index capabilities for rapid lookup of single rows; if you're making heavy use of Vlookup in your computations, then this is exactly what you want.
User avatar
TomatoTomahto
Posts: 17158
Joined: Mon Apr 11, 2011 1:48 pm

Re: Spreadsheets That Are Very Stable?

Post by TomatoTomahto »

We're all making recommendations here based on very little knowledge of what the problem is. I've got a hammer, your problem looks like a nail.
I get the FI part but not the RE part of FIRE.
User avatar
dual
Posts: 1383
Joined: Mon Feb 26, 2007 6:02 pm

Re: Spreadsheets That Are Very Stable?

Post by dual »

JupiterJones wrote: If you're doing a lot of statistical analysis, maybe a stats package (such as R) would be the way to go.
I second this. I have a lot of experience with spreadsheets including writing a commercial application based on Excel and I think a spreadsheet is a bad option for anything but simple calculations.

Complex spreadsheets are extremely difficult to understand and therefore to document and to maintain. As you have found, they put a lot of load on the computer so they execute slowly (for a given computer capability) and can become unstable.

This may be a tall order, but you should consider freezing your current development and starting to develop alternate code based on a procedural language like R or Visual Basic or Matlab. If you have a lot of database code, consider Access or MySQL.
mlipps
Posts: 1099
Joined: Thu Jun 21, 2012 9:35 am

Re: Spreadsheets That Are Very Stable?

Post by mlipps »

pshonore wrote:
bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.

If the company can afford it hire a Software Consulting firm to create a web application using a relational database to perform the functions. It will pay dividends in the long run and if the firm is any good can automate many of the processes.
Agreed - although Access can be a good user friendly front-end to a SQL D/B. 250K records is way too big for Access
Out of curiosity, how much does something like this typically cost?
downshiftme
Posts: 1142
Joined: Sun Mar 11, 2007 6:11 pm

Re: Spreadsheets That Are Very Stable?

Post by downshiftme »

Those are all pretty simple functions that a small amount of programming might allow you to accomplish with plain flat files. Export your giant spreadsheet into csv format and do the calculations on that.

Alternatively, splitting a 250,000 row spreadsheet into two 125,000 row spreadsheets, plus a three row summary sheet adds a small amount of labor to run the analysis on each of the two big sheets, but it gives a huge advantage in getting the size down to something that should reasonably be expected to perform fine on normal office computers. Even avoiding a single lockup or crash would more than compensate for the slightly increased work of dealing with the spreadsheet as PartA and PartB.

Lastly, does anyone in her office have a really large machine? Larger memory and higher processing power might push the ceiling where your programs start to misbehave high enough that your 250,000 row spreadsheet can be worked effectively on that machine.

Of these three, I suspect the splitting might be the easiest to accomplish quickly.
User avatar
TomatoTomahto
Posts: 17158
Joined: Mon Apr 11, 2011 1:48 pm

Re: Spreadsheets That Are Very Stable?

Post by TomatoTomahto »

mlipps wrote:
pshonore wrote:
bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.

If the company can afford it hire a Software Consulting firm to create a web application using a relational database to perform the functions. It will pay dividends in the long run and if the firm is any good can automate many of the processes.
Agreed - although Access can be a good user friendly front-end to a SQL D/B. 250K records is way too big for Access
Out of curiosity, how much does something like this typically cost?
How long is a string?
I know that sounds snarky, for which I apologize, but that's the kind of question whose answer will mislead you if it's provided by anyone whose understanding of the problem is based on his thread.
I get the FI part but not the RE part of FIRE.
bourg
Posts: 63
Joined: Fri Mar 16, 2012 9:26 am
Location: Indianapolis, IN

Re: Spreadsheets That Are Very Stable?

Post by bourg »

mlipps wrote:
pshonore wrote:
bourg wrote:Just don't go from Excel -> Access. Yuck - Access is so horrible.

If the company can afford it hire a Software Consulting firm to create a web application using a relational database to perform the functions. It will pay dividends in the long run and if the firm is any good can automate many of the processes.
Agreed - although Access can be a good user friendly front-end to a SQL D/B. 250K records is way too big for Access
Out of curiosity, how much does something like this typically cost?
My firm is based out of the midwest (so reasonably cheap) and work on a Time and Materials basis for $125/h. We've done projects like this ranging from $5k - $500k. It all depends on the complexity of the existing software and what automation and integrations will be built into the new custom solution. The downside to this approach is cost. The upside is that the company now has a piece of software that they own outright and does EXACTLY what they need to make their job processes as efficient as possible.
User avatar
JupiterJones
Posts: 3623
Joined: Tue Aug 24, 2010 3:25 pm
Location: Nashville, TN

Re: Spreadsheets That Are Very Stable?

Post by JupiterJones »

Claude wrote: How long is a string?
No more than 255 characters if you're storing it in an Access text field.

:P

JJ
"Stay on target! Stay on target!"
Post Reply