Spreadsheets - a Tale of Terror

 

Spreadsheet, a word that elicits different emotional responses, depending on a person's background, career path or even world view. The latter might seem an odd reference, but we'll come back to that one at some point. These days, most people's first and often only exposure to them will have been at school in the IT class. But for my generation, anyone not working in accounts or some sort of office or technical or scientific environment will probably be unaware of their existence.

Suffice to say I'm old enough to have used the very first spreadsheet application ever written; VisiCalc, at the time of its release in 1979. I have used spreadsheets for a number of purposes and in a number of contexts over the intervening forty-one years. They are an extremely useful and indeed valuable tool in the right hands and in the right context. The basic concept of the original was simple; take an accountant's ledgers or any other tabulated form of data that requires calculations to take place across the table and make them electronic. Indeed, in the relatively constrained world of a ledger or single table, spreadsheets are highly effective ways of presenting and manipulating data. A good example is the good old cashflow forecast, beloved of banks as a means of irritating the bejeezus out of any small business setting up a business account with any form of borrowing. In the right place and time, though the cashflow forecast is a useful tool for trying out various financial scenarios for anything from a small manufacturing business to someone's domestic finances.

In these contexts the spreadsheet works. There is a problem with the basic architecture of the spreadsheet, however, which has caused financial havoc throughout the world in the decades of its use. So much so, Europe even has a dedicated Special Interest Group dedicated to this very issue alone: The European Spreadsheets Risks Interest Group; a friend and former colleague of mine having been a member of the group. The principal architectural issue with spreadsheets is that they are essentially dumb, arithmetically. This might come as a bit of a facer to someone who relies on them to run their business, but it's a fact. A spreadsheet cannot tell the difference between good data and bad data, and because they operate in a strictly formulaic way, ie data is subjected to some operation and a result is passed, usually to another cell of the sheet (if you don't know about the way spreadsheets are used or what they're used for - Google...). 

The issue is that it is very easy to produce errors that go undetected; the software can only report basic category errors, such as putting text in a cell where numbers are expected, and so on. It can't find or report errors in either the arithmetic or the logic employed in the sheet. These errors are usually incorporated by mistake by the human programming the sheet, but can be introduced deliberately to fraudulent ends. On a small, domestic-sized spreadsheet, the calculations can be easily verified manually on test data before the sheet is rolled out for daily use. Where you have industrial grade spreadsheets, consisting of hundreds of thousands of active cells spread over hundreds or thousands of individual sheets, linked by scripts, pivot-tables and God knows what else, one single error in setting up the behemoth effectively undermines the whole enterprise. The knock-on effects of these errors can bring down banks, institutions and even whole economies. To take one quote among very many from the European Group: 

'"Complex financial instruments such as CDO's are implemented within, and valued by, large and complex spreadsheets. CDO's and other credit derivatives played a leading role in the collapse of the global financial system" - Grenville Croll'

It don't get much bigger a problem than that, folks. In 2013 it was estimated that 88% of all spreadsheets had errors in them, a whopping 50% of those in use by large businesses similarly afflicted. This amounts to billions of dollars lost just in everyday use across the world.

On a more contemporary and domestic level, The Guardian reported this week on the spreadsheet errors that led to 16,000 Covid test results going AWOL, due to Excel's million-row limit. This says everything you need to know about spreadsheets when applied to data-mining. They're stupid and not to be trusted, certainly not with peoples' lives.