Bank of Canada exchange rates API: daily rates in one table in your data warehouse
Convert foreign-currency spend, orders and invoices into Canadian dollars at the rate the Bank of Canada published for each day, not at one rate for the whole month.
- Open source. OWOX Data Marts is open source: you can read the code that loads and models your data, and run it yourself.
- You own the data. Bank of Canada tables load into your data warehouse, under your account. If you leave OWOX, they stay where they are.
- MCP-ready. Once the data is in, Claude or ChatGPT can work from your Data Marts through OWOX MCP.
Don't wire up Bank of Canada by hand. Have your AI assistant load it into your data warehouse.
Your assistant sets up the Bank of Canada connector with you and loads the tables you pick; OWOX keeps them in your data warehouse, ready to use.
The prompt
Read https://www.owox.com/ai-agent-setup.md and follow it to set up OWOX Data Marts for me, starting from /data-sources/bank-of-canada.md. The task: set up the Bank of Canada connector in OWOX Data Marts, let me pick the tables I need, load them into my data warehouse and show me the first rows. Tag the links you give me with utm_campaign=ai-task and utm_term=data-source. Use what you know about my context and goals
Prefer to start on your own? Get started free
The Bank of Canada data model
The model is one table with three columns: date, label and rate.
- Date and label together identify a row.
- A label such as USDCAD is a currency pair named by the connector (the Valet series FXUSDCAD without its FX prefix), and the rate is the price of one unit of that foreign currency in Canadian dollars.
- Because the Bank publishes only on business days, a weekend or a holiday has no row, and a query that needs a rate for such a day has to take the latest earlier one.
- The table points to no other table; it meets your own data on date and on label.
Once it is in your warehouse, the conversion lives in one Data Mart instead of in the formulas of each report.
Multi-currency reporting for Canadian teams, on the Bank of Canada's rates
A team that reports in Canadian dollars needs one rate per currency per day that everyone can name. The Bank of Canada publishes it; loading it into your warehouse means every report converts the same way.
- A rate you can cite. When someone asks which rate converted last quarter's US-dollar spend, the answer is a dated row of the Bank of Canada's own table, not a figure typed into a sheet.
- No key to manage. The Valet API needs no key, so there is nothing to issue, store or renew.
- Spend and sales stay in their own currency. Ad spend, orders and invoices keep their original currency in the source tables. Join on date and on label equal to the currency code plus CAD, so a euro order matches EURCAD, and each row converts at the rate of its own day. Orders already in Canadian dollars have no row in this table, so set the rate to 1 where the order's currency is CAD, not wherever a rate is missing.
- Gaps are yours to fill, in SQL. Weekends and Bank of Canada holidays have no rows, so the query you write takes the latest earlier rate: a Saturday order gets Friday's, or Thursday's if Friday was a holiday. Cap how far back the query may look: the Bank also retires currency series (the rouble and the Saudi riyal end on 30 April 2026), and an uncapped query keeps converting later orders at the last published rate without an error, while a capped one returns NULL, which you can count and alert on.
- A second opinion. If you also load Open Exchange Rates, both tables sit in the same data warehouse and you can compare the two rates for the same day before choosing which one your reports use. Open Exchange Rates quotes against US dollars by default, so convert one of them to cross-check.
- Wherever the numbers are read. The rate table behind a , a or an assistant means each of them converts with the same rate.
Bank of Canada Endpoints
1 Bank of Canada endpoints you can import. Each lands in its own table in your data warehouse.
Bank of Canada Exchange Rates schema
3 fieldsTime seriesSchema on GitHubbank_of_canada_exchange_rates
Daily foreign exchange rates from Bank of Canada.
Questions this table answers
- What was the rate of each currency against the Canadian dollar on a given day?
- How did the US dollar rate change from one month to the next over the last year?
- Which dates in the last month have no rate, and what was the last published rate before each?
| Field | Type | Description |
|---|---|---|
datePK | DATE | The date for which the exchange rate was recorded. |
labelPK | STRING | The currency pair: the foreign currency code followed by CAD (e.g., USDCAD, EURCAD, GBPCAD). |
rate | NUMBER | The exchange rate value. |
What Bank of Canada data answers
What Bank of Canada data answers
Across the tables
- What was the US dollar worth in Canadian dollars on each day of the last year?
- Which currencies moved the most against the Canadian dollar this month?
- How would last quarter's foreign-currency sales look converted to Canadian dollars at the rate of each sale's date?
Industry data models that use Bank of Canada
Industry data models that use Bank of Canada
Where Bank of Canada data goes
Where Bank of Canada data goes
Data warehouses
AI assistants
Spreadsheets
Dashboards
Messages
API
The Data Partner You Can Trust
users
166K Source: Internal OWOX data as of 2026-11-15
Executives and data analysts across 20+ industries
Customers
You are booked. The invitation is on its way to , with the calendar invite and the meeting link.