I have a dataframe with amounts in different currencies on different dates, and want to create a new column showing the amount in GBP that it is equal to on that date.
For example my data may be as follows
data <- data.frame(
date = seq.Date(from = as.Date('2017-01-01'), to = as.Date('2017-01-10'), length = 10),
currency = c('EUR', 'GBP', 'USD', 'HKD', 'GBP', 'EUR', 'CAN', 'AUD', 'EUR', 'GBP'),
amount = 1:10
)
However I would like a generic function to do this which would work no matter what date range and currencies are in the dataframe.
I'm pretty sure I would be able to do this using the getFX function in the quantmod package but can't quite work out the best way of doing it!
I'm sure this has been done before so any help would be appreciated.
Thanks
With functions ?merge,?lapply and following the steps mentioned below:
Steps:
1) create currency pairs EUR/GBP,USD/GBP
2) get FX data
3) reshape/modify FX data
4) merge with original data and calculate fx translated amount
library("quantmod")
library("reshape2") #for melt function
#replaced CAN by CAD for Canadian Dollar
currDF <- data.frame(
date = seq.Date(from = as.Date('2017-01-01'), to = as.Date('2017-01-10'), length = 10),
currency = c('EUR', 'GBP', 'USD', 'HKD', 'GBP', 'EUR', 'CAD', 'AUD', 'EUR', 'GBP'),
amount = 1:10,stringsAsFactors=FALSE)
currDF
FX Data:
#Set start and end dates
startDt = as.Date("2017-01-01")
endDt = as.Date("2017-01-10")
#create currency pair combinations i.e. EUR/GBP, USDGBP
currCombinations = paste(setdiff(unique(currDF$currency),"GBP"),"GBP",sep="/")
currCombinations
#[1] "EUR/GBP" "USD/GBP" "HKD/GBP" "CAD/GBP" "AUD/GBP"
#get FX data for each currency pair and merge them into a single xts dataset
#see ?lapply, ?do.call and ?merge.xts
#note auto.assign needs to be FALSE
fxData = do.call(merge.xts,lapply(currCombinations,function(x)
getFX(x,from=startDt,to=endDt,auto.assign=FALSE)))
fxData
# EUR.GBP USD.GBP HKD.GBP CAD.GBP AUD.GBP
#2017-01-01 0.852550 0.810242 0.104474 0.602773 0.583503
#2017-01-02 0.852550 0.810242 0.104474 0.602773 0.583503
#2017-01-03 0.850220 0.814326 0.104987 0.606361 0.587630
#2017-01-04 0.850310 0.815139 0.105102 0.609074 0.591082
#2017-01-05 0.853695 0.810205 0.104480 0.610352 0.591940
#2017-01-06 0.855625 0.808914 0.104311 0.610882 0.592583
#2017-01-07 0.857335 0.813948 0.104951 0.614860 0.593995
#2017-01-08 0.857330 0.813942 0.104951 0.614847 0.593990
#2017-01-09 0.864095 0.819833 0.105707 0.619112 0.600639
#2017-01-10 0.870830 0.822656 0.106077 0.622248 0.605533
#remove .GBP from all columns above
colnames(fxData) = gsub("[.]GBP","",colnames(fxData))
#set conversion factor for GBP = 1
fxData$GBP = 1
Data Reshape:
#create data.frame from xts data
fxData_DF = data.frame(date=index(fxData),coredata(fxData),stringsAsFactors=FALSE)
#To make fx dataset(wide format) amenable for merging with currDF
#we convert it to long format by using melt from reshape2 package see,?melt
fxMolten = melt(fxData_DF,id="date",variable.name="currency",value.name="conversionFactor")
head(fxMolten,2)
# date currency conversionFactor
#1 2017-01-01 EUR 0.85255
#2 2017-01-02 EUR 0.85255
tail(fxMolten,2)
# date currency conversionFactor
#59 2017-01-09 GBP 1
#60 2017-01-10 GBP 1
Merged Data:
#For each currency and date, we need a conversion factor hence
#we merge both datasets by columns date and currency
fxMerged = merge(currDF,fxMolten,by=c("date","currency"))
#calculate FX translated amount
fxMerged$convAmount = fxMerged$amount * fxMerged$conversionFactor
fxMerged
# date currency amount conversionFactor convAmount
#1 2017-01-01 EUR 1 0.852550 0.852550
#2 2017-01-02 GBP 2 1.000000 2.000000
#3 2017-01-03 USD 3 0.814326 2.442978
#4 2017-01-04 HKD 4 0.105102 0.420408
#5 2017-01-05 GBP 5 1.000000 5.000000
#6 2017-01-06 EUR 6 0.855625 5.133750
#7 2017-01-07 CAD 7 0.614860 4.304020
#8 2017-01-08 AUD 8 0.593990 4.751920
#9 2017-01-09 EUR 9 0.864095 7.776855
#10 2017-01-10 GBP 10 1.000000 10.000000