Using spreadsheet to show candlestick charts, moving average, RSI, BIAS, MACD, stochastic KD and Bollinger band Simultaneously

Stock Exchange and Market Analysis

Technical Analysis (9)

According to the historical data of individual stocks, the various technical indicators are drawn into commonly used candlestick charts, moving averages, trading volume, RSI, BIAS, MACD, stochastic KD line and Bollinger band. Line graph, and displayed on the same screen at the same time (as shown below).
​
At present, we have introduced the calculation methods of moving average, RSI, BIAS, MACD, stochastic KD line and Bollinger band with EXCEL in the previous articles (Stock technical analysis 2-4, please refer to Ref.1~3). For the convenience of analysis, today we will use the Excel chart to draw these indicators together with the candlestick chart of the stock on the same spreadsheet for the reader's reference:
Template9_1.xls
The candlestick chart on this spreadsheet covers the stock price of Juyang from 2003/01/22 to 2014/01/20, so the horizontal axis (timeline) range is quite long. When the file is opened, the screen only displays the chart of the most recent date. Since EXCEL provides at most two main coordinate axes and sub-coordinate axes for each chart, the spreadsheet "strand chart" is constructed in a multi-graph manner, in which the candlestick chart, the Bollinger band and the moving average are produced in the same chart. And the main/second coordinate axis is indicated. As for other technical indicators, it is displayed in individual charts. Since the technical indicators are directly stacked on the candlestick chart, the candlestick chart will overlap with these indicators in some periods, as long as the readers will have The overlapping technical indicator maps can be clicked and then moved vertically downwards. In addition, there are two lines on the candlestick diagram to provide readers with the pressure line and support line analysis. The end points of the lines can be changed in length and angle, or they can be copied to create additional lines for other analysis purposes.
As for the analysis of the stock chart of other stocks, just return to the "historical stock price" spreadsheet, and copy the relevant information of individual stocks (such as the column A~J in the spreadsheet) to the corresponding field by Ref.4 method. so you can show charts updated in the "stock chart" spreadsheet. Since these charts is the author foray, if left error, please also feel free to correct me, let sorted out more accurate information helpful. Thank you! in addition, 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.) For interested readers, please refer to Ref.5. Stay tuned.
Reference (Ref):

1. Stock Technical Analysis (2) - How to use Excel to produce individual stock technical indicators - moving average and deviation rate
2. Stock Technical Analysis (3) - How to use Excel to produce individual stock technical indicators - RSI, William indicator and KD value
3. Stock technical analysis (4) - how to use Excel to produce individual stock technical indicators - MACD
4. Stock technical analysis (1) - Stock technical analysis ( 1): Taiwan stock index and individual stock historical data query
5. Investment Financial Notes - Stock Technical Analysis

Using spreadsheet to produce Bollinger Bands and Bollinger Limits

Stock Exchange and Market Analysis

Technical Analysis (8)

Bolinger Bands is a set of judges established by John Bollinger , a well-known American securities analyst. The stock price medium- and long-term trend analysis tool. Usually based on the 20th day. Let us import the Excel spreadsheet according to the mathematical formula of the Bollinger Band and Bollinger Limit Indicators today:

SMAn _t : n-day simple moving average on day t (Simple Moving Average, Ref.1)

SMAn_t = (C_t + C_t-1 + … + C_t-n+1) / n
MDn _t : n day standard deviation of the tth day (Standard deviation)
MDn _t
= square root ((C _t - SMAn _t ) ² +(C _t-1 - SMAn _t ) ² +...+(C _t-n+1 - SMAn _t ) ² )/n)
  C _t : the closing price of the tth day
UPn _t : the upper limit of the Bollinger Band on the tth day
  UPn_t = SMAn_t + 2 * MDn_t
DNn _t : the lower limit of the Bollinger Band on the nth day of the tth day
  DNn_t = SMAn_t - 2 * MDn_t
%BBn _t : n-day Bollinger limit value on day t
  BBN% _t = (100 _t - Don _t ) / (UPN _t - Don _t )
Wn _t : nth day Bollinger Band width indicator on day t
  Wn_t = (UPn_t - DNn_t) / SMAn_t
If you want to know more about the usage of these technical indicators, please refer to the explanation on Ref.2. Finally, in order to facilitate the readers to use the Bollinger Band indicators, the author still uses the model of Juyang to continue to produce analytical samples in Excel format. Reader's reference:
template8.xlsx
This sample provides data for the period 2003/1 / 22-2014 / 01/20 calculated SMAn 10 days and 20 days _t , MDn _t , UPn _t , Dnn _t ,% BBn _t , Wn _t . If left error also requested Do not correct me, so that the information compiled can be more accurate and useful. Thank you! In addition, the author has produced various indicators for stock analysis (moving average, RSI, BIAS, stochastic KD line, William indicator, MACD and Bollinger band, etc.). The EXCEL spreadsheet is for readers' reference and further produces candlestick diagrams and other technical indicator line diagrams on the same chart. Interested readers can refer to Ref.3. Stay tuned.
Reference (Ref):
  1. Stock technical analysis (2) - How to use Excel to produce individual stock technical indicators - moving average and deviation rate
  2.  Wikipedia: Bollinger Bands (BBands )
  3. Investment Financial Notes - Stock Technical Analysis

Using spreadsheet to produce WMA (Weighted Moving Averages) and EMA (Exponential smoothing Moving Averages) indicators

Stock Exchange and Market Analysis

Technical Analysis (2) sequel

In the previous stock technical analysis (2) explained how to establish a simple moving average and deviation rate, today we further establish a weighted moving average for the moving average and A sample of the exponentially smoothed moving average. First, import the Excel spreadsheet based on the mathematical formulas of both:
1. n- day weighted moving average:
    (WMA:Weighted Moving Average)
    WMA_t
    = (C_t * n + C_t-1 * (n-1) + … + C_t-n+1 * 1) / (n + (n - 1) + … + 1)
    = (C_t * n + C_t-1 * (n-1) + … + C_t-n+1 * 1) / ((n+1) * n /2)
   Where
    WMA _t : n-day weighted moving average on day t
    C _t : the closing price of the tth day
2. n- day exponential smoothing moving average:
   EMA:Exponential Moving Average
   There are currently two calculation methods on the market, which are described as follows:
i. EMA _t
   = EMA _t-1 + K * (100 _t - EMA _t-1 )
   = K * C _t + (1-K) * EMA _t-1
   = (2 * C _t + (n-1) * EMA _t-1 ) / (n + 1)
   Wherein K = 2 / (n + 1)
   EMA _t : n-day exponential smoothing moving average on day t
ii. EMA _t = (C _t + (n-1) * EMA _t-1 ) / n
The second algorithm (ii) actually reduces the proportion of the closing price of the day, making the trend of the moving average smoother.
If you want to understand the definition and application principles of the above technical indicators, you can refer to the explanation on Wikipedia (Ref.1). Finally, the author made the analysis samples of the above two technical indicators for readers' reference:
Template2_1.xls
This sample provides information on the period of 2003/1/22-2014/01/20 based on Juyang. If there are any mistakes, please correct me and let the information compiled more accurately and usefully. Thank you! Class indicators (moving average, RSI, BIAS, stochastic KD line, William indicator, MACD and Bollinger band, etc.) produced the EXCEL spreadsheet for readers' reference, and further produced candlestick charts and other technical indicators. Above, interested readers can refer to Ref.2. Stay tuned.
Reference (Ref):
1. Wikipedia: Moving Average - Wikipedia, the free encyclopedia
2. Investment Financial Notes - Stock Technical Analysis