Microsoft’s calculator is partially to blame for JPMorgan losing $9bn, and a lot more besides.
Is Excel the most dangerous piece of software in the world? Baseline Scenario‘s James Kwak reports on a little-mentioned aspect of the notorious “London Whale” debacle at JPMorgan, where Bruno Iksil headed a proprietary trading team which made losses of up to $9bn.
It turns out, Kwak writes, that Excel was partly to blame:
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)… 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”… 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…”
Kwak wonders if the very ease of use that Excel offers — allowing people with no programming experience to knock together what are, in effect, relatively advanced applets — also makes it dangerous to use in most sensitive situations. There’s no debug, no audit trail, and no way to test why a spreadsheet returns the value it does. Similarly, training for Excel, where it exists, tends to ignore the importance of elegant and well-designed code, leading to legacy spreadsheets being used with internal workings which are opaque to all but their original creator, who may have left the company 20 years earlier.
The problem is, though, that Excel is the worst way to run a company’s software other than all the other ways. The fact that it’s capable of being programmed by the people who will end up using it means that it might enable hacked-together code, but it also prevents exactly the sort of corporate bloat which leads to people circumventing their company’s software in the first place.
This piece originally appear on the New Statesman, from: http://www.newstatesman.com/technology/2013/02/excel-most-dangerous-piece-software-world?goback=%2Eanb_144180_*2_*1_*1_*1_*1_*1
By Alex Hern, from: http://www.newstatesman.com/technology/2013/02/excel-most-dangerous-piece-software-world?goback=%2Eanb_144180_*2_*1_*1_*1_*1_*1
February 26, 2013 at 8:34 pm
The ease is a problem but not for the reason suggested. People make mistakes programming R, SAS, or any other program that only experienced programmers would consider using. The problem in the example cited was that no one from audit vetted the the formulas before it was put into production.
The more pervasive problem is that excel makes it so easy to do multiple scenarios quickly. It is easy to tweak interest rates, growth rates, etc. so that every loan application “works”, for instance. The arithmetic executed by excel may be flawless but the analysis is sorely lacking.
February 27, 2013 at 2:04 pm
Thanks, Jim, your points are so apt. I especially agree with your thought that >>The arithmetic executed by excel may be flawless but the analysis is sorely lacking.<<
Thank you for your comment!
March 5, 2013 at 5:07 pm
I think Excel is excellent as well. Unfortunately, as mentioned in the article, spreadsheets are often handed down from their original creators to those less adept at formula writing. Therefore, the results of calculations are religously reported without question. I worked for an organization that was using spreadsheets that were over 5 years old. No one had updated them, or audited the formulas and yet they were used religously on a daily basis by new hires that were not experienced in creating spreadsheets. I used to run Lunch and Learn sessions on excel and when I first approached my CFO with the idea, he laughed and said “this is the Finance department everyone knows how to use excel”. Much to his surprise, the Lunch & Learns sessions were packed and as the instructor, I was rather surprised at how little the finance team did about formula writing. At best they had basic formula writing skills. I used to teach, V & H lookups, Sumif statements, quick key strokes and the all mighty Pivot Table. Ultimately, as a new user of this company’s spreadsheets, I had the opportunity to use a particular file and discovered a hidden row playing into a calculation that carried a large amount to it. No one could explain the hidden row or the amount associated with it. 3 years of calculations were in effect in error due to what was eventually discovered to be an error by an associate who had never used Excel. The associate had iadvertantly hidden the row with a large dollar amount and it went undetected for 3 years.
In effect, it is not Excel that is dangerous, It just does what you tell it to. It is the person that management entrusts to use Excel that can be dangerous.
You certainly wouldn’t let an associate drive a company car if they didn’t have a license.. would you?
March 14, 2013 at 11:32 am
Thanks for your comment, that is a terrific (extreme!) example, but I’m sure it’s typical of things that happen in many organizations. Pass along that model or spreadsheet to an assistant, or anyone who doesn’t know how to use the application, and dangerous things can happen.
But, “hidden a large dollar amount that went undetected for 3 years”…that’s crazy.
February 27, 2013 at 5:55 pm
Microsoft became a “standard” due to marketing not quality.
February 28, 2013 at 11:30 am
Thanks, CJ, that certainly could be part of the problem. I think the biggest issues are 1) hubris - no one thinks they will ever make a mistake and 2) too many hand in the Excel pie and 3) not enough testing / risk management. Thanks for your comment.
March 1, 2013 at 2:42 pm
Does not matter how skillful the author is or the quality of the tools used. Bad practices equals Bad tools equals bad results. Garbage in ……
The spread-sheet “model” should have been tested to death before being used for such a critical function, the issue was not with the tools it was with the moron (learned as he or she may have been) that failed to design the sheet and it’s surrounding processes properly ….
March 1, 2013 at 3:02 pm
Thanks, Chi, so true. The lack of forethought, lack of testing, is the critical problem here.
March 3, 2013 at 5:45 pm
Excel is an excellent tool (pun intended) for short term (temporary), non critical and flexible problem solving. The dangers arise when companies or individuals rely on Excel for business critical calculations and the risks are multiplied when this reliance is extended for a period of time (even more so when the spreadsheets are poorly designed and regularly changed).
It is not the tool that is the problem here but the erroneous application of it and an absence of strong oversight to inputs and outputs of the models. When the stakes are as high as the above example, management should not be trying to save a couple of hundred thousand dollar on proper software development and oversight.
March 4, 2013 at 3:22 pm
Thanks David. Re your comment, >>It is not the tool that is the problem here but the erroneous application of it and an absence of strong oversight<< completely agree, the process is flawed when testing and oversight are not part of it.
March 4, 2013 at 8:37 am
Wrong/erroneous deployment of models is more common than you would guess, have heard of horror stories, inverted probabilities sent to client.
Fixes, where only the programmer knew long after, It all boils down to lousy testing and UAT procedures.
First time I have heard of billion dollar bets on an excel/vb deployment though. and this is only a case coming out!
March 4, 2013 at 3:21 pm
Thanks, Karan…re your comment >>First time I have heard of billion dollar bets on an excel/vb deployment though.<< me too, but I’m sure we’ll be hearing more!
March 5, 2013 at 7:53 am
Our solution wraps around Excel and mitigates these issues. It allows the company to continue to work in Excel, yet drives to one version of the truth.
April 5, 2013 at 1:27 am
Blaming the tool and not the operator. Lame excuse. Excel is a good tool but only a tool. In the days of calculators, one would not blame the calculator if the calculation was wrong, but the user. The problem is not Excel, although Excel does have bugs. Excel is more complex, and it is easy to over rely upon it. But a good model builder knows how to build checks to confirm that calculations are correct.
April 15, 2013 at 10:51 am
Thanks, Chien, we totally agree that the fault is not with Excel (or any tool) specifically, but with the management / use of that tool. When too many users deploy a model, many who may not know exactly how to use it (or what defaults are built in), bad things can happen. Thanks for your thoughts!
November 15, 2013 at 11:44 am
Honestly, it is a poor craftsman who blames his tools. Excel has VBA which allows for quite sophisticated work to be done, not requiring “manual cut and paste” and the incorporation of unit tests which would, if properly crafted, create conditions that would catch errors in calculation.
The problem here is not, as the article tries ot make out, the Excel tool. The problem is A) complete and total incompetance of the people who created the system, B) complete and total incompetance of the people managing the people in A and C) the cheap thinking that is an epidemic in corporations by which they won’t hire “expensive” software engineers to create safe and reliable systems but instead let people with no business creating mission critical software to run their business.
This error was not caught because of greed (cheapness on the front end and willful blindness on the back as the system calculated too low a VaR.)
January 29, 2014 at 7:40 pm
I would disagree with the charge that Excel is often the wrong application for the job and is used too often because companies are too cheap to purchase the correct software. While that may be true in some cases, I’ve worked in businesses where a software product that was flexible enough and sophisticated enough wasn’t offered on the market. That easily could have been the case here with a company like JP Morgan. And besides, sometimes Excel is used to help check that the software being used actually works, or to help communicate the needed design of the software. So I’m agreeing with the opinions shared here prior to this post that the issue is the users and the processes. Excel can certainly have errors, but so can leaving out a crucial input in a major software application (or even just fat fingering one). Thanks for the interesting article and responses.