When you merge monthly bank statements, treat the balances as a chain and test every link. Each file's opening balance plus its rows must equal its own closing balance, each file's opening must equal the previous file's closing, and the dates must touch without overlapping. The dates tell you what kind of break you have. The size of the balance jump tells you how much money it involves.
I'm Damin and I run ZeraBooks, which converts statement PDFs to Excel and CSV. Each statement we read gets proven against its own printed balances, but the check between statements is one we leave to the user, so I wrote this to show how little code it takes. It's a sketch, not our code.
The data model
You don't need the transactions to run the chain. One row per statement file is enough: its account, its period, its printed opening and closing balances, and the sum of its Amount column after conversion.
account,file,start,end,opening,closing,rows_total
CHK-2290,2025-01.pdf,2025-01-01,2025-01-31,7410.00,8125.60,715.60
CHK-2290,2025-02.pdf,2025-02-01,2025-02-28,8125.60,7980.15,-145.45
CHK-2290,2025-03.pdf,2025-03-01,2025-03-31,7980.15,9402.88,1422.73
CHK-2290,2025-05.pdf,2025-05-01,2025-05-31,10115.30,9870.04,-245.26
CHK-2290,export-0520-0531.pdf,2025-05-20,2025-05-31,10402.11,9870.04,-532.07
CHK-2290,2025-06.pdf,2025-06-01,2025-06-30,9870.04,11250.00,1379.96
This is a fictional checking account for the first half of 2025. Two problems are planted. April was never downloaded. And someone added a custom export covering May 20 to 31 on top of the full May statement, so those twelve days are in the merged file twice.
The checker
import csv
import sys
from dataclasses import dataclass
from datetime import date, timedelta
from decimal import Decimal
from itertools import groupby
ONE_DAY = timedelta(days=1)
@dataclass
class Stmt:
account: str
file: str
start: date
end: date
opening: Decimal
closing: Decimal
rows_total: Decimal # sum of the Amount column for this file
def load(path):
with open(path, newline="") as f:
for r in csv.DictReader(f):
yield Stmt(
r["account"], r["file"],
date.fromisoformat(r["start"]), date.fromisoformat(r["end"]),
Decimal(r["opening"]), Decimal(r["closing"]), Decimal(r["rows_total"]),
)
def check_account(stmts):
s = sorted(stmts, key=lambda x: (x.start, x.end))
# 1. each file proves itself: opening + rows = closing
for x in s:
if x.opening + x.rows_total != x.closing:
yield f"{x.file}: rows miss its own closing by {x.opening + x.rows_total - x.closing}"
# 2. each join: dates touch and balances chain
for prev, cur in zip(s, s[1:]):
jump = cur.opening - prev.closing
pair = f"{prev.file} -> {cur.file}"
if cur.start > prev.end + ONE_DAY:
yield f"{pair}: GAP {prev.end + ONE_DAY} to {cur.start - ONE_DAY}, balance jump {jump}"
elif cur.start <= prev.end:
yield f"{pair}: OVERLAP {cur.start} to {min(prev.end, cur.end)}, balance jump {jump}"
elif jump != 0:
yield f"{pair}: dates touch but balance jumps {jump}"
# 3. the whole run: rows should add up to last closing less first opening
expected = s[-1].closing - s[0].opening
actual = sum((x.rows_total for x in s), Decimal("0"))
if actual != expected:
yield f"all files: rows add to {actual}, balances say {expected}, off by {expected - actual}"
if __name__ == "__main__":
rows = sorted(load(sys.argv[1]), key=lambda x: x.account)
for account, stmts in groupby(rows, key=lambda x: x.account):
problems = list(check_account(stmts))
print(f"{account}: {len(problems)} problems" if problems else f"{account}: ok")
for p in problems:
print(" " + p)
Cheapest checks first.
Each file against itself. If a converted file's rows don't take its opening balance to its closing balance, nothing downstream is worth checking. Fix the read first.
Each join. Sort by start date, then compare neighbours. The dates decide the label: a start more than one day after the previous end is a gap, a start on or before the previous end is an overlap. The balance jump is reported either way, because it's the number you'll go looking for.
The whole run. The Amount column of the merged file should add up to the last closing less the first opening. It's a couple of lines and it catches anything the joins missed.
Decimal, not float, everywhere. Bank balances are exact, and the comparisons should be too.
Running it
$ python3 chain.py statements.csv
CHK-2290: 3 problems
2025-03.pdf -> 2025-05.pdf: GAP 2025-04-01 to 2025-04-30, balance jump 712.42
2025-05.pdf -> export-0520-0531.pdf: OVERLAP 2025-05-20 to 2025-05-31, balance jump 532.07
all files: rows add to 2595.51, balances say 3840.00, off by 1244.49
Read it line by line.
The gap: March closes at 9,402.88 and May opens at 10,115.30. The missing April moved the balance up by 712.42. That's April's net change, and the date range tells you which PDF to go and get.
The overlap: the export opens at 10,402.11, the balance on May 19, while May closed at 9,870.04. The rows dated May 20 to 31 net to -532.07. They appear once in May's file and again in the export, and the export's opening sits 532.07 above May's close. Same number, opposite sign.
The whole run: the merged rows add to 2,595.51, but the balances say the account moved 3,840.00 (11,250.00 - 7,410.00). The difference, 1,244.49, is exactly the two jumps added together: 712.42 + 532.07. When the joins are clean, this line stays silent. When they aren't, its difference should equal the sum of the jumps, which makes it a self-check on the checker.
Drop the export and run again:
$ python3 chain.py statements.csv
CHK-2290: 2 problems
2025-03.pdf -> 2025-05.pdf: GAP 2025-04-01 to 2025-04-30, balance jump 712.42
all files: rows add to 3127.58, balances say 3840.00, off by 712.42
Only April is left, and the whole-run difference now equals the gap. Get April and the output is a single ok.
Edge cases worth handling
A gap with no jump. Some accounts have months with no activity. If a bank skipped a statement and the balance didn't move, the gap line shows a jump of 0.00. If the file is going to a lender or a court, still get the statement, or a letter saying none was issued.
Card statements. Billing cycles end mid-month, so "touching" means the next cycle starts the day after the last one closed, not on the 1st. The code works on dates rather than months, so it holds.
Several accounts. Group by account before chaining. Chaining checking into savings produces nonsense jumps. The groupby handles this as long as the account labels are consistent.
Interim statements. A bank-issued interim statement has real balances, so it chains into the month-end statement only if the month-end statement starts where the interim ends. Most don't. Treat it like a custom export and keep one or the other.
Where the per-file numbers come from
Disclosure: I'm Damin and I run ZeraBooks, so weigh this part accordingly. ZeraBooks Convert reads PDF bank and card statements and proves each one against its printed balances to the cent, which is the first layer above. You can tick several statements and download them together as one file, with an Account column when there's more than one account. Convert doesn't check one statement against the next or remove duplicate rows, so the other two layers are yours, by hand or with a script like this. It's part of the ZeraBooks subscription, 1,000 pages a month for $79 a month billed annually, after a 7-day trial.
The by-hand version, with a full year and a worked overlap, is in our guide to combining bank statements. Everything else Convert reads is on the bank statement converter page.
A merged file without the chain check is a guess with good formatting. Run it before the file leaves your hands.
Top comments (0)