We explain how outdated IT software caused the Test and Trace issues which led to nearly 16,000 missing cases - and what they could have done to prevent it.

Over the last couple of days, reports have confirmed that 15,841 coronavirus cases were missing from the daily UK figures. These cases should have been recorded between September 25th and October 2nd. But how could this many cases accidentally go unnoticed? The problem originates from outdated IT software.  

 

How the Test and Trace IT systems caused almost 16,000 missed cases 

 

Rather than a bespoke reporting system, Public Health England have been using Microsoft Excel to compile important data and reports. 

The firms paid to carry out swab tests for the virus were filing their results in the form of text-based lists, known as CSV (Comma Separated Value) files. This method was working as intended; however, the problem started after they were sent to Public Health England. 

Public Health England had set up an automatic process to pull this data together into Excel templates so that it could be uploaded to a central system and made available to the NHS Test and Trace team, as well as other government dashboards. 

However, they were using an old file format known as XLS, which has a limit of 65,000 rows per spreadsheet, whereas the newer version is capable of 1,000,000+ rows. Each Covid case took up multiple rows in the spreadsheet and, once the file size limit was hit, any further cases were missed.

 

How could the 16,000 missed cases have been avoided? 

 

There are a couple of areas where the process broke down, which could have prevented the missing Covid cases: 

 

  • The problem: Using XLS Templates 

XLS is an outdated format – it was first used in 1987 and was superseded by XLSX in 2007. XLSX can handle 16 times the number of rows. 

 

  • The solution: Modern data collection 

Excel shouldn’t have been the first solution for data collection on such a large scale. Even if Excel was their only available tool, XLSX file format should have been used. Or, at the very least, sufficient monitoring processes should have been implemented to ensure action could be taken once the XLS file was approaching its maximum data capacity. 

 

Here at Air IT, we would have recommended a dynamic data collection solution such as Microsoft Forms or PowerApps. This would ensure data collected could be fed directly into a central database, rather than being processed multiple times by several different data handlers.  

 

  • The problem: Using Excel to manage data 

Excel is favoured by many companies due to its versatility and ease of use. However, it presents limitations when it comes to dealing with larger datasets and important reports. As companies grow, they may not make the transition to a tailored solution which would remove the need for manually processing their data – whether that is because they don’t want to spend extra time and money, or they are unaware of the options available. 

 

  • The solution:  

A centralised business application such as Microsoft Power BI can make your spreadsheet-based database much more dynamic and robust. This modern way of reporting can bring your data to life in a way that best suits your business needs. It brings together reports from all areas of your business into one easy-to-digest dashboard. 

 

 

Effective data management would have prevented missing Covid cases 

Almost 16,000 Covid cases were missed due to poor data management and monitoring. If measures had been put in place previously, potential data capturing issues could have been uncovered much sooner. 

 

How to avoid similar reporting errors happening to you  

As Business Intelligence experts we have years of experience handling data, and we’ve helped many organisations improve data capture and reporting processes to ensure greater quality and accuracy. 

With our expert knowledge and data management experience, we can provide accurate and easy-to-digest reports. Instead of wading through multiple Excel spreadsheets, data from various departments can be shared and viewed in one streamlined platform. So, at the click of a button, you and any other stakeholders can access any data that needs to be reported on. 

 

Want to know more?  

Air IT is an award-winning Managed Service Provider helping businesses across the UK succeed through technology. If you would like to learn more about our Business Intelligence services and how they could help your business, please don’t hesitate to get in touch.