Bollinger Bands with Excel

Bollinger Bands with Excel
Last Updated on 11/06/2024
Reading time: 3 minutes

Creating Bollinger Bands in Excel involves several steps. You must

  1. Calculating the moving average
  2. Then, the standard deviation
  3. Calculating the upper and lower band
  4. Plot the bands in a Line Chart.

What are the Bollinger Bands?

Bollinger Bands are a popular technical analysis tool used by traders and investors to assess a security's volatility and relative price levels. They consist of a moving average and two standard deviation lines (bands) plotted above and below the moving average. The purpose of Bollinger Bands can be summarized as follows:

  • Identify Overbought/Oversold: Upper band signals overbought; lower band signals oversold.
  • Signal Reversals: Price moving outside then inside bands may indicate trend reversals.
  • Trend Analysis: Upward bands show uptrend; downward bands show downtrend.
  • Pattern Recognition: Helps identify double tops and bottoms.
  • Confirmation Tool: Used with other indicators for better signal accuracy.

Prepare your data in Excel to build your Bollinger Bands

Ensure you have a dataset with dates and corresponding closing prices. That's all you need.

If you work with Excel 365, the STOCKHISTORY function helps you to collect this information easily.

#1: Calculate the Moving Average:

Decide on the period for the moving average, e.g., 20 days.

  1. Select cell B21 (20 rows after the first cell)
  2. In B21, enter the formula for the moving average =AVERAGE(B2:B21)
  3. Drag this formula down to calculate the moving average for the entire dataset.
Moving average for the Boliinger Bands in Excel

#3: Calculate the Standard Deviation

In cell C21, enter the formula to calculate the standard deviation for the same range used in the moving average.

Calculating the standard deviation for the Bollinger Band in Excel

#3: Calculate the Lower Bollinger Band

Bollinger Bands use 2 times the standard deviation to define the upper and lower bands around a moving average. According to statistical theory, approximately 95% of data points in a normal distribution lie within ±2 standard deviations of the mean. Using 2 standard deviations helps capture most price movements, providing a meaningful boundary for volatility.

  • In cell E21, enter the formula to calculate the upper band =C21 - (D21 * 2)
Lower band formula of the bollinger band in Excel

#4: Calculate the Upper Bollinger Band

  • In cell F2, enter the formula to calculate the lower band =C21 - (D21 * 2)
Upper band formula of the bollinger band

Plot the Data in a Line Chart

Drawing a chart in Excel is very easy.

  1. Select all your data
  2. Go the menu Insert > Chart > Line
Menu to Insert Line Chart

Then you have this chart, but you need to customize some chart elements.

Bollinger Bands Chart not customize
  1. Click on the + sign on the right side of the chart
  2. Remove Chart Title
  3. Remove Legend.
Excel chart remove title and legend
  1. Right-click on your chart.
  2. Select the option "Select Data Source"
  3. Unchecked the Stdev series
Uncheck one series of the Bollinger chart
  1. Select the Vertical axis
  2. Open the right pane (double-click on the axis)
  3. Go to the menu Axis option (the chart icon)
  4. Change the value of the minimum according to the data in your chart
Change the minimum axis value
  • Change the color of the lines and the background to present your chart like this
Bollinger Chart finished

How to Analyze a Bollinger Chart?

This is just the theory. There is no foolproof way to make perfect buy or sell decisions in equities.

  • If the stock price reaches or exceeds the upper Bollinger Band, it signals a potential selling opportunity.
  • Conversely, if the stock price hits the lower Bollinger Band, it indicates a potential buying opportunity.
Bollinger Chart Analysis

Leave a Reply

Your email address will not be published. Required fields are marked *

Bollinger Bands with Excel

Reading time: 3 minutes
Last Updated on 11/06/2024

Creating Bollinger Bands in Excel involves several steps. You must

  1. Calculating the moving average
  2. Then, the standard deviation
  3. Calculating the upper and lower band
  4. Plot the bands in a Line Chart.

What are the Bollinger Bands?

Bollinger Bands are a popular technical analysis tool used by traders and investors to assess a security's volatility and relative price levels. They consist of a moving average and two standard deviation lines (bands) plotted above and below the moving average. The purpose of Bollinger Bands can be summarized as follows:

  • Identify Overbought/Oversold: Upper band signals overbought; lower band signals oversold.
  • Signal Reversals: Price moving outside then inside bands may indicate trend reversals.
  • Trend Analysis: Upward bands show uptrend; downward bands show downtrend.
  • Pattern Recognition: Helps identify double tops and bottoms.
  • Confirmation Tool: Used with other indicators for better signal accuracy.

Prepare your data in Excel to build your Bollinger Bands

Ensure you have a dataset with dates and corresponding closing prices. That's all you need.

If you work with Excel 365, the STOCKHISTORY function helps you to collect this information easily.

#1: Calculate the Moving Average:

Decide on the period for the moving average, e.g., 20 days.

  1. Select cell B21 (20 rows after the first cell)
  2. In B21, enter the formula for the moving average =AVERAGE(B2:B21)
  3. Drag this formula down to calculate the moving average for the entire dataset.
Moving average for the Boliinger Bands in Excel

#3: Calculate the Standard Deviation

In cell C21, enter the formula to calculate the standard deviation for the same range used in the moving average.

Calculating the standard deviation for the Bollinger Band in Excel

#3: Calculate the Lower Bollinger Band

Bollinger Bands use 2 times the standard deviation to define the upper and lower bands around a moving average. According to statistical theory, approximately 95% of data points in a normal distribution lie within ±2 standard deviations of the mean. Using 2 standard deviations helps capture most price movements, providing a meaningful boundary for volatility.

  • In cell E21, enter the formula to calculate the upper band =C21 - (D21 * 2)
Lower band formula of the bollinger band in Excel

#4: Calculate the Upper Bollinger Band

  • In cell F2, enter the formula to calculate the lower band =C21 - (D21 * 2)
Upper band formula of the bollinger band

Plot the Data in a Line Chart

Drawing a chart in Excel is very easy.

  1. Select all your data
  2. Go the menu Insert > Chart > Line
Menu to Insert Line Chart

Then you have this chart, but you need to customize some chart elements.

Bollinger Bands Chart not customize
  1. Click on the + sign on the right side of the chart
  2. Remove Chart Title
  3. Remove Legend.
Excel chart remove title and legend
  1. Right-click on your chart.
  2. Select the option "Select Data Source"
  3. Unchecked the Stdev series
Uncheck one series of the Bollinger chart
  1. Select the Vertical axis
  2. Open the right pane (double-click on the axis)
  3. Go to the menu Axis option (the chart icon)
  4. Change the value of the minimum according to the data in your chart
Change the minimum axis value
  • Change the color of the lines and the background to present your chart like this
Bollinger Chart finished

How to Analyze a Bollinger Chart?

This is just the theory. There is no foolproof way to make perfect buy or sell decisions in equities.

  • If the stock price reaches or exceeds the upper Bollinger Band, it signals a potential selling opportunity.
  • Conversely, if the stock price hits the lower Bollinger Band, it indicates a potential buying opportunity.
Bollinger Chart Analysis

Leave a Reply

Your email address will not be published. Required fields are marked *