How to convert a year of bank statements at once – and find the missing months
Catch-up work usually starts with a folder: a client's bank statements for the year, as PDFs or scans, named however the bank or the client felt like naming them. Converting them is only half the job. Before anything goes into the books you need to know that every month is there, that nothing is in twice, and that the twelve files really add up to one continuous year.
What can go wrong across a year of statements
- A month is missing. The client forgot one, or the download failed. Twelve files can still be eleven months if one is a duplicate.
- The same statement twice. "March.pdf" and "March (1).pdf", or the same file renamed. Import both and every transaction that month is doubled.
- Overlapping periods. Some banks cut statements mid-month, or the client sends an interim statement as well as the monthly one. The overlapping transactions appear in both files.
- Two accounts mixed together. A savings account statement in the current account folder.
- A transaction misread or dropped inside one of the statements – see how to check a single statement.
The check that catches all of it: closing balance = next opening balance
Put the statements for each account in date order and compare each statement's closing balance with the next one's opening balance. They must be equal to the cent.
- Equal – the two statements connect. Nothing is missing between them.
- Not equal, and there are weeks with no transactions between them – a statement is almost certainly missing.
- Not equal, but the dates run on – a transaction is missing or misread at the end of one statement or the start of the next, or the bank applied something between statements (interest, a fee) that isn't printed.
- The second statement starts before the first one ends – the periods overlap; keep the shared transactions only once.
Do this for every pair and the year either connects from the first opening balance to the last closing balance, or you know exactly where it doesn't.
Doing it in one go with BalancedProof
Drop the whole folder on the converter – PDFs, scans and phone photos together. Each statement is read and checked against its own printed balances, then the statements are grouped by account (using the account number printed on them) and put in date order. You get a table of statements and a list of checks between them:
| You see | When |
|---|---|
| Closing = next opening | The two statements connect. |
| Likely a missing statement | More than 10 days with no transactions between them, and the balances don't connect. The message gives the dates and both balances. |
| Balances don't connect | The dates run on but the balances differ – the message gives the difference. |
| Periods overlap | Transactions that appear in both statements are counted once; the copy is left out of your import files. |
| Same file uploaded twice | Detected by the file's content, even if it was renamed – the copy is ignored. |
Statements without a readable account number are checked on their own rather than chained to the wrong account. Click any statement to review its rows; rows where the running balance stops matching are shown next to the original line.
Then export everything at once:
- One Excel workbook with a Statements sheet, a Continuity sheet, all transactions, and the rows that still need review – the year on one file.
- Import files for QuickBooks, Xero, Sage, Wave, OFX or .qbo, one per account. Overlapping transactions are only included once, and files are split to stay within QuickBooks Online's 1,000-transaction upload limit.
Up to 30 statements can be processing at once from one network, so a year of monthly statements goes in one go; two or more years go in two batches.
What a year costs
The first 5 pages a day are free, without signing up. A year of monthly statements at two to six pages each is roughly 25 to 75 pages, so a 200-page pack ($9) usually covers it – a one-time payment, valid for 12 months, no subscription.