Choosing FX Rate Sources and Lock Windows

The moment a product prices in more than one currency, someone has to answer three questions: where the exchange rate comes from, how long a rate stays valid once shown to a customer, and who absorbs the difference when it moves. Answering them implicitly — by calling a free rate API at charge time — produces prices that change between the pricing page and the invoice and a margin that nobody is monitoring. This guide sits under Multi-Currency Checkout & Localization and covers making those answers explicit.

The distinction that clarifies everything is between presentment currency and settlement currency. A customer sees and pays in EUR; you settle and report in USD. The exchange rate matters at two different moments for those two purposes, and conflating them is why FX handling becomes confusing.

The practical goal is that a price shown to a customer never changes before they pay, and that every historical invoice can be re-derived exactly, including its rate.

Trade-offs

Most subscription businesses should use a central bank reference rate for pricing and invoicing, and accept the payment provider’s rate for the settlement leg. That combination is auditable where it needs to be — invoices and tax — and realistic where money actually moves.

Step-by-Step Implementation

1. Store rates as immutable versions

CREATE TABLE fx_rates (
  rate_id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  base_ccy    CHAR(3) NOT NULL,
  quote_ccy   CHAR(3) NOT NULL,
  rate        NUMERIC(18,8) NOT NULL,
  source      TEXT NOT NULL,              -- 'ecb_daily' | 'provider' | 'vendor'
  published_at TIMESTAMPTZ NOT NULL,
  fetched_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (base_ccy, quote_ccy, source, published_at)
);

Never update a rate row. A rate is a fact about a moment, and overwriting it makes every document that referenced it unreproducible. The unique constraint makes a re-fetch idempotent.

2. Decide the lock window for displayed prices

from datetime import timedelta

LOCK_WINDOWS = {
    "pricing_page":  timedelta(days=1),    # refreshed with the daily rate
    "checkout":      timedelta(minutes=30),# the session's quoted price is held
    "quote":         timedelta(days=30),   # a sales quote holds its rate
    "subscription":  None,                 # fixed at signup until the price changes
}

The subscription entry is the important one. A recurring price that is re-derived from a live rate every month changes what the customer pays without any decision being made, which reads as arbitrary. Fix the presentment amount at signup and change it only through an explicit price update the customer is told about.

3. Stamp the rate on every document

That gain or loss is posted as described in handling multi-currency FX gain/loss in the ledger.

4. Round after converting, once

from decimal import Decimal, ROUND_HALF_UP

def convert_minor(amount_minor: int, rate: Decimal, quote_exponent: int = 2) -> int:
    """Convert once, round once, at the target currency's precision."""
    converted = Decimal(amount_minor) * rate
    return int(converted.quantize(Decimal(1), rounding=ROUND_HALF_UP))

Currencies with zero decimal places, such as JPY, break code that assumes two. Carry the exponent per currency rather than hard-coding a divisor of 100 anywhere.

5. Monitor drift

-- Locked subscription prices whose implied rate has drifted from today's.
SELECT s.subscription_id, s.currency, s.locked_rate,
       f.rate AS current_rate,
       round(100 * (f.rate - s.locked_rate) / s.locked_rate, 2) AS drift_pct
  FROM subscriptions s
  JOIN fx_rates f ON f.base_ccy = 'USD' AND f.quote_ccy = s.currency
 WHERE f.published_at = (SELECT max(published_at) FROM fx_rates WHERE quote_ccy = s.currency)
   AND abs(f.rate - s.locked_rate) / s.locked_rate > 0.10;

Who Carries the Risk, and What to Do About It

Fixing a presentment price means you carry the currency risk between signup and every subsequent renewal, and that is usually the right trade — but it should be a decision with a review cadence rather than an accident.

The exposure is bounded and measurable. For each currency, the locked prices multiplied by the drift against today’s rate is the margin gained or lost relative to pricing today. Reviewing that quarterly turns FX from an invisible erosion into a number, and the action it implies is a price update for new customers rather than a change to existing ones.

Repricing existing subscribers is a commercial decision, not a technical one, and the honest version of it is a notified price change with reasonable notice rather than a silent rate refresh. Customers accept “our EUR pricing is changing on 1 June” far better than an invoice that quietly differs from last month’s by four percent.

Consider psychological pricing when setting local amounts rather than converting mechanically. A converted price of €18.43 reads as an afterthought; €19 reads as a price. Setting local price points deliberately and updating them occasionally is both better commercially and simpler operationally than tracking a live rate, and it interacts with the purchasing-power considerations discussed in presenting localized prices.

Finally, decide what happens for currencies you price in but do not settle in. If a customer pays in NOK and your provider settles to EUR, there is a conversion happening that you do not control and may not see clearly. Read the provider’s conversion terms, record the settled amount alongside the invoiced one, and treat the difference as a cost of doing business in that currency — one worth quantifying before deciding whether to keep offering it.

Operating the Rate Pipeline

The rate ingestion job is small and consequential, and it deserves the reliability treatment usually reserved for the payment path.

Fetch on a schedule aligned to the source’s publication time, not on an arbitrary cron. A central bank that publishes once each working day mid-afternoon has nothing new at 02:00, and a job that runs then will spend most of the week re-recording yesterday’s figure and will lag by a full day whenever it matters.

Handle non-publication days explicitly. Weekends and public holidays produce no new rate, and the correct behaviour is to continue using the most recent published rate rather than to fail or to interpolate. Make that explicit in the lookup — “the rate in force on this date” rather than “the rate published on this date” — so a Saturday invoice does not fall through a gap.

Alert on staleness rather than on fetch failures. A single failed fetch is unremarkable; a rate that has not moved in four working days means the pipeline is broken even if every job reported success, which happens when a source changes its format and the parser silently returns the previous value.

Validate before storing. A rate that differs from the previous one by more than a few percent is far more likely to be a parsing error or a base-currency inversion than a genuine market move, and quarantining it for review costs nothing while an inverted rate applied to a day of invoices is expensive to unwind.

Keep the whole history. Rates are small rows and the storage cost is negligible next to the value of being able to reproduce any historical document exactly, which is the property the entire design exists to protect.

Verification & Testing

Test the reproducibility property directly: given an invoice, re-derive its converted amounts from the stored rate version and assert exact equality. Any drift means a rate row was mutated or a conversion path used a live rate instead of the stored one.

Test zero-decimal currencies explicitly. Convert to JPY and assert no fractional minor units appear, and convert from JPY and assert the source exponent was handled. This is the single most common currency bug and it is invisible in a EUR/USD-only test suite.

Test the lock window at its boundary: a checkout session at the last second of its window uses the quoted rate; one second later re-quotes. Assert the customer is shown the new price rather than being charged a different amount than displayed, which is the failure this whole mechanism exists to prevent.

Gotchas & Production Pitfalls

• Mutating rate rows. Makes every historical document unreproducible, and the loss is discovered during an audit rather than in testing.
• Live rates on recurring charges. The amount changes every month without any decision, which customers read as arbitrary billing.
• Assuming two decimal places. Breaks for zero-decimal and three-decimal currencies, usually as an off-by-100 error.
• Rounding before conversion. Introduces error proportional to the number of lines. Convert at full precision, round once at the end.
• No record of the settlement rate. Makes the FX gain or loss unattributable and the payout reconciliation manual.

Frequently Asked Questions

Which rate source is best for invoices? A central bank daily reference rate, because it is publicly citable, stable within the day, and accepted by tax authorities in most jurisdictions that care about the conversion.

How often should locked prices be refreshed? For new customers, whenever the drift is commercially material — quarterly is a common cadence. For existing subscribers, only through an explicit, notified price change.

Should customers be able to change currency? Rarely, and never silently. A currency change is effectively a new subscription at a new price, and treating it as an amendment produces a movement that looks like churn plus a new sale in every revenue report.

What about tax on the converted amount? Tax is calculated on the presentment amount in the customer’s currency, and the rate used for tax reporting may be prescribed by the jurisdiction. Store the rate version used for tax separately if it differs from the pricing rate.

Related

• Multi-Currency Checkout & Localization
• Presenting Localized Prices with Purchasing Power Parity
• Handling Multi-Currency FX Gain/Loss in the Ledger
• Reconciliation & Double-Entry Ledger
• Alternative Payment Methods & Local Rails