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.
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”
Hello Samir khan,
I have downloaded spreadsheet “calculate beta with historical market data’ on my laptop from your website https://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.
It won’t work in Open Office.
Hello Samir khan,
Can I download the spreadsheet “calculate beta with the historical market data” in the “EXCEL ONLINE”?
Please send a reply. thanks
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.
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”.
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.
Yes, I can update the spreadsheet. Can you link to investexcel.net on your blog?