«

»

Dec 12

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.

 

3 comments

  1. wherestjb

    Thanks! Can this be done to get historical market cap on a particular date?

  2. Jean-Christophe Emery

    Hi,

    How should I modify the command in order to get EPS?
    I was thinking I simply have to add &f=e but it doesn’t work.
    Any advice?

    Thanks

  3. Lindsey Lee

    Useful tool. Thanks for sharing. How do I modify the code in order to download only the adjusted close?

Leave a Reply

%d bloggers like this: