Backtesting stock returns for Moving Average rule in r

Question

I am trying to backtest stock returns given a 10 month moving average rule. The rule being, if the price is above the 10mnth average - buy, if it is below the 10mnth average - hold the value constant.

I know how to do this in excel very easily, but I am having trouble in R.

Below is my approach in R:

#Downloand financial data
library(Quandl)

SPY <- Quandl("YAHOO/INDEX_GSPC", type = "xts", collapse = "monthly")
head(SPY)

#Calculate log returns
SPY$log_ret <- diff(log(SPY$Close))

#Calculate moving average for Closing price
SPY$MA.10 <- rollapply(SPY$Close, width = 10, FUN = mean)

#Create binary rule to determine when to buy and when to hold
#1 = Buy
SPY$Action <- ifelse(SPY$MA.10 < SPY$Close, 1, 0)

#Create default value in a new column to backtest returns
SPY$Hit <- 100

#Calculate cumulative returns
SPY$Hit <-ifelse(SPY$Action == 1, SPY[2:n, "Hit"] * 
                (1 + SPY$log_ret), lag.xts(SPY$Hit, k=1))

Returns do get calculated correctly for an Action of 1, but when the Action is not 1, I find that SPY$Hit only lags 1 time, then defaults to the 100 value, while I would like it to hold the value from the last Action == 1 time.

This formula works very well in MS Excel and is very easy to implement, but it seems that the issue in R is that I cannot keep the value constant from the last Action == 1, how can I do this so that I can see how well this simple trading strategy would work?

Please let me know if I can clarify this further, thank you.

Sample of the desired output:
      Action Return    Answer
 [1,]      0   0.00 100.00000
 [2,]      1   0.09 109.00000
 [3,]      1   0.08 117.72000
 [4,]      1  -0.05 111.83400
 [5,]      1  -0.03 108.47898
 [6,]      0  -0.02 108.47898
 [7,]      0   0.01 108.47898
 [8,]      0   0.06 108.47898
 [9,]      1  -0.03 105.22461
[10,]      0   0.10 105.22461
[11,]      1  -0.05  99.96338

This is shifting the values such that it starts at the 2nd value of the SPY$Hit column. For example, as the data starts in October 1950, the second datapoint would be November 1950, that is what the SPY[2:n, "Hit"] indicates. — santorch, Apr 03 '17 at 03:05

Evan Friedland · Accepted Answer · 2017-04-03T03:36:00.180

0

Here's my guess, let me know what you think.

# Looping
Hit <- matrix(100, nrow = nrow(SPY))
for(row in 11:nrow(SPY)){ # 11 since you have NA's from your moving average
  if(SPY$Action[row] == 1){ 
    Hit[row] = Hit[row-1] * (1 + SPY$log_ret[row]) # here we needed row-1
  } else {
    Hit[row] = Hit[row-1]
  }
}
SPY$Hit <- Hit

cbind(SPY$Action, SPY$Hit)

For your sample:

x <- data.frame(Action = c(0,1,1,1,1,0,0,0,1,0,1))
x$Return <- c(0,0.09,0.08,-0.05,-0.03,-0.02,0.01,0.06,-0.03,0.10,-0.05)

x$Answer <- matrix(100, nrow = nrow(x))
for(row in 2:nrow(x)){ # 11 since you have NA's from your moving average
  if(x$Action[row] == 1){ 
    x$Answer[row] = x$Answer[row-1] * (1 + x$Return[row])
  } else {
    x$Answer[row] = x$Answer[row-1]
  }
}
x
       Action Return    Answer
1       0   0.00 100.00000
2       1   0.09 109.00000
3       1   0.08 117.72000
4       1  -0.05 111.83400
5       1  -0.03 108.47898
6       0  -0.02 108.47898
7       0   0.01 108.47898
8       0   0.06 108.47898
9       1  -0.03 105.22461
10      0   0.10 105.22461
11      1  -0.05  99.96338

edited Apr 03 '17 at 03:36

answered Apr 02 '17 at 00:55

Evan Friedland

3,062
1
11
25

This is close but does not work. Seems when I run this code that it simply multiplies the row * (1 + SPY$log_ret) and resets the calculation for the initial matrix creation of 100. Same problem I was having as before. I'm looking for the value of the "index" of 100 to capture cumulative returns when the Action == 1. Does this make sense? – santorch Apr 03 '17 at 03:02
Could you post a really small example, maybe 5 or 6 rows where I can see your desired output? – Evan Friedland Apr 03 '17 at 03:12
I posted my desired results as an edit to the original question, let me know if this works. – santorch Apr 03 '17 at 03:19
Please see the edits. I tried to edit the original code but was unable to run due to exceeding API calls... Let me know what you think. – Evan Friedland Apr 03 '17 at 03:36

score 0 · Answer 2 · answered Apr 02 '17 at 07:51

In Excel there are 2 ways to attain it, 1. Go to the Data command find Data Analysis, find Moving Average,, In the dialog box you need to put Input data range, Interval (in yur case 10), then output cell addresses. After finding Result write this formula,

=if(A2 >B2, "Buy", "Hold") Where A2 hold Price, B2 holds 10 months Moving Average value.

Any where in sheet number cells horizontally 1 to 10 (month number). Below row put month's value 1 to 10. Below row calculate 10 months Average.

And finally write the Above written formula to find Buy or hold.

Backtesting stock returns for Moving Average rule in r

2 Answers2