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

181–190 of 834 posts

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

#181

Post by mjw1007 »

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 dropped data rather than failing has that as its default behaviour, or is easily configured to do that, then I think that software (and its authors) are also to blame.

Is there anything in Excel itself that behaves like that?

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

#182

Post by whimsicalism »

mark-r wrote: post #153
morsch wrote: post #29

Earlier quoted context omitted.

CSV seems like a good choice for this kind of tabular, linear data.

Have you seen the kind of mess Excel can make with a CSV file? The names of several genes were recently changed so that Excel would stop mangling them.

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

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

#183

Post by danudey »

beagle3 wrote: post #85

This is indeed user incompetence, but so far every news source quoted the official PR and said it was an "Excel Problem". Microsoft PR was caught unprepared - I wonder how they'll re-spin it in the next few days (and for the first time that I can recall, a Microsoft product was wrongly blamed...) Pay attention, how every time there's a Windows virus or worm, it's a "Computer Virus", but in the (extremely rare) occasi…

> This is indeed user incompetence, but so far every news source quoted the official PR and said it was an "Excel Problem".

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

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

#184

Post by makomk »

dang wrote: post #100
mellosouls wrote: post #96

Actual title: Covid: Test error 'should never have happened' - Hancock Please note this has been discussed quite a lot here today. Eg. https://news.ycombinator.com/item?id=24685911 https://news.ycombinator.com/item?id=24685950

You're right. But this article contains significant new information (SNI) about Excel, and spreadsheet topics are perennially interesting to HN readers, so this is the rare case where a quick follow-up article, and also the title change, probably makes sense. Usually it goes the other way of course: https://hn.algolia.com/?query=follow-up%20by%3Adang&dateRang... https://hn.algolia.com/?query=%22significant%20new%20in…

The BBC has expanded on the Excel-related part and broken it out into a seperate article: https://www.bbc.co.uk/news/technology-54423988 Maybe it'd make more sense to link that one, if you're keeping the title?

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

#185

Post by sk5t »

cscharenberg wrote: post #138
indy wrote: post #121

Earlier quoted context omitted.

I think you'd be shocked by how many of our financial and social institutions rely on emailing Excel spreadsheets to one another.

Don't forget healthcare! Excel and CSV for tons of data exchange.

CSV is somewhat okay-ish. Excel, not so much (thanks Excel for discarding the important leading zeros in this field, and for lossily parsing the other as a date... oh, this Excel file is so big it got the "XLSB" treatment which is hard to undo)

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

#186

Post by Silhouette »

pjc50 wrote: post #7

Professionalism has been entirely absent from the top-level handling of the pandemic. It's not entirely clear from the article who's responsible for this, but I note that the so-called "NHS" test and trace is in fact an outsourced organisation: https://en.wikipedia.org/wiki/NHS_Test_and_Trace run by Tory placewoman Dido Harding. > "She is a former chief executive of the TalkTalk Group where she faced calls for her to…

The whole NHS Test and Trace organisation seems very strange. Going by recent news reports, it's not just Harding who comes from a retail background, several of the other top people do, too. What they don't seem to have done is hire top people with a technical or management background in either public health or technology, which you'd think might be slightly important for an organisation with this remit.

In other news, I'm looking forward to hearing about how a system apparently hacked together from text files and decades-old Excel formats is complying with basic data protection principles, given that it's being used to process sensitive personal data and has profound implications for both public health and now (since violating an instruction from their people to self-isolate has just been made a criminal offence) individual liberty for huge numbers of people.

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

#187

Post by TeMPOraL »

shadowgovt wrote: post #162
TwoBit wrote: post #157

Earlier quoted context omitted.

And what do they use instead of Excel? LibreOffice? Some expensive custom database software?

Yep, that's the problem. There's a gap in the space between the generality of Excel and the performance of a SQL DB with no UI that nobody wants to fill. Microsoft Access sits there, but nobody uses it for some reason.

My belief is because Access sits on the wrong side of the gap you mention. It's an SQL DB with an UI, alright, but the database is (or at least has a reputation of being) kind of flakey, and the UI exposes you to all the complexity and inflexibility of relational databases. This creates two types of hurdle. One is educational - you have to teach people basics of relational data modelling if you want Access to make sense for them. The other is operational - you can't shuffle data or table structure around in Access like you could in Excel. You can't just copy pieces of table around, you can't make rows depend on rows before them, etc.

Worth remembering is that Excel is, fundamentally, a 2D functional reactive programming REPL. Most of its users don't understand that, but they internalize the behavior. They may not know what a DAG is, but they know that updating cells will recalculate cells that depended on them. So you can't replace Excel with just a database - because half of the utility of the program is in formulas.

There is a gap - a tool is missing that would offer the flexibility, the ergonomics, and FRP capabilities of Excel, while also providing tools for ensuring data consistency and relational queries, and at the same time also making them easy for users to wield. It's a tall order.

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

#188

Post by nicoburns »

pintxo wrote: post #114
callamdelaney wrote: post #105

Earlier quoted context omitted.

It's not a user issue, it's a government incompetence issue. Who was the technical genius who thought 'right, we need to store thousands of lines of critical and important data, I know! Excel!'

Have you every worked in a large enterprise? I have personally tested an Excel based credit rating tool, to be rolled-out globally by a major financial institution. The fact that one should not do this, is by no means a reason not to do it.

I once created a very modern mobile offline-capable web app for a large enterprise company that was powered by APIs backed by excel. Luckily we didn't have to deal with those directly, but it was a bit of a pain for the poor guy doing th backend.

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

#189

Post by hardlianotion »

bartread wrote: post #89
CamperBob2 wrote: post #21

Earlier quoted context omitted.

This. We need to drop calculus, even geometry if we have to, in order to make room in the curriculum for statistics. Statistical illiteracy kills.

When I did my A-level in maths the course was half pure and half applied maths. The pure maths course was mandatory, but for the applied side you had the choice of either mechanics or statistics. At the time I was much more interested in physics, and was considering it as a degree course but, even if I'd done that, statistics would have been far more useful to me. I certainly wouldn't choose to drop geometry or calcu…

There's never been a better time to pick up some stats than now.

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

#190

Post by bitlevel »

TwoBit wrote: post #161
bitlevel wrote: post #91

Excel is NOT a database. I don't understand the people who use it as such (and I've come across many who do). Then they're surprised when it all goes tits-up.

It's hard to say from here what would be the best thing for them to use, but simply using a modern spreadsheet format instead of the ancient xls seems fine for interchange.

There's not much to stop the devs from using open source tools to create a PHP web frontend and db backend for this data - and secure it as well.

Heck, if they still need to export, they could do that from the data too.

Sure - use Excel for POC, but get that DB backend up pronto.

Post reply on HN