Payment Reconciliation Automation

Payment reconciliation is the process of matching money that actually landed in your bank account against the invoices or orders it's supposed to be paying for. It sounds like a lookup problem, and for a clean one-to-one payment it is. Real payment processors rarely hand you clean one-to-one payments — they net out fees before payout, batch dozens of transactions into a single deposit, and settle in whatever currency the processor prefers rather than the one the invoice was raised in. Reconciliation automation earns its keep in exactly those cases, not in the trivial ones.

The matching problem

At its simplest, reconciliation matches a bank or processor transaction to an invoice by amount and reference number. That works when the payer includes your invoice number in the payment reference and pays the exact amount. It breaks down constantly in practice: a customer pays one invoice short because they're disputing a line item, a distributor batches payment for twenty invoices into a single wire with no line-level breakdown, or a payment processor's payout reference has nothing to do with your invoice numbering at all. A reconciliation engine needs a matching hierarchy — try exact reference match first, fall back to amount-plus-date-range fuzzy matching, and only then queue the transaction for a human to match manually — rather than treating "found no exact match" as a dead end.

-- Tier 1: exact reference + exact amount
SELECT t.TransactionId, i.InvoiceNbr, 'exact' AS match_type
FROM BankTransactions t
JOIN Invoices i ON i.InvoiceNbr = t.PaymentReference
WHERE t.Amount = i.AmountDue AND t.Matched = 0;

-- Tier 2: amount matches within a tolerance, same customer, unmatched
SELECT t.TransactionId, i.InvoiceNbr, 'fuzzy' AS match_type
FROM BankTransactions t
JOIN Customers c ON c.BankRef = t.PayerRef
JOIN Invoices i ON i.CustomerID = c.CustomerID AND i.Status = 'Open'
WHERE ABS(t.Amount - i.AmountDue) < 1.00
  AND t.Matched = 0
  AND t.TransactionDate BETWEEN i.DueDate - 30 AND i.DueDate + 30;

-- Anything left after both tiers goes to a manual review queue

Fees deducted before payout

Most payment processors and marketplaces (Stripe, PayPal, card networks, e-commerce marketplaces) deduct their processing fee before the payout hits your bank account — so a $1,000 sale can arrive as a $971 deposit, and if your reconciliation only looks at bank deposits, it will never match that deposit to the $1,000 invoice and will flag it as a discrepancy every time. The fix is pulling the transaction-level detail from the processor's own reporting API (not just the bank statement), which typically itemizes the gross amount, the fee, and the net payout separately, and posting the fee as its own line — a contra-revenue or expense entry — tied to the same order or invoice reference, so the reconciliation is comparing gross-to-gross instead of gross-to-net.

Partial payments and multi-currency

A partial payment needs the reconciliation engine to record a partial match against the invoice (reducing the open balance rather than closing it) and route the shortfall to someone who can determine whether it's a dispute, a short-pay for a documented reason, or just an error that needs a follow-up. Multi-currency reconciliation adds another axis: if the invoice was raised in KES but the processor settles in USD, the exchange rate used at settlement time rarely matches the rate assumed when the invoice was created, producing a small FX variance on every cross-currency payment that isn't a reconciliation error at all — it's a real timing difference in exchange rates that needs its own GL treatment (usually an FX gain/loss account) rather than being forced to zero out against the invoice.

Wrapping up

Payment reconciliation automation is worth building because of the exceptions, not the exact matches — pulling gross transaction detail from the processor instead of trusting net bank deposits, tiered matching that falls back gracefully instead of dead-ending, and explicit handling for partial payments and FX variance instead of forcing every mismatch into one generic "unreconciled" bucket. Get those right and the manual queue shrinks to genuinely ambiguous cases instead of routine lookups a system could do.