Skip to content
BookkeepKit

Bank Reconciliation: Match Your Statement to Your Books

Drop the bank statement and the register from your books. Transactions are matched for you, and what's left is sorted into outstanding items and items still to record, with the reconciliation worked out to the cent.

1. Bank statement

PDF, CSV, Excel, QBO, QFX or OFX from the bank.

Processed in your browser. Your files never leave your computer.

2. Your books

The account register or transaction report exported from QuickBooks, Xero or a spreadsheet.

Processed in your browser. Your files never leave your computer.

Need the statement as a file your books can import first? Import from bank statement.

How it works

  1. 1

    Add both files

    The statement (PDF, CSV, Excel, QBO, QFX or OFX) and your books' register or transaction report for that account (CSV or Excel).

  2. 2

    Check the balances

    The statement's ending balance is filled in when the file has it. Enter your books' ending balance for the same date.

  3. 3

    Clear the difference

    Review outstanding checks, deposits in transit and bank-only items until the difference is zero, then download the report.

What a bank reconciliation proves

A reconciliation shows that your books and the bank agree on how much money is in the account, and explains every reason they don't on a given day. Two balances are adjusted until they meet:

  • Adjusted bank balance = statement ending balance + deposits in transit − outstanding checks.
  • Adjusted book balance = books ending balance ± items on the statement you haven't recorded yet (bank fees, interest, automatic payments).

When the two adjusted balances are equal, the account is reconciled. The bank-only items then become entries to post in your books, and the outstanding items are what to watch for on next month's statement.

Getting the right files

The bank statement

Use the statement for the month you're reconciling. A PDF works directly; a CSV, Excel or QBO download from online banking works too, as long as it covers the same dates. When the file includes the ending balance (PDF statements and QBO/OFX downloads usually do), it's filled in for you.

Your books

  • QuickBooks Online: open the account's register, or run a Transaction Detail by Account report for the bank account, filtered to the statement period, and export it to Excel.
  • QuickBooks Desktop: run a Transaction Detail or the account's register report for the period and export it to Excel or CSV.
  • Xero: export the Account Transactions report for the bank account.
  • A spreadsheet: any file with a date, a description and an amount (or debit and credit) column.

Working through the difference

  1. Start with the two lists of unmatched items. Most are genuine: a deposit on the 31st, a check written last week, the monthly service fee.
  2. Look for pairs: an unmatched book item and an unmatched bank item with similar amounts, such as $45.10 and $41.50. That's a typo in the books.
  3. If the same item appears twice on the books side, it was probably entered twice, or imported and also entered by hand.
  4. If dates are the issue (checks clearing weeks later), widen the matching window.
  5. When the difference is zero, download the Excel or PDF report and keep it with the month's records.

Privacy

Both files are read in your browser and matched there. Neither the statement nor your ledger is uploaded, and nothing is kept after you close the page.

Frequently asked questions

How are transactions matched?

Only when the amounts are identical to the cent. If both sides have a check number, the numbers must match too, whatever the dates. Otherwise the dates must fall within the window you choose (4 days by default), and when several transactions qualify, the closest date and most similar description win. Each transaction is matched once at most.

What is a deposit in transit?

Money you recorded in your books that the bank hasn't posted yet, typically a deposit made on the last day of the month. It should appear on the next statement.

What is an outstanding check?

A check or payment you recorded that hasn't cleared the bank yet. Checks outstanding for months may have been lost or never cashed and need following up.

My books file has deposits as negative numbers. Is that a problem?

No. Some reports export a bank account's activity with the opposite sign. The tool tests both conventions and flips the signs when that matches far more transactions, and tells you it did.

Why doesn't the difference reach zero?

Usually one of four things: the two ending balances aren't for the same date, a transaction was recorded with the wrong amount (look for an unmatched item on each side that differs by a small amount or by transposed digits), a transaction is in the books twice, or the books' starting balance was never reconciled.

Is this the same as reconciling in QuickBooks?

It's the same method, done outside your books so you can see the whole picture before marking anything as cleared. After it balances, use the report to tick the same items in your accounting software's reconcile screen.

Guides

Related tools