Normalizing Currency and Timezones in Multi-Cloud Billing

A EUR-denominated AWS invoice line, a GBP Azure meter, and a USD Cloud SQL charge cannot be summed until every amount is converted to one base currency at the rate that was true on the day it was incurred, and every timestamp is collapsed into the same timezone-aware UTC day — this page builds that converter in real Python.

Back to: Multi-Cloud Cost Normalization

Two independent axes have to be aligned before a cross-provider total means anything. The first is money: providers bill in the currency of your billing account, so a global estate produces line items in several currencies at once, and a naive sum adds euros to pounds. The second is time: usage windows and billing periods arrive in mixed zones and offset formats, and a daily rollup that buckets them inconsistently double-counts one day and drops another. This converter sits directly downstream of mapping provider SKUs to canonical compute units — once capacity is comparable, currency and time are the remaining barriers to a single ledger — and it assumes each record already passed schema validation for billing data, so the currency code is a well-typed ISO string and the cost is Decimal, not a JSON float.

The conversion itself is one multiplication, but the correctness lives in choosing the right rate for the right day:

base_amount=amount×rate(currency, utc_day)\text{base\_amount} = \text{amount} \times \text{rate}\big(\text{currency},\ \text{utc\_day}\big)

where rate(currency, utc_day) is the FX rate that was in effect on or before the UTC day the usage fell into — never today’s rate, and never a rate published after the fact.

Prerequisites

The converter runs offline against a rate table and the standard library; the only external dependency is whatever sources your FX rates and billing exports in the first place.

  • A trusted FX rate source. Reconciliation-grade conversion uses the same reference rates your finance team books against — typically a daily fixing such as the ECB reference rate or your treasury system’s close-of-day rate. Persist those as a dated series (see Step 1); do not call a live spot-rate API at report time, because that reintroduces the look-ahead bias this whole page exists to avoid.

  • Read access to the billing exports that carry the currency and period fields. On AWS that is the Cost and Usage Report lineItem/CurrencyCode and lineItem/UsageStartDate; on Azure the Cost Management billingCurrency and date; on GCP the BigQuery billing export currency and usage_start_time. Sourcing those follows the least-privilege posture in security and access control for cost data.

  • Python: 3.9 or newer — zoneinfo is in the standard library from 3.9, so no third-party timezone package is required.

  • Libraries: the batch join uses pandas; everything else (decimal, datetime, zoneinfo, bisect, dataclasses) is standard library.

    pip install "pandas>=2.1" "tzdata>=2024.1"   # tzdata only needed on slim/Windows images

Step-by-Step Implementation

The design keeps money and time strictly separate until the final assembly. A dated FX table answers “what was the rate on this day”; a timestamp canonicalizer answers “which UTC day is this”; and only then does the assembler multiply the Decimal amount by the as-of rate for that day. Nothing touches float, and the UTC day — not the wall-clock date on the record — is what selects the rate.

Step 1 — Model Decimal money and a dated FX rate table

Store each currency as a series of (effective_date, rate) pairs sorted ascending, where the rate is the price of one unit of that currency in the base currency. A rate holds until the next dated entry supersedes it, which mirrors how daily reference rates actually publish. The as-of lookup uses bisect to find the rightmost effective date at or before the requested day, so it can never select a rate that had not yet been published.

import bisect
from datetime import date
from decimal import Decimal, getcontext

getcontext().prec = 28  # headroom for intermediate FX products

BASE_CURRENCY = "USD"

# rate = price of 1 unit of the quoted currency in BASE_CURRENCY.
# Each series is sorted ascending by effective date; a rate holds until
# the next entry supersedes it (matches how daily fixings publish).
FX_RATES: dict[str, list[tuple[date, Decimal]]] = {
    "EUR": [
        (date(2026, 6, 1),  Decimal("1.0724")),
        (date(2026, 6, 15), Decimal("1.0812")),
        (date(2026, 7, 1),  Decimal("1.0693")),
    ],
    "GBP": [
        (date(2026, 6, 1),  Decimal("1.2661")),
        (date(2026, 7, 1),  Decimal("1.2588")),
    ],
    "USD": [
        (date(2026, 1, 1),  Decimal("1")),   # identity: base to base
    ],
}


class RateNotFound(LookupError):
    """No FX rate is effective on or before the requested day."""


def as_of_rate(currency: str, on: date) -> Decimal:
    """Return the rate effective on or before `on` — never a future rate."""
    series = FX_RATES.get(currency)
    if not series:
        raise RateNotFound(f"no rate series for {currency!r}")
    effective_dates = [d for d, _ in series]
    idx = bisect.bisect_right(effective_dates, on) - 1  # rightmost date <= on
    if idx < 0:
        raise RateNotFound(f"no {currency} rate on or before {on.isoformat()}")
    return series[idx][1]


# Expected:
#   as_of_rate("EUR", date(2026, 6, 20)) -> Decimal("1.0812")  # 6/15 entry holds
#   as_of_rate("EUR", date(2026, 7, 10)) -> Decimal("1.0693")  # 7/1 entry holds
#   as_of_rate("EUR", date(2026, 5, 20)) -> RateNotFound       # predates series

Treating a missing rate as a RateNotFound rather than falling back to the nearest available rate is deliberate: a converted amount is only defensible if the rate genuinely existed on that day, so a gap must fail loudly and route to remediation, exactly the dead-letter discipline used across the normalization pipeline.

Step 2 — Convert an amount to the base currency at the as-of rate

Conversion is a single Decimal multiplication followed by an explicit quantize to the base currency’s minor unit. Round once, at the boundary, with a documented mode — ROUND_HALF_UP matches most invoicing conventions — so the same input always yields the same booked figure.

from decimal import ROUND_HALF_UP

CENTS = Decimal("0.01")  # base-currency minor unit (2 dp for USD/EUR/GBP)


def convert_to_base(amount: Decimal, currency: str, on: date) -> Decimal:
    """Convert a Decimal amount to BASE_CURRENCY at the rate as-of `on`."""
    if currency == BASE_CURRENCY:
        return amount.quantize(CENTS, rounding=ROUND_HALF_UP)
    rate = as_of_rate(currency, on)
    return (amount * rate).quantize(CENTS, rounding=ROUND_HALF_UP)


# Expected:
#   convert_to_base(Decimal("182.50"), "EUR", date(2026, 7, 1))
#     -> Decimal("195.15")     # 182.50 * 1.0693 = 195.14725, HALF_UP -> 195.15
#   convert_to_base(Decimal("210.00"), "GBP", date(2026, 6, 20))
#     -> Decimal("265.88")     # 210.00 * 1.2661 = 265.881,   HALF_UP -> 265.88

Step 3 — Canonicalize billing timestamps to UTC day buckets

The rate lookup needs a day, and daily rollups need a day, but providers hand you a timestamp — sometimes offset-qualified (...T10:00:00+01:00), sometimes Z, sometimes a naive local string. The rule is absolute: honor an explicit offset, localize a naive value only with the provider’s documented reporting zone via zoneinfo, never the host’s local zone, and reject a naive value that arrives with no zone to apply. The UTC day is then taken from the offset-normalized datetime, which is where day-boundary drift becomes visible.

from datetime import datetime, timezone
from zoneinfo import ZoneInfo


def to_utc(ts: str, assume_zone: str | None = None) -> datetime:
    """Parse a provider timestamp into a timezone-aware UTC datetime.

    An explicit offset is honored as-is. A naive timestamp is localized
    with `assume_zone` (the provider's documented reporting zone) and
    rejected if none is supplied — a naive value must never silently
    default to the worker's local zone.
    """
    dt = datetime.fromisoformat(ts.replace("Z", "+00:00"))
    if dt.tzinfo is None:
        if assume_zone is None:
            raise ValueError(f"naive timestamp with no assume_zone: {ts!r}")
        dt = dt.replace(tzinfo=ZoneInfo(assume_zone))
    return dt.astimezone(timezone.utc)


def utc_day_bucket(ts: str, assume_zone: str | None = None) -> date:
    """The UTC calendar day a usage timestamp falls into."""
    return to_utc(ts, assume_zone).date()


# Expected:
#   to_utc("2026-06-30T23:30:00-05:00")
#     -> datetime(2026, 7, 1, 4, 30, tzinfo=timezone.utc)   # rolls to next UTC day
#   utc_day_bucket("2026-06-30T23:30:00-05:00") -> date(2026, 7, 1)
#   utc_day_bucket("2026-06-20T10:00:00+01:00") -> date(2026, 6, 20)
#   to_utc("2026-06-30 20:00:00")               -> ValueError (naive, no zone)

A late-evening record in a westerly zone (23:30:00-05:00) lands on the next UTC day. If you had bucketed on the provider’s local date you would file it under June 30; the UTC bucket correctly files it under July 1 — and, because the as-of rate is keyed on that same UTC day, both the day total and the FX rate stay consistent with each other.

Step 4 — Assemble the normalized record and apply across a batch

The assembler wires the pieces in the only order that is correct: bucket the timestamp to a UTC day first, then use that day to select the FX rate, then convert. The output is a frozen canonical record that keeps the original amount and currency alongside the converted figure and the exact rate used, so any converted value can be re-derived and audited.

The flow below traces one raw record from mixed-currency, mixed-zone input to a canonical UTC/base-currency record, with the dated FX table feeding the conversion step.

Currency and timezone normalization flow: raw record to UTC day bucket to as-of currency conversion to canonical recordA raw provider record with a mixed currency and timezone enters a UTC day bucket step that produces the usage day. The usage day flows into a convert-at-as-of-rate step, which reads the rate effective on that day from a dated FX rate table and emits a canonical record denominated in the base currency and keyed on the UTC day.usage dayrate on dayRaw recordmixed ccy + zoneUTC daybucketConvert @as-of rateCanonicalrecordFX rate tabledated as-of series
from dataclasses import dataclass, asdict


@dataclass(frozen=True)
class NormalizedCost:
    provider: str
    resource_id: str
    usage_day: date            # canonical UTC calendar day
    original_amount: Decimal
    original_currency: str
    base_amount: Decimal       # converted to BASE_CURRENCY at the as-of rate
    base_currency: str
    fx_rate: Decimal           # the exact rate used, for audit re-derivation


def normalize(raw: dict) -> NormalizedCost:
    """Convert one raw provider record to a canonical UTC / base-currency record."""
    # 1) bucket first: the UTC day is what keys the rate lookup
    day = utc_day_bucket(raw["usage_start"], raw.get("reporting_zone"))
    # 2) Decimal at the boundary — never let cost travel as float
    amount = Decimal(str(raw["amount"]))
    currency = raw["currency"]
    # 3) select the rate as-of that day, then convert
    rate = as_of_rate(currency, day) if currency != BASE_CURRENCY else Decimal("1")
    base_amount = convert_to_base(amount, currency, day)
    return NormalizedCost(
        provider=raw["provider"],
        resource_id=raw["resource_id"],
        usage_day=day,
        original_amount=amount,
        original_currency=currency,
        base_amount=base_amount,
        base_currency=BASE_CURRENCY,
        fx_rate=rate,
    )

Applied across a billing frame, the same function normalizes every row and lets you roll up a directly comparable base-currency total per UTC day:

import pandas as pd

RAW = [
    {"provider": "aws",   "resource_id": "db-prod-eu",  "currency": "EUR",
     "amount": "182.50", "usage_start": "2026-06-30T23:30:00-05:00"},  # -> UTC 7/1
    {"provider": "azure", "resource_id": "sql-prod-uk", "currency": "GBP",
     "amount": "210.00", "usage_start": "2026-06-20T10:00:00+01:00"},
    {"provider": "gcp",   "resource_id": "cloudsql-us", "currency": "USD",
     "amount": "168.00", "usage_start": "2026-06-20T12:00:00Z"},
]

records = [normalize(r) for r in RAW]
df = pd.DataFrame([asdict(r) for r in records])
daily = df.groupby("usage_day")["base_amount"].sum()
print(df[["provider", "usage_day", "original_amount", "original_currency",
          "fx_rate", "base_amount"]])
print("\nbase-currency total per UTC day:")
print(daily)

Expected output:

  provider   usage_day original_amount original_currency  fx_rate base_amount
0      aws  2026-07-01          182.50               EUR   1.0693      195.15
1    azure  2026-06-20          210.00               GBP   1.2661      265.88
2      gcp  2026-06-20          168.00               USD        1      168.00

base-currency total per UTC day:
usage_day
2026-06-20    433.88
2026-07-01    195.15
Name: base_amount, dtype: object

Verification

Confirm the conversion is auditable and the buckets are stable before any report consumes them.

  1. Re-derive every converted amount from the stored rate. Because each record carries fx_rate, the base amount must reproduce exactly from the original — a mismatch means the rate was mutated after conversion.

    for rec in records:
    expected = (rec.original_amount * rec.fx_rate).quantize(
        Decimal("0.01"), rounding=ROUND_HALF_UP)
    assert rec.base_amount == expected, f"non-reproducible: {rec.resource_id}"

  2. Assert no naive timestamps survived. Every record’s day must come from a timezone-aware datetime; a naive value should already have raised in to_utc. Spot-check that a known cross-boundary record landed on the correct UTC day (23:30-05:00 on June 30 must bucket to July 1).

  3. Reconcile the base-currency total against finance. Sum base_amount for a closed month and compare against the figure your treasury system books using the same reference-rate series. They should agree to the cent; a systematic gap usually means you converted at a different rate date than finance did.

Gotchas & Edge Cases

  • Wrong rate date is the most expensive bug. Converting a June usage line at today’s rate — or at the invoice-issue rate — silently misstates cost whenever the currency moved. Always key the rate on the UTC day the usage was incurred, and store that day on the record so the choice is auditable. This is the single reason the pipeline buckets to UTC before it converts.
  • Look-ahead bias in the rate table. A rate published on July 1 must not be applied to June 30 usage. The bisect_right(...) - 1 lookup enforces “on or before” precisely; if you instead round to the nearest rate you leak future information into a closed period and your reconciliation will never tie out.
  • Day-boundary drift across zones. A record at 23:30 in America/Chicago and one at 00:30 the next day in Asia/Tokyo can belong to the same UTC day or different ones depending purely on offset. Bucketing on the provider’s local date, not UTC, is the classic cause of a daily total that is right in aggregate but wrong per day — which breaks any cost anomaly detection that compares day over day.
  • DST transitions make naive local times ambiguous or nonexistent. A naive 2026-11-01T01:30:00 in America/New_York occurs twice (fall-back); 2026-03-08T02:30:00 never occurs (spring-forward). zoneinfo resolves these with the fold attribute, but the safer fix is to demand offset-qualified timestamps from the provider and treat a naive value as a validation failure, per schema validation for billing data.
  • Never let money travel as float. Read the amount as a string and wrap it in Decimal at the boundary; Decimal("182.50") * 1.0693 as a float product carries binary rounding noise that diverges run to run. This is the same money-typing rule that governs mapping provider SKUs to canonical compute units.
  • Round once, and record the mode. Quantizing at every intermediate step compounds rounding error; convert at full precision and quantize only when you emit the canonical amount. Pin ROUND_HALF_UP (or whatever your finance policy dictates) in one constant so every record rounds identically.
  • Zero-decimal and three-decimal currencies exist. JPY and KRW have no minor unit; BHD and KWD have three. A hard-coded two-place quantize misstates those. Drive the minor-unit exponent from a per-currency table if your estate bills outside USD/EUR/GBP.

Frequently Asked Questions

Which date should I use to select the FX rate — the usage date, the invoice date, or today?

The UTC day the usage was incurred, in almost every case. That is the day the cost economically occurred, and it is the date your finance team’s reference-rate series is keyed on, so converting on it is what lets the two reconcile. Invoice-issue date and “today” both introduce a rate that had nothing to do with when the resource actually ran, and using them guarantees drift the moment the currency moves. Store the chosen day on the record so the decision is explicit and auditable.

How do I stop the rate lookup from leaking future rates into a closed period?

Use an “on or before” selection, never “nearest”. The bisect_right(effective_dates, on) - 1 idiom returns the rightmost rate whose effective date is at or before the requested day, so a rate published after that day is structurally unreachable. Nearest-neighbor matching feels forgiving but it applies tomorrow’s rate to yesterday’s cost, which corrupts any period you have already reported and reconciled.

Why bucket to UTC days instead of keeping the provider’s local billing day?

Because different providers report in different zones, and a local-day bucket makes the same instant fall on two different calendar dates depending on where the meter lives. UTC is the one zone every provider can be projected onto unambiguously, so bucketing there is what makes a daily total comparable across AWS, Azure, and GCP. You can always re-present a UTC-keyed total in a display zone at the reporting layer, but the stored bucket must be UTC.

How should I handle DST transitions when localizing naive timestamps?

Prefer never to localize naive timestamps at all — require the provider export to carry an explicit offset, and treat a naive value as a validation failure. When you genuinely must localize (a legacy export documented as “always US Eastern”), use zoneinfo, which is DST-aware, and rely on the fold attribute to disambiguate the repeated fall-back hour. A time in the spring-forward gap does not exist; decide a documented policy (shift forward by the gap, or reject) rather than letting it resolve silently.

Why Decimal and ROUND_HALF_UP rather than float for currency conversion?

Currency amounts and FX rates are exact decimal quantities, and float cannot represent most of them without binary rounding error that accumulates across millions of line items into real reconciliation gaps. Decimal with a set precision gives exact, reproducible arithmetic, and quantizing once with an explicit ROUND_HALF_UP at the emit boundary means the booked figure matches what finance expects and is identical on every replay. A float-sourced amount can make two runs of the same data disagree in the last cent.

Back to: Multi-Cloud Cost Normalization