Excel is the hammer for too many businesses’ nails (original) (raw)

Excel is a fine piece of software, but as Public Health England found out, its ubiquity and ease of use does not make it the ideal tool for every job that involves data

Public Health England (PHE) has just given us another reminder of how Excel is the most dangerous software in the world. Not, I hasten to add, because there is anything particularly wrong with it, but rather because so many people use it, that something is simply bound to go wrong.

This time around it was, apparently, falling foul of the xls format 65,535 rows limitation, which makes about as much sense to me, a writer and thus word person, as it did to PHE. The effect being those 16,000 unreported Covid-19 cases, and therefore the failure of any track-and-trace operations upon those people.

Quite why a system costing us taxpayers £12bn is using Excel is just one of those things, really. To manage a database of results, perhaps a database could have been used, but again I’m outside the field looking in, not an expert.

The point I would make, though, is that errors like this are not rare – or not rare enough, at least. From the financial markets, we have the tale of the London Whale. This was JP Morgan playing the Big I Am with tens of billions of dollars. Unfortunately, they were modelling their position using a series of Excel spreadsheets.

Not even linked, the macros, the equations were being cut and pasted over from one to t’other. Not all of them made it accurately, which was a problem. For if what is being played is the price of risk, then your connections between the amount and the price of risk do need to be correct. Some $6bn was lost on this in the end. Which, even for a bank of that size, is real money.

It is also possible to enjoy the EuSpRiG list of disasters – errors, perhaps, some of them – caused by the inappropriate use of spreadsheets.

Appropriate technology

This is, believe it or not, a symptom of the same complaint that many development experts have about poor places. That we should use appropriate technology for the case at hand. The specific examples here are of the importation of some higher technology that doesn’t have the necessary support mechanisms – say, diesel-run irrigation pumps in a place that doesn’t have a secure supply of either the fuel or the spare parts.

Perhaps the screw that Archimedes worked out 2,500 years ago is a better solution in such places? Or, another example, that integrated steel plant that every ex-colonial nation seemed to think was essential – in places without ore, coal or limestone – or the demand for that much metal, either.

People are simply using the wrong technology for the task at hand – that’s the similarity here. But that’s what makes diesel pumps dangerous in the same way that Excel is. And it’s that second that is the serious risk, simply because of how widespread the installation base is.

You IT experts will know better than I what the correct solution to PHE’s problem was. Something in Python, perhaps, or at least the use of VBA. Possibly even a proper database, rather than a spreadsheet. But everyone has the spreadsheet on their corporate machine – surely that means that this is what should be used? Well, no, that is to say that if everyone has a hammer, then everything is a nail, which isn’t how to run matters. It is how to bodge them, but not how to run them.

A spreadsheet, whether Visicalc, Lotus or Excel, allows something that was never really possible before about the starting date of 1980 – significant exploration of variances in numerical models. It is no exaggeration to say that modern financial markets simply wouldn’t be possible without the ability to do that. Depending on your view of how those markets work out, this might be a good or a bad thing.

But what couldn’t be done before, not with ease, was to make a model, then run it, change one or other variable – or the relationship between them – then run it again. And again, and so explore the entire modelling space to see what might happen. Used for what they are good at, spreadsheets are a marvellous innovation.

Alternative pathways

There is space in complex modelling like JP Morgan’s – which is to explore what might happen if... And then, once the relationships have been worked out, to encode into something more serious for the actual operating calculation of the markets and or the position. Equally, the use of Excel to think about how to tot up testing results by PHE is just fine. That’s what the innovation is for, to be able to test “what ifs” and alternative pathways. But once the working out has been done, the production system needs to be – as those 16,000 reasons tell us – encoded into something more robust.

Which is where our similarity with Archimedes comes in – that appropriate technology. A spreadsheet is, or at least should be, a prototyping tool. That the vast series of calculations that accompany the exploration of the possibility space are more or less automated now allows the exploration of more spaces in more detail. This is excellent; it’s an advance.

But that’s what the tech is appropriate for. Once the probabilities have been explored, it is time to put away the exploratory tool and adopt the production one. As with the geologist hacking at rocks with a pick and hammer to see what they are, but the miner upgrades to a shovel to actually move them.

That is, good computing doesn’t allow anyone to use Excel to actually do anything, even though it’s an excellent tool to find out what to do. This is equally true about good computing from the technical side of specifying it, or the management side of agreeing what to pay for to do it.

Yes, it is entirely true that everyone already has the spreadsheet, the Excel. But this is the same thinking as the man who only has a hammer – the difficulty being that there really are things that are better screwed than nailed.

Read more on Database software