How nationals pay, and why it never reconciles

Ask most vendors what a given national has actually paid them this year and you will get an estimate. Not because anyone is careless — because every national reports payment differently, and none of them explain how.

Here is what we found reconciling a real book across five of them.

The file is not what it says it is

One national sends remittances as .xls files that are actually HTML tables. Open them in a spreadsheet and they look fine; parse them as a workbook and they fail. Another sends no file at all — payment advices arrive as emails with the detail in an HTML table in the body.

Invoice numbers rarely match your work order

One strips to your work order if you remove a leading letter. Another carries suffixes that have to be unwound in a specific order. A third uses its own internal asset id that matches nothing on your side at all. Each needs its own written rule, and the rule needs testing against a full check before you trust it.

Clawbacks are netted out silently

This is the expensive one. Some remittances include negative lines — money taken back — and the cheque total is the invoices minus those credits. If you reconcile from the invoice lines alone, your books say you were paid more than you were, and the difference sits there for months.

One invoice can span many orders

At least one national bills per property, bundling several services onto one cheque, while most vendors track each service as its own order. Line-by-line matching there is not just difficult, it is wrong — the invoice legitimately covers work you have recorded separately.

A cheque number that means nothing was paid

One export writes a cheque number of zero to mean finalised but not yet paid. Taken literally that books a payment that never happened. Small detail, and it will quietly inflate your cash position until someone notices.

What to do about it

• Write down a matching rule per national and test it against a full cheque before trusting it
• Reconcile to the cheque total, not to the sum of the invoice lines
• Treat negative lines as first-class — they are money, and they are yours to explain
• Compare what the national finalised against what you billed; a systematic gap is a pricing drift, not typos

The point of doing this is not tidy books. It is that short-payments only become visible once the reconciliation exists — and they are small enough individually that nobody ever chases them.