Cell mates

Traders love spreadsheets. But complex deals can quickly outgrow a sheet developed on the fly. Since traders won’t abandon their favourite tools, Stuart Cook and Tony Hughes of The Structure Group look at how firms can control their use

While developments such as straight-through processing (STP) and real-time transactions can reduce human interference and error, human risk can never be eliminated. The issue for a trading party, then, becomes how much benefit is added in moving elements of the control of its portfolio away from spreadsheets and realising the consequent reduction in human risk.

Many of today’s traders live and die by their spreadsheets, which they use for analytics, deal-modelling and position-tracking activities. It is, therefore, unrealistic to think trading houses will ever stop using spreadsheets completely – hence, we don’t want to challenge this scenario, but rather discuss why it is so and how organisations can make the most of it.

Energy trading organisations have quickly outgrown spreadsheets as a means of having complete control over their portfolio. These programs are neither scalable nor robust enough to cope with ever-increasing trading volumes and middle- and back-office requirements. However, reluctance to move away from spreadsheets for certain activities has been one of the biggest challenges facing the industry over the past few years.

Spreadsheet attraction...
So why do traders like using spreadsheets so much? Spreadsheet development generally sits on the trader’s desktop rather than in the company’s IT department. IT practitioners generally support this distribution of the development capability – it is one of the main reasons why progress away from spreadsheets has been given a low priority by organisations. There are a number of reasons why firms have so embraced the technology of spreadsheets for their trading and risk management activities. Contributing factors include:

• Familiarity – traders are familiar with spreadsheets and understand how they work, often creating them to meet their own needs. Traders are, therefore, reluctant to move to unfamiliar black-box software, which they often see as inflexible and time-consuming to come to grips with. They have accumulated trust in their own spreadsheet calculations and models – a trust they would need to rebuild with a new system.
  
• Flexibility – spreadsheets have flexibility that often cannot be offered in many off-the-shelf trading and risk management (TRM) packages. They are highly customisable and can link to algorithms and incorporate add-in functions such as option-valuation models and other analytical tools. Traders also see spreadsheets as being easy to maintain – which is generally true, provided multiple users are not accessing and working on the same set of data.
  
• Speed of implementation – with the aid of spreadsheets, traders are up and running literally instantly, capturing trades, analysing positions and producing risk reports. With small trade volumes, spreadsheets can be a quick and efficient method for trade capture and analysis.
  
• Cost – most traders already have spreadsheet packages on their PCs, which means there are no big up-front IT costs such as those associated with the purchase/build/implementation of more scalable tools.

Why change?
So why try to change? There are a number of dangers and operational risks associated with an over-dependence on spreadsheets. For example, it is reported that John Rusnak, the rogue trader at Allied Irish Banks subsidiary Allfirst Bank, was able to breach spreadsheet security and a weak control environment. Allegedly, Rusnak was able to manipulate data in a spreadsheet that directly fed into the value-at-risk (Var) model used by the risk control group. A key spreadsheet containing foreign exchange rates was also reported to be corrupt and, due to a lack of control, open to manipulation by the traders.

Development and control
The benefits of STP may not be fully realised while a company’s technical infrastructure consists of many disparate spreadsheets with no central repository. The effect of such a scenario is that middle- and back-office processes will be increasingly manual, which can be both time-consuming and prone to human error. It could be argued that the middle and back office benefit the most from the removal of spreadsheets from core trade capture processes. Firms can significantly streamline the process of settling and accounting for trades by using STP.

And on top of the process issues raised through not employing an STP regime, the time taken to find mistakes due to human errors dramatically increases. The ad hoc nature of spreadsheet development means business-critical spreadsheets often contain mistakes. Because the creation and development of spreadsheets is often done in a non-controlled environment, there is a lack of structured testing, which can have an adverse affect on the quality of the data output. And it can be time-consuming finding out where mistakes have occurred in a complex, embedded spreadsheet. A simple mistake in a formula or in the unit of currency can have disastrous consequences.

Further problems arise when the person who developed an individual spreadsheet is not available to provide support. As traders develop their spreadsheets using macros, complex formulas, add-in functions and embedded spreadsheets, the software quickly becomes cumbersome and difficult to handle for anyone not familiar with their construction. Ad hoc development is also often poorly documented, making it difficult to transfer the spreadsheet between personnel. This is highlighted particularly when the owner of the core spreadsheet is away from work.

Scalability and administration
What is initially intended to be a relatively small spreadsheet can easily grow exponentially into a very large number of bespoke or embedded spreadsheets. It is, therefore, not uncommon to have in excess of 200 spreadsheets – sometimes including multiple ‘chained’ spreadsheets – forming an organisation’s TRM solution architecture. In addition to the issues of control we have already looked at, such expansion can lead to problems with the management and maintenance of transaction and market data.

Although spreadsheets may be viable for small trading volumes, they do not have the capability to deal with the continual growth of trade volumes and types. Processing and calculation times will be increasingly slow, as the amount of transaction and market data involved multiplies. This is particularly true for the calculation of computationally intensive risk measures such as Var. Spreadsheets simply do not have the ability to capture and process the huge amount of data associated with big portfolios. Hence, system performance and stability need to be carefully monitored.

Finally, the lack of a complete audit trail from a spreadsheet solution makes it difficult to identify the actions taken by specific individuals.

Possible solutions
It is important to recognise that the decision to use spreadsheets or a TRM package does not have to be binary. A hybrid solution can, in some cases, offer a better solution.

There are areas where the flexibility of spreadsheets offer definite competitive advantages over other technologies, such as in peripheral activities, where data is taken from a central repository and output to a spreadsheet for analysis. This mechanism provides a very safe and reliable medium for performing trade ‘what if’ analysis, data manipulation, and option modelling.

Spreadsheets also provide an ideal environment for prototyping new pricing models and algorithms that may later be incorporated into other systems. This is often necessary, because new models and leading edge technology can often be used for spreadsheets months before it has been incorporated into other systems.

“We use spreadsheets as a convenient addition to our core trading and risk management system,” says Daniel Dahlin, a senior trader at Swedish firm Vattenfall Supply & Trading. “They allow us to easily supplement our core analytics with proprietary models and scenario analysis.”

Small trade volumes make the use of spreadsheets more feasible as the core trade-capture system. However, the decision to proceed on this basis should be accompanied by the implementation of a rigorous configuration management procedure for any new spreadsheet development, so that the proper controls for security, accuracy and audit can be applied. To carry out such implementation would mean full documentation, testing and rollout of new spreadsheet functionality. In practice, this is difficult to achieve, and scalability issues remain. Hence, organisations at this stage of development are usually conscious that a decision to increase portfolio size and/or trade volume above a particular limit may be accompanied by a review of the current technical architecture.

The introduction of a more robust technical architecture for certain activities can help realise a number of benefits and overcome the problems we have discussed. Certain functionality could also be transferred from client machines onto a central server, enabling it to be used by all parts of the organisation. This would reduce the trader’s ability to manipulate trade data – thus improving system security – as well as safe guarding against the duplication of effort in spreadsheet creation.
A gradual shift

For the reasons we have discussed, trading houses are increasingly moving data from spreadsheets to core TRM systems. Companies should adopt a ‘phased’ approach of gradually transferring the core trading data from spreadsheets into existing applications, to allow confidence to grow. The business may already have a TRM system that it is not taking full advantage of or whose models the traders do not fully trust. Part of the transferral exercise would, therefore, involve testing these models and building trader confidence. Another activity may be to break down the firm’s structured deals so that they can be accurately modelled within the system.

Whatever solution is chosen, care should be taken to ensure the organisation can export all trade and price data to spreadsheets. This will help maintain the inherent benefits of spreadsheets, while also overcoming many of their weaknesses.