Detecting gaps and overlaps when merging monthly bank statements
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…
When merging monthly bank statements, it is crucial to examine the balances as a sequence, ensuring each link is tested. Each statement file's opening balance added to its rows should equal its closing balance, while the opening balance of one file must match the closing balance of the preceding file. Additionally, the dates must align without any overlaps.
The dates reveal the type of interruption present, and the disparity in the balance jump indicates the amount of money involved. Damin, the founder of ZeraBooks, which transforms statement PDFs into Excel and CSV files, outlined this process in a simple sketch, emphasizing the minimal code required. The data model does not necessitate transactions to operate the chain; one row per statement file suffices, containing the account, the period, the printed opening and closing balances, and the sum of the Amount column after conversion.
The example provided is a fictional checking account for the first half of 2025. Two issues are introduced: April was not downloaded, and a custom export for May 20 to 31 was added on top of the full May statement, causing those twelve days to appear twice in the merged file. The checker imports the necessary modules, including csv, sys, dataclasses, datetime, Decimal, and itertools.
The code defines a Stmt class with account, file, start, end, opening, closing, and rows_total attributes. The load function reads the CSV file and yields Stmt objects. The check_account function sorts the statements by start and end dates and performs three checks: each file proves itself, each join verifies the dates touch and balances chain, and the whole run ensures the rows add up to the last closing minus the first opening.
Written by urgent.news from Dev.to's reporting — not their text. Machine-written — may contain errors; check the original before relying on it.
