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

441–450 of 834 posts

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

#441

Post by Tistel »

bencollier49 wrote: post #133

Good lord. Old XLS files. I'd love to have seen how this was concocted.

Old code is everywhere. I once worked at a health tech startup that was integrating with a very successful existing company. All their code was in Turbo Pascal. We were shocked. The funny thing is that their code worked great and even had a debugger. Our code was ultra modern typescript micro services and we debugged with prints and logs. Their product looked dated but was superior in every other way. My guess here is that XLS is a simpler format and the devs found an NPM package that could handle XLS but not the newer formats.

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

#442

Post by syshum »

konjin wrote: post #422
gxs wrote: post #334

Earlier quoted context omitted.

Not to mention the "system" solution will have to go through its own debugging and will undoubtedly have bugs in it for some time after initial release. On top of that, there is STILL room for human error as a user is still the one actually inputting data and designing the experiment/data flow. I'm going to get mauled on this forum given the audience, but parent comment reeks of the technical elitism on this forum an…

The same way that engineers saying you shouldn't use dung to build houses are guilty of architectural elitism. Excel is the wrong tool because it mixes presentation and data, to the point that geneticists had to change the names of genes so their spreadsheets would stop turning them silently into dates and then destructively changing the original data[0]. >Excel works great - I've personally built some very complicat…

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

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

#443

Post by sudosysgen »

TeMPOraL wrote: post #402
sudosysgen wrote: post #392

Earlier quoted context omitted.

If you don't already know, Octave is a very good free and open source Matlab compatible interpreter and solution. Was of great use in my time in college.

Back in the day, it wasn't "very good", at least not in terms of getting it to work. That RK4 in Excel happened because I got extremely annoyed at Octave.

Were you on Windows? Octave really, truly shines as a command line tool on a Unix OS. The GUI is ok on Linux tho.

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

#444

Post by konjin »

TeMPOraL wrote: post #316
croes wrote: post #280

Earlier quoted context omitted.

Nothing wrong? Excel autoconverts values if it thinks it knows the data type of a column and Excel treats 1900 as a leap year. Genetic researchers changed the names of genes to prevent auzoconveesion errors.

And JavaScript autoconverts all your numbers to floats, introducing errors every time you touch them. Meanwhile most other popular languages will happily wrap your fixed-size integers around on overflow, or silently perform integer division with truncation when you don't append a decimal fraction part manually to any of the numbers. Point being, all tools come with strange caveats that one needs to be familiar with.…

Yes, and JS is considered a shit language by everyone who isn't a JS fanboy. What's your point?

Most other languages come with libraries for arbitrary precision arithmetic where people who know what they are doing^tm can get the right answers.

>A nice feature of Excel is that the caveats are visible, because unlike writing code, Excel is reactive and interactive.

Unlike any programming language I know I can change the results in excel by changing the presentation of the data.

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

#445

Post by syshum »

TeMPOraL wrote: post #316
croes wrote: post #280

Earlier quoted context omitted.

Nothing wrong? Excel autoconverts values if it thinks it knows the data type of a column and Excel treats 1900 as a leap year. Genetic researchers changed the names of genes to prevent auzoconveesion errors.

And JavaScript autoconverts all your numbers to floats, introducing errors every time you touch them. Meanwhile most other popular languages will happily wrap your fixed-size integers around on overflow, or silently perform integer division with truncation when you don't append a decimal fraction part manually to any of the numbers. Point being, all tools come with strange caveats that one needs to be familiar with.…

>>But let's not pretend you don't need to pay attention if using a tool you're not proficient with.

Decades of experience working in Office Environments tells me many many many many many people do not pay attention period, their experience with the tool is irrelevant.

My experience also shows that inexperienced people do not know WHAT to look out for, especially in excel so they are often more susceptible to mistakes.

This really comes into play when working in larger organizations where excel workbooks are passed around from person to person, often existing for years or decades at a time where people using the workbook are separated from the person that created the workbook.

Try figuring out an excel spreadsheet created 10 years ago by people no longer with the company that several data links importing data from all over the place.....

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

#446

Post by makomk »

ImaCake wrote: post #305
jonplackett wrote: post #288

Earlier quoted context omitted.

Come on, this is a government department of a first world country. They have databases for all sorts of things. They're not using excel for any other NHS databases, or the terrorism watchlist. There are plenty of people capable of making a good decision here. 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.

Yep. If the UK is anything like Australia, they will have a small army of contractors who specialise in building SQL databases for various important and pointless requirements for the government. These contractors are used to working on giant, sensitive data, databases with insufficient allocated time. It should have been pretty much trivial for the UK govt to solve this.

I'm pretty sure the UK does have actual software with actual databases backing up test and trace, some of which was outsourced - in fact it looks like our opposition party and opposition media are trying to blame this failure on outsourcing, even though that has no foundation in reality, mostly because this plays well with their supporters for reasons too tedious and political to explain. Unfortunately, this happened at a kind of interface between different systems designed to take in results from both the Pillar 1 NHS testing and Pillar 2 Lighthouse testing for the general public, deduplicate them, and spit them out to the contact tracing system and the government dashboard. These are seperate systems run by seperate organisations for various not-so-interesting reasons to do with the structure of the healthcare system and the history of Covid testing in the UK.

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

#447

Post by nobody9999 »

nobody9999 wrote: post #435
tqi wrote: post #319

Earlier quoted context omitted.

Your scenario makes it seem like these things are the result of incompetence or stupidity by people who aren't "decent coders". The reality is that for most use cases excel+manual processes are perfectly adequate/have little startup costs and what you're suggesting is grossly over engineered/require immense initial investment. The real problem is if/when scope begins to increase these processes are sufficient until t…

>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. That's a good point. I'd also add that, at least in the US, we don't have any sort of national standard for such things. Each public health department (and there are >1,000 of them in the US) has their own set of processes and procedures. I'd…

My apologies.

I didn't read the article posted until now, and I see it's about issues in the UK and not the US (I was confused, as we had issues with data reporting in the US as well).

My comment was focused on the decentralized US public health model and not on the UK's.

My apologies for injecting analysis of a different problem into this discussion. As such, my prior comment should probably be down-voted.

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

#448

Post by II2II »

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…

A good part of the problem could be the timelines involved. With something like Excel, you have a large number of people who can put something together in a short amount of time and perform the needed analysis.

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.

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

#449

Post by rvba »

konjin wrote: post #422
gxs wrote: post #334

Earlier quoted context omitted.

Not to mention the "system" solution will have to go through its own debugging and will undoubtedly have bugs in it for some time after initial release. On top of that, there is STILL room for human error as a user is still the one actually inputting data and designing the experiment/data flow. I'm going to get mauled on this forum given the audience, but parent comment reeks of the technical elitism on this forum an…

The same way that engineers saying you shouldn't use dung to build houses are guilty of architectural elitism. Excel is the wrong tool because it mixes presentation and data, to the point that geneticists had to change the names of genes so their spreadsheets would stop turning them silently into dates and then destructively changing the original data[0]. >Excel works great - I've personally built some very complicat…

This geneticist thing is a complete misunderstanding. Excel is tuned to autoconvert things that look like dates to dates. Best would be to be able to disable this, but Microsoft research shows that more users need it.

Meanwhile scientists use Excel for lots of stuff.. since it works. It has a not nice "feature" of those conversions.

So option 1) throw away the tool you use every day 2) change the gene name

Option 2 makes sense, although everyone would be more happy if Microsoft gave an option to toggle that auto-conversion off [there is option to properly load CSV files but people dont use it, since it takes more time...]

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

#450

Post by cabalamat »

lordnacho wrote: post #385
TeMPOraL wrote: post #369

Earlier quoted context omitted.

My 4th order Runge-Kutta ODE solver I did in an afternoon in Excel the other day, because I didn't want to pirate Matlab to do a homework assignment, begs to differ :). Not to mention, the entire financial industry and many (most?) small businesses. Lots of people use Excel for various types of math, and it works fine, if you know what you're doing . Excel not being idiot-proof doesn't mean it's not usable. And that'…

> Not to mention, the entire financial industry That IS the dumpster fire. Trust me, the reason I know is that I used to be that guy who thought Excel was a great tool. I built derivatives spreadsheets, backoffice spreadsheets, trading systems with realtime data, all sorts of crap in Excel. Really, it's Stockholm Syndrome. People who previously had zero computing power at their disposal think they've found the hammer…

> It's just that you get a lot of "coding is a thing I have to do in order to get to some target", and so people think that once they've finally bashed out their spreadsheet, they've figured it all out.

I have a friend who wrote a 641-line long bash script to automate a web site. It doesn't use subroutines anywhere, the body for the program is a 550 line long loop with multiple if statements and loops inside it.

He thinks the program is maintainable and easy to understand because he didn't have particular difficulty writing it.

Post reply on HN