Monday, 1 November 2010

The Trouble with Spreadsheets

I don't like you Iceman...

There's probably a guy on your floor who has a mug with the logo "I ♥ Spreadsheets" or something similar. Or you know a guy (it's normally a guy) who refers to himself as a spreadsheet jockey. Or worse, a spreadsheet guru.

Let me tell you something: these people are dangerous.

To be precise, spreadsheets are dangerous. Maybe not dangerous like salmonella or even dangerous like Iceman, but dangerous to your organisation.

Most actuaries will react in one of two ways to this statement:

  1. "Our company couldn't run without spreadsheets" (Disbelief)
  2. "He's right, but what are you going to do? Our company couldn't run without spreadsheets" (Resignment)
  3. "Iceman? Who is Iceman?" (OK, that's three, but the last one doesn't count)

Finance, IT and Excel

The problems with spreadsheets are intimately connected to their utility and ubiquity in the finance industry. Almost every number reported in company accounts, or presented for management information, passes through a spreadsheet at some point. Sometimes they go through nothing else (except maybe Powerpoint or Adobe Acrobat). And the vast majority of those spreadsheets are created by non-specialists, by which I mean non-IT specialists.

Professional programmers, or those who are any good at their jobs, learn to design and implement calculations and processes in such a way as to avoid cock ups. Harsh experience drills Murphy's law into them: anything that can go wrong, will go wrong. Spreadsheets, as they are used today by most financial firms, are free-form programming tools which actively encourage many poor practices that professional programmers try to avoid.

In a big software system controlling air traffic or credit card transactions, something going wrong can be catastrophic. In a system generating risk, valuation or position reports the effect is likely to be more subtle. Mis-stated numbers, errors in underwriting, bleeding P&L. But precisely because these errors are not immediately disastrous, they can be hard to notice and correct.

In this link we tabulate some examples of bad programming which are actively encouraged by spreadsheet packages. The key problems are that spreadsheets are difficult to version control, nearly impossible to verify, and have poor re-useability.

And things get worse when you start having a look at the macros contained in some sheets.

Now true, most of these issues can be overcome. Microsoft's Sharepoint product can function as a rudimentary version control system. Training and strictly enforced guidelines can minimise the chance of casual error. A central library can offer templates. We are aware of at least one global investment bank which runs all of its derivatives trading books off spreadsheets. But their system involves a vast, database-centered infrastructure and additional behind-the-scenes code, implemented and managed by a talented and very well paid IT function. Spreadsheet based systems are not impossible to do well, but to do so requires a lot more resource than the average insurance company or bank has invested until now.

Solvency II and some potential solutions

These issues are now particularly topical with the immanency of Solvency II. Solvency II puts data and processes at the heart of governance issues, requiring processes to be Robust, Reliable, Auditable and Repeatable, and other Good Things. For the reasons explored above, any system built on spreadsheets is unlikely to meet these criteria.

So what is the solution, or what are the alternatives?

We prescribe a range of measures. First of all, recognise that your organisation may have a problem. Do a quick audit of all the spreadsheets in your department. Check who can change them, who created them, who maintains them, how information gets into them and where the information goes next. You may be surprised.

Next, encourage a change in mindset: processes should be planned from front to end before being implemented. Careful planning early on can save man time and money later.

Then realise that the line between actuarial / financial work and IT has become very blurred. Your organisation or department either needs to develop its own skills in this grey area internally, or hire them in from outside.

Finally, when it comes to actual implementation you have a wealth of choice. The proper place for data is in a real database, where it can be controlled and audited. SQL Server is much beloved of IT people who get taken out for lunch by Microsoft salespeople. On the other hand MySql and PostgreSQL are open source and will endear you to the geeks.

The proper place for calculation is inside compiled, tested and version controlled code. The .NET framework has made programming well surprisingly easy. The VBA macro guru in your team might jump at the chance to program in a proper development environment. For number crunching you will need an add-on library like NAG or a the GSL, or a dedicated numerics / statistics package like Matlab or SAS. At a life insurer you are probably already using Prophet, Moses or Mo.NET for actuarial calculations - these systems should be embedded in a wider infrastructure relying on databases and software to talk to each other. If at any point an actuarial student is required to copy out of one spreadsheet and paste into another, you have a problem.

Your ultimate goal: when a number reaches your CFO or the Regulator, you should be able to say with absolute confidence that the number is correct, that you know where it came from, and that if you should need to produce it again, you can do so at the click of a button.

No comments:

Post a Comment