Hayz Labs

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.

Advanced7 min readDoing it takes about 60 min the first timeChecked 11 Oct 2026

What this is
How to compare two lists that should agree, such as a bank statement and your bookkeeping, and find every row that does not match.
Why it matters
You get every difference listed, with proof that no row was lost, without trusting an AI chatbot to do the counting.
The short version
  • 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.
On this page
  1. Steps
  2. The script
  3. The prompts
  4. A worked example (illustrative)
  5. How to tell it worked
  6. Why a script does the matching
  7. Sources
  8. Changes

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

#StepWhoTimeCheck
1Export 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.Person10 minSame date range on both, and you have two figures per file from the apps themselves.
2Run python3 reconcile.py profile.Script1 minIts 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.
3Paste the profile into a chat with the rules prompt and answer its questions.AI5 minEvery proposed setting has a reason you understand.
4Decide each setting yourself and type it into RULES at the top of the script.Person5 minYou can say why each value is what it is. If it fails: do not run step 5.
5Run python3 reconcile.py run.Script1 minIt ends with tie-out ok. If it fails: it prints STOP: and the reason, and writes nothing.
6Check a sample by hand (see the check below).Person10 minEvery pair and leftover you checked is right. If it fails: the settings are wrong; go back to step 3.
7Paste 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 person10 to 30 minEvery leftover has a label, and each "unknown" has an owner and a date to follow up.
8Fix what can be fixed at the source. Keep the script and its settings for next month.PersonvariesNext month only the file names change.

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.

This part of the page is interactive and needs JavaScript.

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.

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 <PROFILE>. Replace names in the sample rows first if they are private: What not to paste.

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.
<PROFILE>
A is <WHAT_A_IS>. B is <WHAT_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:

  • <PROFILE>: everything python3 reconcile.py profile printed.
  • <WHAT_A_IS>: what file A is, such as my bank export, September 2026.
  • <WHAT_B_IS>: 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:

Reconcile: explain leftovers · v1.0
These rows did not match between A (<WHAT_A_IS>) and B (<WHAT_B_IS>). Matching rules: dates within <DAYS> days, amounts within <TOLERANCE>.
only_a.csv:
<ONLY_A>
only_b.csv:
<ONLY_B>
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:

  • <WHAT_A_IS>, <WHAT_B_IS>: the same descriptions you used in step 3.
  • <DAYS>, <TOLERANCE>: the days and tolerance values in your RULES.
  • <ONLY_A>, <ONLY_B>: 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:

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

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, 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; for when a script beats an agent, Script, workflow or agent.

Sources

  1. SpreadsheetBench v2, 2026. Benchmark of models on realistic spreadsheet workflows, preprint. Link

Changes

  1. v1.010 Oct 2026first version
Time to do it
about 60 minutes the first time, 20 after (our estimate)
Sources
1
Updated
10 Oct 2026
Last checked
11 Oct 2026
esc
↑↓ move↵ openesc closeType a product, a job or a question

Keyboard shortcuts

CtrlK
search everything
/
search everything
gr
reviews: all AI, scored
gg
start here
gu
guides
gw
step-by-step workflows
gt
try-it tools
gs
setup
ga
about
[ ]
previous and next entry, or collection
t
theme: auto, light, dark
?
this sheet