Spreadsheets are vital tools for traders and banks’ back-office staff but their flexibility can weaken governance. Dan Barnes looks at the problems and the potential solutions.
Given freedom, people make their own rules. In a trading environment this could mean big trouble. For years, traders have had the freedom to use spreadsheets as they wish: to build their own models, to record and track data, and as a substitute for workflow software. But spreadsheets were not designed for any of these purposes and their proliferation into these roles is exposing potentially fatal weaknesses in governance.
Ray Panko, professor of information technology management at the University of Hawaii, has been addressing the role of spreadsheets in business for many years and notes that failure to implement the right controls can expose a bank to significant risks. “John Rusnak was able to lose $691m at Allfirst by manipulating his trading spreadsheet,” he says.
“Doing paranoid testing on trading spreadsheets is critical and, after the testing is done, the spreadsheet’s logic should be locked and made unchangeable by the trader.”
Mr Rusnak had convinced back-office staff to use data from his own machine to calculate his value-at-risk position while engaging in fraudulent trading activity. Allied Irish Bank sold its subsidiary shortly after this loss.
Compliance guidance
In 2004, consultancy PricewaterhouseCoopers (PwC) issued a white paper offering guidance on spreadsheet compliance for Sarbanes-Oxley, listing five steps to take when “evaluating spreadsheet controls”. The first recommendation was to “inventory spreadsheets” – an obvious but not necessarily simple task, says Richard Gissing, chief technology officer at Gissing Software. “At one client, we carried out an audit. In total, they had three million spreadsheets – some were copies, some were unused – but there was no cataloguing; they had no idea of how many they had. It was viewed as a user tool and people just created their own,” he says.
The situation he highlights is an extreme one, however the regulatory burden is taking its toll across the board of financial services companies that are regulated in the US, says Steve Semenzato, CEO of systems provider ClusterSeven. “The level of certainty required for compliance has had a real effect on business. You will see banks getting rid of 5000 spreadsheets and relying on just 200 with auditing and maintenance having become very manual functions,” he says.
“Now that monitoring is down to the cell level, it is as though you have a CCTV on every action,” he says. The process also requires a huge amount of data to be stored.
One Swiss bank told The Banker that its traders are not allowed to use Microsoft Excel, the dominant spreadsheet software, any more and must use alternative systems. However, former IT chief operations officer at Barclays Capital, PJ Di Giammarino, says that this will not be the solution for everyone. “Some banks will not have the resources to do that and the problem is about legacy spreadsheets – there could be hundreds of thousands to migrate.”
By taking a strong line on how traders can work, a financial services institution could limit innovation but with the reward of improved security against either error or fraud – although the decision these days is often not the bank’s but the regulator’s. In the US, Sarbanes-Oxley compliance has taken a firm hold of the industry, driving many towards restrictive work practices to ensure compliance rather than risk the wrath of the Securities Exchanges Commission (SEC).
Change of behaviour
Rohan Douglas, CEO of Quantifi, a supplier of models and software to the credit markets, says he has seen a notable shift in behaviour. “A number of US organisations have moved away from individuals controlling numerous spreadsheets to centralised spreadsheets that are controlled by a few people centrally. Essentially, this is being done for the purposes of compliance,” he says.
Mr Panko asserts that, deliberate fraud aside, the chief risk of spreadsheets is not their creation (because people generally build 95%-98% of formulas correctly) but detecting error. “The bad news is that people are really bad at detecting errors,” he says. “Testing can reduce error rates to about 0.1% to 0.5% but this will require spending 25%-40% of each spreadsheet project’s time on testing.
“Unfortunately, few companies are doing any testing beyond ‘eyeballing’ numbers for reasonableness and spot checking a few formulas. People completely underestimate how hard it is to reduce errors and tend to have great confidence in ‘whisky’ cures – of all the things that do not cure the common cold, whisky is by far the most popular.”
Trader demands
Colin Knight, chairman of IPC, delivers the hardware side of the trader’s desk but gives a picture of the demands placed upon this unique workstation: “What we design must be modular to fit around the needs of different types of trader. At any desk, you must give the capacity for trading various products and so, as well as tying various tools such as screens and phones into one place, these have to be easily replaced with the appropriate tools for the user.”
This flexibility is important for the software, as well. There are positive reasons for utilising a generic tool such as Excel for specific roles, says Dan Bricklin, co-creator of VisiCalc, which in 1978 was the world’s first electronic spreadsheet. He stresses the impression that a worker makes upon their tools: “When you give a trader a generic but customisable tool, they are essentially able to map out processes and structures that would normally be held in their own heads. This gives you insight into the way they are working without reducing their flexibility to adapt to changing circumstances. They can recustomise that tool as circumstances dictate.”
Microsoft on the case
Microsoft is aware of issues of functionality and is trying to address them. The newest version of Excel, which is due for release in 2007, is intended to include features such as building business intelligence (BI) dashboards, real-time feeding of data and the ability to ‘multi-thread’ processes so that they can take advantage of dual core chips (see ‘Quantum Leap’ in the March edition of The Banker), essentially running a number of tasks simultaneously to increase performance speed.
Michael Worosz, product manager of office enterprise marketing at Microsoft, acknowledges that the real issue with spreadsheets is management-based rather than technology-based, although developments on the next version of Excel will assist in controlling the use of such systems. “For example, using Windows SharePoint Services platform (a method of sharing standard files via a web portal), you will be able to access spreadsheets and run calculations with them but the permissions can prevent you from altering the sheet itself,” he says.
Other advances will improve performance and therefore its value as a tool. “By allowing Excel to be multi-threaded, we can take advantages of new dual core chips and run models like Monte Carlo simulation that are amenable to parallelisation, increasing the processing time,” says Kenny McBride, global industry manager, capital markets and securities at Microsoft.
Microsoft also relies on partners to increase the potential for the application. One of Gissing Software’s products, Gissing RealtimeXL, highlights an issue with the software itself, exploiting Excel’s inability to support real-time data feeds from multiple sources. ClusterSeven provides a solution that stores only the changes to a sheet and allows auditing via a searchable database.
BI provider Panopticon provides a visual representation of data that takes advantage of people’s ability to evaluate size and colour more rapidly than processing numerical information. Although all numerical data can be displayed at the same time, through visualisation of the data the system gives the user a hugely increased reaction time to changing circumstances and makes any extremes in position blindingly obvious. Co-founder of Panopticon, Sam Giertz, claims: “We have demonstrated increases in reaction speed of up to 400% against pure numerical data.”
Risk of redundancy
There is little danger of Microsoft losing significant market share to rivals, even though the regulatory environment will continue to pressure its financial services customers into improving or replacing their current systems with or without the IT giant’s assistance. Should the many features that allow spreadsheets to be customised be restricted for use, they will become redundant. That raises the question of whether more specific tools would be appropriate.
Mr Bricklin does not envisage a market for them yet. “It’s important that the user understands the way in which [the spreadsheet] works so that they can tailor it to perform the task – you can’t build the perfect device for a designated task and expect it to be adaptable,” he says. However, banks must begin to question if their model is currently optimal for both them and their traders. If the model cannot be changed, perhaps the tools will have to be.
