Which exchange rate do you use? Day/month/quarter?
- It depends. If these reports are being used by your finance team, ultimately it is up to the finance team to decide which exchange rate to use, and for the data team to implement it. That’s because currency conversions have a real impact on their reports! For example, in Australia, it’s regulation that accountants use the month-end currency exchange rate for financial reporting.
- If your finance team truly doesn’t have strong opinions on this, my preference is to use a daily exchange rate.
How do you model this?
Source data
We generate a table of exchange rates in our data warehouse. Currently, it’s a (sub-optimal) process of building a Google Sheet that uses the googlefinance function to grab exchange rates, and loading the Google Sheet into our warehouse.
Ideally, I’d like to use a Singer tap on Stitch for this, but there currently isn’t a reliable tap to do this.
Build a model of exchange rates
I would build a model with one record per currency, per day, with the fields:
- date
- source_currency: ISO currency code, e.g. AUD, GBP, USD
- target_currency: ISO currency code, e.g. AUD, GBP, USD
- rate: exchange rate to convert 1 unit of the source currency to the target currency
Any business logic on which exchange rate to use should be included in this model.
Perform the conversion
I would convert prices at the lowest granularity possible (i.e. on the orders table, rather than a rollup of daily_revenue).
For each model with multiple currencies, I tend to store:
price_local
currency # the ISO code for the currency, e.g. AUD, GBP, USD
price_usd # or whatever the target exchange rate is
This is what the SQL would look like to convert to USD. Note that I’ve done it in a way that can be extended if you wish to convert to other currencies too.
with orders as (
select * from {{ ref('orders') }}
),
exchange_rates as (
select * from {{ ref('exchange_rates') }}
),
select
orders.order_id,
orders.price as price_local,
orders.currency,
orders.price * usd_exchange_rates.rate as price_usd
from orders
left join exchange_rates as usd_exchange_rates
on usd_exchange_rates.target_currency = 'USD'
and date_trunc('day', orders.ordered_at) = usd_exchange_rates.date
and orders.currency = usd_exchange_rates.source_currency
Things to be mindful of:
- The timezone for conversion - it’s up to your business to decide the logic here
- Accidentally using the inverse rate (I’ve done this more times than I care to admit)
How do you handle currencies in your BI tool?
One thing to be really conscious of is that you want to prevent your users from trying to aggregate the price_local field. It makes no sense to average an order of $20USD and another order of $15GBP!
To do this, I recommend ensuring that currency is added to any BI queries that reference the price_local field – on Looker it might look like adding currency as a required field to any of your measures that reference price_local.
On other BI tools where users can query the underlying data model without the guard rails of LookML, the only way to truly prevent this from happening is to remove the price_local field and instead create a column for each currency, filling the columns with NULL when the record is not in that currency, e.g.:
select
case when currency = 'USD' then price_local as price_local_usd,
case when currency = 'GBP' then price_local as price_local_gbp,
case when currency = 'AUD' then price_local as price_local_aud,
...
^ This approach isn’t very sustainable as your company grows though.
Advanced things you can do in Looker:
- If converting to multiple target currencies, you can do some trickery with using parameters that are added as an always_filter, so users can switch their reports between USD, GBP etc, without having to choose different measures. Kind of related is this article I wrote a while back on a similar pattern for timezone conversion.
- You can use custom formatting to get the right currency symbol for a price field, as described in this article
Create a 
Use an “all days” table to get your 