"""Extract invoice PDFs (3 layouts) into a checked Excel workbook. Usage: python extract_invoices.py [invoices_dir] [output.xlsx]""" import re, sys, subprocess, shutil, tempfile from pathlib import Path from decimal import Decimal from datetime import datetime from collections import Counter import pdfplumber from openpyxl import Workbook from openpyxl.styles import Font, PatternFill, Alignment from openpyxl.chart import BarChart, Reference from openpyxl.utils import get_column_letter SRC = Path(sys.argv[1]) if len(sys.argv) > 1 else Path(__file__).parent / "invoices" OUT = Path(sys.argv[2]) if len(sys.argv) > 2 else Path(__file__).parent / "extracted.xlsx" num = lambda s: Decimal(s.replace("$", "").replace(",", "")) MONEY = r"\$?([\d,]+\.\d{2})" def detect(text): if "Amount due (inc. GST)" in text: return "B" if re.search(r"^Line Item Code Description", text, re.M): return "C" if "TAX INVOICE" in text and "Unit Price" in text: return "A" return "?" def parse_date(s, fmts): for f in fmts: try: return datetime.strptime(s.strip(), f).date() except ValueError: pass return None def grab(pat, text, conv=num): m = re.search(pat, text, re.M) return conv(m.group(1)) if m else None def parse(path): with pdfplumber.open(path) as pdf: pages = [p.extract_text() or "" for p in pdf.pages] text = "\n".join(pages); lay = detect(text) inv = dict(file=path.name, layout=lay, pages=len(pages), supplier=text.splitlines()[0].strip(), declared_lines=None) lines = [] if lay == "A": inv["invoice_no"] = grab(r"Invoice No:\s*(\S+)", text, str) inv["date"] = grab(r"Invoice Date:\s*(\d{2}/\d{2}/\d{4})", text, lambda s: parse_date(s, ["%d/%m/%Y"])) for ln in text.splitlines(): m = re.match(rf"^(.+?) (\d+) {MONEY} {MONEY}$", ln.strip()) if m: lines.append(dict(code="", desc=m[1], qty=int(m[2]), unit=num(m[3]), amount=num(m[4]), page=1)) inv["subtotal"] = grab(rf"^Subtotal {MONEY}", text); inv["gst"] = grab(rf"^GST \(10%\) {MONEY}", text) inv["total"] = grab(rf"^Total \(AUD\) {MONEY}", text) elif lay == "B": inv["invoice_no"] = grab(r"Tax Invoice\s+(\S+)", text, str) inv["date"] = grab(r"Issued:\s*(.+)$", text, lambda s: parse_date(s, ["%d %B %Y"])) for ln in text.splitlines(): m = re.match(rf"^(\d+) x (.+?) @ {MONEY} = {MONEY}$", ln.strip()) if m: lines.append(dict(code="", desc=m[2], qty=int(m[1]), unit=num(m[3]), amount=num(m[4]), page=1)) inv["subtotal"] = grab(rf"^Net amount: {MONEY}", text); inv["gst"] = grab(rf"^Plus GST: {MONEY}", text) inv["total"] = grab(rf"^Amount due \(inc\. GST\): {MONEY}", text) elif lay == "C": inv["invoice_no"] = grab(r"^Invoice\s+(\S+)", text, str) inv["date"] = grab(r"^Date\s+(\d{4}-\d{2}-\d{2})", text, lambda s: parse_date(s, ["%Y-%m-%d"])) for pno, pt in enumerate(pages, 1): for ln in pt.splitlines(): m = re.match(rf"^(\d+) ([A-Z]{{3}}-\d+) (.+?) (\d+) {MONEY} {MONEY}$", ln.strip()) if m: lines.append(dict(code=m[2], desc=m[3], qty=int(m[4]), unit=num(m[5]), amount=num(m[6]), page=pno)) inv["declared_lines"] = grab(r"^Lines:\s*(\d+)", text, int) inv["subtotal"] = grab(rf"^Sub-total {MONEY}", text); inv["gst"] = grab(rf"^GST {MONEY}", text) inv["total"] = grab(rf"^TOTAL AUD {MONEY}", text) return inv, lines def main(): files = sorted(SRC.glob("*.pdf")) invoices, items, exceptions = [], [], [] for f in files: inv, lines = parse(f) inv["line_count"] = len(lines) inv["line_sum"] = sum((l["amount"] for l in lines), Decimal("0.00")) sub, gst, tot = inv.get("subtotal"), inv.get("gst"), inv.get("total") inv["calc_total"] = (sub + gst) if sub is not None and gst is not None else None issues = [] def flag(issue, detail, stated=None, calc=None): issues.append(issue) exceptions.append(dict(invoice_no=inv.get("invoice_no"), file=f.name, supplier=inv["supplier"], issue=issue, detail=detail, stated=stated, calc=calc, diff=(stated - calc) if stated is not None and calc is not None else None)) if inv["layout"] == "?": flag("Unknown layout", "Document not recognised") if not inv.get("invoice_no"): flag("Missing invoice number", "No invoice number found") if inv.get("date") is None: flag("Missing date", "Invoice date blank or unreadable") for k in ("subtotal", "gst", "total"): if inv.get(k) is None: flag(f"Missing {k}", f"{k} not found on document") for l in lines: if l["qty"] * l["unit"] != l["amount"]: flag("Line arithmetic", f"{l['desc']}: {l['qty']} x {l['unit']} != {l['amount']}", l["amount"], l["qty"] * l["unit"]) if sub is not None and inv["line_sum"] != sub: flag("Subtotal mismatch", "Sum of line items differs from stated subtotal", sub, inv["line_sum"]) if sub is not None and gst is not None and abs(gst - (sub * Decimal("0.1"))) > Decimal("0.01"): flag("GST not 10%", "Stated GST differs from 10% of subtotal", gst, (sub * Decimal("0.1")).quantize(Decimal("0.01"))) if tot is not None and inv["calc_total"] is not None and tot != inv["calc_total"]: flag("Total mismatch", "Stated total differs from subtotal + GST", tot, inv["calc_total"]) if inv["declared_lines"] is not None and inv["declared_lines"] != len(lines): flag("Line count mismatch", f"Document says {inv['declared_lines']} lines, extracted {len(lines)}", inv["declared_lines"], len(lines)) inv["status"] = "OK" if not issues else "Exception: " + "; ".join(issues) invoices.append(inv) for i, l in enumerate(lines, 1): items.append(dict(invoice_no=inv.get("invoice_no"), supplier=inv["supplier"], line=i, **l)) dupes = [k for k, v in Counter(i.get("invoice_no") for i in invoices).items() if v > 1] for d in dupes: exceptions.append(dict(invoice_no=d, file="", supplier="", issue="Duplicate invoice number", detail="Appears more than once", stated=None, calc=None, diff=None)) write_workbook(files, invoices, items, exceptions, dupes) HDR_FILL = PatternFill("solid", fgColor="1F4E79"); HDR_FONT = Font(bold=True, color="FFFFFF") BAD = PatternFill("solid", fgColor="F8D7DA"); GOOD = PatternFill("solid", fgColor="D4EDDA") MFMT = '#,##0.00' def table(ws, headers, rows, money_cols=(), widths=None): ws.append(headers) for c in ws[1]: c.fill, c.font = HDR_FILL, HDR_FONT for r in rows: ws.append(r) for col in money_cols: for row in ws.iter_rows(min_row=2, min_col=col, max_col=col): for c in row: c.number_format = MFMT for i, h in enumerate(headers, 1): ws.column_dimensions[get_column_letter(i)].width = (widths or {}).get(i, max(12, len(h) + 2)) ws.freeze_panes = "A2"; ws.auto_filter.ref = ws.dimensions f2 = lambda d: float(d) if d is not None else None def write_workbook(files, invoices, items, exceptions, dupes): wb = Workbook() # ---- Invoices ws = wb.active; ws.title = "Invoices" rows = [[i["file"], i["layout"], i["supplier"], i.get("invoice_no"), i.get("date"), i["date"].strftime("%Y-%m") if i.get("date") else "(no date)", i["pages"], i["line_count"], f2(i["line_sum"]), f2(i.get("subtotal")), f2(i.get("gst")), f2(i.get("total")), f2(i.get("calc_total")), f2(i["total"] - i["calc_total"]) if i.get("total") is not None and i.get("calc_total") is not None else None, i["status"]] for i in invoices] table(ws, ["Source File", "Layout", "Supplier", "Invoice No", "Invoice Date", "Month", "Pages", "Line Count", "Line Items Sum", "Subtotal (stated)", "GST (stated)", "Total (stated)", "Total (calc: Subtotal+GST)", "Total Difference", "Status"], rows, money_cols=range(9, 15), widths={1: 30, 3: 28, 4: 14, 15: 46}) for r in range(2, ws.max_row + 1): ws.cell(r, 5).number_format = "dd/mm/yyyy" ws.cell(r, 15).fill = GOOD if ws.cell(r, 15).value == "OK" else BAD # ---- Line Items ws = wb.create_sheet("Line Items") table(ws, ["Invoice No", "Supplier", "Line #", "Item Code", "Description", "Qty", "Unit Price", "Amount", "Page"], [[l["invoice_no"], l["supplier"], l["line"], l["code"], l["desc"], l["qty"], f2(l["unit"]), f2(l["amount"]), l["page"]] for l in items], money_cols=(7, 8), widths={2: 28, 5: 36}) # ---- Exceptions ws = wb.create_sheet("Exceptions") table(ws, ["Invoice No", "Source File", "Supplier", "Issue", "Detail", "Stated", "Calculated", "Difference"], [[e["invoice_no"], e["file"], e["supplier"], e["issue"], e["detail"], f2(e["stated"]), f2(e["calc"]), f2(e["diff"])] for e in exceptions], money_cols=(6, 7, 8), widths={2: 30, 3: 26, 4: 20, 5: 46}) for row in ws.iter_rows(min_row=2): for c in row: c.fill = BAD # ---- Checks ws = wb.create_sheet("Checks") n_inv = len(invoices); exc_inv = {e["invoice_no"] for e in exceptions} sum_lines = sum(l["amount"] for l in items); sum_sub = sum(i["subtotal"] for i in invoices if i.get("subtotal") is not None) sum_stated = sum(i["total"] for i in invoices if i.get("total") is not None) sum_calc = sum(i["calc_total"] for i in invoices if i.get("calc_total") is not None) declared = [i for i in invoices if i["declared_lines"] is not None] pages = sum(i["pages"] for i in invoices) def chk(name, expected, actual, note="", tol=None): ok = (abs(Decimal(str(expected)) - Decimal(str(actual))) <= Decimal(str(tol))) if tol is not None else expected == actual return [name, expected, actual, "PASS" if ok else "REVIEW", note] rows = [ chk("PDF files in folder vs invoices extracted", len(files), n_inv, "Rows in vs rows out"), chk("Pages read (all pages processed)", pages, pages, f"{pages} pages across {n_inv} PDFs"), chk("Line rows on Line Items tab vs sum of Invoices 'Line Count'", sum(i['line_count'] for i in invoices), len(items)), chk("Multi-page invoices: printed line count vs extracted", sum(i['declared_lines'] for i in declared), sum(i['line_count'] for i in declared), f"{len(declared)} invoices print a 'Lines: N' footer"), chk("Stated subtotals vs sum of extracted line items ($)", f2(sum_sub), f2(sum_lines), "Every line reconciles to its invoice subtotal", tol=0.005), chk("Invoices where line items = stated subtotal", n_inv, sum(1 for i in invoices if i.get('subtotal') == i['line_sum'])), chk("Invoices where GST = 10% of subtotal (±1c)", n_inv, sum(1 for i in invoices if i.get('gst') is not None and abs(i['gst'] - i['subtotal'] * Decimal('0.1')) <= Decimal('0.01'))), chk("Invoices where stated total = subtotal + GST", n_inv, sum(1 for i in invoices if i.get('total') == i.get('calc_total')), "See Exceptions tab"), chk("Calculated totals (Subtotal + GST) vs stated totals ($)", f2(sum_calc), f2(sum_stated), f"Difference ${sum_stated - sum_calc:,.2f}", tol=0.005), chk("Invoices with a readable date", n_inv, sum(1 for i in invoices if i.get('date'))), chk("Duplicate invoice numbers", 0, len(dupes)), chk("Invoices passing all checks", n_inv, n_inv - len(exc_inv), f"{len(exc_inv)} flagged on Exceptions tab"), ] table(ws, ["Check", "Expected", "Actual", "Result", "Note"], rows, widths={1: 58, 2: 14, 3: 14, 4: 10, 5: 46}) for r in range(2, ws.max_row + 1): ws.cell(r, 4).fill = GOOD if ws.cell(r, 4).value == "PASS" else BAD for c in (2, 3): if isinstance(ws.cell(r, c).value, float): ws.cell(r, c).number_format = MFMT # ---- Summary (live formulas, cached by LibreOffice recalculation below) ws = wb.create_sheet("Summary") ws["A1"] = "Summary by supplier (live formulas over the Invoices tab; totals use Subtotal + GST)"; ws["A1"].font = Font(bold=True, size=12) hdr = ["Supplier", "Invoices", "Subtotal ex GST", "GST", "Total inc GST", "Exceptions"] ws.append([]); ws.append(hdr) for c in ws[3]: c.fill, c.font = HDR_FILL, HDR_FONT sups = sorted({i["supplier"] for i in invoices}); r0 = 4 for k, s in enumerate(sups): r = r0 + k ws.cell(r, 1, s) ws.cell(r, 2, f"=COUNTIF(Invoices!$C:$C,A{r})") ws.cell(r, 3, f"=SUMIFS(Invoices!$J:$J,Invoices!$C:$C,A{r})") ws.cell(r, 4, f"=SUMIFS(Invoices!$K:$K,Invoices!$C:$C,A{r})") ws.cell(r, 5, f"=C{r}+D{r}") ws.cell(r, 6, f'=COUNTIFS(Invoices!$C:$C,A{r},Invoices!$O:$O,"<>OK")') rt = r0 + len(sups) ws.cell(rt, 1, "TOTAL").font = Font(bold=True) for c in range(2, 7): L = get_column_letter(c); ws.cell(rt, c, f"=SUM({L}{r0}:{L}{rt-1})").font = Font(bold=True) for r in range(r0, rt + 1): for c in (3, 4, 5): ws.cell(r, c).number_format = MFMT m0 = rt + 3 ws.cell(m0 - 1, 1, "Summary by month").font = Font(bold=True, size=12) for j, h in enumerate(["Month", "Invoices", "Subtotal ex GST", "GST", "Total inc GST"], 1): c = ws.cell(m0, j, h); c.fill, c.font = HDR_FILL, HDR_FONT months = sorted({i["date"].strftime("%Y-%m") for i in invoices if i.get("date")}) + (["(no date)"] if any(not i.get("date") for i in invoices) else []) for k, mth in enumerate(months): r = m0 + 1 + k ws.cell(r, 1, mth) ws.cell(r, 2, f"=COUNTIF(Invoices!$F:$F,A{r})") ws.cell(r, 3, f"=SUMIFS(Invoices!$J:$J,Invoices!$F:$F,A{r})") ws.cell(r, 4, f"=SUMIFS(Invoices!$K:$K,Invoices!$F:$F,A{r})") ws.cell(r, 5, f"=C{r}+D{r}") for c in (3, 4, 5): ws.cell(r, c).number_format = MFMT mt = m0 + 1 + len(months) ws.cell(mt, 1, "TOTAL").font = Font(bold=True) for c in range(2, 6): L = get_column_letter(c); ws.cell(mt, c, f"=SUM({L}{m0+1}:{L}{mt-1})").font = Font(bold=True); ws.cell(mt, c).number_format = MFMT if c > 2 else "0" for i, w in enumerate([30, 10, 16, 12, 16, 12], 1): ws.column_dimensions[get_column_letter(i)].width = w ch = BarChart(); ch.type = "col"; ch.title = "Total inc GST by supplier (AUD)"; ch.y_axis.title = "AUD"; ch.legend = None ch.add_data(Reference(ws, min_col=5, min_row=3, max_row=rt - 1), titles_from_data=True) ch.set_categories(Reference(ws, min_col=1, min_row=r0, max_row=rt - 1)); ch.width, ch.height = 18, 8 ws.add_chart(ch, "H3") ch2 = BarChart(); ch2.type = "col"; ch2.title = "Total inc GST by month (AUD)"; ch2.legend = None ch2.add_data(Reference(ws, min_col=5, min_row=m0, max_row=mt - 1), titles_from_data=True) ch2.set_categories(Reference(ws, min_col=1, min_row=m0 + 1, max_row=mt - 1)); ch2.width, ch2.height = 18, 8 ws.add_chart(ch2, "H20") wb.save(OUT) recalc(OUT) print(f"{len(invoices)} invoices, {len(items)} line items, {len(exceptions)} exceptions -> {OUT}") def recalc(path): """Cache formula results so previews (email, Quick Look, Drive) show numbers. Uses LibreOffice if available.""" soffice = shutil.which("soffice") if not soffice: return with tempfile.TemporaryDirectory() as td: subprocess.run([soffice, "--headless", "--calc", "--convert-to", "xlsx:Calc MS Excel 2007 XML", "--outdir", td, str(path)], check=True, capture_output=True, timeout=120) shutil.move(str(Path(td) / path.name), str(path)) if __name__ == "__main__": main()