Quality Money Management : Process Engineering and Best Practices for Systematic Trading and Investment

111

10.3. Spreadsheet Modeling

According to the European Spreadsheet Risk Interest Group (EuSpRIG):

Research has repeatedly shown that an alarming proportion of corporate spread-

sheet models are not tested to the extent necessary to support Directors ’ fidu ciary,

reporting and compliance obligations. Uncontrolled and untested spreadsheet

models therefore pose significant business risks. These risks include: lost revenue

& profits, mispricing and poor decision making due to prevalent but undetected

errors, fraud due to malicious tampering [and] difficulties in demonstrating fidu-

ciary and regulatory compliance. These risks are ignored due to a widespread fail-

ure to inventory (keep records of), test, document, backup, archive and control the

legions of spreadsheets that support critical corporate infrastructure.^9

In some ways, Microsoft ’ s Excel spreadsheet is the crack cocaine of financial

markets—it is cheap, easy to obtain, and creates the illusion of speed. The many financial

modeling books have gotten traders and financial engineers hooked by teaching the con-

struction of hybrid Excel and VBA applications with essentially no consideration of plan-

ning or software design.

A Canadian company took a $24 million charge to earnings after a spreadsheet that contained mis-

matched bids caused them to buy more U.S. power transmission hedging contracts than they needed and

at higher prices than they wanted to pay. 10

Too often graduate-level courses in financial engineering focus solely on algorithm

development and not on the process of building robust trading/investment or risk manage-

ment systems. Students design systems in Excel that should otherwise be properly imple-

mented as object-oriented applications using C# or C.

Nevertheless, Excel will never go away. Spreadsheets are more pervasive than ever

due to the increasing pace of change. Excel has a well-developed network of suppliers that

provide myriad add-ins, and Excel projects are understandably additive; traders and finan-

cial engineers can quickly add new spreadsheets and new calculations to existing systems.

But Excel ’ s place should more often be as a prototyping tool, not an implementation

tool, especially when constructing real-time trading/investment systems. Using Excel to

prototype software implies a subsequent conversion to programming code, which often

becomes a reverse engineering project by programmers that may or may not understand

the spreadsheet ’ s underlying context—the financial mathematics or trading strategy.

Excel is a programming environment where the idea-makers, rarely versed in software

development and testing processes, are the developers. “ The ranks of ‘ sorcerer ’ s appren-

tice ’ user-programmers will also swell rapidly, giving many who have little training or

expertise in how to avoid or detect high-risk defects tremendous power to create high-risk

defects. ”^11 The problem is software development methodologies are rigorous and difficult

to implement.

Spreadsheets are an excellent platform for creating prototype models because, among

other reasons, spreadsheets allow the user to easily manipulate data, reducing the time

it takes to set up and modify models. Furthermore, spreadsheets include efficient graph-

ing and good report generating functionalities.^12 However, Grossman and Ozluk point

out that “ spreadsheet programming currently resembles a bag of tricks rather than a

well-organized, coherent toolbox. ”^13 Through experience and hard knocks, individual

10.3. SPREADSHEET MODELING