Every bank statement follows one rule. Each balance is the balance before it, plus the money in, minus the money out. When a statement becomes a spreadsheet, that rule is the fastest way to see whether the conversion is right.
The rule, row by row
| Date | Description | Money in | Money out | Balance |
|---|---|---|---|---|
| 01 Mar | Balance brought forward | 1,200.00 | ||
| 02 Mar | Salary | 2,000.00 | 3,200.00 | |
| 03 Mar | Rent | 950.00 | 2,250.00 |
The rows above are an example, not a real statement.
If a row breaks the rule, something went wrong: in the statement, or more often in the conversion.
Why a conversion breaks it
- An amount in the wrong column. A general PDF converter places each word by where it sits on the page. When a bank prints money in and money out close together, an amount can land in the wrong column, and every balance after it looks wrong.
- A description over two lines. Some banks print a long description on two or three lines. A converter that reads line by line turns the extra lines into rows with no amount.
- Balances left blank. HSBC prints a balance once for each day, and Virgin Money leaves it blank on most rows. Those rows have no balance of their own to check.
- Newest first. Some statements, Monzo's for example, list the most recent transaction first, so the rule runs from the bottom of the page up.
What the Ledger column tells you
Every spreadsheet from BankPDFtoXLS ends with a Ledger column. It checks each row against the row before it, and says one of three things:
ok: the balance on the statement equals the balance before it, plus the money in, minus the money out.filled: the row had a blank or unreadable cell, so the converter worked it out from the rows around it.break: no amount on the row gets from the balance before it to this one. Look here.
A break marks one place to look. The check starts again from that row, so the rows after it are still checked. A statement listed newest first is checked in whichever direction fits it.
When you find a break
- Open the PDF at that row.
- Compare its amounts and its balance with the spreadsheet.
- Look for a misread digit, which happens on scans, or an amount in the wrong column.
- If the PDF shows the same numbers, the statement itself does not add up at that row, which is a question for your bank.