16K COVID-19 Cases Go Missing In UK Due To Government's Use Of Excel CSVs For Tracking

from the excel? dept

Yes, yes, you're sick of hearing about COVID-19. Me too. But the dominant force of 2020 continues to provide news, often times with a technology focus. This mismanaged pandemic has already given us an explosion of esports, students gaming remote learning systems, and enough dystopia to make George Orwell vomit in his grave.

But to really get your anger bubbles gurgling, you need turn only to the myriad of ways far too many governments have taken a moment that requires real leadership and forethought, and pissed it all down their legs. America appears to be trying to lead the charge in this, with our shining city on the hill mostly being illuminated by headlights of cars carrying sick passengers looking to get tested for this disease. Still, we're not alone when it comes to sheer asshatery. The UK recently managed to lose thousands of COVID-19 cases... because it was tracking them in Excel CSVs.

The issue was caused by the way the agency brought together logs produced by commercial firms paid to analyse swab tests of the public, to discover who has the virus. They filed their results in the form of text-based lists - known as CSV files - without issue.

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

Public Health England (PHE) decided to put all of this information into a file using the XLS format. XLS was first introduced in 1987 and was replaced by the XLSX format over a decade ago. Putting aside the use of Excel to monitor positive COVID-19 cases in a major industrialized nation for just a moment, just the use of an antiquated format managed to lose PHE over sixteen thousand positive cases.

How? Well, XLS has restrictions as to how many rows of data it can record.

As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of. And since each test result created several rows of data, in practice it meant that each template was limited to about 1,400 cases.

When that total was reached, further cases were simply left off.

Which means the people that had COVID-19 weren't tracked for contact tracing. The government and its people didn't have a complete picture as to either the total case count for the disease, nor its positivity rate. In other words, the agency in charge of national health failed to keep the nation informed as to its risk exposure because it didn't know how to properly use a common office application that it repurposed to record COVID-19 data.

Labour's shadow health secretary Jonathan Ashworth said lives had still been put at risk because the contact-tracing process had been delayed.

"Thousands of people [were] blissfully unaware they've been exposed to Covid, potentially spreading this deadly virus at a time when hospital admissions are increasing," he told the House of Commons. "This isn't just a shambles. It's so much worse."

The UK's Health Secretary told the House of Commons that PHE had decided to replace the use of Excel, or what he called a "legacy system", two months ago. But apparently PHE hadn't gotten around to it yet.

And still hasn't, actually. In fact, PHE's plan to temporarily fix all of this is... more Excel!

To handle the problem, PHE is now breaking down the test result data into smaller batches to create a larger number of Excel templates. That should ensure none hit their cap.

But insiders acknowledge that the current clunky system needs to be replaced by something more advanced that excludes Excel, as soon as possible.

When you hear complaints that governments are not taking this pandemic seriously, this is what they mean.

Filed Under: covid-19, excel, public health england, uk

Scientists Forced To Change Names Of Human Genes Because Of Microsoft's Failure To Patch Excel

from the code-is-law dept

Six years ago, Techdirt wrote about a curious issue with Microsoft's Excel. A default date conversion feature was altering the names of genes, because they looked like dates. For example, the tumor suppressor gene DEC1 (Deleted in Esophageal Cancer 1) was being converted to "1-DEC". Hardly a widespread problem, you might think. Not so: research in 2016 found that nearly 20% of 3500 papers taken from leading genomic journals contained gene lists that had been corrupted by Excel's re-interpretation of names as dates. Although there don't seem to be any instances where this led to serious errors, there is a natural concern that it could distort research results. The good news is this problem has now been fixed. The rather surprising news is that it wasn't Microsoft that fixed it, even though Excel was at fault. As an article in The Verge reports:

Help has arrived, though, in the form of the scientific body in charge of standardizing the names of genes, the HUGO Gene Nomenclature Committee, or HGNC. This week, the HGNC published new guidelines for gene naming, including for "symbols that affect data handling and retrieval." From now on, they say, human genes and the proteins they expressed will be named with one eye on Excel's auto-formatting. That means the symbol MARCH1 has now become MARCHF1, while SEPT1 has become SEPTIN1, and so on. A record of old symbols and names will be stored by HGNC to avoid confusion in the future.

So far, 27 genes have been re-named in this way. Modifying gene names in itself is not unheard of. The Verge article notes that, in the past, names that made sense to experts, but which might alarm or offend lay people, are also changed from time to time:

"We always have to imagine a clinician having to explain to a parent that their child has a mutation in a particular gene,” says [Elspeth Bruford, the coordinator of HGNC]. "For example, HECA [a cancer-related human gene] used to have the gene name 'headcase homolog (Drosophila),' named after the equivalent gene in fruit fly, but we changed it to 'hdc homolog, cell cycle regulator' to avoid potential offense."

It is nice to know that we won't need to worry about serious problems flowing from Excel's habit of automatically re-naming cell entries. But it's rather troubling that Microsoft doesn't seem to have thought the problem worthy of its attention or a fix, despite it being known for at least six years. It shows once again how people are being forced to adapt to the software they use, rather than the other way around. Or, as Lawrence Lessig famously wrote: "code is law"·

Follow me @glynmoody on Twitter, Diaspora, or Mastodon.

Filed Under: autoconversion, dates, excel, gene names, genes, spreadsheets
Companies: microsoft

Daily Deal: Microsoft Excel Specialist Certification Bundle

from the good-deals-on-cool-stuff dept

With thousands of features and menu commands to get lost in, learning to seamlessly navigate Microsoft Excel 2016 is essential. Gain a solid foundation in this spreadsheet software with the $19 Microsoft Excel Specialist Certification Bundle. In the first course, you'll learn everything from spreadsheet terminology to how to insert graphics and charts, becoming comfortable with the newest edition of Excel. Move through the intermediate-to-advanced tutorials at your own pace in the second course and understand how to manage workbooks, track your changes, troubleshoot, and most importantly, reduce your workload by automating repetitive tasks. Once you’ve completed the course, you’ll be fully prepared to ace the Microsoft Office Specialist exam.

Note: The Techdirt Deals Store is powered and curated by StackCommerce. A portion of all sales from Techdirt Deals helps support Techdirt. The products featured do not reflect endorsements by our editorial team.

Filed Under: daily deal, excel, office