Bollinger Bands are one of the most popular indicators being used by quantitative traders today. While almost any trading software will be able to calculate the Bollinger Band values for you, it never hurts to know how to get under the hood and do it yourself.
Mark from Tradinformed specializes in using excel to backtest trading systems and calculate values for popular indicators. He has released a short blog post and video that walks you through exactly how to calculate bollinger bands using Excel.
He starts by offering his own description of Bollinger Bands, and then explains how they are calculated:
The first stage in calculating Bollinger Bands is to take a moving average.
Then you calculate the standard deviation of the closing price over the same number of periods.
The standard deviation is then multiplied by a factor (typically 2).
The upper band is calculated by adding the standard deviation multiplied by the factor to the moving average.
The lower band is calculated by subtracting the standard deviation multiplied by the factor from the moving average.
Here are the formulas he uses in his video:
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)
This is Mark’s video walk-through on calculating Bollinger Bands with Excel:
He also explains how he uses Bollinger Bands in his own trading:
I don’t normally have Bollinger Bands on my charts because I find they clutter the charts and distract from the price action.
However, I often add them to charts temporarily 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 they can be applied to any market and timeframe without needing to adjust the parameters.