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

691–700 of 834 posts

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

#691

Post by Closi »

mjw1007 wrote: post #181

So, what we know is: « PHE had set up an automatic process to pull this data together into Excel templates [...] When [the row limit] was reached, further cases were simply left off. » The terrible thing here is dropping data rather than reporting an error and refusing to run. It isn't clear what piece of software was behind this "automatic process". Clearly the responsible humans are to blame. If the software that d…

> It isn't clear what piece of software was behind this "automatic process".

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.

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

#692

Post by Closi »

jussij wrote: post #643
1_player wrote: post #352

Earlier quoted context omitted.

> 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.

It's unlikely this was done by a developer in reality, and it's more likely to be a data analyst that was called a developer in the press release.

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

#693

Post by cm2187 »

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…

Also long numeric ids are rounded by Excel.

And fucking with dates.

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

#694

Post by Closi »

Closi wrote: post #635
collyw wrote: post #619

Earlier quoted context omitted.

The real question should be would it be cheaper to replace it with a proper solution (probably something custom built application written by a software engineer). Excel's ease of use is it's downfall. It is the worlds most popular database, despite not actually being a database. I have wasted countless hours dealing with Excel where something else should have been used. I built a database for a friend recently, I thi…

The problem with a database is that business users struggle to build them, they take longer, and sometimes can’t hold logic in the same way. Plus in a database you can’t do the same sort of real-time analysis and also pass the document around for other non-technical folk to add to and modify. In the real world in big companies, people often don’t want to talk to IT because they over-spec and quote what are perceived…

To add to this point, the latest article says that they started building a replacement to the spreadsheet two months ago, so I think that reinforces the speed of a spreadsheet implementation vs anything else in the public sector!

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

#695

Post by ogig »

While I agree with the general sentiment that excel is normally misused, like in this case, it's also capable of handling huge amounts of data thanks to powerquery and powerpivot (1,999,999,997 rows per table, 2 billion tables max, docs say). You can't load the data in the grid, you need to use the data model. Using powerquery properly also solves most problems about excel guessing types wrongly when importing csv and other external data. If strongly recommend any excel user to learn powerquery and powerpivot.

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

#696

Post by giantDinosaur »

sheetjs wrote: post #567
yawaramin wrote: post #563

Earlier quoted context omitted.

Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.

The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"

I have a list of companies I'd like you to consult for. Coincidentally, they're companies I'd like to work for, and I've quite enjoyed building proper database solutions which replace incredibly hacky/terrible Excel (or Excel adjacent) solutions.

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

#697

Post by jamil7 »

sjansen wrote: post #318
remus wrote: post #212

Earlier quoted context omitted.

Looking from an outside perspective I would agree, but in practice we don't actually know what this dataset is, so several things rows per test may make sense in the context of this stage of the data processing.

It never ceases to amaze me how often we as developers are quick to point out other people's obviously incorrect decisions, only to defend our own Rube Goldberg implementations 30 minutes later by pointing out that critics just don't understand the design constraints. Or at least how often my peers do that. Obviously all of my systems and code are perfect.

Nothing seems as easy as another engineer’s problem.

This also amazes me, especially the whining about other people's code from developers. Truely believing that they would do it better. I've even seen inherited code posted to be ridiculed/shamed/bashed in some slacks and subreddits.

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

#698

Post by jjgreen »

bobbylarrybobby wrote: post #497
codeulike wrote: post #335

Earlier quoted context omitted.

Good points 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.

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

Tolstoy keeps on giving ...

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

#699

Post by mratsim »

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…

Working internationally with CSV holding dates is a recipe for disasters.

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

#700

Post by VBprogrammer »

altacc wrote: post #657
collyw wrote: post #619

Earlier quoted context omitted.

The real question should be would it be cheaper to replace it with a proper solution (probably something custom built application written by a software engineer). Excel's ease of use is it's downfall. It is the worlds most popular database, despite not actually being a database. I have wasted countless hours dealing with Excel where something else should have been used. I built a database for a friend recently, I thi…

I find people often use Excel (or Access if they're brave) when they have a known problem and can visualise the solution in something they know, like Excel. The crucial factor is implementation time. They can open an Excel and start work immediately, or wait weeks or months going through a lengthy and complex IT procurement process that will take from their budget. So in most cases starting with Excel is a no-brainer…

> Same reason why a lot of stealth IT gets implemented.

Honestly, I miss the days of writing VBA macros which save hours of work a week and being sneered at by the 'official IT'.

I worked in a team in a large commercial bank handling reconciliations with various funds. Some of which had to be contacted by phone to confirm the current holdings. We had a system which would import our current positions and take imports in various formats from funds. Somewhere around 40% of the differences where due to trades which had been executed over the reconciliation date. I wrote a VBA script which pulled in all of the differences and identified trades which where open over the period and automatically closed the discrepancy with a reference to the trade IDs.

Another time I wrote a VBA script which would take a case ID and look it up in a diary system (at the time the only way I found to do this was to use the Win32 APIs and manually parse the fields in the HTML from the system), it would then enter this at the top of a spreadsheet which had to be completed. People liked it so much I had to rewrite it so that it would work on a list of case IDs and automatically print out the checklist.

Much more fun than figuring out why Kubernetes is doing something weird for the 3rd time this week.

Post reply on HN