On October 4th, 2020, Public Health England (PHE) reported that 15,841 COVID-19 cases were left out of daily reports from September 25th to October 2nd. The cause was due to the way the agency was using a Microsoft Excel spreadsheet in their reporting process. The Press Association (PA) detailed that the spreadsheet in question reached filesize limits, halting an automated process in PHE’s reporting procedure.
The BBC provided further details. PHE used the older XLS file format for their test result template which collated results from labs around the UK. This older file format limits the spreadsheet to 65,536 rows of data. The newer XLSX format has a cap of more than one million rows.
A single test result takes up several rows, resulting in the file only holding roughly 1,400 coronavirus tests.
How is PHE getting around the problem? PA reports that the offending spreadsheet is now being broken up into smaller batches to prevent future occurrences, to the dismay of IT and data experts.
Even if presented with a system that did rely on Excel, one of the first things that should have been identified through a testing process was a limit to the data volume it could process.Adam Leon Smith
It’s widely known within medical device development that the use of commercial off-the-shelf products, such as Excel, requires additional testing to ensure that they are able to meet the stringent requirements of use in a healthcare setting.Dr Peter Bannister
One would think that a software engineer would have considered a worst-case test scenario for any system design – on paper before you even get to writing code – just to make sure it wouldn’t blow up in any way like this.
…Also, a simple sanity check on the data or error checks in the system might have told them when they hit this limit instead of discovering it after the event.Jon Crowcroft
While Excel provides immense flexibility and power, it lacks out-of-the-box controls to ensure accuracy and integrity. Most Excel fiascos have typically been related to human input or logic errors.
For example, an error in a Value at Risk spreadsheet contributed to a $6B+ loss for JPMorgan in 2012. More recently, UK drink wholesaler Conviviality collapsed after losing 60% of its stock value from overstating profits by $5M. The cause? Arithmetic error in a spreadsheet.
This case illustrates another risk in using spreadsheets for critical processes without additional controls. PHE not only faces reputational damage but even more seriously impacted UK public health and safety.
At Apparity, we love our spreadsheets, but is the spreadsheet really the right tool for a nation’s critical COVID-19 reporting at this stage?
Does your organization use spreadsheets or other end user computing (EUC) tools for critical functions such as financial reporting? Get a free evaluation from Apparity’s team of experts on your existing EUC policy or with help creating a new policy.