86 ms·

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

• Mvandenbergh 6y 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.
  • mytailorisrich 6y 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 https://www.youtube.com/watch?v=K_FrQnQv0Vw (probably an accurate depiction of what's going on in Whitehall right now, though)
    • rgallagher27 6y 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
      • iso1631 6y ago
        I'd rather have David Brent, but we are where we are
  • londons_explore 6y 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 6y 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...
      • Closi 6y ago
        It depends which bits of functionality you want, but I think you are close to describing PowerQuery (Get & Transform) which is in Excel, which can ingest multiple data sources into effectively a relational database with hard datatypes and data-cleaning. In PowerQuery & PowerPivot there are also no practical limits on the number of rows of data you can have (other than the impact on processing time, clearly hundreds of millions of records might start to be a problem). It's not quite access - it doesn't actually persist/store the data, just aggregates it from other sources which is probably what is required here. Access is a little legacy and would have it's own issues. I think in reality it depends on how you are ingesting data (which is probably hospitals submitting excel templates or similar?). Maybe these could be automatically aggregated and put onto an analytics platform? (e.g. PowerBi). Hospitals could maybe report it on a portal, but I'm not convinced that the leadership would have wanted to change the established process (because of operational focus, change reduction & risk).
      • thu2111 6y ago
        For a very long time, it did. Weaknesses of Access in no particular order: very poor multi-user/multi-device-access features. No real concept of servers. Buggy, had a nasty habit of corrupting its custom file format causing data loss (Excel never does this). Does expect you to understand SQL, data normalisation, etc. VBA just about serviceable for a spreadsheet but not good enough for a 'real' database-backed app. Probably someone could try and make a better Access. There are surely lots of startups doing that already, along with hosting. All the "no code" tools that are out there, etc. Access had the advantage of coming with Office so lots of people had it already, whereas today's subscription based services that host your data have incremental cost.
        • 6y ago
  • DanBC 6y ago
    I agree, but I'm genuinely glad they're not[1] using fax machines. [1] they almost certainly are using fax somewhere.
    • brigandish 6y 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/ https://stdio.sangwhan.com/wtf-japan-covid-19-report/ [2] The previous HN discussion https://news.ycombinator.com/item?id=22728674 https://news.ycombinator.com/item?id=22728674
      • poooogles 6y ago
        I get a cert warning on https://stdio.sangwhan.com/wtf-japan-covid-19-report/ https://stdio.sangwhan.com/wtf-japan-covid-19-report/ which really is a good start.
  • avian 6y 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 6y ago
      https://twitter.com/MaxCRoser/status/1313046638915706880 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.
      • jiggawatts 6y ago
        Columns!? You're meant to add additional records as rows! (Excel supports only 16,384 columns, but 1,048,576 rows.) Actually, this was a pet peeve of mine: Especially in the early days of COVID, just about every official or unofficial data source was an absolute shitshow: - Transposed data (new entries in columns) - Pretty printed dates (instead of ISO 8601 or Excel format, or... anything even vaguely parseable by computer). - US date formats mixed in with non-US date formats. - Each day in a separate file, often with as little as 100 bytes per file. Thousands upon thousands of files. - Random comments or annotations in numeric fields (preventing graphing the data). We don't all need to be data scientists, or machine learning wizards, or quantum computing gods. But come on. This is the most basic, data entry clerk level Excel 101 stuff. Trivial, basic stuff. Put rows in rows. Don't mix random notes into columns you might want to sum or graph. Don't mix units. Have headers. Use a sane date format. That is all. </rant>
        • deleted 6y ago
          [deleted]
        • cblconfederate 6y ago
          lets assume that local centers people do save them in rows, which is the most logical thing to do. But probably the software where they copy/paste them requires the data in columns, so maybe they transposed the data without realizing that it was truncated to max size. Or else it wouldn't explain why it took so many days to realize that they couldn't add more columns.
  • chimprich 6y 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 6y 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.
      • hdjdbtbgwjsn 6y ago
        Yeah we should give up on controlling polio, measles etc as well. After all polio only kills 0.5% of cases as well. </sarcasm>
    • Mvandenbergh 6y 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.
      • fuzzy2 6y ago
        > Distributionally, most of the cases were 1-2 days before the error was spotted. That’s always true though if the virus is spreading exponentially (dunno whether it currently does in the UK).
        • 6y ago
  • lini 6y 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 6y 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.
    • hdjdbtbgwjsn 6y ago
      Cleaning data is a major part of data engineering there are plenty of professionals capable of helping with this task.
  • learnstats2 6y 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.
  • makomk 6y 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 6y 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 6y 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 https://www.bbc.co.uk/news/health-54387057
  • azalemeth 6y 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-trading-loss-2013-2 https://www.businessinsider.com/excel-partly-to-blame-for-tr...
  • gridlockd 6y ago
    > Using an Excel sheet as a database? You wouldn't (want to) believe how incredibly common that is.
• OJFord 6y 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.
  • pjc50 6y ago
    Care to explain why you think the absolute figure is useless?
    • OJFord 6y ago
      What are you going to use it for? Mostly it seems to be used for comparing differently populous countries.
    • iso1631 6y 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.
• pjc50 6y 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 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."
  • mytailorisrich 6y 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 6y 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-why-a-disgraced-talktalk-ceo-is-leading-the-governments-testing-programme/ar-BB13MK2U https://www.msn.com/en-gb/money/other/people-want-to-know-wh...
      • 6y ago
• social_quotient 6y 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?
  • iso1631 6y 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.
    • makomk 6y 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 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-trace-england-and-coronavirus-testing-uk-statistics-27-august-to-2-september-2020 https://www.gov.uk/government/publications/nhs-test-and-trac...
• daanlo 6y 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_Adonis 6y 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 6y ago
    CSV seems like a good choice for this kind of tabular, linear data.
    • mark-r 6y 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.
      • whimsicalism 6y ago
        CSV != Excel. I work CSVs regularly and can't recall the last time I opened up Excel (intentionally)
        • 6y ago
• underdeserver 6y 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.
  • CamperBob2 6y 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.
    • ImaCake 6y 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.
      • rchaud 6y ago
        Trignometric calculus comprised at least half my high school calc assignments ~20 years ago. Surely that can be scaled back in favour of more lessons on statistics and probability.
      • CamperBob2 6y ago
        I mean, statistics relies heavily on calculus No, it doesn't. Not the kind you'd teach in high school to help young people grow up and maintain a functioning democracy.
    • RhysU 6y 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...
    • 6y ago
• maverickJ 6y 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-decisions-based 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 6y 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 6y ago
  That happens if you only test ill people: https://www.worldometers.info/coronavirus/country/spain/ https://www.worldometers.info/coronavirus/country/spain/
• mhandley 6y 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 http://danger.handley.org.uk/misc/rates-uk-recent.png Raw data: http://danger.handley.org.uk/misc/rates-uk.png 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.
  • pbhjpbhj 6y 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 6y ago
  640k deaths ought to be enough for any country.
• jimmcslim 6y 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 https://github.com/HTBox/allReady [2] https://neo4j.com/ https://neo4j.com/
• makomk 6y 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 6y ago
  I wouldn’t blame Excel. The cause was gross incompetence.
• arnon 6y ago
  For most companies, "big data" is when they can't fit the data in a single Excel sheet anymore.
  • cashewchoo 6y ago
    Devops Borat: "big data is any data which is crash excel"
  • tda 6y 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.
• Slashbot 6y ago
  faggot data collection of bullshit
• BuyMyBitcoins 6y ago
  I love how Excel has become a de facto database - even though it absolutely shouldn’t be.
  • shadowgovt 6y 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.
    • gpmcadam 6y 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 6y 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 http://www.eusprig.org/horror-stories.htm
  • dang 6y ago
    Discussed here: https://news.ycombinator.com/item?id=22431500 https://news.ycombinator.com/item?id=22431500. (Submitted many times, but that's the only real thread, and only from 7 months ago.)
  • whatatita 6y ago
    The site's cert is invalid. Got a mirror?
    • iso8859-1 6y ago
      Certificates are not applicable, the link scheme is HTTP.
      • not_kurt_godel 6y ago
        It presents a self-signed certificate if using a browser/extension that automatically redirects to HTTPS (which everyone should be).
        • corty 6y ago
          If the extension redirects to a broken cert not intended for public use, the extension is broken and should not be used. This is not making things safer, this is training users to click through warnings again. Very much "don't".
          • lukeramsden 6y ago
            The extension is not at fault, all it does is rewrite HTTP links to HTTPS (which should be the default IMO, I agree with the parent commenter). The fault is with the site providing a self-signed certificate when accessed over HTTPS.
• beagle3 6y 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.
  • callamdelaney 6y 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!'
    • pintxo 6y 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.
      • sails 6y ago
        Would be interested in hearing more about that Excel tool.
        • akho 6y ago
          Come work at a bank. Any bank.
          • strangeattractr 6y ago
            Can attest to this. Some of the things I've seen excel spreadsheets contorted to do horrify me. I once got spreadsheet dumped on me to debug because it wasn't working. A colleague used the spreadsheet to 'generate' interest rates that were then input into a mainframe. Dug into the VBA spaghetti mess, turns out this 15 year old script that took 15 minutes to run originally hit a bunch of Oracle/External APIs and performed calculations was now just copying the rates in from a csv file on a shared drive. The error was caused by an excel formula ticking over a new year causing it to look for a directory it did not need to access that didn't exist. I thought it was pretty funny until I heard that the entire asset finance business had been unable to write any loans for days because of this.
• stoobs 6y 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.
  • gpmcadam 6y 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?
    • stoobs 6y 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.
• bitlevel 6y 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 6y ago
    Excel may not be a database solution, but it is the most commonly used database solution.
  • TwoBit 6y 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.
    • bitlevel 6y 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.
      • deleted 6y ago
        [deleted]
      • benhurmarcel 6y ago
        I work in an office that relies way too much on Excel. To consider your solution, first showstopper, it needs a server. We don't have a server, nor anyone who knows how to manage one. We'd need to ask IT, that will take months and they'll require a budget transfer, so we'd need to request it to management (which will need a business case to convince) and involve the finance guys. We can't just plug a RaspberryPi into the wall, not only that would get me fired, but also it wouldn't be able to connect to anything without the company's certificates for the proxy or whatever. Second, we need people who can code in PHP (and their backups when they leave). Probably in practice we'd need IT to do that, so that's more months and budget required. Obviously anything stored in the cloud is out of the question, just the authorizations and contracts to do that would take a year. So in the end it ends up as a shared spreadsheet.
• deleted 6y ago
  [deleted]
• mellosouls 6y 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=24685911 https://news.ycombinator.com/item?id=24685950 https://news.ycombinator.com/item?id=24685950
  • dang 6y 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&dateRange=all&page=0&prefix=true&sort=byDate&type=comment https://hn.algolia.com/?query=follow-up%20by%3Adang&dateRang... https://hn.algolia.com/?query=%22significant%20new%20information%22%20by%3Adang&dateRange=all&page=0&prefix=false&sort=byDate&type=comment https://hn.algolia.com/?query=%22significant%20new%20informa...
    • mellosouls 6y 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.
    • makomk 6y 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 https://www.bbc.co.uk/news/technology-54423988 Maybe it'd make more sense to link that one, if you're keeping the title?
      • dang 6y ago
        Ok, changed to that from https://www.bbc.com/news/uk-54422505 https://www.bbc.com/news/uk-54422505. Thanks!
• deleted 6y ago
  [deleted]
• BooneJS 6y 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,
• isolli 6y 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-depression.html https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel...
• karmakaze 6y 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.
  • codeulike 6y ago
    Further down, maybe the article has been updated 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.
• deleted 6y ago
  [deleted]
• cma 6y 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 6y ago
    You seem to assume the government used a modern version of Excel
• walrus01 6y ago
  If I had a dollar for every office worker that thinks Excel is a database...
• codeulike 6y 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. 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. 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 https://www.bbc.co.uk/news/technology-54423988
  • dang 6y ago
    OK, we'll change to that from https://www.bbc.com/news/uk-54422505 https://www.bbc.com/news/uk-54422505. Thanks! More explanation here: https://news.ycombinator.com/item?id=24690286 https://news.ycombinator.com/item?id=24690286
  • philjackson 6y 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).
• bencollier49 6y ago
  Good lord. Old XLS files. I'd love to have seen how this was concocted.
  • codeulike 6y 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
    • bencollier49 6y 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 6y 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)...
      • brnt 6y ago
        Worked on a world class radiotherapy piece of software. If you like 20 year old Delphi projects and pre C89 ... C, then come over! Xls export is probably a recent addition, xlsx support will most likely come no sooner than when Excel stops supporting xls.
      • labster 6y ago
        Ten years ago people were still complaining about OOXML not being an open standard. Don't worry guys, I'm sure they'll make a reference implementation of the standard soon! I'm sure there were a few people waiting around for a "real" standard to come out before implementing XLSX support.
• aphrax 6y 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..
  • shadowgovt 6y 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.
    • TeMPOraL 6y ago
      I'm betting the exporter isn't running out of file, but out of loop counter.
      • shadowgovt 6y ago
        I mean, same difference. "I cannot generate a file representing state of data in RAM" is a 'fail noisy' error, not a 'fail silent' error.
        • TeMPOraL 6y ago
          (EDIT: numerous comments point out that Excel displays an explicit warning when truncating data above 256x65k. This comment was written under the assumption that it doesn't.) Not if it was a bug in the code. 65k sounds suspiciously close to the limit of 16 bit unsigned int. I'm guessing the exporter looked roughly like: int exportRow( ... ) { if( /* can't export or no more rows */ ) { return 0; } /* export row */ return ++someInternalCounter; } void export( ... ) { unsigned short nextRow = 0; do { nextRow = exportRow(...); } while(nextRow > 0); } In the above example, the export would silently stop after 65k entries. The way people write C in the wild, this wouldn't surprise me in the slightest. And with Microsoft being all about backwards compatibility, Excel probably defers to some ancient and long forgotten code when exporting to XLS.
  • NelsonMinar 6y ago
• mark-r 6y ago
  Friends don't let friends use Excel. Unfortunately Excel is the great swiss army knife of software and it's hard to avoid.
  • TwoBit 6y ago
    And what do they use instead of Excel? LibreOffice? Some expensive custom database software?
    • shadowgovt 6y 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.
      • TeMPOraL 6y ago
        My belief is because Access sits on the wrong side of the gap you mention. It's an SQL DB with an UI, alright, but the database is (or at least has a reputation of being) kind of flakey, and the UI exposes you to all the complexity and inflexibility of relational databases. This creates two types of hurdle. One is educational - you have to teach people basics of relational data modelling if you want Access to make sense for them. The other is operational - you can't shuffle data or table structure around in Access like you could in Excel. You can't just copy pieces of table around, you can't make rows depend on rows before them, etc. Worth remembering is that Excel is, fundamentally, a 2D functional reactive programming REPL. Most of its users don't understand that, but they internalize the behavior. They may not know what a DAG is, but they know that updating cells will recalculate cells that depended on them. So you can't replace Excel with just a database - because half of the utility of the program is in formulas. There is a gap - a tool is missing that would offer the flexibility, the ergonomics, and FRP capabilities of Excel, while also providing tools for ensuring data consistency and relational queries, and at the same time also making them easy for users to wield. It's a tall order.
      • WoodenChair 6y ago
• ivanstame 6y ago
  My mind is blown how can you do any real change in the world without good data? We need exact classification of the data of dead people. If they had COVID, was it the main source of death, real cause of death, was it diagnosed(tested) or not...ect. Until then, this stuff about COVID is total bullshit and you are being deceived and you are being to scared. SNAP OUT OF IT!
  • pfortuny 6y ago
    Compare to the flu... So many deaths of people with the flu are NOT flu deaths...
  • acallaghan 6y ago
    You're talking about US death certificates, I assume. It's the norm to list more than one thing that lead to death: e.g. Heart failure - Lung failure - Trauma - Automobile crash So for covid-19 Respiratory failure - diabetes - COVID-19 You're saying that Respiratory failure or diabetes is the 'real cause of death', but COVID-19 is actually the cause of death. This has been common practice for years, stop believing what Mc. Nixon tweets out.
• langitbiru 6y 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 https://sqlite.org/limits.html
  • weswpg 6y 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?s=20 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 6y ago
      about 26^3 columns, or 17576
  • 013a 6y ago
    XLSX's limit is 2^20, or just over 1.04 million (as the article says).
  • bigbubba 6y 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 6y 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).
• StreamBright 6y 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 6y 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 6y ago
    XLS does in fact have a 16 bit row counter :/
    • emerged 6y ago
      That should be enough bits to do everything a computer will ever need to do.
  • dan-robertson 6y 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 6y 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 6y 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 6y 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.
      • redwall_hp 6y ago
        That's an interesting ethical point in CS: sure, you didn't intend the software you hacked together in an afternoon to be used in anything with life-and-death consequences. But that doesn't mean someone isn't going to come along later and use it, without ever even looking at the source, for something very critical down the line. I wonder how many bleeding edge master branches of GitHub repos, pulled in blindly by someone cobbling something together to meet a deadline, are running in places they probably shouldn't be.
        • ClumsyPilot 6y ago
          People have died from using unsuitable contruction materials or devices, that were never designed to be used that way. It's not the fault of the original designer if he was clearly targetting a different purpose.
• jononor 6y 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 6y 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 6y 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.
    • deleted 6y ago
      [deleted]
  • 1_player 6y 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.
    • 6y ago
• curiousllama 6y 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.
• hinkley 6y 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 6y 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.
    • hinkley 6y 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.
• threatripper 6y 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 6y 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 6y 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.
    • threatripper 6y 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.
  • deleted 6y ago
    [deleted]
• diarrhea 6y 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 6y 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.
    • sjansen 6y 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.
      • jamil7 6y ago
        Nothing seems as easy as another engineer’s problem. This also amazes me, especially the whining about other people's code from developers. Truely believing that they would do it better. I've even seen inherited code posted to be ridiculed/shamed/bashed in some slacks and subreddits.
  • deleted 6y ago
    [deleted]
  • vhold 6y 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.
• Pick-A-Hill2019 6y 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 6y 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 6y 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 6y ago
  Probably excel parsing everything as a date LOL
• smsm42 6y 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 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.
  • hermitcrab 6y ago
    Definitely recommend 'Humble Pi'. Funny as well as informative.
• iainmerrick 6y 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 6y 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 6y 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.
  • NelsonMinar 6y 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 6y ago
      Yes, you get a warning that explicitly says "Data beyond 256 (IV) columns by 65,536 rows will not be saved."
  • piqufoh 6y 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).
• lordnacho 6y 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 6y 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...
  • TeMPOraL 6y 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.
• adolph 6y 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-python-and-pandas https://training.talkpython.fm/courses/move-from-excel-to-py...
  • hermitcrab 6y 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 https://www.easydatatransform.com .
• slykar 6y ago
  "But you wouldn't use XLS. Nobody would start with that." Oh. "Every" sales and business person probably would. Probably.
  • 1-more 6y ago
    Well they'd at least start with XLSX unless they've got a very old copy of Office
• jjuhl 6y ago
  Excel? Really? They are using that crap for nation wide reporting? Wow, mind blown. Epic fail.
  • BSG-Actual 6y 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.
• jl6 6y 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.
  • codeulike 6y 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.
    • bobbylarrybobby 6y ago
      Off by one errors are all alike; every excel error is an error in its own way.
      • codeulike 6y ago
        Not when it mixes up US and UK date formats when you import a CSV. Not when it turns phone numbers into integers and strips off the leading zero.
        • tonyedgecombe 6y ago
          This is the root of the problem isn't it? The wrong tool for the job.
      • jjgreen 6y ago
        Tolstoy keeps on giving ...
  • 6y ago
• nabla9 6y 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 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.
  • nickff 6y 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.
    • sheetjs 6y 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.conservatives.com/News/Speeches/2010/02/George_Osborne_Mais_Lecture_-_A_New_Economic_Model.aspx https://web.archive.org/web/20100414205630/http://www.conser...
      • nickff 6y ago
        People often cite papers which support their positions, but correlation does not necessarily imply causation, and the paper is rarely decisive.
        • mcv 6y ago
          Not the paper on its own, maybe, but it bolsters other arguments. If someone already wants austerity, an authoritative paper saying that's better will make them want austerity more, and gives them a strong argument in discussions with opponents, and may sway people who are unsure.
• dredmorbius 6y 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.hawaii.edu/SSR/Mypapers/whatknow.htm https://web.archive.org/web/20070617041554/panko.shidler.haw...
• chasd00 6y 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.
• deleted 6y ago
  [deleted]
• Aeolun 6y ago
  > "But you wouldn't use XLS. Nobody would start with that." Haha. This would be funny if it wasn’t so abjectly false.
• rbanffy 6y ago
  How much did the UK government pay for this?
• punnerud 6y 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&nv=1&pto=aue&rurl=translate.google.com&sl=no&sp=nmt4&tl=en&u=https://www.tu.no/artikler/oljemyndighetenes-excel-feil-ble-ikke-oppdaget-stortinget-apnet-barentshavet-sorost-med-regnefeil-pa-over-100-milliarder/405367&usg=ALkJrhggTdtBDU6vy0P4a632I-xPosf2zw https://translate.googleusercontent.com/translate_c?depth=1&...
• robomartin 6y 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 6y 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
• masterproc 6y 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). :)
• masterproc 6y ago
  Just saying in Apple Numbers you can work with tables that contain up to 1,000,000 rows and 1,000 columns. :)
• outworlder 6y 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 https://www.youtube.com/watch?v=0nbkaYsR94c
• rossdavidh 6y 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...
  • ComodoHacker 6y 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 6y 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
    • yawaramin 6y 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.
  • kelnos 6y 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 6y 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.
• mshook 6y 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 6y 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 6y 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.
• ekianjo 6y 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 6y ago
  We need to stop accepting data in Excel. Full stop. One day it’s going to kill someone.
• swrobel 6y ago
  Reminds me of my days working in fiance. I was basically a broken record that just repeated: Excel is not a database
  • tsjq 6y ago
    typo :)
• totaldude87 6y 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-of-all-time/ https://floatapp.com/us/blog/5-greatest-spreadsheet-errors-o...
  • tsjq 6y 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-rename-microsoft-excel-misreading-dates https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
    • computerfriend 6y ago
      Also the post-2008 financial crisis austerity programs based on Excel analysis. https://mathbabe.org/2013/04/17/global-move-to-austerity-based-on-mistake-in-excel/ https://mathbabe.org/2013/04/17/global-move-to-austerity-bas...
      • rjtavares 6y ago
        Austerity wasn't based on that study, and it didn't go away once that error was found.
        • 6y ago
• galkk 6y ago
  I read an article and I feel that this is a deflection of a guilt much more than Excel issue.
• jp0d 6y 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 6y 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.
    • jp0d 6y 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 6y ago
    See, they had far less than 65,536 employees, so Excel was fine for their workflow!
    • jp0d 6y ago
      lol.. These days it can support up to a million employees! :D
• known 6y ago
  I use AWK to do floating point calculations and copy the results to Excel https://news.ycombinator.com/item?id=14018450 https://news.ycombinator.com/item?id=14018450
• known 6y 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
• jugg1es 6y 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 6y 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.
    • yawaramin 6y ago
      Quoting doesn't work. Try opening this CSV with Excel: a,b "01",01 Excel interprets both as the same number–1.
      • sheetjs 6y ago
        The correct way to generate a CSV cell with a leading 0 is ="01" You can verify this with 01,"01",="01"
        • rsanders 6y ago
          If CSV were being used just to exchange data with Excel, we probably wouldn't be using CSV. Many systems neither need nor know that ="01" should be treated as the string "01". If Excel were the only intended consumer, .xlsx would be a preferable file format. At least it's mostly unambiguous.
• aaron695 6y 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 6y 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 6y 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 6y 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 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 6y 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 https://karma-engineering.com/lab/blog/Solved
• 8bitsrule 6y 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 6y 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.
    • 8bitsrule 6y 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.
• thetechimist 6y ago
  This is absolutely no different than someone running out of space on a hard drive and then calling it a “storage glitch” because the talent-less drones who work in govt get to avoid accountability. It’s not Excel’s fault in any possible way. It’s the creator of the spreadsheet. Nothing wrong with using Excel for this kind of task but you must be aware of its limitations and plan accordingly (which would be painfully easy to do by putting smaller data sets in separate workbooks and summarizing from a larger one). One might say that before you touch a computer or any data or software, you must be aware of the very concept of limitations. People who aren’t shouldn’t be touching such things. These are the same people who fill up their Gmail and can’t believe they should actually PAY for more storage. Send them back to FarmVille.
• alexbrower 6y 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.
  • alexbrower 6y 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...
• pjmlp 6y 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 6y 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 6y 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".
  • corty 6y 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.
• Turukawa 6y 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 https://github.com/whythawk/data-wrangling-and-validation
• aminumuhammad 6y ago
  > Missing Covid-19 test data was caused by the ill-thought-out use of Excel Buhari today monninig
• smsali97 6y ago
  In these cases shouldnt Microsoft Excel raise a warning that data loss may occur if youre using the legacy file format?
  • WouterSpaak 6y 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 6y ago
  Absolute clowns running the show.
• ogig 6y 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 6y ago
  English get what they buy with cheap IT slaves working on work visas.
• deleted 6y ago
  [deleted]
• bmlzootown 6y 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 6y 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 6y 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 6y 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!