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. Traders often use them to take profits or to help to 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.
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 I find 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 I am developing automatic trading strategies because they are self-scaling. This means that you can apply them to any market and timeframe without needing to adjust 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.
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 a lot of information showing how the inventor uses them to trade. It also contains some interesting historical detail about the background to how John Bolinger created the bands.