Payment reconciliation software: matching gateway, bank and orders

Payment reconciliation software matches three records of the same money: your orders, the gateway's settlement report and your bank statement, and puts every difference in an exceptions queue. Exact amounts rarely line up. At Stripe's standard US price of 2.9% + 30¢, a $100 order settles as $96.80, so the matcher must work from transaction IDs and fee lines, not from totals.

If you would rather have it built for you, see how RAITHub would build this at the end of this guide.

What does payment reconciliation software actually do?

It answers one question every day: for every order we think was paid, did the money arrive in the bank, and if not, why not? To answer it, the software runs two matches and keeps a record of everything that did not match.

Source	What it tells you	Typical key to match on
Your orders (your database)	What the customer was charged, refunded or still owes	Your order ID and the provider's transaction ID you stored at checkout
Gateway settlement report	Each charge, refund, dispute and fee, and which payout it was paid out in	Provider transaction ID, payout ID
Bank statement	The lump-sum deposits that actually landed	Amount, value date and, where the bank shows it, a payout reference

The first match is orders to gateway, one transaction at a time. The second is gateway payouts to bank deposits, one batch at a time. Stripe's payout reconciliation report is built around the second step: it groups every payment, refund, dispute and fee into the payout that settled it, so you can match one bank deposit to one batch. Its itemized export also carries a trace ID, which the same docs describe as a bank-generated reference for locating a transfer.

Anything that fails either match becomes an exception: a row with a type, an amount, an owner and a status. Reconciliation is finished when every exception has an explanation, not when every number agrees.

Why don't my gateway payouts match my orders?

Because a payout is a net batch, not a list of orders. Six kinds of difference account for almost every mismatch, and each needs its own rule.

Difference	What happens	How the matcher handles it
Fees	The gateway keeps its fee before paying you. 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, according to Stripe's pricing page.	Match orders on gross; match payouts on net; post fees to their own ledger account
Refunds	A refund is a negative line, often in a later payout than the original charge. Stripe states that the original processing and currency conversion fees are not returned.	Expected amount is captured minus refunded; the original fee stays an expense
Chargebacks	A dispute pulls the money back, plus a fee. Stripe lists $15.00 per dispute received.	Separate exception type with evidence deadline, never auto-resolved
Partial captures	You authorise one amount and capture less, for example when an item is out of stock	Compare against captured amount, not the order total
Currency	The customer pays in one currency and you settle in another	Store the settlement currency and amount separately from the order currency; never compare across currencies
Timing	A charge on the 30th can land in the bank days later. Stripe notes that its report groups payouts by estimated arrival date, not the date your bank posts the deposit, and that weekends and holidays move arrival.	Match bank lines inside a date window; raise "missing" only after the window closes

One more trap is units. Stripe's report columns express amounts in major units (dollars, not cents), while a well-designed order table stores integer minor units. Convert on import, once, in one function. The reason money belongs in integers is covered in our payments engineering guide.

How do local gateways and cash on delivery change reconciliation?

They add more sources, more ID formats and, for cash on delivery, a courier in the middle. That is where off-the-shelf tools usually run out.

TheSkinProof, the founder's own marketplace that RAITHub built and runs, takes four rails: bKash, Nagad, SSLCommerz and cash on delivery. Each one reports money differently, which is why this guide treats reconciliation as a design problem rather than a report to download.

• SSLCommerz uses three identifiers. Its developer documentation describes tran_id as the merchant's own order reference, val_id as SSLCommerz's validation ID, and bank_tran_id as the ID at the bank's end, which its refund API also uses. Store all three at checkout; the settlement file and the refund trail will reference different ones.
• Validation before booking. The same docs say to validate the transaction and amount with the Order Validation API before updating your database. A reconciliation that starts from unvalidated orders reconciles the wrong list.
• Mobile wallets settle into a merchant account on their own schedule. Treat each wallet as a separate provider with its own import and its own payout batches.
• Cash on delivery is reconciled against the courier's remittance, not a gateway. The courier collects cash, deducts its charges and pays a lump sum. Failed deliveries and returns mean an order can be shipped and never paid.

I sell on Flipkart, Meesho or Amazon. How do I reconcile marketplace payouts?

The same way, with the marketplace in the gateway's seat. The marketplace collects from the buyer, deducts its commission, fees, shipping and return costs, and pays you a net amount per settlement cycle. Your job is to match each settled order to what you shipped, and each settlement to your bank.

1. Download the marketplace's order-level settlement or payments report from your seller panel for the cycle.
2. Join it to your own order list on the marketplace order ID, not on amount.
3. Split each line into sale, each fee type, return reversal and adjustments, so you can see what you actually earned per order.
4. Match the cycle's net total to the bank deposit.
5. Queue every order that shipped but has no settlement line after the cycle closes, and every return that was charged to you but never came back to the warehouse.

Fee names, cycles and report formats differ by marketplace and change over time, so read them from your own seller panel rather than from a blog. Connectors exist for some channels: A2X lists Amazon, Shopify, eBay, Etsy, PayPal, TikTok Shop and Walmart, and posts payout data into QuickBooks Online, Xero or NetSuite. At the time of writing, Flipkart and Meesho are not on that list, which is why sellers on those marketplaces often end up with spreadsheets or a small custom matcher.

What does a reconciliation matching query look like in SQL?

Two queries and one exceptions table. The sketch below assumes PostgreSQL, integer minor units, and that you import settlement lines (one row per charge, refund, dispute or fee) and bank lines into your own tables.

-- Every unexplained difference lands here, once.
CREATE TABLE recon_exception (
  id             bigserial PRIMARY KEY,
  kind           text NOT NULL CHECK (kind IN (
                   'missing_in_gateway', 'missing_in_orders',
                   'amount_mismatch', 'currency_mismatch',
                   'payout_not_in_bank', 'chargeback')),
  provider       text NOT NULL,
  ref            text NOT NULL,          -- transaction or payout ID
  expected_minor bigint,
  actual_minor   bigint,
  status         text NOT NULL DEFAULT 'open'
                   CHECK (status IN ('open', 'explained', 'resolved')),
  reason         text,
  assigned_to    text,
  created_at     timestamptz NOT NULL DEFAULT now(),
  resolved_at    timestamptz,
  UNIQUE (kind, provider, ref)           -- re-running never duplicates
);

-- Match 1: orders to gateway, per transaction, for one day.
WITH o AS (
  SELECT provider, provider_txn_id, currency,
         captured_minor - refunded_minor AS expected_minor
  FROM orders
  WHERE paid_at >= DATE '2026-09-30' AND paid_at < DATE '2026-10-01'
    AND payment_method <> 'cod'
),
s AS (
  SELECT provider, provider_txn_id, currency,
         SUM(gross_minor) AS actual_minor  -- charge plus refund lines
  FROM settlement_line
  WHERE charge_created_at >= DATE '2026-09-30'
    AND charge_created_at < DATE '2026-10-01'
    AND category IN ('charge', 'refund')
  GROUP BY provider, provider_txn_id, currency
)
INSERT INTO recon_exception (kind, provider, ref, expected_minor, actual_minor)
SELECT 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,
       COALESCE(o.provider, s.provider),
       COALESCE(o.provider_txn_id, s.provider_txn_id),
       o.expected_minor, s.actual_minor
FROM o
FULL OUTER JOIN s
  ON s.provider = o.provider AND 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
ON CONFLICT (kind, provider, ref) DO NOTHING;

-- Match 2: each payout's net total to a bank deposit inside a 5-day window.
WITH p AS (
  SELECT provider, payout_id, currency,
         SUM(net_minor) AS net_minor,
         MAX(payout_expected_on) AS expected_on
  FROM settlement_line
  GROUP BY provider, payout_id, currency
)
INSERT INTO recon_exception (kind, provider, ref, expected_minor)
SELECT 'payout_not_in_bank', p.provider, p.payout_id, p.net_minor
FROM p
WHERE p.expected_on < CURRENT_DATE - 5
  AND NOT EXISTS (
    SELECT 1 FROM bank_line b
    WHERE b.currency = p.currency
      AND b.amount_minor = p.net_minor
      AND b.value_date BETWEEN p.expected_on AND p.expected_on + 5
  )
ON CONFLICT (kind, provider, ref) DO NOTHING;

Three design choices matter more than the SQL itself. The unique constraint makes the job safe to re-run after a failed import. Match 2 only raises an exception once the timing window has closed, so normal settlement delay does not flood the queue. And amount-only matching in Match 2 is a fallback: where the bank statement shows a payout reference or trace ID, match on that first, because two payouts of the same amount in one week will otherwise pair with the wrong deposits.

How should the exceptions queue work?

As a work queue with owners, not a report someone reads on Fridays.

• Every exception has a kind, an owner and an age. Chargebacks go to whoever answers disputes, because they have an evidence deadline. Payout gaps go to finance.
• "Explained" is a status, with a reason. A known fee rounding difference, a refund in next week's payout, or a courier remittance not yet received is explained, not resolved.
• Auto-explain only the boring cases. For example, a refund line that appears in the next payout can close the matching exception automatically. Never auto-close chargebacks or missing money.
• Keep the history. Who explained what and when is part of your audit trail.

Testing this is the part teams skip. Replay settlement files with refunds in a later payout, a disputed charge and a duplicate import, and assert the queue is the same each time. The test cases are in testing payments and webhooks.

Buy, build or hire?

Most businesses should start with a tool. Build only when your rails, your volume or your marketplace model fall outside what the tools cover.

Option	Example and cited price	Choose this when	Where it stops
Accounting software with bank reconciliation	Xero US plans from $27 to $97 a month, with bank reconciliation on all three plans	One or two gateways, and you are happy to book each payout as one lump sum with fees	It reconciles deposits, not individual orders
Channel connector	A2X from US$29 a month per channel (Walmart US$79)	You sell on a channel it supports and want payouts split into sales, fees and refunds in your books	Unsupported channels, local wallets, cash on delivery
Spreadsheet or no-code	CSV exports plus a spreadsheet template	Low volume, one person does it weekly, and errors are cheap to fix	Breaks quietly as volume and rails grow; no audit trail
Custom build	Your own import jobs, matcher and exceptions queue	Several rails including local wallets or cash on delivery, order-level matching, or you run a marketplace that pays sellers	You own the maintenance when report formats change

The custom option is usually not a whole product. It is often a matcher that feeds your accounting software, so your accountant keeps the tool they know.

How long does it take to build reconciliation yourself?

Our estimate, for a developer comfortable with SQL and the gateways' APIs: one to two days for a spreadsheet process for one gateway; two to four weeks for a scripted matcher across two to four rails with an exceptions table and a simple review screen. Add time for every provider whose settlement file you have never seen.

The main risk is a false match, not a missing one. A matcher that pairs on amount alone will mark the wrong orders as paid and hide real losses behind a clean report. The second risk is floating-point money in the import path, which produces one-cent differences nobody can explain.

Why RAITHub for this

• Multi-rail payments in production. TheSkinProof, the founder's own marketplace, takes bKash, Nagad, SSLCommerz and cash on delivery, with 217 API endpoints and 750+ automated tests. Every one of those rails reports money differently.
• Money modelled in integers, enforced in the database. Sundor Skin stores amounts as integer poisha and enforces credit limits in PostgreSQL, under 530+ automated tests.
• Payments across many gateways. PadhAI, built by RAITHub, was designed for 9 payment gateways behind one abstraction.
• QA-first. The matcher ships with replayable settlement fixtures, so a changed report format fails a test instead of your month-end close.

RAITHub is an engineering studio, not an accounting firm, and has not shipped a regulated or licensed fintech product. How you book fees, chargebacks and currency differences is a question for your accountant. This is general information; confirm with your adviser.

When you don't need us

• You use one or two gateways that your accounting software or a connector already supports. Buy the tool.
• Your volume is low enough that a weekly spreadsheet takes under an hour and nobody misses a mistake.
• You need a regulated reconciliation process for a bank, payment institution or money transmitter. Hire a team that has taken one through a regulator's or auditor's review.

How RAITHub would build this

• Scope: import jobs for each gateway, wallet, courier or marketplace settlement file, converted to integer minor units on the way in.
• Scope: the two-stage matcher (orders to provider, payouts to bank), with reference-first matching and amount-and-window fallback.
• Scope: an exceptions queue with kinds, owners, statuses, reasons and history, plus a review screen for finance.
• Scope: an export or sync into your accounting software, so books stay where your accountant works.

Timeline: 6–12 weeks as a backend build, depending on how many rails and how messy their files are. A single-gateway matcher sits at the short end.

What you receive: automated tests and CI built on replayable settlement fixtures, handover docs and runbooks for month-end and for a changed report format, and full IP under NDA. We sign NDAs and DPAs and work inside your controls; production and financial data stay in your own cloud account, and development uses synthetic data.

Next step: a free 15-minute technical audit, then a written fixed quote. Book the audit, and bring one sample settlement file per rail. More on the service is on the Backend and API development page and the FinTech page; if you run a store or marketplace, see eCommerce and the marketplace development guide.

Frequently asked questions

What is payment reconciliation?

Checking that every payment you recorded matches what the payment provider settled and what reached your bank, and explaining every difference. It runs at two levels: individual transactions, and payout batches against bank deposits.

Why is my Stripe payout less than my sales?

Payouts are net of fees, refunds and disputes. Stripe's standard US price is 2.9% + 30¢ per domestic card charge, and its payout reconciliation report lists every charge, refund, dispute and fee in each payout.

What should I match payments on: amount or transaction ID?

Transaction ID first, always. Store the provider's IDs at checkout. Use amount and date only as a fallback for bank deposits, and only where no payout reference is available.

How do I reconcile cash on delivery orders?

Against the courier's remittance report, not a gateway. Match delivered orders to collected amounts, account for the courier's charges, and queue orders that were shipped but never remitted or returned.

Can Xero or QuickBooks do payment reconciliation?

They reconcile bank transactions, which covers payout-level matching. Order-level matching across several gateways, wallets or marketplaces usually needs a connector or a custom matcher feeding the accounting software.

How long does a custom reconciliation system take to build?

A scripted matcher for two to four rails is roughly two to four weeks for one experienced developer, by our estimate. A full build with review screens and accounting sync is a 6–12 week backend project.