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

https://www.bbc.com/news/technology-54423988

by cjlm • 6 years ago

1041 781 6 years ago

It's unlikely this will have had much impact since most reported cases were only a few days late but come on guys, really?

Using an Excel sheet as a database? In week 1, that would be "not great" but could be accepted as being a fast solution that everyone could work with. This far into a pandemic I think we can expect a little more professionalism in data handling.

mytailorisrich2• 6 years ago

To be fair, there hasn't been any massive irretrievable data loss yet, so not too bad.

https://www.youtube.com/watch?v=K_FrQnQv0Vw (probably an accurate depiction of what's going on in Whitehall right now, though)

rgallagher27• 6 years ago

At this point I think I'd rather have Peter Capaldi acting as Malcolm Tucker running Whitehall over the shower of incompetent people we have now. At least he might get something done in between the flaying

londons_explore• 6 years ago

People use excel sheets as databases all the time because it doesn't require any specialist knowledge to build or use.

Sure, we techy types prefer a proper database with a web frontend and an API, but that requires significantly more skill to build than an excel file.

No database has the flexibility and flat learning curve that excel has.

Until a database manages to meet these goals, it remains a good tool for certain usecases.

We as engineers should be working towards making a real database thats just as easy to use as excel, and only then can we complain that people are using excel sheets for this kind of stuff.

chii• 6 years ago

I'm surprised that MS Access didn't take this kind of niche.

I know it's a pretty shitty program to use - excel is much more user friendly. If MS had made excel, but have all the features of access in it...

DanBC2• 6 years ago

People use excel sheets as databases all the time because it doesn't require any specialist knowledge to build or use.

Test and Trace is a multi billion pound programme of work, involving huge companies (eg, Serco) interfacing with national governmental bodies (Public Health England).

We've had months to get something in place. One of the reasons organisations like Serco is used is their apparent expertise with IT.

Mvandenbergh4• 6 years ago

Sure. That's why I said it wouldn't bother me if this was in the first few weeks of the pandemic or for one-off ad-hoc analysis. I actually use Excel for that all the time, including for tasks where technically a db is the correct solution. Working is better than perfect and everyone knows (sort of) how to use Excel. To still be using it in production this many months in is not acceptable. Someone should have taken that Excel based workflow and set it up to run through a database, they had the time to do it.

adam-a• 6 years ago

This is a very good point and a shame to see it being downvoted. I have been wishing there was an accessible database-ish software for doing my accounts, as the current mega-spreadsheet I use is pretty hairy and none of the specialised apps quite fit the bill. The closest thing seems to be Airtable, which while nice, is quite expensive.

Recently I was visiting hospital quite a bit (ante-natal) and witnessing the horror of the massive NHS form filling software the nurses and midwives have to use. It's clunky and sprawling but one of the main sins I think is it's lack of adaptability. In the old days of pen an paper, a new form could be written, or adapted, and photocopied easily, and on-site. Now if a new field needs to be added or a process is changed it needs to go (I imagine) to some centralised IT development office and fed into a ticketing system where it might get changed in a few weeks or months. There's no room to quickly adapt at a ground-level, so you end up with things like this excel sheet problem.

LorenPechtel• 6 years ago

There is also the fact that Excel has some rich editing tools. I have actually seen a system where most editing was done by exporting the relevant items to a spreadsheet, making your changes and reimporting.

TeMPOraL19• 6 years ago

Don't forget the formulas! Excel is essentially a functional reactive programming REPL. No RDBMS I know lets you easily define a table in which cells in a row depend on values from previous cells in a row, and keep this as an invariant.

People working on Excel replacements need to remember about this aspect of spreadsheets too.

DanBC2• 6 years ago

I agree, but I'm genuinely glad they're not[1] using fax machines.

[1] they almost certainly are using fax somewhere.

brigandish• 6 years ago

It's probably Japan but that would be the least of the egregiousness.

[1] Japan's COVID-19 Reports - 140KBs of Unadulterated Incompetence https://stdio.sangwhan.com/wtf-japan-covid-19-report/

[2] The previous HN discussion https://news.ycombinator.com/item?id=22728674

avian• 6 years ago

Using an Excel sheet as a database

How do you know it was Excel? I see no technical details in the posted article except "some files containing positive test results exceeded the maximum file size".

benaadams• 6 years ago

https://twitter.com/MaxCRoser/status/1313046638915706880

The reason was apparently that the database is managed in Excel and the number of columns had reached the maximum.

mellosouls4• 6 years ago

I noticed this as well, and started worrying the various news items that were citing Excel since had been reading HN!

I still haven't seen an authoritative source, or one that predates that comment.

But it definitely sounds possible.

chimprich• 6 years ago

It's unlikely this will have had much impact since most reported cases were only a few days late

I'm not sure if you're being sarcastic or not, but in case you're not, this has meant c.16k people missed from having their contacts traced. That's time-critical. Delays of any number of days are very bad. People will likely die because of this error.

This is safety-critical software. It should be stress tested and have sanity checks. It should be impossible for this to happen.

For something like this system, part of a track and trace system costing billions, to fail because of a file size error is just unbelievable.

throwmeaway_pls• 6 years ago

The WHO estimated 750 million cumulative infections on Friday. This error is not unbelievable because the system is just a bit of political theater. There’s nothing more we can do against an endemic human coronavirus. It’s time to move on.

Mvandenbergh4• 6 years ago

I was being serious. Distributionally, most of the cases were 1-2 days before the error was spotted. To be clear though, when I said "not much impact", I didn't mean that people might not die because of it, only that it wouldn't affect the large scale functioning (such as it is) of the tracing operation.

lini• 6 years ago

Excel has a limit of ~1M rows per sheet. Total cases in the UK until now are ~500K. So it seems they use 2 rows per case in the sheet.

Edit: oops, it seems they used columns for the cases and the ~16K column limit was hit when daily cases exceeded that.

ajsnigrutin• 6 years ago

Once you have a couple of thousands of rows in excell files, one file per day, possibly one per testing center.... possibly with inconsistent structure.... non standard date formats (even different in each row),... add to this the inconsistent namings ("Tested at" column with data: "Testing center 1, London", "TC1, London", "TC1 London", "TC1 LONDON",....), etc...

Yes, anything-sql or whatever would be better, but deciding between putting all the data into sql by hand, or "uhm... i'll think of something and do it tomorrow", me, being lazy, I'd pick the second option,...

...until i'd get shamed on here. Maybe even a few days after.

hdjdbtbgwjsn3• 6 years ago

Cleaning data is a major part of data engineering there are plenty of professionals capable of helping with this task.

learnstats2• 6 years ago

It is absolutely standard that everyone doing office work outside of high-tech jobs uses Excel for data handling.

It's not surprising that this reaches into government.

Even if your canonical database is done properly, there will be a clash here as soon as you share (or give anyone the ability to generate) CSV files that are too large for Excel.

makomk8• 6 years ago

Hey, at least the UK has automated handling of data! Parts of Germany managed to not tell people they'd tested positive for a week or two because their manual, Excel-based process for handling test results couldn't cope with the increased load from testing people returning to the country - and they're meant to be the competent ones we look up to, according to our press. (I get the impression that German experts have a rather different view.)

FerretFred• 6 years ago

professionalism in data handling

Nope. They appointed Dido Harding to cough manage it, and Matt Hancock oversees it. That should tell you all you need to know. I wonder if they were running Excel 2007?

231435359236• 6 years ago

I think it's more interesting on what happens to any analysis done with this incorrect data. There was a news article last week suggesting that the growth in COVID-19 cases was "levelling off" and I wonder in retrospect would this incorrect data account for such a position.

The article in question: https://www.bbc.co.uk/news/health-54387057

azalemeth• 6 years ago

Excel always feels like a bit of a curse of the world for me, and one that makes me feel like I'm going mad. I work partly in partnership with the NHS within my university, and get to see it being applied, completely inappropriately, with no ability to comment.

For me, I always feel like I'm living in some sort of parallel universe. Imagine living in a world where _you_, and a select group of others, know that there are better methods to drive a screw into a wall than punching it with your fist. However, 99% of people in the world punch screws into walls by hand, because they know how to use the only tool they have to hand (i.e. their hand). Their friends and neighbours do too. Their colleagues show them neat little tricks about how to fold your hand better to get the screw in with less pain, or more efficiently. The "proper" option -- drilling a pilot hole, using a self-tapping screw, understanding the difference between nails, bolts and screws, etc, or the concept of wall-plugs, is "too technical", "too complex", or requires "a large amount of infrastructure to fix a simple problem" [buying a drill & screwdriver, etc]. Sometimes people buy new walls with screws pre-installed because the thought of driving in those screws seems like an overwhelming technical burden.

You, and a group of similarly-minded nerds, understand just how stupid this is at times. You personally then spend the rest of your life watching people occasionally have horrific hand injuries from punching screws into walls and refusing the offer of screwdrivers. Others profit from selling vertical hard surfaces with a whole array of convenient screws pre-installed; or get employed teaching others the _very_ basics of screw-driving, or sell products containing all sorts of klunky hacks to cope with the fact that screws manually installed are at odd angles and tend to fall over randomly. The supplier of a popular brand of screwdrivers happens to be the world's largest screw manufacturer, but never seems to promote screwdrivers to people who currently punch them in with their fists. Instead, they sell them a subscription to screws, with the option for a very expensive pre-screwed wall-delivery service, built to your specification (that they call Dynamics for some reason…).

You, and a subset of your nerdy and educated friends recognise just how batshit insane the widespread use of punching-as-a-method-to-install-screws is, and occasionally laugh about it, mostly to distract yourself from the grim reality of buildings badly held together by punched-in-screws. Every so often a skyscraper-sized building collapses, [1] and it's realised in retrospect that the manually-driven-in-screws were not the right tool for the job. Still, people carry on banging in screws with their fists, and you increasingly realise that you have to -- somehow -- accept that in order to sleep at night without going insane.

[1] https://www.businessinsider.com/excel-partly-to-blame-for-tr...

duderific• 6 years ago

My dad (who is 79 but still working) proudly mentioned that he spent 11 hours putting together some charts in Excel for his client. Probably something that could be done in an hour or so with an online tool.

gridlockd6• 6 years ago

Using an Excel sheet as a database?

You wouldn't (want to) believe how incredibly common that is.

OJFord5• 6 years ago

Overblown, IMO - only the useless absolute figure is affected, so it just creates another reason it's useless.

Unless positive result files are more likely to exceed the file size limit than negative ones, of course, which I haven't heard suggested is the case.

Edit: Perhaps I should clarify that I'm not suggesting it isn't a cockup. I just don't see it as a massive scandal that renders the data useless - it just decreased the sample size. What particularly wound me up was a Radio 4 presenter this morning objecting to the guest (I missed the start, I'm not sure exactly who - a female public or civil servant) calling it a 'glitch'. ('Really?! Really! 16000 missing cases is a glitch?!') Well, yes. Glitches can have minor, severe, catastrophic, or no consequences.

pjc504• 6 years ago

Care to explain why you think the absolute figure is useless?

OJFord5• 6 years ago

What are you going to use it for?

Mostly it seems to be used for comparing differently populous countries.

iso16316• 6 years ago

It's not much good for comparrison with March when tests were only done in hospital, and I see a lot of people doing that

Most people who had covid by August (which was at least 6%, or 4 million, in the UK according to antibody tests) caught it between start of February and end of April. That's at least 3.5 million over 90 days, or 40k a day on average. Peak was likely double, maybe even treble that, given lockdown on 23rd of March dramatically cut infections.

We're likely testing somewhere in the region of 50% of actual cases. There's the non-symptomatic cases where about equal to the number of symptomatic cases, so double the absolute number -- some with symptoms will refuse to be tested because they don't want to miss work, some without will be caught by contact tracing, those two groups probably cancel each other out.

As such I'd expect 50k/day to be the "March equivelent" - with doubling every 10 days that means another 2 or 3 weeks.

It's not just about absolute cases though. Cases have been doubling roughly every 10 days, as have deaths. Deaths lag cases by 2-3 weeks, so I'd expect deaths in 20 days to be continuing to double even if we all stayed in an isolated booth from now.

Ultimately the concern is we're heading into flu season when hospitals are stretched, and cases, hospital admissions, and deaths are all increasing.

Hopefully flu season will be milder due to social distancing and due to more vulnerable people having been killed off, but either way we need to get a grip soon. Or just abandon any pretense of trying to stop its spread.

summner• 6 years ago

Actually not only that. It affected contact tracing, and people exposed to infected patients were not informed to self isolate. Possibly leading to extra spreading of the virus.

pjc504• 6 years ago

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 resign after a cyber attack revealed the details of 4 million customers. A member of the Conservative Party, Harding is married to Conservative Party Member of Parliament John Penrose and is a friend of former Prime Minister David Cameron. Harding was appointed as a Member of the House of Lords by Cameron in 2014. She holds a board position at the Jockey Club, which is responsible for several major horse-racing events including the Cheltenham Festival. "
"In May 2020, Harding was appointed by Health Secretary Matt Hancock to head NHS Test and Trace, established to track and help prevent the spread of COVID-19 in England. In August 2020, after it was announced Public Health England was to be abolished, Harding was appointed interim chair of the new National Institute for Health Protection, an appointment that was criticised by health experts as she did not have a background in healthcare."

mytailorisrich2• 6 years ago

For those not in the UK, Dido Harding was already on front pages a couple of weeks ago when she was grilled by a Parliamentary committee about the shambolic Covid testing organisation and her reply was "no-one could predict such a strong demand for Covid tests"...

As the NY Times said yesterday, sometimes it feels like "Britain is operating without adult supervision".

spzb• 6 years ago

She was also CEO of ISP TalkTalk when they had a massive personal data breach and told an investigation that she didn't know if the data was encrypted or not.

https://www.msn.com/en-gb/money/other/people-want-to-know-wh...

throw_m239339• 6 years ago

I mean let's call a cat a cat: this is cronyism thus corruption. Unfortunately, voters never care about these immoral practices. When a social system/administration is dysfunctional, corruption is often the first cause of these disfunctions. The result of corruption is always sheer incompetence.

glandium• 6 years ago

OT: are you French? AFAIK, calling a cat a cat is a French idiom. The English equivalent would be calling a spade a spade.

pjc504• 6 years ago

I think the voters do care, but can't keep track of the details and only know what the media choose to focus on. This is why Donald Trump's "drain the swamp" pitch was so effective, despite the reality of the far worse corruption in his campaign.

Voters want less corruption, but their own tribalism leads them to unhelpful positions of "everyone is corrupt", "none of my tribe are corrupt", and sometimes both of those at once.

sgt101• 6 years ago

She holds a board position at the Jockey Club, which is responsible for several major horse-racing events including....

I'll point out that Matt Hancock is MP for Newmarket - a centre for horse racing and associated businesses.

Mvandenbergh4• 6 years ago

While that is true, I have a little difficulty imagining that Harding or anyone else senior was involved in the process of shoveling around spreadsheets. As the overall leadership she obviously has the accountability but just saying, "oh what do you expect when you put a crony in?" ignores the reality that a number of people on an operational level had been doing this for months and nobody seems to have stopped it.

makomk8• 6 years ago

The latest news is that apparently this was a problem with Public Health England's internal processes anyway, not NHS Test and Trace or any other kind of outsourced service: https://www.bbc.co.uk/news/uk-54422505 I don't think that even falls under Harding's responsibilties.

kevin_thibedeau• 6 years ago

Day one on the job of CEO should be requesting input about systemic organizational and operational problems. Both from your direct reports and from the underlings who actually know what is broken.

vanilla-almond• 6 years ago

I have not yet watched this programme from the BBC but it claims the NHS Test and Trace system is a chaotic mess. In other words, as shambolic as everything our Conservative government has done over the pandemic.

Panorama - Test and Track Exposed:

"Panorama hears from whistleblowers working inside the government’s new coronavirus tracking system. They are so concerned about NHS Test and Trace that they are speaking out to reveal chaos, technical problems, confusion, wasted resources and a system that does not appear to them to be working. The programme also hears from local public health teams who say they have largely been ignored by the government in favour of the private companies hired to run the new centralised tracking system. As Panorama investigates, it has left some local authorities questioning whether local lockdowns could have been handled better or avoided altogether."

https://www.bbc.co.uk/iplayer/episode/m000n1xp/panorama-test...

dang6• 6 years ago

We detached this subthread from https://news.ycombinator.com/item?id=24685998.

Silhouette• 6 years ago

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.

social_quotient• 6 years ago

Wish we knew more details.

Maybe helpful for people that don’t know... Xls excel has a 64k row limit. Newer xlsx has 1mm row limit.

Or maybe they were putting people in by columns and hit the 16.3k column limit?

iso16316• 6 years ago

Or maybe it wasn't excel at all.

One number the UK government doesn't release is how many people are tested. They have spent the summer championing "testing capacity", which we found out at the start of September was a lie. They release the number of tests done, and the number of people returning positive. They don't release the number of people done -- if you're tested twice, that counts as two on the number of tests, but one on the number of people.

makomk8• 6 years ago

The trouble is, that entire narrative about the testing capacity being a lie because of secret leaked figures about the number of people tested was itself a massive lie.

For context, the Government publishes a stat for the number of people tested for the first time ever each week (used to be daily, but that was dropped in part due to people misusing it). Certain publications and politicians - starting I think with the Times - pushed the narrative that this was the real number of people being tested, and that the reason it was so far below the stated capacity was because the real capacity was far lower than the Government claims: https://archive.is/n3Yku This was bullshit on multiple levels - there are several important uses of testing, like routine screening of NHS frontline staff and hospital patients being admitted and discharged, that result in people being retested who've been tested at some point previously, and those obviously use actual testing capacity on people who haven't been tested that day or week. (Testing two samples from the same person in short succession, on the other hand, is unusual and not general policy.) Not only that, the people tested number is from pillar 1 and 2 testing in England only for the week ending the 2nd of September, whereas the capacity figure seems to be for all pillars in the entire UK at around the 12th or 13th. It makes absolutely no sense to compare them. And the cherry on the top is that I'm pretty sure this number is from before the problems with test shortages and testing delays that they're implying it explains.

The not-so-secret weekly report in question with the number of people tested for the first time is here: https://www.gov.uk/government/publications/nhs-test-and-trac...

dang6• 6 years ago

(This comment was posted before we merged the threads from https://news.ycombinator.com/item?id=24685911. The current article confirms that it was Excel.)

daanlo• 6 years ago

We did a bit of work for laboratories this year and csv is not an uncommon exchange format between labs. In general almost all exchange formats are text based, with labs saying they will upgrade to „modern xml formats“ at some point in the future. So seen in this context a csv or an excel file doesn‘t really surprise me and should probably also be seen in this context.

ACow_Adonis3• 6 years ago

Did it mention that it was in the context of csv or an excel file?

I only say that because, as someone who is painfully aware of the limitations and problems of those formats, I'm similarly aware of getting "that web-guy" on a project who proclaims "lets put things in a modern xlm format!", and lo and behold the process is now an order of magnitude slower and the xml format an order of magnitude larger than the simple delimited tabular format or stream.

I'm also painfully aware of the old systems (and how old health systems are) with fixed sized buffers and processes, so I can see how this would happen in the context of a lot of computing.

Edit: i see later on someone is mentioning that twitter suggests it had to do with excel file size limitations...

morsch• 6 years ago

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

mark-r7• 6 years ago

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.

iagovar2• 6 years ago

CSV is terrible for anything containing human text though. It's a nightmare.

I use SQLite as files a lot for this reason.

tannhaeuser• 6 years ago

exchange formats are text based, with labs saying they will upgrade to „modern xml formats“

Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in place to capture file write errors, or more generally the use of non-reproducible, manual operating practices which seems common in data processing.

ralphael• 6 years ago

..this right here. No error checking, inadequate testing, no reconciliation process.

Excel is used extensively in many industries. Any file could be cut off in processing by any number of reasons, one off errors for e.g.

So the solution is to "fix" the process by using the existing broken process and smaller files....

theptip3• 6 years ago

There's nothing wrong with CSVs/TSVs

I can see the theoretical purity of this statement, but based on my experience working with CSV files generated by actual non-technical users I have to disagree here.

There are a number of footguns here that are really subtle and the average non-technical user has no hope of spotting them.

Problems that I've seen in the wild, off the top of my head:

* Windows vs. Linux line terminators breaks some CSV libraries.

* Encoding can change depending on what program emitted the CSV file, and auto-detecting encoding is not perfect. For example, Excel for Mac uses Linux encoding by default, IIRC.

* Excel does wacky things when you export a "CSV" in the wrong format; real users use Excel to generate their CSVs, not Python. For example if you import the string "0123456789" in an Excel sheet, it infers "number" and strips the leading "0" when you export. Now your bank account/routing numbers are invalid!

* "What's a TSV?" -- if users use CSV, how do you handle commas in the data? It's nontrivial to train users to do their CSV upload as a TSV.

Etc.

In practice we needed to build a fairly beefy helpdesk article with accumulated wisdom on how to not break your CSV exports, and most users don't read/remember these steps until they experience the trauma first-hand.

I'd say the CSV format is deceptively simple -- it's quite easy to do the right thing as a developer where the source and sink are both code you control, but in the wild it gets messy really quickly.

ashtonkem2• 6 years ago

I feel like text-based is colloquial shorthand for "schemaless" or "dynamically typed".

shalmanese2• 6 years ago

There's nothing wrong with CSVs/TSVs anyway

The first CSV file was created in 1983. The first CSV standard was created in 2005[1].

The two decades of CSV surviving as an informal standard means that it takes minutes to make a 95% complete CSV parser and an infinite amount of time to make a 99.99% complete CSV parser.

[1] https://en.wikipedia.org/wiki/Comma-separated_values#History

secondcoming• 6 years ago

XML would fix the max rows issue, but open you up to OOM issues instead!

andor2• 6 years ago

Sorry, but if you run into OOM issues by parsing an XML file, you're using the wrong API.

The DOM for a large XML document will of course take tons of space in memory. The key to parsing XML files quickly and with low memory consumption is to only keep in memory what's necessary, by streaming over the elements.

https://en.wikipedia.org/wiki/Simple_API_for_XML

smsm422• 6 years ago

There's nothing wrong with CSV, especially if you don't have arbitrary-text data (if you do, just don't do CSV). Excel though adds an addition layer of services, which turns it into a nightmare if used as something it explicitly wasn't made to be - a database.

aschatten• 6 years ago

There is nothing wrong with CSV or text base formats. You can even use AWS Athena to query CSV feels stored in S3. It's a good format for data import/export, that many systems can natively understand or have tools to parse, given it's known how to interpret data.

It was a data pipeline issue. Software has little to do with it. If they received data in json and tried to interpret it as CSV, the same could have happened. I believe Excel even warns when you open file that has too many rows.

tetrahedr0n• 6 years ago

I agree.

Tools exist, for analysts and engineers (MS Access comes to mind for the analyst, python for the engineer), that would rectify the problem. And I think it's a fair assumption to say that those tools would be readily available.

Kinda sounds like a management issue, as well. No one ever said "hey you know XLS doesn't support all of this data"?

What a mess.

iainmerrick2• 6 years ago

As the article notes, the CSV part of the pipeline was fine; it only broke after they imported the data to XLS.

One lesson I’d draw from that is to favor simple human-readable text formats like CSV, where they’re suitable for the job at hand.

underdeserver• 6 years ago

The real, visceral tragedy that is COVID-19 notwithstanding, I think it brings a marvelous example of why statistics, the importance of accuracy in data collection, and the effects of exponential growth are a must-have in today's highschool curriculums.

CamperBob23• 6 years ago

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.

ImaCake2• 6 years ago

I mean, statistics relies heavily on calculus. It might be best to try and fit both in. Something at least a little bit fun and educational could be achieved with some dice throwing or simple computer simulations. High school students place dice games, they will immediately appreciate how useful a bit of probability would be in that context.

RhysU• 6 years ago

Change in deaths over change in statistical illiteracy is positive eh? How positive? Presumably the change in statistical illiteracy depends upon the magnitude of the deaths. It'd be an interesting relationship to study...

bartread• 6 years ago

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 calculus, but I've had many occasions to regret my choice of mechanics over statistics during the past two decades.

OJFord5• 6 years ago

Where did you go to school that you didn't do statistics?

jlokier• 6 years ago

Imho, exponential growth especially, and then clustering.

Some pretty animations in a lesson might be helpful.

Fire grows in a similar way.

I've seen people up in arms because of things like a workplace of 1000 people shut down because "only" 20 people tested positive one day.

What's the problem?

To people who don't understand exponential growth it seems like 1 in 50 people is a fuss about nothing.

To people who do understand, it's an early-warning signal which says 500 people will get it in weeks due to the confined workspace, if not stopped urgently. And they will infect 1000s in the surrounding community in the same timeframe.

(To anyone tempted to point out the published R isn't that high, local R is highly dependent on situation and who mixes with whom. In a confined workspace, especially with people moving around, it's higher than in the general population. This is why it's useful to teach clustering in addition to exponential growth.)

thu21113• 6 years ago

the effects of exponential growth

Oh god not this again.

The problem the world has right now is not a lack of understanding of what exponential growth means. Plenty of people understand that just fine. It's so easy to understand that there is even a simple ancient parable about it (of the Chinese Emperor and the chess board).

The problem is people who are obsessed with the concept of exponential growth even though "grows exponentially until everyone is infected" is not a real thing that happens with viruses, even though COVID-19 no more shows exponential growth than the sine wave does (sin roughly doubles at points), even though Farrs Law is all about how microbial diseases show S-curve type growth.

This leads to crazyness like the UK's chief medical officers going on TV and presenting a graph in which the last few data points are in decline, but with sudden endless exponential growth projected into the future, along with claims that "this isn't a prediction, but clearly, we have to take extreme action now because of exponential growth".

Observing exponential growth for a few days in a row does NOT mean endless growth until the whole world is infected. Growth rates can themselves change over time, and do. That's the thing people don't seem to understand.

thrwyoilarticle• 6 years ago

This government had a statistics screw-up just two months ago and the only negative reaction was about how they handled the fallout. As a nation we simply do not understand its value - or the cost of it being misused.

maverickJ• 6 years ago

Interesting. We might see an increase in the amount of wrongly reported data as a result of the global lockdown. One can only wonder what the consequences of data underreporting might be?

This reminds me of the below event which you can read the full story at https://leveragethoughts.substack.com/p/making-investment-de...

In 1976, the UK government, led by James Callaghan of the Labour, borrowed the sum of $3.9 billion from the International Monetary Fund. The granting of the loan was based on the condition that government fiscal deficit be slashed as a percentage of GDP.

A couple of years later, the chancellor of the exchequer at the time of the IMF loan said this below.

‘If we had had the right figures, we would never have needed to go for the loan”

That’s right!! The decision to borrow money from the IMF was based on wrong data. The public borrowing figures which prompted the UK government to seek IMF loan in 1976 were subsequently revised downwards sometime in the future.

neilwilson• 6 years ago

And of course they would never needed to have borrowed at all if they had just let Sterling float and not taken a us dollar denominated loan in 1975.

The problem was a government still operating as though Britton Woods was in place five years after Nixon ended it

kalaido• 6 years ago

That happens if you only test ill people: https://www.worldometers.info/coronavirus/country/spain/

mhandley• 6 years ago

The article states that it is not known where in the country was affected, but I think we can deduce that. Here are the daily cases by specimen date. Dotted line is Saturday's data, solid line is Sunday's. The difference between the two is partly the normal daily update, but around 2/3 of it is correcting the underreporting:

7-day moving average: http://danger.handley.org.uk/misc/rates-uk-recent.png

Raw data: http://danger.handley.org.uk/misc/rates-uk.png

Looks like all regions were affected, but by far the largest corrections are in NW, NE and Yorkshire regions. In particular, NE had looked like cases were declining, but we can now see this was incorrect, and they're still increasing rapidly

Edit: note the most recent 3 days are always incomplete, so any decline shown there is not a real effect.

pbhjpbhj2• 6 years ago

Nit: presumably whatever the maximum time is before results is the period in which updates to past results should be expected. I think it's about 1 week at present?

Before the recent leap in cases my test took 6 days to come back (negative). My wife's test at the same time came back the next day. At the time they were saying that 72 hours was the expected return time for results. For me it has been a couple of days of steadily worsening coughing, and I gather people take about 3days-1week to show symptoms ordinarily.

So UK results are most likely reflecting infections from 1-2 weeks ago.

jeffrallen• 6 years ago

640k deaths ought to be enough for any country.

jimmcslim• 6 years ago

I'm wondering if there could be an opportunity for an open-source contract tracing system, similar to the allReady [1] application ? Maybe something backed by a graph database such as Neo4J [2]

Looking at the allReady repo, maybe it isn't a great example since it hasn't been touched in years...

[1] https://github.com/HTBox/allReady

[2] https://neo4j.com/

makomk8• 6 years ago

The thing that puzzles me is, what on earth happened on the 28th? A really anomalously low number of cases were reported then, and it's only got more weird after this fix added zero unreported new cases on that day and much higher numbers on the days before and after it. Something definitely doesn't seem right here.

fortran77• 6 years ago

I wouldn’t blame Excel. The cause was gross incompetence.

arnon• 6 years ago

For most companies, "big data" is when they can't fit the data in a single Excel sheet anymore.

cashewchoo• 6 years ago

Devops Borat: "big data is any data which is crash excel"

tda• 6 years ago

This is coincidentally also how I describe my job: I help when it doesn't fit in excel anymore.

Much easier for people to understand than shorter than civil engineer turned software developer in an engineering firm where I do data management, automation and application development for (mostly internal) clients to streamline business and engineering processes.

BuyMyBitcoins• 6 years ago

I love how Excel has become a de facto database - even though it absolutely shouldn’t be.

shadowgovt7• 6 years ago

The first rule of software is "If it works, it'll get used."

This is why people's email inboxes double as their TODO lists.

gpmcadam3• 6 years ago

It doesn't even necessarily have to "work" (as in, work well) it just has to feel like it's the path least resistance.

You can work in a department using Excel for this type of data collection and reporting, and you can continuously suggest to your superior that something more mature could be used. They would probably agree. As would the entire team!

But if the team of analysts are only properly trained in this system and it's all they know coupled with a huge backlog of cases and time pressures, then they're going to keep using the thing that causes least headaches in the short term. And that might be objectively worse to everyone involved but they just keep ploughing through.

Toxic culture, bad management, poor working practises and external pressures can force even the most sane of people to choose the worst technology on the basis that they perceive it as "saving time" in the short term, even when they know full well they're borrowing Peter to pay Paul, they still do it.

galeos• 6 years ago

I wonder if this will be added to the European Spreadsheet Risk Interest Group's (EuSpRiG) horror stories list:

http://www.eusprig.org/horror-stories.htm

dang6• 6 years ago

Discussed here: https://news.ycombinator.com/item?id=22431500. (Submitted many times, but that's the only real thread, and only from 7 months ago.)

whatatita• 6 years ago

The site's cert is invalid. Got a mirror?

iso8859-1• 6 years ago

Certificates are not applicable, the link scheme is HTTP.

earksiinni• 6 years ago

TIL there is something called "spreadsheet risk management."

Wow. Thank you for this gem of human culture.

beagle32• 6 years ago

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) occasions where Linux or MacOS is involved, it's attributed to that system. I don't think that's a coincidence, CMV.

callamdelaney2• 6 years ago

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

pintxo3• 6 years ago

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.

SkyBelow• 6 years ago

Who? Almost anyone who wanted to get something done without going through the red tape of working with IT. Even in a private corporation that red tape is enough to make many a things end up in excel (or for the more tech savvy, access). Government is surely even worse (better?) at having red tape so the pressure was likely stronger.

indy• 6 years ago

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

WrtCdEvrydy3• 6 years ago

Alternatively...

"We need to store some data for a virus" "Where does the data come from?" "Every hospital sends us an excel sheet" "Well, let's merge all of it into a bigger sheet and display it or export it"

libraryatnight• 6 years ago

In my org excel might have been the start, the intent being to move over to a DB once someone got it stood up. Then nobody would stand up the DB - because not urgent, we have the excel for now. The DB would be completely forgotten, and 3 years down the road everybody would be wondering wtf we have this huge excel file for. Then we would have six weeks of meetings to discuss the file until something else comes up and we forget it for another 3 years.

shadowgovt7• 6 years ago

The common case for stories like this is the answer is "nobody."

Time point 1: "We need a way to map the long-form epidemiology state. It's 2D data... Let's use Excel." "Will we hit limits?" "Pfft, no. Not unless we end up needing to track tens of thousands of patients, and that's not practical; we don't have enough people to do that tracking in the whole NHS."

Time point 2: "This pandemic is an URGENT problem. We need to hire more people than ever before to do tracing. And be sure to keep the epidemeology state mapper updated so it can drive the summary dashboards!"

addicted• 6 years ago

Except it wasn’t the government. They outsourced the test and trace system to private companies.

dgudkov• 6 years ago

Of course Excel. What else non-technical users can use? They simply don't have any other data tool.

manigandham• 6 years ago

Excel is perfectly fit for that and is nowhere near the actual issue.

It's lightweight, fast, portable, compatible, and just about everyone can use it regardless of (technical) skill.

dx87• 6 years ago

I think Linux and MacOS viruses get a special callout because they're exceptions. Similar reporting conventions are used all the time, like a terrorist attack in the middle east vs a terrorist attack in France. It's not some nefarious PR scheme, the story is just more interesting because of how unusual it is.

gridlockd6• 6 years ago

The way I heard it, it was an Excel problem: They had a CSV file that had more than 1 million lines, and Excel dropped the lines beyond 1 million.

I can imagine an automated process that appended to the CSV and then Excel saving back to that same file with the lines stripped.

I hope we can all agree that this is not the way Excel or any other software should behave, whether the user is incompetent or not.

codeulike8• 6 years ago

If you read the article (perhaps its been updated?), they were using the old XLS format and they hit the limit at 64,000 lines

PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards.

The problem is that the PHE 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.

shadowgovt7• 6 years ago

If the conversion was failing silently, it's a Microsoft software problem, but I bet you money the conversion was failing in a way that fell into a `catch(Exception e) { }` somewhere in their conversion script. Or even a `catch(Exception e) { log e; }`, and the log is stored to local storage on a machine that no human being ever logs into.

danudey• 6 years ago

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

supercanuck• 6 years ago

Could also be a lack of budget funding. The newer version of excel supports: 1,048,576 rows

LandR• 6 years ago

The test and trace system for the UK was ~£12bn.

munk-a• 6 years ago

Yea, I have a personal vendetta against XLSX due to having had to make tooling replacements for business logic being encoded in spreadsheets in the past, but the title of this article is incredibly slanted.

This was absolutely a user error and the title "Excel: Why using Microsoft's tool caused Covid-19 results to be lost" is really disappointingly click-baity.

hermitcrab4• 6 years ago

It won't be a surprise to anyone who lives in the UK that the problem was blamed on Excel, rather the UK government taking any responsbility for it.

ksk• 6 years ago

Is your view based on data available to everyone else? If so, that would be nice to see. Its far easier for you to convince others with data. Why would anyone want to change your view?

stoobs2• 6 years ago

WTF, they have a big Azure deal with Microsoft, why the hell would the be using Excel as the data source?

IT ineptitude of the highest order, although I'm not surprised having been involved with government IT previously.

gpmcadam3• 6 years ago

So easy to pass judgement on critical software decisions from the outside, with absolutely no knowledge of how they work or their training, the pressure from Government, the previous conditions that they found themselves in when a pandemic hit.

Who are "they"? The Government? GDS? PHE? NHS England? NHS Digital? NHSx?

stoobs2• 6 years ago

Yes, with direct previous experience and knowledge of large UK Government IT projects, how they were integrated and how staff were trained, I feel I'm well placed to comment harshly.

"They" in this case was the UK Government, and part of the goal was creating secure backend systems specifically for the NHS as a data store, Public Health England would absolutely have access to it.

Even the most basic of checks at the start of the project would have highlighted that Excel was not a proper solution for the application (Even if the developer(s?) had used the newer(?!?) XLSX format rather than XLS) - which highlights that there was no proper oversight as to how the system was constructed.

bitlevel3• 6 years ago

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.

ant6n• 6 years ago

Excel may not be a database solution, but it is the most commonly used database solution.

TwoBit2• 6 years ago

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.

bitlevel3• 6 years ago

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.

TeMPOraL19• 6 years ago

If you're thinking regular office workers, they know of no alternative.

Excel works enough for small data, particularly when you don't do complex queries on it. The more you know of it, the better it works. The only other thing that offers similar benefits to Excel but works as a database is MS Access, but the mental model behind it is too complex for your average office worker who wasn't trained in it, and like most database systems, requires a lot of up-front work with figuring out the schema, and doesn't particularly like the schema being modified later on.

As far as I can tell, there's literally nothing else out there. No, random SaaS webapps du jour don't count, because they're universally slow, and also store the data in the cloud, instead of the local drive.

fetbaffe2• 6 years ago

The mix of Excel & Access as a new application would be a killer app. Hasn’t someone already built that in the cloud?

mellosouls4• 6 years ago

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

dang6• 6 years ago

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%20informa...

mellosouls4• 6 years ago

Fair enough, makes sense, thanks. When I posted the comment it was the first comment on the thread (not sure if it's been merged or I'd had the page open a long time before posting) and we'd already had other threads with that leeway. Cheers for the clarification, I'd thought that might be the reason for the earlier ones.

Edit: just seen your other comment re merging, thanks.

makomk8• 6 years ago

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?

BooneJS• 6 years ago

Spreadsheets like Excel or Google Docs, with the expandability offered by VB and JavaScript, allow for rapid development of applications that require data storage and data interpretation in one package. I’ve seen them used and abused for several functions over the course of my career.

Apple/Claris FileMaker has had this niche for a bit, but it’s only been a niche,

isolli2• 6 years ago

Obligatory reminder: an Excel error was the cause for much unnecessary austerity after the 2008 financial crisis [0]

[0] https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel...

karmakaze• 6 years ago

TL;DR [I found it hard to find which actual limit was hit.]

The problems are believed to have arisen when labs sent in their results using CSV files, which have no limits on size. But PHE then imported the results into Excel, where documents have a limit of just over a million lines.
The technical issue has now been resolved by splitting the Excel files into batches.

codeulike8• 6 years ago

Further down, maybe the article has been updated

The problem is that the PHE 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.

cma• 6 years ago

Why does Excel still have such low row limits in 2020? When they increased it from 64K, did they decide to only go to a million to promote Azure for "Big Data"?

kolaente• 6 years ago

You seem to assume the government used a modern version of Excel

walrus01• 6 years ago

If I had a dollar for every office worker that thinks Excel is a database...

codeulike8• 6 years ago

The BBC updated the article with technical details, see about halfway down. Here's the tech details:

(PHE = Public Health England)

Analysis by Leo Kelion, Technology desk editor -----------------------------------------------

The BBC has confirmed the missing Covid-19 test data was caused by the ill-thought-out use of Microsoft's Excel software. Furthermore, PHE was to blame, rather than a third-party contractor.

The issue was caused by the way the agency brought together logs produced by the commercial firms paid to carry out swab tests for the virus.

They filed their results in the form of text-based lists, without issue.

The problem is that the PHE 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.

And since each test result created several rows of data, in practice it meant that each template was limited to about 1,400 cases. When that total was reached, further cases were simply left off.

Until last week, there were not enough test results being generated by private labs for this to have been a problem - PHE is confident that test results were not previously missed because of this issue.

And in its defence, the agency would note that it caught most of the cases within a day or two of the records slipping through its net.

To handle the problem, PHE is now breaking down the data into smaller batches to create a larger number of Excel templates in order to make sure none hit their cap.

But insiders acknowledge that their current clunky system needs to be replaced by something more advanced that does not involve Excel.

edit: also longer version by Leo here https://www.bbc.co.uk/news/technology-54423988

dang6• 6 years ago

OK, we'll change to that from https://www.bbc.com/news/uk-54422505. Thanks!

More explanation here: https://news.ycombinator.com/item?id=24690286

philjackson• 6 years ago

Everyone's saying not checking for errors was the terrible thing, but I think having Excel as a middle-man in such a sensitive system was the first issue.

It's such a British government thing to do too (I worked for Syntegra when I got out of uni).

bencollier493• 6 years ago

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

codeulike8• 6 years ago

Perhaps some old integration code from 10+ years ago that was written to spit out XLS rather than XLSX

e.g. some old versions of Crystal Reports can only do XLS. There must be loads old systems around that can only do XLS

bencollier493• 6 years ago

I suppose someone had written a VBA macro to import all the CSV files and then save them in a format which could be imported into some dated upstream system. Or never changed how they worked. Could even be because Excel does something slightly more rigorous with XLSX files which made writing the VBA trickier.

jjkaczor• 6 years ago

XLSX files have been around for ... nearly 13-years...

Ugh - I still deal with clients who are creating brand new XLS and DOC files and expecting to have all the latest features (co-authoring when hosted in 365/SharePoint Online/OneDrive/Teams)...

makomk8• 6 years ago

It's also entirely possible that whatever they're importing this data into only supports XLS and not XLSX. I don't think Excel is the ultimate endpoint - the data is getting imported into one or more databases somehow.

dumb1224• 6 years ago

I worked with teams integrating legacy systems from hospitals before. There seem to be a large amount of carefully customised scripts for pulling data together. There was never a straight-forward data ETL / export for something other than routinely reported. A lot of information is derived and inferred from even not related (e.g infer chemo events from drug prescription codes if parts of system is not accessible). Hence if you really need high quality data you need a lot of validation and cross-checks as well (either by script or manually). To me it sounds like the checks and validation did not happen enough as the time is very critical. I don't think Excel is the single thing to blame here. Hospital IT systems have a long history of data related issues.

n4r9• 6 years ago

My guess: somewhere down the line someone who doesn't often use Excel typed ".xls" at the end of a string variable or selected it from a dropdown list and made a mental note to come back to it if it caused any issues in testing.

isodev• 6 years ago

“Let’s see if StackOverflow has something”

...

“Oh nice! Exactly what I need!”

...

“Woah it builds! Ship it!”

bencollier493• 6 years ago

This is Excel. There is no "builds".

testplzignore• 6 years ago

It may have been a user education and/or communication issue.

I had someone come to me recently asking for some data "in an XLS". I asked them if they specifically need XLS or if they just need something that can be read by Excel. It was the latter, but they didn't know that there was a difference. To some people, XLS == Excel. Sort of like how TLS is still being referred to as SSL - somewhere in the world there is a developer being asked to use SSL for a new project because SSL == secure.

Tistel• 6 years ago

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.

starky• 6 years ago

There are a surprising number of people out there that still save their office documents in the old formats for whatever reason they have convinced themselves of. Just a couple weeks ago I had a coworker that sent me a manual for a piece of software he wrote as a .doc file.

aphrax3• 6 years ago

I've been bitten by this exact issue (albeit on non critical data), save a file as XLS it will silently drop rows beyond 65k - save it as XLSX and it's all good..

shadowgovt7• 6 years ago

The silent part there is the real surprising part to me. You'd think the exporter would die noisily if it still had data in the buffer and it ran out of file.

TeMPOraL19• 6 years ago

I'm betting the exporter isn't running out of file, but out of loop counter.

NelsonMinar2• 6 years ago

Silent? Are you positive? Not doubting you but if so; WTF?!

rvba6• 6 years ago

I just tried to reproduce it and got a big warning message that says that data will be lost.

You can disable this message (per file basis), if for some reason you dont want to see it.

As far as I remember this message existed since Excel 2007 (which introduced .xlsx format), so the guy is simply lying.

aphrax3• 6 years ago

Yep, I'm positive but as said elsewhere this is a few years back. I recall sending the data out and getting some pretty stern feedback (this is why I recall it) Didn't make the mistake after that...

hermitcrab4• 6 years ago

Until you reach 1,048,576 and then XLSX silently drops rows.

mark-r7• 6 years ago

Friends don't let friends use Excel.

Unfortunately Excel is the great swiss army knife of software and it's hard to avoid.

TwoBit2• 6 years ago

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

shadowgovt7• 6 years ago

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.

andor2• 6 years ago

Ask a professional when the stakes are too high. Hire a developer.

beervirus2• 6 years ago

Excel is just fine for doing one-off computations and visualizations. There's nothing out there that's as ubiquitous, capable, and easy to use.

langitbiru• 6 years ago

So the limit for Excell is 65,000 rows for the old format, and one million-plus rows for the new format (from the article).

Then I wonder, is there any tool that mimic Excell but with Sqlite as the backend? The limit of rows in Sqlite is 2 raised to the power of 64 (18446744073709551616 or about 1.8e+19).

https://sqlite.org/limits.html

ww_wpg• 6 years ago

apparently part of the problem here was simply the fact that they used the columns [1] (of which there are far fewer, even on newer versions of excel) instead of the rows

[1] https://twitter.com/standupmaths/status/1313055411285774336?...

edit: retracting this as the person who posted that tweet made a correction in one of the replies

ryankrage77• 6 years ago

about 26^3 columns, or 17576

013a• 6 years ago

XLSX's limit is 2^20, or just over 1.04 million (as the article says).

bigbubba2• 6 years ago

Mimicking a spreadsheet with an RDBMS would be a neat hack, but I think the better solution is to simply use the database like a database. If an amateur carpenter is trying to hammer screws into wood, you hand him a screw driver, not try to invent some sort of hammer with a twisting mechanism that turns screws when you wack them. Use the right tool for the job, and use it like it's meant to be used. If the workers involved don't understand the right tool, then either train them or hire new workers who do.

skykooler• 6 years ago

not try to invent some sort of hammer with a twisting mechanism that turns screws when you wack them.

That's a thing, it's a manual impact driver. And sometimes it is the right tool for the job (for example, when you have stuck screws and a regular screwdriver would just strip the head).

pbhjpbhj2• 6 years ago

You could open a DB as a table view in OO.org, so presumably you can in LibreOffice. I'm surprised Excel doesn't integrate with Sqlite DBs.

My naive view would expect tables <-> sheets; rows <-> tuples to be easy to do (for MS) and just don't touch the relational aspects??

iagovar2• 6 years ago

Does it work with SQLite? Is it practical?

lmm4• 6 years ago

Microsoft actually has a reasonable migration path where you can use Excel backed by Access and then transition to Access backed by SQL Server. But you'd have to recognise that you had a problem first.

contextfree• 6 years ago

Something sort of like this already exists shipped with Excel in the form of Power Pivot, which stores data in an embedded database inside the Excel workbook, and is supposed to scale up to 2GB and hundreds of millions of rows.

https://support.microsoft.com/en-us/office/power-pivot-power...

As you might see from that link it's semi-integrated with the Excel interface, but the differences in the underlying model show through in some ways (like not being able to edit individual cells or use VBA)

StreamBright• 6 years ago

I wrote an energy reporting system based on Excel. Based on that, I think Excel use falls into 2 categories

a, extremely simple reporting with few workbooks

b, serious misuse of technology

Billion dollar companies were born based on replacing Excel in workflows. The problem is not that they have an old version, the real problem is that they use such a system in the critical path.

SV_BubbleTime• 6 years ago

As a consequence, each template could handle only about 65,000 rows of data

Oh? Like 65,535 or so? That seemed weird a first that even as old as xls is that only 16bits were allocated to max rows.

But then maybe not. Each row might get an ID, so that's 16bits * rows you have. I wonder if when XLS was designed they considered it very unlikely many people would have 500MB of db of empty rows and then would needed more data added to each one?

Simplified of course, I bet even original format had some explicit row identifier that could be shortened with optimizations and increment tags.

usmannk• 6 years ago

XLS does in fact have a 16 bit row counter :/

emerged• 6 years ago

That should be enough bits to do everything a computer will ever need to do.

dan-robertson• 6 years ago

The xls format is pretty old. The newer formats (xlsx, xlsb, and so on) allow for larger spreadsheets.

The reason the spreadsheets in the old format couldn’t grow was probably compatibility. The reason for the limit in the first place was probably about reducing memory on disk and in main memory.

mjw1007• 6 years ago

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?

jsty• 6 years ago

Is there anything in Excel itself that behaves like that?

I'd imagine the default error-handling behaviour of 9/10 Excel macros is to throw away data

piannucci• 6 years ago

Oh, my God. "On Error Resume Next" is one of my earliest memories of learning to program for Windows. It never occurred to me that it might result in deaths.

willtim2• 6 years ago

Yes and "on error resume next" is what I usually see in VBA code

Ma8ee• 6 years ago

I had to import data from Excel documents into MS SQL Server. Since this was supposed to be a one time thing I used the import wizard. Some values where quietly ignored because the cells happened to have different formats than the surrounding cells. They looked exactly the same, so it took me a little while to figure it out. And if I wouldn't have done some simple sanity checks those errors would definitely not have been discovered before it was too late.

Closi8• 6 years ago

It isn't clear what piece of software was behind this "automatic process".

They probably mean a semi-automated process within excel, where each tab is a days extract or something similar and they are using external references to other sheets. In any vaguely up-to-date version of excel the way you would do this is via 'get and transform' which does not have any of these limitations (including the 1m record limit that the news article suggests).

The funny thing is that the latest versions of Excel are brilliant at aggregating and analyzing data and are more than suitable for this task if used correctly (i.e. using PowerQuery). It's just that way less than 1% of users are aware of this functionality - I would assume that even most hacker news readers probably don't know about PowerQuery/PowerPivot, writing M in excel and setting up data relationships e.t.c.

jononor• 6 years ago

Always use checked pre- and post-conditions in a data pipeline.

This simple post-condition would have caught this issue: The sheet after merge operation must have a number of rows equal to the sum of number of rows for all merged sheets.

Assuming this is a merge of a standardized input, then another post-condition might be: The number of columns in output shall equal the number of columns in the input. Might want to check header names, and order as well.

Thinking in terms of universal properties, and putting the checks into production, is better than unit-testing.

jen729w• 6 years ago

Even just within my own sheet I always build in some sort of idiot-check, the assumption being that I’m the idiot.

Do a SUM and match it against a COUNTIF on another column, or something. It doesn’t really matter what it is but if the data is important at all, I always scatter little checks throughout.

The case described in this article sounds like someone who just doesn’t know how to use Excel. I mean why in the name of VLOOKUP would you override the default and choose to save as .xls? That was a conscious decision. Anyone worth their salt knows that’s stupid.

Excel is not to blame here.

dkarp• 6 years ago

I’d guess they wrote a script to merge the csv file and export in xls. Xls might have been the default of the library they used in a rush. I think this may be the case with some python libraries that generate excel files as xlsx is a more complicated format.

1_player• 6 years ago

Always use checked pre- and post-conditions in a data pipeline. [A] simple post-condition would have caught this issue

I'm not sure they teach that in medical school.

avibhu• 6 years ago

Which I feel might be indicative of a larger problem. If you are going to deal with data (and its processing), it only makes sense to teach those skills in school itself.

swiley3• 6 years ago

They spend so much time memorizing random trivia maybe a course in handling data wouldn't be a bad idea.

jussij• 6 years ago

This quote from the article says this work was being done be a developer(s) and if that is the case they should have know better.

The problem is that PHE's own developers picked an old file format to do this - known as XLS.

curiousllama3• 6 years ago

Columns. They were using a new column for each new observation. The 16k number isn't a coincidence - it's the max # of columns in a spreadsheet.

hinkley5• 6 years ago

I hate solving problems and not knowing how I did it, so this particular experience has stuck in my craw.

Trying to develop a budget to pay off debts, my partner made this elaborate Excel spreadsheet and the output was that basically she had no spending money and I had very little, until one or both of us got a raise. It was far more austere than either of us were willing to go. So I started over using a different equation for 'fairness', and a different layout because something about her tables was just confusing and messy. When I was done, we had $300 a month of extra spending money between the two of us, despite using the same targets for pay-down, savings and bills.

I spent about 90 minutes poking at her spreadsheet and mine looking for the error and never did find it. I don't know what the right solution is to this sort of problem, at least for non-developers. But if I was skeptical of Excel going in, I was doubly so after that. Especially for something that is going to be used to make decisions that will affect every day of your life.

1123581321• 6 years ago

That rework based on suspicion is typically how it’s done. :) Add to that subject matter expert review. For example, I used to work on a sheet of scoop size calculations for industrial digging and nothing from it would reach sales staff or customers until a couple of the crusty old experts played with it and vouched for the calculations.

Imperfect measures, but they keep the work within the grasp of everyday/business users in a way a formal test suite wouldn’t necessarily.

hinkley5• 6 years ago

I didn't rework it because I thought the numbers were bad. She and I agreed to ground rules, [but] I could not commit to the consequences even though the 1st order consequences came out in my favor. The second-order consequences would be that I'd tricked her, intentionally or otherwise, which is decidedly unhealthy in a partnership. I had contributed 2/3rds of the income but 2/5ths of the debts (the latter being the main sticking point) so while 'fair' was not going to be 50:50, I'm not some monster who's going to buy high end electronics while my partner is eating ramen.

I expected worse news for myself after re-doing the numbers, and in fact I ended up with a little bit more spending money with the new numbers.

I mention this only because I have come to expect policy people to stick to their initial narrative a little more enthusiastically than I could. Getting them to review data and policy unless there is a clear benefit for themselves is difficult. It's very common for people to get promoted by challenging this friction in ways that benefit everyone (because the deciders couldn't connect the dots). It's always newsworthy, and I wish it weren't.

OrwellianChild• 6 years ago

The only real way to solve issues like this is a step-by-step reconciliation between the old model and new. Each assumption and calculation must be tested to confirm that they replicate the same output. This is extremely difficult if the underlying structure/assumptions are different (e.g. monthly views vs. transactional views, etc.).

joosters• 6 years ago

You made two different spreadsheets, they came up with different results, and you're blaming Microsoft for the error? Really?

Excel, whether you like it or loathe it, is in such wide use around the world that $300 math errors would have been noticed a very long time ago. I could believe that there are still many lurking bugs with obscure corner cases, nasty floating point rounding minutiae and so on, but I would bet (checks spreadsheet) $300 that your mismatched results were solely due to your own failures.

dllthomas2• 6 years ago

I've re-read the parent comment a few times, and I really don't see how this is your takeaway. The author is complaining that they find Excel difficult to inspect and different spreadsheets difficult to compare. There's no claim that Excel did a calculation wrong.

pestaa• 6 years ago

I'm not so sure he claimed that Excel can't add and subtract numbers. I think the argument is that even two people can make such a mess of a spreadsheet that nobody can really untangle it later.

hinkley5• 6 years ago

But they aren't noticed. The classic blunder is dropping the last item off of a summation, and nobody notices until they add another item and things look janky.

Those happen all the time, we just only hear about it when the consequences are outsize.

segfaultbuserr2• 6 years ago

The OP said that using a spreadsheet program can be messy and prone to mistakes, and implied that there could exist alternative approaches. The OP never said a bug in Excel was responsible for the mistake or blamed Microsoft for corrupting the calculation.

In the same sense, one could say manual memory management in C can be messy and prone to mistakes, it doesn't mean that one believes C should never be used in any circumstance, and it doesn't mean one's trying to blame the C complier, libc or Dennis Ritchie for corrupting one's program.

Hypothetically, if someone writes the following reply.

The C programming language, whether you like it or loathe it, is in such wide use around the world that a NULL pointer dereference would have been noticed a very long time ago. I could believe that there are still many lurking libc bugs with obscure corner cases, but I would bet the memory corruptions were solely due to your own failures.

It would completely miss the point.

patrickthebold• 6 years ago

Make a third spreadsheet from scratch and see if it agrees with either of the existing ones.

There's some saying about never going to sea with two spreadsheets.

ACow_Adonis3• 6 years ago

the secret to using Excel well in large complex tables/spreadsheets is the same secret as coding with large complex programs.

You use structure, design away the complexity, implement constraints, error checking and tests.

Note it's true that an inexperienced person is liable to make spaghetti code either way, but there's nothing fundamental about a spreadsheet format that makes it inherently unusable for a lot of small/medium problems. Indeed it even has benefits in terms of interactivity/ turn around/accessibility.

Of course, there's also a lot of problems with Excel and reasons not to use it like a database or anything which fundamentally relies on maintaining data integrity, and it's liable to be the first tool reached for by the non-experienced, who will generally make a mess of things large and complex as a rule.

bradford• 6 years ago

You use structure, design away the complexity, implement constraints, error checking and tests.

I've never thought of Excel as an ideal tool for any of these things. I'm struggling to think of how it would have change-verifications/tests in the same way that software projects do.

(I'm certainly open to the possibility that I'm ignorant/unaware on this subject)

chime• 6 years ago

I wrote this a decade ago and have been using this for all of my personal and business spending: https://zetabee.com/cashflow/

It's not automated but it can help future income/expenses/balance in an Excel-like UI.

beervirus2• 6 years ago

If you're trying to figure out why the spreadsheets had different results, I'd start here:

I started over using a different equation

hinkley5• 6 years ago

Try as I might, I've never found a way, before or since, to push piles of stuff around and end up with a larger sum total.

I suppose it's possible I've discovered Dark Money but I doubt it.

nym3r0s• 6 years ago

This is precisely why I went with YNAB [0], started budgeting better and saved quite a bit of money.

Everyone who sees me paying for an app (I'm from India) ask me - "why can't you just use excel and do the same thing for free?". Excel sure is powerful, but in real life, your mileage may vary.

[0] https://www.youneedabudget.com/

threatripper2• 6 years ago

People keep putting the blame on Excel but it supports more columns than most implementations of SQL databases. MySQL for example supports only 4096 columns. SQLite defaults to 2000. That's way less than Excel's 16384.

maaarghk• 6 years ago

Amazing comment, sorry to see you're getting downvoted. ALTER TABLE upvotes ADD COLUMN threatripper_24691123_upvote DEFAULT 1 NOT NULL;

all_blue_chucks• 6 years ago

True, but they don't need to relate 16384 types of data. It sounds like they were using one column per record, rather than one row per record. So if they had a sensible data model 2000 columns should be more than sufficient.

threatripper2• 6 years ago

You're on to something. The number of tables in MySQL, SQLite, and Excel are virtually unlimited. It seems like that is the lowest common divider ensuring compatibility and data integrity when case numbers grow beyond bounds.

diarrhea2• 6 years ago

Each test result creating several rows of data seems like a problem too. In clean data, every observation is one row. It makes working with the dataset much easier. In this scenario, I would expect one observation to correspond to one test result. The multiple rows are then better off pivoted into columns.

remus• 6 years ago

Looking from an outside perspective I would agree, but in practice we don't actually know what this dataset is, so several things rows per test may make sense in the context of this stage of the data processing.

sjansen2• 6 years ago

It never ceases to amaze me how often we as developers are quick to point out other people's obviously incorrect decisions, only to defend our own Rube Goldberg implementations 30 minutes later by pointing out that critics just don't understand the design constraints.

Or at least how often my peers do that. Obviously all of my systems and code are perfect.

vhold• 6 years ago

I'd say it depends on the data.

If the schema is consistent between rows, and it turns out a test result is made up of several rows because the test is composed of several stages, I would leave it as is until reporting time.

If you pivot prematurely, you could end up dropping data because there are new stages didn't exist when you implemented the pivot.

diarrhea2• 6 years ago

If the schema is consistent between rows, and it turns out a test result is made up of several rows because the test is composed of several stages, I would leave it as is until reporting time.

In that scenario, indeed no action seems to be needed, because each row is one observation: every test stage is an observation. So it would seem to make sense.

One could then argue that each patient deserves their own table (observational unit).

But as other commenters pointed out, this is all speculation.

kobe_bryant• 6 years ago

these are data entries from all over the UK so its most certainly not clean data

Pick-A-Hill2019• 6 years ago

Fun semi-related story. Way back when, while I was squishing some Y2K bugs for a Saudi bank that catered to high net-worth individuals I was asked to look into why the AS400==>>db==>>Excel spreadsheet was "showing something screwy every now and then".

Spoiler Alert: It was a SMALLINT error that was 'patched' by someone previously (legacy code) that worked around the bug by rounding account balances to the Million (since it was a reporting function rather than an accounting function it was a 'sort of ok' hack). The bug re-appeared as undefined behaviour when transaction and or account balances went in to the billions.

andy_ppp• 6 years ago

Maybe everyone in school should be given real world problems like this to do in Excel so people can get a feel for doing it more correctly? Rows === records and columns === fields being the first lesson...

M2Ys4U• 6 years ago

They've tried that already.

When I was in school (early 2000's) our GCSE computing lessons were, more or less, "Here's Microsoft Word, today we will learn how to format a letter!" or "Here's Microsoft Excel, today we will learn how to create a chart!".

The only upside to taking the class was that our school managed to identify the kids who were obviously computer literate and we got to go on a tour of Microsoft's UK headquarters...

Exuma• 6 years ago

Probably excel parsing everything as a date LOL

smsm422• 6 years ago

Was just reading the chapter about using Excel as a database in this book two days ago: https://www.goodreads.com/book/show/39074550-humble-pi

Apparently it's super common, which fills me with horror. But these guys managed to take it to 11 by abusing it in yet another novel way.

hermitcrab4• 6 years ago

Definitely recommend 'Humble Pi'. Funny as well as informative.

iainmerrick2• 6 years ago

As an aside, this is a very clear explanation of the bug in non-technical language, much better than you normally see on general news sites. Excellent reporting!

psahgal• 6 years ago

I would hope that any decent data engineer would know not to do this. Are there avenues for fully-employed engineers to help governments build these systems properly? I'd honestly be happy to just donate some of my free time to help these efforts.

rahimnathwani• 6 years ago

When I use Excel to open a CSV with too many rows, I get an alert saying 'File not loaded completely'. I have to dismiss it by clicking 'OK' before I can interact with the spreadsheet.

So Excel isn't silently discarding data.

NelsonMinar2• 6 years ago

The BBC reports the problem was saving spreadsheets with more than 65536 rows as XLS. Can you verify Excel also throws an error for that case?

sterkekoffie• 6 years ago

Yes, you get a warning that explicitly says "Data beyond 256 (IV) columns by 65,536 rows will not be saved."

piqufoh• 6 years ago

... Yeah, but if you have an automated script compiling the reports into one file, and you forget to add correct error handling .... you end up with this mess :-(

I suspect that wherever this foul up happened there wasn't someone sitting and clicking through sheets ignoring errors (I hope).

lordnacho3• 6 years ago

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 separate to the "backend". This nutter thinks we have time to write some unit tests and also wants to add an authentication module so we know who uploaded what. And somehow he thinks we need to add logging so we can ensure errors can be tracked and debugged. Amazingly he seems to be suggesting that there's going to be errors in our process.

matt123456789• 6 years ago

Add to this the understanding that getting this on contract will take a few months, when the boss was asking for these figures yesterday...

TeMPOraL19• 6 years ago

That would imply non-tech people having some surprisingly deep visibility into technical details. What they really see is Excel vs. some contractor doing "a system".

The reality is that Excel is available today and works, and scales up... well, until it doesn't. Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything), and has a tried and true authentication module built in (a file sent from a government mail address). There's a risk of user errors, but the system will be working immediately[0]. Deployment can be done by anyone, as it's just clicking on File->New and starting to type data in.

Meanwhile, a "pigsqueal" solution will take half a year to design, produce and deploy (that's in an emergency, a year otherwise). And that's with competent IT people, and not someone who wants to profiteer off the crisis. Then you'll have to train the users, and hope the reality won't necessitate any changes, because they will take a while.

I think plenty of decent coders appreciate the fact that Excel is suitable for a surprisingly wide range of tasks, and that if you spot it widely used somewhere, it most likely means there isn't any comparable alternative available.

[0] - Note that the user error that finally happened did not happen in the place where you'd expect it to.

strbean2• 6 years ago

Couldn't you achieve exactly what was needed here with Airtable? Or, easier yet, Google Sheets and a Google Form?

gxs6• 6 years ago

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 and the tendency to immediately condemn anyone who is using excel.

Excel works great - I've personally built some very complicated models that work just fine. Goes through the same process of QA and line-by-line checking of requirements.

Not sure what the big deal is - mistakes happen in excel or otherwise.

srtjstjsj8• 6 years ago

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.

ccktlmazeltov2• 6 years ago

that's rock solid (so no need to unit test anything)

that is crazy talk, the amount of user error that can happen in an Excel spreadsheet means that you would need to do crazy macro work in order to maintain the integrity of the data

bigiain• 6 years ago

Still, you have data entry that everybody in the field understands, that's rock solid (so no need to unit test anything)

Hey @TeMPOraL, there's a guy with a withdrawn PhD thesis on line 1 who'd like a word with you...

http://blogs.nature.com/naturejobs/2017/02/27/escape-gene-na...

jonplackett2• 6 years ago

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.

ImaCake2• 6 years ago

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.

desas• 6 years ago

They're not using Excel for any other NHS databases

I assure you that you're wrong. The SaaS I work for replaces a suite of independently re-invented Excel files used in conjunction with other SaaS. NHS trusts are our main customers, I can only assume the NHS is full of excel spreadsheets.

(Technically speaking PHE is not part of the NHS, it has more in common with the civil service in some ways.)

happytoexplain• 6 years ago

Frankly I think you're being optimistic/pessimistic (depending on your point of view). A person can be a "decent coder" by very reasonable definitions of the term and still not realize that Excel can't handle this particular volume of data.

Reason0772• 6 years ago

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.

untog• 6 years ago

This isn't really true. The people making the decisions will have more or less zero insight into the data format being used to store data, they'll just know that data is stored.

IMO, a much more likely scenario: a brittle, haphazard system was set up with extremely short notice at the start of everything. One administrative snafu led to another, ran into "well, it's working so let's not touch it", and we got to where we are today.

tqi• 6 years ago

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 they aren't, and the tools aren't flexible enough to gracefully manage the transition.

nobody99992• 6 years ago

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 expect that the CDC has a proper database system and that data from those 1000+ entities is collated into that system.

However, state and local laws/rules require specific handling of such data and can differ significantly from state to state, county to county or even town to town.

Attempting to stand up an integrated nationwide system isn't a bad idea. But doing so while trying to manage a pandemic isn't reasonable.

That many places used Excel spreadsheets for storing testing data is neither surprising or necessarily a bad thing. Until March, no one needed to have huge databases of test results -- now we do.

Complaining that places which had , at most, a few dozen cases of reportable infectious diseases should have implemented a database that can support thousands (tens of thousands?) of test results seems rather silly to me.

leokennis• 6 years ago

Look, you’re right that Excel shouldn’t be used for this. But even then, Excel definitely could be used for this.

We’re not talking big data here. We’re talking about a finite number of possible test result from a finite set of testing locations grouped on a daily basis. Let’s say 1000 test locations, where each location has a daily number of tests performed divided into “positive”, “negative”, “inconclusive” and maybe a few other options. That’s ~5000 data points per day. Should not be any problem.

However, you need to properly plan ahead about how you design your spreadsheet, how to format the columns, how to keep it performant and how to not save it in some godawful 25 year old memory-dump dumpster fire of a file format...

hanoz• 6 years ago

The dubious wisdom of choosing Excel for this aside, how many people working in (1.) computing and (2.) statistics saw a file with exactly 65536 rows, with nary an eyebrow raised? And I mean the first time they saw it, not the second, third or fourth day in a row.

commandlinefan• 6 years ago

another lunatic who says something called "pigsqueal"

And it doesn't help that there's a 25-year-old who says, "piqsqueal is outdated anyway, modern organizations use MonkeyDB and Hand Goop".

higeorge132• 6 years ago

rofl

javagram2• 6 years ago

No need for a “front end” or “backend” or “pigsqueal”. MS provides a perfectly workable all in one tool, Access, for this kind of work. You can even use Excel as a front-end to access data if you want.

taxcoder• 6 years ago

I have spent plenty of time running Access programs for employers. You better have a Frontend and Backend if you want data integrity.

II2II• 6 years ago

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.

maerF0x0• 6 years ago

Exactly...

"But you wouldn't use XLS. Nobody would start with that."

Actually almost every non-technical person I know that wants something "database like" ends up in google sheets or excel first.

dahart• 6 years ago

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

adolph• 6 years ago

Excel is great but I think at a certain level folks should start using Python and Pandas. The challenge is understanding when and getting the right resources to the right people.

https://training.talkpython.fm/courses/move-from-excel-to-py...

hermitcrab4• 6 years ago

The conceptual leap from Excel to Python+Pandas is pretty huge. Especially if someone hasn't done programming before. That is part of the reason I created https://www.easydatatransform.com .

slykar• 6 years ago

"But you wouldn't use XLS. Nobody would start with that."

Oh. "Every" sales and business person probably would. Probably.

1-more• 6 years ago

Well they'd at least start with XLSX unless they've got a very old copy of Office

jjuhl• 6 years ago

Excel? Really? They are using that crap for nation wide reporting? Wow, mind blown. Epic fail.

BSG-Actual• 6 years ago

A non-technical senior manager who was in charge of a project I worked on went on to manage England's contact tracing. Not sure if he was directly responsible for picking Excel, but everyone who worked under him was not the least bit surprised when this news came out.

The project failed spectacularly if you were wondering.

jl62• 6 years ago

It’s fun to point and laugh, but is this really just the consequence of an unprecedented health emergency demanding a rush job?

As far as bugs go, it doesn’t sound that bad. They didn’t lose data - they just processed it late? And they spotted it within days/weeks, and have a workaround/correction already? And it’s only the reporting that was wrong, not the more important part where they inform people of results?

I’d rather have this system now than be waiting for the requirements analysis to conclude on the perfect system.

codeulike8• 6 years ago

Good points

Partly I think the Excel bit is news because Excel (and mistakes made with Excel) is easily relatable to people. But bugs always come up and most other bugs are just as stupid. If it had been an off-by-one loop error in some C code somewhere it would be just as dumb but you'd get none of the facepalm memes all over Twitter.

bobbylarrybobby2• 6 years ago

Off by one errors are all alike; every excel error is an error in its own way.

politelemon• 6 years ago

They had a budget of GBP 36 million. It's pretty bad.

lozf• 6 years ago

Serco received 45.8 Million in March (not disclosed until April), and I'm pretty sure they've had more since.

jl62• 6 years ago

What’s your source for that budget? The error was by PHE, not Serco.

gpmcadam3• 6 years ago

You need to back that number up with real facts, as this article is regarding the PHE handling of reporting.

chasd005• 6 years ago

I would say it's error from a rushed (and understandably so) job, I've been involved in a number of Covid related projects at the US county and state level. The teams and departments are having to move very fast with difficult technical problems. Keep in mind, it's not like they were just sitting around with nothing to do pre-covid. Basically, when the pandemic hit, everyone got a second job to go along with their day job.

adwww3• 6 years ago

There's a test and trace system that is supposed to phone everyone with a positive test result and get details of all their close contacts (people they sat in an office with, restaurants they visited, etc), who are in turn contacted.

A delay in the test results means the contact tracing could not happen, and people will have been going around spreading the virus they caught off the original, known cases.

Also, the whole test and trace system has been in the news a lot recently here for various failings, and things like this will just further knock people's confidence in it.

visarga2• 6 years ago

A delay in the test results means the contact tracing could not happen, and people will have been going around spreading the virus they caught off the original, known cases.

A recent piece in The Atlantic argues that we got it all wrong, we should test backwards (who infected the current patient) instead of forwards.

This particularity is caused by the dispersion factor K which is a metric hidden (conflated) by the R0, meaning that the same R0 would be handled differently depending on the K factor.

A related suggestion was that we should focus more on super-spreaders as COVID doesn't spread uniformly. That's why the probability of finding a cluster is higher looking back than forward - most people don't get to actually spread the virus.

https://www.theatlantic.com/health/archive/2020/09/k-overloo...

JoeNr76• 6 years ago

Well, I don't see why developing an application for transforming csv files to xls files would go faster than developing an application that uploaded csv files to a database.

I could see that i could be a rush job problem, but in this case they're not gaining any time.

intended• 6 years ago

This is what it most likely is. Analogous to starting out on an easy to hack together tech stack, succeeding, and dealing with tech debt when something goes wrong.

I guess its easier to understand since everyone uses excel, however it does end up giving a halo of blame to excel, as opposed to human processes.

curiousllama3• 6 years ago

You’re right - but the biggest issue is clearly one of experience. Someone was put into a critical role they weren’t prepared for - a position in which their failures cost lives. They don’t have a redundant process, or error checking, or even someone familiar with spreadsheets.

That’s the scandal: it’s the type of basic error that it screams “they’re not handling this well.” You wouldn’t tolerate your new SWE colleague asking “What’s an array again?”

If publishing the whole set of data was delayed by excel crashing - fine. But silent failure because they ran out of columns? Cmon...

sueders1012• 6 years ago

No. There is difference between a little rush job, and an incompetent rush job. I’m only speaking for my own experience, but when put under pressure most competent teams don’t end up throwing away such important data. I’ve been incompetent for most of my career, but it’s given the serious of task this is inexcusable. I don’t know how things work elsewhere, but checking that the same number of unique records(by some composite key) are ingested as exported isn’t an advanced concept. Know the value of the data you’re handling. Marketing data <> health data. If health data doesn’t line up: you figure out why and solve it. If you don’t have anyone available to do so then the problem is managerial/structural.

mFixman2• 6 years ago

That has been the COVID experience in Britain in the last couple of months: the government is spending a ton of money in training new people and creating new processes, which is causing a ton of slowdowns and bugs.

In the grand scheme of things, having problems because of inexperienced people is much better than not having enough people like during April and May.

tomatocracy2• 6 years ago

I think that's too generous. It would indeed have been excusable for perhaps the first 2 weeks. But at this point, they've had an effectively unlimited budget, likely access to engineers working for big consultancies and big tech who would have been quite happy to literally drop everything to help out and 6 MONTHS to build a system that worked. And they are still using an Excel spreadsheet.

nabla9• 6 years ago

The most consequential Excel error was The 2010 Reinhart-Rogoff error in the paper "Growth in a Time of Debt" https://www.nber.org/papers/w15639

The paper claimed that average real economic growth declines 0.1% when natonal debt rises to more than 90% of gross domestic product (GDP). When you correct the error it shows 2.2% average increase in economic growth.

Paul Ryan used it in the US for Republican budget proposal to cut spending and it was also used in EU to implement Austerity policy that hurt people.

nickff3• 6 years ago

I think you're overstating both the paper's impact, and the significance of the error.

Most such papers are used to support existing policy preferences, not drive them. In addition, the error didn't reverse or erase the correlation, it diminished it, and removed the inflection point from the curve.

sheetjs3• 6 years ago

It was literally cited by Britain's finance minister George Osborne as justification for UK government austerity:

As Ken Rogoff himself puts it, "there's no question that the most significant vulnerability as we emerge from recession is the soaring government debt. It's very likely that will trigger the next crisis as governments have been stretched so wide."

https://web.archive.org/web/20100414205630/http://www.conser...

strangeattractr2• 6 years ago

The most salient point of the paper was the inflection point of 90% for sharply lower growth. It was and still is used by proponents of fiscal austerity to argue against fiscal stimulus.

GuB-42• 6 years ago

Here is an excellent video about this subject (in French, but the auto-translate subtitles are understandable) https://www.youtube.com/watch?v=yeX_Zs7zztY

To summarize:

- The first two thirds of the video is a retelling of the story, the last third is an analysis of the data

- There are two problems in the original paper: a weird way of computing averages, and a mistake in their Excel file.

- There is a correlation, but it is weak (R2=0.04), and if there is an inflection point, it is around 30-40%, not 90%.

- That paper is likely to have been selected by politicians to support their policies instead of influencing them (confirmation bias).

FabHK2• 6 years ago

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 be seen in the broader context of austerity mania: the obviously intense desire of policy makers, politicians and pundits across the Western world to turn their backs on the unemployed and instead use the economic crisis as an excuse to slash social programs. What the Reinhart-Rogoff affair shows is the extent to which austerity has been sold on false pretenses."

I'd say the paper made it certainly easier for the Very Serious People[1] to push through their austerity agenda.

[0] https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel...

[1] Krugman's derogatory term for duplicitous policy wonks (like Paul Ryan) that pretend to be thoughtful and serious and concerned about the nefarious long term effects of debt when discussing stimulus and spending and social programs, but then typically turn around and implement huge tax cuts without second thoughts...

dredmorbius• 6 years ago

There's a long history of spreadsheet errors and literature on same going back a few decades now.

In a business sim class in college a couple of decades back, I discovered that the Lotus spreadsheets (as I said: a couple of decades back) had a totalling error which double-counted individual row totals in the bottom line (everything was twice as profitable as the spreadsheet indicated).

At an early gig, one of the senior developers instituted a practice of code walkthroughs on projects (only a subset of them). One of these involved, you guessed it, a spreadsheet (we used a number of other development tools for much of our work), in this case Excel. Again, numerous errors which substantively changed the outcome of the analysis. One of the walkthrough leader's observations was that you could replace all of the in-cell coding with a VBA macro making debugging far easier (all the code and data are separated and in one place each).

The particular analyst whose project this was: he insisted to the very end that this "wasn't a program" and he "wasn't a programmer" and that the walkthrough didn't apply to his situation. Despite the errors found and corrections made.

At the time (mid 1990s) the walkthrough lead turned up a paper from a researcher in Hawaii on the topic. I'm not certain it was Raymond Panko, but his 2008 paper (a revise of a 1998 work) discusses the matter in depth:

https://web.archive.org/web/20070617041554/panko.shidler.haw...

chasd005• 6 years ago

interesting, i was on a conf call 3 hours ago that was an almost identical use case. Excel gets used because word comes to a director at a health department that says "we need to collect testing results from 500 different facilities in a way their, and our, existing systems do not support" and they're given maybe 3 days to get it done along with the rest of their job. Putting together a technical team would take a week minimum let alone requirements gathering and analysis. So what do they do? They fire up Excel, add column headers that represent the data they need, and then send it to a Sr. Manager and tell them to make it happen.

Aeolun2• 6 years ago

"But you wouldn't use XLS. Nobody would start with that."

Haha. This would be funny if it wasn’t so abjectly false.

rbanffy2• 6 years ago

How much did the UK government pay for this?

punnerud• 6 years ago

In Norway there was a $14 billion calculation error based on Excel, that resulted in opening of new oil fields in the Barents Sea: https://translate.googleusercontent.com/translate_c?depth=1&...

robomartin• 6 years ago

Oh, Excel, how I hate to love you...

As is the case for many, Excel is one of my main tools. From financials to data gathering to electrical, mechanical or software engineering, Excel has always been there. It is fair to say I have made lots of money thanks to this tool.

And yet, every so often...

Many years ago a rounding error in a complex Excel tool we wrote to calculate coefficients for an FPGA-based polyphase FIR filter cost us a little over six months of debug time. I still remember the "eureka!" moment at two in the morning --while looking at the same data for the hundredth time in complete frustration-- when I realized we should have used "ROUNDUP()" rather than "ROUND()".

Most recently, I was working with a client who chose to build a massive Excel sheet to gather a bunch of relevant data. The person doing the work seems to think they know what they are doing (conditional formatting and filtering don't make you an expert). This poor spreadsheet has every color in the rainbow and a mess of formulas. It's impossible for anyone but the guy who created it to touch it.

Here's a hint:

Do not mix data with presentation. Where have we heard that before?

This is one of my pet peeves with Excel. If you need to gather a bunch of data, do it. Treat Excel like a database (apply normalization if you can!) and keep it with as little formatting as you possibly can. Then do all the formatting and calculations on a "Presentation" sheet or sheets. Just don't pollute your database with formatting.

EDIT: Thinking about the UK problem, if they were working with a ".xlsx" file and accidentally saved it in ".xls" form, well, as they say, "There's a warning dialog for that".

What surprises me the most about these kinds of incidents is that people keep working on the same single document. In other words, no semblance at all of what the software business knows as version control.

Decades ago I adopted the idea that storage is cheap and always getting cheaper. If I am working on something critical, I never work more than one day without a backup. I make a copy and continue editing. In most cases I make a new copy every single day.

krsdcbl• 6 years ago

The actual title of the article is awfully clickbaity and misleading. As i understand the issue is not at all Microsofts fault, but NHE using an inadequate way of storing the data

masterproc2• 6 years ago

Just dropping this here: In Apple Numbers you can work with tables that contain up to 1,000,000 rows (and 1,000 columns). :)

masterproc2• 6 years ago

Just saying in Apple Numbers you can work with tables that contain up to 1,000,000 rows and 1,000 columns. :)

outworlder• 6 years ago

Many comments already talked at length on use-cases for Excel, so I won't go for a 'me too'.

Instead, I just want to share this video from Joel Spolsky, aptly titled "You Suck at Excel".

Turns out, most people do suck at Excel. Myself included.

https://www.youtube.com/watch?v=0nbkaYsR94c

rossdavidh2• 6 years ago

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 too many tears for Microsoft, but it does seem a bit off-target to blame "Excel" for this...

kelnos• 6 years ago

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.

sriram_malhar• 6 years ago

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.

bobbylarrybobby2• 6 years ago

The issue with is excel is that its failure mode is suboptimal. Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc). But excel just throws away your data?

tomc1985• 6 years ago

Indeed, you have to manually inspect results to verify correctness. If you're lucky maybe your error will result in one of those weird error values being shown instead, but way too often that is not the case

Not to mention that complex formulae are still usually expressed as a bunch of gobbledygook in the cell value textbox, which is about as easy to parse as minified Javascript. And that's to technical users like ourselves.

curiousllama3• 6 years ago

If it fails, it’s often silent. If not silent, hidden. If not hidden, misleading, if not misleading, opaque. If not opaque, trivial.

Excel is a wonderfully powerful tool that’s very bad at handling errors clearly.

Closi8• 6 years ago

In this case, Excel throws an error and says that not all rows have been loaded.

It seems that warning has just been ignored by the user.

hurrrrrrrr• 6 years ago

Programming languages will generally crash at runtime when an unrecoverable error occurs (index out of bounds, memory allocation failed, null pointer, etc).

JS would like to have a word with you.

nabilhat• 6 years ago

Over-13-years old Excel already opened delimited flat files natively, and it threw warnings when you opened a .csv file that exceeded the worksheet row limit. Someone in the distant past was just clever enough to hack together the script (with no error handling) without being curious enough to know it wasn't necessary in the first place.

yawaramin3• 6 years ago

Nobody is blaming Microsoft. From the article:

And it appears that Public Health England (PHE) was to blame, rather than a third-party contractor.

tonyedgecombe2• 6 years ago

Blaming PHE seems to have become a regular occurance this year. I wonder if it's justified or not.

mcfedr• 6 years ago

If they had used the newer format, we'd be talking about this in a few weeks time when the larger limit was reached.

This is a problem of bad coding, and using the wrong tool for the job.

A defensive coding practice would have prevented this from going unseen. Using a database to store data would have prevented such arbitrary limits.

rossdavidh2• 6 years ago

Yeah, something like "Missing Covid-19 test data was caused by using spreadsheet instead of database", would be more accurate.

ComodoHacker• 6 years ago

You get CSV files as input and have to upload them into the database. You don't need Excel at all, neither in XSL, nor in XSLX format.

My bet is the biggest problem here is subcontracting this work to the lowest bidder, presumably from some developing country.

baud147258• 6 years ago

subcontracting this work to the lowest bidder

knowing a little how things (don't) work in the UK, it's likely subcontracted, but to a company belonging to a mate of the director in charge of the whole thing

yawaramin3• 6 years ago

I'd take that bet ;-) From the article:

> And it appears that Public Health England (PHE) was to blame, rather than a third-party contractor.

mshook• 6 years ago

I call that the Microsoft Office Syndrome.

Tools such as Excel or PowerPoint are almost "too good" as in they allowed users to come up with usage which aren't what the software was made for.

As others have said regarding Excel, it's not so much what it's capable of but what you do with it and how you do it.

I've seen people using Excel as a tool for trading, as an inventory database for a warehouse, as a shared database to track task progress, as a Gantt chart, as a calendar and so on.

PowerPoint is the same. Who hasn't been given a PPT file as a manual for something?

"Oh, it's all in the powerpoint".

Or been given the same kind of file to familiarize yourself about something at your (new) workplace. Issue is you rarely have any of the material the speaker used to present the whole thing so you're left wondering about the meaning of it all while reading bullet point lists...

No, a powerpoint is not a proper doc...

So in a way, I have to applaud MS as they did great with these but almost too great...

segmondy• 6 years ago

I have seen this type of bug in production. After seeing it, I refuse to accept xls for data. CSV all day, whomever needs xlsx can transform it into that after.

zmmmmm• 6 years ago

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.

ekianjo2• 6 years ago

The problem is that PHE's own 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.

Why would they even use XLS in the first place? CSV files have no such limitation, you can have CSV files that hold billions of records.

mr_toad• 6 years ago

We need to stop accepting data in Excel. Full stop. One day it’s going to kill someone.

swrobel• 6 years ago

Reminds me of my days working in fiance. I was basically a broken record that just repeated: Excel is not a database

tsjq2• 6 years ago

typo :)

totaldude872• 6 years ago

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 contract instead of deleting them.

3-utsourcing specialists Mouchel had to endure a £4.3 million profits write down due to a spreadsheet error in a pension fund deficit caused by an outside firm of actuaries

2- Canadian power generator TransAlta suffered losses of $24 million as the result of a simple clerical error which meant they bought US contracts at higher prices than they should hav

and the Biggest one is. -

Basic Excel flaws and incorrect testing led to JP Morgan Chase losing more than $6 billion in their London Whale disaster.

https://floatapp.com/us/blog/5-greatest-spreadsheet-errors-o...

tsjq2• 6 years ago

and one more:

"Scientists rename human genes to stop Microsoft Excel from misreading them as dates. Sometimes it’s easier to rewrite genetics than update Excel" https://www.theverge.com/2020/8/6/21355674/human-genes-renam...

computerfriend• 6 years ago

Also the post-2008 financial crisis austerity programs based on Excel analysis.

https://mathbabe.org/2013/04/17/global-move-to-austerity-bas...

teekert• 6 years ago

Yeah, why there is no "scientific" mode to just turn of all "smartness" has baffled me for years and will probably continue to baffle me for years to come.

Or even an algorithm that can detect that you are using gene name from the cells around march1 and sept7.

simonh2• 6 years ago

I think that's a good way forward, Excel is a fantastically powerful, ubiquitous tool and isn't going away any time soon. If you're going to standardise on a technology, it makes sense to design your systems to work well with that technology. That includes making identifiers that you need to be treated like strings look like strings to your tools. A little bit of thought in advance and maybe some small compromises early on can save massive problems down the line.

thedudeabides52• 6 years ago

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/business/tokyo-stock-mark...

The glitch stemmed from a problem in the hardware that powers the exchange, said the Japan Exchange Group, the exchange’s operator, during a news conference. The system failed to switch to a backup in response to the problem, a representative said.

totaldude872• 6 years ago

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

Bukhmanizer2• 6 years ago

I think similar to “code smells” in software engineering, using excel for critical data pipeline work can be considered a “data smell”. It’s not bad in and of itself, but it’s a signal that something may have gone seriously wrong with the data engineering process.

swiley3• 6 years ago

Is it really that hard to spend 5 minutes setting up a decent database? Use sqlite if you really want something simple.

I guess you still have to understand locking (especially on distributed filesystems.) I've certainly seen people mess that up with spreadsheets.

_trampeltier• 6 years ago

The problem is people can't do this in a office. Nobody has admin rights on there computers. Everybody know how yo use Excel. We all know, if you have to make a ticket to your office IT for such things, you mostly have to wait a long long time and it never, never works the first time. I guess that is one of the main reasons, why the world runs on Excel (and MS Access). It just works, it's just there, no dealing with IT.

29athrowaway• 6 years ago

Whenever you try to do something properly, some person in your organization condescendingly says "perfect is the enemy of the good".

And because Excel is "good" for a vast number of use-cases then people use it for everything.

EpicEng• 6 years ago

Sure, I'll just teach tell my wife to learn SQL and some devops while she's at it. After that I'll coach her on how to get buy in from management on this new system while she neglects her primary duties.

Throwing up a database and integrating it into a workflow/system isn't something anyone can just get up and do. I have to imagein you know that.

numpad0• 6 years ago

Do note that anything other than Excel, UI/UX wise, failed.

When people wants a “database” they fire up Excel, start punching in numbers, solar calculators next to keyboard, and use eyeballs to search for strings.

ako• 6 years ago

Things are never 5 minutes. And it is just as easy to make mistakes with sql as it is with excel.

mFixman2• 6 years ago

Every time I see a problem like this I can't stop thinking that computer education is terrible everywhere.

We are able to teach almost everyone how to use complex software like Word and Excel. Why can't we teach people how to use a terminal, SQLite, or how to create a very simple Python script?

flarg• 6 years ago

No it isn't, but in any big org the plebs are only allowed to use Office and anything else is IT captured.

collyw6• 6 years ago

It takes a course to understand how to to use a relational database.

MS Access used to come as standard with Office and is actually the perfect solution to many of the problems that businesses use Excel for. It's very rarely that people actually used Access as Excel was far more intuitive and good enough for many projects especially in the early stages.

blablabla123• 6 years ago

Sure, Excel is instantly available. But also you get charting and formulas. Especially with formulas they've done something really right. You can transform a column in a few seconds while keeping the old one, tweaking the new one and having a graphical preview through the chart feature at the same time. Of course every Spreadsheet software can do this, but to me that's the ultimate killer feature

koliber• 6 years ago

They are costing money. No doubt.

At the same time, the business world runs on Excel. How much money is Excel making?

I've done my share of cursing at Excel at various jobs. At the same time, I am grateful for the quick and easy way it allows me and many others to manipulate data. It's unfair to just cite the costs of using Excel without acknowledging the benefits it brings.

collyw6• 6 years ago

The real question should be would it be cheaper to replace it with a proper solution (probably something custom built application written by a software engineer).

Excel's ease of use is it's downfall. It is the worlds most popular database, despite not actually being a database. I have wasted countless hours dealing with Excel where something else should have been used. I built a database for a friend recently, I think 75% of the work was cleaning the existing data from the excel to get it into the database.

isolli2• 6 years ago

And how about this one: an Excel error was the cause for much unnecessary austerity after the 2008 financial crisis, costing potentially tens (hundreds?) of billions in lost output.

https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel...

galkk• 6 years ago

I read an article and I feel that this is a deflection of a guilt much more than Excel issue.

jp0d3• 6 years ago

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.

kevin_nisbet• 6 years ago

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.

jp0d3• 6 years ago

I'm not surprised. I've seen all kind of shit working for enterprise clients. Only one of them adhered to very high standards and had processes for everything. They were a military contractor and most of the clients were ex-military.

jaza• 6 years ago

See, they had far less than 65,536 employees, so Excel was fine for their workflow!

jp0d3• 6 years ago

lol.. These days it can support up to a million employees! :D

known2• 6 years ago

I use AWK to do floating point calculations and copy the results to Excel https://news.ycombinator.com/item?id=14018450

known2• 6 years ago

"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

jugg1es7• 6 years ago

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.

tasogare• 6 years ago

Quote your strings. The problem is more with how you generate CSV (which is easy to break in the first place) than Excel’s fault. It’s like writing your identifiers as numbers in JSON instead of string then complain the leading zeros are removed.

yawaramin3• 6 years ago

Quoting doesn't work. Try opening this CSV with Excel:

    a,b
    "01",01

Excel interprets both as the same number–1.

zelphirkalt2• 6 years ago

Are you asuming creation of CSV? 'cause most excel users wont be aware of why they should store in csv instead of excel's proprietary formats.

andreareina3• 6 years ago

IME numeric identifiers are just asking for trouble. Some systems are sensitive to leading zeros, some systems will zero-pad, <input type="number"> loses zero-padding, etc. Worked with a client once with references to the same entity scattered across two databases, one of which zero-padded the identifiers and the other didn't. Oy vey.

throwaway_pdp094• 6 years ago

IME numeric identifiers are just asking for trouble

That's a failure of the system if it can't be told to not interpret the data. However you're saying the world is as it is; can't argue.

pjmlp2• 6 years ago

I just upvoted you.

You are fully correct, I have seen plenty of stuff like that in life sciences projects.

Maha-pudma3• 6 years ago

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.

reportgunner5• 6 years ago

Well Excel trying to be clever stems from people not trying to be clever. We truly reap what we sow.

Ntrails• 6 years ago

As an intern I was once given a spreadsheet of manually entered daily data, stored by a human being over the previous couple of years. Obviously he'd entered the dates in whatever form he felt like at the time and Excel had parsed them in a variety of godawful ways not least of which was defaulting to American month/day assumptions before UK day/month.

I think it would have been far quicker to just manually write a new column interpreting the dates based on previous/next etc. Instead I spent God knows how long trying to be clever, failing, and being embarrassed that I could not solve this obviously trivial problem.

jasode3• 6 years ago

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

We're biased to bash on Microsoft for being "too clever" but maybe we need a reality check by looking at the bigger picture.

Examples of other software not written by Microsoft that also drops the leading zeros and users asking questions on how to preserve them:

- Python Pandas import csv issue with leading zeros: https://stackoverflow.com/questions/13250046/how-to-keep-lea...

- R software import csv issue with leading zeros: https://stackoverflow.com/questions/31411119/r-reading-in-cs...

- Google Sheets issue with leading zeros: https://webapps.stackexchange.com/questions/120835/importdat...

Conclusion: For some compelling reason, we have a bunch of independent programmers who all want to remove leading zeros.

stjohnswarts5• 6 years ago

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

dt3ft• 6 years ago

I have no idea who downvoted my post

That would be the Excel devs working at Microsoft. They read HN. I can feel it.

edejong• 6 years ago

Excel devs, do you hear me? Repent for your sins! Go back to Microsoft and fix those problems. The cost of Excel mishandling numbers and dates must be many orders of magnitude higher than your lifetime salary.

Thank you.

TacoSteemers3• 6 years ago

Maybe it can be solved by prepending "ID:" for exports, and removing it on import. It could be proposed as a convention for all HC software companies.

altacc2• 6 years ago

Introducing standards across many companies is incredibly difficult but your main problem is that a lot of healthcare IT systems are very old and not actively maintained, they're essentially on life support. So changing the format of a file, or requiring new import cleaning, is going to break integrations and data sharing in many places. Basically it'd create far more problems than it's attempting to solve.

IanCal• 6 years ago

Although make sure to not use that for the first column heading, an uppercase I will make excel think it's a SYLK file and then refuse to open it.

petre2• 6 years ago

Just format the identifier cells as text. I've also had this problem, this is how I solved it.

simonh2• 6 years ago

That's fine for an individual working on a specific set of data in a specific sheet, but this isn't just a problem for one individual or a small team that can solve it and move on. It's a systemic problem throughout many vast organisations, with continuous influxes of new personnel and constantly changing requirements. When you get an XLS sheet sent over to you from another team that already made this mistake, it's too late and this happens all the time.

dragonwriter3• 6 years ago

Just format the identifier cells as text

CSV is text. If you mean in Excel, if you opened it in Excel (rather than importing and choosing non-default options), you've already lost the data so formatting doesn't help you.

eCa2• 6 years ago

I used to work with books, which obviously includes ISBNs. They are 13 digit numbers, helpfully turned into scientific notation by Excel.

eru2• 6 years ago

Don't ISBNs also contain an X sometimes?

Cyberspy• 6 years ago

The same problem happens with UK phone numbers, where the leading 0 gets chopped off Solvable if there's a space between the dialling code and the number, but when individuals enter their number, they don't always do this

eythian• 6 years ago

I used to do migrations between library systems, and often they'd export from the old system as CSV. One rule I gave them was "never open this file in excel", because they'd always try to be helpful by tidying something and I'd end up with scientific notation ISBNs, dates that were in one form if they could be coerced into US-style infix, otherwise left in the original form, barcodes losing zeros, etc. etc.

reportgunner5• 6 years ago

The right way to open CSVs in Excel is: Data -> From Text -> Browse to your csv -> Delimited -> Comma -> Next -> Select Text formatting for columns with leading zeroes -> Finish

You can either send xlsx with format or csv without format. If this would be disabled then we'd have another group of people complaining that their dates from CSV are not parsed.

ss64• 6 years ago

and to enforce that, remove the file association which opens .CSV files in Excel by default. If every machine opened CSV files in notepad a huge percentage of 'Excel' problems would just go away. It could be worse though, try opening and saving a CSV in Word and see what that does.

ivanhoe2• 6 years ago

Couldn't you validate the data on upload or if it's such a common problem just auto-fix it by left padding w/ zeros?

SifJar2• 6 years ago

auto-fix by padding assumes that the only way to get an ID that's too short is by excel removing the leading zeros - presumably it could also be human error when entering etc. (in theory I suppose that would be validated in whatever form the number is entered into in the app that generates the CSV, but hard to say for sure). Or some other issue in data processing pipeline erroneously truncating the ID number some how.

Validating the data would at least prevent getting invalid data into database (and presumably this is happening already), but it doesn't actually "fix" the problem, you still then need the original provider of the data to fix what's missing.

rutthenut4• 6 years ago

Validating on upload is sensible and should be done anyway. However, left-padding is not always going to work, it depends on the data. Common problems I see are with phone numbers. UK mobiles begin 07nnn. Excel kindly drops the leading zero and then really helpfully treats the long number as being an exponential-format floating point number, which cannot be turned back to the original number. In our systems, we require country codes in front of the numbers, so UK mobiles should being 447nnn. That doesn't lose the leading zero but still gets the E-format mess when opened by Excel. Customers often miss out the 44 and use the national 07 format. If that leading zero is missing, it can be quite difficult for a system to reliable determine whether that number, beginning with a 7, is an international number for a destination in Russia, or a user error and should've been 447, from an 07 data value.

jugg1es7• 6 years ago

These aren't fixed-width fields with a known length. The length of the field is unique to the person sending the file and they don't tell us how long it's supposed to be.

zentiggr7• 6 years ago

There are data sources that have a clear unambiguous specification of what information is in each field, and exactly how they are formatted, such that you could auto-parse the column. And even have no data entry errors, so that you can rely on that parsing and have clean error free imported data.

There are many more that don't have a clear spec, or even if they do, have every possible variation of data corruption / keying errors / user misunderstanding / total 'don't give a shit, I'll enter it how I want, that's what the computer is supposed to handle' problems in that source data, that make parsing an absolute nightmare.

I've had to manually review a list of 10k+ data readings monthly, from an automated recorder, because the guy who was supposed to copy the collected data files as is and upload them, instead opened each one and fixed what he (badly mistakenly) thought had been recorded wrong. Different changes in a dozen different ways based on no particular logic beyond "that doesn't look right". And un-fireable, of course.

rutthenut4• 6 years ago

Agree with the problem reported here - the fact that Excel will drop leading zeroes and will turn phone numbers into Exponential format causes many live issues where customers use Excel to work on 'CSV' files :(

rutthenut4• 6 years ago

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 strings, escaping characters and so on, but changing the content when a delimiter is found should not be added to that list.

You bold point is the most important, the default behaviour of Excel when opening a plain text CSV file is to alter the content for display, applying magic and often-unwanted formatting rules. That should be optional.

It should be possible to open a text CSV file in Excel, view the contents in columns but the same textual form, save the file in CSV format and open it in another viewer/editor and still see the same content as the original file.

robk• 6 years ago

The problem is windows doesn't have a preloaded double click viewer for csv except excel if you have Office. The average user just double clicks. If notepad or wordpad did any kind of formatting for viewing it would be less an issue. So you have to solve for excel as a dev knowing that's what 99% of people will use.

stjohnswarts5• 6 years ago

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.

cm2187• 6 years ago

Also long numeric ids are rounded by Excel.

And fucking with dates.

mratsim• 6 years ago

Working internationally with CSV holding dates is a recipe for disasters.

mikorym• 6 years ago

It is sometimes possible to write code to alter your CSVs to retain the strings.

The first thing would be to write them as: entry1,"0123456789",entry2 rather than entry1,0123456789,entry2. This has worked for me in some instances in Excel whereby I have to escape certain things inside a string, but I would not be surprised if Excel still messes this up. For example, giving the triangle exclamation mark box and then helpfully suggest to convert to number.

If you want to go further, you can do something like write a routine that alters the CSV, such as entry1,hospitalString(0123456789),entry2. Sure, there are problems with this too, but Excel can break a lot of things and the above examples I do use in practise (the first example I put the double quotes to escape single quotes in foreign language unicode).

Another thing Excel can do is break your dates, by switching months (usually only for dates < 13th of the month, but often a partial conversion in your data for < 13th and >= 13th) or convert dates to integers.

tomp• 6 years ago

So what's the big deal? Just read back the numbers, if it's less than 10 digits prepend zeros. Am I missing something (probably, which means you're leaving something out in the description of the issue)

jugg1es7• 6 years ago

The 10 digits was just an example. There like 100 different ID types with different formats/lengths in healthcare.

baobabKoodaa3• 6 years ago

How on earth is this the top rated comment? I would downvote it if I could. As other people have noted, you can import CSVs values as strings instead of numbers.

Furthermore, losing preceding zeroes in number-typed values is not unique to excel; it is a common feature in all typed programming languages.

SifJar2• 6 years ago

you can import CSVs values as strings instead of numbers

Of course, but the problem isn't that the person who posted the comment doesn't know this - it's that many users of their systems don't know it. Most people are just going to accept whatever defaults Excel suggests and not know any better, causing problems down the line.

lr4• 6 years ago

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 columns that contain leading zeros. The ability exists, but I have found scant few people who know how to actually use the product properly.

Oh, and I also work in healthcare.

mewpmewp22• 6 years ago

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

ineedasername2• 6 years ago

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.

pif2• 6 years ago

Excel interprets a number and then formats it as a number,

Unless you instruct it to interpret the field as a string.

but in healthcare, 10 digit numbers are really strings.

I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

lr4• 6 years ago

This would assume people (many who have over a decade of experience using Excel) actually know how to do that. The largest problem with Excel is people.

hunter2_2• 6 years ago

With US ZIP (postal) codes, padding with zeroes can actually be ambiguous because we have "zip+4" which is often stored as two fields, but just as often concatenated into a single representation. If you have a zip+4 of "05555-0444" in two fields that each lose leading zeroes, and then concatenate to "5555444" you can't reconstruct it with certainty (00555-5444 or 05555-0444 or 55554-0044).

I had a package get seriously delayed one time because it kept being sent to a city whose 5 digit zip code was equal to [my 5 digit zip code, less its leading 0, and the first digit of my +4]. Fun times.

oauea3• 6 years ago

Unless you instruct it to interpret the field as a string.

Except double clicking a CSV does not give you the option to do this. At this point Excel already decided to corrupt your data. And guess how most users open CSV files? That's right, they double click.

I'm wondering, if you expect 10 digits and you get less than that, how difficult is it to add some padding zeroes?

If you expect 10 digits and get less than that, you have corrupted input data. Trying to "fix" this is exactly the sin excel is committing. Don't do that.

radmuzom• 6 years ago

Down-voted as it is factually incorrect. See comment by reportgunner below.

noja• 6 years ago

Does it strip leading zeroes if you format the numbers as strings with quotes like this?

blah,blah,"00001553",blah

kazinator• 6 years ago

there isn't a way to get them back if you save/overwrite.

Of course there is, in your case. If patient identifiers have a fixed, known length, then you can pad with leading zeros to recover them.

You only have a problem if 012345 and 12345 are distinct patient identifiers.

It is bone-headed in the first place to use numeric-looking identifiers (such as containing digits only) which are really strings, and then allow leading zeros. Identifiers which are really strings should start with a letter (which could be a common prefix). E.g. a patient ID could be a P00123.

This is useful for more than just protecting the 00. Anywhere in the system, including on any printed form, if you see P00123, you have a clue that it's a patient identifier. An input dialog can reject an input that is supposed to be a patient identifier if it is missing the leading P, or else include a fixed P in the UI to remind the user to look for P-something in whatever window or piece of paper they are copying from.

The main point in my comment is that instead of shaking your fist that the behavior of other users in the system, such as those who choose Excel because it's the only data munging thing they know how to use, you can look for ways that your own conventions and procedures are contributing to the issue.

If people are going to "Excel" your data, and then loop it back to you, maybe your system should be "Excel proofed".

jugg1es7• 6 years ago

The lengths of the identifiers are not known, man. We are dealing with like 200 hospitals. They all have various kinds of Electronic Medical Record system like Epic or Cerner, or one of the 15 other third party vendors that all have custom implementations. Healthcare is insane in our country.

slowhand09• 6 years ago

The issue I see is you have people who don't know the difference between a string data type and a number. I keep see "Listen to the scientists" and "Listen to the doctors". There are many facets to solving problems. Maybe many disciplines may be better at solving it. Had you/"healthcare" contacted folks with software expertise rather than being your own experts, this would have been a non-problem.

aaron695• 6 years ago

This will not be Excel, but a external export library.

I doubt the current Excel which MS spends billions on would not be opening data if the XLS is over the 'size' limit but a valid format.

I guess MS hate is easier than thinking about software and logic and being a better programmer and stuff.

ksec• 6 years ago

I hope people finally understand ( At least I hope Microsoft Understand ) Their biggest asset isn't actually Windows. But Excel.

Some on HN will definitely comes in with (x)Office Support MS Excel file as well etc.

There are trillion dollar worth of revenue relying on Excel. In the best case scenario, no one wants to work, rework, or even touch that Spreadsheet. Having 99% compatibility is not good enough.

beefield• 6 years ago

Excel has its place. And it is excellent tool for some workflows. However, I have started to think that Microsoft should disable the option to save workbooks. That would kind of force people to use Excel as a numerical scratchpad with fast charting what is where Excel excels.

(There is some sarcasm in this comment, but not that much)

airbreather• 6 years ago

Most people don't know about solve order and possible multiple solves for a given cell on any update.

From : http://www.decisionmodels.com/calcsecretsc.htm

When a cell in a spreadsheet refers to another cell it must be finally calculated after the cell it refers to. This is called a Dependency.

Excel recognizes dependencies by looking at each formula and seeing what cells are referred to. See Dependency Trees for more details of how Excel determines dependencies.

Understanding this is important for User Defined Functions because you need to make sure that all the cells the function uses are referred to in the function arguments. Otherwise Excel may not be able to correctly determine when the function needs to calculated, and what its dependencies are, and you may get an unexpected answer. Specifying Application.Volatile or using Ctrl/Alt/F9 will often enable Excel to bypass this problem, but you still need to write your function to handle multiple executions per calculation cycle and uncalculated data.

johndoe42377• 6 years ago

This is yet another, but subtle example of how abstract models are disconnected from reality

Another one is abysmal failure of all simulation based models to be even close and useful.

https://karma-engineering.com/lab/blog/Solved

8bitsrule2• 6 years ago

A DB system that doesn't verify that each submitted record has been included in the DB (and scream if it hasn't) ... is a DB cruising for a bruising.

topkai22• 6 years ago

It sounds like a data processing pipeline rather than a database itself. A systems A...E at hospital push csv files to a shared system F. System G pulls the data from F, passes it through an excel workbook (failure point!) for data augmentation and cleanup tasks built by a public health SME, the drops the data in location H. System I loads the data then finally loads the data to a “real” database for additional reporting.

If that sounds insane, I assure you that is a simplified version of what happens in some systems.

8bitsrule2• 6 years ago

Wow, what a mess. At the very least (which is about all that could be done) A could assign a new-record-ID to each record; I could tell F where to find it; F could verify its presence (or scream) and tell A.

alexbrower3• 6 years ago

This is clearly a case of someone not knowing the limits of a technology / how to select or use a technology. Yes, more modern versions of Excel support many more rows than 65k. As a "power" excel user in the early 2000s, it's tiring to see this same mistake made. And I might have blamed MSFT in 2008. But the "modern" world has taken an absurdly boorish response to this virus. This unfortunately looks like another example.

alexbrower3• 6 years ago

Forgot to mention: there's nothing inherently wrong with storing data in CSV files. One does have to think about consumption and processing. A Postgres database seems pretty reasonable to track information like this, but I wouldn't blame a CSV format for shortsightedness. We're not talking about actual big data here...

pjmlp2• 6 years ago

I have done a couple of gigs in life sciences and Excel is one of the big responsible for VB.NET to be still relevant.

I have met several researchers using Excel, not R, Python, Julia, nope plain old Excel, eventually with some VBA macros.

The more savvy ones, eventually ask IT for VB installation when they outgrown the VBA capabilities and carry on from there with either small Windows Forms based utilities or Office AddIns.

Any attempt to replace those sheets with proper applications has gotten plenty of push back until we basically offered enough Excel like features on the new applications.

meroes• 6 years ago

This was me when our online meetings exploded and I had to record a ton of metrics manually without any prep or software. Now there is this digusting, VBA-augmented excel sheet with a million hacks being read by the c-levels to make very important changes by. I can't vouch for a single cell being accurate at this point. Even the few automatic metrics from the meeting software are misleading from the get go, but then they meet my black-box of doom.

edanm• 6 years ago

As a data engineer, I think blaming Excel for this is the wrong framing. The real problem here is with the entire process, and specifically, the lack of testing.

Excel is great for many use cases, especially if you need people to enter data somewhere. Its UI is unparalleled in terms of quickly giving something to users that they can understand, mess around with, and verify. It's a very common use case to then need to pull in data from a bunch of Excel files, into one main repository of data (a data warehouse). That can be stored in an Excel file, although more commonly would be stored in a database.

But there are always problems with this process! There can be missing data, there can be weird data conversions because the program/language you're using to parse the data and get it into the database reads things differently than how Excel intended, there can be weird database issues that causes data loss, etc.

Complex systems always, always have bugs.

It is the job of a data engineering team to, among other things, test the systems thoroughly, and put in place systems to test against data loss, etc. It is pretty common, for example, to count the data going into a pipeline, and the data that you end up with, and make sure nothing was lost on the way.

Anyone can make a mistake. Any team, no matter how good, especially when they're rushed, can use shortcuts, use the wrong technologies because it's expedient, or simply have bugs. It is the job of the team, and of project management in general, to do all the manual and automatic testing necessary to make sure that mistakes are caught.

The real lesson isn't "Excel is bad". It's not. It's an amazing tool. The real lesson is "Data Engineering is hard, requires a lot of resources", and "all systems have bugs - testing is mandatory for any critical system".

throwaway_pdp094• 6 years ago

I don't think data engineering is hard or needs much resources. It does need people who care and who will think about things.

Anyone who comes at a problem with the mindset 'this is going to he hard' probably lacks experience and will throw big-data frameworks at it, really screwing things up. The most significant, and valuable, resource needed is thought first, and knowledge+experience second.

All IMO anyway.

visarga2• 6 years ago

Depends on context, you're talking in general, but in particular as the GP post said, there can be pressure from management to rush things, different data formats, bad or missing data and so many other pitfalls. It's hard when it's hard.

You can write a simple test to check a function is working correctly, but how do you make sure your 100,000 item database doesn't have corrupted or missing data caused by the latest pipeline update, especially if the corrupted parts are rare?

collyw6• 6 years ago

I always think that database design is pretty simple, but having worked on numerous databases designed by other, some people get it very badly wrong. "Wrong" maybe isn't the correct word, because you can use the database for what it is needed but it just requires extra joins and hacks at the application level to get around poor design.

corty5• 6 years ago

Disagree on the "users can verify" part. Excel is very hard to verify, because of corner cases like date conversion. Formulas and macros, maybe spread out all over multiple sheets in hidden away white-on-white cells, make things very opaque and impossible to verify for most users. After a long gone colleague has passed the usage of some excel file, it is treated as an arcane and unchangeable wisdom from the past, never to be really understood or changed. Just maybe used as a copy-paste value import to the next generation.

Also, recognized good practices for software development (which any excel sheet that does more than just a sum() will be) like commenting and versioning are quite hard to impossible in excel. So even if you endavour to do it "right" but with excel, it is just the wrong tool for the job.

theshrike79• 6 years ago

I vaguely remember a discussion about this and the problem boils down to Excel sheets being impossible to unit-test.

There might be, for example, a formula going through 20k lines in column F. But the one on line 1138 has a typo and the formula references an incorrect cell. No human will ever go through all the lines to check. Excel itself doesn't check stuff like that. And there are no tools for it either.

zelphirkalt2• 6 years ago

Excel probably excels at promoting file formats like xlsx and such. I would not be surprised, if many mistakes, including choice of xlsx when saving, came from that, instead of storing data in csv, with the most simple csv syntax. So in that regard, I think excel is sometimes just bad.

collyw6• 6 years ago

The main problem I have seen is that data in Excel is essentially not validated.

Sometimes you get an number, sometimes you get a string containing a number. Sometimes the string contains white space that you don't want. And then there are dates, which can have similar problems multiplied ten times.

janekm• 6 years ago

Collecting and distributing the results of life-or-death diagnostic tests is specifically one case where you don't want end users to "mess around with" the data though... (making sure the right test results get associated with the right record is surprisingly difficult and rigid software is part of that).

higeorge132• 6 years ago

Excel is not bad, the users and institutions which use it for any use case are to blame.

The only correct usages of excel (and google sheets) are for user input to other processes, and visualization of data (which should be definitely stored in raw format in some database). And always assuming frequent backups of all sheets and of course manageable datasets. Anything else that includes external processes/scripts which append/ovewrite data in sheets is terrible practice and very error-prone.

Turukawa• 6 years ago

I've been teaching data curation for open data managers in governments around the world for the last decade. All the issues identified in this BBC article are covered in my syllabus.

If anyone's interested, here's the openly-licenced syllabus, in English and French: https://github.com/whythawk/data-wrangling-and-validation

aminumuhammad• 6 years ago

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

smsali97• 6 years ago

In these cases shouldnt Microsoft Excel raise a warning that data loss may occur if youre using the legacy file format?

WouterSpaak• 6 years ago

I believe Office will warn you when saving as .xls (or .doc instead of .docx, or .ppt instead of .pptx for that matter) that some data loss may occur. Problem is that nobody reads those warnings and just hits the OK button.

qmmmur• 6 years ago

Absolute clowns running the show.

ogig• 6 years ago

While I agree with the general sentiment that excel is normally misused, like in this case, it's also capable of handling huge amounts of data thanks to powerquery and powerpivot (1,999,999,997 rows per table, 2 billion tables max, docs say). You can't load the data in the grid, you need to use the data model. Using powerquery properly also solves most problems about excel guessing types wrongly when importing csv and other external data. If strongly recommend any excel user to learn powerquery and powerpivot.

Side-Step• 6 years ago

English get what they buy with cheap IT slaves working on work visas.

bmlzootown• 6 years ago

I'm a bit of a novice when it comes to storing vast amounts of data, especially of this nature, but why would you use Excel rather than an actual database?

DavidPeiffer• 6 years ago

Development time and data accessibility. Everyone you might need to send data to has Excel, fewer have Access, and far fewer have a typical database software.

That's not saying Excel is the best way to store data, but it gets a lot of jobs done without multiple month delays that come with an IT project. Often times, waiting 2 weeks because they're already busy then spending another 2 weeks outlining requirements is categorically unacceptable to accomplish the business goals.

wedesoft• 6 years ago

Worked at an optics company and they were spoiling lenses until someone discovered that Excel was rounding values to two digits after the comma (financial rounding).

mjbeswick• 6 years ago

Why would you even use excel for something so important, rather than importing the data in a database? The problem isn't the version of Excel, but using a Spreadsheet in the first place!

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

Related Stories