Currency & FX Normalization: Decimal-Exact Multi-Currency Cost Ingestion

An international shoot spends money in the currency of wherever the camera happens to be that week — pounds for a London stage, euros for a Prague backlot, dollars for a domestic post house — and the cost report a completion guarantor reviews has to state every one of those lines in a single reporting currency. The naive version of this problem is a one-line multiplication; the defensible version is a normalization subsystem that resolves an exchange rate by the date a cost was actually incurred, converts it with fixed-point arithmetic that reproduces to the cent, and preserves the original amount and the rate that was applied so an examiner can reconstruct the figure months later. Get the resolution wrong and a location day gets priced at the wrong week’s rate; get the arithmetic wrong and cent-level drift compounds across thousands of foreign lines until the reporting-currency total no longer ties to its source. This document specifies that subsystem — its models, its rate-resolution logic, its production-grade Python, and the quarantine and verification mechanics that make it auditable — as one part of the Cost Ingestion & Data Parsing Workflows reference architecture.

Prerequisites and Expected Inputs

The implementation targets Python 3.11+, both for the standard-library zoneinfo module and for modern type-union syntax. It leans on a deliberately narrow stack: Pydantic v2 for boundary schema validation through model_validate and field_validator, as documented in the Pydantic v2 reference; the standard-library decimal, hashlib, and zoneinfo modules for currency-safe arithmetic, deterministic snapshot hashing, and timezone-aware date resolution; and, at production scale, polars or SQLAlchemy to stream cost lines and persist an append-only ledger of normalized entries. Money is never a float. As the Python decimal module documentation makes explicit, only fixed-point arithmetic delivers the deterministic rounding a reporting-currency figure requires; a binary-float rate multiplied across a season of foreign invoices drifts by amounts a guarantor’s auditor will ask a production accountant to explain.

This subsystem assumes its inputs arrive already parsed and cost-coded. Foreign cost lines do not originate here: heterogeneous vendor exports, bank feeds, and payroll files are cleaned upstream by the sibling workflows in this section, where the strict boundary contract of Schema Validation & Error Handling rejects malformed rows before they reach any converter, and each line is keyed to exactly one budget line through Cost Code Standardization. The single input the converter consumes is a validated CostLine — a cost code, an ISO 4217 currency, an original Decimal amount, and the date the cost was incurred. The two things it consumes alongside that line are a resolved PinnedRate and the reporting currency of the production. Its output is an immutable NormalizedLine carrying the original amount, the rate applied, the converted amount, and a SHA-256 fingerprint of the record that produced it. The mechanics of resolving that rate are worked end to end in Sourcing and Pinning Daily FX Rates for Reproducible Conversion, and the focused conversion arithmetic in Normalizing Multi-Currency Cost Reports with Decimal.

Architecture: Resolve by Date, Convert Once, Preserve the Original

The design error that sinks a home-grown converter is looking the rate up live at the moment the report is generated. A live lookup means the same cost line converts to a different figure depending on the wall-clock instant the report ran — which is the precise property a completion guarantor cannot accept, because it makes a cost report irreproducible. The correct model is a pure function over three inputs: the foreign cost line, the reporting currency, and a pinned rate resolved by the line’s work date. Same line, same snapshot, same output, every time.

The pipeline validates the line at the boundary, resolves the applicable rate from an immutable daily snapshot keyed by currency and work date, converts in Decimal and quantizes to cents, and emits a normalized entry that carries the original amount, the rate, and the snapshot provenance. A line whose currency and date have no pinned rate is not converted at a guessed number — it is quarantined.

Multi-currency cost-line normalization pipeline A validated foreign cost line carrying an amount and an ISO 4217 currency enters a rate-resolution step that looks up a pinned daily rate by currency and work date. A decision asks whether a rate was pinned for that date. If yes, the amount is converted in Decimal and quantized to cents, then emitted as an immutable normalized cost entry carrying the original amount, the applied rate, the converted amount, and a SHA-256 hash. If no rate is pinned, the line follows a dashed path to a missing-rate quarantine that alerts a supervisor rather than converting at a guessed rate. Foreign cost line amount + currency Resolve pinned rate currency + work date Rate pinned? for that date yes Convert in Decimal quantize to cents Normalized entry original + rate converted · SHA-256 no rate Missing-rate quarantine alert supervisor · never guess

Two properties make this defensible rather than merely convenient. First, resolution is total: a line either resolves to a pinned rate for its own work date or it is quarantined, so a currency the snapshot has never carried never silently converts at zero or at a neighbouring currency’s rate. Second, the original amount is never thrown away. The NormalizedLine stores the foreign amount and the applied rate beside the converted figure, so a reporting-currency total can always be decomposed back into the source lines a vendor was actually paid — which is what lets a production accountant answer “why is this line $4,812.55?” with a rate, a date, and a source rather than a shrug. This normalization runs in the same non-blocking worker pool described in Async Batch Processing for Multi-Currency Shoots, so a season’s worth of foreign lines can be converted against a pinned snapshot without a live rate lookup ever entering the loop.

Core Implementation

The reference converter models the foreign cost line and the resolved rate as frozen Pydantic v2 objects validated at the boundary, resolves the rate from an immutable snapshot keyed by (currency, work_date), converts in Decimal, and writes a hashed provenance entry for every line. Monetary fields are Decimal; the reporting currency and the snapshot hash travel on every output so no figure is orphaned from the rate that produced it.

import hashlib
import json
import logging
from datetime import date, datetime
from decimal import Decimal, ROUND_HALF_UP
from typing import Any
from zoneinfo import ZoneInfo

from pydantic import BaseModel, ConfigDict, Field, field_validator

logger = logging.getLogger("fx_normalization")

CENTS = Decimal("0.01")


class CostLine(BaseModel):
    """Boundary schema for one foreign-currency cost line entering the converter."""
    model_config = ConfigDict(frozen=True)

    line_id: str
    production_id: str
    cost_code: str
    currency: str                 # ISO 4217, e.g. "GBP"
    amount: Decimal = Field(gt=Decimal("0"))
    work_date: date               # date the cost was incurred (shoot-local)

    @field_validator("amount", mode="before")
    @classmethod
    def reject_float(cls, v: Any) -> Decimal:
        # Reject float amounts outright; parse strings/ints exactly.
        if isinstance(v, float):
            raise ValueError("amount must be str/Decimal, never float")
        return Decimal(str(v))

    @field_validator("currency")
    @classmethod
    def normalize_iso(cls, v: str) -> str:
        if len(v) != 3 or not v.isalpha():
            raise ValueError("currency must be a 3-letter ISO 4217 code")
        return v.upper()


class PinnedRate(BaseModel):
    """One immutable rate from a pinned daily snapshot: base per 1 unit of quote."""
    model_config = ConfigDict(frozen=True)

    base_currency: str            # the production's reporting currency
    quote_currency: str           # the foreign currency being converted
    rate: Decimal = Field(gt=Decimal("0"))
    rate_date: date
    source_id: str                # which published snapshot this came from
    snapshot_hash: str            # SHA-256 of the pinned table


class NormalizedLine(BaseModel):
    model_config = ConfigDict(frozen=True)

    line_id: str
    reporting_currency: str
    original_currency: str
    original_amount: Decimal
    fx_rate: Decimal
    rate_date: str
    converted_amount: Decimal
    source_id: str
    snapshot_hash: str
    payload_hash: str
    computed_at: str
    operator_id: str = "SYS_AUTO"


class FxRateTable:
    """An immutable, pinned daily snapshot resolved by (currency, work date)."""

    def __init__(self, reporting_currency: str, rates: list[PinnedRate],
                 snapshot_hash: str, source_id: str) -> None:
        self.reporting_currency = reporting_currency.upper()
        self.snapshot_hash = snapshot_hash
        self.source_id = source_id
        self._by_key = {(r.quote_currency, r.rate_date): r for r in rates}

    def resolve(self, currency: str, on_date: date) -> PinnedRate | None:
        # A line already in the reporting currency resolves to an identity rate.
        if currency == self.reporting_currency:
            return PinnedRate(
                base_currency=self.reporting_currency, quote_currency=currency,
                rate=Decimal("1"), rate_date=on_date,
                source_id=self.source_id, snapshot_hash=self.snapshot_hash,
            )
        return self._by_key.get((currency, on_date))


class NormalizationEngine:
    def __init__(self, table: FxRateTable) -> None:
        self.table = table
        self.ledger: list[NormalizedLine] = []
        self.quarantine: list[dict] = []

    def _hash(self, line: CostLine, rate: PinnedRate) -> str:
        # Canonical serialization so identical (line, rate) pairs hash identically.
        material = json.dumps(
            {"line": line.model_dump(mode="json"),
             "rate": rate.model_dump(mode="json")},
            sort_keys=True, separators=(",", ":"),
        ).encode("utf-8")
        return hashlib.sha256(material).hexdigest()

    def normalize(self, line: CostLine) -> NormalizedLine | None:
        rate = self.table.resolve(line.currency, line.work_date)
        if rate is None:
            self._quarantine(line, "no pinned rate for currency/work_date")
            return None

        # Convert once, quantize to cents at the payable point.
        converted = (line.amount * rate.rate).quantize(
            CENTS, rounding=ROUND_HALF_UP
        )
        entry = NormalizedLine(
            line_id=line.line_id,
            reporting_currency=self.table.reporting_currency,
            original_currency=line.currency,
            original_amount=line.amount,
            fx_rate=rate.rate,
            rate_date=rate.rate_date.isoformat(),
            converted_amount=converted,
            source_id=rate.source_id,
            snapshot_hash=rate.snapshot_hash,
            payload_hash=self._hash(line, rate),
            computed_at=datetime.now(tz=ZoneInfo("UTC")).isoformat(),
        )
        self.ledger.append(entry)
        logger.info(
            "line=%s %s %s -> %s %s @ %s hash=%s",
            entry.line_id, entry.original_amount, entry.original_currency,
            entry.converted_amount, entry.reporting_currency,
            entry.fx_rate, entry.payload_hash,
        )
        return entry

    def _quarantine(self, line: CostLine, reason: str) -> None:
        payload = line.model_dump(mode="json")
        material = json.dumps(payload, sort_keys=True,
                              separators=(",", ":")).encode("utf-8")
        self.quarantine.append({
            "payload": payload,
            "payload_hash": hashlib.sha256(material).hexdigest(),
            "reason_code": reason,
            "quarantined_at": datetime.now(tz=ZoneInfo("UTC")).isoformat(),
            "requires_manual_override": True,
        })
        logger.warning("Quarantined %s: %s", line.line_id, reason)

The frozen=True config makes both the incoming line and the emitted entry immutable once validated, so no downstream caller can adjust a rate after it has been hashed. Because the fingerprint is taken over the canonical serialization of the line and the rate together — sorted keys, no incidental whitespace — the SHA-256 is stable and meaningful: two runs over the same line against the same snapshot produce the same hash, and swapping in a different rate changes it. That coupling is deliberate. It means the hash does not merely prove the input was unaltered; it proves which rate was applied, which is exactly the question a completion guarantor’s reconciliation asks first.

Bond Reporting Specifics

Currency normalization is not an internal convenience — it is the layer that makes a cross-border cost report legible to the people who underwrite the picture. A completion guarantor (the completion-bond company that guarantees delivery to financiers) reviews cost against budget in one reporting currency, and every foreign line on that report has to be traceable to the rate and the day that produced it. The normalized entries this subsystem emits feed directly into the Completion Bond Reporting & Guarantor Analytics layer, where cost-to-complete projections and guarantor-facing variance reports are computed on the reporting-currency figures. Three properties of the model exist specifically to survive that scrutiny.

  • The original amount is load-bearing, not decorative. When a guarantor’s auditor questions a variance, the answer has to reconstruct from the source: this vendor was paid £3,900 on this date, the pinned rate that day was 1.2340, so the reporting-currency figure is $4,812.60. Storing only the converted amount throws away the two facts that make the number defensible.
  • The rate is pinned by work date, not report date. A location block that ran in April is priced at April’s rates even if the report is assembled in July. Pricing April costs at July’s rate is the single most common way a cross-border cost report drifts from the ledger it claims to summarize, and it is the drift a guarantor is most practiced at catching.
  • The snapshot is versioned and hashed. Because every normalized entry carries the snapshot_hash and source_id, a report built last quarter can be reproduced against the exact rate table it used, rather than against whatever the current table happens to say. That reproducibility is the property that lets a producer defend a settled cost report rather than re-derive it under a lender’s clock.

A production that treats FX conversion as reproducible, source-pinned arithmetic gives the guarantor a report it can verify independently; a production that converts live gives it a report it can only take on faith, and faith is not what releases a reserve.

Error Handling and Quarantine

Not every line should resolve to a converted figure. The rule mirrors the boundary discipline the rest of this section enforces: resolution handles rate availability; quarantine handles record validity and coverage. A line whose currency and work date have no pinned rate, whose currency is not a valid ISO 4217 code, or which carries a non-positive or float amount is not a candidate for conversion — it is quarantined. The distinction that matters operationally is between a line that is wrong and a line that is merely not yet coverable. A malformed line is a validity failure and belongs in the same dead-letter contract defined by Schema Validation & Error Handling. A well-formed line in a currency the snapshot simply does not yet carry — a weekend date before the rate published, a newly added shooting territory — is a coverage gap: it must never be converted at a guessed rate, but it can often be resolved cleanly once the correct pinned snapshot lands.

Conversion versus quarantine for an incoming cost line An incoming cost line hits a coverage gate that asks whether a pinned rate exists for its currency and work date. If yes, the line is converted in Decimal and appended to the append-only, SHA-256 stamped normalized ledger. If no, a second gate distinguishes a malformed line — an invalid currency code or a float amount — which is routed to the dead-letter quarantine, from a well-formed but not-yet-covered line, which is held for a corrected snapshot. A barred dashed link shows that a quarantined line never enters the normalized ledger at a guessed rate. Incoming cost line amount + currency + work date Rate? Normalized ledger append-only · SHA-256 yes no Malformed? code / float Dead-letter quarantine payload · hash · reason_code Coverage hold await corrected snapshot yes no

Every quarantine event serializes the original payload verbatim, attaches the SHA-256 hash of that payload, records a machine-readable reason_code, and pushes the record to a reconciliation queue for human triage. The failing line never enters the normalized ledger as a converted figure — it enters a separate exception store, so the ledger stays clean while the discrepancy is preserved. Reprocessing is then safe and idempotent: once a corrected or extended snapshot is pinned, a held line re-resolves to exactly one rate and produces exactly one normalized entry, because the payload hash deduplicates any line that was already converted on an earlier pass.

Verification

A normalization subsystem is only trustworthy if you can prove, after the fact, which rate and which snapshot produced every converted figure. Verification checks three artifacts rather than one.

First, the normalized entry. Every converted line must produce exactly one NormalizedLine whose original_amount and original_currency echo the source, whose fx_rate and rate_date name the rate applied, whose snapshot_hash and source_id pin the table it came from, whose converted_amount is a Decimal quantized to cents, and whose payload_hash matches an independent re-hash of the line-and-rate pair. Re-running the same line against the same snapshot must yield an identical hash and an identical converted amount — the idempotency check that lets a disputed period be replayed deterministically.

Second, the audit log fields. Each conversion must log, at minimum: line_id, production_id, original_amount, original_currency, fx_rate, rate_date, converted_amount, reporting_currency, snapshot_hash, a UTC computed_at, and an operator_id (SYS_AUTO for automated runs, a real identifier for manual overrides). A guarantor’s auditor reads this sequence as the provenance of the number, so no field is optional.

Third, the reconciliation report shape. A period report should surface, per currency: the count and reporting-currency aggregate of lines converted, the count still held for a missing snapshot, and every line quarantined for a validity failure. A healthy run is fully converted with a short, explained coverage tail; a run heavy with holds signals that a rate snapshot needs to be sourced before the report is trusted. A minimal harness confirms the invariants:

def verify(entry: NormalizedLine, engine: NormalizationEngine,
           line: CostLine) -> None:
    replay = engine.normalize(line)
    assert replay is not None, "covered line must resolve to a normalized entry"
    assert replay.payload_hash == entry.payload_hash, "non-deterministic hash"
    assert replay.converted_amount == entry.converted_amount, "non-deterministic total"
    assert isinstance(entry.converted_amount, Decimal), "money must be Decimal"
    assert entry.snapshot_hash, "every entry must name the snapshot it used"

Engineered with date-keyed rate resolution, Decimal-exact conversion, snapshot-versioned provenance, and coverage-aware quarantine, currency and FX normalization turns the messiest line on an international cost report — a foreign amount nobody can price twice the same way — into a controlled, reproducible figure: totals do not drift, the completion guarantor’s scrutiny is satisfied, and every converted line remains traceable to the original amount, the exact rate, and the day the money was actually spent.

Up: Cost Ingestion & Data Parsing Workflows