How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)

How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too)

---

 

Introduction

I was working on creating a spreadsheet to calculate profits and losses on options positions but didn’t know how to populate excel with stock quotes. Back in the day there used to be an interface to get stock quotes with the MSN Money site but it is not supported anymore. The idea behind this spreadsheet was to use the latest and historic quotes to calculate intrinsic values of options and P&L for expired ones. Kind of just trying to keep track of my record and evaluate performance. Of course the issue we face is that stock prices move every second and maintain all that data manually is not worth it. After some research I tried using Google Finance to populate Excel to no avail but found Yahoo Finance supports this more easily. I ended up writing a post to help others with regards to that: How to: Obtain stock quotes from Yahoo finance (you can query them via Excel too).

Just recently, a reader asked about making the query date specific. Yahoo! Finance does support getting historical (closing) prices. It is a very basic interface, so it comes with a lot of limitations. If you just want historic closing prices, then this is the place for you. Within the limitations I have found (obviously there are more but from my simple needs), I have identified these:

• You are NOT able to get more than one Stock or Index at a time
• You are NOT able to download data for everything (exchange rates is one example, there are some “weird/foreign” stocks as well)

but now that I have completely taken all the enthusiasm away from you, let’s get into the exciting part of how to get this to work:

---

Solution

In order to create the web query that will provide us with historical stock (closing) prices, we need to supply Yahoo some information:

1. Stock Symbol (Mandatory)
2. Date Range (Optional, if not provided it will return all data available)
3. Internal (Optional, defaults to days if not provided)

The URL is composed as follows (step by step):

• Starting URL:
  • http://ichart.yahoo.com/table.csv?
• Stock Symbol
  • http://ichart.yahoo.com/table.csv?s=AAPL
• Starting Date
  • Month (goes from 0 to 11, don’t ask me why. So if you want July which is the seventh month of the year, you need to supply 06)
    • http://ichart.yahoo.com/table.csv?s=AAPL&a=0
  • Day
    • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1
  • Year
    • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010
  • Ending Date
    • Month (again, goes from 0 to 11)
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11
    • Day
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31
    • Year
      • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015
    • Interval
      • Here you supply one of the three trading periods supported:

Name		Tag
Daily		d
Weekly		w
Monthly		m

• http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m

• And let’s tell Yahoo we want this as a CSV file
  • http://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv

We’re done. Now, if you click the URL: https://ichart.yahoo.com/table.csv?s=AAPL&a=0&b=1&c=2010&d=11&e=31&f=2015&g=m&ignore=.csv you will start the download of your CSV file containing all the historical prices for Apple (AAPL) since 2010 through the end of 2015. Did you notice how I used https instead of http? Yahoo Finance supports https if you want to have your queries protected, which I recommend.

---

Results and Conclusions

Now, let’s study some sample the output:

Date	Open	High	Low	Close	Volume	Adj Close
04/01/2010	213.429993	215.589996	190.25	192.059998	215986100	25.547121

Let’s go column by column:

• Date
  • This is the date for which the values correspond
• Open
  • The opening price for APPL for the given period/interval (week in this case)
• High
  • The highest price for AAPL for the given period/interval (week in this case)
• Low
  • The lowest price for AAPL for the given period/interval (week in this case)
• Close
  • The closing price for AAPL for the given period/interval (week in this case)
• Volume
  • The trading volume for AAPL for the given period/interval (week in this case)
• Adjusted Close (Adj Close)
  • The closing price, adjusted for splits and the like. For example, there was a 7:1 split in Apple, so the adjusted close is less than 1/7th of the actual closing price back in 2010. I believe besides splits; it also considers dividends in the adjusted price. If you look at the price between 1/10/2015 and 2/11/2015, you can see there is a 0.509338 price differential which is very close to the .52 dividend paid.

---

UPDATE (May 20th, 2017)

Some sad news for users of Yahoo Finance to obtain free stock quotes: Yahoo has changed the way you form the URL and has made it somewhat more difficult for you to use the service. I think for most cases it is not a viable service anymore. As reported by some of the comments, now the URL requires a parameter called CRUMB which is obtained from the cookies set when establishing a session with Yahoo. In other words, that crumb is unique to your session.

Your options:

1) You can do something like this:

Parse the HTML when you make a query using the web page: Example URL: https://finance.yahoo.com/quote/SAN/history?period1=1483250400&period2=1495256400&interval=1d&filter=history&frequency=1d

2) Use the new URL and somehow get your cookie and obtain the CRUMB:

What do we know?

Yahoo changed their URL scheme to require an established session with cookies. Potentially you could use ‘curl’ to establish a connection to the main site, get a cookie, and then use that to get a crumb. With that crumb, now you can form your download URL. Because the Crumb is part of the cookie, it doesn’t change but is part of your browser so it won’t work on a different browser with a different cookie. Because of that, you might get an error like this:

{
    "finance": {
        "error": {
            "code": "Unauthorized",
            "description": "Invalid cookie"
        }
    }
}

Do note as well that times changed to POSIX/UNIX timestamps. You’ll need to convert your dates now.

3) Use download.finance.yahoo.com instead

A reader in the comments suggested it and it seems I was able to figure out how to use the API there. There is a catch of course: No historical prices… at least I wasn’t able to see an option for it. If you manage to get historical prices from Yahoo please share with us via the comments section. If this works for you, check out my post: How to: Obtain historical stock prices from Yahoo finance (you can query them via Excel too) Part II

4) Use Google instead

This probably means that for regular users Yahoo Finance is no longer a viable option to obtain stock quotes in Excel. I also can’t seem to be able to access https://finance.yahoo.com/quote/GOOG/history?p=GOOG on the excel web browser. My guess is that Yahoo has blocked access for the Excel client (very sneaky.) At this point my suggestion would be to use Google Finance. I’ll try to put together a post and reference it here so people can convert over.