Blind spreadsheet benchmark
Organisations publish the same spreadsheet every month or quarter, and it quietly changes shape. I collected twenty real ones, three editions each, and labelled exactly what changed from one edition to the next. In a third of cases, anything reading them automatically should stop.
- Made
- Built with
- Python, reading the workbook files directly
- Status
- Corpus complete; not yet scored
The question
Tools that read spreadsheets are usually tested on tidy, one-off tables. Real recurring workbooks are nothing like that. Headers span three rows, totals sit in the middle of the data, a formula becomes a typed-in number, a column is renamed, a sheet is reshaped. The hard question isn't "can you read this table?" but "is this month's file still the same kind of file as last month's, and is it safe to carry on?" I wanted a benchmark that asks exactly that, on real files.
What's in it
- 20 recurring families, 60 editions. Each family is one publication that recurs, with three consecutive editions kept exactly as published. They come from ONS (business survey tables), the NHS (cancer waiting times, staffing, beds, waiting lists), Ofgem (price-cap and network allowance models), Ofwat (a price-control financial model), and London traffic-appeal statistics.
- 1,075 sheets inventoried, with 227 critical regions and 203 critical datasets annotated, and 618 values checked literally against the file.
- Every hard case, at least once. Several tables on one sheet (19 families), multi-row headers (all 20), merged cells that carry structure (16), totals inside the data (17), formulas mixed with constants (14), cross-sheet references (14), hidden sheets, rows or columns (12), links to other workbooks (9), mapping sheets (7), macros (2) and a data connection (1).
- Untouched originals. No workbook was opened in a spreadsheet program or re-saved, and no macro was ever run. Everything was read from the file package directly, and every original is kept with its hash.
What broke
Each of the 40 edition-to-edition pairs has a decision: would a careful automated reader carry on, carry on with a warning, or stop?
Visible layout drift appeared in 19 of the 20 families. Most changes are harmless, such as a renamed sheet or a reordered column. But 18 of the 61 changes found were material: they change what the numbers mean, or whether they can be trusted. On the corpus's 0 to 4 drift scale (read from the planted examples below: 1 for renames and reordering, up to 4 when the shape of the data changes), the changes split 20, 17, 8, 12 and 4. The outcomes split 23 carry on, 4 warn and 13 stop.
Ten planted changes
Real drift is uneven, so the corpus also has ten controlled mutations. Each takes one real workbook and makes exactly one change, recorded with the expected rating and outcome. Seven are held blind.
| Change | Rating | Expected |
|---|---|---|
| Sheet renamed | 1 | carry on |
| Columns reordered | 1 | carry on |
| Header reworded, same meaning | 2 | carry on |
| Header reworded, new meaning | 2 | stop |
| Formula replaced by its value | 3 | stop |
| Stored result disagrees with its formula | 3 | stop |
| A total no longer adds up | 3 | stop |
| A row deleted from a mapping sheet | 3 | stop |
| Suppressed values turned into zeros | 3 | stop |
| A long table turned into a cross-tab | 4 | stop |
Two are nasty in a specific way. Replacing a formula with its current value changes nothing you can see today, and breaks the file the next time its inputs change. And a stored result that disagrees with its own formula fools any reader that trusts the cached number.
Keeping it blind
- Three families (and three mutations) are for development. The other seventeen are blind: settings deny the development tools any access to their folders, so nothing can be tuned against them.
- Every annotation, drift decision, licence record and hash is checked by a validation script, which is part of the acceptance rules.
Limits
- Nothing has been scored against it yet. This is the benchmark, not a result.
- Public files are cleaner than private ones. Published workbooks carry less of the informal commentary and local habits that internal spreadsheets have.
- One data connection. Only one family includes a live data connection, so that case is thin.
- Three families can't be shared. Seventeen families are redistributed under the Open Government Licence. For the other three, permission wasn't established, so only their annotations and hashes are kept, and the originals must be downloaded again from their sources.