ZigZag Indicator Calculation

[h.noor]

I am trying to reproduce ZigZag % indicator in Excel. I can't seem to get my head around how I can program/calculate this in Excel.

Here is where I am stuck: (refer to attached file)

- Heading "Point" is the reference for all outputs and calculations.
- Now I take the starting point, which is row 3, and calculate % difference all the way down ("Point" is the numerator and "New Base" is the denominator).
- "New Base" is only to be formed when the old base value is either greater than or less than +/- 5%.
- Now when the "New Base" point has a new value I want the denominator to take the new base value and disregard the old value for % diff calculation. That is where my problem is. How can I make Excel understand to take the latest base value from the data? Right now I have done it manually in the file attached.

Any help is much appreciated. Thank you

ZigZag.xlsx

[MrShorty]

Is the current spreadsheet structure, where you calculate and display "new base" only once, necessary? I would reformulate column H so that "new base" carries down the column, changing when needed. Then, column G could use a relative reference, instead of the absolute reference. Something like:

Please Login or Register  to view this content.

[jarmush]

h.noor: I would recommend you to use two similar columns of New Base. One will calculate the values on a daily basis. The second one will display only changed values (IF<>). Have a look at this Zig Zag file and images: http://www.technicalindicators.net/t...-zag-indicator

[mohzeni]

thanks thanks

[poweru]

Have you checked on the google ?

There is some free ZigZag indicator available from this link:

<www>.algotrading-investment.com/2020/05/10/algorithm-and-prediction-for-artificial-intelligence-time-series-forecasting-and-technical-analysis/

I guess that their zigzag indiactor comes as the part of technical analysis package. Hence, you can use other technical indicators together like RSI and CCI and MACD and Moving average, etc.

They are all free fortunatley.

I really like this free one.

Hope it helps you too.

Note: just remove < > from above link.