There are so many different markets out there and finding the harmonic rotation for each one you trade is a critically important exercise. It’s useful to figure out for your stops and scales. It’s useful as a gauge of strength of the current rotation taking place. There’s some data out there already on markets such as the ES, but what if you want to trade a product where there’s no information on rotational harmonic amplitude?
Well you’re going to have to do it yourself. Hopefully, some work I’ve already done on this might come in handy for you in your own research. Before I explain the process, I would like to credit FT71 as my work here is based on his principles for finding harmonics. The steps might not be 100% the same but they’re not going to be too far off. I’d also like to point out that there are always going to be several different ways of achieving the same (or similar) outcomes in Excel. There may or may not be more elegant ways of achieving the desired result in this case, but the important thing to me is that it works. So here’s how I go about it.
Fractals Not Zigzags
The first point to make is that this is a study based on fractals not zigzags. The reason being is that you have to set a value for zigzags, but for fractals you’re looking for highest high and lowest low over a number of bars. The result of this study can then be used to set the zigzag value. You’re basically using the fractal indicator to try to identify reasonable swings within the data. If you’re a much longer term trader, use a higher timeframe chart. I’ve found that a 7 bar fractal on a 1 minute chart does a good enough job here for me.
Export & Format Your Data
You need to get your fractal data into Excel and I’ll assume you know how to do this. A word of caution though, if you use too much data you might end up getting a lot of slowdown or even Excel crashes – I believe the last year of data is just fine. Once you have the data in Excel, it’s important to get it into a useful format. To start with, you can delete rows with no up or down fractal. I did this by filtering for zeros in both columns then deleting the resulting data. Once done, you can remove the filter.
The next issue to resolve is that you often get multiple high or multiple low fractals occurring in sequence.
We want the highest high or lowest low of each sequence. To do this I group each sequence using a formula and then I can use the max or min function of pivot tables.
Next, I create two pivot tables – one for up fractals and one for down fractals.
At this point it’s worth noting that I’ve added max of time for both pivot tables. This is because in this example I’m not using a 24 hour session and I want to eliminate the trans-session rotations in order to ensure my data is more accurate (not 100% essential but good practice). That being the case, it doesn’t matter that it’s max of time rather than the specific highest high/lowest low fractal in each group as all I want to do is find the rotations with the very largest lengths in time to form –i.e. the trans-session rotations. So I add the “Group Number High” for row labels and max of “Real High” (this is in place of “High” as there is an additional optional step here – but just assume it’s max of “High”) and for the second pivot table, “Group Number Low” and min of “Real Low”.
I then copy and right click “paste values” into another sheet. I then add a formula in the “Rotation Up” and “Rotation Down” columns to subtract the last rotation low from the last rotation high – so for example “=B2-G2” and copy the formula down. So it’s important to ensure you have the correct sequence of cells. At this point if you want to remove the trans-session fractals, you need to insert two columns for time and subtract the times to get a length of rotation.
So you now have your data. To get your rotation you need to have added the “data analysis pack” to Excel. I’m not going to detail how the histogram is produced as FT71 has covered this a lot already. Once you have your POC and VA, you know what the most common fractal is and what a large rotation is (outside the 1st standard deviation). Roughly speaking, if you have a value of roughly POC = VA/2 then you have a good harmonic (and zigzag value) to work with. In this case for the 6E, 5 ticks is the result. If they’re a bit off, you can look at values between the mode and the 1st SD.
So there you have it. I know that this is perhaps difficult to understand without seeing what’s going on in my spreadsheet, so I am prepared to share it: –
***Please note that if you replicate this process but can’t get satisfactory results, you might want to take a look at the data periodicity, fractal bars and the session times that you use (including data from outside primary active session could affect results)***
I’ve noticed an error whereby Excel seems to output the results of the rotation calculation as non-exact numbers.
This can actually have an effect on the histogram since values are tallied between each number in the bin column. So I’ve added a solution (and updated the download file) using the “round” function. Note that the “4” in the formula is the decimal places, so if you’re studying a different product, you’ll need to change this value (e.g. ES = 2).
If you know why Excel does this I’d be delighted to know, but as it stands the revision to the formula as above achieves the desired result!
Please take a look at A Little Bit of Lateral Excel Thinking for an update to my methodology.