FX conversion depending on FX exchange (cross df multiplication)

user_6447399 0
Viewed 37

I am using the following data, which looks like the following: (financial data from quantmod)

# A tibble: 5 x 4
  symbol     year adjusted Total.Revenue
  <chr>     <int>    <dbl>         <dbl>
1 MSFT       2017   101.         9.66e10
2 5563.T     2017   307.         7.13e10
3 WB         2017    91.0        1.15e 9
4 0992.HK    2017     3.94       4.30e10
5 005930.KS  2017 45600.         2.40e14

And

# A tibble: 1 x 4
  date       jpy_usd hkd_usd  krw_usd
  <date>       <dbl>   <dbl>    <dbl>
1 2018-07-09 0.00904   0.127 0.000900

What I am trying to do is to separate the symbol column in df1 so that everything after the . would be put into a new column. So T, HK and KS would be under a new column and if there is no characters after the . then leave a blank space or USD. I have tried the following with not much luck.

dftest <- df1 %>%
  separate(symbol, into = c("ticker", "country"),
           sep = "(?<=[A-Z])\\.(?=[a-z]+)", convert = TRUE)

Then I am trying to multiply the df2 data so that. jpy_usd corresponds to T, hkd_usd corresponds to HK and krw_usd corresponds to KS. Then multiply the adjusted and Total Revenue by the corresponding exchange rates. The columns created from separate which would be left blank should have no exchange conversion since they are already in USD.

So finally some of the values should look like:

5563.T - adjusted = 307*0.00904 = 2.77 0992.HK - Total.Revenue = 5461000000 etc.

I hope I am clear in what I am trying to do. If possible I am trying to do it in the dplyr package.

Data 1:

df1 <- structure(list(symbol = c("MSFT", "5563.T", "WB", "0992.HK", 
"005930.KS"), year = c(2017L, 2017L, 2017L, 2017L, 2017L), adjusted = c(101.459557, 
307, 90.970001, 3.945, 45600), Total.Revenue = c(9.6571e+10, 
7.1346e+10, 1150054000, 43034731000, 2.3958e+14)), .Names = c("symbol", 
"year", "adjusted", "Total.Revenue"), row.names = c(NA, -5L), class = c("tbl_df", 
"tbl", "data.frame"))

Data 2:

df2 <- structure(list(date = structure(17721, class = "Date"), jpy_usd = 0.00904, 
    hkd_usd = 0.127416, krw_usd = 9e-04), row.names = c(NA, -1L
), class = c("tbl_df", "tbl", "data.frame"), .Names = c("date", 
"jpy_usd", "hkd_usd", "krw_usd"))
dplyr r
1 Answers

Answer for part 1,

dftest <- df1 %>% 
  separate(symbol, into = c("ticker", "country"), 
  sep = "[.]", convert = TRUE)

Results in 

# A tibble: 5 x 5
  ticker country  year adjusted Total.Revenue
  <chr>  <chr>   <int>    <dbl>         <dbl>
1 MSFT   NA       2017   101.         9.66e10
2 5563   T        2017   307          7.13e10
3 WB     NA       2017    91.0        1.15e 9
4 0992   HK       2017     3.94       4.30e10
5 005930 KS       2017 45600          2.40e14

Answer for part 2

It's abit ugly... But does the job

dffinal <- dftest %>% 
  mutate(exchange.rates = ifelse(country == "T", df2$jpy_usd, 
ifelse(country == "HK", df2$hkd_usd, ifelse(country == "KS", 
df2$krw_usd, 0)))) %>% 
  mutate(exchange.rates = ifelse(is.na(country), 1, exchange.rates))%>% 
  mutate(adjusted = adjusted * exchange.rates) %>% 
  mutate(Total.Revenue = Total.Revenue * exchange.rates)

Results in

    # A tibble: 5 x 6
  ticker country  year adjusted Total.Revenue exchange.rates
  <chr>  <chr>   <int>    <dbl>         <dbl>          <dbl>
1 MSFT   NA       2017  101.     96571000000         1      
2 5563   T        2017    2.78     644967840         0.00904
3 WB     NA       2017   91.0     1150054000         1      
4 0992   HK       2017    0.503   5483313285.        0.127  
5 005930 KS       2017   41.0   215622000000         0.0009
user_95553880
Related