Metadata-Version: 2.5
Name: cellproof
Version: 0.6.1
Summary: Turn an Excel workbook into SQL (a dbt project on DuckDB) and prove every number matches.
Project-URL: Homepage, https://github.com/Nikhil414/cellproof
Project-URL: Issues, https://github.com/Nikhil414/cellproof/issues
Author: Nikhil414
License-Expression: MIT
License-File: LICENSE
Keywords: dbt,duckdb,excel,migration,spreadsheet,sql,xlsx
Classifier: Development Status :: 3 - Alpha
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Financial and Insurance Industry
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database
Classifier: Topic :: Office/Business :: Financial :: Spreadsheet
Requires-Python: >=3.10
Requires-Dist: duckdb>=1.3
Requires-Dist: openpyxl>=3.1
Provides-Extra: compute
Requires-Dist: formulas<1.4,>=1.3.4; extra == 'compute'
Provides-Extra: dbt
Requires-Dist: dbt-duckdb>=1.8; extra == 'dbt'
Provides-Extra: dev
Requires-Dist: jinja2>=3.1; extra == 'dev'
Requires-Dist: pytest>=8; extra == 'dev'
Description-Content-Type: text/markdown

# 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](https://duckdb.org), 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:

| Verdict | Meaning |
|---|---|
| **PASS** | the SQL gives the value Excel saved |
| **FAIL** | the SQL gives a different value; the report shows both |
| **UNRESOLVED** | cellproof could not translate the formula exactly; the report says why. It never guesses. |

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.

```bash
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

```bash
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. The unresolved reasons are ranked by how many cells depend on them ("Where to start"), so a long list
  of unresolved cells says what to fix first.
- **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`](https://pypi.org/project/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.

140 Excel functions are supported: @ (SINGLE), ABS, AND, AVERAGE, AVERAGEIF(S), CEILING, CHAR, CHOOSE, CLEAN, CODE,
COLUMN, COLUMNS, CONCAT(ENATE), COUNT, COUNTA, COUNTBLANK, COUNTIF(S), DATE, DATEDIF, DAY, DAYS, DAYS360, EDATE,
EOMONTH, EXACT, EXP, FILTER, FIND, FLOOR, FV, HLOOKUP, IF, IFERROR, IFNA, IFS, INDEX, INT, IRR, IS* (BLANK, ERR,
ERROR, LOGICAL, NA, NUMBER, TEXT), ISEVEN, ISODD, LARGE, LEFT, LEN, LET, LN, LOG, LOG10, LOOKUP, LOWER, MATCH, MAX,
MAXIFS, MEDIAN, MID, MIN, MINIFS, MOD, MODE, MONTH, MROUND, N, NA, NETWORKDAYS, NOT, NPER, NPV, OR, PERCENTILE(.INC),
PI, PMT, POWER, PRODUCT, PROPER, PV, QUARTILE(.INC), RANK(.EQ), RATE, REPLACE, REPT, RIGHT, ROUND(UP/DOWN), ROW, ROWS,
SEARCH, SEQUENCE, SIGN, SMALL, SORT, SORTBY, SQRT, STDEV(.S/.P/P), SUBSTITUTE, SUBTOTAL, SUM, SUMIF(S), SUMPRODUCT,
SWITCH, TEXT, TEXTJOIN, TRIM, TRUE/FALSE, TRUNC, UNICHAR, UNICODE, UNIQUE, UPPER, VALUE, VAR(.S/.P/P), VLOOKUP,
WEEKDAY, WORKDAY, XLOOKUP, XMATCH, XOR, YEAR, YEARFRAC.

**Excel tables:** structured references (`Sales[Amount]`, `Sales[@Qty]`, `Sales[[#This Row],[Qty]]`, `Sales[#Totals]`,
`Sales[[Rep]:[Amount]]`) are rewritten to plain references when the file is read, so a calculated table column
compiles like any column pasted down.

**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.
- **Array constants** (`{0,50,100}`) work wherever a range is taken (`SUM`, `MATCH`, `LOOKUP`, `VLOOKUP`, `INDEX`...),
  inside array arithmetic and in array formulas; where one value is expected in a plain formula (`A1*{1,2}`) they are
  UNRESOLVED.
- **Array formulas over a block and dynamic arrays** (legacy Ctrl+Shift+Enter blocks, Excel 365 spills): every cell of
  the block is verified. A range where one value is expected takes the cell's element (one row or column repeats);
  past the array a cell is #N/A, while an IS function's own range is padded per element, as real Excel does. `A1#`
  and `@` are read. **FILTER, SORT and UNIQUE** spill their results and nest in each other; a dynamic spill whose
  result is not exactly as big as the block Excel saved shows #SPILL! in its first cell (a FAIL). The generated SQL
  computes the block Excel saved: if the data later changes the size of a FILTER's result, re-run cellproof.
  SORTBY, SEQUENCE and XMATCH too, and all of them inside aggregates (`SUM(FILTER(...))`, `ROWS(UNIQUE(...))`).
  Not yet: TAKE/DROP/CHOOSECOLS and the other array functions, these functions inside other non-aggregate
  calculations, SORT by several keys or by columns (SORTBY does both), SORT/SORTBY keys that are empty, errors or text
  with punctuation (Excel's own text order), UNIQUE over empty cells or errors - all UNRESOLVED.
- **Array arithmetic in aggregates** (`SUM(A1:A10*B1:B10)`, `SUMPRODUCT((A1:A9>5)*C1:C9)`, `MAX(IF(A1:A9="x",B1:B9))`,
  `SUM(IFERROR(A1:A9/B1:B9,0))`, `COUNTA(A1:A9&B1:B9)`, `SUM(A1:A9^2)`) follows modern Excel: element by element, an
  empty element is 0, text converts when it is a number ("3" -> 3) and is #VALUE! otherwise, comparisons rank
  numbers < text < TRUE/FALSE case-insensitively, concatenation writes numbers the Excel way, and the first error
  wins. A plain-entered range arithmetic or IF as an aggregate argument follows modern Excel,
  not the legacy first-row intersection - a workbook an old Excel last saved can show FAILs there, with both values
  shown.
- **Not supported:** volatile functions (`NOW`, `TODAY`, `RAND`, `OFFSET`, and
  `INDIRECT` of text built while the sheet runs; `INDIRECT("Sales")` is fine), functions outside the list above,
  external workbooks, a structured reference naming several separate columns (`T[[A],[C]]`), INDEX used as a reference, the 1904 date system in
  TEXT date formats.
- **Ranges where one value is expected** (`=IF(E$3:E$41="F",B$3:B$41)`) take the formula's own row or column, as
  Excel does (implicit intersection). As an aggregate's argument the element-wise answer is compiled instead (see
  array arithmetic above); in an array formula, element by element (see array formulas above).
- **Approximate lookups on unsorted data** (LOOKUP, MATCH/VLOOKUP/HLOOKUP with match type 1): compiled for plain
  numeric tables written as inputs (up to about 1,000 values): the SQL carries Excel's binary-search path itself, and
  refuses changed table values instead of answering from a stale search. MATCH with type -1, text tables, tables with
  empty cells or formulas, and bigger tables stay UNRESOLVED, because Excel's answer there is not derivable from the
  file (LibreOffice, the only open source that emulates the search, provably differs from Excel in places).
- **Text to number:** only plain numbers convert (`"3"`, `" -1.5e3 "`). Text Excel reads with the computer's language
  settings (`"1,000"`, `"$5"`, `"50%"`, `"1/2/2020"`, `"2:00 PM"`, `"Jan 5"`) makes the cell UNRESOLVED, because the
  file does not record those settings.
- **Who saved the file:** the report warns when it was last saved by another program (LibreOffice, a script): the
  saved values then follow that program's rules. In a file Excel for Mac calculated, `CHAR` above 127 is UNRESOLVED
  (the Mac character set differs).
- **Edge cases not checked in real Excel yet** are UNRESOLVED with that reason, never guessed: PMT/PV/FV/NPER with
  zero periods, a rate of -100% or less or a payment type other than 0 or 1; NPV at a rate of -100%; LARGE/SMALL with
  a k that is not whole; RANK over a range holding an error; NETWORKDAYS/WORKDAY with text or errors among the
  holidays. NETWORKDAYS.INTL, WORKDAY.INTL, IRR, RATE and the other finance and statistics functions are not in yet.
- **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](SECURITY.md) to report a vulnerability.

## Development

```bash
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](DESIGN.md) and `docs/superpowers/specs/`.

## License

[MIT](LICENSE)
