Handling Multi Currency Columns in Workbooks and Tables

A sales sheet lists orders from four countries. The amount column reads 1,200 in one row, 980 in the next and 150,000 on the third, and the total at the bottom is 152,180 of something. Mixed-currency columns produce confident-looking sums that mean nothing, and they are easy to create because the symbols are hidden by number formats and the data arrived from several systems. Fixing it is not complicated. It takes a discipline: carry the currency with the amount, convert once with a recorded rate, and never add across currencies. This article sets out that discipline for tables and for the workbooks built from them. It supports Excel reports that finance teams rely on, and it goes with handling currency and locale in generated workbooks, which covers display.

An amount is a pair

The core idea: a money value is an amount and a currency, and they are never separated. In a table that means two columns, amount and currency, with the currency a three-letter ISO 4217 code (USD, EUR, JPY). A CHECK constraint or a foreign key to a currency table keeps the codes valid.

CREATE TABLE currencies (
  code        char(3) PRIMARY KEY,
  minor_units smallint NOT NULL        -- 2 for USD, 0 for JPY, 3 for KWD
);

CREATE TABLE order_lines (
  order_id   bigint,
  line_no    int,
  amount     numeric(18,4) NOT NULL,
  currency   char(3) NOT NULL REFERENCES currencies(code),
  PRIMARY KEY (order_id, line_no)
);

Use numeric with more precision than the currency needs, so intermediate conversions do not lose accuracy, and round to the currency's minor units when the policy says. In a workbook, the same pair is two columns; do not rely on a symbol in a number format to carry the code.

Never add across currencies

SUM(amount) over a column with several currencies adds euros to yen. The result is a number with no unit. Protect against it:

• Group by currency in any total: SELECT currency, sum(amount) FROM order_lines GROUP BY currency.

• Or convert first to a reporting currency and sum the converted column.

• Add a check that fails when a total is requested over more than one currency without conversion.

In Excel, SUMIFS(amount, currency, "EUR") totals one currency at a time. A subtotal row per currency is honest. A grand total makes sense only in the reporting currency.

Conditional formatting can flag a mixed-currency range: a rule that highlights any amount cell whose currency differs from the first row's catches the case when someone filters to "all" and sums.

The rate table

Conversion needs exchange rates, and rates are data with a source and a date.

CREATE TABLE fx_rates (
  rate_date   date NOT NULL,
  from_ccy    char(3) NOT NULL REFERENCES currencies(code),
  to_ccy      char(3) NOT NULL REFERENCES currencies(code),
  rate        numeric(18,8) NOT NULL CHECK (rate > 0),
  source      text NOT NULL,                    -- e.g. 'ECB reference rate'
  PRIMARY KEY (rate_date, from_ccy, to_ccy)
);

The convention matters: rate is the number of to_ccy units for one from_ccy, and every rate in the table follows the same direction. Say so in a column comment. Keep a rate per day (or per month for average-rate policies), load it from a defined source such as a central bank reference rate or your treasury system, and record the source. Mixing sources within a period makes the totals unreproducible.

Weekends and holidays have no published rate. Decide the rule: use the last available rate before the date. In SQL, a lateral subquery finds it:

SELECT l.order_id, l.line_no, l.amount, l.currency, o.order_date,
       r.rate, r.rate_date,
       l.amount * r.rate AS amount_usd
FROM order_lines l
JOIN orders o USING (order_id)
JOIN LATERAL (
  SELECT rate, rate_date
  FROM fx_rates
  WHERE from_ccy = l.currency AND to_ccy = 'USD' AND rate_date <= o.order_date
  ORDER BY rate_date DESC
  LIMIT 1
) r ON true
WHERE l.currency <> 'USD'
UNION ALL
SELECT l.order_id, l.line_no, l.amount, l.currency, o.order_date, 1, o.order_date, l.amount
FROM order_lines l JOIN orders o USING (order_id)
WHERE l.currency = 'USD';

Rows in the reporting currency convert at 1 without a lookup. The join returns the rate and the date actually used, which go into the output beside the converted amount. If no rate exists on or before the date, an inner lateral join drops the row; use LEFT JOIN LATERAL and report missing rates as errors, never silently as zero.

Which rate applies is a policy

Accounting standards allow different rates for different purposes: the rate on the transaction date for recording a sale; the average rate for the period for an income statement; the closing rate for balance sheet items. Which applies is for the finance team to say, and it must be written in the report. The query implements it: transaction date, avg(rate) over the month, or the rate on the period end date. Changing the policy changes the numbers, so the policy goes under change control like any other rule (version controlling the query behind an Excel report).

Revaluation, gains and losses on settlement and hedging are beyond a report's job. The report shows converted figures under a stated policy and leaves the accounting treatment to the accounting system.

Minor units and rounding

Currencies differ in how many decimals they use. JPY and KRW have none; USD and EUR have two; BHD and KWD have three. The currencies table holds the count, and display and rounding follow it.

Round after conversion, once, by the policy (rounding rules that survive audit in spreadsheets and SQL): round(amount * rate, 2) for a two-decimal reporting currency. Rounding the original amount before conversion, or rounding the rate, adds error. Keep the rate at full precision (eight decimals is typical) and round the product.

Avoid integer minor units across currencies unless the divisor is looked up per currency; "cents" means different things for different codes, and a column of integers mixed from yen and dollars is a unit trap.

Showing it in the workbook

Write the original amount and its currency in adjacent columns, the rate and the converted amount after them:

Format each amount column with a number format that fits its currency (zero decimals for JPY), applied by row if the column mixes currencies, or split the sheet by currency. A single column format of #,##0.00 on a yen column shows 15,000.00, which is not wrong and looks odd. Put the reporting currency in the header ("Amount, USD") and the rate source and policy on the README sheet. Totals use SUMIFS by currency or the converted column.

Avoid symbol-only formats like $#,##0.00 on columns that hold several currencies: they display a dollar sign on euros (handling currency and locale in generated workbooks).

Checks

Build checks into the job:

• Every row has a valid currency code from the table.

• Every converted row has a rate and a rate date within a stated distance of the transaction date (for example, within 5 days), or the report lists it as an exception.

• The sum of converted amounts equals the sum computed independently by currency and rate group.

• No total is produced over mixed codes.

• The reporting currency rows have a rate of exactly 1.

A control comparing the converted total to the ledger's translated figure shows differences that come from policy or timing. Explain them; do not plug them.

Importing mixed-currency workbooks

When a workbook arrives with amounts in several currencies, check how the currency is conveyed. A currency column is good. A symbol in the number format is lost on import: the cell holds 1200, and the euro sign lived in the format. A symbol typed into a text cell ("€1,200") needs parsing, and ambiguous symbols ($ for several dollars, ¥ for yen or yuan) cannot be resolved without context. Require the code in the template, as its own column, and reject rows without one (data types per column in templates that feed a database).

When a single currency would be simpler

If the business transacts in one currency, store one and skip the machinery. If a report is for one market, filter to its currency and show that. If conversion is only for convenience, such as a rough comparison on a dashboard, label it "approximate, at today's rate" and do not feed it into accounts. The full discipline above is for figures that people rely on: invoices, ledgers, management packs. For rate sources, central banks publish reference rates, such as the European Central Bank euro foreign exchange reference rates, and the currency codes and minor units are defined in the ISO 4217 standard.