Search code examples
rcurrency

Convert values to single currency across multiple columns - R and priceR


I have a dataframe with different currencies spread across multiple columns where I need to convert all values to the same currency (USD).

Here is a sample df:

df <- data.frame(country = c("Romania", "Hungary", "Bulgaria", "Czechia"),
  Value_1 = c("500 000.00 RON", "520 000.00 HUF", "480 000.00 BGN", "460 000.00 CZK"),
  Value_2 = c("100 000.00 RON", "320 000.00 HUF", "580 000.00 BGN", "660 000.00 CZK"),
  Value_3 = c("20 000.00 RON", "10 000.00 HUF", "60 000.00 BGN", "50 000.00 CZK"))

I have had a look at a couple of other questions as well as the priceR package. I am a bit confused how to approach it - is it possible to write a function that converts all currencies in the data set, or will I need to first separate the currency from the amount to be converted? This is the first time I've had to convert currencies in a dataset - any help would be appreciated.


EDIT

Just tried out Ronak's suggested solution, but noticed there was some unusual results for currencies and values that are the same - surely these would be converted to same values instead of different? See below a new sample dataframe and code:

df_No_2 <- data.frame(country = c("Romania", "UK", "UK", "UK"),
                 Value_1 = c("500 000.00 RON", "41 000 000.00 GBP", "32 000 000.00 GBP", "32 000 000.00 GBP"),
                 Value_2 = c("100 000.00 RON", "80 000.00 GBP", "80 000.00 GBP", "80 000.00 GBP"),
                 Value_3 = c("20 000.00 RON", "5 000.00 GBP", "5 000.00 GBP", "5 000.00 GBP"))

convert_currency_1 <- function(x) {
  currency <- sub('.*\\s([A-Z]+)', '\\1', x)
  exchange_rate <- getQuote(paste0(currency, "USD", "=X"))$Last
  as.numeric(gsub('\\s|[A-Z]+', '', x)) * exchange_rate
}

df_No_2 <- df_No_2 %>%
  mutate(across(starts_with("Value_"), convert_currency_1))

Solution

  • Here's a way to do this with getQuote function from quantmod library.

    library(dplyr)
    library(quantmod)
    
    convert_currency_1 <- function(x) {
      currency <- sub('.*\\s([A-Z]+)', '\\1', x)
      symbol <- paste0(currency, "USD", "=X")
      exchange_rate <- getQuote(symbol)[symbol, ]$Last
      as.numeric(gsub('\\s|[A-Z]+', '', x)) * exchange_rate
    }
    
    df_No_2 %>%
        mutate(across(starts_with("Value_"), convert_currency_1))
    
    #  country    Value_1   Value_2  Value_3
    #1 Romania   115441.4  23088.29 4617.658
    #2      UK 56013986.8 109295.58 6830.974
    #3      UK 43718233.6 109295.58 6830.974
    #4      UK 43718233.6 109295.58 6830.974