I have 300+ companies and need to calculate monthly return for them and later use it as one of the variables in my data set. I download prices from Yahoo and calculated monthly return using quantmod package:
require(quantmod)
stockData <- lapply(symbols,function(x) getSymbols(x,auto.assign=FALSE, src='yahoo', from = '2000-01-01'))
stockDataReturn <- lapply(stockData,function(x) monthlyReturn(Ad(x)))
The problem I have is that some companies have different month ends (due to trading halts, etc.) which is reflected in the output list: 2013-12-30 for company AAA and 2013-12-31 for company BBB and the rest of the sample.
When I merge the list using
returns <- do.call(merge.xts, stockDataReturn)
It creates a separate row for 2013-12-30 with all NAs except for AAA company. How can I resolve this? My understanding is that I need to need to stick to month-year format which I need to use as the index before I merge.
Ideally, what I want is that at the monthlyReturn stage, it uses the beginning of the month date rather than end of the month.
You could use lubridate's floor_date to merge on the same beginning of the month timestamp rather than end of the month timestamp. Or use ceiling date to round to the same end of month timestamp for all securities before merging.
library(lubridate)
stockDataReturn <- lapply(stockDataReturn,
function(x) {
index(x) <- floor_date(index(x), "month")
# Or if you want to round to end of month change to:
# index(x) <- ceiling_date(index(x), "month")
x
})
returns <- do.call(merge, stockDataReturn)
colnames(returns) <- symbols