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. hello@minihub.app
