Gary Barnett's Blog

Oh yes my friends, the real culprit behind the recession is the spread-sheeting application  that drives a very significant proportion of the world’s major financial transactions. Well, strictly speaking, spreadsheets don’t kill economies, people kill economies – so it’s fairer (but less attention grabbing) to say “The mis-use of Excel caused the recession”.

If you’re in a rush:

• “Excel madness” is a syndome that occurs when people blindly believe “X” simply because “X” shows up in a certain cell in a certain spreadsheet – Of course… it’s not just Excel – “calc-madness” and “123-madness” are also known conditions
• Many key decisions (many of which have a profound effect on our lives) are made on the basis of data that is simply garbage.
• The mis-use of spreadsheets represents the second most terrifying threat to civilisation after global warming
• Be super cautious about forecasts that contain numbers that appear accurate to three decimal places
• Spreadsheets make really poor databases
• Feral data creates chaos
• We need to establish very clear guidelines about the gathering, use and application of “data”
• Maybe it should simply be illegal to create a spreadsheet with more than 100 * 100 cells?

This is all very, very, obvious (or at least it should be)

I’ve nearly finished writing this, and have had to pop back to the top to add this disclaimer  – because this is pretty much a “stating the obvious” post – (and if this doesn’t ring bells with you, I doubt any kind of explanation will ever help you) – But I reckon it’s still worth posting, because the problem is certainly no smaller even if a lot of us know of its existence.

The background

This post was inspired by a conversation I had recently with a junior financial analyst at a major investment bank. It was an informal conversation – so the identities of both the analyst and the bank will remain secret. Besides – the story I was told is, I strongly suspect, one that is true of most banks.

This analyst had just completed an incredible work-cycle (10 days of working 18 hours/day) to complete an “analysis” for one of the senior bods at the bank. The project started with this analyst being given a zip file containing half a gigabyte of spreadsheets – Over 100 of them. These spreadsheets contain a vast quantity of data, they are all linked – some of them to many others. A single re-calc takes hours. The spreadsheets contain macros, formulas, and all sorts of hocus-pocus that render the output completely and utterly opaque – even to the Oxbridge double-first toting young analyst charged with creating a report.

It scared the living be-jayzus out of me…

Excel-madness

We’ve all seen this – that faintly crazed look in a colleague’s eyes when they’re challenged on a point of data – You can see that they just want to shout “The number is 54.56% because the @$%$ spreadsheet says so!”. Who the hell are you to challenge the contents of cell 4987MP, What sort of messed up anarchist would challenge 4987MP?

If you look closer – into that person’s eyes – you will see their hidden desire to stab you in yours with their biro.

And let’s face it – who the hell are you to challenge  this – Did you spend 110 hours over the last 7 days rushing to produce this analysis for the meeting? Did you grapple with the two dozen spreadsheets that have been linked and interlinked in order to get to this number?

This number is the truth, because the spreadsheet (which as the dweebs amongst you will have noted is OpenOffice Calc) says it is.

As John Mihalec tweeted to me in response to my tweet about writing this blog:

@thinkovation Because 2 + 2 is so obviously 4 that it lulls us into complacency re whether either 2 is even 2 at all.

Many key decisions (many of which have a profound effect on our lives) are made on the basis of data that is simply garbage

Computer Science 101 taught us “Garbage in, Garbage out” – and we’ve been collecting, polishing and re-packaging garbage ever since. But this stuff is different – Our retirement funds, savings, economic stability, even our understanding of climate change all depend on knowing the right things.

The financial crisis was caused by many many things – and I’m not discounting either “greed” or “stupidity” as major causal factors – but the absolutely tippy-top of the list cause of the crisis was the failure of pretty much everyone (except Warren Buffet and a small number of others) to appreciate the level of risk that was associated with all of the various financial instruments that were flying about.

The reason for that failure to understand the true level of risk lies in the way in which both the instruments themselves, and the tools people used to assess their risk, wrapped and wrapped the risk under layers and layers of complexity – It was a giant game of pass the parcel – with the outer wrappings  so numerous and shiny and neat,that the smell from the final parcel of dog do0-do0 was completely overlooked.

If you allow something to become en-mired in many layers of obfuscation, you have to accept that the “system” you create is going to become increasingly chaotic. If you can’t track the journey taken by a simple number through the myriad sections, tabs and linked files – You have to be prepared to factor in “chaos”.

The image below is hypothetical – but it’s not an exaggeration – there really are figures sloshing around that are derived from inter-linked hierarchies of spreadsheets that are a lot more complex than this one.

Take this image as an example. Item A is the output spreadsheet – which combines the results from B, C and D – which each in turn depend on one or more “child” spreadsheets. Here are some boring questions one might ask -

• How long ago was the data in J refreshed?
• Has anyone audited the assumptions made in H?
• Is there anyone in the organisation who could explain to an Actuary how come the number is 54.56%?

If you can’t provide sensible answers to these questions – then, it’s time to take your life in your hands and tell your excel-crazed, sleep deprived colleague that they may as well have arrived at that number using a lab-rat and a roulette wheel.

Incidentally – someone has trained rats to trade, and reckons his rodents can do at least as well as the majority of the top fund managers – check it out here

The mis-use of spreadsheets represents the second most terrifying threat to civilisation after global warming

I really believe this. But to put it a little less alarmingly – what percentage of global GDP is wasted because of bad decisions arising from rubbish information. Well, I’ve done the analysis – and the answer is 15.596%.

I did this analysis by spitting olive stones across my patio – and they seemed to form the shape of 15.596 – more or less. And I will defend my analysis against anybody, any time.

Be super cautious about forecasts that contain numbers that appear accurate to three decimal places

This one really ticks me off in a big big way. Most of us have had to sit and listen to some starry eyed “analyst” stand up and pronounces that the market for Open Source Thingamies will be worth $175.89 million in 2011. Perhaps you think “typical lazy-assed analyst, can’t be bothered to take it down to individual cents, or perhaps you’re like me and you’re wondering whether the analyst is a) Too stupid to understand that his market forecast cannot possibly be that accurate or b) So arrogant that they think I’m stupid enough to be impressed by this semblance of precision. In either case – the analyst has 30 seconds in which to say “Of course – this isn’t this precise” or I’m cancelling my subscription.

Incidentally, some time ago I started rounding my OSS market forecasts to the nearest 100 or 50 million, when I present the numbers I tell the audience the truth – Which is that this is my best guess, based on my experience of the market, and a scenario that I’ve created. I’m always happy to talk to clients about the scenario – and I’ve even given them the excel once or twice so they can see the impact of changes to my assuptions.

Above there’s an imaginary market forecast – obviously this one is immediately suspect – the line is far too staight – it’s based on a CAGR of 5%. Seasoned forecasters will pounce on this and either a) Cynically modify the figures so that the growth appears more natural or picturesque or b) Carefully apply a scenario that will show how the progression won’t be a straight line.

Actually – In many cases both action a) and b) are identical  – with “A” being  the more honest IMHO.

So now I’ve whined about the issues… let’s start moving towards resolution..

Spreadsheets make really poor databases

Databases are the best place to store data. In the olden days, when the data had to be snake-charmed out of the mainframe, we had an excuse for storing data in spreadsheets. We don’t have that excuse any more.

Of course – one reason for taking a copy of the data and stuffing it into a spreadsheet is that the pesky “real data” keeps changing – and there’s nothing as tedious as having your risk assessment messed up by changes to the underlying data – huh?

Feral data creates chaos

Feral data is data that was once domesticated, but which is now “in the wild” – Accurate data that came out of our ERP system, that has now been mooching around sitting in various executives’ laptops. It breeds, it evolves – and it becomes less and less like the original data, and even less like the “current” data that is gazing mournfully through the bars of the ERP cage it’s kept in.

When talking to groups of business people, I often ask for a show of hands – of people who admit to making major decisions on the basis of feral data.

You know it’s the majority – pretty much every time I ask.

We need to establish very clear guidelines about the gathering, use and application of “data”

We all know this to be true – so can we get off our asses and do something about it already?

This is particularly true for regulators – especially in the FS sector… if banks can’t prove the following things, they should have their licenses revoked – it is as simple as that…

• We know the source, age, accuracy and volatility of all of the data that drives our investment decisions
• We maintain version control and configuration managment of all of the models we use to calculate risk
• We run a complete audit of our executives laptops / fileservers and email accounts for feral data every three months

Maybe it should simply be illegal to create a spreadsheet with more than 100 * 100 cells?

No… really!

You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.