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.