Multi-currency and FX analytics

Multi-currency reporting is not solved by attaching a currency code and summing everything. A payment amount, settlement amount, accounting amount, and presentation amount can differ even when they describe the same transaction.

Keep the payment provider, ledger, or finance system authoritative. Use GraphJSON to analyze normalized monetary facts, and make every conversion rule part of the metric contract.

This guide extends Revenue and subscription analytics and Commerce analytics.

Name the currencies#

Distinguish:

A USD customer charge settled to a EUR bank account may have a USD transaction amount, EUR settlement amount, fees in one or more currencies, and a finance-approved functional amount.

Do not call all of these amount_usd.

Store typed monetary facts#

Use integer minor units and a lowercase ISO currency code:

{
  "event": "payment_succeeded",
  "payment_id": "pay_61",
  "account_id": "acct_7",
  "transaction_amount_minor": 12500,
  "transaction_currency": "usd",
  "occurred_at": "2026-07-25T18:20:00Z",
  "provider_event_id": "evt_123",
  "schema_version": 2
}

Do not assume every currency has two decimal places. Centralize currency exponents and formatting in reviewed code.

If settlement is a separate provider fact, emit it separately:

{
  "event": "payment_settled",
  "payment_id": "pay_61",
  "settlement_amount_minor": 10742,
  "settlement_currency": "eur",
  "processor_fee_minor": 312,
  "processor_fee_currency": "eur",
  "settled_at": "2026-07-27T09:00:00Z"
}

Do not overwrite the original transaction with settlement data that arrived later.

Choose the rate date and source#

Define:

• rate provider or finance table
• source and target currency
• quotation convention
• rate date or timestamp
• business-day fallback
• precision and rounding
• rate-type version

Possible dates include authorization, capture, settlement, refund, invoice, service period, or reporting-period close. They answer different questions.

For product operating analytics, a transaction-time reference rate may be appropriate. For accounting, use the finance-approved method. Do not invent a dashboard rate that competes with the ledger.

Convert upstream when the result is governed#

The safest reusable event includes the source amount and a calculated functional amount with lineage:

{
  "event": "payment_value_normalized",
  "payment_id": "pay_61",
  "transaction_amount_minor": 12500,
  "transaction_currency": "usd",
  "functional_amount_minor": 10691,
  "functional_currency": "eur",
  "fx_rate": "0.85528000",
  "fx_rate_date": "2026-07-25",
  "fx_rate_source": "finance_daily_close",
  "fx_policy_version": 3,
  "rounding": "half_even",
  "calculated_at": "2026-07-26T02:00:00Z"
}

Use a decimal string for the rate when JSON binary floating-point is not an acceptable source representation. Perform the calculation in decimal-safe application or finance code, not in JavaScript Number.

GraphJSON does not maintain an FX rate table or restatement engine for you.

Never sum unlike currencies#

The default query should preserve currency:

SELECT
  JSONExtractString(json, 'transaction_currency') AS currency,
  sum(JSONExtractInt(json, 'transaction_amount_minor')) AS amount_minor
FROM billing_events
WHERE JSONExtractString(json, 'event') = 'payment_succeeded'
GROUP BY currency
ORDER BY currency

A combined total is valid only after conversion under one documented policy:

SELECT
  JSONExtractString(json, 'functional_currency') AS currency,
  JSONExtractInt(json, 'fx_policy_version') AS fx_policy_version,
  sum(JSONExtractInt(json, 'functional_amount_minor')) AS amount_minor
FROM normalized_billing
WHERE
  JSONExtractString(json, 'event') = 'payment_value_normalized'
  AND JSONExtractInt(json, 'fx_policy_version') = 3
GROUP BY currency, fx_policy_version

Do not mix functional currencies or policy versions in one unlabeled line.

Handle refunds deliberately#

Choose whether a refund uses:

• the original transaction conversion rate
• the refund-date rate
• actual settlement amounts
• the finance ledger’s booked value

Using the original rate makes operating net sales reverse symmetrically. Using the refund-date rate may expose FX movement. Both can be valid for different reports.

Link the refund to the original transaction and record the choice:

{
  "event": "refund_value_normalized",
  "refund_id": "re_81",
  "payment_id": "pay_61",
  "refund_amount_minor": 2500,
  "refund_currency": "usd",
  "functional_amount_minor": 2138,
  "functional_currency": "eur",
  "fx_basis": "original_transaction_rate",
  "fx_rate_date": "2026-07-25",
  "fx_policy_version": 3
}

Model chargebacks, reversals, processor fees, and taxes as distinct facts. Do not infer them from a negative payment.

Define rounding and residuals#

Round at the grain specified by the authority:

• transaction
• line item
• invoice
• settlement batch
• reporting total

Converting line items and summing can differ from converting the invoice total. Preserve the provider or ledger amount and any explicit rounding residual.

Do not “fix” a reconciliation difference by silently distributing cents across events. Record the allocation policy and version.

Separate FX translation from FX gain or loss#

A management dashboard that translates revenue into a presentation currency is not automatically an FX gain/loss calculation. Realized and unrealized FX effects depend on accounting recognition, receivables, settlement, and ledger policy.

Label product reports as translated operating analytics. Use the accounting system for statutory and booked FX results.

Restate with new versions#

Rates can be corrected and finance policy can change. Choose:

• as originally reported: preserve the original converted event
• latest restated: publish a new calculation version
• constant currency: apply a fixed comparison-period policy

Never overwrite history without a visible version. A restated record should retain the original transaction ID, calculation version, policy version, effective period, and calculation time. Select the latest approved version per logical identity.

Reconcile at every boundary#

For each settled period compare:

1. transaction totals by currency with the payment authority
2. settlement totals and fees with the processor
3. normalized functional totals with the finance rate table
4. refunds and chargebacks with their original transactions
5. GraphJSON event IDs and counts with the normalization job

Investigate differences by currency, rate date, policy version, and event status. Do not reconcile only the grand converted total; errors can cancel out.

Review checklist#

• Transaction, settlement, functional, and presentation currencies are distinct.
• Amounts use integer minor units and explicit currency codes.
• Currency exponents and rounding live in reviewed code.
• FX source, quotation, date, fallback, and version are documented.
• Conversion uses decimal-safe arithmetic.
• Refund, fee, chargeback, and tax facts remain separate.
• Mixed currencies or policy versions are never silently summed.
• Restatements create auditable calculation versions.
• Product reports do not claim to be accounting FX results.
• Every period reconciles by original currency and normalized currency.