Metadata-Version: 2.5
Name: unexcel
Version: 0.1.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/unexcel
Project-URL: Issues, https://github.com/Nikhil414/unexcel/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

# unexcel

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

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. unexcel 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.

```
$ unexcel 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** | unexcel 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 unexcel generated (a bug in unexcel; the message names the cell, please report it).

## Install

Python 3.10 or newer.

```bash
pip install git+https://github.com/Nikhil414/unexcel
pip install "unexcel[dbt] @ git+https://github.com/Nikhil414/unexcel"      # also run dbt build (--dbt)
pip install "unexcel[compute] @ git+https://github.com/Nikhil414/unexcel"  # answer key for files never saved in Excel
```

Runtime dependencies are only `openpyxl` and `duckdb`.

## Use

```bash
unexcel convert book.xlsx                 # writes book_dbt/ next to the file
unexcel convert book.xlsx --out out_dir   # choose the folder
unexcel 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 31 s, all PASS.

## Limitations

unexcel 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%"`;
  unexcel 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 in every row (VLOOKUP, INDEX/MATCH)
  still run cell by cell and are slower (about 6 s per 1,000 lookups). 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.** unexcel 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/unexcel_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
UNEXCEL_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)
