In this synthetic September example, the ledger records €115,824.69 of net Stripe activity: sales less refunds and fees. The bank shows €112,327.70 in Stripe deposits. The gap is €3,496.99, and nothing in the books says where it went.
One number cannot explain it, because it is not one problem. In this demo it is eight kinds of difference with opposite signs: €3,347.78 is a payout that reached the bank after the month-end cutoff, and the rest is €684.51 of negative and €535.30 of positive differences between the ledger and Stripe, which mostly cancel out.
Reconcile level by level, not in one go
Comparing the ledger with the bank statement directly mixes everything together. The demo walks four levels and makes each one add up to the next, to the cent:
- Operation - ledger lines against Stripe's balance transactions, matched by order and kind (sale, refund, fee, deduction). Exact pairs first; the unmatched remainder is classified, and uncertain explanations are marked as hypotheses.
- Payout - each payout Stripe declares against the sum of the lines it carries.
- Bank - each payout against the bank statement: by the payout ID in the reference (in the demo, bank references carry it), otherwise by a unique amount within five days. A pair is accepted only when the payout has exactly one candidate deposit and that deposit has exactly one candidate payout; ambiguous pairs are not matched automatically.
- Bridge - ledger + operation differences = Stripe; + payout differences = payouts; + bank differences = bank.
A dbt test checks the bridge and fails the run if any level does not add up:
where ledger + d_operation <> platform
or platform + d_payout <> payouts
or payouts + d_bank <> bank
The bridge for September:
| Step | Amount |
|---|---|
| Ledger | €115,824.69 |
| Operation differences | −€149.21 |
| Stripe (balance transactions) | €115,675.48 |
| Payout differences | €0.00 |
| Payouts | €115,675.48 |
| September payout received by the bank on 1 October | −€3,347.78 |
| Bank | €112,327.70 |
At each level, a negative difference lowers the total and a positive one raises it.
What was behind the gap
| Cause | Cases | Difference | What happened |
|---|---|---|---|
| In transit | 1 | −€3,347.78 | a payout created on 30 September and received by the bank on 1 October, as Stripe expected — timing, not an error |
| Duplicate | 3 | −€358.11 | sales booked twice in the ledger; Stripe has each once |
| Dispute not booked | 2 | −€238.31 | two customers disputed a payment; Stripe took the amount and a €15 dispute fee each, and the ledger booked neither |
| Amount mismatch | 2 | −€72.00 | amounts differ by transposed digits — a hint, not proof, of a manual edit |
| Fee not booked | 3 | −€11.92 | Stripe fees missing from the ledger |
| Currency rate (a hypothesis) | 2 | −€4.17 | the customer paid in another currency; the books likely used another rate |
| Missing in the ledger | 3 | +€189.45 | sales on Stripe that never reached the books |
| Partial refund | 2 | +€345.85 | Stripe refunded less than the ledger booked |
About 96% of the gap is the payout received on 1 October - a timing difference, confirmed by the matching bank credit. The remaining €149.21 is a net figure: €684.51 of negative differences partly offset by €535.30 of positive ones. Across the 17 operation discrepancies, the absolute differences add up to €1,219.81, about eight times the net. Each line has a key - an order number or a Stripe ID - so each discrepancy can be investigated and corrected individually, not written off as a rounding difference.
How to repeat it on your own data
You need four exports for one month: the ledger lines for the Stripe account (Xero, QuickBooks or NetSuite), Stripe's itemized payout reconciliation report, the list of payouts, and the bank statement running a few days past month-end. This example uses automatic payouts, no opening Stripe balance, and payouts that carry only September activity; on real data, account for the opening and closing Stripe balances and include later bank entries to resolve payouts still outstanding at the cutoff. The demo is SQL (dbt and DuckDB), and every level is a model you can read. The code and the steps are in the repository: github.com/akarazhev/reconciliation-demo.
Limits
The companies and the data are made up, and every discrepancy above was planted on purpose and then found by SQL. Stripe's columns follow its public documentation (the itemized payout reconciliation report and the Payout object); the payout schedule, fees and exchange rate are fictional. One month, one settlement currency. A real ledger brings its own layout, so the first step on real data is always the mapping.
If your payouts don't match
A one-off check finds this month's causes; the same check every month catches them before the close. If your Stripe payouts and your books disagree, send me the case. The first check is free: one month of exports, read-only. You get the size of the gap and how it splits by cause - totals and counts, without the line list. Fixed-fee engagements start at $400: every difference listed with its key, explanation, and any correction needed, and the mapping or integration fixed if that is where the cause is. You pay after you have the result. A monthly check is priced separately, after the first engagement. [email protected]
Top comments (4)
Planting each discrepancy on purpose and keying it is the right way to build a demo like this, because a reader can check every euro of the gap. The example stops at one month, one currency and no opening balance; a second scenario with an FX payout and a carried balance would show the bridge level earning its keep. If you end up building more of these, I work on Misata, a ground truth artificial data generator tool where you declare targets like a payout landing after cutoff and get multi-table data that hits them exactly from a seed. Happy to compare notes on the dbt test either way.
Thanks!
Good point about a carried balance and an FX payout: that's where the bridge really has to work. Here FX only shows up at the line level: a couple of customers paid in USD, Stripe settled in EUR, and the books used another rate. The opening balance is assumed to be zero, as the README says. A second month with a carried balance would be the natural next step.
Out of curiosity, in the cases you've seen, was it the carried balance or the FX conversion that usually broke the reconciliation?
Carried balance, mostly. FX mistakes tend to be loud and local. Stripe converts at its own rate (you can see it on each balance transaction as exchange_rate) while the books use a daily or ECB rate, so the leftover difference shows up next to the line that caused it. A wrong opening balance is quiet and it compounds. A payout still in transit at month end, a dispute opened in one month and settled in the next, or a reserve release booked in the wrong period throws off every month after it by the same amount. Then the bridge looks broken even when that month’s activity is fine. The nasty case is when the two meet: a USD charge in month one refunded in month two, which Stripe converts at the rate on the day of the refund. Now an FX difference is sitting inside the carried balance. If you build the second month, that refund would be a good one to plant.
Thanks, very helpful. The refund across two months is a great case. I'll plant it in the second month and post here when it's ready.