Catch-up bookkeeping: getting 12 months of bank statements into QuickBooks or Xero and reconciled

Catch-up work goes wrong in the same places every time: a missing month nobody noticed, a CSV that QuickBooks rejects for being too big, a transfer booked twice, and a reconciliation that's off by an amount nobody can find. This is a process for getting a year (or three) of bank and card statements into QuickBooks or Xero and reconciled, in an order that catches those problems before they cost you an afternoon.

Step 1: build a statement inventory

Before you touch QuickBooks or Xero, list every account and every statement period in a spreadsheet: one row per account per month, with the statement's opening balance and closing balance copied from the first page.

Then check one thing: each month's opening balance must equal the previous month's closing balance. If it doesn't, a statement is missing or you have the wrong account. Finding that now takes a minute. Finding it during reconciliation in month nine takes much longer.

This sheet also tells you the client's real opening balance for the first month you're catching up, which is the number QuickBooks or Xero needs to start from. Card balances go in as negatives (money owed), which is also how Xero expects them when you reconcile a period.

Step 2: get the transactions out (downloads first, PDFs second)

• Bank downloads are the cleanest source. Most banks let you download CSV, QFX or QBO files for recent activity. How far back varies by bank, so check each one.
• Bank feeds only go back so far. When you connect an account in QuickBooks Online, it pulls roughly the last 90 days. Anything older has to be uploaded, and Intuit warns that uploading dates the feed already covers creates duplicates. Upload only the period before the feed's first transaction.
• Older months, closed accounts and client-emailed statements are PDFs. QuickBooks Online can read a PDF statement itself (Upload from file, with a review screen), Xero can import some PDFs in the US and Canada, and QuickBooks Desktop can't read PDFs at all. For a year of statements across several accounts, converting each PDF to a file you've already checked (next step) is usually faster than reviewing AI extraction row by row.

Step 3: tie out every month before you import

This is the step people skip, and it's the one that saves the most time. For each statement file, with a single signed Amount column (money in positive, money out negative):

Opening balance + SUM(Amount) = Closing balance

In Excel, with amounts in column C and the statement's opening and closing balances in F1 and F2, =ROUND(F1+SUM(C:C)-F2,2) must return 0. Mark the month "Tied out" in your inventory only when it does. A month that ties out before import can only fail reconciliation for reasons inside the books (a transaction entered by hand, a deleted line), never because the import was incomplete. That cuts the search space in half.

If you're converting PDFs, use a method that does this for you or do it yourself every time. PDF tools commonly drop the row at a page break, read a running-balance column as an amount, or miss a separate Checks Paid section.

Step 4: import in batches the software accepts

QuickBooks Online

• Bank transactions, then Link account ▾, then Upload from file.
• Transaction files (.qbo, .qfx, .ofx, .csv, .txt) must be 350 KB or less, with up to 1,000 lines per upload. A year of a busy checking account will exceed that, so upload a month or a quarter at a time. That also keeps each batch matched to a statement you've tied out.
• CSV needs 3 columns (Date, Description, Amount) or 4 (Date, Description, Credit, Debit), one date format throughout, and blank cells instead of zeros.
• Don't upload QIF. QuickBooks Online doesn't accept it.

QuickBooks Desktop

• Supported versions can import .QBO (Web Connect) files through File, then Utilities, then Import, then Web Connect Files.
• Desktop 2023 lost bank feeds and Web Connect uploads after May 31, 2026. Use IIF instead (File, then Utilities, then Import, then IIF Files). IIF posts straight to the register, with no bank feed review step, so import only periods you haven't entered. Our Desktop guide walks through it.

Xero

• Open the bank account, then Import a statement. Xero recommends OFX or QIF where you can get them, because they need no column mapping.
• CSV needs Date and a single Amount column (income positive, expenses negative). Delete balance columns, the account number and empty rows.
• Xero asks for files of more than 1,000 transactions to be split into smaller ones, and it flags lines matching ones already imported as duplicates on the review screen. Read that screen before you confirm.

Not sure which format to pick? See QBO vs IIF vs QIF.

Step 5: categorize with rules, not row by row

• Set up bank rules first. Payroll, rent, the software subscriptions and the merchant deposits repeat every month. A rule written once categorizes all twelve months of them.
• Work by payee, not by date. Sort the review list by description and handle every row for one vendor at once.
• Match transfers, don't categorize them. A payment from checking to the Amex shows up in both accounts. Record it once as a transfer (or a credit card payment) and match the other side to it. If you categorize both sides as expenses, the books overstate spending and the card won't reconcile.
• Park the unknowns. Put anything you can't identify in an "Ask client" holding account and send the client one list at the end instead of twenty questions along the way.

Step 6: reconcile oldest first

Both QuickBooks and Xero want statements reconciled in date order, because each month's starting point is the previous month's result.

QuickBooks Online

1. Gear icon, then Reconcile, then pick the account.
2. Check the Beginning balance against the statement. For the first month, it comes from the opening balance you set up. If it's wrong there, fix the opening balance entry before going further.
3. Enter the statement's ending balance and ending date. Check that the Last statement ending date is the day before this statement starts.
4. Tick each transaction that appears on the statement. The difference should reach 0.00. Then Finish now, and move to the next month.

Xero

1. Reconcile every statement line for the period on the account's Reconcile tab.
2. Open the Reconcile period tab, create a period, and enter the statement's date range and opening and closing balances (negative for credit cards).
3. When the balances match, Close period. Xero fills in the next period's start date and opening balance for you, which is another reason to go in order.

When a month won't reconcile

If the month tied out in Step 3, the import is complete, so the problem is inside the books. The size of the difference usually tells you what kind:

Resist booking a reconciliation adjustment to make a month close. In catch-up work it almost always hides a missing transaction that resurfaces later.

FAQ

Related guides

• How to import bank transactions into QuickBooks Desktop after bank feeds stopped (2026)
• How to convert a credit card statement PDF to Excel
• QBO vs IIF vs QIF: which file QuickBooks and Quicken actually need