Every fact below comes from the public sources listed at the end. ShipDataFast had no involvement in this case.

On Sunday 4 October 2020, Public Health England (PHE) said that 15,841 positive Covid-19 cases between 25 September and 2 October had not been included in the reported daily figures [1]. More than 75% of them (11,968) should have been reported between 30 September and 2 October [1]. The issue had been found overnight on Friday 2 October, in the data load process that moves positive lab results into reporting dashboards [1].

This was not a finance failure, but anyone in investment, fund or finance operations will recognise the shape of it. A file arrives. It goes through a spreadsheet on its way to somewhere that matters. The output looks normal. Some of the rows are gone.

What broke in the data

A load that silently dropped rows. PHE's statement says the cause was that "some files containing positive test results exceeded the maximum file size" of the process that loads them into central systems [1].

An old file format with a hard row limit. The BBC reported the detail. Commercial labs sent their results to PHE as CSV files, which caused no problem on their own. PHE had built an automatic process to pull that data into Excel templates before uploading it to a central system, and the templates used the old XLS format [3]. Microsoft documents that the .xls format allows 65,536 rows per sheet, while .xlsx allows 1,048,576 [4]. Because each test result created several rows, the BBC reported, each template could hold only about 1,400 cases, and "further cases were simply left off" [3].

A downstream process that trusted the output. The missing results were not only absent from the public numbers. The Health Secretary told the House of Commons that they "were not transferred to the contact tracing system" [2]. Everyone who tested positive was still told their own result as normal [1, 2]. All outstanding cases were moved into contact tracing by 1am on 3 October [1].

PHE's daily table shows the gap day by day: 957, 744 and 757 missing cases on the first three affected days, 0 on the fourth, then 1,415, 3,049, 4,133 and 4,786 on the last four [1]. A fixed row limit behaves like that. It does nothing until a day's volume crosses it, and the bigger the day, the more it drops.

Why nobody noticed sooner

PHE said there were "already a number of automated and manual checks" in the process [1]. What those checks were is not public, so we won't guess. What the record does show is that the problem was a size limit, and the Health Secretary told MPs that "the challenge of a maximum file size error is that it would not necessarily have appeared" on a data flow chart [2]. He also said the problem emerged in a PHE legacy system that had already been chosen for replacement in July [2].

The immediate fix was to split large files into smaller batches [1, 3], and PHE started a full end-to-end review of its systems [1].

In fairness: early reports differed on the exact mechanism. The Guardian first reported a million-row Excel limit, and the BBC reported the XLS template explanation [3, 5]. PHE's own statement only says "maximum file size" [1]. Which labs' files were affected and why XLS was chosen are not public.

5 reconciliation checks that catch missing rows

These apply to any file that passes through a spreadsheet or a load step on its way to a report: custodian files, fund accounting extracts, bank statements, Finance spreadsheets.

  1. Matching key. Agree the key that identifies one real record (a test, a trade, a position) and reconcile on it. Totals only tell you something is off. A key gives you the list of records in the source but not in the output.
  2. Tolerances written down. Agree the allowed difference for each check before you run. For row counts per file, the tolerance is zero. A count that is "close" is a break.
  3. Expected differences listed. Write down every row you drop on purpose (duplicates, out-of-scope records) with its rule and reason. Anything missing that isn't on the list is a break, not an explanation to find later.
  4. Named owners. Someone owns each file load and its limits: the format, the row cap, the file size. When volumes grow, that person checks whether the tool still fits before the day it doesn't.
  5. Sign-off evidence. Keep rows in and rows out for every file and every load, alongside the run, then check fields on the matched rows. A green job status, or a spreadsheet that opened without an error, is not evidence that nothing was lost.

None of this is clever. It is counting, done every time, with the numbers kept.

Get the one-page version

We turned this case into a one-page PDF, How to avoid losing rows between a file and a spreadsheet, with the 5 checks, the evidence to ask for and the red flags. For the full set of sign-off checks, get the free checklist at shipdatafast.com/checklist.

Sources

  1. Public Health England, "PHE statement on delayed reporting of COVID-19 cases", 4 October 2020, updated 5 October 2020. GOV.UK statement
  2. Hansard, House of Commons, "Covid-19 Update", 5 October 2020. Hansard debate
  3. Leo Kelion, "Excel: Why using Microsoft's tool caused Covid-19 results to be lost", BBC News, 5 October 2020. BBC News article
  4. Microsoft Support, "What to do if a data set is too large for the Excel grid". Microsoft Support page
  5. "Covid: how Excel may have caused loss of 16,000 test results in England", The Guardian, 5 October 2020. Guardian article