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

471–480 of 834 posts

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

#471

Post by dmitryminkovsky »

TeMPOraL wrote: post #369
srtjstjsj wrote: post #345

Earlier quoted context omitted.

Excel never works for math, at any scale. It's a constant trashfire of "looks fine" until it shows a result someone doesn't want, and then an error is found that changes results and the cycle repeats. Excel is nice for visualizing and browsing data you already have, and informally searching and sorting for hypothesis generation.

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'…

I really agree with this. Excel/spreadsheets are great and should be used whenever possible instead of some bespoke solution that will always be worse unless millions and millions of dollars are sunk into it, and even then, will probably be worse, given the state of software development in 2020.

Like all tools, you have to know how to use Excel. If this is the only error that’s come up, well, so many other problems would have come up with a bespoke solution that Excel is still miles ahead in my book

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

#472

Post by zmmmmm »

A lot of trashing on Excel but I kind of think this particular instance isn't really the essence of the problems with Excel. Perfectly reasonable programmers get this wrong all the time (ever heard of buffer overflow anyone?), and the issue of small test data being unrepresentative of large test data is something everybody has to contend with. If there is a real problem with it I would say it is the presumably silent nature of the truncation which allowed the error to propagate.

The real horrors of Excel come from things like auto-conversion of column data (text, numbers to dates etc), off-by-one errors in copy and paste, overwriting forumlas in cells, sorting of columns that doesn't capture all the rows, etc. These are all problems that are engineered into the user interface of Excel. It's like putting a tripwire at the top of your stairs and just expecting people to step over it day in and day out. It's basically inevitable somebody will fall down the stairs.

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

#473

Post by rvba »

konjin wrote: post #455
rvba wrote: post #449

Earlier quoted context omitted.

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 m…

So I'm wrong because I'm right? Have you looked at Stockholm syndrome?

The generic tool for offices is so good at everything that geneticists also use it; they change a name of a gene to make own life easier?

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

#474

Post by dmitryminkovsky »

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…

Well why are these people wrong? You had a thing going but you didn’t complete the thought: if Excel works and they can now calculate “anything” without developing software and deploying containers, where is Stockholm Syndrome? Why are they blind? What should they use in finance? Jupyter notebooks?

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

#475

Post by konjin »

icedchai wrote: post #466
konjin wrote: post #453

Earlier quoted context omitted.

Because excel couldn't authenticate into our internal server and it was good enough.

Okay, so you write a script to take the data from your internal server and post it somewhere that Excel can access. You don't use Yahoo.

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

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

#476

Post by konjin »

sokoloff wrote: post #460
konjin wrote: post #453

Earlier quoted context omitted.

Because excel couldn't authenticate into our internal server and it was good enough.

Was it good enough? I would argue that if it enabled your firm to make $12M before being the proximate cause of losing $10M, and the alternative was "we don't win $12M nor lose $10M because we can't afford the project" then I agree it was good enough.

No, which is why I was hired to replace it.

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

#477

Post by konjin »

icedchai wrote: post #464
cameronh90 wrote: post #461

Earlier quoted context omitted.

You'd be surprised at how common that was. I've known two other financial orgs that scraped Yahoo as a critical part of their investment system...

I know individuals / hobbyists who do this, which is fine. Financial orgs should know better, especially if it is actually "critical." How did this get through any sort of review? How do you know Yahoo is authoritative?

Hobbyists are usually more diligent than professionals because they care. If anyone cared about the product it wouldn't have been a spreadsheet.

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

#478

Post by dmitryminkovsky »

TeMPOraL wrote: post #391
lordnacho wrote: post #385

Earlier quoted context omitted.

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

You bring up some very good points. But to those, I have a question: what is the alternative? Both now (for UK healthcare), and in general? The "people who previously had zero computing power at their disposal" may be wrong in thinking "they've found the hammer that solves all problems" - but there's literally no other hammer available for them. They're not programmers, they won't write their own software (nor would…

Excel is an excellent hammer. A spreadsheet is a system to develop pure functional graphs and to interact with them in real time. I spent lots of time on finance spreadsheets myself and also thought it was Stockholm syndrome, but when I transitioned to working on software I came to see exactly why spreadsheets are entrenched the way they are, enjoy seeing them used creatively, and would never wish bespoke software on finance people. And good for them, they seem to know better, themselves!

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

#479

Post by Reason077 »

askvictor wrote: post #414
Reason077 wrote: post #329

Earlier quoted context omitted.

Excel can handle the volume of data just fine. The (very outdated) .xls file format with its 65,536 row limit was the problem here.

I understood (different source; not sure what the true picture is) that the problem was with storing each record in a column. There is a limit of 16,000-odd columns. This would, as you say, not be a problem with Excel as such, but with the user(s) not being aware of the limitations (which is still the case in your scenario). I wonder how much the general problem has to do with mistrust of 'experts' so, hey, I can do…

I believe that was misreported. Someone wrote "columns" when they meant "rows".

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

#480

Post by dahart »

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…

Your point is incredibly good, and made me laugh too. It illustrates precisely why people, both technical and non-technical, frequently avoid choosing the massive scalable hammer factory system architecture that takes five people three weeks just to get rev 1, over the little tapper that gets the job done today with one junior developer.

From the technical standpoint of a decent (and probably only decent) coder, I think Excel is an amazingly sensible choice for technical projects for a bunch of reasons. I think decent coders reach for the database solution far too often, and waste ungodly amounts of time setting it up. Excel coming bundled with an amazing front end UI you don't have to write code for shouldn't be ignored. Sure it's error prone, sure it's a nightmare for data validation, sure it doesn't scale to Amazon sizes, but this little project will never get that big. (Yes, until, of course, it does.)

I used Excel as the frontend for artists to author and enter small databases for a game engine, for example, and it was leaps and bounds better, and cheaper, and easier to use, than the unmaintainable SQL nightmare, with a bunch of frontend and backend dev on top of it, that the decent code who proceeded me left behind. Excel even scaled better, up to the size of our studio (hundreds of people), compared to the other system that needed technical artists to know how to use it. No question whether it would crumble if we were talking thousands of people or more. Which is why it happens so often. :P

Post reply on HN