SPY 20 day highs by month

I was looking at SPY 20 day highs, using Yahoo! adjusted closes, and sorted by year/month:

	MONTH
YEAR	1	2	3	4	5	6	7	8	9	10	11	12	Grand Total
1993	1	5	4		5	1	1	8	3	3		6	37
1994	4		1		3	6	1	10		2		3	30
1995	5	9	11	7	10	8	8	1	12	3	15	6	95
1996	4	9		1	7	1		6	4	7	13		52
1997	8	4		2	10	10	9	2	4	2	2	5	58
1998	1	15	15	4		4	9			5	10	7	70
1999	3	2	6	9	1	2	7	2		2	5	5	44
2000	1		7			2	4	7	1		1		23
2001	5			4	5			1		4	8	1	28
2002	2		7					6		5	6	1	27
2003	3		5	4	12	6	1	2	5	5	3	14	60
2004	14	1	1	2		7		1	8	3	7	13	57
2005	1	7	3		5	5	6	1	1		12	2	43
2006	5	4	5	3	1		2	9	9	11	7	7	63
2007	5	8		14	9	1	4		2	4		4	51
2008				9	5			3				1	18
2009	3		6	7	5	4	9	9	7	5	3	8	66
2010	7	1	13	10		2	3	3	10	10	4	12	75
2011	9	10	1	7	1	1	3			6		1	39
2012	9												9
Grand Total	90	75	85	83	79	60	67	71	66	77	96	96	945

When I checked stockcharts.com for the years 1997, 2004, 2011, they seemed to have a end of month dip for January (although not much in 2011), but February all had higher highs.

Options expiration by the month

Here are a couple of charts comparing SPY behavior during options expiration week by month.  Each bar is a different month (month 13 is the summary).  The range is the middle 50% of the weekly movements with the mark being the median value.   First, the change from prior week end close to options expiration close:

and this one, not much different, just going from start of week open through close:

It appears that January is one of only two months where the week change is typically down.

The daily cumulative performance for option expiration week for January looks like:

The number is the trading day of the week.  When the week only has 4 trading days (like this year), day 4 is the second to last trading day and day 5 is the last trading day (that is day 3  is repeated as day 4 also).

January Historical Performance By Day

This month contains month to date (first 10 trading days and last 10 [11-20] trading days) performance graphs for both SPY and IWM:

SPY (1994+)

IWM (2002+)

Compared to November and December, there is a much wider range between the good months and bad months, and there doesn’t seem to be a particular trend.  I did take a look of how January performed depending on December’s price action, and didn’t see any correlation – a little over 50% of the time January went in the same direction as December, although 60% of the time January SPYwas positive (relative to December’s close).

SPY December historical performance by day

Similar to last month, this is a picture of the cumulative performance of the SPY during the month of December. top 25 is the 4th best and bot 25 is the 14th best (SPY 1993-2010, adjusted for dividends).

SPY December cumulative

It appears that the trend is generally up, particularly towards the end of the month.

SPY November historical performance by day

For November, I am displaying a slightly different graph.  This month, I am displaying only the SPY 1993+ (it seemed to me that the IWM was very similar).  But instead of just the median cumulative percentage change, I am also showing the average, and doing a large() to find the range of the mid 50% of the changes.  The x axis is similar to previous posts, 1-10 are the first 10 trading days of November, and 11-20 are the last 10 trading days of the month.

November SPY cumulative returns

Looking at the median, it seems like there might be a slight rise at the start of the month, then a drop around election day,then an upward bias for the rest of the month.

October Historical Performance By Day

Using similar methodology as the last three months, here is October’s daily action:

To recap, I am plotting the median cumulative daily performance of IWM since 2000, SPY since 1990 and SPY since 2000.  The first 10 trading days of the month are 1001 – 1010.  The last ten trading days of the month are 1011 – 1020.  Looking at the graph I would say that there is a  upward bias for the first half of the month, and then some choppiness.

Commodity Channel Index (CCI) calcuation in Excel with Array functions

One of the difficulties in calculating the CCI (stockcharts or Gstock) is you get can get a cell with a calculation like:

=(ABS(H3-G3)+ABS(H3-G4)+ABS(H3-G5)+ABS(H3-G6)+ABS(H3-G7)+ABS(H3-G8)+ABS(H3-G9)+ABS(H3-G10)+ABS(H3-G11)+ABS(H3-G12)+ABS(H3-G13)+ABS(H3-G14)+ABS(H3-G15)+ABS(H3-G16)+ABS(H3-G17)+ABS(H3-G18)+ABS(H3-G19)+ABS(H3-G20)+ABS(H3-G21)+ABS(H3-G22))/20

because the basic CCI calculation is the formula:

(typical price – n day simple moving average of typical price) / (.015 * mean deviation)

Where mean deviation is the average of the absolute values of the difference between the last n days typical price and the current n day simple moving average (hence the sum of abs(H3-Gx)).

If you want to change the number of periods from 20, to say, 99, then the formula becomes very tedious to enter.   However, it seems there is an easier way to enter the formula in Excel (at least from a number of characters to type perspective) and that is to use array formulas (which I used in the medianifs calculation in a prior post).  The first 11 columns are from the example in Gstock. Column G is simply the typical price (pivot value)

=(C3+D3+E3)/3

The first new column – which is column L – the first row is the number of periods, and the rest of the column is simply the formula

=(“G”&ROW()&”:G”&(ROW()+$L$1-1))

I did this primarily because I was having problems with getting the array function to work properly when this was part of the formula in column N, but it has the side effect of making the formula in m3 simple:
=AVERAGE(INDIRECT(L3))

The formula in column N is displayed in column O – remember this is an array function, so rather than pressing enter, you need to press ctrl shft enter.

The CCI calculation now becomes – for cell P3 :

=(G3-M3)/(0.015*N3)

Changing the value in L1 from 20 to 99 will recalculate the CCI in column P to be based on 99 periods.

The original Gstock spreadsheet example:

			Data Table			Step-1	Step-2	Step-3	CCI
Date	Open	High	Low	Close	Volume
09/19/1996	0.86	0.9	0.85	0.9	###	0.883	0.823	0.033	121.425
09/18/1996	0.9	0.9	0.85	0.86	###	0.870	0.818	0.032	107.904
09/17/1996	0.88	0.9	0.86	0.9	###	0.887	0.815	0.030	160.638
09/16/1996	0.86	0.87	0.85	0.86	###	0.860	0.811	0.026	125.160
09/13/1996	0.83	0.87	0.82	0.86	###	0.850	0.809	0.024	117.092
9/12/1996	0.78	0.81	0.78	0.81	###	0.800	0.808	0.023	-23.090
9/11/1996	0.78	0.79	0.77	0.79	###	0.783	0.810	0.024	-74.023
9/10/1996	0.78	0.8	0.77	0.77	###	0.780	0.811	0.024	-87.470
9/9/1996	0.76	0.79	0.76	0.78	###	0.777	0.808	0.027	-78.407
9/6/1996	0.76	0.78	0.76	0.77	###	0.770	0.805	0.029	-79.563
9/5/1996	0.77	0.78	0.77	0.77	###	0.773	0.805	0.030	-69.475
9/4/1996	0.79	0.8	0.78	0.79	###	0.790	0.803	0.032	-28.070

And additional columns (L-P) showing the alternative calculation method:

Col L	Col M	Col N	Col O	Col P
20	Step 2A	Step-3A		CCI
G3:G22	0.823	0.033	{=SUM(ABS(INDIRECT(L3)-M3))/$L$1}	121.425
G4:G23	0.818	0.032	{=SUM(ABS(INDIRECT(L4)-M4))/$L$1}	107.9038
G5:G24	0.815	0.030	{=SUM(ABS(INDIRECT(L5)-M5))/$L$1}	160.6381
G6:G25	0.811	0.026	{=SUM(ABS(INDIRECT(L6)-M6))/$L$1}	125.1596
G7:G26	0.809	0.024	{=SUM(ABS(INDIRECT(L7)-M7))/$L$1}	117.0916
G8:G27	0.808	0.023	{=SUM(ABS(INDIRECT(L8)-M8))/$L$1}	-23.0901
G9:G28	0.810	0.024	{=SUM(ABS(INDIRECT(L9)-M9))/$L$1}	-74.023
G10:G29	0.811	0.024	{=SUM(ABS(INDIRECT(L10)-M10))/$L$1}	-87.4704
G11:G30	0.808	0.027	{=SUM(ABS(INDIRECT(L11)-M11))/$L$1}	-78.4067
G12:G31	0.805	0.029	{=SUM(ABS(INDIRECT(L12)-M12))/$L$1}	-79.5626
G13:G32	0.805	0.030	{=SUM(ABS(INDIRECT(L13)-M13))/$L$1}	-69.4752
G14:G33	0.803	0.032	{=SUM(ABS(INDIRECT(L14)-M14))/$L$1}	-28.0702

September Historical Performance By Day

Using similar methodology as the last two months, here is September’s daily action:

To recap, I am plotting the median cumulative daily performance of IWM since 2000, SPY since 1990 and SPY since 2000.  The first 10 trading days of the month are 901 – 910.  The last ten trading days of the month are 911 – 920.  looking at the graph I would say that there is a slight upward bias for the first half of the month (around this month’s options expiration), then downward bias until the last few days of the month.  Looking back on August 2011, the predicted early weakness certainly happened, but the recovery back above breakeven doesn’t look like it will happen – at least for IWM and SPY.

Year to Date Low in August

I was looking at previous occurrences where the S&P 500 hit a new year to date low in August.  Using Yahoo!, the years seem to be 1953, 1966, 1973, 1974, 1977, 1981 1982 (the decade 1973-1982 sure had a lot), 1990, 2004, and 2011.  Of the years, 1982 and 2004 where the only ones where the August low was the low of the year (2 of the 3 most recent).  Using Stockcharts (which does not seem to have the years prior to 1981) the year that seems to be the most similar to this year to date was 1990:

http://stockcharts.com/h-sc/ui?s=$SPX&p=D&st=1990-01-01&en=1991-01-01&id=p91451617551&a=241455009

where new lows were hit in August, September and October, although finishing the year well off the lows.

If the S&P had a new low in the August – December time frame [ 1953-2010] (17 occurrences), only 3 times did it also hit a new high – the most recent was in 2004 (when the August drop was not as pronounced as this year).

I was also curious about the yearly range of the S&P.  Still using the Yahoo! database, the median yearly range [(high - low)/prior close] is about 22% (average range is closer to 25%).  This year the range has been 22%, so if it makes no higher highs or lows, it will be a typical year.  Furthermore, the August – December range had a median of 14% (mid 50% range is 11%-19%), so this August’s action of 16% already exceeded the median range.

Finally, I did a quick look to see where the median finish was in the range ([year close]-[year low])/([year high]-[year low]).  For all years, the close is near the top of the range at 82%, but if the low was in the August – December time frame, the close was slightly above the middle of the range (56%) (still well off the lows of the year).

So we might have some more downside, but overall it would seem likely the market will finish the year higher than where it is now.

Monthly performance after drop at the start of the month

I took a look at the subsequent monthly performance after the IWM had dropped between 2 and 4 % after 3 days.  I looked at these months:

10	2000
2	2002
9	2002
12	2002
2	2004
7	2004
10	2005
8	2007
7	2008
9	2008
7	2009
5	2010
7	2010
5	2011

Graphically, this is how they performed:

X axis is the adjusted trading day of the month, 1-10 for the first 10 trading days, 11-20 for the last 10 trading days. The dark blue is the median performance, green is typical August, red is all months, orange is the best month to date after being down, and light blue is worst month to date after being down.  5 times IWM finished positive for the month, 9 times it was negative for the month.  Although there is often some recovery mid month, by the end of the month the median month end close is a little lower then the 3rd trading day of the month.