CAM Reconciliation

Reconciling Vendor Invoices to GL Postings

A recoverable CAM pool is only defensible when every dollar a tenant is billed can be traced from the vendor invoice that incurred it to the exact general-ledger posting that recorded it, and reconciling vendor invoices to GL postings is the join that proves that trace holds. This matching step is the audit backbone of GL code mapping for CAM expenses: once a line item has been assigned a category, you still have to confirm that a real, posted GL entry exists for it — same vendor, same money, same period — before it is allowed to accumulate into a pool a tenant will eventually contest. This page builds that reconciler end to end: typed records for the invoice and the posting, a match key on vendor and posting date, an amount comparison with an explicit Decimal tolerance, an optional three-way match against a purchase order or receipt, and an exception queue that captures everything the automatic pass cannot resolve. It is one focused stage of the wider automated invoice parsing and data ingestion pipeline.

Three-way reconciliation of a vendor invoice against a GL posting and a purchase-order receipt Three source records — a vendor invoice, a GL posting, and a purchase-order receipt — converge on a central match engine that keys on vendor and date and compares amounts within a Decimal tolerance. Resolved rows flow to a matched output carrying an audit fingerprint; unresolved rows route to an exception queue for review. Vendor invoice vendor · amount · date GL posting account · amount · date PO / receipt three-way anchor Three-way match key: vendor + date Decimal tolerance Matched audit fingerprint Exception queue unmatched → review match no match
Each vendor invoice is matched against its GL posting and a purchase-order anchor; resolved rows carry an audit fingerprint, and everything else routes to an exception queue.

Context & When to Use This Approach

Reach for an automated invoice-to-GL reconciler the moment your CAM postings and your parsed invoices live in two systems that were never designed to agree. The accounts-payable ledger records what was actually paid and posted; the parsed invoice feed records what a vendor claimed. In a small portfolio you can eyeball the two side by side, but at month-end across dozens of properties the counts run into the thousands, and a single unposted invoice silently inflates a recoverable pool that a tenant’s auditor will later strike out. Matching is the control that catches the invoice that was captured but never posted, the posting that has no supporting invoice, and the duplicate that would double-charge the pool.

The scope here is deliberately narrow. This stage does not classify descriptions — that job belongs to automating vendor invoice classification, which runs first and hands each line item a category. Nor does it decide recoverability, which is a lease fact. Reconciliation consumes an already-categorized invoice and an already-posted GL entry and answers one question: are these two records the same economic event? Use it specifically when you need a defensible audit trail — when a tenant can demand that every billed dollar point back to a posted transaction and a supporting document. If your two feeds share a clean, system-generated posting reference on every row, a plain equality join is enough and you do not need any of the tolerance machinery below. The techniques on this page earn their keep precisely when that shared key is missing, unreliable, or applied inconsistently by the people keying the postings.

Step-by-Step Implementation

The reconciler is built in five steps. Every monetary value flows through the decimal module — binary float cannot represent cent amounts exactly, and a fraction of a cent of drift multiplied across thousands of postings produces a pool that fails to tie out by real dollars.

Step 1 — Model the invoice and the GL posting as typed records

Matching two records reliably starts with two well-formed contracts. Model the vendor invoice and the GL posting as frozen dataclasses whose amounts are Decimal quantized to cents, so no comparison downstream ever has to reason about a stray sub-cent remainder.

from __future__ import annotations

from dataclasses import dataclass, field
from datetime import date
from decimal import Decimal, ROUND_HALF_UP

CENTS = Decimal("0.01")


def to_cents(amount: Decimal) -> Decimal:
    """Quantize a monetary value to cents with bankers-safe rounding."""
    return amount.quantize(CENTS, rounding=ROUND_HALF_UP)


@dataclass(frozen=True)
class VendorInvoice:
    """A parsed, already-categorized vendor invoice awaiting reconciliation."""

    invoice_id: str
    vendor_id: str
    gross_amount: Decimal
    invoice_date: date
    gl_category: str  # assigned upstream by the classifier
    po_number: str | None = None

    def __post_init__(self) -> None:
        object.__setattr__(self, "gross_amount", to_cents(self.gross_amount))


@dataclass(frozen=True)
class GLPosting:
    """A posted general-ledger entry pulled from the accounts-payable system."""

    posting_id: str
    vendor_id: str
    posted_amount: Decimal
    posting_date: date
    gl_account: str
    po_number: str | None = None
    matched_invoice_id: str | None = field(default=None)

    def __post_init__(self) -> None:
        object.__setattr__(self, "posted_amount", to_cents(self.posted_amount))

Freezing the records is a deliberate guard: once a posting has been read from the ledger it is evidence, and nothing in the reconciler should be able to mutate an amount or a date after the fact.

Step 2 — Build a candidate index keyed on vendor and posting window

A naive reconciler compares every invoice against every posting, which is quadratic and slow. Instead, bucket postings by vendor and only consider those whose date falls inside a tolerance window around the invoice date — postings routinely lag the invoice by a few days, so the window absorbs that lag without opening the field to unrelated entries.

from collections import defaultdict
from datetime import timedelta
from typing import Iterable

DATE_WINDOW = timedelta(days=7)


def index_by_vendor(postings: Iterable[GLPosting]) -> dict[str, list[GLPosting]]:
    """Group unmatched postings by vendor for fast candidate lookup."""
    buckets: dict[str, list[GLPosting]] = defaultdict(list)
    for posting in postings:
        if posting.matched_invoice_id is None:
            buckets[posting.vendor_id].append(posting)
    return buckets


def candidates(invoice: VendorInvoice, index: dict[str, list[GLPosting]]) -> list[GLPosting]:
    """Return postings for the same vendor within the date window."""
    lo, hi = invoice.invoice_date - DATE_WINDOW, invoice.invoice_date + DATE_WINDOW
    return [p for p in index.get(invoice.vendor_id, []) if lo <= p.posting_date <= hi]

Keying on vendor_id collapses the search from the whole ledger down to a handful of plausible rows, and the date window keeps a recurring monthly charge from matching against last month’s identical posting.

Step 3 — Compare amounts with an explicit Decimal tolerance

Invoices and postings disagree on the last cent more often than you would like — a rounded tax line, a freight adjustment keyed by hand, a partial credit. Rather than demand exact equality and drown the exception queue, allow a small absolute tolerance expressed in Decimal, and return the signed difference so a reviewer can see exactly how far off a near-match was.

AMOUNT_TOLERANCE = Decimal("0.05")


def amount_delta(invoice: VendorInvoice, posting: GLPosting) -> Decimal:
    """Signed cents difference: positive means the invoice exceeds the posting."""
    return to_cents(invoice.gross_amount - posting.posted_amount)


def amounts_reconcile(invoice: VendorInvoice, posting: GLPosting) -> bool:
    """True when invoice and posting agree within the absolute tolerance."""
    return abs(amount_delta(invoice, posting)) <= AMOUNT_TOLERANCE

The tolerance is a policy number, not a magic constant — set it low enough that a genuine keying error still surfaces. A five-cent band forgives rounding noise while still routing a $50.00-versus-$500.00 transposition straight to review, because its delta of 450.00 dwarfs any plausible tolerance.

Step 4 — Perform the three-way match

An amount-and-vendor match confirms the money moved, but it does not confirm the money was authorized. The three-way match adds the purchase order or receipt as a third leg: when both the invoice and the posting carry the same po_number, the row is corroborated by an independent document and earns a stronger match grade than a two-way amount agreement alone.

from enum import Enum


class MatchGrade(str, Enum):
    THREE_WAY = "three_way"   # invoice, posting, and PO all agree
    TWO_WAY = "two_way"       # invoice and posting agree; no PO anchor
    UNMATCHED = "unmatched"   # no acceptable candidate


def grade_match(invoice: VendorInvoice, posting: GLPosting) -> MatchGrade:
    """Grade a candidate pairing by how many independent legs agree."""
    if not amounts_reconcile(invoice, posting):
        return MatchGrade.UNMATCHED
    po_agrees = (
        invoice.po_number is not None
        and invoice.po_number == posting.po_number
    )
    return MatchGrade.THREE_WAY if po_agrees else MatchGrade.TWO_WAY


def best_match(invoice: VendorInvoice, pool: list[GLPosting]) -> tuple[GLPosting, MatchGrade] | None:
    """Pick the strongest-grade, smallest-delta posting for an invoice."""
    scored = [
        (p, grade_match(invoice, p))
        for p in pool
        if grade_match(invoice, p) is not MatchGrade.UNMATCHED
    ]
    if not scored:
        return None
    # Prefer three-way over two-way, then the tightest amount delta.
    scored.sort(key=lambda sp: (sp[1] is not MatchGrade.THREE_WAY, abs(amount_delta(invoice, sp[0]))))
    return scored[0]

Preferring the three-way grade means that when a vendor sends two similar invoices in the same week, the one whose purchase order matches the posting wins the pairing — the extra document breaks the tie that amount alone cannot.

Step 5 — Route unmatched rows to an exception queue with an audit record

Every reconciled pair, and every invoice that fails to find a partner, must leave a trace. Emit an immutable result carrying a hashlib fingerprint of the two source records so the pairing can be verified later, and push anything ungraded into an exception queue with the reason attached rather than silently dropping it.

import hashlib


@dataclass(frozen=True)
class MatchResult:
    invoice_id: str
    posting_id: str | None
    grade: MatchGrade
    delta: Decimal
    fingerprint: str
    reason: str


def fingerprint(invoice: VendorInvoice, posting: GLPosting | None) -> str:
    """Tamper-evident hash of the exact records that were reconciled."""
    posted = "" if posting is None else f"{posting.posting_id}:{posting.posted_amount}"
    payload = f"{invoice.invoice_id}:{invoice.gross_amount}|{posted}"
    return hashlib.sha256(payload.encode("utf-8")).hexdigest()


def reconcile(invoices: list[VendorInvoice], postings: list[GLPosting]) -> list[MatchResult]:
    """Reconcile a batch of invoices against GL postings, one result each."""
    index = index_by_vendor(postings)
    claimed: set[str] = set()
    results: list[MatchResult] = []

    for invoice in invoices:
        pool = [p for p in candidates(invoice, index) if p.posting_id not in claimed]
        found = best_match(invoice, pool)
        if found is None:
            results.append(MatchResult(
                invoice_id=invoice.invoice_id, posting_id=None,
                grade=MatchGrade.UNMATCHED, delta=Decimal("0.00"),
                fingerprint=fingerprint(invoice, None),
                reason="no posting within vendor and date window",
            ))
            continue
        posting, grade = found
        claimed.add(posting.posting_id)  # one posting satisfies one invoice
        results.append(MatchResult(
            invoice_id=invoice.invoice_id, posting_id=posting.posting_id,
            grade=grade, delta=amount_delta(invoice, posting),
            fingerprint=fingerprint(invoice, posting),
            reason="ok",
        ))
    return results


def exception_queue(results: list[MatchResult]) -> list[MatchResult]:
    """Everything a human must resolve before the pool can close."""
    return [r for r in results if r.grade is MatchGrade.UNMATCHED]

Claiming each posting exactly once is the control that prevents one GL entry from satisfying two invoices — the single most common way a duplicate invoice sneaks a second charge into a pool.

Gotchas & Known Limitations

  • A one-to-one join is a modeling assumption, not a law. A single consolidated posting can settle several invoices, and one invoice can split across multiple postings. When your ledger does that, replace the claimed set with an allocation that tracks remaining posted balance per posting, or you will strand legitimate invoices in the exception queue.
  • The date window is a two-edged control. Widen it and a recurring monthly charge starts matching the previous period’s identical posting; narrow it and a slow-posting vendor falls out entirely. Tune the window against your own posting-lag distribution rather than copying the seven-day default.
  • Amount tolerance must never absorb a category or sign error. A tolerance forgives rounding, not a credit memo booked as a debit. Compare signs explicitly and route any polarity mismatch to review even when the absolute values agree within the band.
  • A three-way match is only as trustworthy as the PO reference. If purchase-order numbers are keyed by hand into both systems, they carry the same typos you are trying to catch elsewhere. Treat a PO agreement as corroboration, not proof, and keep the two-way amount check as the real gate.
  • Reconciliation validates identity, not correctness of the parse. A matched pair can still carry a mis-extracted amount if both feeds inherited the same bad source. Guard the inputs with schema validation for parsed expense data before they ever reach this stage.
  • Never quantize with float anywhere in the path. A single float(amount) slipped into the delta calculation reintroduces representation error and can flip a boundary comparison at the tolerance edge. Keep every value Decimal from ingestion through fingerprint.

Verification

Reconciliation correctness is pinned with table-driven fixtures over deterministic inputs, and because every amount is Decimal quantized to cents, equality assertions are exact — there is no floating-point tolerance to reason about in the tests themselves.

from datetime import date
from decimal import Decimal


def _invoice(**kw: object) -> VendorInvoice:
    base = dict(invoice_id="i1", vendor_id="v1", gross_amount=Decimal("1200.00"),
                invoice_date=date(2026, 3, 10), gl_category="6500", po_number="PO-9")
    base.update(kw)
    return VendorInvoice(**base)  # type: ignore[arg-type]


def test_three_way_beats_two_way() -> None:
    inv = _invoice()
    with_po = GLPosting("p1", "v1", Decimal("1200.00"), date(2026, 3, 12), "6500", "PO-9")
    no_po = GLPosting("p2", "v1", Decimal("1200.00"), date(2026, 3, 11), "6500", None)
    [result] = reconcile([inv], [no_po, with_po])
    assert result.posting_id == "p1"
    assert result.grade is MatchGrade.THREE_WAY


def test_tolerance_forgives_a_rounding_cent() -> None:
    inv = _invoice(gross_amount=Decimal("1200.03"))
    posting = GLPosting("p1", "v1", Decimal("1200.00"), date(2026, 3, 10), "6500", "PO-9")
    [result] = reconcile([inv], [posting])
    assert result.grade is MatchGrade.THREE_WAY
    assert result.delta == Decimal("0.03")


def test_unmatched_invoice_lands_in_the_exception_queue() -> None:
    inv = _invoice(gross_amount=Decimal("999.99"))
    posting = GLPosting("p1", "v1", Decimal("1200.00"), date(2026, 3, 10), "6500", "PO-9")
    queue = exception_queue(reconcile([inv], [posting]))
    assert [r.invoice_id for r in queue] == ["i1"]

Beyond unit fixtures, run the reconciler against a full closed period and reconcile the reconciler itself: the count of matched invoices plus the exception queue must equal the total invoice count, and the sum of matched invoice amounts must tie to the sum of their claimed postings within the aggregate tolerance. A drift in either total is the early signal that a duplicate slipped through or that the date window is stranding real matches. Feeding the fingerprints into a downstream audit log lets you re-verify any historical pairing on demand without re-running the match.

This reconciler hands its matched pairs and its exception queue straight back to GL code mapping for CAM expenses, which only admits a matched dollar into a recoverable pool; if the descriptions on those invoices are not yet resolved to a category, run automating vendor invoice classification before this stage so every row arrives with an account code already attached.