How to Calculate Bollinger Bands in Excel

Save
Bollinger bands show the upper and lower trend lines of stocks and bonds based on recent performance.
Bollinger bands show the upper and lower trend lines of stocks and bonds based on recent performance. (Image: John Foxx/Stockbyte/Getty Images)

Bollinger bands are a mathematical tool used to predict the prices of securities based on their past performance. There are three bands: middle, upper and lower. The bands are a high, low and average prediction of where the security is traveling. A feature of Bollinger bands is that the upper and lower bands contract and expand based on the volatility of the security. The bands can be calculated manually or they can be streamlined and automatically calculated in Microsoft Excel.

Things You'll Need

  • Microsoft Excel
  • Recent security price information

Open a "Worksheet" in Microsoft Excel.

Label columns "A" through "G" by writing a name in the first row of the column. "A" is the Closing Price, "B" is the Simple Moving Average, "C" is the Deviation, "D" is the Deviation Squared, "E" is the Standard Deviation, "F" is the Upper Band, and "G" is the Lower Band.

Enter the closing price of the security for the past 20 days into column "A." Cells "A2" to "A21" should now be filled.

Calculate the mean of the 20 days by entering "=SUM(A2:A21)/20" in the "A22" cell.

Copy the value from cell "A22" into column "B." Cells "B2" to "B21" should now be filled with the same value from cell "A22."

Enter "=B2-A2" in cell "C2." Copy the value in cell "C2" and paste it into the entire column from cells "C2" to "C21."

Enter "=POWER(C2,2)" in cell "D2." Copy the value in cell "D2" and paste it into the column from cells "D2" to "D21."

Enter "=SQRT(SUM(D2:D21)/20)" into cell "E2." Copy the value in cell "E2" and paste it into the entire column from cells "E2" to "E21."

Enter "=B2+(2*E2)" in cell "F2." Copy the value in cell "F2" and paste it into the entire column from cells "F2" to "F21." This column is the Upper Band.

Enter "=B2-(2*E2)" in cell "G2." Copy the value in cell "G2" and paste it into the entire column from cells "G2" to "G21." This column is the Lower Band.

Locate the three band columns. The Middle Band is in column "B." The Upper Band is in column "F." And the Lower Band is in column "G."

Related Searches

References

Promoted By Zergnet

Comments

You May Also Like

Related Searches

Check It Out

4 Credit Myths That Are Absolutely False

M
Is DIY in your DNA? Become part of our maker community.
Submit Your Work!