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.
- 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
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. |
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.
# 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, orNone.date_format:%d/%m/%Yfor 31/12/2026,%m/%d/%Yfor 12/31/2026,%Y-%m-%dfor 2026-12-31.flip_sign:Truefor a file that shows money out as positive where the other shows it as negative.decimal_comma:Truefor a file whose amounts look like1.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.
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>: everythingpython3 reconcile.py profileprinted.<WHAT_A_IS>: what file A is, such asmy bank export, September 2026.<WHAT_B_IS>: what file B is, such asbank 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:
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>: thedaysandtolerancevalues in yourRULES.<ONLY_A>,<ONLY_B>: the full contents ofonly_a.csvandonly_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:
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
- SpreadsheetBench v2, 2026. Benchmark of models on realistic spreadsheet workflows, preprint. Link
Changes
- 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