28 votes

The badly thought-out use of Microsoft's Excel software was the reason nearly 16,000 coronavirus cases went unreported in England

18 comments

  1. [12]
    Omnicrola
    Link
    My inner database developer weeps any time someone uses Excel for anything over 100k rows. I'm of the personal opinion that any beurocratic process involving Excel is fragile not because of the...

    My inner database developer weeps any time someone uses Excel for anything over 100k rows. I'm of the personal opinion that any beurocratic process involving Excel is fragile not because of the tool per se, but because it was probably created by one person who quit the company years ago, but knew just enough to Google some macros. And now everyone uses that spreadsheet but nobody actually understands it, and can't/won't adapt it as their business needs change.

    So many businesses are held together by these "magic" spreadsheets, it's terrifying.

    11 votes
    1. [2]
      0d_billie
      Link Parent
      My firm uses so many spreadsheets for all manner of stuff, and it's such a creaky and sluggish system. The answer to everything always seems to be "let's make a spreadsheet!" Sometimes it's...

      My firm uses so many spreadsheets for all manner of stuff, and it's such a creaky and sluggish system. The answer to everything always seems to be "let's make a spreadsheet!"
      Sometimes it's valuable, but there's other times (such as the software matrix that we're in the process of writing up, or the list of new joiners to the firm) where other tools would suit the use so much better. I've tried showing my bosses tools we could use in lieu of these monolithic spreadsheets, but they don't want to switch away from the tried and tested visual nightmares that we have.

      2 votes
      1. unknown user
        Link Parent
        Spreadsheets are the ultimate generalist tool to solve problems. And they are genuinely useful in a lot of cases! Unfortunately, rote memorisation of Excel functionality along with its ubiquity...

        Spreadsheets are the ultimate generalist tool to solve problems. And they are genuinely useful in a lot of cases!

        Unfortunately, rote memorisation of Excel functionality along with its ubiquity and accessibility to not just developers but also white collar workers, an inability to apportion upfront cost to designing or migrating to new, more specialised systems that enforce the appropriate amount of correctness, and general organisational inadequacy and slow-as-molasses governance leads to Excel being used to support any number of truly disastrous scenarios that any person outside said organisation would immediately find suspect and likely conclude they're all lunatics.

        4 votes
    2. [9]
      arp242
      Link Parent
      Isn't Excel basically a database? I mean, it's got rows, columns, types, you can query data out of it, etc. It seems to me that a spreadsheet is essentially a type of database. One of the...

      Isn't Excel basically a database? I mean, it's got rows, columns, types, you can query data out of it, etc. It seems to me that a spreadsheet is essentially a type of database.

      One of the advantages of Excel is that many non-programmers are familiar with it, and that it's comparatively easily human-readable as well as easily shareable and machine-readable. Properties a "real" database like SQL doesn't really have, not out of the box anyway.

      I don't exactly know the requirements and circumstances here, but it seems to me that Excel may at least be an acceptable tool for the job; although probably not the best one. There was a real time-pressure and the people probably had to deal with different systems from different organisations/departments that had to interact with each other.

      The mistakes here seem 1) using the old xsl format instead of xslx, 2) not having sufficient error reporting/robustness in the software to ensure a write is successful, and 3) not keeping the original CSV files as a backup (which always strikes me as a good idea, regardless of what kind of database/processing you use; always keep original data when possible!) None of these issues seem specifically tied to Excel as such, except perhaps the first one (although you can make similar mistakes in SQL which causes data to be lost).

      The "that one fella that quit 7 years ago and now no one really understands it"-problem seems to be a general one, and not really unique to Excel spreadsheets.

      1 vote
      1. [6]
        Omnicrola
        Link Parent
        Oh I agree, Excel is extremely useful, I've used it for many things myself. And you're correct, it is a flavor of database. However it's only one tool in a toolbox. But if all you're familiar with...

        Oh I agree, Excel is extremely useful, I've used it for many things myself. And you're correct, it is a flavor of database.

        However it's only one tool in a toolbox. But if all you're familiar with is a hammer....

        My point is less about Excel itself and more about it's mis-use being a symptom of some deeper dysfunction. It often belies a fragile set of processes or labrythian bureaucracy which all work just barely "good enough" and have difficulty handling mistakes. And certainly do not have the ability to handle a disruptive change.

        5 votes
        1. [5]
          arp242
          Link Parent
          I find it really hard to pass judgement in these kind of situations since I'm not familiar with the "facts on the ground", so to speak. Programming government software seems exceptionally hard...

          I find it really hard to pass judgement in these kind of situations since I'm not familiar with the "facts on the ground", so to speak.

          Programming government software seems exceptionally hard since you're forever working with shifting laws, and since everyone is expected to be interoperable with everyone else that new act intended for department A may also affect the systems in department B. Never mind there are real hard deadlines: a law will be enacted on such-and-such date, and you really need to have the software ready by then. The Dutch tax service for example is really struggling to keep up with all the new laws; I doubt this is good for the quality.

          With something like COVID you really need a system today; not in a few weeks or next week, but we need to track all of this right now. An Excel spreadsheet sounds like a kludgy hacky stop-gap solution, but ... well, it does work, and is easy and fast to build. And if a system works, then why replace it with a better system (especially if there are probably loads of other things that need to be done, too?)

          Is this a culture of "good enough"? Maybe. But the pressures and challenges seem very real to me, and I'm not entirely sure if I could do better.

          2 votes
          1. mat
            Link Parent
            I find it quite easy to pass judgement in this particular situation because absolutely nobody who thinks Excel is a valid software solution for a national-scale database should be anywhere near a...

            I find it quite easy to pass judgement in this particular situation because absolutely nobody who thinks Excel is a valid software solution for a national-scale database should be anywhere near a national-scale project costing millions of pounds.

            There are lots of restrictions on government IT work although not as much as you might imagine (I wish there was the degree of interoperability you're suggesting!) but none of that changes the fact that I'd be amazed if a work experience kid thought Excel was a good choice of data storage for this job. I once had to explain to a work experience kid that flat text file XML wasn't the ideal option for a much smaller dataset although he wasn't entirely wrong for suggesting it. That spotty teenager wouldn't have made this blunder.

            5 votes
          2. [3]
            Greg
            Link Parent
            It doesn't work, though, and that's the problem - it dropped thousands of records from a critical system in real use. In retrospect, yes there were possible fixes, checks, and workarounds that...

            An Excel spreadsheet sounds like a kludgy hacky stop-gap solution, but ... well, it does work, and is easy and fast to build.

            It doesn't work, though, and that's the problem - it dropped thousands of records from a critical system in real use. In retrospect, yes there were possible fixes, checks, and workarounds that would have made it viable to keep using a spreadsheet, but someone would have needed to think of them in advance.

            Using the right tool isn't just about whether it's theoretically possible, it's about whether it's safe and sensible to do so. This wouldn't have happened if they'd used a proper database, and more generally if they'd had the staff and process in place to point that fact out loudly, clearly, and repeatedly.

            It isn't "good enough". It's an incredibly expensive potentially life-critical system, built over a course of several months, that failed in production. I have very little appetite to give them the benefit of the doubt on this one.

            3 votes
            1. [2]
              arp242
              Link Parent
              That was just a comparatively minor mistake; switch to xlsx or use a different design and it's fixed. You can make the same mistake in SQL by making your primary key too small of an integer for...

              That was just a comparatively minor mistake; switch to xlsx or use a different design and it's fixed. You can make the same mistake in SQL by making your primary key too small of an integer for example, or make any number of similar design mistakes.

              It isn't "good enough". It's an incredibly expensive potentially life-critical system, built over a course of several months, that failed in production. I have very little appetite to give them the benefit of the doubt on this one.

              All the more reason to suspect there might be a complex set of circumstances that makes it not so easy. I mean, the alternative is that they're all a bunch of drooling idiots. Not an impossible scenario, and it's entirely plausible (even likely) that there have been some failures of judgement, but things are rarely as simple as "they're a bunch of idiots".

              3 votes
              1. Greg
                Link Parent
                It was a comparatively minor mistake made because they were using the wrong tool for the job. It's possible to chop an onion with a hammer and chisel, but you'll get a better result with a far...

                It was a comparatively minor mistake made because they were using the wrong tool for the job. It's possible to chop an onion with a hammer and chisel, but you'll get a better result with a far lower chance of injuring yourself if you just use a knife.

                As for the complex circumstances, I do broadly agree. I think there probably are a few idiots in the mix, plus some smart, dedicated people whose warnings were ignored, plus a whole lot of vested interests, plus a staggering array of legacy systems and bureaucracy. And you know what? I don't care. They spent £12m of public money to navigate those problems. They have no excuse for failure, yet they fail time and again.

                6 votes
      2. whbboyd
        Link Parent
        NO, Excel is not a database, basically or otherwise! If we accept that Excel has moved past Lotus 1-2-3's origins as a glorified tabular calculator (which it has, partly), it is still in no way...

        NO, Excel is not a database, basically or otherwise!

        If we accept that Excel has moved past Lotus 1-2-3's origins as a glorified tabular calculator (which it has, partly), it is still in no way suitable for storing or processing nontrivial amounts of data. There is nothing resembling a schema, essentially zero tools for ensuring data validity, and both the format and application utterly fall on their face when faced with more than a few hundred thousand rows.

        There is no credible way this issue could have arisen in a system backed by an actual database (even one from 1987).

        Now, Excel is certainly useful; it lets business people create horrifying ad-hoc abominations without having to bother any software developers (which, as a developer, I'm always grateful for). It has no business in any designed or automated software system.

        5 votes
      3. hamstergeddon
        Link Parent
        Admittedly I only used it for a college course years ago, but it seems like Access would be a better fit for this stuff than Excel. It has a lot (all?) of the benefits of a proper database with...

        Admittedly I only used it for a college course years ago, but it seems like Access would be a better fit for this stuff than Excel. It has a lot (all?) of the benefits of a proper database with the usability and UI of Excel. There's a learning curve, but nowhere near as steep as learning SQL.

        1 vote
  2. [4]
    Greg
    Link
    This is disappointing but not especially surprising. NHS software is an ongoing shitshow and while this is a particularly egregious bug, it's par for the course. They managed to spend just under...

    This is disappointing but not especially surprising. NHS software is an ongoing shitshow and while this is a particularly egregious bug, it's par for the course. They managed to spend just under £12m on this system in less than three months, which is one of those numbers that just gets handwaved away in the context of government spending; it's worth stopping to think just what could be built for that money, especially in the software world.

    9 votes
    1. [3]
      arp242
      (edited )
      Link Parent
      How do you spend £12 million on something like this in just 3 months? This is not a rhetorical question, but a serious one. I'm genuinely interested how that £12 million gets spent. My napkin...

      How do you spend £12 million on something like this in just 3 months? This is not a rhetorical question, but a serious one. I'm genuinely interested how that £12 million gets spent.

      My napkin calculation leads to at least half the amount: let's say you hire 20 people at £100k/year then you've got £2 million. Let's double that for the contractors mark-up and we've got £4 million and finally let's add £1 million for the time that needs to be spent on the government's side. These are very generous measures: £100k is a high salary, you probably don't need 20 people, 100% mark-up is very high, this is for an entire year and not 3 months, etc.

      I just don't see how you can spend £12 million, so maybe I'm missing something? Note that the total projected costs were expected to top £35 million.

      I found a bunch of FOI requests related to this, without much detail. The source for the £11.8 million figure seems to be this brief statement.

      I found another story which says that "£8m was focused on integrating the Google-Apple exposure notification functionality" and "£6m was focused on development that “would have been necessary regardless of which version” the government chose to use", but that only further confuses me.

      I don't really like to operate on the assumption "oh, they're all being stupid", so perhaps I'm missing something here? But I really have trouble explaining these kind of figures. Maybe we should have some school kids do it; I call app Britain.

      7 votes
      1. Greg
        Link Parent
        I feel exactly the same way. A friend of mine pointed out you could have a team of 50 people, each on a salary of £500,000/year (higher than 99.8% of British incomes), and if you pro-rate that to...

        I feel exactly the same way. A friend of mine pointed out you could have a team of 50 people, each on a salary of £500,000/year (higher than 99.8% of British incomes), and if you pro-rate that to 3 months you've still only spent half of your £12m budget. When you break it down, it really is beyond comprehension.

        Once you get into their larger failures, the thoughts of what could actually have been done with £10,000,000,000 at the height of austerity are just too big to envisage, at least for me. I've speculated previously on how it happens - the best guess I've got is the kind of genteel, ingrained corruption that's so endemic it doesn't even really get thought of as such.

        8 votes
      2. parsley
        Link Parent
        You buy your software from deloitte, accenture and/or any other big deal consultancies.

        How do you spend £12 million on something like this in just 3 months? This is not a rhetorical question, but a serious one. I'm genuinely interested how that £12 million gets spent.

        You buy your software from deloitte, accenture and/or any other big deal consultancies.

        2 votes
  3. HoolaBoola
    Link
    shudders Do not let people who don't know anything about software decide how to do implement it.

    shudders

    Do not let people who don't know anything about software decide how to do implement it.

    4 votes
  4. tomf
    Link
    At an old job, the admins kept a spreadsheet that had a list of every IronMountain box (20k or so) that had the UPC in one column followed by the team and a description of the contents. One of...

    At an old job, the admins kept a spreadsheet that had a list of every IronMountain box (20k or so) that had the UPC in one column followed by the team and a description of the contents. One of their admins at some point sorted only the UPC column, but nothing else and promptly saved over the backup.

    We had tape backups going back about two months, but even the oldest one was messed up.

    It's crazy that basic Excel isn't taught in every office -- at least to those who use it as part of their job. More importantly, it should be taught from the get-go that, like @whbboyd said, spreadsheets are not databases.

    Almost every job I've gone into, I've tried to move my team over to databases, but there's so much resistance (even within Change Management teams.)

    More than any other piece of software, I'm willing to wager that bad Excel has cost the economy the most... but I am also willing to accept that my view on this is skewed by experience.

    3 votes