Bollinger Bands Formula and Excel Calculation

This article will show you how to use Excel or any other trading calculation tool or platform and calculate Bollinger Bands by using a simple spreadsheet.

The Bollinger Bands formula is easy to follow, which is why you may construct your indicator with ease.

The first step in using Excel to create your own Bollinger bands is to obtain the stocks you like. We’ll show how simple it can be and take you through a process of quickly pulling Yahoo Finance information, which we’re going then importing into Microsoft Excel!

We recommend you read through our other tutorial on how to import data from Yahoo finance and then proceed with the construction of Bollinger Bands.

Also, in this blog article, we’ll show you how to make your own Bollinger bands indicator for tradestation and multicharts using easylanguage. We will also code the indicators so we can use them on MetaStock!

If you want to get a deeper understanding of the Bollinger band indicator, this blog is full of exciting material that further details the topic. This list has various articles and videos which will help traders understand how it works in everyday trading situations:

  • What indicator is best to use with Bollinger Bands?
  • Exponential Bollinger Bands – Download, Tips and Strategies
  • Bollinger Bands Indicator Essential Tutorial for Dummies
  • Bollinger Bands Standard Deviation
  • TradeStation Strategy – Bollinger Bands Reversal Price Exit Setup
  • The Bollinger Bands EasyLanguage Tutorial
  • Bollinger Bands Volatility Breakout and Squeeze
  • The Bollinger Bands Indicator Tutorial
  • Bollinger Bands BandWidth

The Bollinger Bands Formula

Before we show you how to build the Bollinger Bands in Excel, let’s get familiar with their basic formula. For this indicator, calculate values between two ranges and determine which band corresponds most closely based on volatility levels of prices within those periods.

The Bollinger Bands formula is a technical analysis tool that forecasts the price of a stock or other security by using two bands. The bands themselves consist of 3 lines – the lower band, middle band, and upper band. They represent a trading range in which price is not expected to go outside of soon.

The upper band is set at SMA (simple moving average) plus two standard deviations, and the lower band is set at SMA minus two standard deviations.

The purpose of this is to indicate how volatile or stable the market is in current market conditions. For example, if prices are above their upper limit, they may be on the verge of a correction down; likewise, if prices are below their lower limit, they may be on the brink of correction up.

You can perform the Bollinger Bands calculation using the following formula.

  • Middle Band = 20-day simple moving average (SMA)
  • Upper Band = 20-day SMA + (20-day standard deviation of price x 2)
  • Lower Band = 20-day SMA – (20-day standard deviation of price x 2)

How to calculate Bollinger Bands in Excel

Let’s look at the most crucial aspect of this post, which is a Microsoft Excel tutorial for developing the Bollinger Bands indicator.

As we previously stated, the first step is to gather data from your favorite stocks using Yahoo Finance.

This blog also contains many additional lessons that will teach you about Excel and its use for trading and investment calculations. We’ve compiled a list of tutorials below:

  • How to create Heikin Ashi Excel Stock Chart – Tutorial
  • Excel Stock Charts and Technical Analysis – 2021 Tutorial
  • Forex Trading Journal Excel Template
  • ATR Average True Range Excel Calculation and Charting
  • How to import the stock prices of Yahoo Finance to Excel
  • How to Draw a Chart in Excel Using Yahoo Finance
  • How to Export Data from TradeStation to Excel
  • ATR Stop Loss Calculator | Free Excel Calculator and Indicators

Let us begin by teaching you how to make bollinger bands in Excel.

I created a new Excel spreadsheet by importing all of the data on Apple stock.

Apple stock data imported in Excel

At this stage, all data has been gathered and organized to create our indicator.

We’ll need one more Excel column for the Simple Moving Average calculation, representing the middle band. We’ll use a twenty-period moving average, then select the twenty cells above with the average formula.

Bollinger Bands Excel tutorial create a sma column

We’ve already established the basic parameters of our moving average. After that, we need to calculate the upper band and lower band. These two lines, as you know, represent the standard deviation of the middle line, which is to say, the moving average.

The typical deviation is set at 2. We’ll need two additional Excel columns to obtain the values. The standard deviation is determined using the same Excel cells as the moving average.

Calculate the lower band step

The standard deviation is multiplied by 2. It’s also possible to make an external cell with an editable value so you don’t have to change the formula every time.

Calculate the lower band step

It’s important to subtract the standard deviation from the moving average (middle line) in order to generate the lower band or add the value to the middle line to create the upper band once it’s been determined.

Calculate the lower band step

At this point we have the 3 values needed to create the Bollinger bands indicator.

If you still have concerns, we came across this video on youtube that shows you step by step through the entire procedure in a slightly different manner than ours.https://www.youtube.com/embed/Q0Y3NcevC30?start=118&feature=oembed

How to create Bollinger Bands in EasyLanguage for TradeStation

The Bollinger Bands Formula for calculation in EasyLanguage is:

inputs:
double BollingerPrice ( Close ),
int Length( 10 ),
double NumDevsUp ( 2.0 ),
double NumDevsDn( -2.0 );

variables:
int Avg( 0 ),
double SDev( 0 ),
d ouble LowerBand( 0 ),
double UpperBand( 0 );

Avg = AverageFC( BollingerPrice, Length ) ;
SDev = StandardDev( BollingerPrice, Length, 1 ) ;
UpperBand = Avg + NumDevsUp * SDev ;
LowerBand = Avg + NumDevsDn * SDev ;

 

The Bollinger Bands code for MetaStock:

bb = (close – lower band) / (Upper band – lower band)

{The upper band}
upper:=2*Stdev( CLOSE,20 ) + Mov(CLOSE,20,SIMPLE);
{The lower band}
lower:=Mov(CLOSE,20,SIMPLE)-2*Stdev( CLOSE,20);
{bb}
percb:=(C-lower)/(upper-lower)*100;
percb

As you can see, the Bollinger Bands calculation using only a Simple Moving Average with 2 standard deviations.

Why is an indicator based only on moving average so popular? Because using the standard deviations, you could draw in a chart the price’s volatility expansion.

When volatility increase, the band’s distance widening and vice-versa. In the same way, if you increase the standard deviation value, the Bollinger Bands widening. In a range market, the Bollinger Bands narrowings.

1-bollinger-bands-formula-article

The common calculation is with 20 periods moving average and two standard deviations. You can change these parameters and optimize for many instruments.

Remember that you are overfitting your system every time you choose a specific “custom set” for your indicator.

The best practice is to find a set of parameters that works with many instruments in many markets. Generally, the 20 periods and two standard deviations are good settings.

If you use multiple Bollinger Bands strategies, you can set three different Standard Deviations, for example, 1.5 – 2 – 3.

We created a custom indicator that shows the volatility phases:

Bollinger-Bands-BandWidth-TradeStation-MultiCharts

You can find it in this post.

More resources: