Excel VBA Bollinger Band

Banking, Finance and Investment Industry

VBA / Software Developer: Francis Lim

Project Categories: Data-Mining,  Data Cleansing, Financial Modelling, Numerical Analysis

Freeware: Bollinger Band Historical Stock Data

System Requirements

Windows XP, Windows Vista, Windows 7

Microsoft Excel 2003 or above

This Excel Spreadsheet downloads historical stock prices provided by Yahoo Finance. A freeware Spreadsheet. It is written in Visual Basic Applications (VBA), a macro programming language for Microsoft Office - Access, Excel, Word, FrontPage, Outlook, PowerPoint, and Visio.

To know your stock ticker quotes, please refer to yahoo.finance.com. The downloaded stock data contains Date and Close in ascending date order.

What is Bollinger Band?

Bollinger Bands are a technical analysis tool invented by John Bollinger in the 1980s. Having evolved from the concept of trading bands, Bollinger Bands can be used to measure the highness or lowness of the price relative to previous trades.

Bollinger Bands consist of:

• a middle band being an N-period simple moving average (MA)

• an upper band at K times an N-period standard deviation above the middle band (MA+K*sigma)

• a lower band at K times an N-period standard deviation below the middle band (MA-K*sigma)

Typical values for N and K are 20 and 2, respectively. The default choice for the average is a simple moving average, but other types of averages can be employed as needed. Exponential moving averages are a common second choice. Usually the same period is used for both the middle band and the calculation of standard deviation.

How to Use MACD-BollingerBand Excel Sheet

You will need to enable macros for this spreadsheet to work. To enable macros, go to Excel Menu - Tools / Options, click on the Security tab and go to Macro Security. Change the setting to Medium, close and reopen the workbook It will ask you if you want to enable Macros, click 'Yes' to enable it.

User can input the following cells in the MACD Excel Sheet:-

B1 Cell Row - Yahoo Ticker

Enter a valid stock ticker code. If you're not sure of the stock symbol, click on 'Yahoo Symbol Lookup' to Yahoo Finance website.

B2 Cell Row - EMA (Fast) for MACD

Input the Exponential Moving Average (Fast), default is value 12.

B3 Cell Row - EMA (Slow) for MACD

Input the Exponential Moving Average (Slow), default is value 29.

B4 Cell Row - Signal Line for MACD

Input the Signal line of MACD, default is value 9.

B5 Cell Row - SMA (Bollinger)

Input Simple Moving Average, default is value 21.

B6 Cell Row - Start Date

The start date for the historical data.

Just Press "Retrieve Data" Button, it will download Historical Data from Yahoo Finance website run the MACD calculation and chart.

The purpose of Bollinger Bands is to provide a relative definition of high and low. By definition, prices are high at the upper band and low at the lower band. This definition can aid in rigorous pattern recognition and is useful in comparing price action to the action of indicators to arrive at systematic trading decisions.

Bollinger Calculation

For each trading period an upward change (U) or downward change (D) is calculated. Up periods are characterized by the close b

The first step in calculating the Bollinger Bands is to find the simple moving average.

The upper and lower Bollinger Bands are calculated by determining a simple moving average, and then adding/subtracting a specified number of standard deviations from the simple moving average to calculate the upper and lower bands.

In the above formulas, D represents the number of standard deviations applied to the Bollinger Bands indicator. 

Interpretation

The use of Bollinger Bands varies widely among traders. Some traders buy when price touches the lower Bollinger Band and exit when price touches the moving average in the center of the bands. Other traders buy when price breaks above the upper Bollinger Band or sell when price falls below the lower Bollinger Band. Moreover, the use of Bollinger Bands is not confined to stock traders; options traders, most notably implied volatility traders, often sell options when Bollinger Bands are historically far apart or buy options when the Bollinger Bands are historically close together, in both instances, expecting volatility to revert back towards the average historical volatility level for the stock.

When the bands lie close together a period of low volatility in stock price is indicated. When they are far apart a period of high volatility in price is indicated. When the bands have only a slight slope and lie approximately parallel for an extended time the price of a stock will be found to oscillate up and down between the bands as though in a channel.

Traders are often inclined to use Bollinger Bands with other indicators to see if there is confirmation. In particular, the use of an oscillator like Bollinger Bands will often be coupled with a non-oscillator indicator like chart patterns or a trendline; if these indicators confirm the recommendation of the Bollinger Bands, the trader will have greater evidence that what the Bands forecast is correct.