Converting Bank Statements to Excel for QuickBooks or Xero Reconciliation

Why exact rows beat auto-categorised output

For reconciliation you need the statement's own numbers — the bank's running balance, its dates, its references — not a tool's interpretation of them. An exact-copy workbook gives you an auditable middle step: what the bank printed, in a grid you can filter.

The workflow

• Convert each client statement PDF — typed or scanned — into Excel.
• Review in Excel: check opening and closing balances against the printed summary, scan for gaps in dates, and confirm row counts look right. The Full_Text sheet holds every line of the original if anything needs cross-checking.
• Clean only what your import needs: most bookkeeping tools want a date, description, and amount column, which the workbook already has under the bank's own headings.
• Save as CSV or XLSX and import through your tool's bank-statement import. QuickBooks and Xero both accept spreadsheet-based statement imports; map the columns once and reuse the mapping for that client.

Things that save time

• Separate money-out and money-in columns (common on UK statements) can be combined with a simple formula when your tool wants one signed amount column.
• European decimal-comma amounts stay as printed — convert them with Excel's Text to Columns or VALUE/SUBSTITUTE once you've verified the rows.
• Keep the original workbook unchanged and do clean-up on a copy of the sheet: the exact copy is your audit trail.