Six lessons from the Covid-19 test and trace Excel blunder

26 October 2020 7 min. read

The news that crucial coronavirus test data was delayed in being added to the track and trace system due to an Excel blunder, causing the contacts of more than 16,000 positive cases to be under-reported, led to outrage in IT and public circles. Jon Taylor, Principal Consultant at Conosco, shares six lessons that can be learned from the controversy.

The culprit of the issue? According to Public Health England (PHE), data stored in a CSV file using an Excel spreadsheet was behind the glitch. The test results were not transferred to the health service, and the version of Excel it was using had a column limit of 16,384 – after that cut off point, records were not counted. As many as 48,000 people were continuing to go about their lives for a week, unaware that they had come into close contact with someone who had tested positive. 

As a result, Public Health England is being questioned on how it’s handling the public’s data. This is not a new issue – addressing trust with how the public sector handles data has always been a questioning point. One study by the government found that there is a communications challenge in framing why the public sector needs to use personal data. For those in the public sector, they often have limited budgets and lack of knowledge in advanced infrastructure. 

Six lessons from the Covid-19 test and trace Excel blunder

But what can they learn from the track and trace scandal about data integrity? 

1) Don’t use Excel

When manually adding or changing values in Excel, there is a heightened risk of compromising data accuracy by human error. PHE likely used Excel due to the fact that the testing programme was rapidly developed, and it offers the ease of creating and leveraging macros to perform transformations of the data within the same file.

Excel does not lend itself to automation, which is fine when you have small scale projects. But for large scale production environments, which the public sector regularly faces, manual data entering, collating and sending is not a viable option to ensure data accuracy and validity. When all you have is a hammer, everything looks like a nail – but it’s important to use the appropriate software for your needs.

Further reading: The risks of Excel for modelling and decision-making.

2) Legacy systems can be your downfall

Many public sector organisations work with legacy systems – an outdated system which often doesn’t function with newer software and hardware. This is partly down to cost concerns, which is exacerbated by lack of technical knowledge. In actual fact, the cost of moving to newer cloud systems will actually be at a similar cost to what they’re currently paying. This is because the public sector often employs people to maintain, monitor, and manage your legacy systems, which is costly and time-consuming.

When you work on legacy systems, you open up your data to higher risks and security vulnerabilities that remain unfixed. There are also physical and feature limitations of legacy systems – such as an older version of Excel, which can handle even smaller datasets than the latest version.

3) Turn to the cloud for scalable data storage

Turning to the cloud can guarantee the utmost level of integrity for any transactions on your database. Cloud databases can hold many millions of rows of data, avoiding the risks of data being missed off important reports (Excel has a hard limit of circa 1 million). In the face of austerity, public sector organisations need to demonstrate cost savings. Mistaking a data value for the result of a calculation is also far less likely with a database than a spreadsheet, which is better suited to a multi-use environment. 

Turning to the cloud can create a number of cost savings – such as faster procurement, a better quality of service, higher productivity, and better protection against security risks. The cost of a breach is far, far more than the cost of maintaining robust cloud solutions due to risks of financial theft, data loss, intellectual property theft, business interruption, equipment damage, productivity damage, and more. 

Organisations spend $3.86 million (around £2.9 million) recovering from security incidents, according to Ponemon Institute’s Cost of a Data Breach Report 2020. 

4) Opt for ACID-compliant databases

In the public sector, it’s easy to worry about collating and storing public data – a leak which exposes improper practice could destroy public trust. Instead, a cloud database would be a better alternative to manual data entering. There’s one term you want to look out for when selecting a database: ACID.

An ACID-compliant database guarantees that all updates and changes to data are atomic, consistent, isolated, and durable. Without an ACID-compliance, you lose the assurance of data consistency despite errors, power failures, or other mishaps. There are many cloud-based scalable databases, including Microsoft Azure SQL, Amazon Relational, Oracle, IMB Db2, Google Cloud SQL, and NoSQL.

5) Upskill your team

An organisation which hasn’t been prone to a lot of digital transformation can struggle to adapt to the new ways of working. Ensure that not just your team, but internal and external third parties are up to date on the technical skills, developments, and systems they need to work with on a daily basis. Reorganising the way that your team works will require a framework for those in IT to follow. 

Consider getting your business certified by Cyber Essentials, a scheme that will enable you and your employees to learn the right controls to mitigate your business’ risk to any security threats. By sharing this on your website and with customers, they’ll know that you’ve worked to protect their data against threats. 

6) Contextualise generic risks to your business

We all know that we need to protect our data, but how do you translate advice to make it specific for your business, objectives, and daily activities? 

A vulnerability scanning service will identify the potential points of failure in your software and configurations so you can contextualise generic risks to your organisation. This will enable you to ensure you meet legal requirements such as GDPR, and prioritise any issues which need fixing. Then you can create a risk assessment to present to your key staff, identifying any potential risks where data could be affected. You can also opt for scans that are sent to you monthly, quarterly, or annually. When there are so many other business decisions to make right now, having automated scans that find critical findings and actionable recommendations can be a welcome weight off your shoulders. 

Lessons for the future

The Excel issue could have been prevented – we must learn from it. A lot of personal data is often shared across the public sector, as it can be of huge benefit to delivering better services to the population. However, the government hasn’t created a clear data infrastructure across the board, which has created an environment of tenuous trust. 

If your data is compromised, you don’t just risk losing public trust – you also open yourself up to a huge malware risk that can come with a fine. By law, all data breaches need to be reported to the Information Commissioner’s Office within 72 hours, which can fine businesses if it finds improper practice. The standard maximum is 10 million Euros, or the equivalent of 2% of total annual worldwide turnover, as seen with British Airways recently.