Does your converted bank statement balance?

What is the one check that tells me if a conversion is right?

Opening balance + money in − money out = closing balance. A statement is a record of money moving between two balances, so the rows must explain the whole trip from the first balance to the last. If your sheet is missing a row, has an extra one, or has a wrong amount, the sum will not land on the closing balance.

You do not need to read every row to run this check. You need two numbers from the PDF (the opening balance and the closing balance) and the two amount columns in your sheet. If the sum matches, the amounts add up.

If it does not, you know the sheet and the PDF disagree before you import anything into your accounting software. If you have not converted yet, see why statement PDFs come out messy in Excel for what usually goes wrong.

What does the check look like on a small example?

Here is a tiny statement with five transactions. Every figure is made up. The opening balance is 1,000.00 and the closing balance on the PDF is 2,098.30.

Money in adds up to 2,025.00. Money out adds up to 926.70. So 1,000.00 + 2,025.00 − 926.70 = 2,098.30, which is the closing balance. This one balances.

What Excel formula can I paste to do it?

Put the opening balance in cell E2, the transactions in rows 3 to 7, money in in column C, money out in column D, and the balance printed on the statement in column E. Then paste this into any empty cell:

=ROUND(E2+SUM(C3:C7)-SUM(D3:D7)-E7,2)

The answer is the gap. Zero means the sheet balances.

Rounding to two places stops tiny computer rounding noise from showing up as a fake difference. Change the row numbers to match your own data. Other spreadsheet apps accept the same formula.

How do I find the exact row that is wrong?

Test the running balance one row at a time. Each balance should equal the balance above it, plus money in, minus money out. In cell F3 paste the formula below and fill it down:

=ROUND(E2+C3-D3-E3,2)

Empty cells count as zero, so rows with only money in or only money out work as they are. Every row should show 0. The first row that does not is where the sheet and the PDF disagree. Rows after it will often show the same gap, because the balance carried forward is already off.

If your statement does not print a balance on every row, skip this and use the single formula above. You can still narrow things down by checking the balance printed at the end of each page.

What does the size of the gap tell me?

The gap is a clue. Three patterns cover most failures, and each points to a different kind of mistake.

• Off by exactly one amount: a row is missing or has been counted twice. Look for an amount in the PDF that is not in the sheet, or the same amount listed twice. In the example above, a gap of 64.30 would mean the grocer row is missing.
• Off by twice an amount: a sign flip. A payment out was read as money in, or the other way round. A refund of 25.00 placed in the wrong column gives a gap of 50.00.
• Off by ten or a hundred times an amount: a decimal slip. If 112.40 was read as 1,124.00, the gap is 1,011.60: nine times the real amount. A slip of two places gives ninety-nine times.

For a suspected sign flip, divide the gap by two and look for that amount in your statement. If one row matches, it is probably the culprit. If no pattern fits, more than one thing is wrong, so go back to the row-by-row check.

What do Verified and Check mean in FastOCR?

FastOCR runs this same test for you. The bank statement converter shows Reconciled when opening balance + money in − money out equals the closing balance. It shows Not reconciled, with the difference, when it does not. It shows Could not be checked when the opening or closing balance was not found.

Each row also gets a status, and the results page says how many rows are verified and how many are left to check. A row is Verified only when the balances tie. That is strict on purpose.

One real statement was read with every row right, but its printed totals did not match, so it stayed on Check. That was the correct answer, because the paperwork itself disagreed with itself. Dates and descriptions are not part of this check, so skim them against the PDF.

What should I do when the numbers do not match?

Work from the biggest clue to the smallest. First read the gap with the three patterns above.

Second, run the row-by-row formula and find the first row that breaks. Third, open the PDF at that row and compare the date, the amount and the column it sits in. Fix it in the sheet, and test again.

If the statement was scanned or photographed, expect to do this more often, because a scan has to be read with OCR first and a misread digit is possible. Not sure which you have? Try the 10-second scanned-or-digital test.

A statement downloaded from your bank is easier, since its figures can be read exactly. Either way, finish with the balance check before you import into QuickBooks Online or any other software.

When is this check not enough?

It cannot catch two mistakes that cancel each other out, such as two amounts swapped between rows. It also says nothing about dates, descriptions or which account a row belongs to. For those, read a handful of rows against the PDF. And if your bank offers a CSV download for the dates you need, use that instead of converting.