1

I am currently using R to do some research and I was wondering if anybody had any smart ideas or any prepackaged functions on how to create the following variable:

I have a univariate price series of say daily closing prices of a financial asset, that goes back say 10 years. (its an xts object)

I have chosen my x to be 2%

For each day of my price data. I would like to know the minimum number of days back in time you need to go in order to have created a (potentially absolute) return (assuming you had the foresight to trade back then) greater than x%. The variable will give me back the number of days/months/years needed to generate a return of that x%. NAs are returned at the early stages if needing to go back beyond the start of the data to generate a return greater than x%.

I can think of some manual and clunky ways of doing this, but it uses loads of loops to check through loads of dates/prices in order to return the correct value for each day, and then the process repeats itself for the next day...its quite problematic on 10 years of data or on intra-day data, so up-votes will be given for faster solutions....

Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
h.l.m
  • 13,015
  • 22
  • 82
  • 169
  • Can you provide an example of some data? It's very difficult to try to generate solutions when we don't really know what your input data looks like. – thelatemail Oct 03 '12 at 05:30
  • Use `dput(head(data))` to give us a subset that you can update your answer with. – Maiasaura Oct 03 '12 at 05:37

2 Answers2

2

I don't see any way to do this without a for loop, but the solution below is reasonably fast (~1 second per 10,000 rows on my 2.2Ghz laptop).

Note that I'm taking the coredata of the xts object and dropping the dim attribute (via drop), which returns a vector. I do this because math operations in xts/zoo align by index, so you need to remove the index from the numerator before you can calculate returns.

However, this causes the division dispatch to call NextMethod because the numerator is a numeric class and the denominator is a xts class. I avoid that overhead by performing the division on numeric vectors.

library(quantmod)
getSymbols("^GSPC",from="1900-01-01")

x <- Ad(GSPC)

lookback <- function(x, p) {
  # lookback() assumes x is xts
  # select first column, take coredata, drop dims
  dcx <- drop(coredata(x[,1]))
  # initialize result object
  f <- dcx*NA
  # loop over all rows in 'x'
  for(i in 1:nrow(x)) {
    # Calculate cumulative return through today
    r <- dcx[i]/dcx-1 # or log(dcx[i]/dcx)
    # This really slows things down:
    # r <- dcx[i]/x-1
    #
    # Find which returns are greater than 'p'
    w <- which(abs(r[1:i]) > p)
    # If any returns are greater than 'p', then
    # record the closest location to 'p'
    if(length(w)!=0)
      f[i] <- max(w)-i
  }
  # return an xts object
  xts(f, index(x))
}
nrow(x)
# [1] 15791
system.time(lookback(x,0.02))
#    user  system elapsed 
#  15.761   0.152  16.040
Joshua Ulrich
  • 173,410
  • 32
  • 338
  • 418
1

First you should reverse the time series so that the x values with the smallest positive value are the nearest in time and the more distant past points are out to the right. Then you can take the current value and say:

set.seed(123)
yval=cumsum(0.01*rnorm(100))
plot(1:100, yval, type="b")
which(rev(yval) < 0.95*yval[100])
which(rev(yval) < (1- 0.05)*yval[100])[1]
[1] 5

If you had provided a sample dataset with date-time or Date classed variables we could have helped with any needed conversions.

IRTFM
  • 258,963
  • 21
  • 364
  • 487
  • Is this equivalent (in CPU cycles) to Joshua's answer, or does reversing the data give some benefit (beyond the convenience of using [1] to get the last entry)? – Darren Cook Oct 03 '12 at 23:45
  • Reversing just allows vectorizing in a manner that was easier on my mind. I do not know if it is equivalent. – IRTFM Oct 04 '12 at 01:53
  • The sample data that Joshua Ulrich pulls in via `quantmod` and `getSymbols` is representative of my problem...In your example, the result is only for one specific day/point. I.e. the final point. how would you go about vectorising for the whole dataset? – h.l.m Oct 05 '12 at 02:00
  • I would make a function that does the work for one date and then submit a list of dates to it ... probably using `sapply`. I'm not sure why you are asking. Was Joshua's answer defective in some way? I assumed the question had been answered. – IRTFM Oct 05 '12 at 02:38