Using spreadsheet to produce RSI, William indicator and stochastic KD Line technical indicators

Stock Exchange and Market Analysis

Technical Analysis (3)

Continuing the previous stock technical analysis (2), today let us use the same template to make other stock technical indicators, and import the mathematical formulas of RSI, William indicator and stochastic KD Line into Excel table:

1. RSI (relative strength indicator):

The commonly used relative strength indicator formula is the modified RSI, in order to eliminate the shortcomings of the simple RSI change which is too intense and easy to be distorted. The simple description is as follows:

       RSI = [UAt / (UAt + DAt)] * 100

       UAt = UAt-1 + (Ut – UAt-1) / n

       DAt = DAt-1 + (Dt – DAt-1) / n

      Ut: The increase on the tth day

      Dt: Decline on the tth day

      UAt: The corrected average of the increase on the tth day

      DAt: The corrected average of the decline on the tth day

The most primitive first UA1 and DA1 are the increase/decline average of the previous n days.

 2. William indicator:

      %Rn = (Cn - Hn) / (Hn - Ln) * 100

      Cn : closing price on the nth day, 
      Hn: the highest price in the n days, 
      Ln: the lowest price in the n days.

 3. Stochastic KD line:

      RSVn = (Cn- Ln) / (Hn - Ln) * 100

      Kn = α * RSVn + (1 – α) * Kn-1

      Dn = α * Kn + (1 – α) * Dn-1

RSVn (Raw Stochastic Value) is the original random value in n days, which always falls between l and 100. As for α, it usually takes the empirical value (1/3). For Kn-1 and Dn-1, if there is no previous K value and the D value for reference can be replaced by the intermediate value 50, respectively.

If you want to learn more about the usage of these technical indicators, please refer to the explanation on Wikipedia (Ref.1~3).

Finally, in order to facilitate the readers to use these technical indicators, the author produced an analysis sample in Excel format for readers' reference:

Template3.xlsx

This sample provides data for the period of 2003/1/22-2013/12/05 to calculate the RSI (common parameters: 5 days/10 days), William indicator (common parameters: 5 days / 8 days / 13 days / 21 days / 34 Day) and stochastic KD Line  (common parameters: 9th). In addition, the previous moving average and the BIAS are included together. If there are any mistakes, please inform me to correct it and let the information compiled more accurately and usefully. Thank you! The author has produced EXCEL spreadsheets for readers' reference for various indicators of stock analysis (moving average, RSI, BIAS, stochastic KD Line , William indicator, MACD and Bollinger band, etc.), and further made Candlestick charts and other technical indicator line chart is on the same chart. Interested readers can refer to Ref.4. Stay tuned.

Reference (Ref):

1. Wikipedia: RSI

2. Wikipedia: William indicator

3. Wikipedia: stochastic KD Line 

4. Investment Financial Notes - Stock Technical Analysis