Reconciling a bank statement is mostly an extraction problem, and most of the effort goes into the one part a machine should be doing.
The pattern is always the same. A bank hands you a PDF, someone reads it carefully, and two hundred rows get retyped into a spreadsheet by hand. A digit slips. A date shifts. The closing balance no longer matches the figure the bank printed, and now somebody is spending a week working backwards through the month to find out why.
None of that is arithmetic. It is transcription.
Two jobs, not one
The fix people reach for is to stop checking. That is the wrong half to drop.
Extracting rows and verifying them are separate problems, and they are solved by different means. Once you accept that split, the rest is straightforward:
- Extraction is a structural problem. Can a tool recover the transaction table from this particular file?
- Verification is arithmetic. Does the last extracted row agree with the closing balance the bank printed?
Keep the second one and you keep your accounting integrity. Automate the first one and you get the time back.
The input decides the ceiling
A PDF exported from online banking has a real text layer laid out as a table. A converter reads structure rather than guessing, and most of the rows come back clean.
A scan of a scan has no text layer at all. Everything depends on optical recognition, and a tool that reports a confidence value is telling you something useful: a low value is a prompt to look, not a detail to ignore.
A phone photo sits between the two. Lay the page flat, keep the camera parallel to it, and keep all four corners in frame. Perspective and shadow cluster their errors near the row boundaries, which is exactly where a decimal point goes missing.
If your tool offers a confidence score, treat a low one as a work item. If it does not, that is a reason to be more careful, not less.
Choosing the export
The format should follow what happens next:
# When a person will read the file, Excel keeps the columns and number formats.
export = read_statement_pdf("statement.pdf", fmt="xlsx")
# When it feeds a script or an accounting import, CSV needs no configuration.
rows = read_statement_pdf("statement.pdf", fmt="csv")
# When rows merge with another source, JSON keeps the numeric types intact.
merged = merge(read_statement_pdf("statement.pdf", fmt="json"), ledger)
The download shape matters just as much. Separate files keep each account distinct. A ZIP keeps a month together without merging anything. One merged export is the fastest route to a period summary, and it loses the account boundary unless you add a source-file column before you merge.
Keep the columns you will check against
At minimum: transaction date, description, debit, credit, and a running or closing balance. Add currency and account identifier, because a single workbook that quietly mixes two accounts is worse than no workbook at all.
If the statement prints a running balance, keep that column. It turns verification from a judgement into arithmetic.
The verification step
Do not sort. Do not tidy. Take the last exported row and compare it against the printed closing balance.
If they agree to the cent, the table is very likely right, and the rest of the month is worth processing. If they do not, find the difference before doing anything else, because every total downstream inherits that error.
The causes are worth knowing in advance:
- a duplicated transaction where the statement wraps across a page boundary
- a multi-line description split into two rows
- a currency conversion read from the wrong column
- a bank fee or interest line sitting in a separate summary block rather than inside the transaction table
Spot-checking a handful of rows is enough: the largest amount, the earliest date, the one nearest the balance, and anything the tool flagged as uncertain. You are not redoing the extraction, you are confirming the machine read the page the way you did.
Categorising without over-engineering
Descriptions are rarely consistent across a month. The same merchant appears as an abbreviation on one statement and a trading name on another, sometimes with a terminal code attached.
Work the largest, least ambiguous lines first, derive the rule, apply it to the rest. Leave the lines you cannot classify visible, because an unclassified row is information: a new subscription, a one-off charge, a bank fee, or a row the tool misread.
For a single-file check before you commit a month, there is a browser-based converter that takes a statement PDF or photo and returns Excel, CSV or JSON: https://bank-statement-converter.online/
When not to automate
Old archival formats, layouts that deliberately depart from the norm, and pages shot at an angle severe enough that a line-by-line check is cheaper than a second extraction attempt. If the balance does not reconcile after two careful passes, manual entry is the honest answer.
Record which files needed it. That list tells you which sources are worth re-downloading in a better format next quarter, which is more useful than pretending the automation covers everything.
The habit beats the tool
Pick a recurring slot. Process every account for the period in one sitting. Reconcile each account before moving on. Leave anything unresolved visible rather than deferring it, because deferred exceptions are what turn a two-hour task into an afternoon.
The tooling is the easy part. Transcription is the expensive part, and it is the part that should not have been manual in the first place.
Top comments (0)