# Reconcile two exports Two files that should agree, such as a bank export and your bookkeeping: a small script matches them by rules you approve and proves nothing was lost, you check a sample by hand, and an assistant only helps label the leftovers. Workflows · v1.0 · draft · https://hayzlabs.com/workflows/reconcile-two-exports/ In short: - Export both files as CSV, note each app's row count and total, and confirm the script's profile matches them. - Set the matching rules yourself, run the script until it prints tie-out ok, and check a sample of pairs and leftovers by hand. - Use an assistant only to propose settings and suggest labels for leftovers, and confirm labels against the source records. > [!NOT ADVICE] > This page is a general method for finding differences between two records. It is not accounting, tax or financial advice. If the figures feed a tax return, payroll or accounts that someone else relies on, have a qualified person review the result. **Produces:** three lists from two files that should agree (rows matched, rows only in A, rows only in B), with totals that tie and a label on every leftover. **Use it when** two exports should agree and do not: a bank statement and a bookkeeping export, orders and payments, or two systems' customer or invoice lists with an amount and a date. It pays off most when you will do it again next month. **Not when** each file has fewer than about 20 rows (tick them off by hand), or you need an audit opinion or a tax answer. This finds the differences; a professional decides what they mean. **You need** both exports as CSV for the same dates, the row count and total each app shows for that range, Python 3 (nothing to install), and a chat assistant for steps 3 and 7. The assistant sees only a short profile and the leftovers, never the full files. The steps and times are our suggestion; the script was checked on small made-up files. ## Steps | # | Step | Who | Time | Check | |---|---|---|---|---| | 1 | Export both files as CSV for the same dates. Save them as `a.csv` and `b.csv` next to `reconcile.py`. Write down the row count and total each app shows for that range. | Person | 10 min | Same date range on both, and you have two figures per file from the apps themselves. | | 2 | Run `python3 reconcile.py profile`. | Script | 1 min | Its rows and total for each file equal the figures you wrote down. **If it fails:** fix the date range, a column name or `date_format` and run it again. A summary or balance row stops the script with its line number; delete that row from the CSV. | | 3 | Paste the profile into a chat with the rules prompt and answer its questions. | AI | 5 min | Every proposed setting has a reason you understand. | | 4 | Decide each setting yourself and type it into `RULES` at the top of the script. | Person | 5 min | You can say why each value is what it is. **If it fails:** do not run step 5. | | 5 | Run `python3 reconcile.py run`. | Script | 1 min | It ends with `tie-out ok`. **If it fails:** it prints `STOP:` and the reason, and writes nothing. | | 6 | Check a sample by hand (see the check below). | Person | 10 min | Every pair and leftover you checked is right. **If it fails:** the settings are wrong; go back to step 3. | | 7 | Paste `only_a.csv` and `only_b.csv` into a chat with the explain prompt. Open the source record for any label that would change what you do, then fill the `label` column yourself. | AI, then person | 10 to 30 min | Every leftover has a label, and each "unknown" has an owner and a date to follow up. | | 8 | Fix what can be fixed at the source. Keep the script and its settings for next month. | Person | varies | Next month only the file names change. | > [!CHECK] > Steps 2 and 6 are the gates, and neither relies on the model. In step 2 the script's row counts and totals must equal what each app reports, which proves the files are complete. In step 6, pick five rows from `matched.csv` and five from the leftover files, find each one in the original exports by its line number (line 14 is usually row 14 in a spreadsheet), and confirm by eye that the pair is the same item and the leftover has no partner. Then check the `difference A - B` line: the gap between the two file totals should equal only-A minus only-B, plus any matched gap allowed by your tolerance. Try the two settings on a small example before you set them for real. Widen them and watch which rows pair up, and which pairs are wrong. ## The script Save this as `reconcile.py` in the folder with the two CSVs. Edit only the `RULES` block. It reads money as exact decimals, handles a byte-order mark, `1,500.00`, `(63.50)` for negatives and blank lines, and stops with a plain message rather than guessing. ```python title="reconcile.py · v1.0" # reconcile.py: match two CSV exports by rules you set. Python 3 only. # python3 reconcile.py profile row count, total, dates and first rows of each file # python3 reconcile.py run writes matched.csv, only_a.csv and only_b.csv import csv, sys from datetime import datetime from decimal import Decimal, InvalidOperation RULES = { "a": {"file": "a.csv", "date": "Date", "amount": "Amount", "text": "Description", "date_format": "%d/%m/%Y", "flip_sign": False, "decimal_comma": False}, "b": {"file": "b.csv", "date": "Date", "amount": "Total", "text": "Contact", "date_format": "%Y-%m-%d", "flip_sign": False, "decimal_comma": False}, "days": 3, # dates may be this many days apart "tolerance": "0.00", # amounts may differ by this much } def stop(msg): sys.exit("STOP: " + msg) def money(text, decimal_comma): s = text.strip() for ch in " $£€¥": s = s.replace(ch, "") neg = s.startswith("(") and s.endswith(")") # (63.50) means -63.50 s = s.strip("()") s = s.replace(".", "").replace(",", ".") if decimal_comma else s.replace(",", "") try: v = Decimal(s) except InvalidOperation: stop(f"cannot read amount {text!r}; check the amount column and decimal_comma") return -v if neg else v def load(side): r = RULES[side] rows = [] with open(r["file"], newline="", encoding="utf-8-sig") as fh: reader = csv.DictReader(fh) for col in (r["date"], r["amount"]): if col not in (reader.fieldnames or []): stop(f"{r['file']} has no column {col!r}; it has {reader.fieldnames}") for rec in reader: if not any(isinstance(v, str) and v.strip() for v in rec.values()): continue # a line of empty cells line = reader.line_num # the line in the file, so you can find the row try: day = datetime.strptime((rec[r["date"]] or "").strip(), r["date_format"]).date() except ValueError: stop(f"{r['file']} line {line}: date {rec[r['date']]!r} does not fit {r['date_format']}") amt = money(rec[r["amount"]] or "", r["decimal_comma"]) text = (rec.get(r["text"]) or "").strip() if r["text"] else "" rows.append({"line": line, "date": day, "text": text, "amount": -amt if r["flip_sign"] else amt}) if not rows: stop(f"{r['file']} has no rows") return rows def total(rows): return sum((x["amount"] for x in rows), Decimal(0)) def profile(): for side in ("a", "b"): r, rows = RULES[side], load(side) print(f"== {side.upper()}: {r['file']}") print(f"rows {len(rows)} | total {total(rows)} | dates " f"{min(x['date'] for x in rows)} to {max(x['date'] for x in rows)}") with open(r["file"], newline="", encoding="utf-8-sig") as fh: for i, row in zip(range(6), csv.reader(fh)): print(" ", row) def run(): a, b = load("a"), load("b") tol = Decimal(RULES["tolerance"]) free, matched = list(b), [] for x in sorted(a, key=lambda r: (r["date"], r["line"])): fits = [y for y in free if abs((x["date"] - y["date"]).days) <= RULES["days"] and abs(x["amount"] - y["amount"]) <= tol] if fits: # take the closest date, then the closest amount y = min(fits, key=lambda y: (abs((x["date"] - y["date"]).days), abs(x["amount"] - y["amount"]), y["line"])) free = [z for z in free if z is not y] matched.append((x, y)) used_a = {x["line"] for x, _ in matched} only_a = [x for x in a if x["line"] not in used_a] only_b = free m_a, m_b = total([x for x, _ in matched]), total([y for _, y in matched]) # Tie-out: stop rather than write anything if a row was lost or used twice. if len(used_a) != len(matched) or len({y["line"] for _, y in matched}) != len(matched): stop("a row was matched twice") if len(matched) + len(only_a) != len(a) or len(matched) + len(only_b) != len(b): stop("rows do not add up") if m_a + total(only_a) != total(a) or m_b + total(only_b) != total(b): stop("totals do not tie") if abs(m_a - m_b) > tol * len(matched): stop("matched amounts differ by more than the tolerance") with open("matched.csv", "w", newline="", encoding="utf-8") as fh: w = csv.writer(fh) w.writerow(["a_line", "b_line", "a_date", "b_date", "days_apart", "a_amount", "b_amount", "a_text", "b_text"]) for x, y in matched: w.writerow([x["line"], y["line"], x["date"], y["date"], abs((x["date"] - y["date"]).days), x["amount"], y["amount"], x["text"], y["text"]]) for name, rows in (("only_a.csv", only_a), ("only_b.csv", only_b)): with open(name, "w", newline="", encoding="utf-8") as fh: w = csv.writer(fh) w.writerow(["line", "date", "amount", "text", "label"]) for x in rows: w.writerow([x["line"], x["date"], x["amount"], x["text"], ""]) print(f"A: {len(a)} rows, total {total(a)} | B: {len(b)} rows, total {total(b)}") print(f"matched {len(matched)} | only in A {len(only_a)} ({total(only_a)}) | " f"only in B {len(only_b)} ({total(only_b)})") print(f"difference A - B = {total(a) - total(b)} = only-A {total(only_a)} " f"- only-B {total(only_b)} + matched gap {m_a - m_b}") print("tie-out ok") if __name__ == "__main__": if sys.argv[1:] == ["profile"]: profile() elif sys.argv[1:] == ["run"]: run() else: sys.exit("Usage: python3 reconcile.py profile | run") ``` What each setting means: - `file`, `date`, `amount`: the file name and the exact column headers, as the profile prints them. - `text`: a column to show beside each row, such as the description, or `None`. - `date_format`: `%d/%m/%Y` for 31/12/2026, `%m/%d/%Y` for 12/31/2026, `%Y-%m-%d` for 2026-12-31. - `flip_sign`: `True` for a file that shows money out as positive where the other shows it as negative. - `decimal_comma`: `True` for a file whose amounts look like `1.234,56`. - `days`, `tolerance`: how far apart a pair's dates and amounts may be. Start with 3 days and `"0.00"`. ## The prompts For step 3, paste everything the profile printed in place of ``. Replace names in the sample rows first if they are private: [What not to paste](https://hayzlabs.com/start/what-not-to-paste/). ```text title="Reconcile: propose settings · v1.0" I need to reconcile two CSV exports with a Python script. For each file its settings are: date column, amount column, text column, date_format (Python strptime), flip_sign, decimal_comma. Shared settings: days (date tolerance) and tolerance (amount tolerance). Here is a profile of each file: row count, total, date range and the first rows. A is . B is . Propose a value for each setting, with one line on why. Then list what you cannot tell from the samples as questions for me: fees, split payments, refunds, pending items, or a different date basis such as posting date against transaction date. Do not write code. ``` Replace the following: - ``: everything `python3 reconcile.py profile` printed. - ``: what file A is, such as `my bank export, September 2026`. - ``: what file B is, such as `bank transactions in my bookkeeping app, same dates`. Before you accept the reply, check that the proposed date format fits the sample rows and that the sign of money out is stated for both files. For step 7, paste the contents of the two leftover files, not the full exports: ```text title="Reconcile: explain leftovers · v1.0" These rows did not match between A () and B (). Matching rules: dates within days, amounts within . only_a.csv: only_b.csv: For each row give: file | line | label | one-line reason. Use only these labels: timing (will appear next period), fee, duplicate, split (one row in one file is several in the other), missing (recorded in one place only), amount error, unknown. Use "unknown" when you are not sure. Do not guess. Point out any pair of leftovers, one from each file, that look like the same item. ``` Replace the following: - ``, ``: the same descriptions you used in step 3. - ``, ``: the `days` and `tolerance` values in your `RULES`. - ``, ``: the full contents of `only_a.csv` and `only_b.csv`. A "duplicate" label stays a suggestion until you have seen both receipts. ## A worked example (illustrative) A bank export (A) has 7 transactions for September and a bookkeeping export (B) has 6. The profile matches both apps' figures. The run prints: ```text A: 7 rows, total 3142.75 | B: 6 rows, total 3156.76 matched 5 | only in A 2 (-47.00) | only in B 1 (-32.99) difference A - B = -14.01 = only-A -47.00 - only-B -32.99 + matched gap 0.00 tie-out ok ``` The leftovers are a 5.00 account fee and a second 42.00 office supplies charge on the same day (both only in A), and a 32.99 software subscription only in B. The assistant suggests fee, duplicate and missing. Checked against the bank record, the fee and the duplicate hold, and the subscription was paid from another card. The figures are invented. ## How to tell it worked - Step 2 matched both apps' own row counts and totals. - Step 5 printed `tie-out ok`, and your hand sample in step 6 was right. - Every leftover has a label, and each "unknown" has an owner and a date. - It has failed if many leftovers in both files share the same amounts. A date format, sign or tolerance setting is wrong; go back to step 3. One variation worth knowing: for card payouts where the processor takes a fee first, set `tolerance` to the largest fee you accept, such as `"2.50"`, and expect a "fee" label on the difference. > [!LIMIT] > The script pairs each row in A with the closest-dated unused row in B that fits the rules. With many equal amounts a few days apart it can swap two pairs that are each plausible; the totals still tie, so check `days_apart` in `matched.csv` for pairs at the edge. It does not match one payment against several invoices, and it cannot tell you which file is right. ## Why a script does the matching - Models are weak at long table work: on SpreadsheetBench 2 (2026), the best of eight frontier models reached about 35% task accuracy on realistic business spreadsheet workflows ([SpreadsheetBench v2](https://arxiv.org/abs/2606.29955), benchmark, preprint). We found no study of models on reconciliation itself. - So the model only proposes settings and labels, where a wrong answer is cheap to spot, and a script you can rerun does the counting. For open questions about one file, see [Asking questions of your data](https://hayzlabs.com/workflows/asking-questions-of-your-data/); for when a script beats an agent, [Script, workflow or agent](https://hayzlabs.com/guides/script-workflow-or-agent/). ## Sources 1. SpreadsheetBench v2, 2026. Benchmark of models on realistic spreadsheet workflows, preprint. [Link](https://arxiv.org/abs/2606.29955)