Calculate Beta With Historical Market Data

This tool calculates beta across any time frame 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).

The value of beta given by this tool (specifically, the beta of the close prices) matches that quoted by Yahoo Finance.

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


7 thoughts on “Calculate Beta With Historical Market Data

  1. Hello Samir khan,
    I have downloaded spreadsheet “calculate beta with historical market data’ on my laptop from your website http://www.investexcel.net. But it is not working in my open office even after enabling macros. Do I require to record macros, run macros, or organise macros?
    Would you guide me in this regard so that spreadsheet starts working.
    Please send a reply quickly.

  2. Hello Samir khan,
    Can I download the spreadsheet “calculate beta with the historical market data” in the “EXCEL ONLINE”?
    Please send a reply. thanks

  3. Does not work on Excel for Mac, ver. 15.15 (latest update for Office for Mac 2016). Clicking the “Download Historical Stock Data and Calculate Beta” button gives me a “Compile error in hidden module: Module 1” message.

  4. This spreadsheet no longer works, I suspect because Yahoo changed the column order for historical price downloads. Macro reports “Run-time error ‘1004’: Method “Range’ of object ‘_Global’ failed”.

  5. Could you update the algorithm for obtaining historical data from Yahoo in this spreadsheet Excel “Calculate Beta With Historical Market Data” ?
    In May, Yahoo! curtailed their free datafeed API that was supplying free data to Pairtrade Finder.

    Over the last month, we’ve been able to build a new bridge for Pairtade Finder to enable our

    users to continue to use Yahoo! to source free data. We’ve also upgraded the IQ Feed connection

    in the latest version to ensure that no matter what happens with Yahoo! (they just merged with Verizon)

    you always have access to high-quality data.

Leave a Reply

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

What is 13 + 3 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)