There's an expression that says that even a blind squirrel finds a nut once in a while. Without getting into whether I'm the squirrel or the nut, while researching cycle analysis I came across a Fourier technique that uses Excel for analysing water samples. The reference page is here. The connection is that a water sample taken on any particular day is a discrete data point in a time series. So are stock and commodities prices. Mathematics used to study the cyclical behavior of water samples over time should also be effective for studying the cyclical behavior of other discrete data samples. That may seem obvious today but it was not when J.M. Hurst launched his cycle based price-forecasting model in 1970. We used Hurst's work to develop our own price and time forecasting technique that was not dependent on understanding his rocket math. More on J.M. Hurst Cycle Trading Without the Rocket Math.

In our Hurst book we deliberately avoid any discussion of cycle finding techniques other than counting the time between obvious cycle bottoms or tops on bar charts. We discovered early on that some simple graphical techniques that Hurst did talk about could be appplied in ways he did not reveal in his book to produce some accurate price and time forecasts. So long as you get within the ballpark of the periodicity of the dominant cycle you can apply our graphical techniques without doing any rocket math. Your eyeballs are more than accurate enough. By the same token, we had the position that if we came across a relatively straightforward method for identifying the dominant cycle that we would include that method in our description.

You need to be somewhat proficient in Excel or other suitable spreadsheet. For our example we use SPX cash closes from late 1999. Cycle periods are not immutable and we wanted to sample data from the most recent significant high or low. The all time high occurred in early 2000 so we operate under the assumption that the 1527.46 close is significant enough to cause a cycle shift if indeed one has occurred. You may find it valuable or informative to calculate and compare cyclical data from other periods.

The first step is to arrange your data in Columns A and B. We show only the first 9 of the 1443 data points we used in our example. You could have more or less.

Insert a column between the date and the closing price, and a row above the data. Place the date 01/01/1900 in cell A1. The built-in Excel Julian date function is not particularly useful here so we'll just use January 1, 1900 as the reference date. We named cell A1 as RefDate. The numbers in Column B are derived by subtracting RefDate from the date in Column A. Be sure to format Column B as a number or you'll see a wierd result.

Add columns and formulae as follows:

After copying the formulae and performing the calulations your spreadsheet will look like this (with the same input data).

Next step is to use the built-in Regression function to get the coefficents for the regression we will do later. The menu commands are: Tools--->Data Analysis--->Regression. The regression window will look like this.

The y range is the closing price. The x range is the trigonomtric calculations in Columns E-H.

The output from the automatically added regression worksheet will look like this. The t Stat values are greater than absolute 2 for three out of four of the trigonometric variables indicating that they are contributing significantly to the fit. The sole purpose of the regression exrecise is to get the coefficients we will use in the next formula. If you cut and Paste Special--->Values the coefficients into Column P of your main spreadhseet you will not have to modify the formula we show you next.


The final step is to add the trigonometric regression formula to the spreadsheet using the time data in Column B and the coefficients we placed in Column P as the inputs. If your source data is in different columns then make the adjustments. Be sure to lock the location of the coefficients by using $COL$ROW or by naming them individually.

Your spreadsheet should look something like this.

What does all this get you? Look at the graph that overlays the cyclical data you just computed in Column J and SPX prices from late 1999. You can use the cursor while still in Excel to see that there are about 251 trading days from trough to trough in the cyclical data. This is the dominant cycle. Shorter cyclical periods are harmonics of the dominant cycle, i.e. 1/2 or 1/4 of 251.

The cyclical data defines the trend. Going into the October, 2002 low the SPX made a lower price low on each cyclical trough and a higher high on each cyclical peak thereafter. The classic definition of a trend. You can also see that although the cyclical data is a pretty good representation of future stock prices that it does not align perfectly with the stock data. The cyclical data will tell when the lake is frozen but it won't tell you where the ice is thin.

The cyclical overlay chart begs for further research (1) using the October, 2002 low as a starting point, (2) dissecting individual bull and bear swings within the trend, and (3) comparing the dominant cycle with the periodicity of previous trends and swings.

We use the information about the dominant trend to forecast price and time from the techniques you can learn in our Hurst book. With a graphical application that can quickly plot different displaced moving average sets, such as our training software, you can effectively and accurately use the displaced moving average technique to forecst price and time without doing any mathematical cycle work at all.

For those interested in further experimentation the spreadsheet we used for this article is available at no charge to the purchasers of our Hurst book. The spreadsheet is not a complete application but it would prevent typos and save some set-up time. You do not need the book to do the Fourier cycle analysis demonstrated on this page.

NOTE BENE: The example spreadhseet is set up for End of Day (EOD) daily data. Different time periods will NOT produce reliable results. The information on this this page is copyright TRADINGFIVES. All rights reserved. You may link to this page but you are not allowed to copy any of the contents of this page for public use. A reader has pointed out that this example does not use the same technique that J.M. Hurst showed in the appendix of his book. That is correct but it was never intended to do that. We invite comments (our email) from other readers as to whether this water sample method is, or is not, an appropriate way to determinine periodicity in stock and commodities prices.