Reconciling a bank statement against your ledger: the six things that never tie out

Bank fees, one transfer covering three invoices, duplicate imports, settlement lag, ids buried in the remark field, and credits with no invoice. How to match them without guessing.

Month-end reconciliation fails in the same six ways every time. If your process is VLOOKUP on an id column, four of them are invisible to you.

1 · The id is not in its own column

Your export has order_id. The bank's export has remark containing transfer INV-1001 Acme Ltd. VLOOKUP finds nothing, so all 25 invoices look unpaid and you spend an afternoon eyeballing.

The fix is token extraction, not fuzzy matching: pull every run of at least three digits out of the remark and intersect it with your real ids.

tokens = set(re.findall(r"\d{3,}", remark)) & set(your_ids)

Do it on the raw text. If you strip spaces and hyphens first, INV-1021/1022/1023 becomes one long number and you match nothing.

2 · One transfer, several invoices

A customer settles three invoices with one payment of 5,200.46 and writes all three ids in the remark. A naive matcher assigns that payment to the first invoice it can find, then reports a huge amount mismatch on one row and two phantom unpaid invoices.

Rule: a line that mentions more than one of your ids is never matched to a single invoice. Send it to a lump-sum pass, then check whether the open invoices sum to the transfer.

3 · Amount mismatch is usually a fee, not an error

Invoice 2,029.85, received 2,014.85. That is a 15.00 bank fee. Report it with the sign, and do not "fix" it by matching on a rounded amount — that hides real short-payments of the same size.

4 · Duplicate payment (or duplicate import)

The same credit line imported twice, or a customer genuinely paying twice. Both look like extra money and both need a different action. A duplicate is detectable: the same id appears in two bank rows, and the second one is not explained by any open invoice.

5 · Settlement lag

Ordered Monday, settled Thursday. An exact-date join reports three days of unpaid invoices every week. Use a window (±7 days is typical for cards) and record the lag so you can spot when it grows.

6 · The credit with no invoice

A deposit from a partner, a refund reversal, a customer paying the wrong account. These are the entries that hide mis-postings. They must be listed, not absorbed.

The order that works

1. Match on id — its own column, then tokens inside the remark
2. Match on amount + settlement window (for statements with no ids at all)
3. Lump-sum pass for one transfer covering several invoices
4. Everything left over is reported as one-sided — nothing is "assumed matched"

Then the check that matters: ledger total vs bank total, and the difference. If your matcher is wrong, that number moves. Print it every run.

Rather not build it

Recon does exactly this and writes a 7-sheet colour-coded workbook that points at your original row numbers, with a non-zero exit code when anything fails to tie out — so a month-end cron job can refuse to close a dirty period. 39 USDT. Try it on the included demo files first: they are wrong in exactly six ways.