Cases · Reconciliation · month close
Closing March across seven channels: what was sold, what arrived, where the rest is
Self-initiated case study on synthetic data in the documented formats, with the disagreements planted on purpose. The code that reconciles reads only the files, but it was written by the same person who planted them, so this is a demonstration of the method, not a blind test.
Goal
A shop sells through its own Shopify store, where customers pay by card, PayPal, Klarna, bank transfer or cash at the till; on amazon.de; and by invoice to a US wholesale customer through Stripe, in dollars. At the end of March the owner asks how much actually came in this month, and where the rest of the money is.
Challenge
Eleven files across seven channels: Shopify orders, Shopify Payments, PayPal, Klarna, Amazon All Orders and settlements, Stripe in dollars, and two bank accounts. No two write the same thing the same way: a Klarna sale is three rows keyed by a UUID, Amazon writes one row per amount type, and the bank has one line per payout and none per order.
Solution
Rules written down before counting: which sales belong to March, that money is received when the bank books it, each provider's cut-off, fees held to the contracts, and credits that are not a customer's money kept apart. Each order was joined to its provider and each payout to its bank line, and every channel's bridge from sales to the bank was closed to the cent.
Outcome
Received in March: EUR 422,108.80 and USD 32,753.08. EUR 93,170.12 had not reached the account on 31 March: EUR 42,234 paid out in March and booked in April, EUR 33,656 not yet paid out, EUR 7,938 held by the providers, and EUR 9,341.59 a payout the bank never booked. 28 records did not agree, each with what to do. EUR 33,132 that came into the account is not revenue: a transfer from savings, a currency conversion and a shareholder loan. 119 refunds, EUR 12,307.90, are still to go out.
The 28 records that did not agree
Download JSONAs the close delivers them, and as data for the shop's own tools. Amounts in EUR unless marked USD.
Showing 28 of 28
| Channel | What went wrong | Reference | In the shop | At the provider | In the bank | Difference | What to do |
|---|---|---|---|---|---|---|---|
| Shopify Payments | Order paid, no card charge | #10394 | 38.95 | — | — | -38.95 | Ask Shopify support; the order may have been marked paid by hand |
| Shopify Payments | Charged less than the order | #9838 | 273.00 | 260.50 | — | -12.50 | Collect the difference or correct the order |
| Shopify Payments | Order paid, no card charge | #8935 | 337.85 | — | — | -337.85 | Ask Shopify support; the order may have been marked paid by hand |
| Shopify Payments | Charged less than the order | #8345 | 52.95 | 48.00 | — | -4.95 | Collect the difference or correct the order |
| Shopify Payments | Charged twice | #7560 | 266.00 | 266.00 + 266.00 | — | 266.00 | Refunded already; the fee of the second charge was kept |
| Shopify Payments | Card charged for an order paid through PayPal | #9031 | 47.95 | 47.95 | — | 47.95 | The customer paid twice: refund one of the two payments |
| Shopify Payments | Card charged for an order paid through PayPal | #9490 | 745.00 | 745.00 | — | 745.00 | The customer paid twice: refund one of the two payments |
| Shopify Payments | Card refund differs from the order's refund | #9090 | 99.00 | 103.95 | — | -4.95 | Find who refunded the difference and why |
| Shopify Payments | Adjustment with no order | 2026-03-12 | — | -23.40 | — | -23.40 | Ask Shopify what the adjustment was for |
| Shopify Payments | Chargeback, the order still reads paid | #7795 | 154.00 | -154.00 and a 15.00 fee | — | -169.00 | Answer the dispute, or record it on the order |
| Shopify Payments | Payout never reached the bank | payout 2026-03-18 | — | 9341.59 | — | -9341.59 | Ask Shopify for the payout's trace; check the bank account on file |
| PayPal | Order paid, no PayPal payment | #8041 | 582.00 | — | — | -582.00 | Search PayPal by the customer's e-mail; the order may be marked paid by mistake |
| PayPal | Refund in PayPal, none in the shop | #9232 | 0.00 | -45.00 | — | -45.00 | Find who refunded it and why; record it in the shop or claim it back |
| Klarna | Fee above the contract (3.49% for 2.99%) | KL202603234928 | 2.99% | 3.49% | — | -146.50 | Claim the difference from Klarna |
| Klarna | Held back as a rolling reserve | KL202603234928 | — | 2000.00 | — | -2000.00 | Ask Klarna why and until when; it is the shop's money |
| Bank transfer | Customer paid a different amount | #8067 | 224.10 | — | 209.10 | -15.00 | Probably the customer's bank charges; invoice them or write it off |
| Cash | Deposit differs from the till | week of 09 March | 1748.00 | — | 1708.00 | -40.00 | Count the till against the day reports of that week |
| Amazon | Cancelled, but charged | 302-2534212-3499741 NO-BTL-75 | Cancelled | 27.90 | — | 27.90 | Refund the customer |
| Amazon | Paid less than the price | 304-4103772-7671362 NO-CBL-02 | 14.90 | 9.90 | — | -5.00 | Open a case with Seller Support |
| Amazon | Commission above the rate card | 302-5103311-7220635 NO-PWR-10 | 2.44 | 5.24 | — | -2.80 | Open a case with Seller Support |
| Amazon | Shipped, never paid | 306-2995046-5638590 NO-SCK-02 | 24.90 | — | — | -24.90 | Open a case with Seller Support |
| Amazon | FBA fee charged twice | 303-3618526-6250112 NO-SCK-02 | -3.12 | -6.24 | — | -3.12 | Open a case with Seller Support |
| Amazon | Shipped, never paid | 304-7670079-7856065 NO-LMP-35 | 39.90 | — | — | -39.90 | Open a case with Seller Support |
| Amazon | Shipped, never paid | 304-7670079-7856065 NO-SPK-01 | 39.80 | — | — | -39.80 | Open a case with Seller Support |
| Amazon | Charge with no order in the report | 302-8819284-1290456 NO-LMP-35 | — | 39.90 | — | 39.90 | Check whether the report was pulled before this order |
| Amazon | Refund larger than the sale | 304-4179934-8659362 NO-GLV-01 | 34.90 | 69.80 | — | -34.90 | Open a case with Seller Support |
| Stripe (USD) | Invoice due, not paid | NO-US-2026-007 | USD 3880.00 | — | — | USD -3880.00 | Remind the customer |
| Bank | Credit nobody can place | 26.03.2026 | — | — | 310.00 | 310.00 | Ask the bank for the payer's details |
No record matches the filter.
From the case
The close document, 8 pages (PDF)
Service
Reconciliation for your own files
The same method on your exports: definitions in writing first, then an independent recount.