Invoice extract

Invoice Extract & Summarize

Turns a folder of invoice PDFs into a verified Excel workbook (Summary + Line Items sheets).

Prerequisites

None — no MCP required. Only needs the openpyxl Python package (installed in step 4 if missing).

When to use

User wants invoice PDFs in the project directory read and turned into a cost/price summary table (Excel/xlsx). Rerun this any time new invoices are added — it re-extracts from scratch, it does not incrementally update an old workbook.

Steps

1. Find the invoices. Glob **/*.pdf (or a narrower pattern like **/*[Ii]nvoice*.pdf if the user names one) under the target directory. Confirm the file list with the user only if it's ambiguous which PDFs are invoices vs unrelated PDFs.

2. Batch and delegate extraction. For more than ~4-5 invoices, split into batches of 3-4 and spawn parallel general-purpose Agent calls (not forks — these need no prior context). Each agent's prompt must:

   • List its exact batch of file paths (use forward-slash paths, e.g. /c/repos/<project>/..., they work fine with Read).
   • Instruct it to Read each PDF and extract: invoice no, invoice date, visit date, department/clinic, vendor, patient/bill-to, every line item (description, qty/unit, amount before subsidy, amount after subsidy — or whatever the analogous discount/tax structure is), subtotal, subsidy/discount, pre-tax total, tax/GST amount, total payable, any Medisave/other deduction, amount paid, final amount payable.
   • Tell it to double check every number against the actual PDF text (invoices have easy-to-transpose digits) and to return a plain-text or markdown structured block per invoice, not prose paragraphs, so you can parse it reliably.
   • For small batches (<=5 total invoices), skip delegation and just Read the PDFs directly yourself.

3. Consolidate into JSON. Merge all batch results into one JSON object matching the schema documented at the top of scripts/build_xlsx.py: {"invoices": [...], "line_items": [...]}. Write it to a scratch file (use the session scratchpad directory).

4. Verify before building. Run python scripts/build_xlsx.py <input.json> <output.xlsx> — it runs an arithmetic reconciliation pass first (line-item sum vs subtotal, subtotal+subsidy vs pre-tax total, 9%-style GST recompute vs stated GST) and prints any mismatches before saving. If it reports mismatches, do not report success to the user — go back to the source PDF for that invoice and correct the figure, don't just suppress the warning.

   • If the invoice's tax scheme isn't a flat percentage (no GST-style rate, or a different rate), skip the GST-recompute check but still verify the line-item-sum-vs-subtotal check, which is scheme-agnostic.
   • openpyxl must be installed (pip install openpyxl if missing).

5. Report. Tell the user the output path, invoice count, sheet names, and call out anything that needed manual correction during verification (don't silently fix without mentioning it).

Notes

• Works on whatever project directory Claude is invoked in — point it at any folder of invoice PDFs.
• The build script is generic and reusable on its own: any caller that produces the JSON schema it expects can run it directly without re-deriving the xlsx-building code.