Last Updated on September 21, 2023 by Mark Ursell
This article and video shows you how to calculate Bollinger Bands using Excel.
Traders use these bands for their remarkable predictive power as well as their simplicity and ease of use.
Bollinger Bands are a technical indicator that are placed on charts to show when the price is at an extreme relative to recent price action. You can use them to take profits or identify changes in the market direction.
Bollinger Bands expand and contract depending on price action. The width of the bands is a useful guide to volatility.
Watch the YouTube video to see me demonstrate how to calculate Bollinger Bands in Excel.
Calculate Bollinger Bands using Excel
The first stage in calculating Bollinger Bands is to take a simple moving average. In Excel, we use the formula =AVERAGE().
Next, we need to calculate the standard deviation of the closing price over the same number of periods. The standard deviation is a measure of volatility, and it increases when the price moves away from the average. We calculate the standard deviation using the formula =STDEVPA().
The standard deviation is then multiplied by a factor (typically 2). We calculate the upper band by adding the standard deviation multiplied by the factor to the moving average. We calculate the lower band by subtracting the standard deviation multiplied by the factor from the moving average.
I don’t typically have Bollinger Bands on my trading screen because they clutter the charts and distract from the price action. However, I often add them to my charts to see whether the current price is inside or outside the bands. I also like using them when developing automatic trading strategies because they are self-scaling. This means that you can apply them to any market and timeframe without adjusting the parameters.
SMA H23 =AVERAGE(F4:F23) Upper Bollinger Band I23 =H23+(STDEVPA(F5:F23)$I$3) Lower Bollinger Band J23 =H23-(STDEVPA(F5:F23)$J$3)
Learn to Backtest
You can learn how to backtest your trading strategies using a Tradinformed Backtest Model. The models are built using Excel and you can use them to test on any timeframe and market. See the latest models in the Tradinformed Shop.
The real power of Bollinger Bands becomes evident when they areJohn Bollinger – Bollinger on Bollinger Bands
combined with indicators.
Not many technical indicators have been so memorably named after their creator as Bollinger Bands. Bollinger on Bollinger Bands is an excellent guide to trading with the bands. This book has phenomenal information showing how the inventor uses them to trade.
It also contains some interesting historical detail about the background of how John Bolinger created the bands.
You can see more by checking out the book on Amazon: Bollinger on Bollinger Bands
The Next Step: Get More Winning Trades
If you are eager to take your trading strategies and market analysis to the next level, consider exploring Tradinformed Excel Backtest Models. These Excel-based models will help you design, optimize and track your trading strategies.
The 15-in-1 Package is our most powerful package. This will help you learn how to backtest trading strategies and make informed trading decisions while providing you with the tools you need to develop your own trading systems.