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)