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