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

521–530 of 834 posts

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

#521

Post by taxcoder »

javagram wrote: post #404
lordnacho wrote: post #247

The problem is that anyone who isn't a decent coder think excel is a sensible choice for this, because they've seen it in their school or work. So when the proposals come in, you're going to see one guy who says it's all common sense and we use familiar old excel for everything, and another lunatic who says something called "pigsqueal" is actually the standard, connected to a "frontend" which for some reason is now s…

No need for a “front end” or “backend” or “pigsqueal”. MS provides a perfectly workable all in one tool, Access, for this kind of work. You can even use Excel as a front-end to access data if you want.

I have spent plenty of time running Access programs for employers. You better have a Frontend and Backend if you want data integrity.

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

#522

Post by avibhu »

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.

Which I feel might be indicative of a larger problem. If you are going to deal with data (and its processing), it only makes sense to teach those skills in school itself.

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

#524

Post by earksiinni »

galeos wrote: post #84

I wonder if this will be added to the European Spreadsheet Risk Interest Group's (EuSpRiG) horror stories list: http://www.eusprig.org/horror-stories.htm

TIL there is something called "spreadsheet risk management."

Wow. Thank you for this gem of human culture.

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

#525

Post by konjin »

gxs wrote: post #496
syshum wrote: post #442

Earlier quoted context omitted.

>>>I've personally cleaned up after people like you, Yep... The critical question that always gets blank stares is "How is this data and calculations validated" There is never an answer to that question from the ExcelMaster, it is always a variation of "it looks correct to me" Wonderful....

Except you can do it if you know what you're doing. No one is saying to manage millions of records etc. But you can get more mileage out of excel than people think - and people who half understand it are the worse because they know enough to know that it doesn't work. Custom solutions are great - but aren't always the answer and with proper process Excel will work just fine in many use cases.

>Except you can do it if you know what you're doing.

Ye gads, why didn't we think of that in any other programming language? If we know what we're doing we can do whatever we feel like, and if we get the wrong answers, it was obvious that we didn't know what we were doing which can be fixed by just knowing what we were doing!

Excel's only use case is for data that fits on one screen, or an exploratory poke at the data to see what's in which column and if there are any patterns you can eyeball. Then you put those hunches in a script and start doing the work for real.

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

#526

Post by sueders101 »

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…

No. There is difference between a little rush job, and an incompetent rush job. I’m only speaking for my own experience, but when put under pressure most competent teams don’t end up throwing away such important data. I’ve been incompetent for most of my career, but it’s given the serious of task this is inexcusable. I don’t know how things work elsewhere, but checking that the same number of unique records(by some composite key) are ingested as exported isn’t an advanced concept. Know the value of the data you’re handling. Marketing data health data. If health data doesn’t line up: you figure out why and solve it. If you don’t have anyone available to do so then the problem is managerial/structural.

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

#527

Post by konjin »

icedchai wrote: post #484
konjin wrote: post #475

Earlier quoted context omitted.

How about don't use excel in the first place?

Oh, I completely agree. That was one of the "couple of problems" in my initial reply!

Pretty common for finance in the 00s and 10s to be honest. It's slowly getting better, but until you fire everyone who isn't on board with this and replace them with people who can at least script - at every level of the business - you're going to have the same problem.

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

#528

Post by swiley »

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…

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.

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

#529

Post by swiley »

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.

They spend so much time memorizing random trivia maybe a course in handling data wouldn't be a bad idea.

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

#530

Post by foobar1962 »

mark-r wrote: post #200
whimsicalism wrote: post #182

Earlier quoted context omitted.

CSV != Excel. I work CSVs regularly and can't recall the last time I opened up Excel (intentionally)

Doesn't Excel by default capture the .csv extension so that it gets called automatically when you try to open the file? Since Excel is one of the few standard pieces of software that knows how to open CSV, it gets used a lot of times when it shouldn't. There's another post I made comparing Excel to a swiss army knife, and there's a reason for that.

The problem with Excel is that it changes the data, silently.
Post reply on HN