The morning spreadsheet that never balances
Every morning someone in finance or ops downloads yesterday's statement from the provider, exports your ledger for the same day, and pastes both into a spreadsheet. The totals are close. They are rarely the same.
The difference is small, so it gets noted as a timing difference and carried forward. By Friday there are five of these, and a couple from last week that never cleared. Nobody is sure whether they relate to the same payments. When the month closes, finance asks for an explanation and the answer is a list of guesses.
Where the differences come from
A daily total comparison hides the causes, because different kinds of break net against each other. The usual causes are ordinary but they need different fixes.
| Cause | What it looks like | Where it usually starts |
|---|---|---|
| Cut-off timing | An item is on one day in your ledger and the next day at the provider | Different day boundaries or time zones |
| Provider fees | Amounts deducted that your ledger did not post | Fees shown only in statements, not in events |
| Returns and reversals | Money back in with a new reference | Return events not linked to the original payment |
| Missed events | Provider has an item your ledger never saw | Dropped or failed webhooks |
| Manual adjustments | Ops fixed something in one place only | No shared adjustment process |
| Duplicates | The same event posted twice | Retried webhooks without idempotency checks |
Once the breaks are split by cause, most of them repeat. The same fee type, the same kind of return, the same webhook that sometimes arrives twice.
Living with unexplained breaks
Unexplained differences take real people's time every day and they tend to grow. They make month end slow, because finance has to reconstruct weeks of history. They make it harder to answer a partner who asks for evidence that balances were checked. And a genuine problem, such as a payment that went out twice, can hide inside a pile of small timing differences for longer than it should.
The reconciliation we set up
What we build is an automated daily match at item level, with the human part saved for the breaks that need a decision.
- Provider data is pulled automatically each day through the provider's API or statement files, including fees and returns.
- Your ledger entries for the same window are read from your database or ledger service.
- Items are matched on the provider reference first, then on amount, date window and counterparty where references are missing.
- Unmatched items are classified using rules for the causes above, so a known fee type or a timing item is labelled rather than left open.
- Timing items are carried and automatically cleared when their pair arrives the next day.
- Everything left over goes to a short break list, with the evidence from both sides on one screen and a place to record the resolution.
- A daily summary shows matched, carried, explained and open items, and the age of each open one.
Where a break reveals a missed event, the fix is logged so engineering can see patterns, such as one webhook type that keeps failing.
If your finance system is Xero, NetSuite or similar, the reconciled position and adjustments can be posted there through its API rather than typed.
After a month of daily matching
The morning spreadsheet is replaced by a report that is already done. Most days the break list is short, and each item on it has both sides of the evidence attached. Ops clears it in the morning, records a reason, and the item stops appearing.
Month end becomes a review of daily results rather than a reconstruction. When someone asks how a particular balance was checked on a particular day, you open that day's report and show them.
Are your balances doing this?
- Someone compares daily totals in a spreadsheet by hand.
- Small differences get carried forward as timing without a match.
- Provider fees or returns are not posted to your ledger automatically.
- The same break keeps appearing for the same reason.
- Month end takes days because breaks have to be rebuilt from history.