Gateway, bank and ledger do not reconcile: find and fix the mismatch

When your gateway, bank and ledger do not reconcile, the money is almost never missing: it is miscategorised, timed differently or recorded in the wrong units. Work by category, not by eyeballing totals. Match on transaction IDs, isolate the difference to fees, refunds, disputes, timing or currency, then trace it to one transaction. Fix forward with an adjusting entry; never edit history.

If you would rather have this found and fixed for you, see how RAITHub would fix it at the end of this guide.

Why won't my gateway, bank and ledger agree?

Because the three records measure the same money at different moments and in different shapes. Your ledger records what you expected to earn, the gateway report records net settlements after it takes its cut, and the bank records the lump-sum deposits that actually landed, often days later. A clean total match is the exception. Reconciliation is finished when every difference has an explanation, not when the numbers happen to agree.

Six categories account for almost every mismatch. Diagnose by category first, because each has a different cause and a different fix.

Category	What you see	Most likely cause
Fees	Deposit is smaller than the order total	The gateway kept its fee. At Stripe's standard US price that is 2.9% + 30¢ per domestic card charge, plus 1.5% for international cards and 1% for currency conversion.
Refunds	A negative line with no matching order in the window	A refund settled in a later payout than the original charge. Stripe states the original processing fee is not returned.
Disputes	Money pulled back plus an extra charge	A chargeback. Stripe lists a $15.00 fee per dispute received.
Timing	A charge with no bank deposit yet	Settlement delay. The deposit lands days after the charge, and weekends move it.
Currency	Amounts that are close but never equal	The customer paid in one currency and you settled in another.
Units	Every figure off by a factor of 100, or by one cent	Dollars compared against cents, or floating-point money in the import path.

What is the diagnostic path to find the mismatch?

Narrow from the whole day to one transaction. Each step removes a category, so you are never staring at a single wrong total with no idea where it came from.

1. Agree the scope. Pick one gateway, one currency and one day. Mixing rails or days is why totals never tie out.
2. Check units first. If the difference is a factor of 100, you are comparing major and minor units. Gateway reports often export dollars; a well-built ledger stores integer minor units. Convert once, on import.
3. Match gross orders to gross charges on the transaction ID. Not on amount. Store the provider's transaction ID at checkout so this join is possible at all.
4. Subtract the fee lines. Most of the remaining gap is fees. Post them to a fee account and the gross match should hold.
5. Pull refunds and disputes into their own buckets. They are often in a different payout, so they look like missing money until you widen the window.
6. Match the payout's net total to the bank deposit inside a date window, on the payout reference where the bank shows one, on amount and date only as a fallback.
7. Whatever is left is a real exception. One transaction, one amount, one owner.

The underlying design — three sources, two matches, one exceptions queue — is covered in depth in payment reconciliation software. This post is the incident version: you already have the mismatch and need to locate it today.

How do I trace one mismatch to a single transaction in SQL?

Full-outer-join your orders against the settlement lines on the transaction ID, and label every row that does not tie out. The sketch assumes PostgreSQL and integer minor units.

-- One day, one provider: label every difference by category.
WITH o AS (
  SELECT provider_txn_id, currency,
         captured_minor - refunded_minor AS expected_minor
  FROM orders
  WHERE paid_at >= DATE '2026-10-10' AND paid_at < DATE '2026-10-11'
    AND provider = 'stripe'
),
s AS (
  SELECT provider_txn_id, currency,
         SUM(gross_minor) AS actual_minor
  FROM settlement_line
  WHERE charge_created_at >= DATE '2026-10-10'
    AND charge_created_at < DATE '2026-10-11'
    AND provider = 'stripe'
    AND category IN ('charge', 'refund')
  GROUP BY provider_txn_id, currency
)
SELECT COALESCE(o.provider_txn_id, s.provider_txn_id) AS txn,
       o.expected_minor, s.actual_minor,
       CASE
         WHEN s.provider_txn_id IS NULL THEN 'missing_in_gateway'
         WHEN o.provider_txn_id IS NULL THEN 'missing_in_orders'
         WHEN o.currency <> s.currency THEN 'currency_mismatch'
         ELSE 'amount_mismatch'
       END AS category
FROM o
FULL OUTER JOIN s
  ON s.provider_txn_id = o.provider_txn_id
WHERE o.provider_txn_id IS NULL
   OR s.provider_txn_id IS NULL
   OR o.currency <> s.currency
   OR o.expected_minor <> s.actual_minor;

Reading the result: missing_in_gateway is usually a charge that has not settled yet, so widen the date window before you panic. missing_in_orders is often a refund or dispute recorded only by the gateway. An amount_mismatch that equals the fee is not a mismatch at all once you match on gross. Stripe's payout reconciliation report groups every charge, refund, dispute and fee into the payout that settled it, which is the file to import for this.

How do I fix a confirmed mismatch without making it worse?

Fix forward. A double-entry ledger is an append-only history, so you correct it by adding an entry, never by editing or deleting one. Editing history destroys the audit trail and breaks every report that already ran.

Confirmed cause	The safe fix	Never do this
Unbooked gateway fee	Post the fee to a fee expense account; the gross match now holds	Adjust the order total to hide the gap
Refund in a later payout	Record the refund with its real date; it will match next cycle	Delete the original charge
Chargeback	Post the reversal and the dispute fee as separate entries	Auto-close it; it has an evidence deadline
Wrong units on import	Fix the import function, then re-import; the unique key prevents duplicates	Hand-edit individual rows
Still unexplained	Leave it open in the exceptions queue with an owner	Force a balancing entry to make the report green

The append-only principle, and why money lives in integers, are explained in double-entry ledger database design and our payments engineering guide. If the mismatch turns out to be the ledger itself drifting out of balance, follow repairing an out-of-balance ledger.

Buy, build or hire?

Match the tool to how many rails you run and whether you need order-level matching, not just deposit-level.

Option	Example and cited price	Choose this when	Where it stops
Accounting software bank reconciliation	Modern Treasury and standard accounting packages reconcile deposits	One or two gateways; booking each payout as one lump sum is acceptable	Reconciles deposits, not individual orders
Spreadsheet from CSV exports	Gateway and bank CSV plus a template	Low volume, one person, mistakes are cheap	Breaks quietly as volume grows; no audit trail
Custom matcher + exceptions queue	Your own import jobs and SQL matcher	Several rails, local wallets or cash on delivery, or marketplace payouts	You own the maintenance when report formats change
Hire an engineering team	A diagnostic engagement, then a build	Money is unexplained now and nobody can trace it	Not a substitute for an accountant's sign-off

How long does it take to find a mismatch yourself?

Our estimate, for someone comfortable with SQL and the gateway's settlement export: one to four hours to locate a single mismatch by category, once you have both files in the same table and matched on transaction ID. A recurring, automated matcher across two to four rails is a two-to-four-week build. The main risk of doing it alone is a false match: a matcher that pairs on amount marks the wrong orders as paid and hides a real loss behind a clean report.

How RAITHub would fix this

• Diagnose first: import one day of your gateway settlement file and bank statement, match on transaction IDs, and hand you the mismatch isolated to its category and the exact transactions.
• Fix forward: adjusting entries for booked fees, late refunds and disputes, with the ledger history left intact.
• Prevent repeats: a two-stage matcher (orders to gateway, payouts to bank) feeding an exceptions queue with owners and statuses.
• Harden the import: integer minor units converted once, and a unique key so a re-run never duplicates.

Timeline: a diagnostic is days; a scripted matcher across two to four rails is two to four weeks; a full reconciliation build with a review screen is a 6–12 week backend project. What you receive: automated tests built on replayable settlement fixtures, CI, handover docs, and full IP under NDA. Production and financial data stay in your own cloud account; development uses synthetic data.

RAITHub is an engineering studio, not an accounting firm, and has not shipped a regulated or licensed fintech product. How you book fees, disputes and currency differences is a question for your accountant: this is general information, so confirm with your adviser. Proof we can point to: TheSkinProof, the founder's own venture, runs multi-gateway payments and reconciliation with 750+ automated tests, and PadhAI, built by RAITHub, was designed for 9 payment gateways behind one abstraction. Next step: a free 15-minute technical audit, then a written fixed quote. Book the audit and bring one day of settlement and bank files. More on the service is on the SaaS development page and the FinTech page.

Frequently asked questions

Why is my bank deposit smaller than my sales total?

Because payouts are net of fees, refunds and disputes. At Stripe's standard US price, card processing is 2.9% + 30¢ per domestic charge, so a $100 order settles as $96.80. Match orders on gross and post fees to their own account, and the gap disappears.

Should I reconcile on amount or on transaction ID?

Transaction ID, always. Amount matching pairs the wrong records whenever two charges share a value, which marks the wrong orders as paid. Use amount and date only as a fallback for bank deposits with no payout reference.

A charge has no matching bank deposit. Is the money lost?

Usually not. Settlement is delayed by days and weekends push it further. Widen the date window before raising it as an exception, and only flag a payout as missing after its expected arrival window has closed.

Can I just edit the ledger to make it balance?

No. A double-entry ledger is append-only. Correct it with an adjusting entry that references the original, so the audit trail survives and reports that already ran stay valid.

How do I reconcile cash on delivery or local wallets?

Against the courier's remittance or the wallet's own settlement, each treated as a separate provider with its own IDs and payout batches. Off-the-shelf tools usually do not cover these, which is where a small custom matcher earns its place.