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
            
            
            

How to add an indicator to Simulator.xls Chart (or any Excel Stock Chart).

If you have ever tried to add a simple moving average (or any data series) to a stock chart you may have noticed that after adding the series, the chart got all messed up!

Here is a short tutorial and an example file showing how to add a simple moving average to a stock chart. I am going to use the Simulator.xls as and example. It should be noted that all the unused columns on the BarData sheet (currently K-IV) of the simulator can be used to add indicators of your choice.

Trick number one. the =NA() trick

To plot a simple moving average (SMA), the formula is =AVERAGE(E132:E150)

So that would go in R150 on BarData Sheet.

There is a problem though. Excel will try to plot something when there is not enough data to average or even when there is an error. Here is an example of a chart we are trying to avoid.

ind11

The keep the chart clean, I want to generate the error NA# whenever I do not want anything plotted.

so to keep things looking right, change the formula in R150 to =IF(ISBLANK(E132),NA(),AVERAGE(E132:E150))

which means:

“If there is not enough data for this simple moving average, then give me NA# (so excel does not plot anything) otherwise just calculate the average.”

Next just drag and copy this formula from R150 up to R19 (the first cell where it is possible to have enought data to calculate the average!)

Now all that is left is to add this moving average to the chart. It’s surprisingly tricky though.

1. Right click on the chart and select “source data”

the following screen comes up. Click on “add” and fill it in as follows. (make sure there are some numbers in column R)

ind2

2. Select OK and take a look at the chart. Things should look funny on the chart now. Don’t worry about it, just find the data series “indicator”. It should be somewhere on the chart. In my case it plotted this price moving average on the secondary axis which is volume!

Hover your mouse around the volume series looking for “indicator”. Right click on the “indicator” series and change it to primary axis from secondary axis (which will be hidden somewhere down in the volume bars if it’s on the secondary axis. It can be hard to find/click on, if it is you may want to zoom in)

ind31

Now “indicator” should be plotted up there with price, but now the price chart will be all messed up!!!!!! (see how the first bar is missing parts!)

ind4

Next you once again need to hover your mouse around the price series looking for “indicator” when indicator is visible, right click on it and select “chart type”.

The following screen comes up and just select XY (Scatter) as shown and press OK.

ind5

Now the chart will look normal again.

ind6

The last step is to properly format this scatter chart to look like a line. For the last time find the “indicator” series on the chart and right click on it. This time select “format data series” and set it as follows.ind7

Select “OK” and the resulting indicator should like like this.

ind8

One final tip.

Use google! If you are wanting to add a stochastic indicator for example, but don’t know how to calculate it. Try the following search:

stochastic filetype:xls

This will return tons of Excel files described with the word stochastic. There is a good chance that you will find a nice example there. If you have no luck, next you should search investopedia.com, which often explains the calculations.