2

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))
lenlen
  • 79
  • 7

1 Answers1

4

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
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • Hi Ronak - Thanks for your answer, I really appreciate it. I tried out your solution on my end, although I find I am getting some strange results. For example, Identical currencies and values seem to be giving different conversions. I'll edit my question with more details. – lenlen Jan 16 '22 at 15:47
  • There was an issue when the currencies were identical. I have fixed it and it should work now. Can you check @lenlen ? – Ronak Shah Jan 17 '22 at 01:30
  • Thanks! This appeared to work. – lenlen Jan 26 '22 at 11:02