MACD VBA

This post includes links to two example xls files that show both VBA and Formula based calculations for the indicator MACD or Moving Average Convergence Divergence.

Both files contain the exact same set of Open High Low Close data. The formula based version is easier to understand and serves as a way to verify that the VBA code that I wrote is correct. Both methods give the same result.  The major benefit of using VBA is that the parameters for MACD can be easy changed from the input boxes. In addition the VBA method shows only the final result rather than take up five columns.

The VBA based version can be downloaded here.

The formula version can be downloaded here. Below is the code from the VBA based version.

Sub ETmacd() 'written by Exceltrader www.exceltrader.net
Dim EMAslow As Double, EMAfAst As Double, ws As Worksheet, LR As Integer
Dim eMaF() As Double, eMaS() As Double, EMAdif(), emaPer() As Double, MacDper As Double, coUnt As Integer
Dim DataRange As Range
Dim ExPSlowWeight As Double
Dim ExPFastWeight As Double
Dim PerWeight As Double
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''  The below three lines are the MACD settings.                                         ''
''  The Values can either be changed here or uncomment the inputbox lines to be prompted.''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
EMAslow = 13 'InputBox(Prompt:="Enter Macd Slow settings.", Title:="MACD SLOW", Default:="13")
EMAfAst = 5 'InputBox(Prompt:="Enter Macd Fast settings.", Title:="MACD Fast", Default:="5")
MacDper = 6 'InputBox(Prompt:="Enter Macd Period settings.", Title:="MACD Period", Default:="6")
ExPSlowWeight = 2 / (EMAslow + 1)
PerWeight = 2 / (MacDper + 1)
ExPFastWeight = 2 / (EMAfAst + 1)
Set ws = ThisWorkbook.Worksheets("VBA") 'or use exact sheet name for example ThisWorkbook.worksheet("Sheet1")
'slow
    With ws
    LR = .Cells(Rows.coUnt, "A").End(xlUp).Row
        For Each DataRange In ws.Range(.Cells(2, "A"), .Cells(LR, "A"))
        coUnt = DataRange.Row + 1
        'fill the eMA slow Array
        ReDim Preserve eMaS(1 To coUnt)
                If coUnt = EMAslow + 1 Then
                    'get the first value which is the Simple Moving average
                     eMaS(coUnt) = Application.Average(ws.Range(.Cells(2, "E"), .Cells(coUnt, "E")))
                ElseIf coUnt > EMAslow Then
                    eMaS(coUnt) = (.Cells(coUnt, "E") * ExPSlowWeight) + (eMaS(coUnt - 1) * (1 - ExPSlowWeight))
                End If
        Next DataRange
'fast
        For Each DataRange In ws.Range(.Cells(2, "A"), .Cells(LR, "A"))
        coUnt = DataRange.Row + 1
        'fill the eMA slow Array
        ReDim Preserve eMaF(1 To coUnt)
                If coUnt = EMAfAst + 1 Then
                    'get the first value which is the Simple Moving average
                     eMaF(coUnt) = Application.Average(ws.Range(.Cells(2, "E"), .Cells(coUnt, "E")))
                ElseIf coUnt > EMAfAst Then
                    eMaF(coUnt) = (.Cells(coUnt, "E") * ExPFastWeight) + (eMaF(coUnt - 1) * (1 - ExPFastWeight))
                End If
        Next DataRange
        ReDim Preserve EMAdif(EMAslow To UBound(eMaF))
            For coUnt = EMAslow + 1 To UBound(eMaF)
                EMAdif(coUnt) = eMaF(coUnt) - eMaS(coUnt)
            Next coUnt
'MacD Period
        Dim x As Integer, y As Integer, z As Integer, Avee As Double
        y = EMAslow + MacDper - 1
            For x = y To UBound(EMAdif) - 2
                'get the SMA for first value
                If x = y Then
                        For z = EMAslow + 1 To EMAslow + MacDper  '(EMAslow + MacDper - 1)
                        Ave = Ave + EMAdif(z)
                        Next z
                        ReDim emaPer(x To LR)
                        emaPer(x) = Ave / MacDper
                ElseIf x > y Then
                emaPer(x) = (EMAdif(x + 1) * PerWeight) + (emaPer(x - 1) * (1 - PerWeight))
                End If
            Next x
  x = Empty: y = Empty: z = Empty
  x = LBound(emaPer)
  y = UBound(emaPer)
  For z = x To y - 1
  .Cells(z + 1, "J") = EMAdif(z + 1) - emaPer(z)
  Next z
End With
End Sub 'written by Exceltrader www.exceltrader.net

Comments

  1. Kebaya Mwamba says:

    How do i use this to draw a MACD chart?

  2. ExcelTrader says:

    Try this file. It will create a OHLC chart and a MACD pane below.

  3. HOW DO I USE THIS CODE TO MACD OF 90 IN 90 MINUTES? TRADE DAY

  4. ExcelTrader says:

    You would need 90 bars of 1 minute data, then change the 13,5,6 default settings to your new settings. (change these>>>>EMAslow = 13, EMAfAst = 5, MacDper = 6 )

  5. if not asking too much. would you change the code and paste it here. so I will not spoil the code .. thanks

  6. Friend! how do I update the macro alone from time to time, as does this have? thanks

  7. ExcelTrader says:

    Erivaldo,

    The ETmacd procedure can be run at set intervals with a little added code. As an example you could add this procedure.

    sub runMACDtimed()
    Application.OnTime Now + TimeSerial(0, 1, 0), "ETmacd" '0,1,0 would set it to run once in one minute.
    end sub

    and add the line "runMACDtimed" at the end of ETmacd so that it is scheduled again each time it runs.

  8. What is the proper sort for the data in the formala sheet, oldest to newest or vice versa?

  9. ExcelTrader says:

    Data should be oldest to newest.

  10. Hi,
    I have been searching for Excel Worksheet (but not VBA) that can convert data into Volume Adjusted Moving Averages and Parabolic SAR but to no avail. Can you help or point me in the right directions?

    Many thanks

  11. Sorry, forgot to ask how to draw OHLC with tickmarks for Open and Close in Excel 2010?
    Thanks thanks

  12. Hi,

    Kindly guide me how to calculate MACD chart in excel spreadsheet

    Thanks

  13. Sir, do u have any formula or kind of VBA Code to find out ZigZag points from excel data OHLC newest to oldest.i.e want to plot ZigZag point in excel.currently i am using LRS formula, it is working good with EOD data analysis but it is worst in intraday.so i want formula or VBA which works with all time frame.(EOD,30min,15min,1min,tick)

  14. Sir, how do i plot MACD & Signal data in excel from above VBA it produce only difference of MACD & Signal Line......?

  15. ExcelTrader says:

    itjockey, This VBA version will show the MACD, Signal line and Histogram. The formula based version also shows these values.

    The code changed is the addition of the 7th and 8th line below

      x = Empty: y = Empty: z = Empty
      x = LBound(emaPer)
      y = UBound(emaPer)
      .Cells(x, "L") = "MACD Histogram": .Cells(x, "K") = "Signal Line": .Cells(x, "J") = "MACD"
      For z = x To y - 1
      .Cells(z + 1, "L") = EMAdif(z + 1) - emaPer(z)
      .Cells(z + 1, "K") = emaPer(z)
      .Cells(z + 1, "J") = EMAdif(z + 1)
      Next z
    End With
    End Sub 'written by Exceltrader http://www.exceltrader.net

  16. Hi,
    I try to change from
    EMAslow = 13 'InputBox(Prompt:="Enter Macd Slow settings.", Title:="MACD SLOW", Default:="13")
    EMAfAst = 5 'InputBox(Prompt:="Enter Macd Fast settings.", Title:="MACD Fast", Default:="5")
    MacDper = 6 'InputBox(Prompt:="Enter Macd Period settings.", Title:="MACD Period", Default:="6"
    to
    SlowMa = InputBox(Prompt:="Enter the Slow Moving MA.")
    FastMa = InputBox(Prompt:="Enter the Fast Moving MA.")
    MACDtime = InputBox(Prompt:="Enter total MACD Period.")

    with 13 , 5 ,6 input
    then the output appear huge different from previous one....

    Wanna know why....

  17. ExcelTrader says:

    It's because you are using new variables that you created instead the the existing ones. You can control the MACD settings by entering your settings into input boxes by changing the code as shown below:

    EMAslow = InputBox(Prompt:="Enter Macd Slow settings.", Title:="MACD SLOW", Default:="13")
    EMAfAst = InputBox(Prompt:="Enter Macd Fast settings.", Title:="MACD Fast", Default:="5")
    MacDper = InputBox(Prompt:="Enter Macd Period settings.", Title:="MACD Period", Default:="6")

  18. Etienne@Lorenceau.net says:

    Can you chart equivolume charts from an Excel spreadsheet ?

  19. Sir - Thank you most kindly. I have been struggling to develop this by myself. I am in your debt.

  20. ExcelTrader, Thank you for sharing.

  21. I keep seeing examples where the data is static. What about where the data keeps changing every 5 min for example and it is only one single cell that keeps changing. Must you then still copy the value of that cell to different cells so you can do the calculations to determine the macd values. I want to do it all in vba. I don't want to copy values to other cells in a worksheet. Is this possible

  22. I would like to know whether any error on following codes or not.
    Does you have any suggestions?
    Thanks in advance for any suggestions

    Error ??? : emaPer(x) = (EMAdif(x + 1) * PerWeight) + (emaPer(x - 1) * (1 - PerWeight))

    Correct ??? : emaPer(x) = (EMAdif(x + 1) * PerWeight) + (emaPer(x) * (1 - PerWeight))

  23. if i try to use your macd vba and change (.Cells(count, "E") for example into (.Cells(count, "M")
    row is empty and will be filled by program start
    VBA is giving nonstop error
    where do i go wrong in this
    can this be solved somehow

  24. Does anyone has VBA code that can download and calculate Williams %R value of a specific stock?

Speak Your Mind