Reconciliation Program

A local-first reconciliation engine that imports Excel data, normalizes messy financial rows into a canonical transaction schema, and proposes 1‑to‑1, 1‑to‑many, and many‑to‑many matches using deterministic rules, TF‑IDF + cosine similarity, and fuzzy string matching. Every match carries an explanation (per-signal scores) so a human can review and accept/reject it.

The engine is a standalone Python package; thin shells (a FastAPI backend + React UI, and an Excel VBA add-in) reuse it without duplicating logic.

---

📌 Portfolio Snapshot

This repository is a portfolio/demo project, published source-available for technical review (see LICENSE.md). It is not a finished commercial product. All sample data is synthetic — there is no real financial, customer, or transaction data anywhere in this repo (see Data Privacy).

What this demonstrates

• Reconciliation architecture — a phased, greedy matching pipeline (1:1 → 1:many → many:many) with clean separation between candidate generation, scoring, and solving.
• Data normalization — header detection, fuzzy column→field mapping via configurable presets, and coercion of arbitrary spreadsheets into a canonical TransactionRow schema.
• Fuzzy / similarity matching — TF‑IDF + cosine similarity over transaction text, rapidfuzz token-sort ratios, plus a pluggable field-type scoring system (text / reference / numeric / date).
• Explainable scoring — each MatchGroup records a per-feature score breakdown and a human-readable summary, the basis for a review/audit trail.
• Excel workflow automation — a VBA add-in that sends selected ranges to a local backend and writes results back into the workbook.
• Testable business logic — deterministic unit tests over fixtures plus a reproducible synthetic end-to-end demo, wired into CI.

---

🚀 Quickstart

Reproducible end-to-end demo on synthetic data (no real data, no network):

# from the repository root
cd core && pip install -e . && cd ..
python scripts/sample_data_generator.py

This installs the recon engine, generates two synthetic workbooks, runs the full reconciliation pipeline, and writes the result to sample_data/demo_output.json. Expected output:

Result: 5 matches, 1 unmatched (left), 1 unmatched (right)

The 5 matches include four 1‑to‑1 matches and one 1‑to‑many match (BANK-0003 → LEDG-0003 + LEDG-0004, a single payment covering two invoices). See sample_data/demo_output.json for the full explainable result.

Run the tests:

cd core && pip install pytest && pytest src/tests -q

To drive it through the HTTP API and desktop UI instead, see apps/desktop/README.md.

---

🧠 Architecture

flowchart TD
    subgraph Clients
      U[React desktop UI]
      V[Excel VBA add-in]
    end
    U -->|HTTP /run| B[FastAPI backend]
    V -->|HTTP /run| B
    B --> E
    subgraph E[recon core engine]
      direction LR
      I[Ingest &amp; normalize<br/>header detect · column map] --> G[Candidate generation<br/>date window · amount tolerance]
      G --> S[Scoring<br/>TF-IDF/cosine · fuzzy · amount/date · rules]
      S --> SO[Solvers<br/>1:1 · 1:many · many:many]
      SO --> R[Explainable matches<br/>+ unmatched exceptions]
    end
    R --> B

Loading

The pipeline (core/src/recon/pipeline.py) runs in three greedy phases; each phase only sees the rows the previous phase left unmatched:

1. 1‑to‑1 — one bank row ↔ one ledger row.
2. 1‑to‑many — one row ↔ a group whose amounts sum to it (e.g. one payment covering several invoices).
3. many‑to‑many — group ↔ group, under size/amount constraints.

Within each phase: candidate generation prunes the O(N²) space using a date window (±3 days) and amount tolerance; scoring combines amount, date, text (TF‑IDF/cosine + fuzzy), and reference signals; solvers select a consistent, non-overlapping set of matches. Tuning knobs live in ReconDefaults (core/src/recon/config.py).

---

🧱 Repository structure

reconciliation-program/
  README.md  ·  LICENSE.md  ·  .env.example
  docs/                      product brief & user stories
  sample_data/               synthetic demo inputs + demo_output.json
  scripts/sample_data_generator.py   reproducible demo generator

  core/                      the reusable engine (pip-installable)
    pyproject.toml
    src/recon/
      pipeline.py            three-phase orchestration
      config.py              ReconDefaults tuning knobs
      model/                 TransactionRow, MatchGroup (+ explanation)
      ingest/                workbook loading, header detect, column map, normalize
      features/              text_vectorizer (TF-IDF)
      matching/              candidate_generate, score, similarity,
                             flexible_scoring, solve_one_one/one_many/many_many
      ai/                    mapping_suggester (column-mapping assist)
      utils/
    src/tests/               pytest unit tests + fixtures

  apps/
    desktop/
      backend/               FastAPI service over the engine (+ PyInstaller build)
      ui/                    React + Vite review UI
    excel_addin/vba/         Excel VBA add-in (ranges → backend → results)

---

🔒 Data Privacy

• All sample data in this repository is synthetic. The workbooks under sample_data/ and core/tests/test_data/, and all fixtures, are generated or hand-authored with fictional companies (e.g. "Acme Supplies", "Globex Logistics") and invented amounts/references. Any resemblance to real entities is coincidental.
• The engine is local-first: reconciliation runs entirely on your machine and over localhost only. No transaction data is sent to any external service.
• Error monitoring (Sentry) is opt-in and disabled by default — telemetry is sent only if you set SENTRY_DSN yourself (see .env.example).

---

⚠️ Limitations & roadmap

This is a focused demo of the matching engine; some surrounding pieces are intentionally out of scope or only partially built:

• Implemented & working: Excel ingest/normalization, the 1:1 / 1:many / many:many matching engine, TF‑IDF/cosine + fuzzy + field-type scoring, explainable match output, FastAPI backend, React UI, and the VBA add-in's range→backend→results flow.
• Not yet implemented (roadmap): PDF/image ingestion, persistent storage/database, Excel/PDF export of results, a durable audit log, and the LLM-backed mapping/explanation assist (the ai/mapping_suggester interface exists but is not wired to a model).
• The many‑to‑many solver is greedy and constraint-bounded; it is not a global optimizer.
• The Excel VBA add-in targets Windows primarily (Mac paths/HTTP are handled but less tested) and requires the local backend to be running.

---

📄 License

Source-available for portfolio review only; all rights reserved. See LICENSE.md.