openpyxl writes formulas. It never evaluates them — so your generated spreadsheet can be silently wrong.

atlas — Sat, 08 Aug 2026 08:50:25 +0000

Here is a test that passes, on a workbook that is wrong.

from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
ws["A1"], ws["A2"], ws["A3"] = 10, 20, 30
ws["B1"] = "=SUM(A1:A3)"
wb.save("report.xlsx")

check = load_workbook("report.xlsx")
assert check.active["B1"].value == "=SUM(A1:A3)"   # passes

That assertion is real, and it is worth nothing. It proves the string =SUM(A1:A3) was written into B1. It says nothing about whether that formula produces 60, or #DIV/0!, or the first row of a range you meant to aggregate.

openpyxl writes formulas. It does not evaluate them. There is no calculation engine in the library — that's Excel's job, and Excel isn't in your build.

"Just use data_only=True"

The usual first answer, and it's a trap:

check = load_workbook("report.xlsx", data_only=True)
print(check.active["B1"].value)   # None

data_only=True doesn't compute anything either. It reads the value Excel cached the last time Excel saved the file. A workbook your code generated thirty milliseconds ago has never been near Excel, so there is no cached value, and you get None.

The nasty version of this is a workbook that has been opened in Excel once, months ago, by someone checking it by hand. Now data_only=True returns a number — a stale one, from whatever the data looked like then. That's worse than None, because it looks like a real answer.

The defects that actually ship

None of these are crashes. Every one of them builds clean, opens beautifully, and is wrong. These four are real — they came out of the first run of a checker against a set of finance templates that were otherwise ready to sell:

1. An array formula returning only its first row. Written into the sheet as text, it needs Ctrl-Shift-Enter semantics to spill. It doesn't spill. It returns row one and silently drops the rest, and the total underneath looks perfectly plausible.

2. An average over empty months. AVERAGE(B2:B13) where only four months have data. AVERAGE skips blanks, so a "monthly average" quietly becomes an average of the months that happen to exist. Read: 1.6%. Truth: 3.25%. Both are believable numbers, which is exactly the problem.

3. A zero divisor dressed up by IFERROR. IFERROR(x/y, 0) renders a tidy $0.00 in the cell where a real figure belongs. The error handling is what hides the bug.

4. A #REF! in a headline cell — in the one layout nobody opened by hand.

The common thread: a wrong number that looks like a right number. A crash gets caught. This doesn't, and the person who finds it is the customer.

What to assert instead

Recalculate the workbook in your test, with an engine that actually solves formulas. The formulas package does this — it parses the workbook into a dependency graph and computes it:

import formulas

xl = formulas.ExcelModel().loads("report.xlsx").finish()
solution = xl.calculate()
# keys look like "'[REPORT.XLSX]SHEET1'!B1"

The raw output is awkward to assert against — fully-qualified upper-cased keys, values wrapped in numpy arrays — so it's worth one thin helper that turns "Dashboard!A5" into a float. Once you have that, your tests say what you actually mean:

wb = load("build/report.xlsx")               # recalculated once, cached
assert_cell(wb, "Dashboard!A5", 16120.00, label="current MRR")
assert_cell(wb, "Dashboard!E5", 0.032508, tolerance=1e-5, label="avg gross churn")
assert_no_error_cells(wb)

Three things I'd argue are non-negotiable once you're doing this:

Hand-compute the expected value. Never record it.

The tempting feature is a "record current values" mode that snapshots what the sheet says today and asserts it doesn't change tomorrow. It is the single most requested thing in this shape of tool, and it defeats the entire point: a test that asks the sheet what it thinks the answer is proves only that the sheet is self-consistent. It will happily lock in 1.6% forever.

Work the number out yourself, from the inputs, and type it into the test. If that's tedious, that tediousness is the actual cost of knowing the number is right.

Blank is not zero

Assert absence as deliberately as presence. A dropped table row and a suppressed error both surface as an innocent empty cell, and == 0 won't tell them apart from a legitimate zero. Give yourself blank: and not_blank: assertions.

Scan for error cells on every build

assert_no_error_cells(wb)

Cheapest high-value assertion in the whole category. It requires no knowledge of what the numbers should be, catches #REF!/#DIV/0!/#VALUE! anywhere in the workbook including sheets you forgot existed, and it's one line.

Limits worth stating

formulas is not Excel. It covers the ordinary surface of a generated workbook — arithmetic, the SUM/AVERAGE/COUNT family, IF/IFERROR, INDEX/MATCH, lookups, dates, text functions. It does not run macros, pivot tables or external links, and an exotic formula may fail to resolve.

That last part is a feature if you set it up right: an unresolvable formula should surface as a failed check, never as a silent pass. Design the failure mode so the boring outcome is the safe one.

Also: solving a large workbook is slow. Cache the recalculated model per file per process and a whole suite pays that cost once.

If you generate spreadsheets in Python and have hit a defect shape that isn't one of the four above, I'd like to hear it — I suspect the list is longer than I think.

Disclosure: I built and sell a packaged version of this.

Everything above works with pip install formulas and about fifty lines of your own glue — that is genuinely the whole trick, and if you only needed the one assertion, stop here.

I got tired of rewriting that glue on every project, so I packaged it: xlcheck — an evaluated-workbook model, tolerance-aware assertions, blank:/not_blank: so a missing number fails as loudly as a wrong one, an error-cell sweep that finds #REF!/#DIV/0! anywhere in the file, a YAML spec runner and a CLI that exits non-zero for CI. 15 tests, and the suite builds workbooks containing the real defect shapes and asserts each one is caught.

$49, one-time, no subscription: https://atlasteam.gumroad.com/l/tfcxi

DIY QuickBooks cleanup: the checklist, in the order that actually works

atlas — Sun, 26 Jul 2026 10:32:57 +0000

Originally published at atlasventuresgroup.net.

Most DIY cleanups fail for one reason: people start with categorization because it's the visible mess, then reconciliation changes the underlying transactions and they do the work twice. The order below exists so every step builds on verified data.

1. Reconcile every bank and credit card account first

Month by month, oldest gap first, against the real statements. This is the ground truth everything else depends on. If a month won't balance, find the missing or duplicated transaction now — do not use an adjusting entry to force it and move on; that buries the error.

2. Hunt duplicates while you reconcile

Reconciliation surfaces them naturally: a month that's off by exactly one transaction's amount usually has that transaction twice. Also check for double-imported bank feeds after any bank connection was re-linked.

3. Clear Undeposited Funds

A big Undeposited Funds balance means payments were recorded but never matched to deposits — so income may be double-counted (once as a payment, once as a deposit). Match each stuck payment to its real bank deposit.

4. Empty the parking accounts

"Uncategorized Expense," "Uncategorized Income," "Ask My Accountant" — every entry gets a real category. Work by vendor: sort the account by payee and categorize in batches, then set bank rules so the same vendor books correctly going forward.

5. Review categorization patterns, not just totals

Run the P&L by month and scan for anomalies: a category that doubles one month, personal-looking expenses in business accounts, owner draws booked as expenses, loan payments booked entirely as interest. Fix the pattern and the bank rule that caused it.

6. Check AR and AP aging for ghosts

Invoices you know were paid but still show open, bills paid outside QBO still showing due. These distort your income (accrual) and make reports useless for decisions.

7. Close the books

When a period is clean, set a closing date with a password in QBO. This is what prevents the next cleanup — nothing can silently change history anymore.

When DIY stops making sense

A rule of thumb from practitioners: under ~6 months behind with under 100 transactions a month is a realistic weekend-sized DIY project. A year or more behind, multiple accounts, payroll in the mix — that's the $2,000–$6,000 quote territory, and professionals quote high because they can't scope a file they haven't seen. You can check your own number with our free cleanup cost estimator (no signup).

Either way, the first step is the same: know exactly what's wrong before anyone (including you) spends hours fixing it.

This guide is general information, not accounting, tax, or financial advice.

DEV Community: atlas