How to Calculate Bollinger Bands in Excel

by Suman Medda ; Updated July 27, 2017
Bollinger bands show the upper and lower trend lines of stocks and bonds based on recent performance.

Items you will need

  • Microsoft Excel
  • Recent security price information

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.

Step 1

Open a "Worksheet" in Microsoft Excel.

Step 2

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.

Step 3

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

Step 4

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

Step 5

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

Step 6

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

Step 7

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

Step 8

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."

Step 9

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.

Step 10

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.

Step 11

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."

About the Author

Suman Medda holds a B.S. in biomedical engineering with a specialty in tissue engineering. His biomedical research abstracts have been featured in the Southeastern Surgical Conference. He enjoys technical and scientific writing and has been writing since 2007.

Photo Credits

  • John Foxx/Stockbyte/Getty Images