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

How do i use this to draw a MACD chart?

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

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

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 )

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

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

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.

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

Data should be oldest to newest.

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

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

Thanks thanks

Hi,

Kindly guide me how to calculate MACD chart in excel spreadsheet

Thanks

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)

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

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

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....

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")

Can you chart equivolume charts from an Excel spreadsheet ?

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

ExcelTrader, Thank you for sharing.

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

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))

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

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