Skip to content
allanninal.dev/build
Collaborate

Part 2 of 7 · Payout reconciler series ~5 min read

What is actually inside a payout

Every provider knows exactly what it did with your money, to the penny and the minute, and publishes it. The work is not finding the information. It is that four providers publish it four different ways, and none of them in the shape an accounting system wants.

Key takeaways

  • The settlement report is the source of truth. The payout is its last line.
  • Four formats become one line shape: date, type, amount, provider, payout.
  • Detect a report’s layout from its header, and count what you could not parse.
  • A payout’s lines must sum to it exactly, or the batch does not post.
  • Reports are parsed in code. Nothing about a CSV needs a model.

Four providers, four formats, one line

Four provider settlement reports normalised into one settlement lineFour boxes on the left feed one box on the right. Card terminal, a daily file for the shop's sales, labelled daily. Online checkout, a balance report by API, labelled daily. PayPal, an activity download paid when withdrawn, labelled on withdrawal. Marketplace, a statement per settlement period, labelled fortnightly. All four converge on Settlement line, holding date, type, amount, provider and payout, which feeds a box below labelled Clearing account, one per provider. A note says the four reports disagree on column names, signs and where fees go, and the box on the right is where they stop disagreeing.Card terminala daily file forthe shop's salesdailyOnline checkouta balance report,by APIdailyPayPalan activity download,paid when withdrawnon withdrawalMarketplacea statement persettlement periodfortnightlySettlement linedate, type, amount,provider, payoutClearing accountone per providerThe four reports disagree on column names, signs and where fees go. The box on the right is where they stop disagreeing.
Fig 1. Four routes in, one shape out. Everything downstream of the right-hand box is written once, not four times.
  • Management
  • Analytics
  • Front-end & mobile

Signs, rows and columns

The differences are not cosmetic. One provider reports a refund as a negative amount on a row typed refund; another reports a positive amount and relies on the type to say which way it goes; a third reports a negative gross with the returned fee in a separate column. A parser that takes the sign at face value will add refunds to sales for one provider in four, and the total will still look plausible.

Fees are the other trap. Some reports put the fee on the same row as the sale, as a column; others issue a separate fee row for every payment; the marketplace nets its commission into each order and lists its other charges at the bottom of the statement. The normalised shape makes the fee a line of its own every time, so a sale is always a sale of the amount the customer paid.

Detect the layout, then count the leftovers

Providers change their export columns without notice, and not always the file name with them. So each parser recognises a report by its header row rather than by where it came from, refuses a header it has never seen, and records every row it could not place. A report that parsed 1,204 rows of 1,206 is not a success with two warnings. It is a report with two unexplained rows, and it does not post until they are explained.

One payout, taken apart

One online checkout payout decomposed into sales, refunds, fees and reserveA horizontal row of five boxes joined by arrows. Sold: three thousand four hundred and twelve pounds sixty from forty-seven website payments. Refunded: one hundred and eighty-four pounds, two orders sent back. Fees: sixty pounds fifty-nine, one line per payment. Held back: one hundred and seventy pounds sixty-three, a five per cent rolling reserve. And Banked: two thousand nine hundred and ninety-seven pounds thirty-eight, the only number anybody saw. A note says that booked as a sale, this payout understates sales by four hundred and fifteen pounds twenty-two, hides two refunds and the fees, and loses track of one hundred and seventy pounds sixty-three the business is still owed.ONE ONLINE CHECKOUT PAYOUTSold£3,412.60 from 47website paymentsRefunded£184.00, two orderssent backFees£60.59, one lineper paymentHeld back£170.63, a 5%rolling reserveBanked£2,997.38, the onlynumber anybody sawBooked as a sale of £2,997.38, this payout understates sales by £415.22, hides two refunds and the fees, and loses track of £170.63 the business is still owed.
Fig 2. A single deposit from the online checkout, taken apart. A report’s worth of lines becomes five numbers, and only the last of them reached the bank.
  • Security & identity
  • Management
  • Analytics

The penny rule

The lines that belong to a payout must add up to it exactly. Not approximately, and not within a tolerance: £3,412.60 less £184.00 less £60.59 less £170.63 is £2,997.38, and if the lines the parser produced come to £2,997.37 the batch is held.

This sounds fussy, and it is the single most useful check in the system. A penny out is almost never rounding. It is a missing line, a doubled line or a sign read the wrong way round, and each of those is cheap to find while the report is open and expensive to find once it has been posted and a month has closed on top of it.

It also makes the rest of the design safe. Because every payout has been shown to equal its lines, a clearing account built from those lines cannot drift for reasons inside the reports. When it does drift, and the next post is about when it does, the cause is outside them.

Where the reserve went

The £170.63 is the line most bookkeeping loses completely. The checkout provider began holding five per cent of each day’s sales after a run of disputes, and releases each day’s hold after a set period. It is not a fee and it is not gone. It is the business’s money on the provider’s balance, and for a while it was in nobody’s books at all.

Treated properly it is a movement between two things the business owns: from money the provider owes now to money the provider owes later. When it is released it comes back through a future payout as a line of its own, and without the first line the release looks like income nobody can explain.

The next post is the account those lines post to, and why its balance is the one number in this system that can be checked against somebody else’s.

All posts