How to reconcile shop orders with Stripe payouts
By the Analistable team · Updated · 6 min read
A Stripe payout bundles many charges, minus fees and refunds, so a payout never equals a single order. Export Stripe's payout reconciliation data (one row per transaction with its payout ID, gross, fee and net), match each charge to an order by the order number in the charge description or metadata, then sum by payout. Orders with no matching charge, and charges with no order, are your reconciling items.
Part of our guide: How to reconcile data in Excel
What each side contains
| Orders export | Stripe payout transactions |
|---|---|
| Order number, date, customer email | Payout ID, payout date |
| Order total, refunds | Transaction type (charge, refund, fee, adjustment) |
| Payment gateway | Gross, fee, net |
| Description or metadata (often contains the order number) |
Check your shop's Stripe integration for where the order number ends up — usually the charge description or a metadata field such as order_id.
Step by step
- Extract the order number from the Stripe description into its own column, for example
=TEXTAFTER([@Description], "#")for “Order #1042”. - Next to each order, look up its charge:
=XLOOKUP([@Order], Stripe[Order], Stripe[Gross], "No charge"). - Flag differences:
=IF([@Charge]="No charge", "Missing in Stripe", IF(ABS([@Charge]-[@Total])>0.005, "Amount differs", "OK")). - Check each payout:
=SUMIFS(Stripe[Net], Stripe[Payout ID], [@Payout])should equal the deposit on your bank statement.
Worked example
| Order | Order total | Stripe gross | Fee | Net | Status |
|---|---|---|---|---|---|
| #1041 | 60 | 60 | -1.85 | 58.15 | OK |
| #1042 | 120 | 120 | -3.13 | 116.87 | OK |
| #1043 | 45 | — | — | — | Missing in Stripe (paid by PayPal) |
| Refund #1038 | -30.00 | -30 | 0 | -30 | Refund from an earlier order |
Payout total = 58.15 + 116.87 − 30.00 = 145.02, which should match the bank deposit. #1043 isn't a problem — it was paid through another gateway — but it belongs on the reconciling-items list until you've confirmed that.
Differences you'll see
- Fees: Stripe pays out net. Book fees as an expense rather than reducing sales.
- Refunds and disputes: they reduce a later payout, not the payout of the original order.
- Timing: charges made late on the last day of the month often pay out in the next month.
- Currency: charges in another currency are converted before payout; compare in the payout currency.
Bank side of the same process: reconciling a bank statement with the general ledger. Method overview: reconciling data in Excel.
Monthly routine
At month end, list orders paid by Stripe whose charges haven't reached a payout yet. They're a receivable from Stripe, not a missing payment, and they should clear in the first payouts of the next month.
Formulas for older Excel and Google Sheets
TEXTAFTER and XLOOKUP need Excel 365 or Excel 2021 and later. In Excel 2019 or 2016, extract the order number with MID and FIND, and look it up with INDEX/MATCH. Google Sheets has XLOOKUP and REGEXEXTRACT, which copes better with descriptions that vary in format.
Excel 2019: =MID([@Description], FIND("#", [@Description]) + 1, 10)
=IFERROR(INDEX(Stripe[Gross], MATCH([@Order], Stripe[Order], 0)), "No charge")
Google Sheets: =REGEXEXTRACT(C2, "#(\d+)")
=XLOOKUP(A2, Stripe!F:F, Stripe!D:D, "No charge")REGEXEXTRACT returns text, so wrap it in VALUE() if your order numbers are stored as numbers in the orders export. Text “1042” and number 1042 do not match in a lookup, which is the most common reason every row shows “No charge”.
Second example: a payout with a dispute
Disputes behave differently from refunds. Stripe takes the disputed amount back and usually charges a dispute fee as well, both deducted from the next payout. In this example the customer of order #1039 disputed a £50 charge, and the dispute fee is shown as £15 for illustration only — check the fee on your own balance transactions.
| Row | Type | Gross | Fee | Net |
|---|---|---|---|---|
| #1044 | Charge | 200 | -6.1 | 193.9 |
| #1045 | Charge | 80 | -2.62 | 77.38 |
| #1039 | Dispute | -50 | -15 | -65 |
| Total | 230 | -23.72 | 206.28 |
The bank deposit should be 206.28. Record the £50 as a reduction of sales (or a receivable if you are contesting it) and the £15 as a fee. If you later win the dispute, the £50 comes back in another payout, and that payout will look £50 too high against its orders unless you remember the earlier dispute.
The same check in SQL
If you keep a year of payout data, a query is quicker than a workbook full of lookups. This version sums every payout and lists orders that never reached Stripe:
SELECT payout,
COUNT(*) AS rows_in_payout,
ROUND(SUM(gross), 2) AS gross,
ROUND(SUM(fee), 2) AS fees,
ROUND(SUM(net), 2) AS net
FROM stripe
GROUP BY payout
ORDER BY payout;
SELECT o.order_no, o.total
FROM orders o
LEFT JOIN stripe s
ON s.order_no = o.order_no AND s.type = 'charge'
WHERE s.order_no IS NULL;On the two payouts above the first query returns po_123 with a net of 145.02 and po_124 with 206.28; the second returns only order 1043, the PayPal order. For more join patterns see joining data in Python, R and SQL.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Every order shows “No charge” | Order number stored as text on one side and number on the other | Convert both with VALUE() or TEXT() |
| Payout total is out by a few pence | Rounding in a currency conversion | Compare against the payout row's own amount, not a recalculation |
| Orders matched but payout still differs | Refund, dispute or adjustment rows filtered out | Include every transaction type in the SUMIFS |
| Same order matched twice | Partial capture or a retried charge | Count charges per order with COUNTIFS and inspect any above 1 |
| Payout missing from the bank | Payout still in transit or failed | Check the payout status and arrival date in Stripe |
Which method to use
- Under a few hundred orders a month: the XLOOKUP sheet above is enough and easy for anyone to review.
- Several gateways or shops: stack the exports in Power Query with a Source column, then merge on order number — see Power Query merge join kinds.
- A year or more of history: use SQL so you can group by payout, month and gateway in one place.
Whichever you pick, prove the month in one line: the sum of all payout nets should equal the Stripe deposits on the bank statement, and opening plus charges minus fees, refunds and payouts should equal Stripe's closing balance.
Common mistakes
- Matching orders to payouts by date. A payout's date is when Stripe sent the money, not when the orders were placed. Always go through the payout ID on each balance transaction.
- Using the charges export instead of balance transactions. The charges list has no payout ID and leaves out fees, refunds and adjustments, so it can never add up to a deposit.
- Ignoring test-mode or manual charges. Payment links, invoices and manual charges in the same Stripe account also land in payouts. Give them a Source column so they don't appear as orders missing from the shop.
- Reconciling in the wrong currency. If you sell in several currencies, Stripe may hold separate balances and send separate payouts per currency. Reconcile each one on its own.
A quick sense check before you finish: fees divided by gross charges for the month should be close to your usual rate. A sudden jump usually means a higher share of international or manually keyed cards, or a fee row counted twice.
Frequently asked questions
- Why doesn't my Stripe payout match my orders?
- A payout combines many charges minus fees, refunds and disputes, often from different days. Match orders to charges first, then sum charges by payout ID.
- Where do I find which orders are in a Stripe payout?
- Stripe's payout reconciliation data lists every balance transaction in each payout. Export it from the Stripe dashboard's reports.
- Should I record Stripe sales net or gross?
- Usually gross, with fees recorded as a separate expense, so sales match your order totals. Ask your accountant for your jurisdiction.