Calculate Beta With Historical Market Data

This tool calculates beta across any timeframe for any stock against any benchmark. It uses historical stock quotes downloaded from Yahoo Finance.

Other Excel spreadsheets on investexcel.net demonstrate how to calculate beta, and how Yahoo Finance calculates this measure of portfolio risk.

But this spreadsheet goes one step beyond this, and gives you a value of beta for your specific requirements

Beta measures historical systematic risk against a specific benchmark, and the values given on Yahoo Finance and Google Finance aren’t quite what you need. For example, Yahoo gives beta for the trailing 3 years against the S&P500 – but you need beta for the five years between 1995 to 2000 against the FTSE 100.

If so, this spreadsheet is perfect for you.  Just enter

  • a stock ticker whose beta you want
  • a benchmark ticker
  • and two dates

After you click the prominently-placed button, the tool grabs the historical market data from Yahoo Finance and calculates beta.

In the following screengrab, we’ve calculated the beta of Exxon Mobil (ticker: XOM) against the S&P500 for the three years trailing 31st March 2015.

Beta

You could, if wanted, change the time period or swap out the benchmark for NASDAQ 100 (ticker: ^NDX)

Downlowd Spreadsheet to Calculate Beta from Historical Market Data with Yahoo Finance

It's only fair to share...Share on FacebookShare on Google+Tweet about this on Twitter

Leave a Reply

Your email address will not be published. Required fields are marked *