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.

## Histogram Analysis

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 1^{st} 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 1^{st} 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)*****

**UPDATE:**

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.

THANKS

Very nice. Thks. -LT

Terrific — thanks very much for sharing this — really helps an excel noobie like me!

Thanks so much for sharing this information.

Thanks you so much!

Thanks for the information. How do you use this info?

On entries, to me it would make sense to enter just beyond the 1st std deviation if its at a level I think is going to hold and reverse (ie fade), since I should not be stopped out by the 5 tick “harmonic noise”. But, would you say it is better to enter just beyond (or just before) the 2nd std, so that if it goes beyond that level on an entry, there is about a 5% chance of being stopped out, but of course, there would be a lot less signals showing up at the levels that I think are interesting.

This is the question I can’t seem to answer (on my own), ever since I saw the first FT71 video on the subject.

Thanks for the great writeup.

Hi Larry,

Thanks for your comment. When looking at entries, personally I believe that the association people naturally draw between the statistical distribution of rotations going beyond a certain number of ticks and probability of a rotation stopping at that level is a spurious one.

Think about it in these terms – a rotation which has already moved 5 ticks can either stop and complete a minimum opposite rotation or it can continue. If you remove the stats in the distribution for all rotations below 5 ticks to create a dataset of rotations reaching 5 ticks or more, the odds that

at5 ticks the rotation is complete become rather less appetizing.I prefer to look at it in terms of whether the move has much energy left to break whatever area you are fading. If energy has already been expended in terms of a decent size (and speed) of move, then subjectively the chances are better that there will be a counter rotation of at least enough ticks to get you a scale-out.

Hi TR,

I also like to see it as a spark and as a guide to whether we are likely to get a continuation move – ie whether the last rotation has enough energy/momentum to generate further follow through after a decent pullback.

Obviously it depends on where in the profile that move started and where it is likely to end up as to whether you can get a scale out and whether there is an exit nearby

Absolutely – there are lots of things a rotation in the right context can help you to see.

An impulsive type rotation can be a very useful event to gauge the possibility of a continuation – depending of course like you say, on market structure, auction context, etc.

Hello TR,

Thank you for the post. Can you please share the sheet you used to calculate this?

I singed up for the site, but did not receive a file.

Thank you,

Tim

Hi Tim. Did you confirm the signup? You should have received an email to do this. If not, please double check your spam folder and let me know. Thanks.

Hey Man how are you? I been trying to download the spreadsheet but the email does not arrived to my inbox/spam folder. Can you help me?

Hi Francisco. It doesn’t look like the email service has any record of your email address at all. There may be something in your browser interfering with the form submission. Could you try submitting via an incognito or private window please and let me know if that does the trick.

Hi TR how are you? yes man I tried with the incognito but I dont know if its works. I didnt recieved the spreadsheet yet.

Do you think you could help me man? Thanks 😛

Hi Francisco. Looks like you received the email. Cheers.

May you share your excel sheet for CL crude oil harmonic rotations please ?

Hi Omran. If you sign up to the newsletter on this page, you’ll receive a link to the CL spreadsheet. Just make sure that you confirm your subscription. Cheers.