Metadata-Version: 2.5
Name: cellproof
Version: 0.2.0
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.1
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.
- **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.

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](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)
