Build a Foreign Exchange Revaluation Report

A company that invoices customers in euros and pays suppliers in dollars carries balances in those currencies at every month end. They were recorded in the home currency at the exchange rate on the day of each transaction, but the rates have moved since. Accounting rules require monetary items — receivables, payables, foreign-currency bank accounts — to be shown at the closing rate, and the difference goes to profit and loss as an unrealised exchange gain or loss. Doing it by hand means a spreadsheet of open items, a lookup of rates, and careful thought about signs, because a stronger euro is good news for a euro receivable and bad news for a euro payable. This page builds the revaluation with pandas: open items in EUR and USD revalued at the September closing rates, gains and losses per item and per currency, the journal and its reversal, and a report that ties back to the ledger. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

Two inputs. The open items list holds every unsettled foreign-currency item at 30 September — receivables (AR), payables (AP) and the balances on the two foreign-currency bank accounts — with the currency amount and the date it was booked (for bank accounts, the date of the last revaluation). The rates table gives sterling per unit of currency for each date needed. The home currency is GBP.

Step 1 — Join each item to its booking rate and the closing rate

import pandas as pd

items = pd.read_excel("open_items.xlsx", parse_dates=["Booked"])
rates = pd.read_excel("fx_rates.xlsx", parse_dates=["Date"])
close_date = pd.Timestamp("2026-09-30")

items = items.merge(rates.rename(columns={"Date": "Booked", "GBP per unit": "Booked rate"}),
                    on=["Booked", "Currency"], how="left", validate="many_to_one")
closing = rates[rates["Date"] == close_date].set_index("Currency")["GBP per unit"]
items["Closing rate"] = items["Currency"].map(closing)
assert items[["Booked rate", "Closing rate"]].notna().all().all(), "missing exchange rate"

items["Booked GBP"] = (items["Amount"] * items["Booked rate"]).round(2)
items["Revalued GBP"] = (items["Amount"] * items["Closing rate"]).round(2)
print(items[["Reference", "Currency", "Amount", "Booked rate", "Closing rate", "Booked GBP", "Revalued GBP"]].to_string(index=False))

Each item needs two rates: the one it was booked at, and the closing rate at the month end. The booking rate is joined on the date and currency, with validate confirming that every item matches at most one rate, and the closing rate is a simple lookup by currency. A missing rate stops the run, because an item revalued with a missing rate would silently become zero. Multiplying the currency amount by each rate gives the item's value in sterling as booked and as revalued, both rounded to pence as the ledger holds them. The euro strengthened in September, from 0.8545 to 0.8612 pounds, while the dollar weakened from 0.7488 to 0.7395, so euro items are now worth more in sterling and dollar items less.

Step 2 — Gains and losses with the right signs

SIGN = {"AR": 1, "Bank": 1, "AP": -1}            # assets gain when the currency strengthens; liabilities lose
items["Gain/(loss)"] = ((items["Revalued GBP"] - items["Booked GBP"]) * items["Type"].map(SIGN)).round(2)
print(items[["Type", "Reference", "Currency", "Gain/(loss)"]].to_string(index=False))

by_currency = items.pivot_table(index="Currency", columns="Type", values="Gain/(loss)", aggfunc="sum", fill_value=0).round(2)
by_currency["Total"] = by_currency.sum(axis=1)
print(by_currency.to_string())
print("net unrealised gain/(loss):", round(items["Gain/(loss)"].sum(), 2))

The difference between the revalued and booked sterling amounts is the revaluation, but whether it is a gain or a loss depends on what the item is. A receivable or a bank balance is an asset: if the currency strengthens, the asset is worth more pounds, which is a gain. A payable is a liability: if the currency strengthens, more pounds are needed to settle it, which is a loss. One small mapping of item type to sign handles that, instead of a set of special cases. Summarised by currency and type, the result shows where the exposure lies — the euro receivables and the euro bank account gain from the stronger euro, partly offset by the euro payable, while the dollar receivable and bank balance lose and the dollar payable gains. Overall the euro positions gain 416.99 and the dollar positions lose 311.26, a net unrealised gain of 105.73 for the month.

Step 3 — The journal, its reversal and the ledger check

net = round(items["Gain/(loss)"].sum(), 2)
control = {"AR": "Trade receivables", "AP": "Trade payables", "Bank": "Bank — foreign currency"}
lines = []
for typ, g in items.groupby("Type"):
    gain = round(g["Gain/(loss)"].sum(), 2)          # a gain debits the account: asset up, or liability down
    if gain:
        lines.append({"Account": control[typ], "Debit": max(gain, 0), "Credit": max(-gain, 0)})
fx_line = {"Account": "Unrealised FX gains and losses", "Debit": max(-net, 0), "Credit": max(net, 0)}
journal = pd.DataFrame(lines + [fx_line]).round(2)
assert round(journal["Debit"].sum() - journal["Credit"].sum(), 2) == 0, journal
reversal = journal.rename(columns={"Debit": "Credit", "Credit": "Debit"})[["Account", "Debit", "Credit"]]
print(journal.to_string(index=False))

ledger_ar_gbp = 40_163.92                        # receivables control account before revaluation, from the trial balance
schedule_ar_gbp = items.loc[items["Type"] == "AR", "Booked GBP"].sum().round(2)
print(f"AR open items {schedule_ar_gbp:,.2f} vs ledger {ledger_ar_gbp:,.2f}: difference {schedule_ar_gbp - ledger_ar_gbp:,.2f}")

The journal moves each control account by its own share of the result. A gain on an asset means the asset is worth more, so the account is debited; a gain on a liability means less is owed, so the payable is debited too; losses are credits. Here the receivables lose 82.74 overall, because the dollar invoice's loss outweighs the euro invoices' gains, the payables gain 90.88 and the bank accounts gain 97.59. The net, a gain of 105.73, is credited to the unrealised exchange account. Debits must equal credits, which the assertion checks. Most companies reverse the revaluation on the first day of the next month and revalue again from the original booking rates at the next month end; the reversal is the same journal with debits and credits swapped. When an item is finally settled, the difference between the booking rate and the payment rate becomes a realised gain or loss, which is recorded by the payment, not by this report.

Before posting, the open items must agree with the ledger: the sterling value of the receivables at their booking rates should equal the receivables control account for foreign-currency customers. A difference means the open-item list is incomplete or an item was booked at a different rate from the one in the rates table. Here the three receivables come to 40,163.92 at their booking rates, matching the control account exactly, so the journal can be posted.

Step 4 — The report

with pd.ExcelWriter("fx_revaluation_2026-09.xlsx") as xw:
    items.assign(Booked=items["Booked"].dt.date).to_excel(xw, sheet_name="Items", index=False)
    by_currency.reset_index().to_excel(xw, sheet_name="By currency", index=False)
    journal.to_excel(xw, sheet_name="Journal 30 Sep", index=False)
    reversal.to_excel(xw, sheet_name="Reversal 1 Oct", index=False)
    rates[rates["Date"] == close_date].to_excel(xw, sheet_name="Closing rates", index=False)
print("written")

The workbook lists every item with both rates and both sterling values, summarises by currency and type, holds the journal and its reversal, and records the closing rates used, so the reviewer can check them against the published source. Keeping the rates sheet in each month's report is the simplest audit trail for "which rate did we use?" — a question that always comes up at year end. Rates themselves can be loaded from a central bank feed into a dated table, as in Convert Multi-Currency Transactions with an FX Rate Table, which also covers keeping a dated rate table.

Common pitfalls

• Revaluing non-monetary items. Inventory and fixed assets stay at historical rates.
• One sign for everything. Payables lose when the currency strengthens; map signs by item type.
• Missing rates. Assert that every item has both rates, or values silently become zero.
• No reversal. Without it, next month's revaluation double counts.
• Unreconciled open items. Agree the booking-rate values to the ledger before posting.

Conclusion

To revalue foreign-currency balances, join every open receivable, payable and bank balance to its booking rate and the closing rate, compute the sterling value at each, and take the difference with a sign that depends on whether the item is an asset or a liability. Summarise by currency and type, post the net to unrealised exchange gains and losses with the control accounts moved by their own changes, reverse it the next day, and reconcile the open items to the ledger first.

Frequently asked questions

How do I calculate an unrealised FX gain in Python? Multiply each open item by its booking rate and by the closing rate, take the difference, and give it a positive sign for assets and a negative sign for liabilities.

Which balances are revalued at month end? Monetary items in foreign currency: receivables, payables, bank balances and loans; not inventory or fixed assets.

Why is the FX revaluation reversed the next month? So that each month end revalues from the original booking rates and the gain or loss is not counted twice.

What is the difference between realised and unrealised FX gains? Unrealised gains come from revaluing open items; realised gains arise when an item is settled at a rate different from its booking rate.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build an Intercompany Reconciliation — FX differences between entities.
• Build an Aged Creditors Report — the open payables.
• Join Rows to Effective Date Ranges with pandas — rates by date.