Premium Billing Reconciliation
Principle 02Fail loudEvery month, six analysts reconciled a ~60-client premium-billing book by hand — roughly 500 carrier reports, each client consolidated from as many as 20 carrier feeds, scrubbed line by line into invoice packages that have to be penny-exact, because accounting rejects a payment one cent off. The team’s own charter put the burden at ~80 hours a week, all of it key-person dependent. This is a Python pipeline that does the consolidation: drop the month’s files into carrier folders, type the month, run. It detects each source, parses every layout, applies the team’s own scrub and wash rules, reconciles to the penny, and writes the same deliverables the analysts produced by hand. And wherever a source is missing or ambiguous, it refuses loudly with a named error — nothing is ever quietly guessed.
pandas · openpyxl
336 carrier feeds
+ coverage report
one run + review
Drop the month in. Get the book back, reconciled.
Drop
The month’s files go into per-carrier folders — the folder decides the carrier, so a misfiled report can’t masquerade as another source.
Detect
Each file is identified from its content against configured detection patterns — never trusted on filename alone.
Parse
Workbooks, PDFs, and portal exports are read by per-layout parsers into one normalized model.
Reconcile
Scrub tiers and per-carrier wash rules apply, then enrollment is matched to billing — exact to the penny on the invoice side.
Package
Client workbooks, per-carrier invoices, and a coverage report land in the output folder; inputs are archived untouched.
It would rather stop than be confidently wrong.
In billing, the dangerous failure isn’t the crash — it’s the plausible number that’s wrong. So every gap here is a named refusal: a missing source, an unreadable file, an unresolvable rate each halt the run with exactly what and where, and the run exits non-zero until the month is genuinely complete.
A coverage report accounts for every configured carrier on every run — expected, produced, or why not — so an unknown stays visible instead of becoming a silent “nothing expected.”
No two clients bill alike — so the book lives in config.
The reason this took a team is heterogeneity: one to twenty carrier feeds per client, three billing scenarios, per-carrier wash rules, carrier-specific templates. All of it now lives in reviewable configuration — the knowledge that was key-person dependent is written down, versioned, and executable.
Onboarding the next client is configuration, not new code — and the config is the count: the roster file, not a number typed into a doc, is the source of truth.
Deterministic where it can be. A human call where it must be.
“Scrubbing” was the team’s craft: reviewing every self-bill-with-retro line and removing what isn’t billable. The engine splits that craft honestly — four deterministic tiers are gated automatically and reproducibly; four judgment tiers are detected, counted, and surfaced for review.
The line it never crosses: it never auto-deletes on a judgment call. Those rows are flagged for a person, because turning a visible unknown into an invisible deletion is how billing goes quietly wrong.
It earns trust by reproducing the team’s own work.
The rollout standard isn’t “looks right” — it’s byte-for-byte. The pipeline runs the same months the analysts worked and its deliverables are compared to theirs document by document: 239 of 244 tracked documents tying, with every remaining gap waiting on externally supplied files or answers, not code.
Under it all sits a test suite where every rule is exercised — including the refusals. The failure modes are tested as deliberately as the successes.
A pipeline the operators run by double-click.
Python 3.11 · pandas · openpyxl · PyMuPDF · pytest — zero-install for the operator: the first run bootstraps its own environment, and every month after is drop, type the month, double-click.