At Uni, decades ago (and using Lotus-1-2-3 rather than Excel), we were given a set of spreadsheet templates for a business management exercise (the project ran much the duration of the course). I discovered looking at these that there was an error in the spreadsheet double-counting all inputs (rows and columns were added and the row-totals included in the grand total). Best I can tell, finding that bug was not in fact part of the course goal....
At an early gig doing analytics we hired an old fart who actually introduced some rigour to our project practices, including code walkthroughs. Some of this involved more obviously programmatic tools, but the process was also applied, over the strenuous objections of the analyst involved, to a project largely done in Excel. Numerous errors were found, and corrected. The spreadsheet was also significantly rationalised by having "dumb" data-entry area, and then relying on a sheet with VBA code rather than in-cell logic for the computations.
The analyst continued to object to the code review, after the errors were found and corrected, insisting that it was inappropriate because he "wasn't a programmer". (I looked him up recently, he's continuing to work on analytic projects, two decades on.)
This was also about the time that I first heard of Ray Panko, at the University of Hawaii, who'd found reliably that 88% of spreadsheets had errors. This still holds largely true.
http://panko.shidler.hawaii.edu/
I'm not saying "get rid of all Excel". But I am saying that the tool makes for very, very, very buggy processes, and this is itself a problem.
I'd love to see work toward alternatives that retain the flexibility and versatility of Excel (or other spreadsheets) but reduce the possibility of bugs and errors.
Multiple times I've come across issues like this when developing the replacement for an old access system. The numbers between the new an old system don't match, so you have to dig through both to find the error. If the error is in the new version that's great, but when you tell the boss that the error is in the old one and they've been off by a few million dollars over the years...
However, development costs are so expensive they might have broken even when factoring the cost of creating a system.
Now I just offer handy things like pre-sorting, filtering (some boolean columns to allow them to quickly filter stuff), better column names or reports with extra info. I try to figure out the things they find tedious and automate them once and for all. But I hold back as much as I can.
Couldn't agree more. It's also a huge, latent liability. In my experience, mostly because it's an opaque data store.
To use the parent's example. Turn's out the $100mm business is actually only worth $80mm because someone had accidentally coded GBPUSD directly into the spreadsheet. Before Brexit was announced. Say goodbye to senior trader.
This sort of thing happens though not as much as it used to as we don't allow Excel in the path that hits the ledger.
http://www.codedawn.com/excel-add-ins.php http://blog.malcolmp.com/2010/regular-expressions-excel-add-...
The only people who think it can be or needs to be done better are primarily not the users.
Excel is the human condition, messy, varied, capable of doing many things and most of the features aren't used, and results are often horrifying. But it works.
That said, I want regex based search in excel, or better data cleaning tools.