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.
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
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.
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).
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.
LibreOffice Base is something of an attempt at a newer Access, but unfortunately it's far from ready for production use.
Base has very solid data storage foundations (including the ability to connect to real database servers) but the UI is very buggy and is painful to fight with. Note I said 'fight with' rather than 'use.'
Base seems to have a fair bit of potential, however, if someone where to pour some money and/or time into developing it further.
> People use excel sheets as databases all the time because it doesn't require any specialist knowledge to build or use.
Test and Trace is a multi billion pound programme of work, involving huge companies (eg, Serco) interfacing with national governmental bodies (Public Health England).
We've had months to get something in place. One of the reasons organisations like Serco is used is their apparent expertise with IT.
The main reason Serco is used is their political connections; they really ought to have been banned from consideration after the fraud scandal: https://www.bbc.co.uk/news/uk-50806919
(my one-step programme for improving UK public contracting would be that after a significant failed contract the contracting company and its directors and any other companies they are directors of would be banned from tendering for a period; maybe a couple of years?)
Unfortunately the procurement rules currently in place prevent procurement personnel evaluating a tender from taking into account any prior performance. I'm unsure whether this is a result of EU legislation and common market rules, or whether it is just the UK.
italy has legislation to forbid people who engaged in corruption/embezzlement from working with public administrations.
It also has a required certification for companies that work with the public that they weren't condemned for certain crimes (trafficking in illegal waste, extorsion, organized crime etc).
So, it doesn't seem an EU/Single Market rule that you can't check this.
One issue they have is that very few companies are willing to have the government as a customer. That's one reason why they keep going back to the same failed firms time and time again.
Reasons vary but the ones I've seen are generally:
1. Complex procurement regulations that take a lot of effort to comply with by contractors.
2. Low willingness to pay (both price-wise and time-wise).
3. Civil servants are nightmare customers who don't know what they want and are under no pressure to figure it out, so there's a lot of timewasting involved.
4. PR nightmare if/when things go wrong even if it's not your fault, as government is relatively open compared to other types of customers so easier to find out about problems, and lots of people automatically blame the private sector in preference to blaming government employees due to (misguided) assumptions of moral superiority of the public sector.
Sure. That's why I said it wouldn't bother me if this was in the first few weeks of the pandemic or for one-off ad-hoc analysis. I actually use Excel for that all the time, including for tasks where technically a db is the correct solution. Working is better than perfect and everyone knows (sort of) how to use Excel. To still be using it in production this many months in is not acceptable. Someone should have taken that Excel based workflow and set it up to run through a database, they had the time to do it.
This is a very good point and a shame to see it being downvoted. I have been wishing there was an accessible database-ish software for doing my accounts, as the current mega-spreadsheet I use is pretty hairy and none of the specialised apps quite fit the bill. The closest thing seems to be Airtable, which while nice, is quite expensive.
Recently I was visiting hospital quite a bit (ante-natal) and witnessing the horror of the massive NHS form filling software the nurses and midwives have to use. It's clunky and sprawling but one of the main sins I think is it's lack of adaptability. In the old days of pen an paper, a new form could be written, or adapted, and photocopied easily, and on-site. Now if a new field needs to be added or a process is changed it needs to go (I imagine) to some centralised IT development office and fed into a ticketing system where it might get changed in a few weeks or months. There's no room to quickly adapt at a ground-level, so you end up with things like this excel sheet problem.
There is also the fact that Excel has some rich editing tools. I have actually seen a system where most editing was done by exporting the relevant items to a spreadsheet, making your changes and reimporting.
Don't forget the formulas! Excel is essentially a functional reactive programming REPL. No RDBMS I know lets you easily define a table in which cells in a row depend on values from previous cells in a row, and keep this as an invariant.
People working on Excel replacements need to remember about this aspect of spreadsheets too.
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".
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.
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.
Oh, I saw excel files that contained screen shots of other excel files over cells containing data. Comments where in text fields, if you were lucky, or again screen shots, from word.
And then the same people wondered why things based on this file didn't work.
They seem to have hit the row limit, not the column limit. They were using Excel to collate the data coming in from different test centres: when one of the CSVs was loaded in, the row limit was exceeded and Excel cut off the extra rows without the operator realising (I'm going off reporting by Alex Hearn at The Guardian here, and he's normally pretty good)
> 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.
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.
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.
This is more true than it would be from pure exponential growth alone, especially the relatively slow exponential growth the UK as a whole currently seems to be seeing (doubling time maybe about a fortnight?) The issue was effectively dropping all results after a certain number from a particular location, so at the start relatively few results were dropped and it was spotted around the time that number started to become substantial. It's that hard cutoff where results suddenly start to be dropped that means most of them are in the last few days.
Sadly it takes about 24 to get tested and 24 hours for the lab result. That gives very little time to trace contacts before they start entering the asymptomatic-but-infectious-to-others stage. A one day delay is a problem.
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.
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.
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.)
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?
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.
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.
My dad (who is 79 but still working) proudly mentioned that he spent 11 hours putting together some charts in Excel for his client. Probably something that could be done in an hour or so with an online tool.
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.
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.
Actually not only that. It affected contact tracing, and people exposed to infected patients were not informed to self isolate. Possibly leading to extra spreading of the virus.
Professionalism has been entirely absent from the top-level handling of the pandemic. It's not entirely clear from the article who's responsible for this, but I note that the so-called "NHS" test and trace is in fact an outsourced organisation: https://en.wikipedia.org/wiki/NHS_Test_and_Trace run by Tory placewoman Dido Harding.
> "She is a former chief executive of the TalkTalk Group where she faced calls for her to resign after a cyber attack revealed the details of 4 million customers. A member of the Conservative Party, Harding is married to Conservative Party Member of Parliament John Penrose and is a friend of former Prime Minister David Cameron. Harding was appointed as a Member of the House of Lords by Cameron in 2014. She holds a board position at the Jockey Club, which is responsible for several major horse-racing events including the Cheltenham Festival. "
> "In May 2020, Harding was appointed by Health Secretary Matt Hancock to head NHS Test and Trace, established to track and help prevent the spread of COVID-19 in England. In August 2020, after it was announced Public Health England was to be abolished, Harding was appointed interim chair of the new National Institute for Health Protection, an appointment that was criticised by health experts as she did not have a background in healthcare."
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".
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.
I mean let's call a cat a cat: this is cronyism thus corruption. Unfortunately, voters never care about these immoral practices. When a social system/administration is dysfunctional, corruption is often the first cause of these disfunctions. The result of corruption is always sheer incompetence.
I think the voters do care, but can't keep track of the details and only know what the media choose to focus on. This is why Donald Trump's "drain the swamp" pitch was so effective, despite the reality of the far worse corruption in his campaign.
Voters want less corruption, but their own tribalism leads them to unhelpful positions of "everyone is corrupt", "none of my tribe are corrupt", and sometimes both of those at once.
While that is true, I have a little difficulty imagining that Harding or anyone else senior was involved in the process of shoveling around spreadsheets. As the overall leadership she obviously has the accountability but just saying, "oh what do you expect when you put a crony in?" ignores the reality that a number of people on an operational level had been doing this for months and nobody seems to have stopped it.
The latest news is that apparently this was a problem with Public Health England's internal processes anyway, not NHS Test and Trace or any other kind of outsourced service: https://www.bbc.co.uk/news/uk-54422505 I don't think that even falls under Harding's responsibilties.
Day one on the job of CEO should be requesting input about systemic organizational and operational problems. Both from your direct reports and from the underlings who actually know what is broken.
I have not yet watched this programme from the BBC but it claims the NHS Test and Trace system is a chaotic mess. In other words, as shambolic as everything our Conservative government has done over the pandemic.
Panorama - Test and Track Exposed:
> "Panorama hears from whistleblowers working inside the government’s new coronavirus tracking system. They are so concerned about NHS Test and Trace that they are speaking out to reveal chaos, technical problems, confusion, wasted resources and a system that does not appear to them to be working. The programme also hears from local public health teams who say they have largely been ignored by the government in favour of the private companies hired to run the new centralised tracking system. As Panorama investigates, it has left some local authorities questioning whether local lockdowns could have been handled better or avoided altogether."
The whole NHS Test and Trace organisation seems very strange. Going by recent news reports, it's not just Harding who comes from a retail background, several of the other top people do, too. What they don't seem to have done is hire top people with a technical or management background in either public health or technology, which you'd think might be slightly important for an organisation with this remit.
In other news, I'm looking forward to hearing about how a system apparently hacked together from text files and decades-old Excel formats is complying with basic data protection principles, given that it's being used to process sensitive personal data and has profound implications for both public health and now (since violating an instruction from their people to self-isolate has just been made a criminal offence) individual liberty for huge numbers of people.
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.
The trouble is, that entire narrative about the testing capacity being a lie because of secret leaked figures about the number of people tested was itself a massive lie.
For context, the Government publishes a stat for the number of people tested for the first time ever each week (used to be daily, but that was dropped in part due to people misusing it). Certain publications and politicians - starting I think with the Times - pushed the narrative that this was the real number of people being tested, and that the reason it was so far below the stated capacity was because the real capacity was far lower than the Government claims: https://archive.is/n3Yku This was bullshit on multiple levels - there are several important uses of testing, like routine screening of NHS frontline staff and hospital patients being admitted and discharged, that result in people being retested who've been tested at some point previously, and those obviously use actual testing capacity on people who haven't been tested that day or week. (Testing two samples from the same person in short succession, on the other hand, is unusual and not general policy.) Not only that, the people tested number is from pillar 1 and 2 testing in England only for the week ending the 2nd of September, whereas the capacity figure seems to be for all pillars in the entire UK at around the 12th or 13th. It makes absolutely no sense to compare them. And the cherry on the top is that I'm pretty sure this number is from before the problems with test shortages and testing delays that they're implying it explains.
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.
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...
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.
Doesn't Excel by default capture the .csv extension so that it gets called automatically when you try to open the file?
Since Excel is one of the few standard pieces of software that knows how to open CSV, it gets used a lot of times when it shouldn't. There's another post I made comparing Excel to a swiss army knife, and there's a reason for that.
I think our company has a company-wide license for Office. When IT sets up a machine you get it automatically. Microsoft works hard to get those kind of setups to be common-place.
Last time I worked in a big corporate you had to fill in a long form to request a license for anything you needed. If you didn't use it again within a fortnight or so it got yoinked away...
The startups I've worked at since have all been big on GSuite.
Sure it does but it doesn't give a f*k - once you open it in excel, it does its' own formatting and will save that incorrect formatting even if you save the CSV as a CSV
Excel removes leading zeros from numeric fields no matter what you do and you cannot turn it off. This is a huge problem in healthcare where many patient identifiers have leading zeros
> exchange formats are text based, with labs saying they will upgrade to „modern xml formats“
Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in place to capture file write errors, or more generally the use of non-reproducible, manual operating practices which seems common in data processing.
I can see the theoretical purity of this statement, but based on my experience working with CSV files generated by actual non-technical users I have to disagree here.
There are a number of footguns here that are really subtle and the average non-technical user has no hope of spotting them.
Problems that I've seen in the wild, off the top of my head:
* Windows vs. Linux line terminators breaks some CSV libraries.
* Encoding can change depending on what program emitted the CSV file, and auto-detecting encoding is not perfect. For example, Excel for Mac uses Linux encoding by default, IIRC.
* Excel does wacky things when you export a "CSV" in the wrong format; real users use Excel to generate their CSVs, not Python. For example if you import the string "0123456789" in an Excel sheet, it infers "number" and strips the leading "0" when you export. Now your bank account/routing numbers are invalid!
* "What's a TSV?" -- if users use CSV, how do you handle commas in the data? It's nontrivial to train users to do their CSV upload as a TSV.
Etc.
In practice we needed to build a fairly beefy helpdesk article with accumulated wisdom on how to not break your CSV exports, and most users don't read/remember these steps until they experience the trauma first-hand.
I'd say the CSV format is deceptively simple -- it's quite easy to do the right thing as a developer where the source and sink are both code you control, but in the wild it gets messy really quickly.
Almost none [0] of the problem, such as line terminators, encodings, or escape characters, is an inherent flaw in the idea of using a text-based file to represent data. They can be fixed by just a little bit of standardization. But unfortunately, I don't think it's possible, we're stuck at here.
[0] Except type conversion, which is a real problem.
Agreed; I think the crux of the problem is that CSV is not a format that supports metadata. And even if you somehow created such a convention/standard, it's impossible to upgrade all the existing users with 10+ year-old Excel installs; that ship has already sailed.
You have to navigate the same basic issues to convert each csv as it comes in to xls as PHE did in the article. They could just as well have converted them to a consistent csv format and stopped there.
The first CSV file was created in 1983. The first CSV standard was created in 2005[1].
The two decades of CSV surviving as an informal standard means that it takes minutes to make a 95% complete CSV parser and an infinite amount of time to make a 99.99% complete CSV parser.
Sorry, but if you run into OOM issues by parsing an XML file, you're using the wrong API.
The DOM for a large XML document will of course take tons of space in memory. The key to parsing XML files quickly and with low memory consumption is to only keep in memory what's necessary, by streaming over the elements.
Correct. And to add to this: apparently the lost data was due to the data that exceeded the 16k rows XLS supports, so the amount of data per file was apparently not huge to begin with. So even a shitty XML parser should do just fine here.
There's nothing wrong with CSV, especially if you don't have arbitrary-text data (if you do, just don't do CSV). Excel though adds an addition layer of services, which turns it into a nightmare if used as something it explicitly wasn't made to be - a database.
There is nothing wrong with CSV or text base formats. You can even use AWS Athena to query CSV feels stored in S3. It's a good format for data import/export, that many systems can natively understand or have tools to parse, given it's known how to interpret data.
It was a data pipeline issue. Software has little to do with it. If they received data in json and tried to interpret it as CSV, the same could have happened. I believe Excel even warns when you open file that has too many rows.
Tools exist, for analysts and engineers (MS Access comes to mind for the analyst, python for the engineer), that would rectify the problem. And I think it's a fair assumption to say that those tools would be readily available.
Kinda sounds like a management issue, as well. No one ever said "hey you know XLS doesn't support all of this data"?
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.
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.
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.
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...
When I did my A-level in maths the course was half pure and half applied maths.
The pure maths course was mandatory, but for the applied side you had the choice of either mechanics or statistics. At the time I was much more interested in physics, and was considering it as a degree course but, even if I'd done that, statistics would have been far more useful to me.
I certainly wouldn't choose to drop geometry or calculus, but I've had many occasions to regret my choice of mechanics over statistics during the past two decades.
Then that is nuts, and you don't need to remove calculus to fix it.
Presumably there's something you're teaching that we (in the UK) aren't, or more depth somewhere, but I don't know what it is. Our systems are quite different in that mathematics becomes optional after GCSEs (15-16yo) here, but statistics is taught from a far younger age than that, to that, and beyond for those that take A level(s) in mathematics. (As I recall there are six statistics A level modules total, S1-6, I think S1-2 are compulsory for a full A2 (vs. AS) mathematics qualification (which consists of six modules total). In order to do all six statistics modules one would at least take the second A level 'further mathematics', and probably (pun intended; unless statistics was a particular passion and the school allowed it) 'further additional'.
NB I quite liked that structure - there are 18 'modules' total (arranged in 'core', 'further pure', 'decision' (algorithms), 'statistics', and 'mechanics'. Three A levels total available (six modules each) or fewer and an AS (three). Which ones you want to do are almost entirely up to you if the school's big/lenient enough. IIRC you could even decide for yourself how to allocate the modules' grades across the number of A levels you were eligible for, e.g. if AC would be more beneficial to you than BB.
Imho, exponential growth especially, and then clustering.
Some pretty animations in a lesson might be helpful.
Fire grows in a similar way.
I've seen people up in arms because of things like a workplace of 1000 people shut down because "only" 20 people tested positive one day.
What's the problem?
To people who don't understand exponential growth it seems like 1 in 50 people is a fuss about nothing.
To people who do understand, it's an early-warning signal which says 500 people will get it in weeks due to the confined workspace, if not stopped urgently. And they will infect 1000s in the surrounding community in the same timeframe.
(To anyone tempted to point out the published R isn't that high, local R is highly dependent on situation and who mixes with whom. In a confined workspace, especially with people moving around, it's higher than in the general population. This is why it's useful to teach clustering in addition to exponential growth.)
The problem the world has right now is not a lack of understanding of what exponential growth means. Plenty of people understand that just fine. It's so easy to understand that there is even a simple ancient parable about it (of the Chinese Emperor and the chess board).
The problem is people who are obsessed with the concept of exponential growth even though "grows exponentially until everyone is infected" is not a real thing that happens with viruses, even though COVID-19 no more shows exponential growth than the sine wave does (sin roughly doubles at points), even though Farrs Law is all about how microbial diseases show S-curve type growth.
This leads to crazyness like the UK's chief medical officers going on TV and presenting a graph in which the last few data points are in decline, but with sudden endless exponential growth projected into the future, along with claims that "this isn't a prediction, but clearly, we have to take extreme action now because of exponential growth".
Observing exponential growth for a few days in a row does NOT mean endless growth until the whole world is infected. Growth rates can themselves change over time, and do. That's the thing people don't seem to understand.
This government had a statistics screw-up just two months ago and the only negative reaction was about how they handled the fallout. As a nation we simply do not understand its value - or the cost of it being misused.
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?
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.
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:
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.
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.
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...
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.
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.
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.
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".
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.
All it does is assume that a https site serves the same content and audience as the corresponding http site. That is a broken assumption. The consequences of such a broken assumption are very much the fault of the extension.
Why is that a broken assumption? Can you name a legitimate reason for HTTP and HTTPS sites to serve separate contents and audiences? I would rather not connect over HTTP to _anything_ nowadays.
CMS, serve the content over http and the admin page over https.
And for sites with noncritical static content https is superfluous to dangerous. ESNI isn't implemented yet, IP addresses are still visible to the eyes. And content sizes and timing are a dead giveaway for the things you are looking at. HTTPS for everything is just a simulation of privacy at best, and misleading and dangerous at worst, because there IS NO PRIVACY in the aforementioned cases.
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.
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!'
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.
It started very innocent: Some consulting company apparently was tasked to create a prototype of the credit rating algorithm. Quite appropriately, I would say, they did so in Excel. As you can easily tweak the logic, show input, intermediate results and output. And all can be manipulated by the client who does not need to know a thing about programming to do so. Great.
From there it went downhill: Apparently someone up the hierarchy thought "Wow, that's 80% of what we need, let's just add a little UI and ship it"
They added a UI, but not as a VBA UI as you might hope. No, they did the whole UI in different worksheets. Storing any intermediate data ... also on worksheets. Long story short, it was a mess, and a slow one for that.
Oh, did I say this was a multi-lingual application?
Production rollout was on Jan 2nd, Dec 31st around 4pm I found a bug in the other language, on the one machine which had the this language configured. I believe this was the only time I ever saw a programmer literally run down the office to that machine to debug this issue.
I once created a very modern mobile offline-capable web app for a large enterprise company that was powered by APIs backed by excel. Luckily we didn't have to deal with those directly, but it was a bit of a pain for the poor guy doing th backend.
Fun fact: Toshiba has software that generates entire CRUD app skeletons driven 100% by excel spreadsheets. You fill out object definitions in excel spreadsheets with property names and data types etc. It's like an extremely uncool long lost cousin of rails -g
Source: Got one of their engineers to show me after I heard about it and had to know if it was real.
Anyone who's ever worked with or in Japan knows just how far they are willing to torture Excel spreadsheets to get them to do anything and everything.
to play devil's advocate, i had a desktop support job in college and worked with an executive assistance. She was _very_ attractive and so i was at her desk "working on the printer" a lot, she knew Excel better than anyone i've ever seen (before or since). It was like watching a magician.
I've (miss)used excel as a web-service testing platform once. The test data (input/expected output) was anyway in the Excel sheet, so why not. We did not have other software available at the time, so Excel+VBA it is.
It was one of my nicest testing gigs ever. A test session would take only minutes, all results documented to the t. And fiddling around with the test data was so easy. Would be interesting to know if this thing is still in use.
Not sure the customer liked it that much, I regression tested the 6 previous - still running - versions of the service, something no one had cared to do for years. We found bugs both in the Spec and in the Code for nearly all old versions...
>I've (miss)used excel as a web-service testing platform once. The test data (input/expected output) was anyway in the Excel sheet, so why not. We did not have other software available at the time, so Excel+VBA it is.
That's an upgrade on how Toshiba did testing. They did basically the same thing except it was Excel+Humans. No joke. Never saw that one with my own eyes but a coworker did. He also said they had doctors there and each doctor was responsible for X number of staff and every so often made them fill out a questionnaire of which the final question was "have you thought about killing yourself lately?" and if you answered yes you got a day off. Apparently people would ask for transfers to different offices where the doctor responsible for them wasn't physically present, so they could avoid even being asked the question for fear of being made to take time off work. One of the more senior guys on the project kinda just disappeared too. Fun times.
Who? Almost anyone who wanted to get something done without going through the red tape of working with IT. Even in a private corporation that red tape is enough to make many a things end up in excel (or for the more tech savvy, access). Government is surely even worse (better?) at having red tape so the pressure was likely stronger.
Yes exactly. An endless battle: IT want to stamp out spreadsheets and centralise everything but it all becomes an ossified bottleneck of development projects. Meanwhile people need to get stuff done so they use Excel.
CSV is somewhat okay-ish. Excel, not so much (thanks Excel for discarding the important leading zeros in this field, and for lossily parsing the other as a date... oh, this Excel file is so big it got the "XLSB" treatment which is hard to undo)
I think the healthcare industry is probably the only thing keeping fax machines alive. Interestingly, fax machine are tagged as HIPPA compliant. Lots of spreadsheets get printed and then fed to the fax machine for transmission instead of email/whatever. On the other side, and i'm being serious, someone gets the fax and creates a new spreadsheet and enters all the rows manually. fun fun.
"We need to store some data for a virus"
"Where does the data come from?"
"Every hospital sends us an excel sheet"
"Well, let's merge all of it into a bigger sheet and display it or export it"
"We should maybe start tracking some of this"
"Where does the data come from?"
"Dunno, a handful of hospitals... maybe?"
"Right. Let's just use Excel, not gonna waste time on that now"
>"We need to store some data for a virus" "Where does the data come from?" "Every hospital sends us an excel sheet" "Well, let's merge all of it into a bigger sheet and display it or export it"
In my org excel might have been the start, the intent being to move over to a DB once someone got it stood up. Then nobody would stand up the DB - because not urgent, we have the excel for now. The DB would be completely forgotten, and 3 years down the road everybody would be wondering wtf we have this huge excel file for. Then we would have six weeks of meetings to discuss the file until something else comes up and we forget it for another 3 years.
That's why Excel wins. Because it's right there, infinitely adaptable to any change in workflow you may need to make. No need to send official requests through half a dozen layers of management to get the IT people to maybe, in half a year, add a column to the database. No need to involve 20 people for a year to create new software for a new workflow. You just agree with your co-workers on a change, swap a column, add some color, File->Save, done. It wins because it puts power in the hands of the people doing the work.
I mean, yeah, but pretend Excel is, idk, sword arms. You get a lot of work done by whirling around the office, spinning your swords, cutting off a variety of limbs and heads in the process. You get to the end, meet the few survivors, staring dismayed at the trail of blood and dismemberment behind you, and you yell, "BUT I GOT THE JOB DONE!"
You're not wrong, but...
I wish there were some alternative where we instead fixed our ossified, byzantine processes gradually over time, so that we didn't need to break out the sword arm tornado in the name of getting work done and then, in hindsight, say, "well, shame about the negative externalities, but there really just was no other way. Ah well, let's move on, everyone who isn't a pile of flesh and blood and bits on the floor tidy up. Gotta get everything clean for the next sword arm tornado."
But the negative externalities don't seem all that big, considering. For every problem like this, you get countless millions of person-hours and dollars saved in work that wouldn't have happened at all, or would have been delayed, because of the necessity and cost of creating complex IT systems to support it.
If Excel is a sword tornado, it's one happening in an environment where everyone knows to be super vigilant about sharp objects. The alternative then would be a central processing factory that takes several years and millions of dollars to built, and which has to be turned off for a month several times a year, to change the shape of the blades used by the automated cutters.
> For every problem like this, you get countless millions of person-hours and dollars saved in work that wouldn't have happened at all,
I've found errors in every Excel spreadsheet I've ever looked at, and I'm not some master-excel user; I usually find them because - if I care about the results, I rewrite them as a Python script, so I get to go through everything.
The fact that Excel is effectively not auditable is a huge problem. There are a lot of dollars saved, like you said, but also many wasted or embezzled without anyone noticing in time because past a very low bar of complexity, it's really impossible to figure out what's happening without much, much work.
> If Excel is a sword tornado, it's one happening in an environment where everyone knows to be super vigilant about sharp objects.
... rather, no one is really careful, everyone gets a small cut every now and then, to which the apply a bandage and continue like nothing happened. And occasionally, they lose an eye or a limb or a head - and it is only those cases you read about in the newspapers.
I do not have a better suggestion, I'm afraid, but Excel is causing damage everywhere - e.g. [0], the subtitle - which I'm afraid is not an exaggeration, is "Sometimes it’s easier to rewrite genetics than update Excel"
I agree with what you wrote. It does cause a lot of small mess everywhere, and an occasional big mess. But there really isn't any better suite of tools available that hits all the non-database requirements.
The common case for stories like this is the answer is "nobody."
Time point 1: "We need a way to map the long-form epidemiology state. It's 2D data... Let's use Excel." "Will we hit limits?" "Pfft, no. Not unless we end up needing to track tens of thousands of patients, and that's not practical; we don't have enough people to do that tracking in the whole NHS."
Time point 2: "This pandemic is an URGENT problem. We need to hire more people than ever before to do tracing. And be sure to keep the epidemeology state mapper updated so it can drive the summary dashboards!"
I think Linux and MacOS viruses get a special callout because they're exceptions. Similar reporting conventions are used all the time, like a terrorist attack in the middle east vs a terrorist attack in France. It's not some nefarious PR scheme, the story is just more interesting because of how unusual it is.
If you read the article (perhaps its been updated?), they were using the old XLS format and they hit the limit at 64,000 lines
PHE had set up an automatic process to pull this data together into Excel templates so that it could then be uploaded to a central system and made available to the NHS Test and Trace team as well as other government computer dashboards.
The problem is that the PHE developers picked an old file format to do this - known as XLS.
As a consequence, each template could handle only about 65,000 rows of data rather than the one million-plus rows that Excel is actually capable of.
If somebody finds it easier to do this process using Excel than with a scripting language, I wouldn't expect them to know the differences between file formats. I would be surprised if they understood the concepts behind file formats.
They may have chosen XLS because they used Excel back when that was default, and now they don't want to "risk" anything by switching horses mid-stream.
If the conversion was failing silently, it's a Microsoft software problem, but I bet you money the conversion was failing in a way that fell into a `catch(Exception e) { }` somewhere in their conversion script. Or even a `catch(Exception e) { log e; }`, and the log is stored to local storage on a machine that no human being ever logs into.
There's nothing to convert, Excel natively supports CSV. It just doesn't read beyond a million lines, it presumably won't warn you about that, and if you save the file again (as CSV), the lines will just be gone.
> This is indeed user incompetence, but so far every news source quoted the official PR and said it was an "Excel Problem".
It's been quoted/reported as "an ill-thought-out use of Excel", when in reality it's "poor use of an older file format".
There's also a lesson to be had here about validating your data conversions. Especially for critical things like this, it's always a good idea to perform the conversion from the old file format to the new one, and then to go back and extract the data from the new one to compare to the source data.
This can also find issues like Excel cluelessly assuming a field is a date, PHP automatically converting a string which happens to only contain numbers into an integer, and so on.
From what I can tell, that sort of thing, assuming proper monitoring (which is a huge assumption), would have detected this immediately (plus however long until someone notices the error e-mail, etc.).
Yea, I have a personal vendetta against XLSX due to having had to make tooling replacements for business logic being encoded in spreadsheets in the past, but the title of this article is incredibly slanted.
This was absolutely a user error and the title "Excel: Why using Microsoft's tool caused Covid-19 results to be lost" is really disappointingly click-baity.
Is your view based on data available to everyone else? If so, that would be nice to see. Its far easier for you to convince others with data. Why would anyone want to change your view?
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?
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.
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.
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.
I'm sure, at a push, a PC could be co-opted to do the work while a server (even online) could then be brought up to speed and the data migrated - but that's your use case.
What we're talking about here is a government department who do have access to servers, but choose not to use them (or so it would seem).
If you're thinking regular office workers, they know of no alternative.
Excel works enough for small data, particularly when you don't do complex queries on it. The more you know of it, the better it works. The only other thing that offers similar benefits to Excel but works as a database is MS Access, but the mental model behind it is too complex for your average office worker who wasn't trained in it, and like most database systems, requires a lot of up-front work with figuring out the schema, and doesn't particularly like the schema being modified later on.
As far as I can tell, there's literally nothing else out there. No, random SaaS webapps du jour don't count, because they're universally slow, and also store the data in the cloud, instead of the local drive.
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:
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.
The BBC has expanded on the Excel-related part and broken it out into a seperate article: https://www.bbc.co.uk/news/technology-54423988 Maybe it'd make more sense to link that one, if you're keeping the title?
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,
833 comments
[ 4.7 ms ] story [ 393 ms ] threadUsing 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.
https://www.youtube.com/watch?v=K_FrQnQv0Vw (probably an accurate depiction of what's going on in Whitehall right now, though)
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.
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...
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).
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.
Base has very solid data storage foundations (including the ability to connect to real database servers) but the UI is very buggy and is painful to fight with. Note I said 'fight with' rather than 'use.'
Base seems to have a fair bit of potential, however, if someone where to pour some money and/or time into developing it further.
Test and Trace is a multi billion pound programme of work, involving huge companies (eg, Serco) interfacing with national governmental bodies (Public Health England).
We've had months to get something in place. One of the reasons organisations like Serco is used is their apparent expertise with IT.
(my one-step programme for improving UK public contracting would be that after a significant failed contract the contracting company and its directors and any other companies they are directors of would be banned from tendering for a period; maybe a couple of years?)
It also has a required certification for companies that work with the public that they weren't condemned for certain crimes (trafficking in illegal waste, extorsion, organized crime etc).
So, it doesn't seem an EU/Single Market rule that you can't check this.
Reasons vary but the ones I've seen are generally:
1. Complex procurement regulations that take a lot of effort to comply with by contractors.
2. Low willingness to pay (both price-wise and time-wise).
3. Civil servants are nightmare customers who don't know what they want and are under no pressure to figure it out, so there's a lot of timewasting involved.
4. PR nightmare if/when things go wrong even if it's not your fault, as government is relatively open compared to other types of customers so easier to find out about problems, and lots of people automatically blame the private sector in preference to blaming government employees due to (misguided) assumptions of moral superiority of the public sector.
Recently I was visiting hospital quite a bit (ante-natal) and witnessing the horror of the massive NHS form filling software the nurses and midwives have to use. It's clunky and sprawling but one of the main sins I think is it's lack of adaptability. In the old days of pen an paper, a new form could be written, or adapted, and photocopied easily, and on-site. Now if a new field needs to be added or a process is changed it needs to go (I imagine) to some centralised IT development office and fed into a ticketing system where it might get changed in a few weeks or months. There's no room to quickly adapt at a ground-level, so you end up with things like this excel sheet problem.
People working on Excel replacements need to remember about this aspect of spreadsheets too.
[1] they almost certainly are using fax somewhere.
[1] Japan's COVID-19 Reports - 140KBs of Unadulterated Incompetence https://stdio.sangwhan.com/wtf-japan-covid-19-report/
[2] The previous HN discussion https://news.ycombinator.com/item?id=22728674
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".
> The reason was apparently that the database is managed in Excel and the number of columns had reached the maximum.
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>
Or else it wouldn't explain why it took so many days to realize that they couldn't add more columns.
And then the same people wondered why things based on this file didn't work.
ie a csv is equivalent to Excel at the Daily Mail, so they printed Excel as the issue due to it being more familiar to their readership.
(Can't think of what the technical issue would be in the case of a csv, also doubt the credibility of the source!)
I still haven't seen an authoritative source, or one that predates that comment.
But it definitely sounds possible.
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.
That’s always true though if the virus is spreading exponentially (dunno whether it currently does in the UK).
Edit: oops, it seems they used columns for the cases and the ~16K column limit was hit when daily cases exceeded that.
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.
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.
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?
The article in question: https://www.bbc.co.uk/news/health-54387057
For me, I always feel like I'm living in some sort of parallel universe. Imagine living in a world where _you_, and a select group of others, know that there are better methods to drive a screw into a wall than punching it with your fist. However, 99% of people in the world punch screws into walls by hand, because they know how to use the only tool they have to hand (i.e. their hand). Their friends and neighbours do too. Their colleagues show them neat little tricks about how to fold your hand better to get the screw in with less pain, or more efficiently. The "proper" option -- drilling a pilot hole, using a self-tapping screw, understanding the difference between nails, bolts and screws, etc, or the concept of wall-plugs, is "too technical", "too complex", or requires "a large amount of infrastructure to fix a simple problem" [buying a drill & screwdriver, etc]. Sometimes people buy new walls with screws pre-installed because the thought of driving in those screws seems like an overwhelming technical burden.
You, and a group of similarly-minded nerds, understand just how stupid this is at times. You personally then spend the rest of your life watching people occasionally have horrific hand injuries from punching screws into walls and refusing the offer of screwdrivers. Others profit from selling vertical hard surfaces with a whole array of convenient screws pre-installed; or get employed teaching others the _very_ basics of screw-driving, or sell products containing all sorts of klunky hacks to cope with the fact that screws manually installed are at odd angles and tend to fall over randomly. The supplier of a popular brand of screwdrivers happens to be the world's largest screw manufacturer, but never seems to promote screwdrivers to people who currently punch them in with their fists. Instead, they sell them a subscription to screws, with the option for a very expensive pre-screwed wall-delivery service, built to your specification (that they call Dynamics for some reason…).
You, and a subset of your nerdy and educated friends recognise just how batshit insane the widespread use of punching-as-a-method-to-install-screws is, and occasionally laugh about it, mostly to distract yourself from the grim reality of buildings badly held together by punched-in-screws. Every so often a skyscraper-sized building collapses, [1] and it's realised in retrospect that the manually-driven-in-screws were not the right tool for the job. Still, people carry on banging in screws with their fists, and you increasingly realise that you have to -- somehow -- accept that in order to sleep at night without going insane.
[1] https://www.businessinsider.com/excel-partly-to-blame-for-tr...
You wouldn't (want to) believe how incredibly common that is.
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.
Mostly it seems to be used for comparing differently populous countries.
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.
> "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."
As the NY Times said yesterday, sometimes it feels like "Britain is operating without adult supervision".
https://www.msn.com/en-gb/money/other/people-want-to-know-wh...
Voters want less corruption, but their own tribalism leads them to unhelpful positions of "everyone is corrupt", "none of my tribe are corrupt", and sometimes both of those at once.
I'll point out that Matt Hancock is MP for Newmarket - a centre for horse racing and associated businesses.
Panorama - Test and Track Exposed:
> "Panorama hears from whistleblowers working inside the government’s new coronavirus tracking system. They are so concerned about NHS Test and Trace that they are speaking out to reveal chaos, technical problems, confusion, wasted resources and a system that does not appear to them to be working. The programme also hears from local public health teams who say they have largely been ignored by the government in favour of the private companies hired to run the new centralised tracking system. As Panorama investigates, it has left some local authorities questioning whether local lockdowns could have been handled better or avoided altogether."
https://www.bbc.co.uk/iplayer/episode/m000n1xp/panorama-test...
In other news, I'm looking forward to hearing about how a system apparently hacked together from text files and decades-old Excel formats is complying with basic data protection principles, given that it's being used to process sensitive personal data and has profound implications for both public health and now (since violating an instruction from their people to self-isolate has just been made a criminal offence) individual liberty for huge numbers of people.
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?
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.
For context, the Government publishes a stat for the number of people tested for the first time ever each week (used to be daily, but that was dropped in part due to people misusing it). Certain publications and politicians - starting I think with the Times - pushed the narrative that this was the real number of people being tested, and that the reason it was so far below the stated capacity was because the real capacity was far lower than the Government claims: https://archive.is/n3Yku This was bullshit on multiple levels - there are several important uses of testing, like routine screening of NHS frontline staff and hospital patients being admitted and discharged, that result in people being retested who've been tested at some point previously, and those obviously use actual testing capacity on people who haven't been tested that day or week. (Testing two samples from the same person in short succession, on the other hand, is unusual and not general policy.) Not only that, the people tested number is from pillar 1 and 2 testing in England only for the week ending the 2nd of September, whereas the capacity figure seems to be for all pillars in the entire UK at around the 12th or 13th. It makes absolutely no sense to compare them. And the cherry on the top is that I'm pretty sure this number is from before the problems with test shortages and testing delays that they're implying it explains.
The not-so-secret weekly report in question with the number of people tested for the first time is here: https://www.gov.uk/government/publications/nhs-test-and-trac...
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...
Since Excel is one of the few standard pieces of software that knows how to open CSV, it gets used a lot of times when it shouldn't. There's another post I made comparing Excel to a swiss army knife, and there's a reason for that.
The startups I've worked at since have all been big on GSuite.
I use SQLite as files a lot for this reason.
Worth noting that XML is also a text format. SGML even can treat CSVs as markup. There's nothing wrong with CSVs/TSVs anyway - it's a concise tabular format using only minimal special coding for a record and a field separator, as envisioned by ASCII and EDIFACT. The problem seems more like that there was no error checking in place to capture file write errors, or more generally the use of non-reproducible, manual operating practices which seems common in data processing.
Excel is used extensively in many industries. Any file could be cut off in processing by any number of reasons, one off errors for e.g.
So the solution is to "fix" the process by using the existing broken process and smaller files....
I can see the theoretical purity of this statement, but based on my experience working with CSV files generated by actual non-technical users I have to disagree here.
There are a number of footguns here that are really subtle and the average non-technical user has no hope of spotting them.
Problems that I've seen in the wild, off the top of my head:
* Windows vs. Linux line terminators breaks some CSV libraries.
* Encoding can change depending on what program emitted the CSV file, and auto-detecting encoding is not perfect. For example, Excel for Mac uses Linux encoding by default, IIRC.
* Excel does wacky things when you export a "CSV" in the wrong format; real users use Excel to generate their CSVs, not Python. For example if you import the string "0123456789" in an Excel sheet, it infers "number" and strips the leading "0" when you export. Now your bank account/routing numbers are invalid!
* "What's a TSV?" -- if users use CSV, how do you handle commas in the data? It's nontrivial to train users to do their CSV upload as a TSV.
Etc.
In practice we needed to build a fairly beefy helpdesk article with accumulated wisdom on how to not break your CSV exports, and most users don't read/remember these steps until they experience the trauma first-hand.
I'd say the CSV format is deceptively simple -- it's quite easy to do the right thing as a developer where the source and sink are both code you control, but in the wild it gets messy really quickly.
[0] Except type conversion, which is a real problem.
The first CSV file was created in 1983. The first CSV standard was created in 2005[1].
The two decades of CSV surviving as an informal standard means that it takes minutes to make a 95% complete CSV parser and an infinite amount of time to make a 99.99% complete CSV parser.
[1] https://en.wikipedia.org/wiki/Comma-separated_values#History
The DOM for a large XML document will of course take tons of space in memory. The key to parsing XML files quickly and with low memory consumption is to only keep in memory what's necessary, by streaming over the elements.
https://en.wikipedia.org/wiki/Simple_API_for_XML
It was a data pipeline issue. Software has little to do with it. If they received data in json and tried to interpret it as CSV, the same could have happened. I believe Excel even warns when you open file that has too many rows.
Tools exist, for analysts and engineers (MS Access comes to mind for the analyst, python for the engineer), that would rectify the problem. And I think it's a fair assumption to say that those tools would be readily available.
Kinda sounds like a management issue, as well. No one ever said "hey you know XLS doesn't support all of this data"?
What a mess.
One lesson I’d draw from that is to favor simple human-readable text formats like CSV, where they’re suitable for the job at hand.
No, it doesn't. Not the kind you'd teach in high school to help young people grow up and maintain a functioning democracy.
The pure maths course was mandatory, but for the applied side you had the choice of either mechanics or statistics. At the time I was much more interested in physics, and was considering it as a degree course but, even if I'd done that, statistics would have been far more useful to me.
I certainly wouldn't choose to drop geometry or calculus, but I've had many occasions to regret my choice of mechanics over statistics during the past two decades.
Remember that HN comments are an exercise in selection bias.
Presumably there's something you're teaching that we (in the UK) aren't, or more depth somewhere, but I don't know what it is. Our systems are quite different in that mathematics becomes optional after GCSEs (15-16yo) here, but statistics is taught from a far younger age than that, to that, and beyond for those that take A level(s) in mathematics. (As I recall there are six statistics A level modules total, S1-6, I think S1-2 are compulsory for a full A2 (vs. AS) mathematics qualification (which consists of six modules total). In order to do all six statistics modules one would at least take the second A level 'further mathematics', and probably (pun intended; unless statistics was a particular passion and the school allowed it) 'further additional'.
NB I quite liked that structure - there are 18 'modules' total (arranged in 'core', 'further pure', 'decision' (algorithms), 'statistics', and 'mechanics'. Three A levels total available (six modules each) or fewer and an AS (three). Which ones you want to do are almost entirely up to you if the school's big/lenient enough. IIRC you could even decide for yourself how to allocate the modules' grades across the number of A levels you were eligible for, e.g. if AC would be more beneficial to you than BB.
Some pretty animations in a lesson might be helpful.
Fire grows in a similar way.
I've seen people up in arms because of things like a workplace of 1000 people shut down because "only" 20 people tested positive one day.
What's the problem?
To people who don't understand exponential growth it seems like 1 in 50 people is a fuss about nothing.
To people who do understand, it's an early-warning signal which says 500 people will get it in weeks due to the confined workspace, if not stopped urgently. And they will infect 1000s in the surrounding community in the same timeframe.
(To anyone tempted to point out the published R isn't that high, local R is highly dependent on situation and who mixes with whom. In a confined workspace, especially with people moving around, it's higher than in the general population. This is why it's useful to teach clustering in addition to exponential growth.)
Oh god not this again.
The problem the world has right now is not a lack of understanding of what exponential growth means. Plenty of people understand that just fine. It's so easy to understand that there is even a simple ancient parable about it (of the Chinese Emperor and the chess board).
The problem is people who are obsessed with the concept of exponential growth even though "grows exponentially until everyone is infected" is not a real thing that happens with viruses, even though COVID-19 no more shows exponential growth than the sine wave does (sin roughly doubles at points), even though Farrs Law is all about how microbial diseases show S-curve type growth.
This leads to crazyness like the UK's chief medical officers going on TV and presenting a graph in which the last few data points are in decline, but with sudden endless exponential growth projected into the future, along with claims that "this isn't a prediction, but clearly, we have to take extreme action now because of exponential growth".
Observing exponential growth for a few days in a row does NOT mean endless growth until the whole world is infected. Growth rates can themselves change over time, and do. That's the thing people don't seem to understand.
This reminds me of the below event which you can read the full story at https://leveragethoughts.substack.com/p/making-investment-de...
In 1976, the UK government, led by James Callaghan of the Labour, borrowed the sum of $3.9 billion from the International Monetary Fund. The granting of the loan was based on the condition that government fiscal deficit be slashed as a percentage of GDP.
A couple of years later, the chancellor of the exchequer at the time of the IMF loan said this below.
‘If we had had the right figures, we would never have needed to go for the loan”
That’s right!! The decision to borrow money from the IMF was based on wrong data. The public borrowing figures which prompted the UK government to seek IMF loan in 1976 were subsequently revised downwards sometime in the future.
The problem was a government still operating as though Britton Woods was in place five years after Nixon ended it
7-day moving average: http://danger.handley.org.uk/misc/rates-uk-recent.png
Raw data: http://danger.handley.org.uk/misc/rates-uk.png
Looks like all regions were affected, but by far the largest corrections are in NW, NE and Yorkshire regions. In particular, NE had looked like cases were declining, but we can now see this was incorrect, and they're still increasing rapidly
Edit: note the most recent 3 days are always incomplete, so any decline shown there is not a real effect.
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.
Looking at the allReady repo, maybe it isn't a great example since it hasn't been touched in years...
[1] https://github.com/HTBox/allReady
[2] https://neo4j.com/
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.
This is why people's email inboxes double as their TODO lists.
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.
http://www.eusprig.org/horror-stories.htm
Why is that a broken assumption? Can you name a legitimate reason for HTTP and HTTPS sites to serve separate contents and audiences? I would rather not connect over HTTP to _anything_ nowadays.
And for sites with noncritical static content https is superfluous to dangerous. ESNI isn't implemented yet, IP addresses are still visible to the eyes. And content sizes and timing are a dead giveaway for the things you are looking at. HTTPS for everything is just a simulation of privacy at best, and misleading and dangerous at worst, because there IS NO PRIVACY in the aforementioned cases.
Wow. Thank you for this gem of human culture.
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.
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.
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.
From there it went downhill: Apparently someone up the hierarchy thought "Wow, that's 80% of what we need, let's just add a little UI and ship it"
They added a UI, but not as a VBA UI as you might hope. No, they did the whole UI in different worksheets. Storing any intermediate data ... also on worksheets. Long story short, it was a mess, and a slow one for that.
Oh, did I say this was a multi-lingual application?
Production rollout was on Jan 2nd, Dec 31st around 4pm I found a bug in the other language, on the one machine which had the this language configured. I believe this was the only time I ever saw a programmer literally run down the office to that machine to debug this issue.
Source: Got one of their engineers to show me after I heard about it and had to know if it was real.
Anyone who's ever worked with or in Japan knows just how far they are willing to torture Excel spreadsheets to get them to do anything and everything.
In the right hands Excel is pretty amazing.
It was one of my nicest testing gigs ever. A test session would take only minutes, all results documented to the t. And fiddling around with the test data was so easy. Would be interesting to know if this thing is still in use.
Not sure the customer liked it that much, I regression tested the 6 previous - still running - versions of the service, something no one had cared to do for years. We found bugs both in the Spec and in the Code for nearly all old versions...
That's an upgrade on how Toshiba did testing. They did basically the same thing except it was Excel+Humans. No joke. Never saw that one with my own eyes but a coworker did. He also said they had doctors there and each doctor was responsible for X number of staff and every so often made them fill out a questionnaire of which the final question was "have you thought about killing yourself lately?" and if you answered yes you got a day off. Apparently people would ask for transfers to different offices where the doctor responsible for them wasn't physically present, so they could avoid even being asked the question for fear of being made to take time off work. One of the more senior guys on the project kinda just disappeared too. Fun times.
"We need to store some data for a virus" "Where does the data come from?" "Every hospital sends us an excel sheet" "Well, let's merge all of it into a bigger sheet and display it or export it"
... six months later ...
"Hey um..."
you have no idea how right you are :)
Wasn't a bad choice, but once you load 20TB into it, you realize you done goofed.
You're not wrong, but...
I wish there were some alternative where we instead fixed our ossified, byzantine processes gradually over time, so that we didn't need to break out the sword arm tornado in the name of getting work done and then, in hindsight, say, "well, shame about the negative externalities, but there really just was no other way. Ah well, let's move on, everyone who isn't a pile of flesh and blood and bits on the floor tidy up. Gotta get everything clean for the next sword arm tornado."
If Excel is a sword tornado, it's one happening in an environment where everyone knows to be super vigilant about sharp objects. The alternative then would be a central processing factory that takes several years and millions of dollars to built, and which has to be turned off for a month several times a year, to change the shape of the blades used by the automated cutters.
I've found errors in every Excel spreadsheet I've ever looked at, and I'm not some master-excel user; I usually find them because - if I care about the results, I rewrite them as a Python script, so I get to go through everything.
The fact that Excel is effectively not auditable is a huge problem. There are a lot of dollars saved, like you said, but also many wasted or embezzled without anyone noticing in time because past a very low bar of complexity, it's really impossible to figure out what's happening without much, much work.
> If Excel is a sword tornado, it's one happening in an environment where everyone knows to be super vigilant about sharp objects.
... rather, no one is really careful, everyone gets a small cut every now and then, to which the apply a bandage and continue like nothing happened. And occasionally, they lose an eye or a limb or a head - and it is only those cases you read about in the newspapers.
I do not have a better suggestion, I'm afraid, but Excel is causing damage everywhere - e.g. [0], the subtitle - which I'm afraid is not an exaggeration, is "Sometimes it’s easier to rewrite genetics than update Excel"
[0] https://www.theverge.com/2020/8/6/21355674/human-genes-renam...
Time point 1: "We need a way to map the long-form epidemiology state. It's 2D data... Let's use Excel." "Will we hit limits?" "Pfft, no. Not unless we end up needing to track tens of thousands of patients, and that's not practical; we don't have enough people to do that tracking in the whole NHS."
Time point 2: "This pandemic is an URGENT problem. We need to hire more people than ever before to do tracing. And be sure to keep the epidemeology state mapper updated so it can drive the summary dashboards!"
It's lightweight, fast, portable, compatible, and just about everyone can use it regardless of (technical) skill.
I can imagine an automated process that appended to the CSV and then Excel saving back to that same file with the lines stripped.
I hope we can all agree that this is not the way Excel or any other software should behave, whether the user is incompetent or not.
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.
They may have chosen XLS because they used Excel back when that was default, and now they don't want to "risk" anything by switching horses mid-stream.
The Guardian reported my version:
https://www.theguardian.com/politics/2020/oct/05/how-excel-m...
CSV would to me make more sense than an outdated binary format like XLS, but either story is plausible.
It's been quoted/reported as "an ill-thought-out use of Excel", when in reality it's "poor use of an older file format".
There's also a lesson to be had here about validating your data conversions. Especially for critical things like this, it's always a good idea to perform the conversion from the old file format to the new one, and then to go back and extract the data from the new one to compare to the source data.
This can also find issues like Excel cluelessly assuming a field is a date, PHP automatically converting a string which happens to only contain numbers into an integer, and so on.
From what I can tell, that sort of thing, assuming proper monitoring (which is a huge assumption), would have detected this immediately (plus however long until someone notices the error e-mail, etc.).
This was absolutely a user error and the title "Excel: Why using Microsoft's tool caused Covid-19 results to be lost" is really disappointingly click-baity.
IT ineptitude of the highest order, although I'm not surprised having been involved with government IT previously.
Who are "they"? The Government? GDS? PHE? NHS England? NHS Digital? NHSx?
"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.
Then they're surprised when it all goes tits-up.
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.
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.
What we're talking about here is a government department who do have access to servers, but choose not to use them (or so it would seem).
Excel works enough for small data, particularly when you don't do complex queries on it. The more you know of it, the better it works. The only other thing that offers similar benefits to Excel but works as a database is MS Access, but the mental model behind it is too complex for your average office worker who wasn't trained in it, and like most database systems, requires a lot of up-front work with figuring out the schema, and doesn't particularly like the schema being modified later on.
As far as I can tell, there's literally nothing else out there. No, random SaaS webapps du jour don't count, because they're universally slow, and also store the data in the cloud, instead of the local drive.
Covid: Test error 'should never have happened' - Hancock
Please note this has been discussed quite a lot here today.
Eg.
https://news.ycombinator.com/item?id=24685911
https://news.ycombinator.com/item?id=24685950
https://hn.algolia.com/?query=follow-up%20by%3Adang&dateRang...
https://hn.algolia.com/?query=%22significant%20new%20informa...
Edit: just seen your other comment re merging, thanks.
Apple/Claris FileMaker has had this niche for a bit, but it’s only been a niche,
[0] https://www.nytimes.com/2013/04/19/opinion/krugman-the-excel...