How to Export Historical Prices to Excel from Yahoo Finance
This is a cool little trick I learned while taking a class in advanced portfolio management in college. If you are at all interested in measuring stock performance over time, it helps to know how to grab historical data from a popular free source. There are a few you can use, but the method that is the easiest is through Yahoo! Finance.
Why would I want historical data?
Good question. The key is in being able to better understand stock price movements over time. Maybe you are a big fan of data visualization, or maybe you just enjoy looking at stock charts all day long. Either way, having a data set is crucial. Let’s look at how to pull it from Yahoo Finance.
How to Download the Data
Let’s take a look at IBM stock as our example.
1. Go to Yahoo Finance, and enter the ticker IBM in the upper left.
2. On the left sidebar, click “Historical Prices.”
3. Enter your date range, and the type of returns that you want (daily, weekly, or monthly). Ignore “dividends only.” Click on “Get Prices.”
4. Scroll down to the bottom of the page and click the link to “Download to Spreadsheet.”
5. It will default save as a .csv file. This is okay.
How to Analyze The Data
Open this .csv file into an Excel spreadsheet. Let’s now calculate a periodic return. The only columns that we really care about for this are the date and the adjusted close (you can hide the other columns, or just get rid of them altogether). If you downloaded the data on a daily basis, then periodic returns = daily returns, and so on. Daily stock prices obviously give you a lot more data points than weekly returns. Regardless, we compute the return the same way as follows:
(B-A) / A
where A = price at time t
B = price at time t + 1
If you want this simplified, it equals
B/A – 1
Either formula works.
So, create a third column, enter this formula, and copy-paste it all the way down through your data. (For Excel beginners, the formula will dynamically change it as you paste it to other cells).
You now have the periodic returns of that stock price. This works for daily, weekly, and monthly returns. Total time involved should be no more than 5-10 minutes if you are proficient with Excel