Missing Covid-19 test data was caused by the ill-thought-out use of Excel

You are fully correct, I have seen plenty of stuff like that in life sciences projects.

That would be the Excel devs working at Microsoft. They read HN. I can feel it.

You can either send xlsx with format or csv without format. If this would be disabled then we'd have another group of people complaining that their dates from CSV are not parsed.

They clearly do not understand system integration and the use of CSV text files for data interchange between multiple systems and application. Hey, JSON and Javascript libraries are the answer to that, eh

There are already enough potential issues with CSV interpretation on wrapping strings, escaping characters and so on, but changing the content when a delimiter is found should not be added to that list.

You bold point is the most important, the default behaviour of Excel when opening a plain text CSV file is to alter the content for display, applying magic and often-unwanted formatting rules. That should be optional.

It should be possible to open a text CSV file in Excel, view the contents in columns but the same textual form, save the file in CSV format and open it in another viewer/editor and still see the same content as the original file.

And fucking with dates.

The first thing would be to write them as: entry1,"0123456789",entry2 rather than entry1,0123456789,entry2. This has worked for me in some instances in Excel whereby I have to escape certain things inside a string, but I would not be surprised if Excel still messes this up. For example, giving the triangle exclamation mark box and then helpfully suggest to convert to number.

If you want to go further, you can do something like write a routine that alters the CSV, such as entry1,hospitalString(0123456789),entry2. Sure, there are problems with this too, but Excel can break a lot of things and the above examples I do use in practise (the first example I put the double quotes to escape single quotes in foreign language unicode).

Another thing Excel can do is break your dates, by switching months (usually only for dates < 13th of the month, but often a partial conversion in your data for < 13th and >= 13th) or convert dates to integers.

Furthermore, losing preceding zeroes in number-typed values is not unique to excel; it is a common feature in all typed programming languages.

I recently had to show a 20+ year Excel-using fanatic how to import data from a CSV file so that they could select as type Text columns that contain leading zeros. The ability exists, but I have found scant few people who know how to actually use the product properly.

Oh, and I also work in healthcare.

Unless you instruct it to interpret the field as a string.

> but in healthcare, 10 digit numbers are really strings.

I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

blah,blah,"00001553",blah

Of course there is, in your case. If patient identifiers have a fixed, known length, then you can pad with leading zeros to recover them.

You only have a problem if 012345 and 12345 are distinct patient identifiers.

It is bone-headed in the first place to use numeric-looking identifiers (such as containing digits only) which are really strings, and then allow leading zeros. Identifiers which are really strings should start with a letter (which could be a common prefix). E.g. a patient ID could be a P00123.

This is useful for more than just protecting the 00. Anywhere in the system, including on any printed form, if you see P00123, you have a clue that it's a patient identifier. An input dialog can reject an input that is supposed to be a patient identifier if it is missing the leading P, or else include a fixed P in the UI to remind the user to look for P-something in whatever window or piece of paper they are copying from.

The main point in my comment is that instead of shaking your fist that the behavior of other users in the system, such as those who choose Excel because it's the only data munging thing they know how to use, you can look for ways that your own conventions and procedures are contributing to the issue.

If people are going to "Excel" your data, and then loop it back to you, maybe your system should be "Excel proofed".

"Scientists rename human genes to stop Microsoft Excel from misreading them as dates. Sometimes it’s easier to rewrite genetics than update Excel" https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

Entire Japanese stock market went down last week, and it's not like there was a flood of people on HN bemoaning that. At least with Reinhart and Rogoff et al you have a responsible party.

As opposed to 'nameless machine failed, and nameless backup machine also failed, and now it's in JIRA so don't worry about it'.

https://www.nytimes.com/2020/09/30/business/tokyo-stock-mark...

The glitch stemmed from a problem in the hardware that powers the exchange, said the Japan Exchange Group, the exchange’s operator, during a news conference. The system failed to switch to a backup in response to the problem, a representative said.

I guess you still have to understand locking (especially on distributed filesystems.) I've certainly seen people mess that up with spreadsheets.

At the same time, the business world runs on Excel. How much money is Excel making?

I've done my share of cursing at Excel at various jobs. At the same time, I am grateful for the quick and easy way it allows me and many others to manipulate data. It's unfair to just cite the costs of using Excel without acknowledging the benefits it brings.

https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel...

Anyone who comes at a problem with the mindset 'this is going to he hard' probably lacks experience and will throw big-data frameworks at it, really screwing things up. The most significant, and valuable, resource needed is thought first, and knowledge+experience second.

All IMO anyway.

Also, recognized good practices for software development (which any excel sheet that does more than just a sum() will be) like commenting and versioning are quite hard to impossible in excel. So even if you endavour to do it "right" but with excel, it is just the wrong tool for the job.

Sometimes you get an number, sometimes you get a string containing a number. Sometimes the string contains white space that you don't want. And then there are dates, which can have similar problems multiplied ten times.

The only correct usages of excel (and google sheets) are for user input to other processes, and visualization of data (which should be definitely stored in raw format in some database). And always assuming frequent backups of all sheets and of course manageable datasets. Anything else that includes external processes/scripts which append/ovewrite data in sheets is terrible practice and very error-prone.

Ultimately tools are built for particular things, and if you choose to use a tool for something it's not built for, and it breaks catastrophically, that's on you.

> And it appears that Public Health England (PHE) was to blame, rather than a third-party contractor.

This is a problem of bad coding, and using the wrong tool for the job.

A defensive coding practice would have prevented this from going unseen. Using a database to store data would have prevented such arbitrary limits.

My bet is the biggest problem here is subcontracting this work to the lowest bidder, presumably from some developing country.

Partly I think the Excel bit is news because Excel (and mistakes made with Excel) is easily relatable to people. But bugs always come up and most other bugs are just as stupid. If it had been an off-by-one loop error in some C code somewhere it would be just as dumb but you'd get none of the facepalm memes all over Twitter.

A delay in the test results means the contact tracing could not happen, and people will have been going around spreading the virus they caught off the original, known cases.

Also, the whole test and trace system has been in the news a lot recently here for various failings, and things like this will just further knock people's confidence in it.

I could see that i could be a rush job problem, but in this case they're not gaining any time.

I guess its easier to understand since everyone uses excel, however it does end up giving a halo of blame to excel, as opposed to human processes.

That’s the scandal: it’s the type of basic error that it screams “they’re not handling this well.” You wouldn’t tolerate your new SWE colleague asking “What’s an array again?”

If publishing the whole set of data was delayed by excel crashing - fine. But silent failure because they ran out of columns? Cmon...

In the grand scheme of things, having problems because of inexperienced people is much better than not having enough people like during April and May.

Wow. Thank you for this gem of human culture.

I'd imagine the default error-handling behaviour of 9/10 Excel macros is to throw away data

They probably mean a semi-automated process within excel, where each tab is a days extract or something similar and they are using external references to other sheets. In any vaguely up-to-date version of excel the way you would do this is via 'get and transform' which does not have any of these limitations (including the 1m record limit that the news article suggests).

The funny thing is that the latest versions of Excel are brilliant at aggregating and analyzing data and are more than suitable for this task if used correctly (i.e. using PowerQuery). It's just that way less than 1% of users are aware of this functionality - I would assume that even most hacker news readers probably don't know about PowerQuery/PowerPivot, writing M in excel and setting up data relationships e.t.c.

If the schema is consistent between rows, and it turns out a test result is made up of several rows because the test is composed of several stages, I would leave it as is until reporting time.

If you pivot prematurely, you could end up dropping data because there are new stages didn't exist when you implemented the pivot.

Before the recent leap in cases my test took 6 days to come back (negative). My wife's test at the same time came back the next day. At the time they were saying that 72 hours was the expected return time for results. For me it has been a couple of days of steadily worsening coughing, and I gather people take about 3days-1week to show symptoms ordinarily.

So UK results are most likely reflecting infections from 1-2 weeks ago.

Do a SUM and match it against a COUNTIF on another column, or something. It doesn’t really matter what it is but if the data is important at all, I always scatter little checks throughout.

The case described in this article sounds like someone who just doesn’t know how to use Excel. I mean why in the name of VLOOKUP would you override the default and choose to save as .xls? That was a conscious decision. Anyone worth their salt knows that’s stupid.

Excel is not to blame here.

I'm not sure they teach that in medical school.

I only say that because, as someone who is painfully aware of the limitations and problems of those formats, I'm similarly aware of getting "that web-guy" on a project who proclaims "lets put things in a modern xlm format!", and lo and behold the process is now an order of magnitude slower and the xml format an order of magnitude larger than the simple delimited tabular format or stream.

I'm also painfully aware of the old systems (and how old health systems are) with fixed sized buffers and processes, so I can see how this would happen in the context of a lot of computing.

Edit: i see later on someone is mentioning that twitter suggests it had to do with excel file size limitations...

Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in place to capture file write errors, or more generally the use of non-reproducible, manual operating practices which seems common in data processing.

It was a data pipeline issue. Software has little to do with it. If they received data in json and tried to interpret it as CSV, the same could have happened. I believe Excel even warns when you open file that has too many rows.

One lesson I’d draw from that is to favor simple human-readable text formats like CSV, where they’re suitable for the job at hand.

[1] https://twitter.com/standupmaths/status/1313055411285774336?...

edit: retracting this as the person who posted that tweet made a correction in one of the replies

My naive view would expect tables <-> sheets; rows <-> tuples to be easy to do (for MS) and just don't touch the relational aspects??

https://support.microsoft.com/en-us/office/power-pivot-power...

As you might see from that link it's semi-integrated with the Excel interface, but the differences in the underlying model show through in some ways (like not being able to edit individual cells or use VBA)

The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentication module built in (a file sent from a government mail address). There's a risk of user errors, but the system will be working immediately[0]. Deployment can be done by anyone, as it's just clicking on File->New and starting to type data in.

Meanwhile, a "pigsqueal" solution will take half a year to design, produce and deploy (that's in an emergency, a year otherwise). And that's with competent IT people, and not someone who wants to profiteer off the crisis. Then you'll have to train the users, and hope the reality won't necessitate any changes, because they will take a while.

I think plenty of decent coders appreciate the fact that Excel is suitable for a surprisingly wide range of tasks, and that if you spot it widely used somewhere, it most likely means there isn't any comparable alternative available.

--

[0] - Note that the user error that finally happened did not happen in the place where you'd expect it to.

I think you let them off too easily by just assuming they're dumb. This a bad decision by people who definitely should have known better.

IMO, a much more likely scenario: a brittle, haphazard system was set up with extremely short notice at the start of everything. One administrative snafu led to another, ran into "well, it's working so let's not touch it", and we got to where we are today.

The real problem is if/when scope begins to increase these processes are sufficient until they aren't, and the tools aren't flexible enough to gracefully manage the transition.

We’re not talking big data here. We’re talking about a finite number of possible test result from a finite set of testing locations grouped on a daily basis. Let’s say 1000 test locations, where each location has a daily number of tests performed divided into “positive”, “negative”, “inconclusive” and maybe a few other options. That’s ~5000 data points per day. Should not be any problem.

However, you need to properly plan ahead about how you design your spreadsheet, how to format the columns, how to keep it performant and how to not save it in some godawful 25 year old memory-dump dumpster fire of a file format...

And it doesn't help that there's a 25-year-old who says, "piqsqueal is outdated anyway, modern organizations use MonkeyDB and Hand Goop".

Contrast that to a database.

The first problem you run into is the limited number of people who actually have meaningful experience with databases. Databases ceased being consumer products about 20 years ago, so most people have only interacted with very limited front ends. They certainly haven't created a database, nor performed anything more than trivial queries. (Even then, they probably aren't thinking in terms of databases and queries.)

Okay then, we are reliant on a much smaller number of skilled database administrators and developers. Do their tools facilitate rapid development? Even if they do have access to suitable tools, the process is going to be slowed by communications between the people who need the analysis done and the people who can actually perform the analysis. Needless to say that Excel, with all of its limitations is starting to look like a good option.

> "But you wouldn't use XLS. Nobody would start with that."

Actually almost every non-technical person I know that wants something "database like" ends up in google sheets or excel first.

From the technical standpoint of a decent (and probably only decent) coder, I think Excel is an amazingly sensible choice for technical projects for a bunch of reasons. I think decent coders reach for the database solution far too often, and waste ungodly amounts of time setting it up. Excel coming bundled with an amazing front end UI you don't have to write code for shouldn't be ignored. Sure it's error prone, sure it's a nightmare for data validation, sure it doesn't scale to Amazon sizes, but this little project will never get that big. (Yes, until, of course, it does.)

I used Excel as the frontend for artists to author and enter small databases for a game engine, for example, and it was leaps and bounds better, and cheaper, and easier to use, than the unmaintainable SQL nightmare, with a bunch of frontend and backend dev on top of it, that the decent code who proceeded me left behind. Excel even scaled better, up to the size of our studio (hundreds of people), compared to the other system that needed technical artists to know how to use it. No question whether it would crumble if we were talking thousands of people or more. Which is why it happens so often. :P

e.g. some old versions of Crystal Reports can only do XLS. There must be loads old systems around that can only do XLS

...

“Oh nice! Exactly what I need!”

<copy>, <paste>

...

“Woah it builds! Ship it!”

I had someone come to me recently asking for some data "in an XLS". I asked them if they specifically need XLS or if they just need something that can be read by Excel. It was the latter, but they didn't know that there was a difference. To some people, XLS == Excel. Sort of like how TLS is still being referred to as SSL - somewhere in the world there is a developer being asked to use SSL for a new project because SSL == secure.

I can imagine an automated process that appended to the CSV and then Excel saving back to that same file with the lines stripped.

I hope we can all agree that this is not the way Excel or any other software should behave, whether the user is incompetent or not.

It's been quoted/reported as "an ill-thought-out use of Excel", when in reality it's "poor use of an older file format".

There's also a lesson to be had here about validating your data conversions. Especially for critical things like this, it's always a good idea to perform the conversion from the old file format to the new one, and then to go back and extract the data from the new one to compare to the source data.

This can also find issues like Excel cluelessly assuming a field is a date, PHP automatically converting a string which happens to only contain numbers into an integer, and so on.

From what I can tell, that sort of thing, assuming proper monitoring (which is a huge assumption), would have detected this immediately (plus however long until someone notices the error e-mail, etc.).

This was absolutely a user error and the title "Excel: Why using Microsoft's tool caused Covid-19 results to be lost" is really disappointingly click-baity.

The problem was a government still operating as though Britton Woods was in place five years after Nixon ended it

Excel works enough for small data, particularly when you don't do complex queries on it. The more you know of it, the better it works. The only other thing that offers similar benefits to Excel but works as a database is MS Access, but the mental model behind it is too complex for your average office worker who wasn't trained in it, and like most database systems, requires a lot of up-front work with figuring out the schema, and doesn't particularly like the schema being modified later on.

As far as I can tell, there's literally nothing else out there. No, random SaaS webapps du jour don't count, because they're universally slow, and also store the data in the cloud, instead of the local drive.

One number the UK government doesn't release is how many people are tested. They have spent the summer championing "testing capacity", which we found out at the start of September was a lie. They release the number of tests done, and the number of people returning positive. They don't release the number of people done -- if you're tested twice, that counts as two on the number of tests, but one on the number of people.

I suspect that wherever this foul up happened there wasn't someone sitting and clicking through sheets ignoring errors (I hope).

The reason the spreadsheets in the old format couldn’t grow was probably compatibility. The reason for the limit in the first place was probably about reducing memory on disk and in main memory.

It was a weird dual source of authority system, where the excel configuration was used at install / upgrade time, but you could change the config after the install at runtime. So you had to merge the active configuration into excel, upload the excel document to some server that customers didn't have access to that turned the excel file into a config file, then you could use that config file to upgrade the system.

It was as problematic as you could imagine, lost configuration, lots of macros, etc. Eventually they made some improvements, but I left the industry so don't know what the current status is.

Enterprise / Telecom solutions at their best.

Who are "they"? The Government? GDS? PHE? NHS England? NHS Digital? NHSx?

Most such papers are used to support existing policy preferences, not drive them. In addition, the error didn't reverse or erase the correlation, it diminished it, and removed the inflection point from the curve.

Imperfect measures, but they keep the work within the grasp of everyday/business users in a way a formal test suite wouldn’t necessarily.

Excel, whether you like it or loathe it, is in such wide use around the world that $300 math errors would have been noticed a very long time ago. I could believe that there are still many lurking bugs with obscure corner cases, nasty floating point rounding minutiae and so on, but I would bet (checks spreadsheet) $300 that your mismatched results were solely due to your own failures.

There's some saying about never going to sea with two spreadsheets.

You use structure, design away the complexity, implement constraints, error checking and tests.

Note it's true that an inexperienced person is liable to make spaghetti code either way, but there's nothing fundamental about a spreadsheet format that makes it inherently unusable for a lot of small/medium problems. Indeed it even has benefits in terms of interactivity/ turn around/accessibility.

Of course, there's also a lot of problems with Excel and reasons not to use it like a database or anything which fundamentally relies on maintaining data integrity, and it's liable to be the first tool reached for by the non-experienced, who will generally make a mess of things large and complex as a rule.

It's not automated but it can help future income/expenses/balance in an Excel-like UI.

> I started over using a different equation

Everyone who sees me paying for an app (I'm from India) ask me - "why can't you just use excel and do the same thing for free?". Excel sure is powerful, but in real life, your mileage may vary.

[0] - https://www.youneedabudget.com/

This is why people's email inboxes double as their TODO lists.

https://www.youtube.com/watch?v=K_FrQnQv0Vw (probably an accurate depiction of what's going on in Whitehall right now, though)

Sure, we techy types prefer a proper database with a web frontend and an API, but that requires significantly more skill to build than an excel file.

No database has the flexibility and flat learning curve that excel has.

Until a database manages to meet these goals, it remains a good tool for certain usecases.

We as engineers should be working towards making a real database thats just as easy to use as excel, and only then can we complain that people are using excel sheets for this kind of stuff.

[1] they almost certainly are using fax somewhere.

How do you know it was Excel? I see no technical details in the posted article except "some files containing positive test results exceeded the maximum file size".

I'm not sure if you're being sarcastic or not, but in case you're not, this has meant c.16k people missed from having their contacts traced. That's time-critical. Delays of any number of days are very bad. People will likely die because of this error.

This is safety-critical software. It should be stress tested and have sanity checks. It should be impossible for this to happen.

For something like this system, part of a track and trace system costing billions, to fail because of a file size error is just unbelievable.

Edit: oops, it seems they used columns for the cases and the ~16K column limit was hit when daily cases exceeded that.

Yes, anything-sql or whatever would be better, but deciding between putting all the data into sql by hand, or "uhm... i'll think of something and do it tomorrow", me, being lazy, I'd pick the second option,...

...until i'd get shamed on here. Maybe even a few days after.

It's not surprising that this reaches into government.

Even if your canonical database is done properly, there will be a clash here as soon as you share (or give anyone the ability to generate) CSV files that are too large for Excel.

Nope. They appointed Dido Harding to cough manage it, and Matt Hancock oversees it. That should tell you all you need to know. I wonder if they were running Excel 2007?

The article in question: https://www.bbc.co.uk/news/health-54387057

For me, I always feel like I'm living in some sort of parallel universe. Imagine living in a world where _you_, and a select group of others, know that there are better methods to drive a screw into a wall than punching it with your fist. However, 99% of people in the world punch screws into walls by hand, because they know how to use the only tool they have to hand (i.e. their hand). Their friends and neighbours do too. Their colleagues show them neat little tricks about how to fold your hand better to get the screw in with less pain, or more efficiently. The "proper" option -- drilling a pilot hole, using a self-tapping screw, understanding the difference between nails, bolts and screws, etc, or the concept of wall-plugs, is "too technical", "too complex", or requires "a large amount of infrastructure to fix a simple problem" [buying a drill & screwdriver, etc]. Sometimes people buy new walls with screws pre-installed because the thought of driving in those screws seems like an overwhelming technical burden.

You, and a group of similarly-minded nerds, understand just how stupid this is at times. You personally then spend the rest of your life watching people occasionally have horrific hand injuries from punching screws into walls and refusing the offer of screwdrivers. Others profit from selling vertical hard surfaces with a whole array of convenient screws pre-installed; or get employed teaching others the _very_ basics of screw-driving, or sell products containing all sorts of klunky hacks to cope with the fact that screws manually installed are at odd angles and tend to fall over randomly. The supplier of a popular brand of screwdrivers happens to be the world's largest screw manufacturer, but never seems to promote screwdrivers to people who currently punch them in with their fists. Instead, they sell them a subscription to screws, with the option for a very expensive pre-screwed wall-delivery service, built to your specification (that they call Dynamics for some reason…).

You, and a subset of your nerdy and educated friends recognise just how batshit insane the widespread use of punching-as-a-method-to-install-screws is, and occasionally laugh about it, mostly to distract yourself from the grim reality of buildings badly held together by punched-in-screws. Every so often a skyscraper-sized building collapses, [1] and it's realised in retrospect that the manually-driven-in-screws were not the right tool for the job. Still, people carry on banging in screws with their fists, and you increasingly realise that you have to -- somehow -- accept that in order to sleep at night without going insane.

[1] https://www.businessinsider.com/excel-partly-to-blame-for-tr...

You wouldn't (want to) believe how incredibly common that is.

Much easier for people to understand than shorter than civil engineer turned software developer in an engineering firm where I do data management, automation and application development for (mostly internal) clients to streamline business and engineering processes.

When I was in school (early 2000's) our GCSE computing lessons were, more or less, "Here's Microsoft Word, today we will learn how to format a letter!" or "Here's Microsoft Excel, today we will learn how to create a chart!".

The only upside to taking the class was that our school managed to identify the kids who were obviously computer literate and we got to go on a tour of Microsoft's UK headquarters...

That's not saying Excel is the best way to store data, but it gets a lot of jobs done without multiple month delays that come with an IT project. Often times, waiting 2 weeks because they're already busy then spending another 2 weeks outlining requirements is categorically unacceptable to accomplish the business goals.

If that sounds insane, I assure you that is a simplified version of what happens in some systems.

The project failed spectacularly if you were wondering.

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.

The problem is that the PHE developers picked an old file format to do this - known as XLS.

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.

As the NY Times said yesterday, sometimes it feels like "Britain is operating without adult supervision".