How to Calculate Bollinger Bands in Excel

Written by suman medda
  • Share
  • Tweet
  • Share
  • Pin
  • Email
How to Calculate Bollinger Bands in Excel
Bollinger bands show the upper and lower trend lines of stocks and bonds based on recent performance. (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 travelling. 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.

Skill level:
Moderately Challenging

Other People Are Reading

Things you need

  • Microsoft Excel
  • Recent security price information

Show MoreHide

Instructions

  1. 1

    Open a "Worksheet" in Microsoft Excel.

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

  3. 3

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

  4. 4

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

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

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

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

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

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

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

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

Don't Miss

Filter:
  • All types
  • Articles
  • Slideshows
  • Videos
Sort:
  • Most relevant
  • Most popular
  • Most recent

No articles available

No slideshows available

No videos available

By using the eHow.co.uk site, you consent to the use of cookies. For more information, please see our Cookie policy.