Energy Trading: Spreadsheets vs Automated Software

TO ENHANCE YOUR READING EXPERIENCE, CLICK HERE TO VIEW THIS ARTICLE IN OUR INTERACTIVE READER!

Energy Trading & Risk Management:  Spreadsheets vs Automated Software  

written by Stuart Cook

Any energy trader who works with spreadsheets knows that it’s all too easy for errors to creep in. Most are due to negligence, but some are due to fraud. In 2002, rogue trader John Rusnak’s fraudulent manipulation of spreadsheets lost his employer, Allfirst Bank, US$691 million.

Spreadsheet disasters are not restricted to banks. In 2003, Canadian energy company TransAlta Corp suffered a US$24 million hit to its earnings after a bidding error meant it accidentally paid over the odds for more US energy transmission hedging contracts than it needed. The company’s spreadsheet contained mismatched bids for the contracts. TransAlta’s chief executive blamed the debacle on “a cut-and-paste error that we didn’t detect when we did our final sorting and ranking of bids prior to submission.”

So how much has changed? Even six years on, a 2009 CommodityPoint survey sponsored by software vendor Triple Point Technology of energy and commodity executives discovered that 70 per cent of companies were using spreadsheets or internally assembled systems to manage counterparty credit risk. At the same time, 60 per cent of the companies surveyed felt the need to upgrade their credit risk management systems to effectively manage counterparty risk in the current business environment.

Spreadsheets alone are not the answer, but they still have a part to play

In recent years, the energy trading industry has been characterised by the emergence of new markets, higher trading volumes and more stringent risk and regulatory reporting requirements, especially in the wake of recent events in financial markets. As the industry has matured, it has become clear that spreadsheets alone are neither scalable nor robust enough to cope with ever-increasing trading volumes and middle and back-office requirements.

However, spreadsheets continue to form a necessary and useful part of the energy trading landscape, and there are many perfectly legitimate reasons behind energy traders’ traditional enthusiasm for relying on spreadsheet technology to manage their trading and risk activities. Traders have built up trust in their own calculations and models, and are therefore reluctant to move to unfamiliar software, which they often perceive as complex, inflexible and unintuitive.

With budgets under closer scrutiny, spreadsheets can be an attractive low-cost option

With IT budgets being squeezed at many firms since the downturn, the low costs involved in using spreadsheets can be a decisive factor. Most traders already have spreadsheet packages on their PCs, which can sidestep the bigger upfront IT costs associated with the purchase of more scalable automated software tools.

When it comes to prototyping new pricing models and algorithms that may later be incorporated into other systems, spreadsheets can create an ideal environment. They are highly customisable and have the ability to link to algorithms and incorporate add-in functions such as option valuation models and other analytics tools.

But just as there are situations where spreadsheets, if properly managed, can be a perfectly acceptable short-term solution, there are also clear-cut situations where relying on them can be downright dangerous. Business-critical data should never be mastered in a spreadsheet, particularly if it is accessed by numerous people and departments at the same time. As soon as multiple users start to access the same spreadsheets, issues of version control and auditability can rear their ugly head, with errors representing a significant operational risk.

Simple mistakes can be replicated many times over

It can be a time-consuming task to find out where mistakes have occurred in a complex, embedded spreadsheet, and when one spreadsheet is linked to another, a small initial mistake can be replicated many times over. And for a solution that is commonly seen as low-cost, when errors start to creep into spreadsheets, the cost of unpicking mistakes without a clear audit trail can soon outweigh the initial savings.

As trading volumes increase, spreadsheet are feeling the pressure

Although spreadsheets may be a good solution for small trading volumes, they struggle to cope with the continual growth of trade volumes and types. Processing and calculation times will be increasingly slow as the amount of transactions and market data involved multiplies. This is especially true when calculating computationally intensive risk measures such as value at risk. Spreadsheets simply do not have the ability to capture and process the huge amount of data associated with large portfolios.

Taking a commonsense approach

With the credit crunch throwing the potential pitfalls of over-reliance on spreadsheets into sharper relief, a growing number of firms are focusing on aligning their business processes and systems with this new risk-conscious mentality. But companies shouldn’t let themselves be infected with the hysteria that can surround spreadsheets by eradicating them across the board. Rather than demonising spreadsheets, businesses need to be clear on when it’s right and when it’s wrong to use them.  

Stuart Cook, Senior Manager, Baringa Partners