Down Days For SPY

Recently I've been playing around with simple backtests in excel, ranging from the proverbial "golden cross" and "death cross" in multiple instruments (e.g., SPY, GLD, etc) as well as other look-back tests (e.g. # of days above moving averages, etc). I don't consider myself an expert in excel whatsoever, far from it. With that said, the most recent test I ran in excel is looking back to see the number of consecutive down days in SPY year-to-date. With a relatively small sample size, you could just look at a chart and do the calculations manually, but in instances where you're looking back 20 years (as I have done in previous tests), the power of automation in excel can be very helpful.

Here's a link to my google docs showing my calculations as well the tweets I sent out earlier summarizing my findings: https://docs.google.com/spreadsheets/d/1Ocgi-I_FmSq0fPUlFXbpOtDNmgTQscxm3J-Z3gOSozs/edit?pli=1#gid=0

=======================================================================

Update: I ran the same exact test in excel using the S&P 500 Index (SPX), and the results are slightly different than the results for SPY (the ETF that tracks the S&P). While SPY is meant to track the SPX and its movements, there are times when small discrepancies do occur between the two instruments. The main takeaways from this new test are listed below:

• The most consecutively negative days for SPX is still 3 days year-to-date, however, this has occurred 4 times this year compared to only 3 times for SPY. 
• So far year-to-date, if SPX has closed down 2 or more times the next day has been positive 77.78% of the time, with an average next day return of 0.23% (median return 0.37%). This is compared to SPY's stats - seen in the link above - which has closed positive 81.25% of time with an average next-day return of 0.28% (median return 0.43%). So compared to SPY, the SPX has a slightly lower probability of closing higher the following day if down 2 or more previous days, and also has a slightly lower average next-day return. 
• I still find it interesting that even despite the choppy action we saw in the markets earlier this year, the S&P (both SPX and SPY) have closed positive the next day over three-fourths of the time if down 2 or more consecutive days. And even more interesting to me is that the S&P hasn't been red 4 consecutive days - even marginally - so far this year. Eventually this track record will be broken, but who knows when. Maybe this time is different...?

Please let me know if you believe I have made any errors, as I'm sure most of you reading this are more familiar with excel than I am!

StockTwits: @MarketPicker

Twitter: @MarketPicker