Blog

Why a Converted Bank Statement Does Not Balance

Each balance should equal the one before it, plus money in, minus money out. Why converted statements break that rule, and how to find the row.

, updated

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

  1. Open the PDF at that row.
  2. Compare its amounts and its balance with the spreadsheet.
  3. Look for a misread digit, which happens on scans, or an amount in the wrong column.
  4. If the PDF shows the same numbers, the statement itself does not add up at that row, which is a question for your bank.

Convert your own statement.

We read every row, check every balance, and give you a file for your spreadsheet or your books.

Start your free trial

15 pages free, up to 5 for each statement. No card.

See plans and prices