I was working on an Oil ETRM implementation in London for a large investment bank when the Head of Commodities Trading and my Team Lead walked over to my desk, both a bit red-faced.
“Drop everything. We’re going to New York.”
They managed to fill me in on the flight. The senior US Natural Gas trader had managed to lose $400m in a single week and all sorts of alarm bells had gone off and he was under investigation. When asked what happened, he simply replied that it was the fault of IT because the ETRM system did not work and showed him the wrong exposure. That was why my boss and I were on our way over. We had to find out what had gone wrong and why IT were being blamed. No pressure then.
The trader in question had been suspended from trading duties, but still expected to be present in the office so he could answer any questions. He sat on a non-functional desk on the New York trading floor looking like a toddler who had been put on the naughty step.
The US NatGas business was the first business to be implemented using the ETRM solution that I was working on for Oil. It had been implemented the previous year by consultants who had all long gone. A few hours of investigation had revealed that the reporting side of the system was a bit of a mess, but not “wrong”. However, it was clunky, so the traders had continued to use their spreadsheets for exposure and simply used the ETRM solution for trade entry only. The trade data and market data going in was correct. The reporting coming out was not great, but it was not to blame for $400m.
So, my next port of call was the trader spreadsheet. It was big and complex. Futures, swaps, and various types of options, including compound options (vol on vol), all part of the 30,000 trade portfolio. It didn’t take me long to figure out the problem. Due to the sheer amount of trades, macros were used to move data from one place to another, manipulate and pivot the data. The trader was not technical, so that meant someone had coded it all for him. The coder had protected his code (and most of the spreadsheet) and had subsequently left the company – I keep coming across this problem! The trader had been using this spreadsheet in blind faith and no support for almost 4 months. Because it was protected, I had to recreate the spreadsheet exposures from scratch using the same input data in order to prove where the old protected spreadsheet had gone wrong. I had to do a crash course in the US NatGas market as well as advanced option theory, but it was worth it. A few days of peeling back layers and I was ready to present my findings to the Head of Trading.
Now, the one thing that every trader and trading analyst knows is that spreadsheets do not travel well through time. Almost every trader I know spends the first working day of every month meticulously going through the formulae in their spreadsheet to make sure the prices and positions are correctly calculated. There are different formulae for calendar month contracts that roll at the end of the month and futures contracts that have various expirations. Some markets price in quarters, semi annuals, and annuals, so when a month rolls off the averaging changes. There are also considerations to be made on live feeds. Some contracts use month codes (CLZ7 is Crude Light Dec 17) and some use front month codes (CLc1 is the front month for Crude Light), so there are lot of places where a spreadsheet can go wrong. It turns out that over time, this particular spreadsheet rolled on in time and incorrect calculations had snowballed. Because the sheet was protected, no one could correct any of the formulae and because the trader was not “technical”, he didn’t even know the formulae were incorrect.
A spreadsheet schoolboy error had mushroomed into a $400m loss. We had managed to prove the innocence of the ETRM platform as well as back solve the protected spreadsheet and deciphered the real exposure. We delivered the new spreadsheet to management and planned an emergency remediation so that the reporting side of the platform was usable. A lot of expensive lessons learned!
If you’re wondering, the trader was eventually sacked for the loss, went on to work for a fund and made big money for the next couple of years….. but then history happened….. and that’s another story!