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

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

#811

Post by pintxo » Tue, Oct 06, 2020, 7:47 PM UTC

sails wrote: post #132

pintxo wrote: post #114

Earlier quoted context omitted.

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.

Would be interested in hearing more about that Excel tool.

It started very innocent: Some consulting company apparently was tasked to create a prototype of the credit rating algorithm. Quite appropriately, I would say, they did so in Excel. As you can easily tweak the logic, show input, intermediate results and output. And all can be manipulated by the client who does not need to know a thing about programming to do so. Great.

From there it went downhill: Apparently someone up the hierarchy thought "Wow, that's 80% of what we need, let's just add a little UI and ship it"

They added a UI, but not as a VBA UI as you might hope. No, they did the whole UI in different worksheets. Storing any intermediate data ... also on worksheets. Long story short, it was a mess, and a slow one for that.

Oh, did I say this was a multi-lingual application?

Production rollout was on Jan 2nd, Dec 31st around 4pm I found a bug in the other language, on the one machine which had the this language configured. I believe this was the only time I ever saw a programmer literally run down the office to that machine to debug this issue.

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

#812

Post by pintxo » Tue, Oct 06, 2020, 7:54 PM UTC

nullsense wrote: post #300

pintxo wrote: post #114

Earlier quoted context omitted.

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.

Fun fact: Toshiba has software that generates entire CRUD app skeletons driven 100% by excel spreadsheets. You fill out object definitions in excel spreadsheets with property names and data types etc. It's like an extremely uncool long lost cousin of rails -g Source: Got one of their engineers to show me after I heard about it and had to know if it was real. Anyone who's ever worked with or in Japan knows just how fa…

I've (miss)used excel as a web-service testing platform once. The test data (input/expected output) was anyway in the Excel sheet, so why not. We did not have other software available at the time, so Excel+VBA it is.

It was one of my nicest testing gigs ever. A test session would take only minutes, all results documented to the t. And fiddling around with the test data was so easy. Would be interesting to know if this thing is still in use.

Not sure the customer liked it that much, I regression tested the 6 previous - still running - versions of the service, something no one had cared to do for years. We found bugs both in the Spec and in the Code for nearly all old versions...

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

#813

Post by ineedasername » Tue, Oct 06, 2020, 10:06 PM UTC

zentiggr wrote: post #764

ineedasername wrote: post #734

Earlier quoted context omitted.

Excel supports a variety of data types other than numbers and includes many built in functions for dealing with non-numeric data. Whatever it's history, it is not accurate to say that it is only for numbers.

Regardless of its _additional_ support for other types, those are only in support of its primary role as a spreadsheet. It is designed around the concept of plugging in _numbers_ and calculating with them. Everything else is to make those calculations well notated and human friendly. Any other use of Excel is bending it into a role it wasn't intended for, and user beware. And it is all too easy to just go there since…

If notation were the sole purpose there'd be little need for anything but row and column labels or simple string input. Instead there's a full set of string manipulation tools, embedding of other objects, charts, images, drawings etc.

You are taking the application as it existed 35 years ago and saying it must still be that thing, yet it has had 35 years to evolve far beyond that. Microsoft itself, when it talks about Excel, talks about using it to organize "data", not just numerics data. It has become a more general purpose tool.

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

#814

Post by FabHK » Tue, Oct 06, 2020, 10:34 PM UTC

nickff wrote: post #409

FabHK wrote: post #401

Earlier quoted context omitted.

> I think you're overstating [...] the paper's impact Krugman [0] called it "surely the most influential economic analysis of recent years" and adds that it "quickly achieved almost sacred status among self-proclaimed guardians of fiscal responsibility; their tipping-point claim was treated not as a disputed hypothesis but as unquestioned fact." However, indeed, he cautions that "the Reinhart-Rogoff fiasco needs to b…

Krugman's editorials are polemics, not economics. I don't think anyone should trust the content of an opinion piece which purports to describe the logic, motives, and rationales of ideological opponents, as was the case in your linked article.

> Krugman's editorials are polemics, not economics.

The discussion here is not about economics. The discussion is about the impact of the (flawed) Reinhart-Rogoff paper, which one commenter thought was overstated.

> I don't think anyone should trust the content of an opinion piece [...]

Krugman is of the opinion that said paper had 1. a big (and 2. a deleterious) impact. For the first contention (which is, again, the pertinent topic), you don't have to rely on Krugman's expert assessment, though, you can look at the evidence: the infamous 90% inflection point had been quoted all over the place (as for example in the WaPo editorial cited by Krugman).

Lastly, what makes an article describing "the logic, motives, and rationales of ideological opponents" intrinsically untrustworthy?

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

#815

Post by kausban » Tue, Oct 06, 2020, 11:45 PM UTC

SCNP wrote: post #795

throwaway_pdp09 wrote: post #783

Earlier quoted context omitted.

Oh yes please. At least you would know what's happening and be in control of it "Hey, is that a date? I bet that's a date!" - Aaaargh Noooo!

YES!! I seem to remember a few versions of Excel ago there was a way to import data and set the data type for each column in a preview window to see what it would look like. And to your point, it's very easy to select an existing column in Excel and change the data type anyway. So why not just make everything a string by default?

Maybe you recall the CSV import window in libreoffice

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

#816

Post by stjohnswarts » Wed, Oct 07, 2020, 1:01 AM UTC

Maha-pudma wrote: post #585

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…

Every single problem I've ever had with excel stems from excel trying to be too clever. Nearly all my problems with importing data into it could be solved if excel left everything as is until I asked it to format values a certain way.

And the people who make excel will just tell you "excel is not a database" lol

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

#817

Post by stjohnswarts » Wed, Oct 07, 2020, 1:08 AM UTC

rustybelt wrote: post #762

salamander014 wrote: post #726

Earlier quoted context omitted.

You are right on the cause of the issue. But the software does not HAVE to guess the type. If I paste something into Excel, I want what I pasted to be what's in Excel. Changing the type and inadvertently affecting the data isn't something any data software should do by default. And the fact that OP said they can't turn it off means that this is beyond a design mistake, it is a FEATURE. If Excel took itself seriously…

You can format the column as text before you paste or you can load data from text and set the types for every column. Excel has features to handle all of these issues. I think it's biggest problem is that it's just too easy to use. It will allow novices to do all sorts of things with their data without providing guardrails for common mistakes.

Yeah it does, libreoffice has methods of recognizing numbers that are to be interpreted as strings as well. This is user error and the programmer trying to blame that on the program rather than it being a process issue. The spreadsheet can't read your mind

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

#818

Post by stjohnswarts » Wed, Oct 07, 2020, 1:11 AM UTC

rutthenut wrote: post #689

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…

Interesting to see just how many responses to this real-world problem are suggesting that people rewrite CSV files to work with Excel. They clearly do not understand system integration and the use of CSV text files for data interchange between multiple systems and application. Hey, JSON and Javascript libraries are the answer to that, eh There are already enough potential issues with CSV interpretation on wrapping st…

If you want it to be a string then put it in quotes. It's that simple. If the user is too dumb to slow down and look at the import options it is not excel's fault, it's a process issue. Excel can't read a cell and then read your mind and extract the correct option.

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

#819

Post by stjohnswarts » Wed, Oct 07, 2020, 1:16 AM UTC

mewpmewp2 wrote: post #733

lr wrote: post #731

Earlier quoted context omitted.

First off, I “hate” Excel, but only because people misuse it. It, like so many other products of its kind, are for numbers! Yet I would be hard-pressed to find someone actually using it for numbers. People absolutely insist on using it as a generic table system for holding non-number data. I recently had to show a 20+ year Excel-using fanatic how to import data from a CSV file so that they could select as type Text c…

As a lay-person, what would you use for non numbers tabular data?

Excel is fine, but keep the data as xls and you won't have problems like dropping leading zeros. As soon as you convert to a simpler file format like cvs, you will likely lose data unless you are very careful in both export and import.

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

#820

Post by stjohnswarts » Wed, Oct 07, 2020, 1:19 AM UTC

vertere wrote: post #599

sheetjs wrote: post #567

Earlier quoted context omitted.

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

Perhaps by "correct way" you meant "dodgy hack to make Excel happy and risk breaking more sensible implementations"? Excel may predate the RFC but AFAIK MS didn't invent or coin the term CSV, so you can't just say whatever Excel does is correct. The RFC is loose because of nonsense like this, it doesn't mean it was ever a good idea.

How in the world is Excel supposed to know which fields you want to be numbers and which to be strings? CSV doesn't have that info built in unless you surround the number with quotes and you select the right process. Excel isn't just a CSV importer, it reads all sorts of files, and it needs some help if you expect it to work. What ever happened to process and a sense of responsibility and craft in your work?