Bank statements into Excel, without the mess
Bank statements are the worst PDFs to work with: multi-page, no consistent grid, amounts with thousands separators. Here is a workflow that produces something usable.
Why statements are harder than invoices
An invoice is one page with a known shape. A statement is a long table where the columns drift between pages, dates repeat per line, and the running balance interrupts the flow. Text extraction gives you a wall of words in roughly the right order — and no reliable way to know which number was a debit.
Extract, then rebuild
Two phases, and do not skip the first:
- Extract the text and read it. You are looking for whether the statement has a text layer at all — many bank PDFs do, which makes this much easier than it looks.
- Rebuild rows from the text using the date as an anchor. Every transaction line starts with a date, so split on that pattern rather than on columns.
A date anchor is crude but robust. It survives column drift, and it fails loudly (a row without a date) rather than silently mis-assigning amounts.
The debit/credit problem
Statements rarely say "debit". They show a negative sign, brackets, or two separate columns where one is blank. If your extraction flattens columns, you lose the distinction between £40 in and £40 out — a mistake that changes the sign of your whole reconciliation.
Practical rules, in order of reliability:
- Parentheses or a minus sign on the amount → negative.
- Two amount columns with one empty → whichever column has the number tells you the direction.
- Only one amount column and a running balance → recompute the delta between consecutive balances. This is the most reliable method when the balance column exists.
Validate before you trust it
Three checks catch almost every extraction error:
- Row count. Does it match the statement's summary ("42 transactions")?
- Sum. Do your amounts add up to the closing balance minus the opening balance?
- Largest and smallest. Sort by amount; outliers are usually parsing artefacts.
If the sum does not match, do not start fixing individual rows. The error is almost always structural — a missed page, a merged column, or a header row being read as data.
Privacy, specifically for statements
A bank statement contains your account number, your address, your salary and every merchant you use. It is the single document you should be most careful about uploading. If a converter sends it to a server, you are trusting that server's retention policy, its backups, and its employees.
Processing locally avoids the question entirely: render the PDF, read the text, build the table — all in the tab. Slower, but nothing to leak.
Common questions
Can Excel open a statement PDF directly?
No. Excel's PDF import only works for well-structured tables and usually mangles statements. Extracting to text first, then rebuilding rows, is more reliable.
How do I handle multiple pages?
Process page by page into the same table, then de-duplicate on date plus amount plus description — banks often repeat the last line of a page at the top of the next.
What if the PDF is a photo scan?
Then it needs OCR, and accuracy drops. Check the closing balance against the statement's own summary before using any of the numbers.