Spreadsheet Screw-ups (or bad choice of Excel for serious purposes)

No, I’m not porting over all the AO threads/posts on this [I am backing them up, tho]

This is a new one, combining two things: Excel and COVID

Furious blame game erupts between PHE and testing tsar Dido Harding after 16,000 Covid cases are missed because an ‘Excel spreadsheet maxed out and wouldn’t update’ - meaning thousands of potentially infected contacts slipped through net

  • Latest daily coronavirus figures show 22,961 new cases and 33 deaths in UK
  • It follows a big jump in cases on Saturday when 12,800 Covid cases recorded
  • But it has now been revealed that 16,000 missed cases have been added to data
  • Issue due to Excel spreadsheet hitting maximum size and failing to update more
  • Health chiefs have been scrambling to find contacts of those cases missed

Some tweets on this:

[oh cool, the site software auto-embeds tweets]


Did a LinkedIn post:


I don’t recall seeing a spreadsheet where many individual records were put into columns like this. What a weird design

1 Like

So, it’s not clear to me what the formatting of the original .csv files were…

But there has been additional reporting since I wrote that post yesterday, and what it sounds like is that they got the data from labs via csv file, import into CURRENT version of Excel… and save as .xls

Yes, the old Excel format with 65K row limit.

And then that .xls is used for the dashboard/other apps

I’m old enough to remember having problems with that 64k limit. But that was in the order of decades ago. These people running Windows 3.1?

I think they are probably running a current version. Excel never thought anyone would need more than 16k columns, as who would organize data putting thousands of individual records into columns?

You can be using O365, and still run into this problem if you save as an old-type Excel file (aka .xls, as opposed to .xlsx or .xlsb) – You will get a warning message, but people don’t pay attention to those.

lol @ normies using excel. Libreoffice on ubuntu or gtfo.

That’s nice. Some of us have to use the software everybody else in the office uses. (ugh)

Most of the people I work with aren’t actuaries, fwiw

If they saved as .xls I would say that is more of a user error than a software glitch. Excel still has tons of pitfalls though, I wish Actuaries didn’t have to use it so much.

This was never a “glitch”. In all of the potential screw-ups mentioned, Excel worked exactly as in specs.

There are some Excel errors where it’s a software screw-up (such as when the calculation tree gets messed up, and cells don’t recalculate), but in the grand majority of Excel errors, it’s the user’s fault.

1 Like

More coverage – the Daily Telegraph:

Excerpt (I got from EuSpRIG):

When the affair of the London Whale became public in 2012, it sent shock waves through the global financial system. Senior traders at JP Morgan Chase, scrambling to mitigate existing losses, had made a series of complex financial bets so big that they had visibly shaken the market.

In the end, the bank lost more than $6bn (£4.6bn) and paid almost $1bn in fines. It was, as Jamie Dimon, its long-serving chief executive, said later, "the stupidest and most embarrassing situation I have ever been a part of ". And it might not have happened if not for a badly written Excel spreadsheet.

As it turned out, JP Morgan’s financial wizards had built their whole financial risk assessment system as a series of Excel spreadsheets, with crucial data being copied and pasted manually between them. Some of the formulas had errors, leading the system to dangerously underestimate the level of risk.

Today, Britain’s health services appear to have suffered an Excel error even more consequential. We do not yet know how much suffering, or even death, the temporary loss of 16,000 coronavirus case records by Public Health England (PHE) - and the resulting distortion of England’s Covid-19 statistics - may have caused.

It was not so much his words that stung - he is quite publicly not a fan of the Conservative Party - as his expertise: he is head of the European Spreadsheet Risks Group (EuSprig), an academic body that has been attempting to warn business leaders about the dangers of spreadsheets for more than a decade.

On its website, EuSprig has compiled a long list of “horror stories” at banks, airlines, police forces and the London 2012 Olympics committee where duff spreadsheets caused total financial discrepancies of almost £8.7bn.

One of the most consequential was a 2010 research paper by the economists Carmen Reinhart and Kenneth Rogoff, whose finding that too much national debt leads economies to suddenly stutter was cited by numerous hawkish politicians attempting to trim back government budgets in the wake of the 2008 crash.

In fact, Reinhart and Rogoff omitted five countries out of 20 from their Excel spreadsheet, meaning that their original average GDP drop of 0.1pc was really an average increase of 2.2pc.

Slapdash use of Excel has also been blamed for a plague of errors in studies of genetics, potentially clouding our understanding of our Genes such as septin 2 (abbreviated to “SEPT2”) and membrane-associated ring-CH-type finger 1 (abbreviated to “MARCH1”) had been automatically converted into dates by the helpful software.

Rather than try to change the way Excel worked, the geneticists decided to simply rename 27 human genes. One top scientist despaired that Excel should only be used for “lightweight scientific analysis”, and never “clinical trials”.

Nor is this a new problem. The first known spreadsheet error appears to have occurred around 1800BC, in a Babylonian accounting tablet now known as Plimpton 322. Scholars generally agree that the tablet contains six mathematical errors, although it is not known what risk those errors caused.

Unfortunately, it may be impossible to wean the world off using Excel for such serious purposes. Some software specialists argued that in many situations, where patchily trained employees in budget-strained institutions must bodge together whatever solutions they can, Excel is impossible to avoid in practice.

One source told British cyber-tabloid The Register that Excel was the "default for all tech in all of the NHS and related quangos and other bodies … to bridge all the gaps that the `proper’ tech hasn’t been designed to cope with?” A pseudonymous cybersecurity blogger claimed to know of nuclear power plants and airports that depended on Excel spreadsheets, or something as basic.

Ha! I’ve mastered the spoiler! Watch out!

1 Like

Don’t organize your crime in Excel:

[yes, I’m “saving” old links from the AO by posting them here]

Don’t use spreadsheets to run an election:

1 Like

A video on the matter:

1 Like

Regarding OP: Excel throws up a big fat verbose error warning if you try to save more 64k rows as an xls.

I suppose it’s possible they have some automation process that skips errors, but that’s definitely on them and not poor little old MS.

<3 When I first started thinking of BeingAnActuary, I had a dream that I was hired by Al Capone for risk management.

On the other hand, this may be a fabulous use for a spreadsheet.

Up to 40,000 cases from a system that logs complaints against Bristol City Council were stuck in an “unfathomable” spreadsheet, causing delays to investigations that will continue for another year or two, it has emerged.

The local government watchdog expressed concerns about the delays in an unusually critical annual review letter to the local authority.

If you’re using spreadsheets to value a deal, for an important M&A, getting an answer that’s off by 16% is pretty shabby.