TeodorLazarev/Shutterstock
When the UK prime minister, Boris Johnson, first said the country would develop a £12 billion “world-beating” system for testing and tracing cases of COVID-19, few people probably imagined that it would be based around a £120 generic spreadsheet program. Yet the news that the details of 16,000 positive test cases had been lost because of an error made with Microsoft Excel revealed that was exactly the case.
But perhaps we shouldn’t be that surprised. Spreadsheets are ubiquitous. They can be found in critical operations in practically every industry. They are highly adaptable programming environments that can be applied to many different tasks.
They are also very easy to misuse or make mistakes with. As a result, almost everyone has their own spreadsheet horror stories. And the UK government in particular has a long history of embarrassing errors.
In 2012, the Department for Transport was forced into an embarrassing and costly retraction when it realised its spreadsheet-based financial model for awarding the west coast rail franchise contained flawed assumptions and was inconsistently communicated to bidding companies. The Department had to retract and re-tender the £9 billion, ten-year contract, refunding £40 million to the four bidders. The entire episode was thought to have cost the taxpayer up to £300 million.
In 1999, the government-owned company British Nuclear Fuels Limited admitted that some of its staff had falsified three years’ worth of safety data on fuel it was exporting by copying and pasting reams of data in spreadsheets. Customers immediately ceased trading with BNFL and insisted the UK government take the defective shipments of nuclear fuel back to the UK and pay compensation.
While this was a deliberate act rather than a software error, spreadsheets should not have been the tool of choice in this scenario because they are open to manipulation and error. A bespoke system should have existed for this important safety-critical process.
Andrey Popov/Shutterstock
Perhaps the most significant spreadsheet problem occurred in 2010, when then-chancellor George Osborne based his justification for a decade of public austerity on the conclusions of a research paper, Growth in a Time of Debt. The paper, published by Harvard University economics professors Carmen Reinhardt and Kenneth Rogoff, asserted that a country’s economy will shrink when its debt exceeds 90% of GDP.
But this analysis was flawed. The spreadsheet used to calculate the figures omitted some rows of relevant data from the calculation. In fact, the data showed that an excess of 90% debt to GDP still resulted in positive economic growth. And so the argument for austerity was also flawed.
Yet as a result of this argument, the UK suffered significant cuts to welfare and public services, including health and social care. In 2017, a paper in the British Medical Journal attributed 120,000 excess deaths to these cuts.
Not enough testing
The underlying problem with spreadsheets is how easy it is to use them without applying adequate scrutiny, oversight and validation. They can include many complex customised algorithms but are not typically built by software engineers who are trained to create reliable software.
Instead, research shows, they are often built without sufficient planning, any control process or testing. This results in lurking data integrity problems that, given the right circumstances, can suddenly cause catastrophe.
Such a lack of testing appears to have been exactly the problem with the spreadsheet used by the test and trace system, which lost 16,000 entries when a file was imported to an older version of the software that could hold less data. Clearly the importing of data was not tested properly, and a bug went unnoticed which eventually caused a catastrophic failure of the system in operation. If the importing of data had been tested, it would have been noticed and corrected.
Unless we wish to keep repeating the same mistakes with spreadsheets, government needs to take this software more seriously. It needs to apply the same engineering controls that it would if developing in a formal language and prevent the inappropriate use of spreadsheets.
This isn’t the first time government spreadsheet misuse has been connected with life or death decisions. And unless things change, it probably won’t be the last.
Simon Thorne is the programme chair of the European Spreadsheets Risks Interest Group (EuSpRIG) a learned society that studies the phenomena of spreadsheet error and risk.