Over the summer of 2016 I read Peter Pham’s; ‘The Big Trade: Simple Strategies for Maximum Market Returns’. In it, he teaches he us  how to use excel to do quantitative analysis. He does not believe in technical analysis as he likens it to “studying chicken entrails” and which looks for arbitrary patterns. Instead, a quantitative analyst is supposed to identify patterns and assign them statistical probability based on historical data.

I made my own spreadsheet to study the opening range of 16 years worth of the daily S&P 500 quotes. Here are the results and bellow is the spreadsheet;

The average range of a particular day’s closing price and the previous day’s close is $10.56. The ‘Avg. high move’ is the day’s high subtracted by it’s open and then the average is taken. The same is done for the ‘Avg low move’ but the opening price is subtracted by the day’s low instead. For the ‘Break prev. high’ I have an IF statement on the excel sheet that “if” the day’s high is bigger than the previous days high, then a 1 will appear in the column, if not a 0 will appear. I then tallied the 1’s to see how many times this occurred in 16 years and then divided it by the total number of days. I found that 51.2% of the time the day’s high was higher than its previous high. This makes sense considering the S&P 500 has rose considerably in the past 16 years. The same thing was done for ‘break prev low’ except the low’s were used. Not surprisingly, the average low of a particular day only “surpassed” its previous low 45% of the time.

The ‘High + low break’ is another column of if statements. In this particular column a 1 is assigned if the day’s high surpassed the previous high AND the day’s close also surpassed its previous day’s low. This implies the day was particularly volatile. I found that days that meet such criteria only happened 8.9% in 16 years.

Finally, the ‘open is high’ and ‘open is low’ is simply the number of times that the open was the high or low. The open was the high 14.7% of the time, while the open was the low 17.2% of the time. The latter statistic is interesting because it implies the open ended up being the low more often than it was the high, which has bullish implications. This means  the close ended up being higher than the open, at least 17.2% of the times which suggests the market was rising (as it did in the past 16 years).

This chart is the end goal of the opening  range analysis. Peter Pham explained that you assign a probability to how much the price will move based on its historical data. The top part looks at the chance of a ‘up move’. I used the COUNTIF function to see how many times the ‘Avg high move’ was at least $2 of greater. This means; how many times did the price move $2 or more from its opening price. If it moves $2, it implies the market is bullish and perhaps it will rise more than $2. The ‘Avg high move’ was >= $2 exactly 3049 times in 16 years. Then I did the same COUNTIF function to see how many time the ‘Avg high move’ was $5 of greater. The ‘Avg high move’ was >= $5 exactly 2312 times in 16 years. I then divided the two numbers; 2312/3049 = 0.758281. This means, when the price rose at least $2, there is a 75% chance that it move up another $3 to $5 or more. The same thing was down for the “Down moves” except it looked at the day’s low instead of the high.

Here is the excel spreadsheet I used. On the left you see the collusion of 1’s and zeros that I used to define certain situations. If you scroll all the way down you’ll see the tallied numbers and statistics.

s&p_daily_16yrs