Committed workbooks carry no recalculated values — 30 of 56, including half the final-public release set

[alexroessner]

30 of 56 committed public-case workbooks contain zero cached formula values. The committed artefact and the artefact CI verifies are different files, and the difference blinds the one gate that scans for cached formula errors.

Measured on 9e6b7c8 with openpyxl (data_only=True vs data_only=False, counting formula cells that have a stored result).

The measurement

committed instances carrying formulas: 56
  fully cached : 26
  partial      : 0
  ZERO cached  : 30

Zero-cached, and it is not a fringe set — 6 of the 12 workbooks in the final-public evidence release are in it:

02_Corporate_Finance/instances/public_microsoft_2024.xlsx        0/198   <- final-public
05_Private_Credit/instances/public_yellow_2022_stress.xlsx       0/293   <- final-public
06_Debt_Finance/instances/public_carnival_2020_stress.xlsx       0/148   <- final-public
06_Debt_Finance/instances/public_microsoft_2024.xlsx             0/149   <- final-public
07_Public_Finance/instances/public_jamaica_2024.xlsx             0/271   <- final-public
07_Public_Finance/instances/public_sri_lanka_2023_stress.xlsx    0/271   <- final-public
09_Risk_Management/instances/public_fred_balanced_2024.xlsx      0/313
19_..._Securitization/instances/public_fed_mortgage_2024.xlsx    0/956
20_Project_Finance/instances/public_nrel_solar_2024.xlsx         0/503
22_Quantitative_Systematic/instances/public_sp500_2019_2023.xlsx 0/591
31_Software_SaaS/instances/public_adobe_fy2025.xlsx              0/307
... 19 more

It is a regression, not a standing condition. 02_Corporate_Finance/instances/public_microsoft_2024.xlsx was 198/198 at c87c705 and is 0/198 at f5c0219.

Why it matters beyond presentation

tools/weekly_refresh_check.py is the repo's cached-error scanner — it loads data_only=True and scans for #REF! #NAME? #VALUE! #DIV/0! #N/A #NULL! #NUM!. On a workbook with no cache every formula cell reads None, so it can never find anything. It is a prohibition check running against an empty subject, and it reports that as clean.

I did not want to assert that from reading the code, so I ran the repo's own tool against a planted control. Three files, all from public_microsoft_linkedin_2016.xlsx:

file	formulas	cached	IS!F5	weekly_refresh_check.py says
A: unmodified	194	194	=E5*(1+Assumptions!C5)	clean
B: IS!F5 set to =1/0, then really recalculated	194	194	=1/0	71 CELL ERROR(S)
C: B, round-tripped through openpyxl	194	0	=1/0	clean

B and C have byte-identical formulas and the same =1/0 defect. The instrument finds 71 errors in one and reports the other clean. So the negative it produces on those 30 files is evidence of nothing.

Two checks I expected to have the same problem do not, and I want to be clear about that: validate_model_inventory.py, validate_reconciled_models.py and workbook_engineering.py all load data_only=False, but each is deliberately scoped to literal typed-in error strings (literal_errors, literal_excel_error) and is correctly named for what it does. They are not affected.

Why CI doesn't catch it

final-public-evidence-release.yml does recalculate 12 committed workbooks and rejects total_errors != 0 — but it recalculates in the runner and discards the result. The recalculated bytes are never committed, so the check is real and its subject is a temporary file. flagship-validation.yml recalculates .flagship-build/*.xlsx, which are freshly built, not the committed instances.

Nothing anywhere asserts that a committed workbook carries the values it was verified with. I grepped for a prior implementation before writing one and found none.

What I'd propose

A tools/verify_recalc_persistence.py that fails closed when a committed public-case workbook carries formulas and no cached values, wired into CI, plus a recalculated re-commit of the 30. Written as a presence check — an empty cache must fail rather than pass quietly — and reporting the count either way, so "0 errors in 198 cached cells" and "0 errors in 0 cached cells" stop reading as the same green.

I'll send that as a PR unless you'd rather shape it differently. Flagging the file list up front so we don't both build it: tools/verify_recalc_persistence.py (new), tests/test_recalc_persistence.py (new), one new workflow, and the 30 .xlsx files above.