## Stochastics Indicator – Excel VBA and Formula versions

This post includes  two example excel files that show both VBA and Formula based calculations for the indicator "Stochastics".

Both files contain the exact same set of Open High Low Close data. The formula based version should be 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 for both %k and %D.  The major benefit of using VBA is that the parameters for Stochastics can be easily 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 an image of the formulas used in the formula based version.

Below is the code that I wrote for the VBA based version.

Dim StochSetting As Integer, Ksetting As Integer, Dsetting As Integer
Dim A() As Double, B() As Double, C() As Double, D() As Double, E() As Double
Dim Count As Long
Dim Xcounter As Integer, Xavg As Double
Dim Zcounter As Integer, Zavg As Double
Dim x As Integer, y As Integer, z As Integer
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''  The below three lines are the Stochastic settings.
''  The Values can either be changed here or uncomment the inputbox lines to be prompted.
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
StochSetting = 14 'InputBox(Prompt:="Enter Stoch settings Number of Periods.", Title:="Stochastic Period", Default:="13")
Ksetting = 2 'InputBox(Prompt:="Enter Moving Average For %K", Title:="%K Setting", Default:="5")
Dsetting = 3 'InputBox(Prompt:="Enter Moving Average For %D", Title:="%D Setting", Default:="6")
Set ws = ThisWorkbook.Worksheets("Svba")
With ws
LR = .Cells(Rows.Count, "A").End(xlUp).Row
'does the same as formula =E15-(MIN(D1:D15))
For Each DataRange In ws.Range(.Cells(2, "A"), .Cells(LR, "A"))
Count = DataRange.Row
ReDim Preserve A(1 To Count)
If Count >= StochSetting + 1 Then
A(Count) = .Cells(Count, "E") - Application.Min(ws.Range(.Cells(Count - StochSetting, "D") _
, .Cells(Count, "D")))
End If
Next DataRange
'does the same as formula   =MAX(C36:C50)-MIN(D36:D50)
For Each DataRange In ws.Range(.Cells(2, "A"), .Cells(LR, "A"))
Count = DataRange.Row
ReDim Preserve B(1 To Count)
If Count >= StochSetting + 1 Then
B(Count) = Application.Max(ws.Range(.Cells(Count - StochSetting, "C"), .Cells(Count, "C"))) _
- Application.Min(ws.Range(.Cells(Count - StochSetting, "D"), .Cells(Count, "D")))
End If
Next DataRange
'=100*(I50/J50)
ReDim Preserve C(StochSetting + 1 To UBound(B))
For Count = StochSetting + 1 To UBound(B)
C(Count) = (A(Count) / B(Count)) * 100
Next Count
'=AVERAGE(K49:K50)
ReDim Preserve D(StochSetting + Ksetting To UBound(B))
For Count = StochSetting + Ksetting To UBound(B)
For Xcounter = Count - Ksetting + 1 To Count 'just go back and get the first C and go forward to current
Xavg = C(Xcounter) + Xavg
Next Xcounter
D(Count) = Xavg / Ksetting
Xavg = Empty
Next Count
ReDim Preserve E(StochSetting + Ksetting + Dsetting - 1 To UBound(B))
For Count = StochSetting + Ksetting + Dsetting - 1 To UBound(B)
For Zcounter = Count - Dsetting + 1 To Count 'just go back and get the first C and go forward to current
Zavg = D(Zcounter) + Zavg
Next Zcounter
E(Count) = Zavg / Dsetting
Zavg = Empty
Next Count
x = Empty: y = Empty: z = Empty
x = LBound(E)
y = UBound(E)
'put the stochastics on the workbook. Change column Letter as needed
.Cells(x - 1, "J") = "%D"
.Cells(x - 1, "I") = "%K"
For z = x To y
.Cells(z, "J") = E(z)
.Cells(z, "I") = D(z)
Next z
End With
End Sub

## MACD

The following excel file is an example of how to calculate the MACD indicator. This example shows the 5, 13, 6, setting. the MACD is in column N.  Over time I may add more excel indicator examples. As I do they will be most easily found in the right sidebar under the “How to” – “Indicators” category.

MACD example in excel.