Exchange Rates

The extension provides table functions to fetch exchange rates from 10 different sources - 7 free central bank APIs and 3 paid commercial APIs.

Quick Start

-- Load extension
INSTALL monetary FROM community;
LOAD monetary;

-- Fetch ECB rates (free, no API key needed)
SELECT currency_from, currency_to, rate, valid_from
FROM ecb_exchange_rates()
LIMIT 5;

Storing Exchange Rates

For production use, store rates locally rather than fetching on every query:

-- Create a persistent rates table
CREATE TABLE exchange_rates AS
SELECT * FROM ecb_exchange_rates();

-- Check what we have
SELECT COUNT(*) as total_rates,
       MIN(valid_from) as oldest,
       MAX(valid_from) as newest
FROM exchange_rates;

Update Rates Daily

-- Replace with fresh rates
CREATE OR REPLACE TABLE exchange_rates AS
SELECT * FROM ecb_exchange_rates();

-- Or append historical data
INSERT INTO exchange_rates
SELECT * FROM ecb_exchange_rates()
WHERE valid_from > (SELECT MAX(valid_from) FROM exchange_rates);

Multi-Source Rates Table

Combine multiple sources for better coverage:

CREATE TABLE exchange_rates AS
-- ECB for EUR-based rates
SELECT * FROM ecb_exchange_rates()
UNION ALL
-- Bank of Canada for CAD rates
SELECT * FROM boc_exchange_rates()
UNION ALL
-- RBA for AUD rates
SELECT * FROM rba_exchange_rates();

Schema

All exchange rate functions return the same schema:

Column	Type	Description
currency_from	ENUM	Source currency (ISO 4217)
currency_to	ENUM	Target currency (ISO 4217)
rate	DECIMAL(20,10)	Exchange rate
valid_from	TIMESTAMPTZ	Rate publication timestamp
valid_to	TIMESTAMPTZ	Rate expiry (NULL if current)
source	ENUM	Data source identifier
source_id	VARCHAR	Source-specific identifier
fetched_at	TIMESTAMPTZ	When data was fetched

Free Sources (No API Key)

ECB - European Central Bank

The European Central Bank publishes daily reference rates for 30+ currencies.

SELECT currency_from, currency_to, rate, valid_from
FROM ecb_exchange_rates()
WHERE currency_to IN ('USD', 'GBP', 'JPY', 'CHF');

• Base: EUR
• Update: Daily ~16:00 CET (weekdays)
• Currencies: USD, GBP, JPY, CHF, SEK, NOK, DKK, PLN, CZK, HUF, RON, BGN, TRY, AUD, CAD, CNY, HKD, IDR, ILS, INR, KRW, MXN, MYR, NZD, PHP, SGD, THB, ZAR, BRL

Bank of Canada (BOC)

SELECT currency_to, rate, valid_from
FROM boc_exchange_rates()
ORDER BY valid_from DESC;

• Base: CAD
• Update: Daily ~16:30 ET

Reserve Bank of Australia (RBA)

SELECT * FROM rba_exchange_rates()
WHERE currency_to = 'USD';

• Base: AUD
• Update: Daily 4:00 PM AEST
• License: CC BY 4.0 (attribution required)

Norges Bank (Central Bank of Norway)

SELECT * FROM norges_bank_exchange_rates();

• Base: NOK
• Update: Daily ~16:00 CET

Czech National Bank (CNB)

SELECT * FROM cnb_exchange_rates();

• Base: CZK
• Update: Daily ~14:30 CET

Swiss National Bank (SNB)

SELECT * FROM snb_exchange_rates();

• Base: CHF
• Update: Daily 11:00 Zurich
• Currencies: EUR, USD, GBP, JPY (limited set)

FreeGoldAPI - Historical Gold Prices

768 years of gold price history!

-- Get recent gold prices
SELECT valid_from, rate as usd_per_oz, data_source
FROM freegold_exchange_rates()
WHERE data_source = 'yahoo_finance'
ORDER BY valid_from DESC
LIMIT 10;

Filter by data source for different timeframes:

-- Daily Yahoo Finance (2025+)
SELECT * FROM freegold_exchange_rates()
WHERE data_source = 'yahoo_finance';

-- Monthly World Bank (1960-2024)
SELECT * FROM freegold_exchange_rates()
WHERE data_source = 'worldbank';

-- Annual historical (1257-1959)
SELECT * FROM freegold_exchange_rates()
WHERE data_source = 'historical';

Paid Sources (API Key Required)

Configure API keys using DuckDB secrets:

-- Option 1: Direct secret
CREATE SECRET (TYPE monetary, SOURCE <source>, SECRET 'your-api-key');

-- Option 2: From environment variable
CREATE SECRET (TYPE monetary, PROVIDER env, SOURCE <source>);

Twelve Data

Real-time forex and crypto rates.

-- Set API key (get free key at twelvedata.com)
CREATE SECRET (TYPE monetary, SOURCE twelvedata, SECRET 'your-key');

-- Or from TWELVEDATA_API_KEY env var
CREATE SECRET (TYPE monetary, PROVIDER env, SOURCE twelvedata);

-- Fetch rates
SELECT * FROM twelvedata_exchange_rates();

• Base: USD
• Update: Real-time
• Free tier: 800 API calls/day
• Includes: Forex, crypto (BTC, ETH)

Open Exchange Rates (OXR)

CREATE SECRET (TYPE monetary, SOURCE openexchangerates, SECRET 'your-app-id');
SELECT * FROM openexchangerates_exchange_rates();

• Base: USD (free tier), configurable (paid)
• Update: Hourly
• Free tier: 1,000 requests/month

GoldAPI

Real-time precious metal prices.

CREATE SECRET (TYPE monetary, SOURCE goldapi, SECRET 'your-token');
SELECT * FROM goldapi_exchange_rates();

• Returns: XAU/USD, XAG/USD
• Update: Real-time

Practical Examples

Find Best Rate for a Currency Pair

WITH all_rates AS (
    SELECT 'ECB' as provider, * FROM ecb_exchange_rates()
    UNION ALL
    SELECT 'BOC' as provider, * FROM boc_exchange_rates()
)
SELECT provider, currency_from, currency_to, rate, valid_from
FROM all_rates
WHERE currency_to = 'USD'
ORDER BY valid_from DESC
LIMIT 5;

Track Rate Changes Over Time

-- Store historical rates
CREATE TABLE rate_history AS
SELECT *, current_timestamp as snapshot_time
FROM ecb_exchange_rates();

-- Later, append new snapshot
INSERT INTO rate_history
SELECT *, current_timestamp as snapshot_time
FROM ecb_exchange_rates();

-- Compare EUR/USD over time
SELECT snapshot_time, rate
FROM rate_history
WHERE currency_from = 'EUR' AND currency_to = 'USD'
ORDER BY snapshot_time;

Export Rates to Parquet

COPY (SELECT * FROM ecb_exchange_rates())
TO 'ecb_rates.parquet' (FORMAT PARQUET);

Summary Table

Function	Provider	API Key	Base	Update
ecb_exchange_rates()	ECB	No	EUR	Daily
boc_exchange_rates()	Bank of Canada	No	CAD	Daily
rba_exchange_rates()	RBA	No	AUD	Daily
norges_bank_exchange_rates()	Norges Bank	No	NOK	Daily
cnb_exchange_rates()	CNB	No	CZK	Daily
snb_exchange_rates()	SNB	No	CHF	Daily
freegold_exchange_rates()	FreeGoldAPI	No	USD	Daily
twelvedata_exchange_rates()	Twelve Data	Yes	USD	Real-time
openexchangerates_exchange_rates()	OXR	Yes	USD	Hourly
goldapi_exchange_rates()	GoldAPI	Yes	USD	Real-time