Exchange rate data for analytics and BI

Revenue arrives in EUR, costs in GBP, payroll in INR – and the board wants one number. That requires exchange rate data for analytics: a clean daily rate table in your warehouse that every model, dashboard and notebook joins against. fxapi delivers daily time series back to 1999 for 190+ currencies as CSV, ready for BigQuery, Snowflake, Postgres or any warehouse with a CSV loader. This page shows the load pattern, a dbt-style conversion model and SQL for constant-currency reporting.

Why BI teams need their own exchange rate table

Spreadsheets with pasted rates and ad-hoc conversions in dashboard formulas cause three recurring problems:

• Inconsistent numbers. Two dashboards convert the same revenue with different rates and disagree.
• Missing history. Year-over-year comparisons need rates for every past day, not just today.
• Currency effects hide performance. When the dollar strengthens, international revenue looks weaker even if volumes grew. Without a constant-currency view, nobody can tell the difference.

One warehouse table with one rate per currency per day, from one source, fixes all three.

How fxapi delivers exchange rate data for analytics

The /v1/range CSV has four columns: datetime,base_currency,currency,value. It is long-format, one row per currency per timestamp – exactly the shape a warehouse table wants. Read more about the exchange rate time series API.

Load workflow

1. Pick one base currency. USD or your reporting currency. Every other pair is derived as a cross rate.
2. Backfill once. Loop over calendar years from 1999 (or the year your data starts), calling /v1/range with accuracy=day and format=csv for each year – the endpoint allows up to 366 days per call.
3. Load into a raw table with the warehouse’s native CSV loader (see below).
4. Schedule a daily increment. Each morning, fetch yesterday’s rates with /v1/range for a one-day window or /v1/historical, and append.
5. Model it. Build a clean fx_rates_daily model with a date column and forward-fill any calendar dates that have no rate row.
6. Join everywhere. Fact tables join on (currency, date) and divide or multiply once, in the model – never in a dashboard formula.

Code example: backfill with Python

import datetime as dt, os, pathlib, requests

HEADERS = {"apikey": os.environ["FXAPI_KEY"]}
out = pathlib.Path("fx_csv"); out.mkdir(exist_ok=True)
yesterday = dt.date.today() - dt.timedelta(days=1)

for year in range(1999, yesterday.year + 1):
    start = dt.date(year, 1, 1)
    end = min(dt.date(year, 12, 31), yesterday)
    params = {
        "base_currency": "USD",
        "currencies": "EUR,GBP,JPY,CHF,INR,BRL,AUD,CAD",
        "datetime_start": f"{start}T00:00:00Z",
        "datetime_end": f"{end}T23:59:59Z",
        "accuracy": "day",
        "format": "csv",
    }
    r = requests.get("https://api.fxapi.com/v1/range", headers=HEADERS, params=params, timeout=60)
    r.raise_for_status()
    (out / f"fx_{year}.csv").write_bytes(r.content)
    print(year, r.headers.get("X-RateLimit-Remaining-Quota-Month"))

Then load the files into your warehouse:

# PostgreSQL
psql "$DATABASE_URL" -c "\copy fx_rates_raw FROM 'fx_csv/fx_2025.csv' WITH (FORMAT csv, HEADER true)"

# BigQuery
bq load --source_format=CSV --skip_leading_rows=1 analytics.fx_rates_raw 'fx_csv/fx_2025.csv' \
  datetime:TIMESTAMP,base_currency:STRING,currency:STRING,value:FLOAT64

# Snowflake (SnowSQL)
snowsql -q "PUT file://fx_csv/*.csv @%fx_rates_raw; COPY INTO fx_rates_raw FROM @%fx_rates_raw FILE_FORMAT = (TYPE = CSV SKIP_HEADER = 1);"

Loader references: PostgreSQL COPY, BigQuery CSV loading, Snowflake COPY INTO. For more Python patterns see the Python guide.

A dbt-style conversion model

With USD as the base, value is the number of currency units per 1 USD. Converting a local amount to USD is a division; a cross rate is a ratio of two USD rates.

-- models/fx_rates_daily.sql
select
    cast(datetime as date) as rate_date,
    currency,
    value                  as units_per_usd
from {{ source('fxapi', 'fx_rates_raw') }}
where base_currency = 'USD'

-- models/fct_revenue_usd.sql
select
    o.order_id,
    o.order_date,
    o.currency,
    o.amount_local,
    o.amount_local / r.units_per_usd as amount_usd
from {{ ref('stg_orders') }} o
join {{ ref('fx_rates_daily') }} r
  on r.currency = o.currency
 and r.rate_date = o.order_date

If orders can be in the base currency itself, union a row with units_per_usd = 1 for USD. The math behind cross rates is explained in base currency and cross rates.

Constant-currency reporting in SQL

Restate this year’s revenue at last year’s average rates to separate growth from currency movements:

with prior_year_avg as (
    select currency, avg(units_per_usd) as avg_rate_py
    from {{ ref('fx_rates_daily') }}
    where rate_date between '2025-01-01' and '2025-12-31'
    group by currency
)
select
    date_trunc('month', o.order_date)          as month,
    sum(o.amount_local / r.units_per_usd)      as revenue_usd_actual,
    sum(o.amount_local / p.avg_rate_py)        as revenue_usd_constant_fx
from {{ ref('stg_orders') }} o
join {{ ref('fx_rates_daily') }} r on r.currency = o.currency and r.rate_date = o.order_date
join prior_year_avg p             on p.currency = o.currency
where o.order_date >= '2026-01-01'
group by 1
order by 1

Prefer an API-calculated average? /v1/average with period=year returns the same kind of figure, plus min and max, as CSV – see the average exchange rates API.

Pitfalls and best practices

• Use one base and one source. Mixing rate tables from different providers produces small, persistent reconciliation differences.
• Fill calendar gaps explicitly. Join to a date spine and forward-fill so a missing row never drops a fact from a report.
• Store base_currency in the table. It makes the direction of every rate unambiguous.
• Keep legacy codes. HRK, LTL, VEF and other retired codes stay available for history, so old transactions still convert. Background: currency redenominations and code changes.
• Respect the span limits. accuracy=day allows up to 366 days per request; hour up to 7 days within the last 3 months.
• Monitor quota. Log X-RateLimit-Remaining-Quota-Month in your pipeline.

Give every dashboard the same exchange rates

/v1/range and its CSV export are part of the Professional plan ($34.99 per month, 600,000 requests); monthly averages and fluctuation CSVs work on every plan, including Free. Compare options on the pricing page, then create your API key and run the backfill script against your warehouse today.

Everything on this page runs on the fxapi foreign exchange rates API: one key, 190+ currencies, data back to 1999.

Frequently asked questions