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.

StochasticFormula

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

Sub ETstochastic() 'written by Exceltrader www.exceltrader.net
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.