My role
Defined the validation rules and reference logic, specified the outputs, tested against months of real reports
Status
Run daily before the report is delivered
Stack
Python · pandas · openpyxl
Tests
28 unit tests over the validation logic

The problem

The Daily Sales Report is client-facing: 18 of its columns are derived from a quarterly template and a master mapping workbook. A wrong lookup or stale mapping meant a wrong number in front of the client — found, if at all, after delivery. Manual spot-checks caught some errors and missed others, and every check cost analyst time the team didn't have.

How it works

  • Reads exactly three Excel inputs — the template, the MTM master, and the day's DSR — identifying each by filename pattern, and refuses to run on ambiguous input.
  • Recomputes all 18 derived columns independently from the reference workbooks and compares them cell-by-cell to what the DSR actually says.
  • Writes one output workbook with a summary sheet, a data sheet listing every failing row with expected vs. actual values, and a warnings sheet for rows it can't verify (e.g. a model missing from the master).
  • Logs every run to a timestamped file, so "was it validated?" is always answerable.
  • Ships with a README written for non-technical colleagues — the tool is only useful if the team can run it without me.

Decisions worth explaining

Recompute, don't sample
Spot-checking finds the errors you guessed might exist. Recomputing every derived cell finds the ones you didn't — which is the category that reaches clients.
Warnings are separate from failures
A row the validator can't verify is not the same as a row that's wrong. Mixing them teaches people to ignore the report; separating them keeps failures loud.
Unit tests on the validator itself
A validator with a bug is worse than no validator — it manufactures false confidence. The 28 tests pin the matching, lookup, and comparison logic against fixture workbooks.

Outcome

  • The report's error rate has been zero since the validator became part of the daily pre-delivery routine.
  • Checking is now minutes of machine time instead of analyst attention.
  • A sibling tool, the MTM–PCSD Validator, applies the same philosophy to reconciling two master datasets — with every comparison rule in a JSON config so field pairs can change without code changes.
All projects