Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

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

bbc.com

Post reply on HN
Print view View original

641–650 of 834 posts

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

#641

Post by Maha-pudma »

collyw wrote: post #623
swiley wrote: post #528

Earlier quoted context omitted.

Is it really that hard to spend 5 minutes setting up a decent database? Use sqlite if you really want something simple. I guess you still have to understand locking (especially on distributed filesystems.) I've certainly seen people mess that up with spreadsheets.

It takes a course to understand how to to use a relational database. MS Access used to come as standard with Office and is actually the perfect solution to many of the problems that businesses use Excel for. It's very rarely that people actually used Access as Excel was far more intuitive and good enough for many projects especially in the early stages.

I can attest to this. I move job and my new work place used to manage everything in excel. Something's still are. Realising excel could not scale, and with no support for IT for a proper solution the "tech" guy (that is good with computers but not a developer) was tasked with creating a "database". What we now have is essentially 3 excel spreadsheet tables in access that you can run SQL on and input data using access forms. No normalisation, no well anything really. We're now coming to the point where access is creaking at the seams. I'm confident with properly designed tables and queries we wouldn't be, and I'm no expert either, but it will have to fail before there's a concerted effort to redesign or god forbid IT do there jobs.

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

#642

Post by eru »

eCa wrote: post #640
jugg1es wrote: post #538

I work in healthcare and by far the biggest production issues we've ever run into are people who run Excel spreadsheets through our file processing where the leading zero's have been removed from patient identifiers because that's Excel's default behavior and you CANNOT TURN IT OFF! EDIT: I have no idea who downvoted my post because what I said is 100% true. We have to tell customers to stop opening CSVs in Excel and…

I used to work with books, which obviously includes ISBNs. They are 13 digit numbers, helpfully turned into scientific notation by Excel.

Don't ISBNs also contain an X sometimes?

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

#643

Post by jussij »

1_player wrote: post #352
jononor wrote: post #199

Always use checked pre- and post-conditions in a data pipeline. This simple post-condition would have caught this issue: The sheet after merge operation must have a number of rows equal to the sum of number of rows for all merged sheets. Assuming this is a merge of a standardized input, then another post-condition might be: The number of columns in output shall equal the number of columns in the input. Might want to…

> Always use checked pre- and post-conditions in a data pipeline. [A] simple post-condition would have caught this issue I'm not sure they teach that in medical school.

This quote from the article says this work was being done be a developer(s) and if that is the case they should have know better.

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

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

#644

Post by rjtavares »

computerfriend wrote: post #598
tsjq wrote: post #518

Earlier quoted context omitted.

and one more: "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...

Also the post-2008 financial crisis austerity programs based on Excel analysis. https://mathbabe.org/2013/04/17/global-move-to-austerity-bas...

Austerity wasn't based on that study, and it didn't go away once that error was found.

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

#645

Post by tonyedgecombe »

yawaramin wrote: post #565
rossdavidh wrote: post #427

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

Nobody is blaming Microsoft. From the article: > And it appears that Public Health England (PHE) was to blame, rather than a third-party contractor.

Blaming PHE seems to have become a regular occurance this year. I wonder if it's justified or not.

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

#646

Post by timclark »

eru wrote: post #642
eCa wrote: post #640

Earlier quoted context omitted.

I used to work with books, which obviously includes ISBNs. They are 13 digit numbers, helpfully turned into scientific notation by Excel.

Don't ISBNs also contain an X sometimes?

X can be a check digit. From memory the check digit is calculated using a modulo 11 calculation.

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

#647

Post by simonh »

petre wrote: post #636
jugg1es wrote: post #538

I work in healthcare and by far the biggest production issues we've ever run into are people who run Excel spreadsheets through our file processing where the leading zero's have been removed from patient identifiers because that's Excel's default behavior and you CANNOT TURN IT OFF! EDIT: I have no idea who downvoted my post because what I said is 100% true. We have to tell customers to stop opening CSVs in Excel and…

Just format the identifier cells as text. I've also had this problem, this is how I solved it.

That's fine for an individual working on a specific set of data in a specific sheet, but this isn't just a problem for one individual or a small team that can solve it and move on. It's a systemic problem throughout many vast organisations, with continuous influxes of new personnel and constantly changing requirements. When you get an XLS sheet sent over to you from another team that already made this mistake, it's too late and this happens all the time.

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

#648

Post by tonyedgecombe »

codeulike wrote: post #626
bobbylarrybobby wrote: post #497

Earlier quoted context omitted.

Off by one errors are all alike; every excel error is an error in its own way.

Not when it mixes up US and UK date formats when you import a CSV. Not when it turns phone numbers into integers and strips off the leading zero.

This is the root of the problem isn't it? The wrong tool for the job.

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

#649

Post by teekert »

tsjq wrote: post #518
totaldude87 wrote: post #491

All previous major spreadsheet screwups were costing money (largest being $6 billion in losses due to a excel formula mishap) , but this one is playing with lives :( 5 – London Olympics Oversells Swimming Event by 10,000 Tickets 4- Banking powerhouse Barclay’s accidentally bought 179 more contracts than they intended in their purchase of Lehman Brothers assets in 2008. Someone hid cells containing the unwanted contra…

and one more: "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...

Yeah, why there is no "scientific" mode to just turn of all "smartness" has baffled me for years and will probably continue to baffle me for years to come.

Or even an algorithm that can detect that you are using gene name from the cells around march1 and sept7.

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

#650

Post by tomatocracy »

jl6 wrote: post #315

It’s fun to point and laugh, but is this really just the consequence of an unprecedented health emergency demanding a rush job? As far as bugs go, it doesn’t sound that bad. They didn’t lose data - they just processed it late? And they spotted it within days/weeks, and have a workaround/correction already? And it’s only the reporting that was wrong, not the more important part where they inform people of results? I’d…

I think that's too generous. It would indeed have been excusable for perhaps the first 2 weeks. But at this point, they've had an effectively unlimited budget, likely access to engineers working for big consultancies and big tech who would have been quite happy to literally drop everything to help out and 6 MONTHS to build a system that worked. And they are still using an Excel spreadsheet.
Post reply on HN