Live data from Hacker News

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

bbc.com

531–540 of 834 posts

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

#531

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…

One part is knowing how important correctness actually is in your case. A lot of people on HN are perfectionists, and there's a lot of rhetoric going around our industry about avoiding errors and not breaking things; in reality business processes have always had to be error tolerant (because until recently they were always done by humans) and a quick solution that breaks sometimes is often more valuable than a slow solution that doesn't break.

Another piece is pervasive auditability. Any result should come with an explanation of where it came from; "Bob did some calculations in his head and he reckons the answer is 7" would be acceptable for some kinds of business decisions, while for others it needs to be more like "Bob followed the procedure specified in the XZY institute handbook, page 456". Somehow we've let all that go out the window, partly because people who don't understand computing are managing organisations that deeply depend on it. But you don't even really need computer literacy; what you do need is the same kind of scepticism that you'd apply to any other piece of work.

Managers need to manage. Some of the problem is just people lacking the necessary skills (and a lot of that goes all the way to the top: the UK government doesn't have the wherewithal to hire skilled computer professionals because at every level the people on top don't have the skills to assess whether the people below them are any good), but a lot is a misplaced perception of computers as infallible.

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

#532
You'd be surprised to hear how many big organisations still use Excel for their reporting needs. I used to worked at a company with 35000 employees. Their payroll reports were generated using a MS Access database and reported using Excel. It was a massive manual job every week as they'd weekly, fortnightly and monthly payroll cycles! It's very easy to lose files and/or make an error in the calculations. But nobody bothered to change it.

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

#533
post #532

You'd be surprised to hear how many big organisations still use Excel for their reporting needs. I used to worked at a company with 35000 employees. Their payroll reports were generated using a MS Access database and reported using Excel. It was a massive manual job every week as they'd weekly, fortnightly and monthly payroll cycles! It's very easy to lose files and/or make an error in the calculations. But nobody bo…

We had a telco vendor use excel to manage all their configuration.

It was a weird dual source of authority system, where the excel configuration was used at install / upgrade time, but you could change the config after the install at runtime. So you had to merge the active configuration into excel, upload the excel document to some server that customers didn't have access to that turned the excel file into a config file, then you could use that config file to upgrade the system.

It was as problematic as you could imagine, lost configuration, lots of macros, etc. Eventually they made some improvements, but I left the industry so don't know what the current status is.

Enterprise / Telecom solutions at their best.

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

#534
post #492

While I am not in the habit of defending Microsoft, I think it should be pointed out that it wasn't using "Excel" that was the biggest problem, but using "13-years obsolete format for Excel". Not that there was any reason to be using Excel for this, but it's a bit unfair to blame Microsoft for something they never claimed XLS could do, and provided a much better capacity in XLSX 13 years ago. Again, it's hard to cry…

Sure, and I think at least the headline ("ill-thought-out use of Excel") avoids blaming MS or Excel itself. Ultimately tools are built for particular things, and if you choose to use a tool for something it's not built for, and it breaks catastrophically, that's on you.

No. Excel is definitely to blame. A simple error "cannot import/export more than 65k rows" would have done the trick. Not silently throw away rows.

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

#536

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…

Well, not exactly like non-Excel systems are bulletproof either. Entire Japanese stock market went down last week, and it's not like there was a flood of people on HN bemoaning that. At least with Reinhart and Rogoff et al you have a responsible party. As opposed to 'nameless machine failed, and nameless backup machine also failed, and now it's in JIRA so don't worry about it'. https://www.nytimes.com/2020/09/30/busi…

Reminds me of this - https://www.reddit.com/r/programminghorror/comments/fv7qi7/b...

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

#537
"Developers picked an old file format to do this - known as XLS.

As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of."

"Experience is the name everyone gives to their mistakes" --Oscar

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

#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 then uploading them to us because what they upload could be missing critical data. Excel interprets a number and then formats it as a number, but in healthcare, 10 digit numbers are really strings. Unless your IT team has created an Excel extension and had it preloaded onto your local copy, Excel will remove leading zeros and there isn't a way to get them back if you save/overwrite.

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

#539
post #153
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.

Excel removes leading zeros from numeric fields no matter what you do and you cannot turn it off. This is a huge problem in healthcare where many patient identifiers have leading zeros

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

#540
post #200

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.

Sure it does but it doesn't give a f*k - once you open it in excel, it does its' own formatting and will save that incorrect formatting even if you save the CSV as a CSV
Post reply on HN