Bank reconciliation without the spreadsheet: OFX, BAI2 and grouped payments
Reconciling bank statements against invoices is the most mechanical task in bookkeeping. Here is what full automation actually looks like — formats, matching, and the grouped-payment problem.
Ask any bookkeeper where the hours go and reconciliation is near the top of the list. Manual data entry and reconciliation errors consistently rank among the biggest operational pain points for accounting teams — hundreds of amounts keyed by hand, transposition errors, missed decimals, and totals that refuse to tie out at month-end.
The irony is that reconciliation is a control, not a judgment call. There is a right answer to "which payment settles which invoice" — which means almost all of it can be automated, if the software handles the three messy parts that spreadsheets never solved.
Messy part #1: every bank speaks its own dialect
Before matching anything, you have to read the statement. This is where most "just import your CSV" promises die:
- OFX / QFX — the classic download format from Chase, Amex and most US consumer banks. Structured, but with vendor-specific quirks in date and payee fields.
- BAI2 — the corporate treasury format used by US commercial banks. Powerful, cryptic, and nobody's idea of a fun Friday.
- CSV — where it gets genuinely ugly. Chase uses "Posting Date", Bank of America opens the file with a balance line that breaks naive parsers, Citi splits debits and credits into separate columns, and Wells Fargo exports with no header row at all. In the UK, Lloyds and HSBC split money-out and money-in, NatWest uses a signed Value column, and Starling labels the counterparty differently from everyone else.
A reconciliation tool that only reads one clean format has quietly delegated the format problem back to you. DOXALIO detects the format automatically — including headerless exports — normalizes dates whether they arrive as MM/DD/YYYY or DD/MM/YYYY, and recognizes the CSV dialects of the major US and UK banks out of the box.
Messy part #2: matching that goes beyond "same amount"
Amount-only matching is where naive automation produces confident nonsense: two invoices for $500 in the same week, and the tool pairs the wrong one. A serious matching engine crosses five signals:
- Amount and direction — obviously, but with tolerance for bank fees shaving cents off a transfer
- Date proximity — a payment usually lands within a window of its invoice's due date
- Invoice reference — including when the reference hides inside a free-text payment memo ("PAYMENT INV0182 THANKS")
- Counterparty name — tolerant of the legal-suffix noise that plagues exact matching ("ACME LOGISTICS" vs "ACME LOGISTICS INC" vs "ACME LOG.")
- History — this customer has paid this way before
Each proposed match carries a confidence score, so the reviewer's attention goes where the engine is unsure — not evenly across hundreds of obvious pairs.
The case most tools miss: grouped payments
A customer settles three invoices with one $7,250 transfer. No single invoice matches the amount — but a combination does. Spreadsheet reconciliation handles this with human memory and squinting; most software handles it not at all. DOXALIO searches invoice combinations against unmatched transactions and proposes the group, scored, with the component invoices listed. One click confirms all three.
The same logic runs in reverse for partial payments: a transfer that covers most of an invoice, with the remainder flagged rather than silently forgotten.
Messy part #3: matches that persist
A reconciliation that lives in a spreadsheet is a disposable calculation — close the file and next month you start from zero, re-deriving matches you already confirmed. That is not a workflow; it is a ritual.
In a persistent system, a confirmed match is written onto the entries: statement reference, transaction date, transaction id. Settled invoices leave the candidate pool for good. Next import starts from where reality actually is, and the audit trail — who matched what, when, against which statement line — builds itself.
What lands on your desk
After automation has done its pass, two short lists remain:
- Transactions with no invoice — bank fees, direct debits you forgot about, the genuinely unexpected. Worth a look, individually.
- Invoices with no transaction — your actual collection work, sorted by due date.
That is the judgment work, and it is minutes, not hours. Everything else was just pointing — and pointing is what machines are for.
FAQ
How often should reconciliation run?
Continuously — on every statement import — rather than at month-end. Continuous reconciliation means your unpaid-invoice list is always current, errors surface days after they happen instead of weeks, and the month-end close starts from a maintained position.
What about transactions that genuinely have no match?
They stay visible as exceptions. Good automation never invents a match to clear its queue; a low-confidence pairing is presented as a suggestion, not silently confirmed. The system's job is to shrink the exception list honestly, not to make it disappear.
Does this replace my accounting software?
No — it feeds it. Confirmed reconciliations and validated entries export to Xero, QuickBooks or CSV. The ledger of record stays where it is; what changes is how much human time it takes to keep it true.