Convert Multi-Currency Transactions with an FX Rate Table

A business that invoices in euros, dollars and yen but reports in pounds needs every figure converted before it can be added up, and "converted" hides several decisions: which rate source, which day's rate, which direction the rate is quoted in, and whether a balance still open at month end should be revalued. Getting any of them wrong produces totals that look plausible and do not match the accounts. This page builds the conversion step by step with pandas, from a rate table to an Excel report with the unrealised gain or loss on open invoices. It is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

Two files. eur_reference_rates.xlsx is a daily rate table in the format central banks publish: one row per business day, one column per currency, each value meaning how many units of that currency one euro buys. There is no row for weekends or for the 31 August bank holiday. invoices_q3.xlsx lists sales invoices for July to September 2026 in EUR, USD, JPY and GBP, with whether each is paid or still open. The reporting currency is GBP.

Step 1 — Get the rates the right way round

The table says 1 EUR = 0.858 GBP and 1 EUR = 1.162 USD. What the conversion needs is pounds per one unit of each currency. For the euro that is the GBP column itself; for any other currency it goes through the euro: 1 USD = 0.858 / 1.162 GBP. This is triangulation, and it is how reporting systems derive cross rates from a single-base table:

import pandas as pd

rates = pd.read_excel("eur_reference_rates.xlsx", parse_dates=["Date"])
BASE = "GBP"

long = rates.melt(id_vars="Date", var_name="Currency", value_name="PerEUR")
gbp_per_eur = rates.set_index("Date")[BASE]
long["BasePerUnit"] = long["Date"].map(gbp_per_eur) / long["PerEUR"]
eur_rows = pd.DataFrame({"Date": rates["Date"], "Currency": "EUR", "PerEUR": 1.0, "BasePerUnit": gbp_per_eur.values})
fx = pd.concat([long[long["Currency"] != BASE], eur_rows], ignore_index=True)
fx = fx[["Date", "Currency", "BasePerUnit"]].sort_values("Date")
print(fx[fx["Date"] == "2026-07-01"].round({"BasePerUnit": 6}).to_string(index=False))

usd = fx.loc[fx["Currency"] == "USD", "BasePerUnit"].iloc[0]
assert 0.5 < usd < 1, "USD rate looks inverted"
print(f"sanity check: 100 USD = {100 * usd:.2f} GBP")

Getting the direction wrong is the classic error, and it is easy to catch: converting 100 USD should give fewer pounds, roughly 74, not 135. The script asserts it, so an inverted rate stops the run instead of reaching a report. The base currency itself needs no rate; it is handled in Step 2.

Step 2 — Attach the rate in force on each date

Invoices dated on a Saturday or a holiday have no rate of their own; the convention is the latest published rate on or before the transaction date. merge_asof does this per currency with by=, and tolerance stops a missing week of rates from silently reusing a stale one:

tx = pd.read_excel("invoices_q3.xlsx", parse_dates=["Date"]).sort_values("Date")

conv = pd.merge_asof(tx, fx.rename(columns={"Date": "RateDate"}), left_on="Date", right_on="RateDate",
                     by="Currency", direction="backward", tolerance=pd.Timedelta(days=5))
conv.loc[conv["Currency"] == BASE, ["BasePerUnit", "RateDate"]] = [1.0, pd.NaT]
missing = conv[conv["BasePerUnit"].isna()]
assert missing.empty, f"no rate for: {missing[['Invoice', 'Currency', 'Date']].to_dict('records')}"
print(conv[["Invoice", "Date", "Currency", "RateDate", "BasePerUnit"]].round({"BasePerUnit": 6}).to_string(index=False))

INV-8808, dated Monday 31 August, uses Friday 28 August's rate because the bank holiday had none. Match Rows by Nearest Date with merge_asof covers the options and the sorting errors merge_asof raises.

Step 3 — Convert and round to the right precision

Currencies have different minor units: pence and cents have two decimals, yen has none. Round converted amounts to the base currency's precision, and display source amounts in their own:

MINOR_UNITS = {"GBP": 2, "EUR": 2, "USD": 2, "CHF": 2, "JPY": 0}

conv["BaseAmount"] = (conv["Amount"] * conv["BasePerUnit"]).round(MINOR_UNITS[BASE])
conv["Month"] = conv["Date"].dt.to_period("M")
summary = conv.pivot_table(index="Currency", columns="Month", values="BaseAmount", aggfunc="sum", fill_value=0)
summary["Q3"] = summary.sum(axis=1)
print(summary.round(2).to_string())

Round once, at the converted amount. Rounding the rate first changes large amounts noticeably: the yen rate on 24 September was 0.005022 pounds; cut to four decimal places it becomes 0.0050, and the 4,125,000-yen invoice drops by about 90 pounds.

Step 4 — Average rates for the P&L, closing rates for balances

Many groups translate income at the month's average rate rather than each day's, so a month of sales is converted consistently; and they value balances still open at the month end at the closing rate. Both come from the same table:

fx["Month"] = fx["Date"].dt.to_period("M")
average = fx.groupby(["Currency", "Month"])["BasePerUnit"].mean().rename("AvgRate")
closing = fx.sort_values("Date").groupby(["Currency", "Month"])["BasePerUnit"].last().rename("ClosingRate")
rate_table = pd.concat([average, closing], axis=1).reset_index()
print(rate_table[rate_table["Month"] == "2026-09"].round({"AvgRate": 6, "ClosingRate": 6}).to_string(index=False))

Which method applies is an accounting policy, not a technical choice; the script's job is to make both available and label which one each figure used. In September the dollar's average rate was 0.7224 and its closing rate 0.7166, so a dollar balance valued at the average would be overstated by almost 1% at the quarter end. Differences of that size are why auditors ask which rate each line used.

Step 5 — Revalue open invoices

An invoice booked at one rate and still unpaid at the month end is worth a different amount in pounds at the closing rate. The difference is an unrealised exchange gain or loss, reported until the invoice is paid, when it becomes realised:

open_inv = conv[conv["Status"] == "Open"].copy()
q3_close = rate_table[rate_table["Month"] == "2026-09"].set_index("Currency")["ClosingRate"]
open_inv["ClosingRate"] = open_inv["Currency"].map(q3_close).fillna(1.0)
open_inv["ValueAtClose"] = (open_inv["Amount"] * open_inv["ClosingRate"]).round(2)
open_inv["FX gain/(loss)"] = (open_inv["ValueAtClose"] - open_inv["BaseAmount"]).round(2)
print(open_inv[["Invoice", "Currency", "Amount", "BaseAmount", "ValueAtClose", "FX gain/(loss)"]].to_string(index=False))
print(f"net unrealised FX: {open_inv['FX gain/(loss)'].sum():,.2f} GBP")

The GBP invoice shows zero, as it must. The largest movement is on the dollar invoice from 18 September: the dollar weakened against sterling over the last fortnight of the quarter, from 0.7248 to 0.7166 pounds, so 30,400 dollars are worth 247 pounds less than when they were booked. Next month the revaluation is repeated from the new closing rate, and when an invoice is paid the difference between its booked value and the pounds actually received becomes a realised gain or loss.

Step 6 — Write the report

Keep the rates alongside the results: anyone checking a figure needs the rate and its date. Write the converted invoices, the rate table and the revaluation to one workbook:

with pd.ExcelWriter("fx_report_q3_2026.xlsx", engine="xlsxwriter") as xw:
    out = conv.drop(columns=["Month"]).copy()
    out.to_excel(xw, sheet_name="Invoices in GBP", index=False)
    rate_table.assign(Month=rate_table["Month"].astype(str)).to_excel(xw, sheet_name="Rates", index=False)
    open_inv.drop(columns=["Month"]).to_excel(xw, sheet_name="Revaluation", index=False)
    book = xw.book
    rate_fmt = book.add_format({"num_format": "0.000000"})
    money = book.add_format({"num_format": "#,##0.00;[Red](#,##0.00)"})
    date_fmt = book.add_format({"num_format": "yyyy-mm-dd"})
    ws = xw.sheets["Invoices in GBP"]
    ws.set_column("B:B", 12, date_fmt)
    ws.set_column("C:C", 14)
    ws.set_column("E:E", 14, book.add_format({"num_format": "#,##0.##"}))
    ws.set_column("G:G", 12, date_fmt)
    ws.set_column("H:H", 12, rate_fmt)
    ws.set_column("I:I", 13, money)
    xw.sheets["Rates"].set_column("C:D", 12, rate_fmt)
    xw.sheets["Revaluation"].set_column("I:L", 14, money)
print("written")

The source-amount format #,##0.## shows yen without decimals and euros with them; for strict per-currency formatting, write each row with a format chosen from MINOR_UNITS.

Common pitfalls

• Inverted rates. Know whether the table says units of currency per euro or euros per unit, and sanity-check a known conversion.
• Exact date joins. Weekend and holiday transactions have no rate row; use merge_asof with a tolerance.
• The base currency. It has no row in the rate table; give it a rate of 1 explicitly.
• Rounding the rate. Round the converted amount, not the rate.
• Mixing methods. Label which figures use transaction, average or closing rates; mixing them in one total produces unexplained differences.
• Revaluing paid invoices. Only balances still open at the reporting date are revalued; paid ones produce a realised difference at payment.

Conclusion

Triangulate a single-base rate table into base-currency rates per unit, attach the rate in force on each transaction date with merge_asof(..., by="Currency", tolerance=...), give the base currency a rate of 1, and round converted amounts to the base currency's minor unit. Derive monthly average and closing rates from the same table for P&L translation and balance revaluation, calculate unrealised gains and losses on open invoices, and keep the rates and their dates in the report.

Frequently asked questions

How do I convert currencies using ECB euro reference rates? The rates say how many units of each currency one euro buys. To convert into a non-euro base currency, divide the base currency's rate by the source currency's rate for the same day; for euro amounts, multiply by the base currency's rate.

Which exchange rate should a weekend transaction use? Usually the latest published rate on or before the transaction date, typically Friday's. merge_asof with direction="backward" implements that rule.

What is the difference between average and closing rates? Average rates, the mean of a month's daily rates, translate income and expenses for the month; closing rates, the last rate of the month, value balances that are still open at that date.

How do I calculate an unrealised FX gain or loss? For each open foreign-currency balance, convert it at the closing rate and subtract the amount it was booked at in the base currency. A positive result is a gain.

Related

• Up one level: Building Finance and Operations Reports — the rest of the finance reports.
• Match Rows by Nearest Date with merge_asof — the join in detail.
• Build an Accounts Receivable Aging Report — the open invoices, by age.
• Join Rows to Effective Date Ranges with pandas — rates valid between two dates.
• Build a Profit and Loss Statement from a General Ledger — where translated income lands.