How to loop through a time series in R and identify a specific pattern?

Question

I've imported price data for the S&P500 for the last 20 years and created a separate XTS containing only the close price for each day. I now need to create a loop that goes through the data and records every time the price has declined for three days in a row.

This is the code I entered with the aim of returning a vector that has a value of 1 every time there is a straight three-day decline in the price, and 0 other wise.

# Create an empty vector to store the results
result2 <- numeric(length(SPY.Close))

# Loop through the time series and check if each value has declined three times in a row
for(i in 3:length(SPY.Close)){
  if(SPY.Close[i] < SPY.Close[i-1] && SPY.Close[i-1] < SPY.Close[i-2]){
    result2[i] <- 1
  } else {
    result2[i] <- 0
  }
}

However, I keep getting the following error:

Error in if (SPY.Close[i] < SPY.Close[i - 1] && SPY.Close[i - 1] < SPY.Close[i -  : 
  missing value where TRUE/FALSE needed

I've googled using the is.na function, but not sure how to implement it in my code

Answer 1

The way xts (and zoo) work is that when you combine two objects using an operator they are aligned on date but element i and i-1 are xts objects with different dates so they can't be directly compared, combined with &, etc.

Use zoo::rollapplyr like this. It runs a window of length 3 over the input and if all the successive differences are < 0 then it returns TRUE for the window or else FALSE and the + converts that to 1/0.

SPY.Close$dn3 <- +rollapplyr(diff(SPY.Close) < 0, 3, all, fill = NA)

This also works and generalizes to other patterns by changing the logical vector.

SPY.Close$dn3 <- +rollapplyr(diff(as.zoo(SPY.Close)) < 0, 3, identical, 
  c(TRUE, TRUE, TRUE), fill = NA)

Note

Generate sample input

library(quantmod) # also pulls in xts and zoo

getSymbols("SPY")
SPY.Close <- Ad(SPY) # adjusted close

Answer 2

You can avoid the issue entirely and ditch the for loop using ifelse() with stats::lag():

library(xts)

result2 <- ifelse(
  x < stats::lag(x) & stats::lag(x) < stats::lag(x, k = 2),
  1,
  0
)

result2[-c(1:2)]

         [,1]
Mar 2010   NA
Apr 2010   NA
May 2010   NA
Jun 2010   NA
Jul 2010   NA
Aug 2010    0
Sep 2010    0
Oct 2010    0
Nov 2010   NA
Dec 2010   NA
Jan 2011    0
Feb 2011    0
Mar 2011    0
Apr 2011    0
May 2011    1
Jun 2011    0
Jul 2011    0
Aug 2011    0
Sep 2011    1
Oct 2011    0
Nov 2011    0
Dec 2011    0

Or if you want to return 0 if there’s an NA, add is.na() to your condition:

decline3 <- x < stats::lag(x) & stats::lag(x) < stats::lag(x, k = 2)

result2 <- ifelse(decline3 & !is.na(decline3), 1, 0)

result2[-c(1:2)]

         [,1]
Mar 2010    0
Apr 2010    0
May 2010    0
Jun 2010    0
Jul 2010    0
Aug 2010    0
Sep 2010    0
Oct 2010    0
Nov 2010    0
Dec 2010    0
Jan 2011    0
Feb 2011    0
Mar 2011    0
Apr 2011    0
May 2011    1
Jun 2011    0
Jul 2011    0
Aug 2011    0
Sep 2011    1
Oct 2011    0
Nov 2011    0
Dec 2011    0

Example data:

set.seed(13)

x <- as.xts(ts(runif(24, 10, 30), start = c(2010, 1), frequency = 12))

x[c(2,5,6,11)] <- NA

             [,1]
Jan 2010 24.20645
Feb 2010       NA
Mar 2010 17.79269
Apr 2010 11.82767
May 2010       NA
Jun 2010       NA
Jul 2010 21.48590
Aug 2010 25.28796
Sep 2010 27.46765
Oct 2010 10.82127
Nov 2010       NA
Dec 2010 27.56742
Jan 2011 27.81118
Feb 2011 21.32561
Mar 2011 21.87095
Apr 2011 17.29029
May 2011 17.14826
Jun 2011 21.82914
Jul 2011 27.30903
Aug 2011 23.61047
Sep 2011 12.74133
Oct 2011 20.93898
Nov 2011 23.55826
Dec 2011 20.56163