GL Code Mapping for CAM Expenses
Every recoverable dollar on a tenant’s year-end statement traces back to a single decision: which general-ledger account a vendor line item was posted to. GL code mapping for CAM expenses is the stage of the Automated Invoice Parsing & Data Ingestion pipeline that turns a free-text vendor description — HVAC PM Q3, Common Area Sweep, Roof membrane repl — into a canonical chart-of-accounts code and a recoverable-versus-excluded flag. Get this mapping wrong and the error is silent: a capital replacement leaks into the operating pool, a non-recoverable management fee is passed through to tenants, and the reconciliation ties out to a number that no auditor will accept. This page specifies the mapping engine’s data contracts, its deterministic-first decision logic, a runnable Python implementation, and the edge cases that separate a mapping that runs unattended from one that floods the review queue.
Prerequisites & Data Contracts
GL mapping does not parse documents and it does not compute allocations — it sits between the two, and it can only be as correct as the contracts on either side. Three inputs must exist and be stable before the mapping engine runs a single line item.
A validated line-item record. Mapping consumes the output of the extraction and validation stages, never a raw PDF. That means coordinate-aware table extraction with pdfplumber has already isolated the description, amount, service dates, and vendor identifier, and schema validation for parsed expense data has already guaranteed types and required fields. The mapping engine’s input contract is a typed record with a non-null description, a Decimal amount, a resolved vendor_id, and a property_id. If any of those are absent, the record belongs in the quarantine queue, not the mapper.
A canonical chart of accounts. The engine maps to a fixed set of GL codes — 6100 Utilities, 6200 Janitorial, 6300 Landscaping, 6400 Security, 6500 Repairs & Maintenance, 1700 Capital Improvements, and so on. This list is owned by the accounting team, versioned, and treated as an enumeration: a description that resolves to a code outside the enumeration is a bug, not a new category. The recoverability of each code — whether it can be billed back through CAM — is a property of the lease, not the chart, which is why the categories themselves are defined in defining CAM expense categories in commercial leases.
A lease-aware recoverability matrix. The same GL code can be recoverable under one lease and excluded under another. A management fee capped at 3% of gross recoveries, a capital item amortized rather than expensed, or a category a specific tenant negotiated out — all of these are lease facts. The mapping engine reads them from the lease abstraction database, keyed by property_id, so that the recoverable flag it emits is defensible against the exact lease language in force during the reconciliation period.
Algorithm & Rule Design
The mapping engine is deterministic-first by design. Machine learning is a fallback for genuinely novel descriptions, never the primary path, because a reconciliation that cannot explain why a line item landed in a pool cannot survive an audit. The decision cascade runs in strict priority order and stops at the first confident answer.
- Exact synonym lookup. A normalized description (lowercased, whitespace-collapsed, punctuation-stripped) is matched against a curated synonym dictionary.
common area maint,cam sweep, andparking lot cleaningall resolve deterministically to6200 Janitorial. This path carries a confidence of 1.0 and covers the large majority of recurring vendor traffic. - Vendor-scoped default. If the description is unknown but the vendor is known, the engine applies the vendor’s historical modal GL code. A landscaping vendor that has posted to
6300on its last forty invoices maps there again, at a confidence discounted by how consistent that history is. - Fuzzy similarity. For descriptions with no exact or vendor match, the engine computes a string-similarity ratio against every synonym key and takes the best match above a floor. This catches typos and abbreviations (
landscp maintenence) without inventing categories. - Review queue. Anything below the confidence threshold is assigned the sentinel
PENDING_REVIEW, never a guessed code. Silent misassignment is the one outcome the design refuses.
The confidence a candidate mapping carries is a weighted blend of the three signals, so a strong exact match dominates while a weak fuzzy hit backed by consistent vendor history can still clear the bar:
where is the exact-match indicator, is the best string-similarity ratio, and is the fraction of the vendor’s prior invoices posted to the candidate code. A record is auto-assigned only when (a tunable threshold, typically 0.85); otherwise it routes to review.
Recoverability is a second, independent decision layered on top of the code. Once a GL code is assigned, the engine consults the lease matrix to split the amount into a recoverable component and an excluded component. A management fee coded 6900 might be 100% excluded, while a capital roof replacement coded 1700 is excluded from the operating pool but amortized into it over its useful life — a distinction that must be preserved as data, not folded away.
Python Implementation
The engine below is deterministic, typed, and uses Decimal for every monetary value — binary float cannot represent cent amounts exactly, and summed drift across thousands of line items produces pools that fail to tie out. String similarity uses difflib from the standard library (docs.python.org/3/library/difflib.html) to avoid a third-party dependency in the hot path.
from __future__ import annotations
from decimal import Decimal
from difflib import SequenceMatcher
from enum import Enum
from typing import Mapping
from pydantic import BaseModel, Field, field_validator
# --- Domain enums -----------------------------------------------------------
PENDING_REVIEW = "PENDING_REVIEW"
class GLCode(str, Enum):
"""Canonical chart-of-accounts codes recognised by the reconciliation."""
UTILITIES = "6100"
JANITORIAL = "6200"
LANDSCAPING = "6300"
SECURITY = "6400"
REPAIRS = "6500"
MANAGEMENT_FEE = "6900"
CAPITAL_IMPROVEMENT = "1700"
# --- Data contracts ---------------------------------------------------------
class LineItem(BaseModel):
"""A validated invoice line item entering the mapping stage."""
description: str = Field(min_length=1)
amount: Decimal
vendor_id: str
property_id: str
@field_validator("amount")
@classmethod
def _two_places(cls, v: Decimal) -> Decimal:
# Quantise to cents so downstream sums stay penny-exact.
return v.quantize(Decimal("0.01"))
class MappingResult(BaseModel):
"""The engine's output contract, one per line item."""
gl_code: str # a GLCode value or PENDING_REVIEW
confidence: Decimal
recoverable_amount: Decimal
excluded_amount: Decimal
method: str # "exact" | "vendor" | "fuzzy" | "review"
# --- Mapping engine ---------------------------------------------------------
class GLMapper:
"""Deterministic-first GL code mapping for CAM vendor line items.
Runs an exact synonym lookup, then a vendor-scoped historical default,
then fuzzy similarity, and finally routes low-confidence items to review.
"""
def __init__(
self,
synonyms: Mapping[str, GLCode],
vendor_history: Mapping[str, Mapping[GLCode, int]],
recoverable_pct: Mapping[tuple[str, GLCode], Decimal],
threshold: Decimal = Decimal("0.85"),
weights: tuple[Decimal, Decimal, Decimal] = (
Decimal("0.6"),
Decimal("0.25"),
Decimal("0.15"),
),
) -> None:
self._synonyms = {self._normalise(k): v for k, v in synonyms.items()}
self._vendor_history = vendor_history
self._recoverable_pct = recoverable_pct # (property_id, code) -> 0..1
self._threshold = threshold
self._w_exact, self._w_fuzzy, self._w_hist = weights
@staticmethod
def _normalise(text: str) -> str:
cleaned = "".join(c.lower() if c.isalnum() else " " for c in text)
return " ".join(cleaned.split())
def _history_share(self, vendor_id: str, code: GLCode) -> Decimal:
counts = self._vendor_history.get(vendor_id, {})
total = sum(counts.values())
if not total:
return Decimal("0")
return Decimal(counts.get(code, 0)) / Decimal(total)
def _best_fuzzy(self, norm_desc: str) -> tuple[GLCode | None, Decimal]:
best_code: GLCode | None = None
best_ratio = Decimal("0")
for key, code in self._synonyms.items():
ratio = Decimal(str(SequenceMatcher(None, norm_desc, key).ratio()))
if ratio > best_ratio:
best_code, best_ratio = code, ratio
return best_code, best_ratio
def _split(
self, item: LineItem, code: GLCode
) -> tuple[Decimal, Decimal]:
pct = self._recoverable_pct.get((item.property_id, code), Decimal("1"))
recoverable = (item.amount * pct).quantize(Decimal("0.01"))
return recoverable, (item.amount - recoverable)
def map(self, item: LineItem) -> MappingResult:
norm = self._normalise(item.description)
# 1. Exact synonym match -> confidence 1.0
code = self._synonyms.get(norm)
if code is not None:
recoverable, excluded = self._split(item, code)
return MappingResult(
gl_code=code.value,
confidence=Decimal("1.00"),
recoverable_amount=recoverable,
excluded_amount=excluded,
method="exact",
)
# 2/3. Blend fuzzy similarity with vendor history.
fuzzy_code, fuzzy_ratio = self._best_fuzzy(norm)
candidate = fuzzy_code
s_hist = (
self._history_share(item.vendor_id, candidate)
if candidate is not None
else Decimal("0")
)
confidence = (
self._w_fuzzy * fuzzy_ratio + self._w_hist * s_hist
).quantize(Decimal("0.01"))
if candidate is not None and confidence >= self._threshold:
recoverable, excluded = self._split(item, candidate)
method = "vendor" if s_hist > fuzzy_ratio else "fuzzy"
return MappingResult(
gl_code=candidate.value,
confidence=confidence,
recoverable_amount=recoverable,
excluded_amount=excluded,
method=method,
)
# 4. Below threshold -> never guess.
return MappingResult(
gl_code=PENDING_REVIEW,
confidence=confidence,
recoverable_amount=Decimal("0.00"),
excluded_amount=Decimal("0.00"),
method="review",
)The engine is intentionally boring: no network calls, no shared mutable state, and a pure map method that returns the same result for the same inputs. That determinism is what makes it testable and what lets an auditor reproduce any mapping decision months later. The broader classifier that sits behind the fuzzy path — including the NLP fallback for descriptions no synonym resembles — is developed in automating vendor invoice classification.
Validation Rules & Edge Cases
The mapping stage has its own failure taxonomy, distinct from extraction errors. Each of these is a real pattern seen in CRE vendor traffic, and each has a defined mitigation rather than a guess.
- Capital-versus-operating ambiguity.
Replace roof membranemaps cleanly to a code, but whether it is6500 Repairs & Maintenance(expensed in-year) or1700 Capital Improvements(amortized) depends on a lease-defined capitalization threshold and the asset’s useful life. The engine flags any amount above the property’s threshold on a repair code for capitalization review rather than silently expensing it. - Blended line items. A single line reading
Jan–Mar landscaping + irrigation repairspans two categories. The mapper must not average them; it routes the item to review so an accountant can split it, because a blended amount posted to one code corrupts both pools. - Missing vendor history for a new supplier. The vendor-scoped default contributes zero signal for a first-ever invoice, so a novel description from a novel vendor correctly falls through to review instead of borrowing another vendor’s history.
- Recoverability drift mid-year. If a lease amendment changes a category’s recoverable percentage partway through the reconciliation year, the split must use the percentage in force on the line item’s service date, not the invoice date — the same period-matching discipline the pipeline applies everywhere.
- Confidence clustering near the threshold. A backlog of items landing at 0.84–0.86 signals a synonym-dictionary gap, not a modeling problem. The mitigation is to promote recurring review-queue descriptions into the synonym dictionary, which moves them to the deterministic path permanently. This tuning discipline mirrors threshold tuning for allocation accuracy on the allocation side.
Records that route to PENDING_REVIEW are never dropped. They carry their raw description, source-document hash, and computed confidence into the review queue so an accountant resolves them without re-parsing the underlying invoice.
Integration Points
The mapping engine’s MappingResult is a hand-off contract, and three downstream consumers depend on its shape.
The reconciliation and allocation engine. The recoverable_amount field is what accumulates into each property’s recoverable pool, and that pool is the recoverable_expenses term in every tenant’s pro rata calculation. The mapper never computes the share itself — that is the job of the pro rata allocation algorithms in the Expense Allocation Logic & Rule Engines pillar — but the integrity of every share is bounded by the integrity of these mappings.
GL posting and the ERP. Assigned codes are posted as journal entries into platforms such as Yardi Voyager or MRI, mapped from the canonical chart to each platform’s account hierarchy. Only records with a real gl_code post; PENDING_REVIEW items are held back so an unresolved classification never reaches the general ledger.
The audit log. Every mapping decision is written to the append-only audit trail with its method, confidence, and the ruleset version that produced it, so a mapping can be replayed and explained against the exact synonym dictionary and lease matrix that were live at the time.
Testing & Verification
Mapping correctness is verified with table-driven fixtures that pin known descriptions to expected codes, plus invariants that guard the money math. Two properties matter most: the split must conserve the original amount to the cent, and a below-threshold item must never receive a real code.
from decimal import Decimal
def test_split_conserves_amount() -> None:
mapper = GLMapper(
synonyms={"common area maint": GLCode.JANITORIAL},
vendor_history={},
recoverable_pct={("prop-1", GLCode.JANITORIAL): Decimal("0.80")},
)
item = LineItem(
description="Common Area Maint",
amount=Decimal("1000.00"),
vendor_id="v-1",
property_id="prop-1",
)
result = mapper.map(item)
assert result.gl_code == GLCode.JANITORIAL.value
assert result.method == "exact"
# No cent is lost or invented in the recoverable/excluded split.
assert result.recoverable_amount + result.excluded_amount == item.amount
assert result.recoverable_amount == Decimal("800.00")
def test_unknown_description_routes_to_review() -> None:
mapper = GLMapper(
synonyms={"common area maint": GLCode.JANITORIAL},
vendor_history={},
recoverable_pct={},
)
item = LineItem(
description="zzz unrecognisable vendor note",
amount=Decimal("500.00"),
vendor_id="new-vendor",
property_id="prop-1",
)
result = mapper.map(item)
assert result.gl_code == PENDING_REVIEW
assert result.recoverable_amount == Decimal("0.00")Because all arithmetic is Decimal quantized to two places, equality assertions on money are exact — there is no floating-point tolerance to reason about. The conservation invariant (recoverable + excluded == amount) is the single most valuable check in the suite: if it ever fails, a pool is silently gaining or losing money, and the reconciliation will not tie out.
Frequently Asked Questions
Why is mapping deterministic-first instead of a single ML classifier? An auditor asks why a line item landed in a pool, and “the model predicted it” is not an answer that survives review. Exact synonym and rule matches carry an explicit, reproducible reason; machine learning is a fallback for genuinely novel descriptions, and even then its output routes through the same confidence gate and review queue.
What is the difference between a GL code and its recoverability? The GL code is a chart-of-accounts fact — what kind of expense this is. Recoverability is a lease fact — whether that expense can be billed back to tenants, and at what percentage. The same code can be fully recoverable under one lease and excluded under another, which is why the engine reads recoverability from the lease abstraction database rather than the chart.
How does the engine decide between Repairs & Maintenance and Capital Improvements? It assigns the base code from the description, then checks the amount against the property’s lease-defined capitalization threshold. A repair-coded line above that threshold is flagged for capitalization review rather than expensed in-year, because misclassifying a capital replacement into the operating pool overstates recoverable CAM.
What happens to a line item the mapper cannot classify confidently?
It is assigned the sentinel PENDING_REVIEW, never a guessed code, and routes to a review queue carrying its raw description, source-document hash, and confidence score. It contributes zero to every pool until an accountant resolves it, so an uncertain mapping can never quietly distort a reconciliation.
Why must monetary amounts use Decimal rather than float?
Binary floating point cannot represent most cent values exactly, so summing thousands of float amounts drifts by fractions of a cent and produces pools that fail to tie out. Decimal, quantized to two places, keeps the recoverable-versus-excluded split penny-exact and the audit math reproducible.
Where This Fits
GL code mapping is the control point where unstructured vendor language becomes structured, recoverable, audit-ready expense data. By running a deterministic synonym-and-rule cascade first, gating everything else on an explicit confidence threshold, and splitting each amount against lease-defined recoverability before it ever reaches a pool, the engine produces mappings that an accountant can defend line by line. It sits downstream of schema validation for parsed expense data, leans on automating vendor invoice classification for its novel-description fallback, and feeds the pro rata allocation algorithms that turn recoverable pools into tenant statements — the reconciliation’s integrity riding on the correctness of every code it assigns.
Related
- Automated Invoice Parsing & Data Ingestion — the parent pipeline this mapping stage belongs to, from raw document to reconciled GL entry.
- Automating vendor invoice classification — the regex, fuzzy, and NLP classifier behind the mapper’s fallback path.
- Schema validation for parsed expense data — the upstream Pydantic gate that guarantees the records this engine consumes.
- Defining CAM expense categories in commercial leases — where the recoverable categories these codes map into are defined by lease language.
- Implementing pro rata allocation algorithms — the downstream engine that turns mapped recoverable pools into tenant shares.