Tianhao Zhang

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.

7,113audit entries; 205 real problems found
14 of 14planted problem types found
486 / 0customer names matched automatically / matched wrong

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.
Eight raw register rows next to the same rows as clean, formatted sales sheet cells
Raw register rows (left) and the same rows after cleanup (right).
A drawn supplier invoice PDF beside its parsed rows in the invoice sheets
A sample supplier invoice and the rows parsed from it.
Bar chart of problem types found
Problems found, by type.

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 →