Skip to content
allanninal.dev/build
Collaborate

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

Matching the bank, and closing the month

Everything so far has happened inside the providers’ reports. The bank is where it has to agree with the outside world and month end is where it has to agree with the calendar, and both are places where the obvious answer is subtly wrong.

Key takeaways

  • Each bank line is matched to an expected payout, a set of payouts, or a provider debit.
  • A payout that never arrives is found from what was expected, not from what was received.
  • Negative payouts happen, and they are debits from the bank, not errors.
  • Cut-off follows the date of the sale, in UK time. Money in transit at month end is an asset.
  • The journal posts sales, refunds, disputes and fees gross, and a person imports it.

Matching a bank line

A bank line matched to one payout, a set of payouts or a provider debitA vertical chain inside an AWS account container, entered from a box on the left labelled A bank line, a credit or a debit with a reference. One expected payout, from the same provider and for the same amount within its window, with a side exit labelled yes leading to Matched, to that payout. A set of payouts, several that total this line exactly, with a side exit labelled yes leading to Matched as a set, every member recorded. A provider debit, where refunds and disputes beat sales that day, with a side exit labelled yes leading to Negative payout, posted through clearing. The final step is Unexplained, sent to a person with the nearest candidates. A note says the match also runs the other way, so a reported payout that no bank line claims in its window is raised on its own.AWS ACCOUNTA bank linea credit or a debit,with a referenceOne expected payout?same provider andamount, in its windowMatchedto that payoutyesA set of payouts?several that totalthis line exactlyMatched as a setevery memberrecordedyesA provider debit?refunds and disputesbeat sales that dayNegative payoutposted throughclearingyesUnexplainedto a person, withthe nearest candidatesThe match also runs the other way: a reported payout that no bank line claims in its window is raised on its own.
Fig 1. Three kinds of match and one remainder. The remainder is short, because every payout is already expected before the bank line arrives.
  • Machine learning
  • Networking
  • Management
  • Analytics

Expected before it arrives

Because every payout has been taken apart from the report before the money lands, the system knows what should appear in the bank, roughly when, and from whom. Matching becomes a check that an expected thing happened rather than a hunt for what an unexpected line might be, and the expected side can raise its own alarm.

That second direction is the one that pays. A payout the provider marked as sent that has not appeared after its usual window is late, or went to an old bank account, or is not what it seemed. None of those shows up in an ordinary bank reconciliation, because an ordinary bank reconciliation only looks at what the bank received.

Sets and debits

Deposits do not always arrive one payout at a time. A weekend or a bank holiday can bring several together, and the reference the bank shows is often truncated to the point of being useless. The match accepts a set of expected payouts from one provider whose total equals the bank line exactly, and records every member, so the set can be taken apart again later.

The other direction is a payout below zero. On a day when a provider’s refunds and disputes exceed its sales there is nothing to pay out, and some providers take the difference from the bank account instead. A matcher that only expects credits will leave that debit unexplained forever. Here it is a payout with a minus sign, posted through clearing like any other.

One sale across a year end

One sale made on the last day of the year and paid out after itA horizontal row of five boxes joined by arrows. Sold 31 March: eighty-six pounds forty at 21:14, on the website. In the report: dated 31 March and posted into clearing. Year closes: the amount is owed by the provider. Paid out 2 April, inside a larger deposit. Stays in March, and April sees only a transfer. A note says the sale is revenue of the year that ended on 31 March, and the deposit on 2 April moves money from one asset to another and changes nobody's sales.ONE SALE ACROSS A YEAR ENDSold 31 March£86.40 at 21:14,on the websiteIn the reportdated 31 March,into clearingYear closesowed by theproviderPaid out 2 Aprilinside a largerdepositStays in MarchApril sees onlya transferThe sale is revenue of the year that ended on 31 March. The deposit on 2 April moves money from one asset to another and changes nobody's sales.
Fig 2. The same £86.40 in five places. Booked from the bank, it would have been a sale of the following financial year.

Cut-off on the sale date

The month closes on the date of the activity in the provider’s report, not the date the money reaches the bank. Everything sold, refunded, disputed or charged up to midnight on the last day belongs to that month whether or not it has been paid out, and the unpaid part sits in the clearing account as money the provider owes.

Which midnight matters. Timestamps are converted to UK time before the cut, because a provider reporting in UTC puts an hour of sales on the wrong side of every month end during British Summer Time, and 31 March is usually already inside it.

The journal, and the pack behind it

At close the system writes one journal per provider per month: sales, refunds, disputes, dispute reversals, processing fees and other charges each to their own account, reserve movements between two asset accounts, and payouts from clearing to the bank. The bank account in the ledger receives nothing but transfers, which is what it should always have received.

Beside the journal it writes a reconciliation pack: each clearing account’s opening balance, movements and closing balance, the provider’s own balance on the same date and the difference, the payouts still in transit, the lines typed by the model and not yet confirmed, and anything the bank match could not place.

A person reads the pack and imports the journal. Nothing here decides on its own to change reported sales, and the pack is short enough that reading it takes less time than the old way of not reconciling at all.

The close will not run while

  • Any payout’s lines fail to sum to it exactly.
  • Any report arrived with rows the parser could not place.
  • Any clearing account differs from the provider’s own balance without a note.
  • Any PDF statement’s lines fail to sum to its printed total.
  • Any bank line in the month is still unexplained.

The next post prices it and the one after gives the service names, the tables and the IAM. The cost is the interesting part: tens of thousands of report lines a year cost almost nothing to parse, and the model is paid only for the handful of sentences no rule can type.

All posts