A Simple Spreadsheet Error That Could Kill People Over the Coming Weeks

A spreadsheet error by Public Health England (PHE) resulted in 16,000 Coronavirus cases being unreported to track and trace departments.

Over the years I have written many times about spreadsheet errors causing business catastrophes or even resulting in 10,000 people being wrongly bugged by MI5. This is the first time I have ever written about a spreadsheet error potentially resulting in deaths.

One of Public Health England’s (PHE) key tasks is monitoring the number of Coronavirus cases in England and assisting in a track and trace system. As a part of that process, they were to pass the details of all positive cases to the track and trace department so they could notify any contacts to self-isolate.

This system failed, and will probably fail again because this massive task was tackled with nothing more than a humble Excel spreadsheet. This resulted in the track and trace department being unaware of 16,000 positive cases.

The impact is hard to guess, but there is no doubt it will cause more cases and ultimately more deaths. If each of those 16,000 cases had come into contact with just 10 people each, that is potentially 160,000 people that should have been isolating. Instead, they were potentially wondering the streets, spreading the disease further.

That is massively simplifying the maths, and the numbers were probably far less than that. (My expertise is in Excel and spreadsheets, rather than anything medical).

So what went wrong?

The Initial Problem

PHE Gathered data from commercial firms responsible for testing patients around England. This data was provided to the PHE in the form of a CSV file from the commercial firms. (A CSV is a simple text file that holds fields of data, usually separated by commas, hence the name; Comma Separated Values = CSV. It can hold an almost unlimited amount of data).

The trouble started when PHE’s internal developers imported these CSV files into Excel. They set up an automated process to open each firm’s CSV file and copy its contents into its own tab of a spreadsheet. This was probably developed in VBA but could have been developed in other programming languages such as Python.

The big mistake was using the .xls Excel file format rather than the .xlsx format.

The .xls file format is limited to holding just 65,000 rows of data in each sheet. As each patient’s record needed several rows of data, they soon exceeded this limit. So, every row of data after the 65,000-row limit was reached was lost. In this case, 16,000 patient records were lost.

If the developers had used the .xlsx file format instead of the .xls format, they would have had over 1 million rows available to them. This is over 15 times as many rows, which would have meant the records wouldn’t have been lost in this case.

However, if the UK gets 15 times more cases, this higher limit would also be met and it would start to shed records again.

Tackling so much sensitive and important data with a spreadsheet was a massive mistake. This was a task better suited for a database or specialist software. Using the most limited spreadsheet format compounded this mistake further.

Where Were the Error Messages?

A big question yet to be answered is, how did they do this without generating any error messages? And if there were error messages, were they just ignored?

If you try to paste too much data into an Excel sheet, it will generate a warning message to tell you it will truncate your data. Even if you use VBA to automate things, an error message should still be generated.

Three scenarios spring to mind;

  • There were error messages, but the developer clicked passed them. If the developer got an error message and checked the destination datasheet, they may have seen a sheet full of data. So, they would have a reason to think everything had worked okay despite the error message. In my experience, this happens a lot in the commercial world.
  • They suppressed the error messages. If you program a solution in VBA, you can turn off error messages, screen updates, and calculations to make the programmer run quicker. This is fairly common in VBA programming and can reduce runtimes by about 90%.
  • The developer used a different programming language that could not detect errors happening in Excel. So there were no error messages.

Ignoring or suppressing error messages is always a risk, no matter what you are doing with a spreadsheet. But when you are trying to save lives, it is unforgivable.

Could it Happen Again?

Yes, it could, and going by the PHE’s response it probably won’t take long to happen again. Their immediate answer was not to transfer the data to .xlsx files or a database. It was to breakdown the test data into smaller batches and still use spreadsheets.

If anything, this increases the chances of other problems. It means more file handling/processing and stitching together results will need working with many more spreadsheet sheets. Any Excel developer worth their salt would never come up with such a problematic solution to such a simple but vital task.

Conclusions

There was clearly little planning and testing during the development of this tool, and now there are people who could potentially pay the ultimate cost for those mistakes.

This article is not meant to be a scare story or to be political in any way. It is to demonstrate;

  • How easy it is to make a hidden mistake in Excel.
  • How bad those errors can be.

It will be a while (if at all) we get any official figures on how many extra infections resulted from these mistakes. I can’t imagine that out of the hundreds of thousands of contacts that should have been notified, none of them had been infected.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s