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.

#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.

#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)

#4: Calculate the Upper Bollinger Band

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

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

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

1. Click on the + sign on the right side of the chart
2. Remove Chart Title
3. Remove Legend.

1. Right-click on your chart.
2. Select the option "Select Data Source"
3. Unchecked the Stdev series

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 color of the lines and the background to present your chart like this

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.