Reconcile Business Ledgers
Reconcile two datasets without changing the originals. Produce matched records, unmatched records, ambiguous candidates, control totals, and a human-readable audit trail.
Non-negotiable controls
- Copy inputs before transformation and preserve raw values.
- Never drop duplicates, blank keys, parse failures, or unmatched rows silently.
- Do not use fuzzy matches as confirmed matches.
- Keep one-to-one matching unless the user explicitly defines another relationship.
- Record every normalization, tolerance, filter, and exclusion.
- Reconcile record counts and monetary totals before and after matching.
- Mask unnecessary personal identifiers in reports.
Workflow
1. Define the two sides
Identify the business meaning of Side A and Side B, reporting period, expected relationship, currency, time zone, and authoritative source. Obtain the user's acceptance rule when the correct relationship is not evident.
2. Profile before matching
For each dataset, report:
- row and column counts;
- candidate key columns and uniqueness;
- blank, duplicate, and malformed keys;
- date range and amount totals;
- inconsistent types, encodings, units, signs, or currencies;
- obvious header, subtotal, or footer rows.
Do not begin matching until the selected keys and amount semantics are stated.
3. Create a rule hierarchy
Use the least permissive rule first:
- exact composite business key;
- normalized composite key;
- normalized key plus amount tolerance;
- normalized key plus date window;
- candidate-only fuzzy similarity for manual review.
Read references/matching-rules.md before using normalization, tolerance, or fuzzy candidates. Assign a rule ID to every matched record.
4. Execute deterministically
For CSV inputs with explicit key columns, prefer scripts/reconcile_csv.py. Example:
python scripts/reconcile_csv.py \
--left orders.csv --right payments.csv \
--left-key order_id --right-key merchant_order_no \
--left-amount payable_amount --right-amount paid_amount \
--amount-tolerance 0.01 --output-dir reconciliation-output
For XLSX inputs, use an available spreadsheet tool to export working copies as CSV or reproduce the same controls directly. Never overwrite the workbook.
5. Investigate exceptions
Classify each exception as:
- missing on Side A;
- missing on Side B;
- duplicate key;
- amount mismatch;
- date mismatch;
- ambiguous multiple candidates;
- parse or format error;
- excluded by an approved rule;
- unresolved.
Preserve candidate rows and explain why automatic matching stopped.
6. Report and sign off
Deliver:
- reconciliation scope and rule table;
- control totals for both sides;
- matched count and amount by rule;
- exception count and amount by category;
- matched and unmatched detail files;
- the five most material discrepancies;
- assumptions, unresolved questions, and recommended next actions.
Quality gate
Verify the conservation equations:
- Side A rows = matched Side A rows + unmatched Side A rows;
- Side B rows = matched Side B rows + unmatched Side B rows;
- no record appears in more than one confirmed pair;
- all numeric parse failures are reported;
- reported totals reproduce from the exported detail files;
- fuzzy or ambiguous candidates remain unconfirmed.
