CODEWITHSUNDEEP
excelrxtszoo
jonnie
jonnie

Reputation: 765

apply functions for xts

My data is currently an xts or zoo object of daily stock prices per row and each column is a different company.

library(quantmod)
getSymbols("AAPL;MSFT;YHOO")
closePrices <- merge(Cl(AAPL),Cl(MSFT),Cl(YHOO))

I am still new to R and need some assistance reproducing this Excel function. My first thought was to split the function into numerator and denominator, and then compute the index:

dailyDiff <- abs(diff(closePrices,1))
numerJ <- diff(closePrices,10)
denomJ <- as.xts(rollapply(dailyDiff,11, sum))
idx <- abs(numerJ/denomJ)

This was great because the values for each portion were accurate, but are aligned by incorrect dates for denomJ. For example, the tail of numerJ goes to 6/21/2012, while the tail of denomJ goes to 6/14/2012.

The output that I am looking for is:

Upvotes: 2

Views: 386

Answers (2)

GSee
GSee

Reputation: 49830

You can use a combination of diff and either runSum or rollapplyr

#Get the data
library(quantmod)
getSymbols("AAPL")

I think this is what you're trying to do (note the use of the lag argument to diff.xts, and the n argument to runSum)

out <- diff(Cl(AAPL), lag=10) / runSum(abs(diff(Cl(AAPL))), n=11)
tail(out['/2012-06-21'])
#           AAPL.Close
#2012-06-14 -0.1047297
#2012-06-15  0.2176938
#2012-06-18  0.3888185
#2012-06-19  0.4585821
#2012-06-20  0.2653782
#2012-06-21  0.1117371

Edit

Upon closer review of your question, I do not understand why rollapplyr is not the answer you're looking for. If I take your code, exactly as is, except I change rollapply to rollapplyr, it looks to me like it's exactly the output you're looking for.

dailyDiff <- abs(diff(closePrices,1))
numerJ <- diff(closePrices,10)
denomJ <- as.xts(rollapplyr(dailyDiff,11, sum))
idx <- abs(numerJ/denomJ)
#           AAPL.Close MSFT.Close YHOO.Close
#2012-06-14  0.1047297 0.03826531 0.06936416
#2012-06-15  0.2176938 0.35280899 0.25581395
#2012-06-18  0.3888185 0.33161954 0.31372549
#2012-06-19  0.4585821 0.47096774 0.34375000
#2012-06-20  0.2653782 0.32644628 0.23750000
#2012-06-21  0.1117371 0.18997912 0.10256410

Also, note that both numerJ and denomJ both end on the same date if you use rollapplyr (which is the same as using rollapply with align="right")

end(numerJ); end(denomJ)
#[1] "2012-07-20"
#[1] "2012-07-20"

Yahoo Bug

Maybe the problem you're seeing is the yahoo bug where sometimes -- for example, right now -- yahoo duplicates the last (chronologically speaking) row of data. If so, try deleting the duplicated row before attempting to use the data for your calculations.

tidx <- tail(index(closePrices), 2)
if(tidx[1] == tidx[2]) {
  closePrices <- closePrices[-NROW(closePrices), ]
}

Upvotes: 2

chandler
chandler

Reputation: 856

It's hard to tell exactly what your problem is without exact data, but the problem appears to be with rollapply. rollapply will only apply the function to whole intervals unless the argument partial is set to TRUE. Consider the following example

require(zoo)
#make up some data
mat <- matrix(1:100,ncol=2)
colnames(mat) <- c("x1","x2")
dates <- seq.Date(from=as.Date("2010-01-01"),length.out=50,by="1 day")
zoo.obj <- zoo(mat,dates)
#apply the funcitons
numerJ <- diff(zoo.obj,10)  #dates okay
denomJ <- rollapply(zoo.obj,11, sum,partial=TRUE)  #right dates
denomJ2 <- rollapply(zoo.obj,11,sum) #wrong dates
index <- abs(numerJ/denomJ)  #right dates

Upvotes: 2

Related Questions