Overview
I handle billing for Dent Right, a small paintless dent repair and wheel repair business that services car dealerships. Four years of records lived in hundreds of monthly Word statements and invoice photos, with no way to see the whole picture. I built a Python pipeline that turned those files into one clean dataset, audited every statement, and pulled out the trends the business should act on.
To protect the business, client names, prices and locations are left out. Dealership accounts appear as Client A–F.
Key results
~7,900invoice lines extracted from 46 months of statements (Dec 2022 – Sep 2026), nearly $1M billed
221 / 229statement totals matched their invoice lines exactly
40billing records flagged to fix, including 3 possible double billings
56%of all billing comes from one account, a key concentration risk
The question
Are our statements accurate, and what do four years of billing say about where the business is strong and where it's exposed?
What I did
- Wrote a parser that reads the table inside every Word statement, handling inconsistent date formats, statements saved in the wrong folder, and multi-page statements
- Cleaned and standardized every invoice line, then replaced client names with codes using a private mapping file
- Reconciled each statement's typed total against the sum of its lines, and checked every invoice number and date for repeats and typos
- Built charts and an interactive dashboard of billing by month, client, service and year
What I found
The records were mostly accurate, but the audit turned up 8 statements whose totals were off, 1 statement addressed to the wrong client, 1 invoice listed twice, 3 invoices that appear on two clients' statements with the same date and amount, 7 reused invoice numbers, 15 date typos, and 5 months of statements that were never saved. Billing for January–September 2026 is up 9.9% over the same months of 2025. The biggest risks are concentration (two accounts make up 79% of billing) and volume swings: one account's work fell 59% in 2025 before recovering 43% this year. A rate increase at the second-largest account didn't reduce its volume.
Next steps
- Correcting the flagged records and confirming the possible double billings with each client
- Saving every month's statements in a consistent place so the record is complete
- Reconciling totals automatically before statements go out
- Prioritizing new dealership accounts to reduce dependence on one client
The Code
The Python I wrote for this project, with real file paths and client names removed. Scroll inside the window to read it all.
# Step 1: turn 46 months of Word statements into one invoice-level dataset.
# Each monthly folder holds one .docx statement per client account; every
# statement is a table of: date | invoice # | P.O. # | amount | running balance,
# followed by the statement's "AMOUNT DUE" total.
import zipfile, re, glob, os, json
import xml.etree.ElementTree as ET
ROOT = "statements" # e.g. statements/DR JANUARY2025/<client>.docx
W = "{http://schemas.openxmlformats.org/wordprocessingml/2006/main}"
MON = {m: i for i, m in enumerate(
["JAN", "FEB", "MAR", "APR", "MAY", "JUN", "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"], 1)}
def cells(path):
"""Return the text of every table cell in a .docx, in reading order."""
root = ET.fromstring(zipfile.ZipFile(path).read("word/document.xml"))
out = []
for tc in root.iter(W + "tc"):
paras = ["".join(t.text or "" for t in p.iter(W + "t")) for p in tc.findall(W + "p")]
out.append("\n".join(paras).strip())
return out
def money(s):
return float(re.sub(r"[^\d.]", "", s)) if re.search(r"\d", s or "") else None
DATE = re.compile(r"\d{1,2}[-/.]\d{1,2}([-/.]\d{2,4})?") # 4-2-26, 6-2, 4/2/2026
rows, statements = [], []
# some statements were saved one folder deeper, next to the invoice photos
files = glob.glob(f"{ROOT}/DR */*.docx") + glob.glob(f"{ROOT}/DR */*/*.docx")
for f in sorted(files):
folder = os.path.relpath(f, ROOT).split("/")[0] # "DR JANUARY2025"
m = re.match(r"DR ([A-Z]+?)(\d{4})$", folder)
year, month = int(m.group(2)), MON[m.group(1)[:3]]
c = cells(f)
cur = None
for i, x in enumerate(c):
if DATE.match(x):
x = re.sub(r"\*+$", "", x).strip() # "12-1-22***" -> "12-1-22"
if x.startswith("Statement"): # a new statement page starts
cur = {"file": f, "year": year, "month": month, "to": "", "service": "", "due": None}
statements.append(cur)
elif cur and x.startswith("To:"): # billing address block
cur["to"] = x
for y in c[i + 1:i + 4]: # service label: PDR / Wheel Repair
if y and not y.lower().startswith("amount"):
cur["service"] = y.strip()
break
elif cur and x == "AMOUNT DUE": # the statement's typed total
cur["due"] = next((money(y) for y in c[i + 1:i + 5] if money(y) is not None), None)
elif cur and DATE.fullmatch(x) and i + 3 < len(c) and c[i + 1].upper().startswith("INVOICE"):
inv = re.sub(r"\D", "", c[i + 1])
amt = money(c[i + 3])
if inv and amt is not None:
rows.append({"file": f, "year": year, "month": month, "to": cur["to"],
"service": cur["service"], "date": x, "invoice": inv,
"po": re.sub(r"(?i)p\.?o\.?\s*#?", "", c[i + 2]).strip(),
"amount": amt})
json.dump({"rows": rows, "statements": statements}, open("raw.json", "w"))
print(len(statements), "statement pages,", len(rows), "invoice lines")# Step 2: clean each invoice line and replace client names with codes.
# The real name -> code mapping lives in a private file that is never published.
import json, re, csv, datetime as dt
raw = json.load(open("raw.json"))
CLIENT_CODES = json.load(open("client_codes_private.json")) # {"<dealership>": "Client A", ...}
def client_name(to_block):
"""The dealership name is the line right after 'Accounts Payable-'."""
lines = [l.strip() for l in to_block.split("\n") if l.strip()]
for k, l in enumerate(lines):
if l.lower().startswith("accounts payable") and k + 1 < len(lines):
return lines[k + 1]
return lines[1] if len(lines) > 1 else to_block
def parse_date(text, stmt_year):
m = re.match(r"(\d{1,2})[-/.](\d{1,2})(?:[-/.](\d{2,4}))?", text)
mo, da, yr = int(m.group(1)), int(m.group(2)), m.group(3)
yr = int(yr) if yr else stmt_year # "6-2" has no year: use the statement's
yr = yr + 2000 if yr < 100 else yr
try:
return dt.date(yr, mo, da)
except ValueError: # impossible dates get flagged later
return None
out = []
for r in raw["rows"]:
d = parse_date(r["date"], r["year"])
out.append({
"statement_month": f"{r['year']}-{r['month']:02d}",
"client": CLIENT_CODES[client_name(r["to"])],
"service": "Wheel Repair" if "wheel" in r["service"].lower() else "PDR",
"invoice": r["invoice"],
"date_typed": r["date"],
"date": d.isoformat() if d else "",
"amount": r["amount"],
"file": r["file"],
})
with open("invoices.csv", "w", newline="") as f:
w = csv.DictWriter(f, fieldnames=list(out[0]))
w.writeheader()
w.writerows(out)
print(len(out), "clean invoice rows")# Step 3: audit the billing records.
# a) Does each statement's typed "AMOUNT DUE" equal the sum of its lines?
# b) Is any invoice number used more than once?
# c) Is any invoice dated outside the month of its statement (a likely typo)?
import json, csv, collections, datetime as dt
raw = json.load(open("raw.json"))
rows = list(csv.DictReader(open("invoices.csv")))
for r in rows:
r["amount"] = float(r["amount"])
# a) reconcile totals, per statement file and service
def svc(label):
return "Wheel" if "wheel" in label.lower() else "PDR"
stated = {(s["file"], svc(s["service"])): s["due"] for s in raw["statements"] if s["due"] is not None}
summed = collections.defaultdict(float)
for r in rows:
summed[(r["file"], "Wheel" if r["service"] == "Wheel Repair" else "PDR")] += r["amount"]
mismatches = [(k, stated[k], round(v, 2)) for k, v in summed.items()
if k in stated and abs(stated[k] - v) > 0.01]
print(f"{len(summed) - len(mismatches)} of {len(summed)} statements reconcile")
for (f, s), due, total in mismatches:
print(f" {f} {s}: typed {due:,.2f}, lines add to {total:,.2f}, off by {due - total:+,.2f}")
# b) repeated invoice numbers
by_number = collections.defaultdict(list)
for r in rows:
by_number[r["invoice"]].append(r)
for number, hits in by_number.items():
if len(hits) > 1:
same_day = len({(h["date_typed"], h["amount"]) for h in hits}) == 1
kind = "possible double billing" if same_day else "number reused"
print(f" invoice {number}: {kind} ->", [(h["client"], h["date_typed"]) for h in hits])
# c) dates that don't belong to the statement month
for r in rows:
y, m = map(int, r["statement_month"].split("-"))
if not r["date"]:
print(" impossible date:", r["invoice"], r["date_typed"])
continue
d = dt.date.fromisoformat(r["date"])
gap = (d.year - y) * 12 + d.month - m
if gap != 0 and not (gap == -1 and d.day >= 25): # late-month work billed next month is normal
print(" date typo?", r["client"], r["invoice"], r["date_typed"], "on", r["statement_month"])
