Converting a currency string to a numeric in R

Question

Dear StackOverflow community, I am working right now on a way to convert a vector of currency data stored as a character into a numeric vector with the ability to transform the currency into another currency.

So imagine my vector is:

x <- c("$5M", "€10B", "CHF5K")

and I would like to transform it into billion USD, so the result should be (random currency rates considered):

x <- c(5,11000,0.4)

I developed a solution for this with the creation of two functions, my first function removes the thousands, billions and millions characters and transforms it:

convMK <- function(cats){
for(i in 1:length(cats)){
  if(grepl("M",cats[i])==TRUE){
    cats[i] <- gsub("M","",cats[i])
  } else if(grepl("K",cats[i])==TRUE){
    temp <- "0."
    cats[i] <- gsub("K","",cats[i])
    cats[i] <-paste0(temp,cats[i])
  } else if(grepl("B",cats[i])==TRUE){
    temp <- "00"
    cats[i] <- gsub("B","",cats[i])
    cats[i] <-paste0(cats[i],temp)
    cats[i] <- gsub("\\.","",cats[i])
  } else{}
}
  return(cats)
}

The second one transforms it into a numeric considering the exchange rates:

convCurr2 <- function(cats) {
  catsNum <- c(0)
  for (i in 1:length(cats)) {
    if (grepl("\\$", cats[i]) == TRUE) {
      cats[i] <- gsub("\\$", "", cats[i])
      catsNum[i] <- as.numeric(cats[i])
      catsNum[i] <- catsNum[i] * exUSD
    } else if (grepl("\\€", cats[i]) == TRUE) {
      cats[i] <- gsub("\\€", "", cats[i])
      catsNum[i] <- as.numeric(cats[i])
      catsNum[i] <- catsNum[i] * exEUR
    } else if (grepl("CA", cats[i]) == TRUE) {
      cats[i] <- gsub("CA", "", cats[i])
      catsNum[i] <- as.numeric(cats[i])
      catsNum[i] <- catsNum[i] * exCA
    } else if (grepl("\\£", cats[i]) == TRUE) {
      cats[i] <- gsub("£", "", cats[i])
      catsNum[i] <- as.numeric(cats[i])
      catsNum[i] <- catsNum[i] * exGBP
    } else if (grepl("\\CHF", cats[i]) == TRUE) {
      cats[i] <- gsub("CHF", "", cats[i])
      catsNum[i] <- as.numeric(cats[i])
      catsNum[i] <- catsNum[i] * exCHF
    }
  }
  return(catsNum)
}

And then I would run the functions in that order:

cats<-convMK(cats)
cats <- convCurr2(cats)

My question now is: Isn't there an easier, shorter way? Because this seems to be way too complex! Especially because I still haven't implemented a solution to the problem, that the function also should look up the correct exchange rate to a given date.

I am very curious for your answers, I only started learning R one week ago and as a marketing student I don't have that much coding experience (meaning: none). Thus, I am eager learning to write more elegant code:)

This would probably be a better question for [the Code Review Stack Exchange](https://codereview.stackexchange.com/) — Cubemaster, Nov 01 '18 at 20:34
I'm voting to close this question as off-topic because more suitable for https://codereview.stackexchange.com/ — zx8754, Nov 01 '18 at 21:17
Take a look at https://stackoverflow.com/questions/26694042/how-to-get-currency-exchange-rates-in-r. It suggests several packages for dealing with the current exchange rate — Kerry Jackson, Nov 01 '18 at 21:23

score 1 · Accepted Answer · answered Nov 01 '18 at 21:17

Just vectorize it, no need for loops, I think.

multiplier <- recode(gsub('.*([[:alpha:]]+)$', '\\1', x),
                     K = 1e3,
                     M = 1e6,
                     B = 1e9,
                     .default = NA_real_)
multiplier
# [1] 1e+06 1e+09 1e+03

This is the "BMK"-like units. The default is NA since anything else suggests you don't have what you think you should have.

currency <- gsub('^([^-0-9.]*)[-0-9.].*', '\\1', x)
currency
# [1] "$"   " "   "CHF"

The blank is how it's being presented ... more work may be needed to deal with unicode.

xnum <- as.numeric(gsub('[^-0-9.]', '', x))
xnum
# [1]  5 10  5

This gives it to you in "1s", not millions:

xnum * multiplier # all in "1" units
# [1] 5e+06 1e+10 5e+03

which is easy enough to correct depending on what you want when you calculate/print the output:

xnum * multiplier / 1e6
# [1] 5e+00 1e+04 5e-03

At this point, all you need is the conversion into USD. You should be able to do something like recode(currency, ...) like I did with multiplier, and then just multiply xnum by this conversion factor.

That's genius! Thank you for your time and answer :) – Alex Nov 01 '18 at 21:32 — Alex, Nov 01 '18 at 21:32

Converting a currency string to a numeric in R

1 Answers1