Reconciliation without losing your mind: matching ledger, gateway and bank

If you move money, at some point finance will ask "does our ledger match the bank". The honest answer at most startups is "mostly". This post is how we went from "mostly" to a daily report that finance actually trusts. It took us around 4 months, so dont feel bad if yours is still a spreadsheet.

Three sources, not two

For card and UPI payments you have three views of the same money:

1. Your ledger: what you think happened.
2. The gateway settlement report: what the gateway thinks happened, including their fees.
3. The bank statement: what actually landed in your account.

Matching ledger to gateway tells you if you recorded transactions correctly. Matching gateway settlements to bank credits tells you if you were actually paid. You need both, and people often do only the first one.

Ingest raw, never edit

Every file we receive is stored as-is (original in object storage with a checksum) and parsed into staging tables. Nobody edits a row in staging. If the bank sent a wrong file, they send a corrected file and we ingest that as a new version. When there is a dispute 6 months later you want the exact bytes you received.

Match in passes

Our matcher runs in passes, strictest first:

• Pass 1, exact reference. Gateway transaction id on both sides, same amount. This matches 95%+ of rows.
• Pass 2, reference with a difference. Same id but amount differs. Usually partial refunds or fee adjustments. These go to a review queue with the difference shown, never auto accepted.
• Pass 3, amount and date window. No reference on one side (happens with some bank transfers). Same amount within T+0 to T+2. Only accepted if there is exactly one candidate. Two payments of ₹499 on the same day will happily match the wrong way otherwise.

Settlements are net and batched

The gateway does not send you each payment. It sends one bank credit per settlement cycle, which is the sum of payments minus refunds minus fees minus tax on fees. So you cannot match a bank credit to a payment. You match the bank credit to a settlement id from the gateway report, and the settlement id to its list of payments. In India the bank side usually has a UTR number and most gateways include the UTR in their settlement report, so that becomes your join key.

The unmatched queue needs an owner

This is the part that is not technical and it matters most. Every unmatched item goes into a queue with an age. 0-2 days is normal (settlement lag). 3-7 days someone must look. 7+ days is escalated. And the queue has a named owner in finance ops, not "the team". Before we did this our unmatched list was 3000 rows and growing and everyone assumed someone else was on it.

Report the difference by component

Our daily report shows matched count, unmatched count by age, and the total difference broken into principal, fees and tax. A difference only in the tax column tells you where to look before you open a single file.

No fancy tool here. Some SQL, a cron job and a dashboard. The hard part was agreeing with finance what "matched" means, write that definition down first.