User story: multi-currency consolidation

The story

Priya consolidates P&L from offices in London (GBP), Tokyo (JPY), and Berlin (EUR). The CFO wants USD totals using the rate on the date of each transaction.

The data

tx = pd.read_excel("multi_ccy_tx.xlsx")
# date, office, account, amount, currency

rates = pd.read_csv("fx_rates_2026.csv")
# date, currency, rate_to_usd

The join

tx["date"] = pd.to_datetime(tx["date"])
rates["date"] = pd.to_datetime(rates["date"])

merged = tx.merge(rates, on=["date", "currency"], how="left")
missing = merged["rate_to_usd"].isna().sum()
print(f"Transactions with no rate: {missing}")

# If date doesn't match exactly (weekend), forward-fill from previous business day
if missing > 0:
    rates_indexed = rates.set_index(["currency", "date"]).sort_index()
    def lookup(row):
        try:
            sub = rates_indexed.loc[row["currency"]]
            avail = sub[sub.index <= row["date"]]
            return avail.iloc[-1]["rate_to_usd"] if len(avail) else None
        except KeyError:
            return None
    merged.loc[merged["rate_to_usd"].isna(), "rate_to_usd"] = merged[merged["rate_to_usd"].isna()].apply(lookup, axis=1)

merged["usd_amount"] = (merged["amount"] * merged["rate_to_usd"]).round(2)

Rollup

usd_pnl = (merged.groupby(["office", "account"])["usd_amount"]
                  .sum()
                  .unstack(fill_value=0))

usd_pnl["Total"] = usd_pnl.sum(axis=1)
usd_pnl.loc["Total"] = usd_pnl.sum(numeric_only=True)
usd_pnl.to_excel("pnl_usd.xlsx")