Fuzzy CSV Reconciler — Insurance Claim / Bordereau Register

A production-grade reconciliation tool for insurance brokers and MGAs who must monthly reconcile insurer bordereaux and claim registers against internal spreadsheets. It finds exact and fuzzy-matched records, flags amount discrepancies, compares additional fields, and surfaces every unmatched row — all in a single structured JSON report you can forward directly to your insurer counterparty or finance team.

---

Who Buys This

Persona	Pain
Insurance broker ops teams	Monthly bordereau reconciliation done in Excel; mismatches found late, causing payment delays
MGA finance teams	E&O exposure from undetected claim register discrepancies
Reinsurance technicians	Ceded/assumed bordereau matching across differently-formatted exports

---

What It Does

1. Ingests two CSV registers — your internal export (A) and the insurer bordereau (B).
2. Auto-detects key columns — claim ID, claim number, policy number, reference, etc. — with no configuration needed for standard column names.
3. Exact key matching — identical claim IDs are matched instantly.
4. Fuzzy key matching — typos, formatting differences (CLM-2024-001 vs CLM2024001), and abbreviations are reconciled using configurable similarity scoring.
5. Amount comparison — flags rows where paid/gross/incurred amounts differ beyond a configurable tolerance (default ±0.01).
6. Extra field comparison — optionally compare insured name, status, date of loss, or any named column pair between the two registers.
7. Unmatched rows — rows present in A but absent from B (and vice versa) are clearly labelled.
8. Summary statistics — exact matches, fuzzy matches, amount mismatches, field mismatches, unmatched counts.

What It Does NOT Do

• Does not read PDF or Excel files (CSV only).
• Does not send emails or upload reports automatically.
• Does not use AI or machine learning — fuzzy matching is deterministic sequence similarity (Python difflib).

---

Input Fields

Field	Type	Required	Default	Description
csv_a	string	✅	—	Full CSV text of register A (internal system)
csv_b	string	✅	—	Full CSV text of register B (insurer bordereau)
key_col_a	string	No	auto	Column name in A for claim/record ID
key_col_b	string	No	auto	Column name in B for claim/record ID
amount_col_a	string	No	auto	Amount column in A
amount_col_b	string	No	auto	Amount column in B
extra_fields	array	No	[]	[{"col_a": "status", "col_b": "claim_status"}]
fuzzy_threshold	number	No	0.80	0–1 similarity; lower = more permissive matching
amount_tolerance	number	No	0.01	Max absolute difference treated as a match

---

Output Structure

{
  "summary": {
    "total_rows_a": 150,
    "total_rows_b": 148,
    "exact_matches": 140,
    "fuzzy_matches": 6,
    "unmatched_in_b": 4,
    "unmatched_in_a": 2,
    "amount_mismatches": 3,
    "field_mismatches_rows": 5,
    "ok_rows": 141
  },
  "metadata": {
    "key_col_a": "claim_id",
    "key_col_b": "claim_number",
    "amount_col_a": "amount",
    "amount_col_b": "gross",
    "extra_fields_compared": []
  },
  "records": [
    {
      "key_a": "c001",
      "key_b": "c001",
      "match_type": "exact",
      "match_score": 1.0,
      "status": "OK",
      "amount_comparison": {
        "status": "match",
        "a_amount": 1000.0,
        "b_amount": 1000.0,
        "difference": 0.0
      },
      "field_comparisons": [],
      "row_a": {"claim_id": "C001", "amount": "1000.00"},
      "row_b": {"claim_number": "C001", "gross": "1000.00"}
    }
  ]
}

Each record has status: OK, MISMATCH, UNMATCHED_IN_B, or UNMATCHED_IN_A.

---

Local Demo

Prepare two CSV files as strings and pass via CLI:

python3 main.py '{
  "csv_a": "claim_id,insured,amount\nC001,Acme Corp,1000.00\nC002,Beta Ltd,2500.50\nC003,Gamma Inc,750.00",
  "csv_b": "claim_number,insured_name,gross\nC001,Acme Corp,1000.00\nC002,Beta Ltd,2499.00\nC004,Delta PLC,300.00",
  "fuzzy_threshold": 0.80,
  "amount_tolerance": 0.01
}'

Expected highlights in output:

• C001 → OK (exact match, amounts agree)
• C002 → MISMATCH (amount differs by 1.50)
• C003 → UNMATCHED_IN_B (not in bordereau)
• C004 → UNMATCHED_IN_A (bordereau has extra claim)

---

Run Tests

$pytest tests/test_main.py -v

All tests use Python standard library only. No external dependencies required.

---

Pricing

• Pay-per-run: $15–40 depending on register size
• Monthly subscription: $99/mo unlimited runs

---

Generated by the Fuzzy CSV Reconciler — reconciliation reports carry attribution that travels with the artefact.