cellproof

Turn an Excel workbook into SQL, and prove every number matches.

(Released as unexcel up to 0.1.0; renamed to cellproof in 0.2.0.)

Business logic often lives in a spreadsheet that one person understands. Moving it into a database is risky because nobody can show the new numbers equal the old ones. cellproof translates every formula of an .xlsx into SQL, runs that SQL on DuckDB, and compares each result with the value Excel itself saved in the file. Excel's saved values are a free answer key.

$ cellproof convert examples/functions.xlsx
72 pass, 0 fail, 4 unresolved -> examples/functions_dbt/REPORT.md

Every formula cell gets one verdict:

Exit code: 0 no FAIL, 1 at least one FAIL (or dbt build failed), 2 the workbook could not be read, 3 DuckDB rejected SQL that cellproof generated (a bug in cellproof; the message names the cell, please report it).

Install

Python 3.10 or newer.

pip install cellproof
pip install "cellproof[dbt]"      # also run dbt build (--dbt)
pip install "cellproof[compute]"  # answer key for files never saved in Excel

Runtime dependencies are only openpyxl and duckdb.

Use

cellproof convert book.xlsx                 # writes book_dbt/ next to the file
cellproof convert book.xlsx --out out_dir   # choose the folder
cellproof convert book.xlsx --dbt           # also run `dbt build` on the generated project

The output folder holds:

• REPORT.md / report.json: every verdict, failed cells with both values, unresolved cells with the reason, findings (for example a number stored as text, which Excel skips in SUM), and which blocks of rows became readable table models.
• A dbt project (dbt_project.yml, models/, seeds/input_cells.csv) that recomputes the workbook on DuckDB. It runs exactly the SQL the verifier checked.

The workbook must have been saved by Excel (so the file holds Excel's values). For a file generated by code and never opened in Excel, install the compute extra: the formulas library then computes the expected values, and the report labels them computed (a weaker answer key than Excel).

How it works

reader -> regions -> dependency graph -> compiler -> verifier -> dbt emitter -> report

1. Read the file twice with openpyxl: the formulas, and the values Excel saved. Dates become Excel serial numbers.
2. Find regions. A block of rows where columns repeat one formula down (=A2*1.1, =IF(B2>100,B2,0)) is a region. Running totals in a region (=C1+B2, =SUM($B$2:B2)) become SQL window functions. A region compiles to one readable statement (amount * 1.1 AS tax) instead of one statement per cell.
3. Order every formula after the cells it reads. Circular references are UNRESOLVED.
4. Compile each formula to SQL with Excel's rules: Excel's type conversions ("3"+1 = 4), its error codes (#DIV/0!, #N/A, #VALUE! ...) which must match exactly, the left-to-right order in which Excel reports errors, and DuckDB guards (division by zero, ln(0), overflow of POWER) so DuckDB never fails where Excel returns an error. Cells outside regions run in batches ("cell mode").
5. Verify on DuckDB in memory: numbers must match within a relative 1e-9, text and booleans exactly, errors by code.
6. Emit the dbt project and the report.

93 Excel functions are supported: ABS, AND, AVERAGE, AVERAGEIF(S), CEILING, CHAR, CHOOSE, CLEAN, CONCAT(ENATE), COUNT, COUNTA, COUNTBLANK, COUNTIF(S), DATE, DATEDIF, DAY, DAYS, DAYS360, EDATE, EOMONTH, EXACT, EXP, FIND, FLOOR, HLOOKUP, IF, IFERROR, IFNA, IFS, INDEX, INT, IS* (BLANK, ERR, ERROR, LOGICAL, NA, NUMBER, TEXT), LEFT, LEN, LN, LOG, LOG10, LOWER, MATCH, MAX, MAXIFS, MEDIAN, MID, MIN, MINIFS, MOD, MONTH, MROUND, NA, NOT, OR, PI, POWER, PRODUCT, PROPER, REPLACE, REPT, RIGHT, ROUND(UP/DOWN), SEARCH, SIGN, SQRT, SUBSTITUTE, SUBTOTAL, SUM, SUMIF(S), SUMPRODUCT, SWITCH, TEXT, TEXTJOIN, TRIM, TRUE/FALSE, TRUNC, UPPER, VALUE, VLOOKUP, WEEKDAY, XLOOKUP, XOR, YEAR.

Speed (Windows 11 laptop, measured): a 200,000-formula workbook (four formula columns over 50,000 rows, one of them a running total) converts and verifies in 26 s, all PASS. 50,000 rows of VLOOKUP into a 1,000-row table: 4.6 s. COUNTIFS/SUMIFS with a criterion from the row run at the same speed.

Limitations

cellproof prefers an honest UNRESOLVED or FAIL to a guess. Known limits, each reported with a reason:

• File formats: .xlsx and .xlsm only (no .xls, .xlsb, .ods). Macros (VBA) are ignored.
• Not supported: array formulas and dynamic arrays, volatile functions (NOW, TODAY, RAND, OFFSET, INDIRECT), functions outside the list above, external workbooks, structured table references (Table1[@Col]), INDEX used as a reference, the 1904 date system in TEXT date formats.
• Approximate lookups on unsorted data (MATCH/VLOOKUP/HLOOKUP with match type 1/-1): UNRESOLVED, because Excel's answer then depends on its binary search.
• Text to number: only plain numbers convert ("3", " -1.5e3 "). Excel also reads "1,000", "$5", "50%"; cellproof gives #VALUE!, which shows as a FAIL.
• Some function options: SUBTOTAL 101-111 (hidden rows), DATEDIF units MD/YM/YD, WEEKDAY types 11-17, TEXT formats outside the supported table, COUNT or MEDIAN of values typed into the formula, criteria taken from an empty cell.
• Floating point: a running total becomes a fast window function only when Excel's saved values prove the different addition order cannot matter; balances that cancel back to zero (ledgers) are added in Excel's order instead, which is slower. Elsewhere SQL can still differ from Excel in the last digits, which would show as an honest FAIL. A number that overflows becomes inf instead of #NUM!.
• Speed: regions cover columns that repeat one formula over the same rows, lookups and *IF(S) criteria included. A formula that is not part of such a column runs cell by cell (a lookup alone: about 7 s per 1,000). Running totals other than plain sums (=C1*1.05+B2, =MAX(C1,B2)) also run cell by cell.
• The answer key: a workbook must have been saved by Excel; formulas (the compute extra) is a weaker fallback and is known to disagree with Excel in places.
• dbt: the generated project targets DuckDB (dbt-duckdb). Other warehouses are not generated yet.

Security and privacy

• No network. cellproof never sends workbook data anywhere; everything runs locally in DuckDB.
• Untrusted files: workbook text only ever reaches SQL as escaped string literals; headers become SQL names only after being reduced to [a-z0-9_]; braces are escaped so dbt's Jinja cannot run workbook text. A file that unzips to more than 2 GB is refused. For extra XML hardening on untrusted files, pip install defusedxml (openpyxl then uses it automatically).
• Output folder: a rerun deletes the old *.sql files in <out>/models/cellproof_cells/ so stale models cannot linger; point --out at a folder of its own.
• --dbt runs your installed dbt on the generated project (no shell).

See SECURITY.md to report a vulnerability.

Development

python -m venv .venv && . .venv/bin/activate      # Windows: .venv\Scripts\activate
pip install -e ".[dev,dbt,compute]"
python -m pytest tests -q                          # ~8 min with dbt on PATH
CELLPROOF_PERF=1 python -m pytest tests/test_perf.py -s   # the 200k-formula benchmark

Rules the code follows: never guess a value (anything not translated exactly is UNRESOLVED with a reason a user understands); a false PASS is the worst bug; no DuckDB exception may escape from a supported function; tests first; real Excel is the ground truth. Design notes: DESIGN.md and docs/superpowers/specs/.

License

MIT