Let's use five-minute open-high-low-close data. Column A in Excel will be Date; Column B is Time; and Columns C, D, E, and F are Open, High, Low, and Close for the ES futures. In my example, I downloaded the data from e-Signal into Excel and arranged the columns as above.
For Column G, we'll compute Price Change for the five-minute bar. I calculate that as a percentage change. The formula in Excel (cell G3) would look like:
=((f3-f2)/f2)*100
For Column H, we'll compute the Range for the five-minute bar, which will be our proxy for price volatility. The formula in Excel (cell H3) would look like:
=((d3-e3)/e3)*100
Now we copy G3 and H3 and fill in all the G and H cells to the end of the data sample (which, in my post, was one trading day). That will give us Price Change and Range for each five-minute period during the day.
Now, in Column I, we calculate the 20-bar correlation between the values of Columns G and H; that correlation is our Power Measure. So the formula for cell I22 would look like:
=correl(G3:G22,H3:H22)
Once again, we copy that cell (I22) and fill in all the I cells to the end of the data sample. We now have a moving 20-period correlation of five-minute data. It's like a moving average, except that it's a moving correlation. My chart simply plotted this moving correlation alongside ES price to illustrate how the indicator moved through the day.
I hope this explanation is helpful. For those with an interest, my new book goes into greater detail into the use of Excel to calculate market indicators and research historical patterns; that is the topic of Chapter 10.
.
Posts
