Van Ngo
Work / 04 / Commission Audit Dashboard

Commission Audit Dashboard.
Engineers see where it failed, and how to fix it.

Engineering generates commission data as six CSV files per carrier, per market, per statement month, and the data team has to prove every file is right. I turned the checks into a Python script that Claude runs inside a Cowork workspace I set up, and built a dashboard that shows pass, warning or fail, where each failure is, and often how to fix it.

This is an internal tool, so there are no screens here. I am happy to walk through it in an interview.

TL;DR2 minute read
Problem
Checking every file by hand in Excel took hours, and when a check failed, engineers spent a long time finding where.
Users
The data team who validates the files, and the engineers who fix what fails.
My role
Designed and built it: the Python checks, the workspace that runs them, the pass, warning and fail system, and the dashboard.
Built with
Python, Claude in Cowork, and a dashboard I built for the results.
Outcome
Hours of review cut to minutes. Totals are checked against the logic, failures are pointed out, and engineering built it into their internal tools.
Scale
16 carriers by handoff. Some carriers covered both Medicare and ACA, and some had as many as six statement months.
Status
Handed off to engineering, who built it into their internal tools.
  1. 01Problem
  2. 02How it works
  3. 03The setup
  4. 04Design calls
  5. 05Result

Six files per carrier, per market, every month

Engineering generates the commission data. For every carrier and market, each statement month produces six CSV files, and the data team has to validate them before anyone relies on the numbers. Another team supplied a set of general checks, and on top of those every carrier has checks of its own. By the time I handed it off, the tool covered 16 carriers, some with both a Medicare and an ACA market, and some with as many as six statement months.

Done by hand in Excel, that took hours. And when a file failed, the engineers who had to fix it spent a long time just finding where it went wrong.

How it works

  1. 01Engineering generates the filesSix CSVs per carrier, per market, per statement month.
  2. 02Filed in one structureCarrier, then market, then the date the files were generated.
  3. 03Claude runs my scriptThe general checks plus each carrier’s own, including whether the totals add up.
Then every check gets a verdict
  1. 04Pass, warning or failSo a reviewer knows at once what needs attention.
  2. 05Where it failed, often with the fixWritten next to each failure, because Claude has the context and my reasoning.
  3. 06Dashboard and team summaryEvery result on one screen, plus a summary ready to paste into Teams.

The setup: context before checks

The script is only as good as what Claude knows when it runs it, so I built the context first.

  • WorkspaceA Cowork workspace called Validation, with a CLAUDE.md (the standing brief) and a memory file (what has been learned and decided), so Claude knows what this work is before it opens a file.
  • ProjectInside it, a Commissions project with its own brief and memory, for the rules that only apply to commissions.
  • FilesA resources folder ordered carrier, market, date generated, then the files, so every run knows exactly which statement it is checking.
  • LearningClaude picked up more of the logic as it worked through real files, so the checks got better along the way.

Three design calls

  • VerdictsI came up with pass, warning and fail, so the data team sees at a glance which files need attention.
  • LocationEngineers took a long time to find a failure, so every failed check says where it failed, and often how to fix it.
  • SummaryThe results end in a summary formatted for Teams, so I can copy it and tell the whole team where things stand in one message.

Result

  • Hours of Excel review cut to minutesCompared with checking by hand
  • Totals checked against the logicEvery file, general and carrier checks
  • Failures located for engineeringWhere it failed, often with the fix
  • 16 carriers covered by handoffMedicare and ACA, up to six statement months
  • Built into engineering’s internal toolsAfter I handed it off, engineers use it to check their own data output