How to check a converted bank statement is complete

Whatever turned your PDF statement into a spreadsheet – a converter, OCR or copy and paste – it can drop a line at a page break, misread a 3 as an 8, or flip a payment into a deposit. The statement itself contains everything you need to catch that. These four checks take a few minutes in Excel and work with any tool.

Check 1: opening + transactions = closing

Every statement prints an opening balance and a closing balance. Add up the converted transactions:

Opening balance + total money in − total money out = closing balance

If you have money in and money out in columns D and E, and the opening balance in a cell called Opening:

=ROUND(Opening + SUM(D:D) - SUM(E:E), 2)

This must equal the printed closing balance to the cent. A difference means a transaction is missing, extra, misread or has the wrong sign. If the difference is exactly the amount of one transaction, look for a missing or duplicated line; if it is twice an amount, that transaction probably has the wrong sign. Many statements also print total money in and total money out for the period – compare those too, they narrow the problem down to one side.

Check 2: the running balance, line by line

Check 1 tells you whether something is wrong. If the statement prints a balance next to each transaction, this check tells you where. Say the columns are:

Column	Holds
D	Money in
E	Money out
F	Balance printed on the statement
G	Our running balance (new)
H	Difference (new)

Put the opening balance in G1, then in row 2 and filled down:

G2: =G1 + D2 - E2
H2: =IF(F2="", "", ROUND(G2 - F2, 2))

Filter column H for anything that isn't 0. The first non-zero row is where the problem is – the line itself or one just above it. Rows after it will keep showing the same difference until the error is fixed, so you only ever need to look at the first one.

Some banks print a balance only once per day, not on every line. Leave F empty on the other rows; the formula skips them.

Check 3: statement to statement

When you convert several months for the same account, put the statements in date order and check that each closing balance equals the next statement's opening balance. If one doesn't match and there is a gap between the two periods, a statement is missing. If the periods overlap instead, some transactions appear in both – keep them only once, or they will be imported twice.

Check 4: dates

Day and month. 06/12 is 6 December in the UK and June 12 in the US. Sort by date: if transactions jump between months out of order, they were read the wrong way round.
The year. Statements that run from December into January often print no year on each line. Check that the January transactions got the new year.
Wrapped descriptions. A description that wraps onto a second line can become a separate "transaction" with no date or amount. Filter for rows with an empty amount.

Or let BalancedProof do it

BalancedProof runs checks 1 to 3 on every statement you convert: it shows whether the statement reconciles, highlights the rows where the running balance stops matching, shows that exact line from the original page, and suggests the correction that makes the balances work. With a year of statements it also checks each month against the next and keeps overlapping transactions only once. It shows how it read the dates and warns you when day and month can't be told apart. The Excel export includes a reconciliation sheet, so you can show your work.