How to compare bank statements with QuickBooks
Reconciled does not always mean right. A messy file can show a clean reconciliation and still hold duplicates, missing lines and entries posted to the wrong month. To find them, go back to the bank's own record and compare it line by line with the register.
Export the register
- In QuickBooks Online, run Transaction Detail by Account for the bank account and the period you need.
- Export it to Excel or CSV. Keep the date, name, memo and amount columns.
- Export one bank account at a time, or choose the account in Tickproof if the file holds several.
Prepare the statements
Download PDFs from the bank rather than scanning paper. Downloaded PDFs hold real text, which can be read exactly. Keep them in date order so gaps stand out.
Compare the two
Match on amount first, because the amount is what the bank and the books are most likely to share. Allow a few days of difference, since cheques and card payments settle later than they were entered. Whatever does not pair up is your work list.
- On the statement only: the entry is missing from the books.
- In the books only: it may be a duplicate, a void that was never removed, or a cheque that has not cleared.
- Same payee, different amount: check for swapped digits. A difference divisible by 9 is the classic sign.
Fix in date order
Work from the oldest difference forward. A fix to an old period changes the opening balance of every later one, so tell the client or their accountant before you change a closed period.