A Corporate Disaster Resulting from Spreadsheet Integration

Spreadsheets are powerful and ubiquitous tools. They allow relatively non-technical people to solve problems in their day-to-day business lives. Once these begin to encroach on core business processes though, we begin to run the risk of interrupting core business services. I have previously summarised two areas where this can be a worry.

Today I was reminded of a story I thought I would share with you, from my pre-charity work with global financial services companies. At the time, I was heading up internal applications for one of the world's largest asset management firms. Think of these as like share traders, but where the value of each trade is often in the hundreds of millions, sometimes more.

I became aware of the issue at 3 am when my phone rang. At the other end, a trading manager had just realised that a large trade (£200M) that had been made the day before had settled for a different amount than the value for which it had apparently been submitted. Basically, a trade had been made at a loss when they were expecting it to have made a significant profit. The discrepancy was a significant distance into the millions of pounds.

This was real money lost, not some theoretical amount on an internal report. If the traders had realised the real value of the assets they were buying, they would not have made the trade until the position improved. You can imagine the craziness that ensued for the next couple of days.

I think it is the best example of where an initially minor and innocent spreadsheet became a mission-critical part of a financial institution's tech platform without anyone realising. Every day at around 11 am, it was part of someone's job to run the previous day's end-of-day reports, download them as a CSV file, load the file into a spreadsheet, press a few buttons, select a number of columns from the spreadsheet, and save that information into another CSV file in a different directory.

What this person (or anyone else in the organisation) were no longer aware of, is that this process was required in order to keep the day's asset prices current in downstream trading systems. It transpires that this person was on leave the day before the issue in question. And simply because no one was aware of its importance, the spreadsheet created a downstream catastrophe because someone used old information to make a trade.

I'm pretty sure this issue had happened previously – but either they weren't able to isolate the source of the problem, or the discrepancies were small enough that no one noticed or cared. We found over 100 versions of the spreadsheet in various backup folders where people had manually backed them up for fear of causing issues. The dates on these old versions went back over a decade, so I think what happened is that the people who understood the importance of the spreadsheet in the early days had simply left without passing on the knowledge effectively.

The trick with these scenarios is noticing when spreadsheets are being used in the flow of information between two systems. Once this happens, it is always cheaper to convert the logic of the spreadsheet into automated code that runs reliably every day and is documented well. As it turns out, this spreadsheet was removed within 48 hours with only one developer working on it.

Andrew Walker
Technology consulting for charities
https://www.linkedin.com/in/andrew-walker-the-impatient-futurist/

Did someone forward this email to you? Want your own subscription? Head over here and sign yourself right up!

Back issues available here.