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.

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

## Recent Comments