How to recreate "ZigZag" effect on stock data?

meeple

New Member
Joined
Feb 7, 2007
Messages
19
Hi. I look at stock charts that allow an overlay of "zigzag patterns." This zigzag links price extremes with a line. The price extremes are defined according to "wave size" which is described as follows:

"Wave Size: The Most Important Setting... and the thing you will want to tinker with -- is Wave Size. Wave Size is a number that defines a high or low price extreme, by stating the number of surrounding bars on either side that must not be higher or lower than the candidate extreme. Another way to think of it is: Wave Size + 1 is the minimum distance between extremes of the same high or low type."

Here is an example of what a stock chart with these zigzags drawn in looks like. [Click on the image to zoom in and see it better.]


http://img240.imageshack.us/img240/2497/zigzagsamplega2.jpg

The black diagonal line is built using a "wave size = 5" using the above definition. And the green one is, "wave size = 20"

I am trying to build this into a spreadsheet so I can play with the data. If I have four columns: (a:eek:pen)/(b:high)/(c:low)/(d:close) I cannot figure out how to build the columns after to do this zigzag. I understand what the line is, but it just doesn't make sense to me how to derive it from the data. If anyone has any pointers or thoughts on how to get a column n that has the value of the zigzag (with wave size = 5 for example) I'd really appreciate it. I'm so stumped, I keep going around in circles getting nowhere and am sort of discouraged.
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
Quick and dirty answer

Code:
Function zigzag(rg As Range, w As Integer)
    n = rg.Cells.Count
    Dim zz()
    ReDim zz(1 To n)
    Dim spots As New Collection
    For i = 1 To n
        up = -1E+30
        dn = 1E+30
        For j = i - w To i + w
            k = j
            k = max(k, 1)
            k = min(k, n)
            If rg(k) > up Then up = rg(k)
            If rg(k) < dn Then dn = rg(k)
        Next j
        If rg(i) = up Then spots.Add i
        If rg(i) = dn Then spots.Add i
    Next i
    For i = 1 To spots.Count - 1
        a = spots(i)
        b = spots(i + 1)
        For j = a To b
            zz(j) = (rg(a) - rg(b)) / (a - b) * (j - a) + rg(a)
        Next j
    Next i
    zigzag = zz
End Function

Function min(a, b)
    If a <= b Then min = a
    If a >= b Then min = b
End Function

Function max(a, b)
    If a <= b Then max = b
    If a >= b Then max = a
End Function

The function return a row of zigzag values.
If your stocks data are in columns enter: Transpose(zigzag(stocks data , windowsize)) to get the result as a column.

Many improvements can be done to this routine: working without the collection, returning a row or a column depending on the orientation of the input data, more clever search algorithm, ... and it should also be checked if it does correctly this job.

Question: does that help to get money from stocks? :wink:
 

blackbox

Board Regular
Joined
Apr 2, 2006
Messages
122
Your chart doesn't look exactly like Elliot Wave but similar. My experience has been that wave strategies tend to be very subjective. You tend to go back and try to reconfigure a number 5 wave to be a B wave to fit the larger wave of the super set.

With that said it may be difficult to use a set logic to plot the waves.
 

blackbox

Board Regular
Joined
Apr 2, 2006
Messages
122

ADVERTISEMENT

well, we're getting a little off the excel subject but heres my 1.5 cents...

Information in it's self is usesless.
Fundamentals are altered and skewed by companies.
Analysts don't really care if they are right or wrong as long as they don't standout from the other analysts.
Technicals truely are nothing more than tea leaves

From the Editorial review of your book. Pretty funny!
Shocking in their obtuseness are the so-called Elliot Wave followers, who believe stocks operate according to an impossibly arcane series of numerical waves and cycles. The efficient-market theorists-many of whom believe the stock market is so inherently efficient that everything one needs to know about a company is reflected in its stock price-get the most thorough joshing from Paulos: never able to resist a joke, he tells one about how many efficient market theorists it takes to change a light bulb. "Answer: None. If the light bulb needed changing the market would have already done it."

Even economic news can't be trusted as accurate.
example: discontinuation of M3 publication, recalculation of CPI

What really matters is what others think of the information.
For example, technical analisys tends to create a self fulfilling prophecy because if everyone thinks that the 50 day moving average is support and buys against it, then in fact it did become support.
 

lalbatros

Well-known Member
Joined
Sep 5, 2007
Messages
659
I think you would enjoy the book by Paulos.

I agree that "Information in it's self is usesless".
However, it is possible to use information and gain money from information elsewhere than on the stock market: just in nearly all usual businesses

Is it not true that value is produced everywhere except on the stock market? :)
 

itjockey

New Member
Joined
Apr 17, 2012
Messages
1
Can you attached excel file with code & data to understand what is code is doing....sir

will pls sir attached sample file with code with sample data to see what your code is doing
Code:
Function zigzag(rg As Range, w As Integer)
    n = rg.Cells.Count
    Dim zz()
    ReDim zz(1 To n)
    Dim spots As New Collection
    For i = 1 To n
        up = -1E+30
        dn = 1E+30
        For j = i - w To i + w
            k = j
            k = max(k, 1)
            k = min(k, n)
            If rg(k) > up Then up = rg(k)
            If rg(k) < dn Then dn = rg(k)
        Next j
        If rg(i) = up Then spots.Add i
        If rg(i) = dn Then spots.Add i
    Next i
    For i = 1 To spots.Count - 1
        a = spots(i)
        b = spots(i + 1)
        For j = a To b
            zz(j) = (rg(a) - rg(b)) / (a - b) * (j - a) + rg(a)
        Next j
    Next i
    zigzag = zz
End Function

Function min(a, b)
    If a <= b Then min = a
    If a >= b Then min = b
End Function

Function max(a, b)
    If a <= b Then max = b
    If a >= b Then max = a
End Function

The function return a row of zigzag values.
If your stocks data are in columns enter: Transpose(zigzag(stocks data , windowsize)) to get the result as a column.

Many improvements can be done to this routine: working without the collection, returning a row or a column depending on the orientation of the input data, more clever search algorithm, ... and it should also be checked if it does correctly this job.

Question: does that help to get money from stocks? :wink:
 

Watch MrExcel Video

Forum statistics

Threads
1,123,110
Messages
5,599,776
Members
414,337
Latest member
ogoodheart

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top