Converting Between Currencies Using priceR (2024)

[This article was first published on rstats on Bryan Shalloway's Blog, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

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 <- 10000set.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”¹
• 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 month².

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 inputs³.

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 USD⁴, 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>

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

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.

Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.