[Closed] $9bn Excel error FAIL

There's no debug, no audit trail, and no way to test why a spreadsheet returns the value it does

dont blame the tool for process failures.

excel was partly to blame

I always try and graph my data (easier for me since it's mainly timelined cashflows) and before looking at the output I sketh the line I expect to see on a piece of paper, noting key dates and shifts in the line. If the graphed line doesnt look like my expected line each variation must be identified and rationalised or corrected.

The article is instantly compromised by having a question as the headline. There is another well known publication that employs the same [url= http://www.qwghlm.co.uk/toys/dailymail/ ]lazy tactic[/url]

we are currently going through a transition to Office 2003 and I dread to think of the errors that will result as people get used to the new Excel layout.
with Excel the errors aren't obfuscated by programming code. is there a greater risk with known knowns or unknown knowns?

That's great stoner and better than most do, unless you've got an order of magnitude error...

The links at the bottom of the article also point to the DailyMailisation of the Staggers

More from New Statesman
"Porn never did me any harm"

Hitler wrote Mein Kampf, hence books are bad.

I think this in the comments sums it up. 🙂

Lack of compliance and process, nothing to do with excel.

having said that, I have seen massive spreadsheet apps running trades here and shuddered, but they have generally been replaced with actual software now

And people wonder why IT departments are reluctant to start supporting 'a quick Excel workbook I put together'.

As above, it's a failure of coporate governance, not an Excel failure that caused it.

brakes - Member
we are currently going through a transition to Office 2003 and I dread to think of the errors that will result as people get used to the new Excel layout.

I'm assuming that you mean a transition from 2003 to 2007

Didn't find that here at all FWIW. Yes people took a while to get used to where things were but they didn't make errors as a result.

this link is really useful - particularly the interactive guides

http://office.microsoft.com/en-gb/training/guides-to-the-ribbon-use-office-2003-menus-to-learn-the-office-2007-user-interface-HA010229584.aspx

The problem is that Excel is a pretty basic programming tool but because people can slap together stuff so easily they think they can create production quality solutions with it, which can be impossible for anything other than mickey-mouse or personal use.

Loads of banks have slapped together/clunky systems like this based on Excel, trying to handle things like real-time price feeds, etc, when the tool is just not up to it. They call these systems 'strategic' in an effort to justify their slapped-together nature.

There is also the problem of traders using their own models on private sheets instead of some model where the risks are fully known to the bank.

I also blame Microsoft as the spreadsheet model is fine, but the tool is junk for the purposes companies try to put it to.

[i]I'm assuming that you mean a transition from 2003 to 2007

Didn't find that here at all FWIW. Yes people took a while to get used to where things were but they didn't make errors as a result.

[/i]

Same here. A week's worth of whinging followed by business as usual.

sorry yes, transition FROM Office 2003.
I expect that the whinging will go on for months, but at least people will stop whinging about not being able to open files generated outside the company and the fact that Internet Explorer 8 doesn't work anymore...

Have they never heard of regression testing?

Every time I change my VBA, I run regression tests to check to see if I've changed something I didn't intend to. Often pops up differences which have to be hunted down and explained or fixed.

I once inherited a set of projects that were part of a company-wide programme; my bit was about £10m out of the £100m.

My team accountant was struggling with the budgets (a vast concoction of inherited and created sheets across multiple files), but there was an error he just couldn't find.

So one weekend I took a copy and attemped to desk-check it (I was a Cobol programmer from the days when we wrote them onto coding sheets, rather than having terminals to input ourselves).

After about 10 hours I found the error, someone had added £179,000 onto the end of a 3-line formula...

Once I found that, I had him desk-check the lot - we were about 1/2 million out across the programme. It took a lot of hiding did that 😉

Trace precedents and trace dependents is a great tool for unpicking convoluted spreadsheets.

Have they never heard of regression testing?

I don't think you quite understand the programming environments in these establishments.

If they were mature enough to have regression testing suites then they wouldn't have chosen to write the thing in Excel/VBA.

Plus the sheets are often not written by any form of experienced programmer...

dont blame the tool for process failures.

in this case, i'd say blaming the tools is exactly the thing to do

Microsoft's calculator is partially to blame for JPMorgan losing $9bn, and a lot more besides.

No it isn't! Stupid article.

As others have said, it's a process failure. Not the tool's fault.

We use Excel and more advanced software and even do calculations on paper. Sometimes for amounts as big as JPM's loss. The process is always the same: do, check and then review.

Nought wrong with Excel or VBA, you just have to apply the same processes as with any other SW development.

I have a Nintendo

The problem is not excel but the bank allowing something so stupid and inept. End of

there's no regression testing, let alone consideration for recovery or tool suitability etc - because users want to do things faster than an IT department would, but the stuff that IT take their time over is this kind of stuff that bites them on the arse

[i]there's no regression testing, let alone consideration for recovery or tool suitability etc - because users want to do things faster than an IT department would, but the stuff that IT take their time over is this kind of stuff that bites them on the arse [/i]

Yep, so many user think they are IT experts, mainly because they can link Excel sheets and they've setup their home network.

The strength of something can also be its weakness....

It's the unstructured nature of Excel that produces real benefits (ease and speed of use, adaptability, quick to learn etc.) but also often leads to problems (bugs, mess, too many layers and opacity).

The key is good model design, which is often not taught alongside Excel skills and functions. I consult in Excel and teach Excel modelling at uni, so I bang on about this quite a bit...

From reading the context of that article, I wouldn't be sure that this wasn't deliberate - the spreadsheet calculating the risk had a sum instead of an average which, what would you know, made the dodgy trader's positions look less risky?

That's one of the the other beautiful effects of excel misuse, hiding dodgy numbers

someone had added £179,000 onto the end of a 3-line formula

that doesn't happen by accident

we were about 1/2 million out across the programme

You wondered how that had happened. I'd be wondering why.

It took a lot of hiding

there's an app for that

Nought wrong with Excel or VBA

I beg to differ, as would any programmer that has been forced to write any system of any size in Excel, and then spends the rest of their career having to support it.

Luckily I only had a brief flirtation with Excel (and not VBA) when creating a plugin for real-time price display, because the ones from Reuters were so clunky - again you would think that there would have been a decent programming model behind Excel considering how long Microsoft have been developing it, but not in the version I was using - having to thunk through timers to toggle between the different function calls and where you can call them from was crazy.

Luckily I found the book "Financial Applications Using Excel", although I think that is now outdated.

I love Excel, but hate the way some muppets have created truly terrible worksheets, then locked and password proteted them. We then have to keep using them "Coz that's what we use..." despite obvious errors and duff formulae.

Excel is fine. Macros are programming: normal rules of software development apply. Users are idiots.

I did some work a few years ago to build a new process in Oracle Apps to do all the billing for Link cash machines - the way the banks get charged for using each others machines. All very complicated and the process I was building was to replace an incredibly complex Excel spreadsheet that no-one really understood and the users were pretty sure they were losing money due to bugs.

[i]that doesn't happen by accident[/i]

Agree, there's always incompetence...

there's no regression testing, let alone consideration for recovery or tool suitability etc - because users want to do things faster than an IT department would, but the stuff that IT take their time over is this kind of stuff that bites them on the arse

Sounds like IT failing to meet the requirements of the business again. 😈

In turn, usually caused by the business failing to fund IT sufficiently to provide the resources it needs... 😉

In turn, usually caused by the business failing to fund IT sufficiently to provide the resources it needs...

And that's excel's greatest strength and weakness. Anyone can use it and it can be a very simple solution to a complex problem.

Only some people realise that it isn't always that simple...

IT: it'll cost £X and take Y months to setup and test.
Manager: yeah but Dave the work experience lad is a dab hand with excel, we'll get him to do it, then you can support it yeah?
IT: that's an incredibly bad idea.
Manager: No, I just saved the company £X

that's the way it works at most places 🙁

IT can be at fault as well though, 'cos there is no guarantee that the system that took Y months to build will be sufficient or workable.

But modern development techniques such as automated unit testing/mocking/agile/etc are a much better way of being able to deliver demonstrable quality as fast as reasonable possible without having to sink to the depths of Excel.

I beg to differ, as would any programmer that has been forced to write any system of any size in Excel, and then spends the rest of their career having to support it.

I write and maintain a tool in Excel, it's about 50k lines of VBA.

It's no harder to maintain than any other language, all the same provisos apply, it needs to be well written, structured, tested, documented etc.

EDIT: for anyone into Excel, this is a must read:

[img] [/img]

it needs to be well written, structured, tested, documented etc.

precisely, and how many Excel programmers have those skills?

 

IT can be at fault as well though, 'cos there is no guarantee that the system that took Y months to build will be sufficient or workable.

As often as not that's the customer's fault...

I did some work a few years ago to build a new process in Oracle Apps to do all the billing for Link cash machines - the way the banks get charged for using each others machines

You need a BRMS, that's what you need there.

As often as not that's the customer's fault...

ah yes, blame the specs.

It is more a failure of process here - requirements gathering, etc - step forward agile or XP...

precisely, and how many Excel programmers have those skills?

Quite a few, there's a huge VBA development community and loads of companies run on VBA Aps eg I notice the jeweller I was in the other day was using a VBA CRM system hooked into Outlook.

The issue is the developers, not the system. You can write duff code in any language....

EDIT: for anyone into Excel, this is a must read

Not anyone! Not many people get close to the level where they'd need to open [i]that[/i] book.

Anyone want to buy my pristene copy?

Not anyone! Not many people get close to the level where they'd need to open that book

I've pinched loads of their code and methodology for my apps. But then I've spent the last few years spending 60-80% of my time working in VBA.....

ah yes, blame the specs.

I didn't say blame the specs. I said it's the customer's fault. As you rightly point out, it's not as simple as incorrect specs. It's often customers not really understanding their situation.

Development teams need to do a lot more than code to specs. They need to take the client through the process of creating IT systems. However, most clients think they already know all about that when they really don't.

Quite a few, there's a huge VBA development community and loads of companies run on VBA

none that I've met...

Not anyone! Not many people get close to the level where they'd need to open that book.

I think I have browsed that book and I don't think it was very low-level. Maybe that is the problem, too many high level VBA programmers not really knowing what they are doing or the environment they are working in...

GEEK FIGHT!

[img] [/img]

I think I have browsed that book and I don't think it was very low-level.

It's not low level, nor is it a basic book. It's probably 50% methodology and approach and 50% advanced techniques on GUIs, Dictator Apps and interacting with other SW eg databases, Windows OS, VB, DLLs, .NET, etc.

GEEK FIGHT!

That was a very odd film!

how many Excel programmers have those skills

The issue is the developers

The issue is that lots of this stuff is created by people who aren't, nor would claim to be, "programmers" or "developers". Lots of them are accountants. They understand what they want it to do, they understand the maths involved, and because it's easy to get stuck in and make it do things, and they spend most of their lives using Excel anyway, they can make excel do some fairly complex things that will answer the questions they start with. But they won't care (or know) anything about development methodologies, testing or whatever, they'll just design a apreadsheet to do what they want to do. Then start using it.

I am one of those accountants, btw

[img] [/img]

GEEK FIGHT!

producing production quality systems is also a task often beyond geeks (and nerds)

Don't mess with the Nerds:

[img] [/img]

But they won't care (or know) anything about development methodologies, testing or whatever, they'll just design a apreadsheet to do what they want to do. Then start using it.

Nothing wrong with that per se, it's when the organisation starts to rely on those spreadsheets and they end up filling the role of a properly designed IT system.