I’ve made this case in conversation several times — usually after having to forensically determine just why someone’s spreadsheet produced an unlikely answer — the greatest strength of spreadsheets is also their greatest weakness. Anyone who’s built a spreadsheet knows how easy it is to make a mistake, and how hard that mistake can be to detect after the fact. Spreadsheets are free-form: you can set up relationships on the fly, pull data from one place to plug into a different formula somewhere else. It’s literally empowering to gain that much control over your data without having to learn a full programming language.
But that flexibility and power comes at a cost: there’s no built-in error checking of your assumptions. Oh, it’ll alert you to practical problems like mis-matched data types or mechanical errors in your formula, but can’t tell you whether the operation you’re attempting makes sense. The program can’t read your mind and can’t sanity check your work.
Do a spreadsheet for your family budget and you’ll almost certainly make a minor error or two.
Make a set of inter-linked spreadsheets and you probably double the chances of error for each new spreadsheet in the set.
Make a set of inter-linked spreadsheets that require manual copy-and-paste updates and you exponentially increase the chances of error.
Then, make that manually updated set of spreadsheets have a real-world impact on vast amounts of money:
To give you and idea of how important this is here’s a great tale from James Kwak:
The issue is described in the appendix to JPMorgan’s internal investigative task force’s report. To summarize: JPMorgan’s Chief Investment Office needed a new value-at-risk (VaR) model for the synthetic credit portfolio (the one that blew up) and assigned a quantitative whiz (“a London-based quantitative expert, mathematician and model developer” who previously worked at a company that built analytical models) to create it. The new model “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another.” The internal Model Review Group identified this problem as well as a few others, but approved the model, while saying that it should be automated and another significant flaw should be fixed.** After the London Whale trade blew up, the Model Review Group discovered that the model had not been automated and found several other errors. Most spectacularly,
“After subtracting the old rate from the new rate, the spreadsheet divided by their sum instead of their average, as the modeler had intended. This error likely had the effect of muting volatility by a factor of two and of lowering the VaR . . .”
To translate that into the vernacular, the bank, JP Morgan, was running huge bets (tens of billions of dollars, what we might think of a golly gee gosh that’s a lot of money) in London. The way they were checking what they were doing was playing around in Excel. And not even in the Masters of the Universe style that we might hope, all integrated, automated and self-checking, but by cutting and pasting from one spreadsheet to another. And yes, they got one of the equations wrong as a result of which the bank lost several billion dollars (perhaps we might drop the gee here but it’s still golly gosh that’s a lot of money).
And it’s not just JP Morgan: every financial firm, every bank, every brokerage uses Excel (or another spreadsheet program). Multiply JP Morgan’s experiences by the number of companies to get a rough idea of how much is at risk from un-audited (possibly even un-audit-able) financial models running on spreadsheets.
