Vendor Statement Reconciliation

A vendor statement and an AP subledger will almost never agree line-for-line by coincidence — timing differences, unapplied credits, and genuinely missing invoices are normal. The point of this skill is to classify every difference explicitly rather than let a vendor's claimed balance and the subledger's balance sit unreconciled with no record of why.

The gate

The script refuses to produce a clean workbook when:

1. An amount or date does not parse. A visible, blocking exception on the row it came from — never coerced or dropped.
2. A (vendor, invoice_no) pair is duplicated in the subledger. Matching against an ambiguous target is unreliable, so the workbook is not written until the subledger is deduplicated or corrected.

A reconciling difference after classification is never a gate failure — it is the normal, expected output of a recon with real discrepancies, reported as a quantified amount on the Per-Vendor Reconciliation tab, never forced to zero or absorbed into a plug.

Inputs

1. Vendor statement extract, all vendors together — vendor, statement_date, ref, amount, line_type (line_type: invoice, credit, or payment).
2. AP subledger export — vendor, invoice_no, amount, invoice_date, status (status: open or paid).

Step 1 — Run the reconciliation

python3 scripts/vendor_statement_recon.py --statement vendor_statements.csv \
  --subledger ap_subledger.csv --client "Northwind Systems Inc" \
  --out "Northwind - Vendor Statement Recon.xlsx"

Step 2 — How a statement line is classified

The reconciled balance starts from the subledger's open total and applies each classified item's dollar effect; the result is compared to the statement's own implied ending balance. Agreement within a cent is a tie. Anything else is reported as a named difference, never plugged.

Step 3 — Proposed actions are suggestions, not determinations

Every discrepancy carries a proposed next step (request an invoice copy, apply a credit, confirm with the vendor) — explicitly labeled SUGGESTED (not a determination). The skill never concludes a discrepancy is resolved; it stops at classifying and quantifying, and the engagement decides the actual resolution.

Deliver

Workbook tabs:

1. Per-Vendor Reconciliation — statement balance, subledger open balance, reconciled balance, difference, and tie/no-tie per vendor.
2. Discrepancy List — every classified item with its suggested (not determined) action.
3. Unreconciled-Exceptions — vendors whose difference did not tie, plus any gate failures if the workbook was forced.

Then, in chat: which vendors tied and which didn't, the largest unreconciled dollar amounts first, and a count of missing-from-subledger items — those are the ones with the clearest unrecorded-liability exposure.

What to escalate

• Missing-from-subledger invoices concentrated near period end — unrecorded-liability risk for the close, not just an AP housekeeping item.
• A vendor with a persistent, unexplained difference across multiple statement periods — a recurring tie failure usually means a systemic posting issue, not a one-off.
• Unapplied credits sitting on a vendor statement for several periods — cash or discount value not yet realized.
• A disputed amount where the subledger figure is larger than the statement figure — worth confirming we haven't accrued or posted more than the vendor believes we owe.

Report each as a fact with the vendor and reference. Do not characterize intent or conclude a dispute's resolution — that decision belongs to whoever is working the vendor relationship.

Security posture

Fully local: standard library plus openpyxl. No network calls, no uploads, no telemetry. Vendor and AP data never leave the machine.

Dependencies

pip install openpyxl

Built by Adopt AI — free to use and modify.