Get historic prices by ISIN from yahoo finance

Carol.Kar picture Carol.Kar · Sep 11, 2015 · Viewed 11.4k times · Source

I have the following problem:

I have around 1000 unique ISIN numbers of stock exchange listed companies.

  • I need the historic prices of these companies starting with the earliest listing until today on a daily basis.

However, as far as my research goes, yahoo can only provide prices for stock ticker symbols, which I do not have.

Is there a way to get for example for ISIN: AT0000609664, which is the company Porr the historic prices from yahoo automatically via their api?

I appreciate your replies!

Answer

GPMueller picture GPMueller · Sep 21, 2015

The Answer:

To get the Yahoo ticker symbol from an ISIN, take a look at the yahoo.finance.isin table, here is an example query:

http://query.yahooapis.com:80/v1/public/yql?q=select * from yahoo.finance.isin where symbol in ("DE000A1EWWW0")&env=store://datatables.org/alltableswithkeys

This returns the ticker ADS.DE inside an XML:

<query yahoo:count="1" yahoo:created="2015-09-21T12:18:01Z" yahoo:lang="en-US">
    <results>
        <stock symbol="DE000A1EWWW0">
            <Isin>ADS.DE</Isin>
        </stock>
    </results>
</query>
<!-- total: 223 -->
<!-- pprd1-node600-lh3.manhattan.bf1.yahoo.com -->

I am afraid your example ISIN won't work, but that's an error on Yahoos side (see Yahoo Symbol Lookup, type your ISINs in there to check if the ticker exists on Yahoo).

The Implementation:

Sorry, I am not proficient in Java or R anymore, but this C# code should be almost similar enough to copy/paste:

public String GetYahooSymbol(string isin)
{
    string query = GetQuery(isin);
    XDocument result = GetHttpResult(query);
    XElement stock = result.Root.Element("results").Element("stock");
    return stock.Element("Isin").Value.ToString();
}

where GetQuery(string isin) returns the URI for the query to yahoo (see my example URI) and GetHttpResult(string URI) fetches the XML from the web. Then you have to extract the contents of the Isin node and you're done.

I assume you have already implemented the actual data fetch using ticker symbols. Also see this question for the inverse problem (symbol -> isin). But for the record:

Query to fetch historical data for a symbol

http://query.yahooapis.com:80/v1/public/yql?q=select * from yahoo.finance.historicaldata where symbol in ("ADS.DE") and startDate = "2015-06-14" and endDate = "2015-09-22"&env=store://datatables.org/alltableswithkeys

where you may pass arbitrary dates and an arbitrary list of ticker symbols. It's up to you to build the query in your code and to pull the results from the XML you get back. The response will be along the lines of

<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="71" yahoo:created="2015-09-22T20:00:39Z" yahoo:lang="en-US">
  <results>
    <quote Symbol="ADS.DE">
      <Date>2015-09-21</Date>
      <Open>69.94</Open>
      <High>71.21</High>
      <Low>69.65</Low>
      <Close>70.79</Close>
      <Volume>973600</Volume>
      <Adj_Close>70.79</Adj_Close>
    </quote>
    <quote Symbol="ADS.DE">
      <Date>2015-09-18</Date>
      <Open>70.00</Open>
      <High>71.43</High>
      <Low>69.62</Low>
      <Close>70.17</Close>
      <Volume>3300200</Volume>
      <Adj_Close>70.17</Adj_Close>
    </quote>
    ......
  </results>
</query>
<!-- total: 621 -->
<!-- pprd1-node591-lh3.manhattan.bf1.yahoo.com -->

This should get you far enough to write your own code. Note that there are possibilities to get data as .csv format with &e=.csv at the end of the query, but I don't know much about that or if it will work for the queries above, so see here for reference.