Calculating and visualizing Bollinger Bands in Excel for a financial market is simple. Here is a step-by-step tutorial.
First, here’s a video tutorial on how to calculate Bollinger Bands in Excel. You can also scroll down below for a text tutorial.
Step 1: Paste your data into Excel
Copy and paste your market’s data into Excel. Make sure that the furthest historical date is at the top row, and the most present data is at the bottom row.
For illustration purposes, I have included each day’s OPEN, HIGH, LOW, and CLOSE price. Since most Bollinger Bands only use CLOSE prices, you can just copy and paste the “Date” and “Close” columns.
*This applies to daily Bollinger Bands, weekly Bollinger Bands, monthly Bollinger Bands, etc
Step 2: calculate your Bollinger Band’s simple moving average
All Bollinger Bands revolve around a simple moving average (the most common being the 20 simple moving average).
Select the number of cells to calculate the simple moving average.
Then double click on the right hand bottom corner of the cell, so that every day’s simple moving average is calculated
Step 3: calculate the market’s standard deviation
Calculate your market’s standard deviation.
- Use “population standard deviation”, not “sample standard deviation”
- The number of periods you use in your standard deviation calculation depends on the lenght of the moving average. E.g. for a 20 sma, calculate a 20 period standard deviation.
Step 4: calculate the market’s upper Bollinger Band and lower Bollinger Band
Bollinger Bands are frequently 2 standard deviations above or below the market’s moving average.
If you want to use a wider Bollinger Band, consider replacing the “2” with a “3”
To calculate the Upper Bollinger Band, add 2 standard deviations to the moving average
To calculate the Lower Bollinger Band, subtract 2 standard deviations from the moving average.
That’s it. That’s how you calculate Bollinger Bands in Excel
If you want to visualize the Bollinger Bands in Excel, create a new sheet.
Copy and paste the Bollinger Bands “Date” column onto the new sheet
Then copy and paste the “Close” column onto the new sheet.
Then copy and paste the “Simple moving average”, “Upper Bollinger Band”, and “Lower Bollinger Band” columns onto the new sheet. Be careful to only paste the VALUES, and not the FORMULAS.
Then delete the blank rows.
Then select all the cells that you would like to appear on the chart.
Click on the “Insert” tab, then click on the “line chart” icon.
To make the chart more visually appealing, adjust the left-side scale.
What are Bollinger Bands, and why calculate them in Excel?
Bollinger Bands measure the market’s short term range and volatility.
- Bollinger Bands demonstrate how the market is fluctuating around its short term moving average (e.g. 20 daily simple moving average).
- When the Bollinger Bands are very short, it means that the market’s volatility is very low and is trading within a narrow range.
- When the Bollinger Bands are very wide, it means that the market’s volatility is very high and is trading within a wide range. Wide Bollinger Bands usually occur when the market is crashing, because the market goes down more quickly than it goes up.
- Traders go long when the market reaches or falls below its lower Bollinger Band, betting on a mean-reversion rebound in the market.
- Traders sell when the market reaches or exceeds its upper Bollinger Band, betting on a mean-reversion retracement in the market.
Why would you need to know how to calculate Bollinger Bands in Excel? After all, you can see them on any free charting service.
Calculating Bollinger Bands is useful if you want to backtest this technical indicator against your market. We backtest many technical indicators in our quantitative market studies, so we need to know how to calculate Bollinger Bands first in Excel.
Only when you can see the value for these Bollinger Bands can you use them in an objective, quantitative way.