Catch-up bookkeeping — turning a year of PDF bank statements into a clean ledger

The job arrives the same way every time: a new client, a folder (or a shoebox) of monthly PDF statements, no bank feed, and a deadline. Retyping 12 statements is a week of work and a guaranteed source of errors. Here is the workflow we recommend; it scales from one account to twenty.

1. Collect and name the files

Ask for the official PDF statements for every account — current, savings, credit card, loan, PayPal/Stripe if relevant — for the full period plus one month either side (you will need the opening balance of month 1 and the closing balance of month 12).

Name them consistently: Client_Bank_Account_YYYY-MM.pdf. It sounds trivial; it is what makes the next steps mechanical.

2. Convert each statement — and check each one reconciles

Upload each PDF to Rowvert (the Professional plan covers 1,200 pages a month; a typical year of personal statements is 40–80 pages). For each conversion:

• Look at the Reconciled pill. Green means opening + credits − debits = closing for that statement.
• If rows are flagged, compare them with the PDF, then either correct the source (a bad scan) or exclude the row before downloading.
• Download Excel for your working file, and the QuickBooks or Xero CSV for the import later.

Twelve reconciled statements chained together mean the whole year reconciles: month 1's closing balance is month 2's opening balance, and so on. Check that chain once in step 4.

3. Merge into one workbook

Create Client_Bank_Account_2025.xlsx with one sheet per month (paste each Transactions sheet) and a Year sheet that stacks them. In Excel 365, Power Query does this in two clicks (Data → Get Data → From Folder, combine). Add columns:

• Account — so several accounts can be stacked later.
• Category — empty for now.
• Notes — for queries to the client.

4. Check the chain

On the Year sheet: =opening balance of Jan + SUM(Credit) − SUM(Debit) must equal the December closing balance. Then confirm each month's closing equals the next month's opening. If a statement is missing (skipped month, replaced card), this is where it shows up — before you have categorised anything.

5. Categorise fast

• Sort by Description. Recurring payees (rent, payroll, software, utilities) cluster together; categorise each cluster once with a fill-down.
• Build a small rules table (payee contains "AMAZON" → Office supplies; "HMRC" → Tax) and use XLOOKUP with wildcards or Power Query merge to apply it. The rules carry over to next year.
• Sort by Amount descending and review the top 30 individually — that is where the material items live.
• Everything left uncategorised goes into a query list for the client, with dates and amounts, in one email rather than twelve.

6. Import into the ledger

Import the CSVs month by month into QuickBooks Online or Xero. Because each file already reconciled to its statement, the bank balance in the software will match the paper at each month end; if it doesn't, the discrepancy is in the import (usually a date-format choice), not in the data.

Where the client's bank feed does exist for recent months, import only the older months from CSV and let the feed cover the rest; overlapping periods create duplicates to weed out.

7. Keep the evidence

File the original PDFs, the converted Excel files and the Summary sheets (which record the reconciliation result and the flagged rows) with the working papers. If a figure is ever questioned, you can show the statement, the extracted row and the check that passed.

Time budget

For a single current account with ~60 transactions a month: conversion and checks about 20 minutes for the year; merge and chain check 15 minutes; categorisation 1–2 hours depending on how messy the payees are. Compare that with 20–40 minutes per statement for manual entry — and no independent check that it was entered correctly.

Related: Rowvert for bookkeepers · Rowvert for accountants · How to reconcile a bank statement in Excel.