Mapping Legacy CAM Categories to a Standard Chart of Accounts
When two properties you acquired last year still book sweeping to MISC-CAM-07 and JAN-999 while your target ledger expects a single account 6200, no reconciliation can roll up until those legacy strings are resolved onto one authoritative account list. Mapping legacy CAM categories to a standard chart of accounts is the crosswalk exercise that retires each inherited, property-specific code and binds it to a canonical account you control, and it is the groundwork step within standardizing CAM taxonomies across portfolios. This page builds that crosswalk end to end: an inventory sweep of every legacy code in use, a versioned crosswalk table as the system of record, a deterministic mapper with a fuzzy-match assist that only ever suggests, an unmapped queue that blocks silent guessing, and an apply-and-version step that stamps each posting with the mapping revision it was produced under. The concern here is strictly the account-to-account translation — reconciling the codes that different property-management systems emit for the same concept is the separate job of normalizing CAM codes across property management systems.
Context & When to Use This Approach
Reach for a formal crosswalk the moment a portfolio stops being a single ledger. A property acquired mid-year arrives with a chart of accounts its prior manager built for their own reporting — abbreviations, numeric prefixes, and one-off buckets like CAM-OTHER that meant something only to the person who created them. Left in place, those codes make portfolio-level roll-ups impossible: you cannot sum landscaping across ten buildings when four of them call it LNDS, three call it 6300, and three fold it into a generic GROUNDS-CAM. The crosswalk is the durable artifact that lets every legacy code keep flowing in unchanged while every downstream report reads a single account.
This approach is deliberately narrow, and its boundary matters. A crosswalk translates one account list to another — it answers “what does the target ledger call the thing this legacy code named?” It does not decide what a raw vendor description means; that free-text classification is handled upstream, and it does not reconcile the differing code formats emitted by Yardi versus MRI for an identical concept, which is why the account translation here stays distinct from system-level normalizing CAM codes across property management systems. Use a crosswalk specifically when the source and target are both finite, curated account lists and you need every mapping decision to be auditable, reversible, and explainable to a tenant who disputes how their share was categorized. The same discipline underpins GL code mapping for CAM expenses, which consumes the canonical accounts this crosswalk produces.
Step-by-Step Implementation
The crosswalk is built in five steps. Because these accounts ultimately carry recoverable dollars into tenant statements, every monetary rollup that references a mapped account uses the decimal module — binary float cannot represent cent amounts exactly, and drift across a portfolio of postings produces recovery pools that fail to tie out. The canonical account codes themselves mirror the categories set in the lease abstraction database.
Step 1 — Inventory every legacy code in use
You cannot map codes you have not seen. Sweep every source ledger and collect each distinct legacy code alongside its human label, the properties it appears on, and how much money has posted to it — the last figure tells you which mappings are material and worth an analyst’s attention first.
from __future__ import annotations
from collections import defaultdict
from dataclasses import dataclass, field
from decimal import Decimal
from typing import Iterable
@dataclass
class LegacyPosting:
"""A single row read from an acquired property's source ledger."""
property_id: str
legacy_code: str
legacy_label: str
amount: Decimal
@dataclass
class LegacyCodeStat:
"""Aggregated evidence for one distinct legacy code."""
legacy_code: str
labels: set[str] = field(default_factory=set)
properties: set[str] = field(default_factory=set)
total_amount: Decimal = Decimal("0.00")
def inventory(postings: Iterable[LegacyPosting]) -> list[LegacyCodeStat]:
"""Collapse raw postings into one stat row per distinct legacy code."""
stats: dict[str, LegacyCodeStat] = defaultdict(
lambda: LegacyCodeStat(legacy_code="")
)
for row in postings:
stat = stats[row.legacy_code]
stat.legacy_code = row.legacy_code
stat.labels.add(row.legacy_label.strip())
stat.properties.add(row.property_id)
stat.total_amount = (stat.total_amount + row.amount).quantize(Decimal("0.01"))
# Materiality first: the largest pools get mapped and reviewed soonest.
return sorted(stats.values(), key=lambda s: s.total_amount, reverse=True)The inventory is a report, not a mapping. It surfaces the surprises — the same MISC-CAM code carrying two contradictory labels across two properties — before any translation is attempted.
Step 2 — Build the crosswalk table as the system of record
The crosswalk is a versioned list of explicit legacy-to-canonical entries owned by accounting, never inferred at runtime. Model the canonical accounts as an enum so a typo can never invent an account, and give each crosswalk a monotonically increasing revision number so any posting can later name the exact mapping it was produced under.
from enum import Enum
from pydantic import BaseModel, Field
class CanonicalAccount(str, Enum):
"""The one standard chart of accounts every property maps onto."""
UTILITIES = "6100"
JANITORIAL = "6200"
LANDSCAPING = "6300"
SECURITY = "6400"
REPAIRS = "6500"
MANAGEMENT_FEE = "6900"
CAPITAL_IMPROVEMENT = "1700"
class CrosswalkEntry(BaseModel):
"""One authoritative legacy-code-to-canonical-account binding."""
legacy_code: str = Field(min_length=1)
canonical: CanonicalAccount
note: str = ""
class Crosswalk(BaseModel):
"""An immutable, versioned crosswalk revision."""
revision: int = Field(ge=1)
entries: dict[str, CanonicalAccount]
@classmethod
def from_entries(cls, revision: int, rows: list[CrosswalkEntry]) -> "Crosswalk":
return cls(
revision=revision,
entries={r.legacy_code.upper(): r.canonical for r in rows},
)Step 3 — Map deterministically, with a fuzzy assist that only suggests
The mapper runs an exact lookup first: a legacy code present in the crosswalk resolves with full certainty and no ambiguity. Only on a miss does a fuzzy comparison run — and crucially, it produces a suggestion for a human, never an applied mapping. The rapidfuzz scorer compares the legacy label against known canonical labels and rescales its 0–100 output into a Decimal so the confidence composes cleanly with the threshold.
from rapidfuzz import fuzz
# Human-readable names for each canonical account, used only for suggestion.
CANONICAL_LABELS: dict[CanonicalAccount, str] = {
CanonicalAccount.UTILITIES: "utilities electricity water",
CanonicalAccount.JANITORIAL: "janitorial cleaning sweeping",
CanonicalAccount.LANDSCAPING: "landscaping grounds irrigation",
CanonicalAccount.SECURITY: "security patrol guard",
CanonicalAccount.REPAIRS: "repairs maintenance hvac",
CanonicalAccount.MANAGEMENT_FEE: "management administrative fee",
CanonicalAccount.CAPITAL_IMPROVEMENT: "capital replacement improvement",
}
SUGGEST_THRESHOLD = Decimal("0.60")
def suggest(legacy_label: str) -> tuple[CanonicalAccount | None, Decimal]:
"""Suggest the closest canonical account for an unmapped legacy label."""
best_account: CanonicalAccount | None = None
best_score = Decimal("0")
for account, label in CANONICAL_LABELS.items():
raw = fuzz.token_set_ratio(legacy_label.lower(), label) # 0..100
score = (Decimal(str(raw)) / Decimal("100")).quantize(Decimal("0.01"))
if score > best_score:
best_account, best_score = account, score
if best_score < SUGGEST_THRESHOLD:
return None, best_score
return best_account, best_scoreThe fuzzy score never auto-applies. It exists purely to pre-fill the accountant’s confirmation screen so a reviewer clears a queue of proposals rather than typing account numbers from a blank field.
Step 4 — Route every unmatched code to an unmapped queue
A legacy code absent from the crosswalk must never be silently dropped or guessed into a default bucket — either choice quietly corrupts a recovery pool. Resolution returns a typed result that either carries a confirmed canonical account or enqueues the code for confirmation, and the queue item ships with the fuzzy suggestion attached so review is a decision, not research.
from dataclasses import dataclass
@dataclass
class Resolved:
legacy_code: str
canonical: CanonicalAccount
revision: int
@dataclass
class Unmapped:
legacy_code: str
legacy_label: str
suggestion: CanonicalAccount | None
suggestion_confidence: Decimal
def resolve(
legacy_code: str,
legacy_label: str,
crosswalk: Crosswalk,
) -> Resolved | Unmapped:
"""Resolve a legacy code deterministically, or queue it for confirmation."""
hit = crosswalk.entries.get(legacy_code.upper())
if hit is not None:
return Resolved(legacy_code=legacy_code, canonical=hit, revision=crosswalk.revision)
account, confidence = suggest(legacy_label)
return Unmapped(
legacy_code=legacy_code,
legacy_label=legacy_label,
suggestion=account,
suggestion_confidence=confidence,
)Step 5 — Apply confirmed mappings and version the result
When an accountant confirms an unmapped code, the decision is folded into a new crosswalk revision rather than mutating the current one. The prior revision stays intact so any statement produced under it remains reproducible, and every posting rewritten by the mapper is stamped with the revision that governed it — a hashlib fingerprint over the entry set gives you a tamper-evident label for that revision.
import hashlib
def add_confirmation(
current: Crosswalk,
legacy_code: str,
canonical: CanonicalAccount,
) -> Crosswalk:
"""Return a new revision with one confirmed mapping added."""
merged = dict(current.entries)
merged[legacy_code.upper()] = canonical
return Crosswalk(revision=current.revision + 1, entries=merged)
def revision_fingerprint(crosswalk: Crosswalk) -> str:
"""Deterministic hash over a crosswalk revision for audit stamping."""
payload = ";".join(
f"{code}={account.value}"
for code, account in sorted(crosswalk.entries.items())
)
digest = hashlib.sha256(payload.encode("utf-8")).hexdigest()
return f"xw-{crosswalk.revision}-{digest[:12]}"Stamping each posting with revision_fingerprint closes the loop: a tenant dispute months later can be traced to the precise mapping revision in force when the charge was categorized, and re-running the mapper against a superseded revision reproduces the original result exactly.
Gotchas & Known Limitations
- A legacy code is not always one canonical account. An inherited
CAM-OTHERbucket may hide landscaping and security dollars mixed together. A one-to-one crosswalk cannot split it; flag any high-total code whose labels disagree across properties for manual decomposition at the posting level before mapping. - Never map to a default account to empty the queue. Dumping unmatched codes into a
MISCcanonical account makes the queue look clean while silently misstating recoveries. An unmapped code must stay unmapped until a human confirms it. - The fuzzy suggestion is advisory only.
token_set_ratiowill happily scoreCAM-MGTagainst both management and maintenance labels. Auto-applying it would post a management fee as a repair. The Step 4 boundary — suggest, never assign — is load-bearing. - Case and whitespace fracture exact matches.
jan-999andJAN-999are the same code to a human and different keys to a dict. Normalize to upper-case and strip before both storing and looking up, as the entry constructor does. - Mutating a live crosswalk breaks reproducibility. If you edit the current revision in place, every prior statement that cited it silently changes meaning. Only ever append a new revision, and keep the old ones immutable.
rapidfuzzreturns integers 0–100. Blending or thresholding that raw score against aDecimalin[0, 1]without rescaling pins every suggestion above the bar. The rescale in Step 3 must not be dropped.
Verification
Crosswalk correctness is pinned with table-driven fixtures: known legacy codes must resolve to their expected canonical account, and any code absent from the crosswalk must return an Unmapped result rather than a fabricated mapping. Because the canonical accounts are an enum and confidence is a quantized Decimal, equality assertions are exact and there is no floating-point tolerance to reason about.
from decimal import Decimal
def _sample_crosswalk() -> Crosswalk:
return Crosswalk.from_entries(
revision=1,
rows=[
CrosswalkEntry(legacy_code="JAN-999", canonical=CanonicalAccount.JANITORIAL),
CrosswalkEntry(legacy_code="LNDS", canonical=CanonicalAccount.LANDSCAPING),
],
)
def test_known_code_resolves_deterministically() -> None:
result = resolve("jan-999", "Common area sweeping", _sample_crosswalk())
assert isinstance(result, Resolved)
assert result.canonical == CanonicalAccount.JANITORIAL
assert result.revision == 1
def test_unknown_code_is_queued_not_guessed() -> None:
result = resolve("MISC-CAM-07", "grounds irrigation service", _sample_crosswalk())
assert isinstance(result, Unmapped)
assert result.suggestion == CanonicalAccount.LANDSCAPING # suggested, not applied
def test_confirmation_produces_a_new_immutable_revision() -> None:
base = _sample_crosswalk()
updated = add_confirmation(base, "MISC-CAM-07", CanonicalAccount.LANDSCAPING)
assert updated.revision == 2
assert "MISC-CAM-07" in updated.entries
assert "MISC-CAM-07" not in base.entries # prior revision untouchedBeyond unit fixtures, cross-check the crosswalk against a reconciled prior period: rewrite a closed quarter’s postings through the mapper and confirm the canonical roll-ups equal the numbers the source ledgers reported for the same accounts. A mismatch means a legacy code split across canonical accounts, or an unmapped code that leaked into a total. Track the unmapped-queue depth over time — a healthy portfolio drives it toward zero as recurring codes are confirmed into the crosswalk, and a sudden rise is the early signal that a newly acquired property arrived with an unfamiliar account list.
With every legacy code now resolved to your standard chart of accounts, the portfolio is ready for the broader consolidation described in standardizing CAM taxonomies across portfolios; the next reconciliation concern is format-level unification, covered in normalizing CAM codes across property management systems.
Related
- Standardizing CAM taxonomies across portfolios — the portfolio-wide standardization effort this crosswalk is the first concrete step of.
- Normalizing CAM codes across property management systems — the sibling task of reconciling differing code formats emitted by Yardi, MRI, and other systems for the same concept.
- GL code mapping for CAM expenses — the downstream stage that consumes the canonical accounts this crosswalk produces to split recoverable from excluded costs.