6 min read

Converting Between Currencies Using priceR

Tags: priceR dplyr tidyr purrr lubridate devtools

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:

  1. Date of transaction
  2. Start currency (what you’ll be converting from)
  3. End currency (what you’ll be converting to)
  4. 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 place
  • local_currency : currency code that price is in
  • price : 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”1
  • floor_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 rows

Function 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 rows

Note 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 rows

  1. Can 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.↩︎

  2. Highest granularity for API is day.↩︎

  3. This could be converted to be an “all inclusive” function – i.e. no need to specify the rates_lookup in a separate step. However the advantage with the current set-up is you could use the rates_lookup table on multiple functions. It might also make sense to have create_rates_lookup() simply output a function that would be like convert_currency() but specific to the rates that were looked-up, i.e. just setting currencies = rates_lookup.↩︎

  4. In this way you are not restricted to converting to a single to currency.↩︎