How to Calculate Bollinger Bands Using Excel.
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. They can be used for taking profits or helping 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. 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.
YouTube Video.
Formulas Used.
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)
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 how the bands were originally created.
Related Links.
If you are interested in using Excel to backtest trading strategies my new Ebook course: How to Backtest a Trading Strategy using Excel is now available in the Amazon Kindle Bookstore. You may also be interested in how to calculate the following indicators: Calculating the RSI Indicator Calculating the SuperTrend Indicator Optimising Trading Strategies using Excel.
Other Articles You Might Like.
// The SuperTrend Indicator is a great way to identify the current market trend. The…
This article shows how to calculate the RSI indicator developed by J. Welles Wilder. It…
This video article describes how you can can calculate the MACD Indicator in Microsoft Excel. I…
Tradinformed.
Tradinformed is committed to helping traders develop their skills and stay ahead of the competition. See how you can learn to backtest your own strategies and get new trading ideas.
How to Calculate the SuperTrend Indicator using Excel 3 Profitable Ichimoku Trading Strategies A Simple, Profitable Heikin-Ashi Trading System How to Calculate the PSAR Indicator using Excel Example: Backtesting a Trading Strategy How to Calculate a Trailing Stop-Loss Using Excel Which Tradinformed Model to Choose? Latest Posts.
Algorithmic Trading (1) Binary Options (2) Chart Patterns (1) Ebook (2) Economic Data (1) Economic Growth (2) Essential Traders Library (4) Excel Trading (6) Google Sheets (1) How to Backtest (2) Interviews with Traders (1) Learn to Trade (17) MT4 (5) Trade Ideas (2) Trading Automation (3) Trading Book Reviews (1) Trading Books (1) Trading Information (10) Trading Psychology (2) Trading Strategies (24) Uncategorized (2)
Santa Claus Rally Backtest Model $ 19.75 10 in 1 Package $ 112.10 $ 72.18 4 in 1 Package $ 42.98 $ 33.52 Breakout Model $ 19.75.
21 Technical Indicators $ 5.99 Long-Short Backtest Model using Excel $ 11.25 Advanced Backtest Model $ 19.75 21 More Technical Indicators $ 5.99.
VIX Volatility S&P 500 Entry $ 19.75 4 in 1 Package $ 42.98 $ 33.52 Long-Short Backtest Model using Excel $ 11.25.
Tradinformed is committed to helping traders develop their skills and stay ahead of the competition. See how you can learn to backtest your own strategies and get new trading ideas.

Forex, Bollinger Bands, and Excel.
If you’re a regular traveller, you’ll know that changing money from one currency to another can be a constantly changing battleground. Foreign exchange rates (or Forex), change all the time, and what can seem like a good rate can disappear within days.
When I first started visiting Canada in 2001, one British pound (GBP) bought $2.35 Canadian dollars (CAD). Canada seemed cheap! However, fast forward ten years to 2011, and 1 GBP now only buys 1.59 CAD. In other words, the pound has devalued by around 30% against the Canadian dollar (and a similar amount to the US dollar). This makes my (still necessary) trips to North America much more expensive, and things no longer seem like a bargain.
This means I have to keep a much closer eye on the exchange rate in order to balance my budget. I now exchange money in advance if I think I’m getting good value. But how do I know this? I can’t tell the future – I can only look backwards.
The first step is to locate historical exchange rates. There are many websites that give you this data for any two currencies. I like Oanda because it allows you to download historical data in a format you can import into Excel.
This is an Excel chart that gives the GBP-USD daily exchange rate (thats’ the blue line called CLOSE) over the last year.
But there are three more lines on the chart. We have the 20 Day Moving Average, and the Upper and Lower Bollinger Bands. Bollinger Bands are equivalent to the 20-day moving average plus or minus two standard deviations)
If you examine the chart carefully, you’ll find that nearly all of the movement of the blue closing line is between the two Bollinger Bands. Let’s examine one small section of the chart in detail.
The blue line doesn’t above or below the Bollinger bands. In fact, when the blue line hits the bottom Bollinger band, it’s probably going to either drift along the bottom or go up. When the blue line hits the top Bollinger band, it’ll probably drift along the top or go down.
You can use these indicators to determine whether you’re getting a good or bad exchange rate. Now, this rule of thumb isn’t universally true – it’s only valid in a market that’s trading horizontally, without much up or down momentum.
Similar to the stock market, there are times in which Forex rates change rapidly. This is known as volatility. During periods of volatility the Bollinger bands move further apart, and vice-versa when the market is stable.
Now, some professional forex traders have drawn the conclusion that bands that get narrower indicate that the market will become more volatile in the future, while bands that get wider mean that the market will become less volatile in the future. Additionally, these traders use Forex data that’s updated on a minute-by-minute basis, not day-by-day as I have used here. This allows them to make instant trading decisions, taking advantage of small pricing movements.
Another useful indicator is the Bollinger Squeeze. This is equal to the (Upper Band – Lower Band)/Moving Average. A six month low in the Bollinger Squeeze indicates greater future volatility (whether it’s a significant up or down movement is flagpolled by other indicators).
Bollinger bands are remarkably easy to create in Excel. The STDEV() function is key – it’ll calculate the standard deviation of a data set. You can download the Excel spreadsheet I used to create the charts above by clicking here.
2 thoughts on “ Forex, Bollinger Bands, and Excel ”
I do not find a downloadable Bollinger band code on this page should it be here?
Sorry found that here after I wrote the comment, can I suggest you bold and underline that Hyperlink.

How To Calculate Bollinger Bands Using Excel.
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.
Knowing how to calculate the indicators you use will give you a better understanding of your quantitative trading system.
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:
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.
3 responses.
The calculations for the Upper and Lower Bollinger Bands as given in the rubric “Here are the formulas he uses in his video:” are WRONG.
I have been in contact with Mark Unsell (who made the video)and he has agreed his equations are in error.
Herewith CORRECTED eqs; –
Upper Bollinger Band I23 =H23+(STDEVPA(F4:F23)*$I$3)
Lower Bollinger Band J23 =H23-(STDEVPA(F4:F23)*$J$3)
Thanks for sharing!
Parece q ele cometeu um pequeno deslize ao usar períodos diferentes para a média móvel e para o cálculo do desvio padrão: para um começou com a célula F4 e para o outro F5, respectivamente.
Só n entendo pq aparece esse “três dólares”. Isso é só uma forma de fixação d células, então só era para aparecer o símbolo de dólar.

Bollinger bands excel spreadsheet

Please use this website in an appropriate and considerate manner. This means that you should cite Finance4Traders by at least providing a link back to this site if you happen to use any of our content. In addition, you are not permitted to make use of our content in an unlawful manner. You should also understand that our content is provided with no warranty and you should independently verify our content before relying on them. Do refer to the site content policy and privacy policy when visiting this site.
1 comments:
Can you post the contents of the BB_1 Sub routine? That would be most helpful.