Case study 3 · Data cleanup
Month-end cleanup: invoice PDFs and sales exports into one audited Excel workbook
A small Python pipeline turns a month of messy supplier invoice PDFs and two inconsistent sales exports into one clean Excel workbook, with every fix, drop and flag logged.
Python · pdfplumber · pypdf · pandas · openpyxl · rapidfuzz · pytest · runs offline
Problem
Month-end input for a bookkeeper rarely arrives tidy: invoice PDFs from five vendors in different layouts (a few of them scans), a register export with mixed date formats and region spellings, and an online sales file with different column names, currency strings and stray refund rows. Cleaning it by hand is slow, and silent fixes are worse than no fixes.
What I built
- Vendor layouts in a config file. Each vendor's anchor text, field patterns and table rules live in
config.yml, not in code. - A full audit ledger. Every standardisation, drop and flag is one row on an Issues sheet: file, row or page, old value, new value, severity.
- Exact money. All amounts are parsed as decimals and rounded once, half up to cents, so a European amount like 1.234,56 cannot quietly become 1.23.
- Matching measured against the truth. The demo data generator records which real customer each messy name came from, so the matcher's precision and recall are measured, not assumed.
- Review before delete. Identical register rows are kept and flagged rather than deleted, and the summary shows totals both with and without them.
- Incremental runs. A cache keyed on file hashes and a config fingerprint skips unchanged files on repeat runs.
Results (simulated demo data)
- 5,019 sales rows in, 4,980 kept, 39 dropped, 34 held for review. Totals are shown both ways: 3,052,452.83 with the held rows, 3,033,332.76 without.
- 7,113 audit entries; 205 real problems found. All 14 planted problem types are found, and tests check each one.
- Customer matching: 486 names applied automatically, 0 wrong; precision 1.0 and recall 1.0 against the generator's ground truth; 12 unclear names sent to review.



Links
Built with AI coding tools from my brief. I wrote the brief, made the design calls, and checked every number on this page against the project's results files.
Next: A deal analysis tool, and the 214 prices nobody had checked →