Foreign exchange rates

lhbox duckdb --catalog commons/pubdata

SELECT date, currency, round(rate_per_eur, 4) AS per_eur, round(usd_per_unit, 4) AS usd_per_unit
FROM commons_pubdata.reference.fx_rates
WHERE date = (SELECT max(date) FROM commons_pubdata.reference.fx_rates)
  AND currency IN ('USD', 'GBP', 'JPY', 'CHF', 'CAD', 'AUD', 'CNY', 'BRL', 'INR', 'MXN')
ORDER BY currency;

INSTALL iceberg; LOAD iceberg; INSTALL httpfs; LOAD httpfs;
CREATE SECRET pubdata (TYPE S3, ENDPOINT 'lakehousebox-public-data.b-cdn.net', URL_STYLE 'path', REGION 'us-east-1');
ATTACH ':memory:' AS pubdata;
CREATE SCHEMA pubdata.reference;
CREATE VIEW pubdata.reference.fx_rates AS SELECT * FROM iceberg_scan('s3://commons--pubdata/reference/fx_rates');

SELECT date, currency, round(rate_per_eur, 4) AS per_eur, round(usd_per_unit, 4) AS usd_per_unit
FROM pubdata.reference.fx_rates
WHERE date = (SELECT max(date) FROM pubdata.reference.fx_rates)
  AND currency IN ('USD', 'GBP', 'JPY', 'CHF', 'CAD', 'AUD', 'CNY', 'BRL', 'INR', 'MXN')
ORDER BY currency;

# Foreign exchange rates

*Daily Euro and US Dollar reference exchange rates for 41 currencies since 1999.*

Daily foreign exchange reference rates published by the European Central Bank (ECB) at ~16:00 CET on every TARGET business day from 1999-01-04 on. Unpivoted to one row per calendar business day and ISO 4217 currency code, with three ready-to-multiply rates: units of the currency per 1 EUR (`rate_per_eur`), EUR per 1 unit of the currency (`eur_per_unit`), and USD per 1 unit of the currency (`usd_per_unit`, cross-computed from that day's ECB USD/EUR reference rate). Joins `reference.countries` on `currency = currency_code` and time-series tables on `date`.

## At a glance

| | |
|---|---|
| Table | `pubdata.reference.fx_rates` |
| Category | Reference · Daily, Time series, Country |
| Rows | n/a |
| Size | n/a data file(s) |
| Iceberg format | v? |
| Last refreshed | not loaded yet |
| Source version | n/a |
| Refresh cadence | daily (replace) |
| Coverage | 41 major world currencies (ISO 4217); 1999-01-04 to the latest TARGET business day |

## Origin

- **Source:** [European Central Bank (Euro foreign exchange reference rates)](https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html)
- **Licence:** Free reuse with attribution (ECB statistics reuse policy)
- **Attribution:** Source: European Central Bank (ECB), Euro foreign exchange reference rates.
- **Recipe:** `pubdata/recipes/fx_rates.py` in the LakehouseBox repository; registry entry `pubdata/registry/fx_rates.yaml`.

## Schema

| column | type | description |
|---|---|---|
| `date` | date | reference business day (CET, no weekend or TARGET holiday rows) |
| `currency` | string | ISO 4217 3-letter currency code (joins reference.countries on currency_code) |
| `rate_per_eur` | double | units of `currency` per 1 EUR (the official ECB quote) |
| `eur_per_unit` | double | EUR per 1 unit of `currency` (multiply an amount in `currency` by this to get EUR) |
| `usd_per_unit` | double | USD per 1 unit of `currency` (multiply an amount in `currency` by this to get USD) |

**Join keys:** `date`, `currency`.

## For agents

The ECB publishes rates only on TARGET business days (no Saturdays, Sundays or ECB holidays). When converting daily
transactions that include weekends or holidays, use DuckDB's `ASOF JOIN ... ON t.currency = fx.currency AND
t.date >= fx.date` so weekend dates pick up the preceding Friday's rate. `EUR` itself is included with
`rate_per_eur = 1.0` and `eur_per_unit = 1.0` so a join never drops Euro-denominated rows.

## Example SQL (DuckDB)

```sql
-- latest exchange rates for the major currencies
SELECT date, currency, round(rate_per_eur, 4) AS per_eur, round(usd_per_unit, 4) AS usd_per_unit
FROM pubdata.reference.fx_rates
WHERE date = (SELECT max(date) FROM pubdata.reference.fx_rates)
  AND currency IN ('USD', 'GBP', 'JPY', 'CHF', 'CAD', 'AUD', 'CNY', 'BRL', 'INR', 'MXN')
ORDER BY currency;
-- convert your daily invoices in local currency into EUR and USD (carrying Friday's rate over weekends)
SELECT i.invoice_id, i.invoice_date, i.currency, i.amount,
       round(i.amount * fx.eur_per_unit, 2) AS amount_eur,
       round(i.amount * fx.usd_per_unit, 2) AS amount_usd
FROM my.invoices AS i
ASOF JOIN pubdata.reference.fx_rates AS fx
  ON i.currency = fx.currency AND i.invoice_date >= fx.date;
```

## How to read it

With a LakehouseBox account:

```bash
# with a LakehouseBox account (any organisation): the public catalog by name, read-only
lhbox duckdb --catalog commons/pubdata     # opens DuckDB attached as `commons_pubdata`
SELECT * FROM commons_pubdata.reference.fx_rates LIMIT 10;
```

Public URL of the table's storage: `https://s3.lakehousebox.com/commons--pubdata/reference/fx_rates/`. This card: `https://lakehousebox.com/pubdata/cards/fx_rates.md`; the page: https://lakehousebox.com/public-data/fx_rates. Every dataset as a table: `commons_pubdata.meta.datasets`; as Markdown: https://lakehousebox.com/public-data.md.