data-reconcile

name

data-reconcile

description

Reconcile any two record sets (A vs B) and triage the discrepancies — match line-by-line on a shared key, or heuristically on amount + date, then classify every unreconciled item (category, materiality, probable cause, suggested action) into a reconciliation working paper (.xlsx). Use whenever the user wants to "reconcile A to B", "reconcile the bank to the ledger", "reconcile the invoice tracker to the accounts", "tie out", "match these two lists/exports", "find the differences between", "what doesn't match", "reconciliation working paper", or "discrepancy triage". Ships presets for common recurring reconciliations (invoice tracker vs accounting records, bank vs cashbook, fund administrator vs internal records, payments/PRF vs bank); generic for anything else. Deterministic and computed locally — it never force-fits a match and never posts an adjustment; it produces a working paper for finance to review and sign off. NOT budget/variance analysis, and NOT deal/asset analysis (that is out of scope).

Finance Reconciliation

Reconcile A (ours) against B (theirs) and — the point of the skill — triage the differences: every unreconciled item is classified, not just listed, so finance can act on the few that matter. Produces a reconciliation working paper for a qualified person to review and sign off.

Working paper, not a posting. It matches and flags; it does not adjust the ledger, post a correction, or write to any system. Unmatched items stay flagged — never force-fitted into a match. Matching and triage are computed on your machine (the engine makes no external calls) — though the AI agent driving it does send whatever it reads into its context to your AI provider; see ../../PRINCIPLES.md#data-handling--pii-policy.

Workflow

import sys; sys.path.insert(0, "scripts")
from reconcile import (reconcile_files, propose_aggregations, apply_aggregations,
                       finalize, render_proposals, write_workpaper, render_report, PRESETS)

Run from the skill directory (skills/data-reconcile/). The scripts path resolves to this skill's scripts/ subdirectory where reconcile.py lives.

1. Intent first — what are we reconciling, and against what?

Establish A (e.g. our invoice tracker / cashbook / internal records) and B (e.g. the ledger / bank statement / fund administrator). Pick the preset if it's a recurring one (python scripts/reconcile.py --catalogue), else go generic.

2. Pick the match strategy (per run — it's a mix)

• Key — a shared reference exists on both sides (invoice no, payment ref): --mode key --key <col>.
• Amount + date — no clean key (typical for bank vs cashbook): --mode amount_date — matches on amount within tolerance, nearest date within the date window (--date-window N, default ±5 days). A small in-window gap is a timing difference (likely in-transit), not a true exception. An equal-amount pair outside the window is not reconciled — it's flagged ambiguous_match for you to confirm, so transactions weeks apart aren't passed off as timing.
  • Always pass --date <col> in this mode — the date window is the safety rail. With no resolvable date column the window can't apply, so equal-amount pairs are held as ambiguous_match ("matched on amount alone — confirm") and the run warns rather than matching on amount alone.
  • Never ship a run flagged ⛔ UNRELIABLE. When almost nothing reconciles although the two sides share many exactly-equal amounts, the engine says so in the headline and leads the warnings with UNRELIABLE RESULT (summary["unreliable"]). At thousands of rows no reviewer can see by eye that virtually every item should have matched, so this is checked mechanically. It nearly always means a misconfigured run, not a broken ledger — re-check the sign convention (--flip-b), the --debit/--credit mapping and --date, then re-run. Do not rationalise a 0%-matched working paper.
  • A second pass over the one-sided residue recovers duplicate, sign_flip and amount_mismatch (e.g. a double-entered cashbook line, a debit booked as a credit, a net-vs-gross GST gap) that key mode gets from the key — so they're classified, not left as bare missing_in_A/B. Conservative and confirm-first (see references/triage-taxonomy.md).

Amounts are parsed as exact Decimal — ties don't break on binary-float dust. Currencies are compared, so 100 USD never matches 100 SGD: name a currency column with --currency <col> (else the code is read from the amount cell's symbol — US$/S$/£/…; a bare $ is treated as ambiguous). A key match whose currencies differ is flagged currency_mismatch; in amount_date mode incompatible currencies simply don't match. For audit/finance work add --strict-currency (strict_currency=True): an unknown currency on either side won't match either — it's routed to currency_unknown rather than assumed compatible (default mode stays permissive). Multi-tab .xlsx? Pass sheet_a= / sheet_b= (CLI --sheet-a/--sheet-b); otherwise ingest auto-picks the single data sheet, or asks you to choose.

Day-to-day bank/GL conveniences (column names resolve case-insensitively, so amount finds a bank CSV's Amount):

• Separate Debit / Credit columns (the usual bank/GL export layout): --debit <col> --credit <col> — signed amount = debit − credit; a side without those columns just uses its amount column, so a debit/credit file reconciles against a signed-amount file.
• Opposite sign conventions (bank statement vs the GL cash account): --flip-b negates B's amounts before comparing.
• Statement completeness: --opening-a/--closing-a (and -b) checks opening + net movement = stated closing — catching a truncated or filtered export before it silently "reconciles". Result is reported in the header of the working paper.
• Ageing: --as-of <date> ages every one-sided exception with a parsed date (age_days), so stale unbanked receipts / uncleared payments rank visibly.
• An amount_mismatch whose gap is a common GST/VAT/WHT rate of the smaller side gets an advisory net-vs-gross hint in its probable cause (never a category change).

Messy inputs? Tidy them with data-tidy first, then reconcile the clean outputs.

3. Run it

res, exceptions, summary, _ = reconcile_files(
    "tracker.xlsx", "ledger.xlsx", preset="invoice_tracker_vs_ledger",
    material=1000, escalate=10000)            # materiality thresholds (set to your policy)
write_workpaper(res, exceptions, summary, "reconciliation_[name]_[date].xlsx",
                a_label="Tracker", b_label="Ledger")

Or one-shot from the CLI: python scripts/reconcile.py A B --preset bank_vs_ledger --mode amount_date --out wp.xlsx

3a. (Optional) Aggregation — sum-to-one / sum-to-sum, confirm-first

When one item ties to a sum of items on the other side (a bank receipt = several invoices), or a batch on each side ties total-to-total, run a second pass over what's still unmatched. It is heuristic, so proposals are never auto-accepted — present them, get the user's confirmation, then apply only the confirmed ones. Bound the search by a shared key + date window + counterparty (a subset-size cap applies underneath) to stay accurate.

res, exceptions, summary, proposals = reconcile_files(
    "bank.xlsx", "ledger.xlsx", preset="bank_vs_ledger", aggregate=True,
    group_col="batch", party_col="counterparty", date_window=7)   # the constraints
print(render_proposals(proposals, a_label="Bank", b_label="Cashbook"))   # show the user
# ... the user confirms which to accept, e.g. [0, 2] ...
apply_aggregations(res, proposals, accepted=[0, 2])
exceptions, summary = finalize(res, a_label="Bank", b_label="Cashbook", material=1000)

Confirmed proposals become matched (aggregated); the rest stay as exceptions. Never confirm on the user's behalf — surface the proposed groupings (constituents + how they tie) and let them decide. (CLI: add --aggregate --group-col … --party-col … --date-window N; it prints the proposals for review — --auto-confirm exists for headless/testing only, not normal use.)

4. Read the triage, act on the reds

The working paper leads with the answer (RAG, % reconciled, value matched vs in exception), then the exceptions sorted by materiality (escalate → material → immaterial) within category. Each carries a probable cause and a suggested action — but the action is for a person to take; the skill proposes, it doesn't post.

Discrepancy Triage (the categories)

Full taxonomy + the materiality bands in references/triage-taxonomy.md (the artifact for finance to red-line). In short:

Category	Means	Typical action
missing_in_B	in A, not B (e.g. invoiced-not-booked; approved-not-paid)	investigate / suggest posting / chase
missing_in_A	in B, not A (e.g. booked-not-tracked; paid-without-approval)	investigate / record / query
amount_mismatch	matched, amounts differ	investigate the difference
rounding	differ within the rounding / FX tolerance	accept (note)
sign_flip	equal magnitude, opposite sign	correct the sign/side
duplicate	appears more than once on one side	confirm + remove
timing_difference	matches on amount, dates differ but within the window — in-transit / cut-off	monitor — clears next period
ambiguous_match	equal amount, dates differ beyond the window — possible match, possibly coincidental	confirm same item before reconciling
currency_mismatch	matched item, but the currencies differ — amounts not comparable as-is	investigate; convert at rate / fix mis-booked currency
currency_unknown	amounts tie but a side's currency is unknown (strict mode only)	establish the currency both sides, then re-run

Materiality grades each by value: within tolerance → immaterial → material → escalate (thresholds you set per run).

Files

• scripts/reconcile.py — engine. Pure match() / triage() (work on parsed rows — offline, testable); aggregation: propose_aggregations() (read-only; sum-to-one + sum-to-sum, bounded by key/date/party), apply_aggregations() (applies only confirmed proposals), render_proposals(), finalize() (triage + summarise after confirmation); reconcile_files() (reads A and B in any format via the shared ingest/dataclean engine), summarise(), render_report(), write_workpaper() (.xlsx — Summary/Exceptions/Matched/Aggregations), redact() (mask parties/amounts before any artefact leaves entitled use), catalogue_md(), PRESETS. --self-test runs offline; --catalogue lists the presets.
• references/triage-taxonomy.md — the discrepancy taxonomy, materiality bands and presets — read this; it's the artifact for finance to confirm against your own policy.
• examples/ — a worked invoice-tracker vs ledger pair (fictional) + the reconciliation output.

Boundaries

• Reconciliation only — records ↔ records. Not budget/variance (no headroom or over-budget analysis), and not deal/asset analysis (development monitoring, capex returns → out of scope). It won't depend on another toolkit.
• Drafts, not advice; never invents. An unmatched or ambiguous item is flagged, never guessed into a match. The output is a first-pass working paper for a qualified person.

Principles

Behavioural charter: ../../PRINCIPLES.md — drafts not advice, never invent, honesty and calibration, plain speech, action boundary.

Data handling

Matching and triage are computed on your machine — A, B and the working paper stay in your synced or shared file store and nothing is sent to any external tool by the engine. (The AI agent driving the skill does send whatever it reads into its context to your AI provider.) Reconciliations routinely touch bank details and sensitive account data (esp. the fund-administrator preset) — keep it local, and redact() parties/amounts before any reconciliation artefact leaves its entitled use. Full rule: ../../PRINCIPLES.md#data-handling--pii-policy.

Feedback

Found a bug or an improvement (a new preset, a triage category)? Capture it with the toolkit's shared format — ../../CONTRIBUTING.md#skill-feedback-format — and save as feedback_data-reconcile_[date].txt.

Requirements & mode

Pre-screen: ../../README.md#mode--environment-compatibility + python ../../scripts/envcheck.py. Python + openpyxl (for the .xlsx working paper and to read .xlsx sources); reads CSV/PDF/.docx/.msg via the shared engine (same optional deps as data-tidy). Portable — the code needs no network, no MS Office, no credentials, no connector.