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

Using spreadsheet to produce moving average and BIAS technical indicators

Stock Exchange Market and Technical Analysis

Technical Analysis (2)

In the previous 'Stock technical analysis (1)', we have described how to capture the world stock index and individual stock historical data. Today let us try to use a sample (template) to produce individual stock technical indicators. This article we first import the Excel spreadsheet based on the mathematical formula of the moving average and the BIAS:

  1. MAnt: The simple moving average of the t-day (Simple Moving Average, Ref.1)

      SMAnt = (Ct + Ct-1 + ... + Ct-n+1) / n

  2. BIASnt: n-day BIAS on day t

      BIASnt = (Ct - MAnt) / MAnt * 100

    Ct: closing price on day t, MAnt: n-day simple moving average on day t

If you want to understand the definition and application principles of the above technical indicators, please refer to the explanation on Wikipedia (Ref.1 & 2).

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

Template2.xls

This sample provides information for the period of 2003/1/22-2013/11/26, and calculates 5 days (one week) / 10 days (two weeks) / 20 days (one month) / 60 days (one season) / 120 days (half year) /240 days (one year) moving average and BIAS. In addition, the top two columns indicate the maximum and minimum values ​​of each field. This sample is the author's initial test. If there is any mistake, please inform me to correct it. The information can be more accurate and useful. Thank you!

In addition, the author produced EXCEL spreadsheets for the readers' reference calculations for various indicators of stock analysis (moving average, RSI, BIAS, stochastic KD line, William indicator, MACD and Bollinger bands, etc.) and further made Candlestick charts and other technical indicators are shown on the same chart. Interested readers can refer to Ref.3. Stay tuned.

Reference (Ref):

1. Wikipedia: Moving Average

2. Wikipedia: BIAS

3. Investment Financial Notes - Stock Technical Analysis (3)

Individual stock historical data query

Stock Exchange Market and Technical Analysis

Technical Analysis (1)

After checking the stock index, individual stock historical data and several searches, I have listed several useful websites for your reference:

1. Yahoo Finance:

    Dow Jones Industrial Average (^DJI)

    Stocks inquiries: Such as Apple Inc. (AAPL) which is coded  as AAPL .

The prices of various stocks in the website can be displayed daily/weekly/monthly. It can also download all the historical data in the specified time in Excel format (*.csv) by specifying the time period at the top left of the price table.  There is a Download to Spreadsheet button at the top right of the price table. Yahoo Finance has two more columns - Volume and Adj Close (Adjusted Close: means the adjusted closing price, which is the adjustment value when the stock splits or dividends are issued). The value after the ex-rights (XRT) can be calculated and the value is restored.

2. The Wall Street Journal:

    Dow Jones Industrial Average: DJIA , The historical data is displayed as DJIA Historical price. 

    Stocks inquiries: Such as Apple Inc. (AAPL) which is coded as AAPL . The historical data is displayed as AAPL Historical price. 

The time period specification and data download are also similar with the website of Yahoo Finance.

3. Investing.com:

   Dow Jones Industrial Average: DJIA , The historical data is displayed as DJIA Historical price. 

   Stocks inquiries: Such as Apple Inc. (AAPL) which is coded as AAPL . The historical data is displayed as AAPL Historical price. 

It can also download all the historical data in the specified time by specifying the time period at the top right of the price table.  There is a Download to Spreadsheet button at the top center of the price table.

4. ANUE :

   Dow Jones Industrial Average: DJIA , The historical data is displayed the page bottom as DJIA Historical price. 
Strangely, when specify the time period of the stock price, it seems not work to display it, it need to follow up the problem.

   Stocks inquiries: Such as Apple Inc. (AAPL) which is coded as AAPL . The historical data is displayed as AAPL Historical price. 

The ANUE website although only displays as Chinese version, but the information of world stocks is prosperous to study the trend of the whole stock market.

The other famous website "Google Finance" had provided historical data before, but now somehow is inhibited and can not touch those data anymore.

Although the above content has been confirmed many times, it is inevitable to hang a leak. If there are any mistakes, please correct me, so that the information compiled can be more accurate and useful. In addition, the various indicators for stock analysis (moving average, RSI, BIAS, Stochastic KD value, William indicator, MACD and Bollinger band, etc.) produced the EXCEL trial calculation table for readers to use, and further made candlestick diagram and other technical indicator line diagram on the same chart, interested readers can refer to Ref.3 .

Reference (Ref):

1.Google Finance:

   How to Use the New Google Finance Tool

2. Yahoo Finance:

    How to Use Yahoo! Finance ?

    Beginner’s Guide to the Yahoo Finance numbers

3. Investment Banking Notes - Stock Technical Analysis (2)