How to check a bank export before you import it

Satwinder Singh · 28 October 2026 · 5 min read

Accounting software imports whatever you give it. If a row is missing, doubled or has the wrong sign, the import still succeeds. The problem shows up weeks later as a reconciliation that's off by an amount nobody recognises.

The good news: a bank statement already prints everything you need to check an export. Three checks, done in order, catch most problems before they reach your books.

1. Opening balance plus every row equals the closing balance

This is the one check worth doing every time. Take the statement's opening balance, add every amount in your file (money in as positive, money out as negative), and compare the result with the closing balance printed on the statement.

opening balance + sum(all amounts) = closing balance

In a spreadsheet, if the amounts are in column C and the opening balance is in F1:

=F1 + SUM(C2:C500)

If the result matches the closing balance to the penny, every row is very likely present and signed correctly. If it doesn't, the size of the difference is a strong clue:

• Off by exactly one transaction's amount: that row is missing, or appears twice.
• Off by twice a transaction's amount: that row has the wrong sign (a payment recorded as a deposit, or the reverse).
• Off by a multiple of 9 (counting in pennies): two neighbouring digits are probably swapped. 12.91 read as 12.19 is off by 0.72, which is 72 pennies, or 8 × 9.
• Off by a lot, with no obvious pattern: a whole page or date range is missing. Check the dates at page breaks first.

2. Compare the totals for money in and money out

Many statements also print total credits and total debits for the period. Sum the positive amounts and the negative amounts separately and compare each with the printed totals.

This catches one case the first check can't: two errors that cancel out. A missing 50.00 deposit and a missing 50.00 payment leave the closing balance correct, but both totals will be off by 50.00.

One caution: some banks leave reversed or offsetting entries out of these totals, or count them on both sides. If the closing balance matches and both totals are off by the same amount, look for a reversal pair before assuming a row is wrong.

3. Follow the running balance to find the exact row

When the statement prints a balance after each transaction, you can find the exact row where things go wrong. For each row:

previous balance + this row's amount = this row's balance

Add a helper column that calculates this and flags any row where it doesn't hold. The first flagged row is where to look. Usually the row just before it is missing, or the flagged row has the wrong amount or sign.

Some banks print the balance only once per day, at the last transaction of the day. In that case, add up all of the day's amounts and compare at the rows that show a balance.

Small things that cause big differences

• Dates read the wrong way round. 03/04 is 3 April in the UK and March 4 in the US. A file read in the wrong order puts transactions in the wrong month, which breaks month-end reconciliations even though the year total is right.
• Amounts in brackets or with a trailing minus. (54.20) and 54.20- both mean negative. A careless import reads them as positive.
• Separate debit and credit columns. If the file has both, make sure the import maps each one, or combines them into one signed amount first.
• Pending transactions. Exports taken from online banking can include pending items that never appear on the final statement.

Doing it automatically

These checks take a few minutes in a spreadsheet. If you'd rather not build the formulas each time, I built a free bank CSV checker that runs the balance checks on a CSV or QBO file and points to the rows that don't add up. Either way, the rule is the same: don't import a file you haven't checked against the closing balance.