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 you get from BankPDFtoXLS ends with a Ledger column. It checks each row against the row before it, and says one of four 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 we 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.unchecked: the row had a blank or unreadable cell that we could not work out from the rows around it, so we could not check it. Look here too.
A break marks one place to look. We start the check again from that row, so the rows after it are still checked. If your statement lists the newest first, we check it in whichever direction fits.
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.