Portfolio currency translation

Portfolio currency translation

A portfolio holding NYSE, LSE, and TSE positions needs one number: total value in the home currency. The naive approach — shares × local_price × today's_fx_rate for each lot, summed — is wrong in at least three ways: it mixes timestamps across markets, it rounds too early, and it silently hides how much of the change was the stock moving versus the currency moving. This skill covers the translation problem end-to-end. Engine: exchangerate.dev (https://api.exchangerate.dev, keyless).

1. Why naive conversion is wrong

The naive loop — for each lot, shares × local_price × today's_fx_rate, summed — stacks three separate bugs:

• Timestamp mismatch. A TSE stock's close is from ~08:00 UTC, an LSE stock's from ~16:30 UTC. Fetching one "today's" FX rate at request time (say 14:00 UTC) applies a single snapshot to prices fixed 6+ hours apart — the JPY leg converted at a rate from after Tokyo closed, the GBP leg from before London closed. See §2.
• Per-lot rounding. Rounding each lot to home-currency minor units before summing compounds rounding error across dozens of positions — see §7.
• No return decomposition. A JPY stock up 2% with JPY down 1% against USD nets to ~+1% in USD, but "the portfolio is up 1%" hides that the position outperformed while the currency dragged — see §4.

2. Pick ONE valuation convention and document it

There is no single "correct" close — Tokyo close, London close, and the ECB ~16:00 CET fix are three different points in time on the same calendar date. Two conventions work; pick one and apply it everywhere:

• Home-close convention (simpler, recommended default): value every position using the FX rate as of your home market's close, regardless of where the asset trades. A US-based portfolio holding LSE and TSE stocks values everything at the US market close FX rate. One FX fetch per day, consistent snapshot, easy to explain to a user.
• Local-close convention (more "correct," more bookkeeping): value each position using the FX rate at its own market's close — TSE positions get the ~08:00 UTC rate, LSE positions the ~16:30 UTC rate. More accurate mark-to-market per position, but two portfolios computed under different conventions are not comparable, and you must persist which convention produced which number.

Whichever you choose, store it as metadata next to the valuation (fx_convention: "home_close") and never mix conventions within one portfolio snapshot. Use the response's own clock instead of your server's local time:

curl "https://api.exchangerate.dev/v1/latest?base=USD&symbols=GBP,JPY"

{
  "result": "success", "base": "USD", "source": "live",
  "market_session": "open",
  "timestamp": "2026-07-06T10:50:32Z",
  "data_updated_at": "2026-07-06T10:50:09Z",
  "rates": { "GBP": 0.74966, "JPY": 162.316 }
}

Persist data_updated_at alongside every stored valuation row — it's the actual rate-of-record timestamp, not "when my cron ran."

For a specific historical date (end-of-day snapshots, backfills), use the daily snapshot endpoint rather than /v1/latest:

curl "https://api.exchangerate.dev/v1/2026-07-02?base=USD&symbols=GBP,JPY"

3. Historical valuation series

Building a portfolio value time-series (for a performance chart or backtest) means joining an FX series to a price series on exact dates:

curl "https://api.exchangerate.dev/v1/range?base=USD&symbols=GBP,JPY&start_date=2026-01-01&end_date=2026-06-30"

Rules that matter here (full detail in fx-rates-correctness):

• Business days only. Weekend rows are absent, not null. Don't assume 365 rows/year or align by position — align by date.
• Paginate beyond 366 rows. A 6-month range query already brushes the page limit; a multi-year backtest will span pages. Check has_more and follow next_cursor until it's false — don't silently truncate the earlier history.

fx = fetch_range(base="USD", symbols=["GBP", "JPY"], start_date=start, end_date=end)
prices = fetch_price_series(...)  # your market data, keyed by trading date

df = prices.join(fx, how="left")   # exact-date join first
df = df.ffill()                    # forward-fill AFTER the join, last step

Forward-filling before the join lets a Friday FX rate leak into a Monday valuation that should have used Monday's own rate — see fx-rates-correctness §2 for why that's look-ahead bias's quieter sibling. Join on exact dates, decide the fill policy explicitly, fill last.

4. Decompose return: local vs currency

A position's total return in home currency splits into two independent components:

total_return ≈ (1 + local_return) × (1 + fx_return) − 1

where local_return is the position's return in its own trading currency and fx_return is the change in the FX rate over the same period (note they multiply, not add). Report both, not just the blended total: a TSE stock up 8% local while JPY weakened 6% nets to ~+1.5% in USD, which alone reads as "barely moved" — the split is what lets a user tell a bad stock from bad currency exposure, i.e. sell-the-position from hedge-the-currency.

5. Dividends and cash flows: convert at pay-date rate

Foreign dividends and other cash flows convert at the FX rate on their actual pay date, not the valuation date or the ex-date:

curl "https://api.exchangerate.dev/v1/2024-05-15?base=GBP&symbols=USD"

Check is_forward_filled on the response — a pay date landing on a weekend or holiday means the API returns the prior business day's fix forward-filled, and the pay-date-as-labeled did not have its own published rate:

div_fx = get_historical(pay_date, base="GBP", symbols=["USD"])
if div_fx["is_forward_filled"]:
    # pay date had no published fix (weekend/holiday settlement).
    # Decide explicitly and record which convention you used:
    #  - use the forward-filled (prior business day) rate as-is, or
    #  - shift to the actual next business-day fix
    ...
usd_dividend = gbp_dividend * div_fx["rates"]["USD"]

Whichever choice you make, apply it consistently across all dividend cash flows in the portfolio — mixing "use forward-filled" for some and "shift forward" for others makes total-return reconciliation impossible later.

6. Weekend/holiday effects aren't a bug

Equity markets and FX markets run on different calendars. NYSE is closed Saturday–Sunday; the FX interbank market is closed roughly Friday 21:00 UTC to Sunday 21:00 UTC, which is not the same window. A portfolio that "jumps" between Friday's close and Monday's open is very often FX catching up over the weekend gap, not a stock price change — nothing traded, but the currency leg moved.

Label this explicitly in the UI rather than presenting it as a normal daily change:

snapshot = get_latest_rate(base="USD", symbols=["GBP"])
if snapshot["market_session"] in ("weekend", "interbank_closed"):
    valuation["market_session"] = snapshot["market_session"]
    valuation["note"] = "FX last updated " + snapshot["data_updated_at"]

A market_session: weekend value with source: live is last week's trading consensus, not a fresh quote — see fx-rates-correctness §6. Don't let a Monday-morning valuation read as "the market panicked over the weekend" when it's really Friday's rate finally being applied to a Monday snapshot, or vice versa.

7. Precision: sum first, convert once, round once

Convert per-lot-then-sum-rounded-values is the single most common precision bug in portfolio code:

# WRONG — rounds N times, error compounds across lots
total = sum(round(lot.shares * lot.local_price * rate, 2) for lot in gbp_lots)

# RIGHT — sum in the asset currency, convert once, round once
subtotal_gbp = sum(lot.shares * lot.local_price for lot in gbp_lots)
converted = get_convert("GBP", "USD", subtotal_gbp)
total_usd = converted["converted"]  # already rounded to USD minor units

For a portfolio spanning several currencies, do this per-currency subtotal, then sum the (already-converted) subtotals in the home currency and round once at the very end. Pull minor-unit precision from GET /v1/currencies (minor_units is 0 for JPY, 3 for KWD/BHD, 2 for most) rather than hard-coding 2 dp — rounding a JPY subtotal to 2 decimals invents fractional yen that don't exist and won't reconcile against any broker statement.

8. Broker-statement mismatch: debugging checklist

When a computed portfolio value doesn't match a broker's home-currency figure, walk this list before assuming either number is "wrong":

• Rate date convention. Did you use the broker's valuation date, or a day off from it? A broker valuing as of market close in its own timezone will diverge from a home-close convention on any day markets moved.
• Fix vs close. Brokers often use the ECB ~16:00 CET fix (or their custodian's own EOD fix) rather than a live intraday rate. exchangerate.dev's source: ecb_daily responses match a CET-fix convention; source: live does not.
• Spread. Indicative rates (~5–15 bps band) are not the rate at which money actually moved. A broker's executed conversion includes their FX spread — do not expect an exact match on cash conversions, only a close one. See fx-rates-correctness §7.
• Derived crosses. A triangulated pair (derived: true, error bound in derivation_bps_max, typically 1–2 bps) will diverge slightly from a broker's native quote on that cross. Check derived_symbols before assuming a bug.
• Rounding order. Confirm the broker also sums-then-converts-then-rounds rather than converting per-lot — if not, small discrepancies are expected and are not a bug in either system.

Work down this list in order — rate-date convention accounts for the large majority of "my numbers don't match my broker" reports; spread and derivation bps only matter for the last few basis points.

Related skills in this repo

• exchangerate-dev — endpoint reference, auth, and MCP server setup for the underlying API.
• fx-rates-correctness — the full pitfall catalog this skill builds on: forward-fill, join order, precision, derived crosses, staleness.
• fx-accounting-rates — rate-of-record choices for invoicing and bookkeeping (a sibling problem to portfolio valuation, different rules).