5 ms·

JP Morgan's $6.2 billion "London Whale" trading loss: An unnamed overworked staffer in London copied and pasted the wrong figures into an Excel spreadsheet

by panarky 12y ago

JP Morgan's $6.2 billion "London Whale" trading loss: An unnamed overworked staffer in London copied and pasted the wrong figures into an Excel spreadsheet, throwing the firm's risk models out of whack. http://finance.fortune.cnn.com/2013/01/18/jpmorgan-london-whale-investigation/ http://finance.fortune.cnn.com/2013/01/18/jpmorgan-london-wh... Spreadsheet errors are reaching epidemic proportions globally. What we need is a transnational organization to contain the threat with research, best practices and conferences! http://www.eusprig.org/ http://www.eusprig.org/ Love their compendium of horror stories. Did you know the US Securities and Exchange Commission has weak accounting because they rely on spreadsheets? http://www.eusprig.org/horror-stories.htm http://www.eusprig.org/horror-stories.htm As we have reported in prior audits, SEC's general ledger system and certain software applications and configurations are not designed to provide the accurate, complete, and timely transaction-level data needed to accumulate and readily report reliable financial information... Many of the agency’s financial reporting processes are still manual in nature and reliant on spreadsheets and databases to both initiate transactions and perform key control functions.

bane 12y ago
I guess it's a good thing we don't have errors in software then, because that would totally eliminate these kinds of issues. Oh wait, a user can enter a bad number in any tool, regardless of it being Excel or some custom cobbled together monstrosity that does less than the same work, but costs 100x as much.
twistedpair 12y ago
I used to work for Deutsche Bank. All financially modeling ran through Excel, though admittedly a massive C++ library that is a big Excel macro plugin. These sheets get massive and there are a few clusters of blades that do nothing but run Excel macros day and night. But why is this madness? It works for them. The iBanking world is the madness. You can put in place effective change controls and auditing with any system. However, if people just want it done and don't care how, regardless of whether these calculations were in Python, Mathematica or Fortran, garbage in will still be garbage out. So, let's not wantonly scapegoat Excel for a process problem here.
- dworin 12y ago
  There is actually a business reason why this is the case. The general assumption in most investment banks is that either the market or the regulations will change so quickly that by the time you've written software the "right" way, either the market or the government has already moved on. Excel lets you move fast, and make changes quickly, even if it results in a bloated mess later on. Like you say, Excel isn't the issue, it's the nature of the business.
  - orbifold 12y ago
    I would still expect that with their profit margins they would all have come up with custom solutions by now.
    - jmeister 12y ago
      They all do in varying degrees - e.g. Goldman's Slang/SecDB -and are investing in well-engineered valuation/risk systems that work across various trading units due to regulatory pressures as well as to reduce manpower/maintenance costs.
  - collyw 12y ago
    You make Excel sound like the true agile way to do software.
- rrrx3 12y ago
  Coincidentally, so did I. I agree with you 100%. iBankers use Excel because it does what they want. One of the main reasons I left was because I got tired of trying to re-invent the excel wheel with every piece of software. There was always an inherent lack of trust that any UI was "doing it properly," so we always had to add the failsafe of export to CSV so people could do it themselves in Excel. Mind-boggling inefficiency.
  - watwut 12y ago
    The value of export is beyond just checking on UI. If they can expert it to Excel, then they can do whatever simple data analysis they just made up. No reason to wait for IT to implement whatever stats they are curious about, order it in some new way or whatever. Doing those things in excel is very fast and they get to have complete control over it. Even if they only color cells and rows depending on some ad-hoc rule that will never be used again, excel still added them a lot of value. Plus, I would pay gold for users that actually test system before relying on it. There is always shortage of testers and if the customer would do some of it, he deserves a discount.
jacquesm 12y ago
Nobody ever caused a bug in a piece of custom software that caused an expensive problem? Knight Capital Group? Users make mistakes, users given the power to work with computers that can cause billions of $ to be traded can cause billions of $ in losses. The solution would seem to be to limit the exposure, to put a cap on the amount of $ that a user could trade without going through some form of verification. Spreadsheets may not be ideal, but they're the poor mans gate to programming and I suspect they don't have a bug incidence that is much greater than regular software developed by programmers. Of course the programmers would love to believe otherwise.
- anigbrowl 12y ago
  Yeah, I don't get all the Excel-hate. You can most certainly put in tests and other safety factors; back in the 90s I built spreadsheets for tasks ranging from electricity market pricing (for a national power generation company) to interest-rate shopping (for banks to allocate expense spending). There were tests and tests of tests because the sheets were shuffling around huge quantities of money. The object was not just to arrive at the best outcome every month but to generate a defensible audit trail that referenced the canonical steps of the manual procedure. I get that programmers hate the formulae being distributed all over the place, but this is precisely the advantage for business people; they want to be able to 'walk the heap' and follow the provenance of individual values back to the source, and they want to see things in parallel at all times. CS people lean towards theoretical provability, business people are inclined towards using statistical sampling. So I would write unit tests that would lock most of a spreadsheet while running a batch of checks on the contents of individual sub-sheets, then lock the validated sheet, unlock the next sub-sheet and copy-by-value the data into that for the next round; but I would also load up random historical batches that were known-good and make sure all the final totals matched. Sure, spreadsheets are inefficient, but a mistake in an a very efficient process can easily lead to monstrous results. And from the domain specialist's point of view (ie the accountants/business managers), the inefficiency cost of doing things in spreadsheets is far preferable to the costs of auditing a black-box process at a later date if queries arise about its integrity.
DanBC 12y ago
I love EUSPRIG. Another website people interested in spreadsheet erros might like is Ray Panko's spreadsheet research http://panko.shidler.hawaii.edu/SSR/ http://panko.shidler.hawaii.edu/SSR/

by panarky 12y ago

bane 12y ago

I guess it's a good thing we don't have errors in software then, because that would totally eliminate these kinds of issues. Oh wait, a user can enter a bad number in any tool, regardless of it being Excel or some custom cobbled together monstrosity that does less than the same work, but costs 100x as much.

twistedpair 12y ago

I used to work for Deutsche Bank. All financially modeling ran through Excel, though admittedly a massive C++ library that is a big Excel macro plugin. These sheets get massive and there are a few clusters of blades that do nothing but run Excel macros day and night. But why is this madness? It works for them. The iBanking world is the madness. You can put in place effective change controls and auditing with any system. However, if people just want it done and don't care how, regardless of whether these calculations were in Python, Mathematica or Fortran, garbage in will still be garbage out. So, let's not wantonly scapegoat Excel for a process problem here.

dworin 12y ago
There is actually a business reason why this is the case. The general assumption in most investment banks is that either the market or the regulations will change so quickly that by the time you've written software the "right" way, either the market or the government has already moved on. Excel lets you move fast, and make changes quickly, even if it results in a bloated mess later on. Like you say, Excel isn't the issue, it's the nature of the business.
- orbifold 12y ago
  I would still expect that with their profit margins they would all have come up with custom solutions by now.
  - jmeister 12y ago
    They all do in varying degrees - e.g. Goldman's Slang/SecDB -and are investing in well-engineered valuation/risk systems that work across various trading units due to regulatory pressures as well as to reduce manpower/maintenance costs.
- collyw 12y ago
  You make Excel sound like the true agile way to do software.
rrrx3 12y ago
Coincidentally, so did I. I agree with you 100%. iBankers use Excel because it does what they want. One of the main reasons I left was because I got tired of trying to re-invent the excel wheel with every piece of software. There was always an inherent lack of trust that any UI was "doing it properly," so we always had to add the failsafe of export to CSV so people could do it themselves in Excel. Mind-boggling inefficiency.
- watwut 12y ago
  The value of export is beyond just checking on UI. If they can expert it to Excel, then they can do whatever simple data analysis they just made up. No reason to wait for IT to implement whatever stats they are curious about, order it in some new way or whatever. Doing those things in excel is very fast and they get to have complete control over it. Even if they only color cells and rows depending on some ad-hoc rule that will never be used again, excel still added them a lot of value. Plus, I would pay gold for users that actually test system before relying on it. There is always shortage of testers and if the customer would do some of it, he deserves a discount.

jacquesm 12y ago

Nobody ever caused a bug in a piece of custom software that caused an expensive problem? Knight Capital Group? Users make mistakes, users given the power to work with computers that can cause billions of $ to be traded can cause billions of $ in losses. The solution would seem to be to limit the exposure, to put a cap on the amount of $ that a user could trade without going through some form of verification. Spreadsheets may not be ideal, but they're the poor mans gate to programming and I suspect they don't have a bug incidence that is much greater than regular software developed by programmers. Of course the programmers would love to believe otherwise.

anigbrowl 12y ago
Yeah, I don't get all the Excel-hate. You can most certainly put in tests and other safety factors; back in the 90s I built spreadsheets for tasks ranging from electricity market pricing (for a national power generation company) to interest-rate shopping (for banks to allocate expense spending). There were tests and tests of tests because the sheets were shuffling around huge quantities of money. The object was not just to arrive at the best outcome every month but to generate a defensible audit trail that referenced the canonical steps of the manual procedure. I get that programmers hate the formulae being distributed all over the place, but this is precisely the advantage for business people; they want to be able to 'walk the heap' and follow the provenance of individual values back to the source, and they want to see things in parallel at all times. CS people lean towards theoretical provability, business people are inclined towards using statistical sampling. So I would write unit tests that would lock most of a spreadsheet while running a batch of checks on the contents of individual sub-sheets, then lock the validated sheet, unlock the next sub-sheet and copy-by-value the data into that for the next round; but I would also load up random historical batches that were known-good and make sure all the final totals matched. Sure, spreadsheets are inefficient, but a mistake in an a very efficient process can easily lead to monstrous results. And from the domain specialist's point of view (ie the accountants/business managers), the inefficiency cost of doing things in spreadsheets is far preferable to the costs of auditing a black-box process at a later date if queries arise about its integrity.

DanBC 12y ago

I love EUSPRIG. Another website people interested in spreadsheet erros might like is Ray Panko's spreadsheet research http://panko.shidler.hawaii.edu/SSR/ http://panko.shidler.hawaii.edu/SSR/