Bank reconciliation

Every operating account, every entity, has to reconcile the accounting register against the bank statement every month. By hand it is tedious and easy to fumble, and the one question actually worth asking (why book and bank disagree) gets buried under the matching.

How it works

Two files go in. The QuickBooks register export is a binary .xls I read with xlrd. The bank download is a CSV or a Quicken .dat. I set the statement cutoff, take the register's running balance at that date as the book balance, and compare it to the bank's ending balance. To explain the gap the script does a two-way greedy match of bank lines against book lines by exact amount inside a date window (checks clear on a lag), then verifies the residual by check number.

• Outstanding checks make bank higher than book. Deposits in transit make bank lower than book. The sign tells me which side to go look at.
• A downloaded file can quietly belong to a different entity, so I check the payer name before trusting that two files even pair up.
• A roll-forward check (opening gap plus or minus the period deltas equals closing gap) catches anything the line-matching missed.

It runs whenever a statement lands and writes a markdown working paper. Docyt auto-imports transactions, so balances often already tie. The script's real job is to prove it and surface the stale items, like a check that never cleared months later.

This is the whole thesis in miniature. The reconciliation is not the work. The exceptions are. The script does the matching so the only thing that reaches me is the short list of things that genuinely do not line up.