In this post I’ll walk through an example of how to convert between currencies. A challenge is that the conversion rate is constantly changing. If you have historical data you’ll want the conversion to be based on what the exchange rate was at the time. Hence the fields you need when doing currency conversion are:
- Date of transaction
- Start currency (what you’ll be converting from)
- End currency (what you’ll be converting to)
- Price (in units of starting currency)
For my example I’ll use the priceR package which provides an R interface to the exchangerate.host API. To limit the number of API hits required I first create a lookup table with all unique currency conversions and dates required and then use this table to convert between currencies.
Update
I’ve made some improvements and taken the key functionality in this post and put it into a function at the gist convert-currencies.R. I added an Appendix where I just use this function directly.
Simulate data
I’ll invent some data.
sale_date: date the transaction took placelocal_currency: currency code thatpriceis inprice: sale price in `local_currency
library(priceR)
library(dplyr)
library(tidyr)
library(purrr)
library(lubridate)
sim_count <- 10000
set.seed(123)
transactions <- tibble(
sales_date = sample(
seq(as.Date('2021/09/01'),
as.Date('2022/01/01'),
by = "day"),
replace = TRUE, sim_count) %>%
sort(),
local_currencies = sample(
c("CAD", "EUR", "JPY"),
replace = TRUE, sim_count),
list_price = abs(rnorm(sim_count, 1000, 1000))
)Note that I’m not worried here about keeping the sale prices consistent with one another – they’re all just random values hovering around 1000 units of the local currency. Also, for my first example, I’ll just convert everything to “USD.”
Create rates lookup table
data: dataframe of transactions of interest
currency_code: local currency code that you want to convert away from
date: date of transaction
to: string of currency code you want to convert to, default is “USD”1floor_unit: default is “day”. If is set to e.g. “month” it will lookup the conversion rate based on the day at the start of the month2.
create_rates_lookup <- function(data,
currency_code,
date = lubridate::today(),
to = "USD",
floor_unit = "day"){
rates_start <- data %>%
count(currency_code = {{currency_code}},
date = {{date}} %>%
as.Date() %>%
floor_date(floor_unit)
)
# When passing things to the priceR API it is MUCH faster to send over a range
# of dates rather than doing this individually for each date. Doing such
# reduces API calls.
rates_end <- rates_start %>%
group_by(currency_code) %>%
summarise(date_range = list(range(date))) %>%
mutate(
rates_lookup = map2(
currency_code,
date_range,
~ priceR::historical_exchange_rates(
from = .x,
to = to,
start_date = .y[[1]],
end_date = .y[[2]]
) %>%
set_names("date_lookup", "rate")
)
) %>%
select(-date_range) %>%
unnest(rates_lookup)
rates <- rates_end %>%
semi_join(rates_start, c("date_lookup" = "date"))
rates_lookup <- rates %>%
mutate(to = to) %>%
select(from = currency_code, to, date = date_lookup, rate)
# this step makes it so could convert away from "to" currency --
# i.e. so can both convert from "USD" and to "USD" from another currency.
bind_rows(rates_lookup,
rates_lookup %>%
rename(from = to, to = from) %>%
mutate(rate = 1 / rate)) %>%
distinct()
}
rates_lookup <- create_rates_lookup(transactions,
local_currencies,
sales_date)
rates_lookup## # A tibble: 738 x 4
## from to date rate
## <chr> <chr> <date> <dbl>
## 1 CAD USD 2021-09-01 0.793
## 2 CAD USD 2021-09-02 0.796
## 3 CAD USD 2021-09-03 0.799
## 4 CAD USD 2021-09-04 0.798
## 5 CAD USD 2021-09-05 0.798
## 6 CAD USD 2021-09-06 0.798
## 7 CAD USD 2021-09-07 0.790
## 8 CAD USD 2021-09-08 0.788
## 9 CAD USD 2021-09-09 0.790
## 10 CAD USD 2021-09-10 0.788
## # ... with 728 more rowsFunction to convert prices
This function is set-up to look-up the conversion rates based on the vector inputs3.
convert_currency <- function(price,
date,
from,
to = "USD",
currencies = rates_lookup){
tibble(price = price,
from = from,
to = to,
date = date) %>%
left_join(currencies) %>%
mutate(output = price * rate) %>%
pull(output)
}Convert Prices
Now let’s convert our original currencies to USD.
transactions_converted <- transactions %>%
mutate(list_price_usd =
convert_currency(list_price,
sales_date,
from = local_currencies,
to = "USD"))
transactions_converted## # A tibble: 10,000 x 4
## sales_date local_currencies list_price list_price_usd
## <date> <chr> <dbl> <dbl>
## 1 2021-09-01 CAD 1002. 794.
## 2 2021-09-01 CAD 885. 701.
## 3 2021-09-01 JPY 284. 2.58
## 4 2021-09-01 JPY 83.6 0.760
## 5 2021-09-01 CAD 2185. 1732.
## 6 2021-09-01 EUR 468. 554.
## 7 2021-09-01 EUR 668. 791.
## 8 2021-09-01 EUR 1064. 1260.
## 9 2021-09-01 JPY 1922. 17.5
## 10 2021-09-01 JPY 3334. 30.3
## # ... with 9,990 more rowsNote that it is possible to then convert from “USD” to any currency type that is in the to field of our lookup table. Below I’ll convert list_price_usd to currencies other than USD4, list_price_converted will represent the value of list_price converted based on local_currencies and new_currencies (i.e. from and to respectively).
transactions_converted %>%
mutate(new_currencies = sample(c("CAD", "EUR", "JPY"), replace = TRUE, sim_count)) %>%
mutate(list_price_converted =
convert_currency(list_price_usd,
sales_date,
from = "USD",
to = new_currencies))## # A tibble: 10,000 x 6
## sales_date local_currencies list_price list_price_usd new_currencies
## <date> <chr> <dbl> <dbl> <chr>
## 1 2021-09-01 CAD 1002. 794. JPY
## 2 2021-09-01 CAD 885. 701. EUR
## 3 2021-09-01 JPY 284. 2.58 CAD
## 4 2021-09-01 JPY 83.6 0.760 EUR
## 5 2021-09-01 CAD 2185. 1732. CAD
## 6 2021-09-01 EUR 468. 554. CAD
## 7 2021-09-01 EUR 668. 791. EUR
## 8 2021-09-01 EUR 1064. 1260. JPY
## 9 2021-09-01 JPY 1922. 17.5 CAD
## 10 2021-09-01 JPY 3334. 30.3 EUR
## # ... with 9,990 more rows, and 1 more variable: list_price_converted <dbl>Appendix
For my initial “transactions” example dataset here, I’ll have from and to currencies that can vary line-by-line to showcase that the convert_currencies() function handles these fine.
set.seed(123)
transactions_complex <- tibble(
sales_date = sample(
seq(as.Date('2021/01/01'),
as.Date('2022/01/01'),
by = "day"),
replace = TRUE, sim_count) %>%
sort(),
from_currency = sample(
c("CAD", "EUR", "JPY", "USD"),
replace = TRUE, sim_count),
to_currency = sample(
c("CAD", "EUR", "JPY", "USD"),
replace = TRUE, sim_count),
list_price_start = abs(rnorm(sim_count, 1000, 1000))
) %>%
filter(from_currency != to_currency)
# load function from gist
devtools::source_gist("https://gist.github.com/brshallo/650c1ad3f4bd9b74076592c6bc4ff8ae")
transactions_complex %>%
mutate(
list_price_converted = convert_currencies(
price_from = list_price_start,
from = from_currency,
to = to_currency,
date = sales_date
)
)## # A tibble: 7,532 x 5
## sales_date from_currency to_currency list_price_start list_price_converted
## <date> <chr> <chr> <dbl> <dbl>
## 1 2021-01-01 JPY CAD 1454. 17.9
## 2 2021-01-01 CAD USD 3315. 2604.
## 3 2021-01-01 CAD USD 1009. 793.
## 4 2021-01-01 JPY EUR 2375. 18.9
## 5 2021-01-01 USD JPY 2792. 288225.
## 6 2021-01-01 CAD USD 2548. 2002.
## 7 2021-01-01 JPY CAD 1017. 12.5
## 8 2021-01-01 EUR USD 815. 992.
## 9 2021-01-01 EUR JPY 548. 68870.
## 10 2021-01-01 JPY EUR 7.58 0.0603
## # ... with 7,522 more rowsCan only be a single value when building the lookup function at this stage. However later when applying
convert_currency()you can have it be any currency that is in the lookup table.↩︎Highest granularity for API is day.↩︎
This could be converted to be an “all inclusive” function – i.e. no need to specify the
rates_lookupin a separate step. However the advantage with the current set-up is you could use therates_lookuptable on multiple functions. It might also make sense to havecreate_rates_lookup()simply output a function that would be likeconvert_currency()but specific to the rates that were looked-up, i.e. just settingcurrencies = rates_lookup.↩︎In this way you are not restricted to converting to a single
tocurrency.↩︎