Retrieving Yahoo! Finance Information using YQL

YQL is a query language that lets you retrieve data from across the web using an SQL-like syntax. By making a simple REST query using a standardized syntax, you can receive XML- or JSON-formatted data from a bunch of web service APIs. You can play around with YQL here

I wrote a YQL wrapper in python which queries the Yahoo! Finance open data tables for financial data. Using this script, you can get current and historical financial info, news feeds, and options data for any ticker symbol.

Usage

The below examples assume the following import/initialization steps have been done. You can get most data with just a line of code.

import stockretriever
stocks = stockretriever.StockRetriever()


Getting Current Stock Summary Information

info = stocks.get_current_info(["YHOO","AAPL","GOOG","MSFT"])

get_current_info() uses the yahoo.finance.quotes datatable to get all of the stock information presented in the main table on a typical stock page and a bunch of data from the key statistics page.
Here’s what a typical result-set looks like:

 [{
    "symbol": "YHOO",
    "Ask": null,
    "AverageDailyVolume": "25755200",
    "Bid": null,
    "AskRealtime": "0.00",
    "BidRealtime": "0.00",
    "BookValue": "9.184",
    "Change_PercentChange": "0.00 - 0.00%",
    "Change": "0.00",
    "Commission": null,
    "ChangeRealtime": "0.00",
    "AfterHoursChangeRealtime": "N/A - N/A",
    "DividendShare": "0.00",
    "LastTradeDate": "6/29/2010",
    "TradeDate": null,
    "EarningsShare": "0.557",
    "ErrorIndicationreturnedforsymbolchangedinvalid": "N/A",
    "EPSEstimateCurrentYear": "0.68",
    "EPSEstimateNextYear": "0.77",
    "EPSEstimateNextQuarter": "0.16",
    "DaysLow": null,
    "DaysHigh": null,
    "YearLow": "13.88",
    "YearHigh": "19.12",
    "HoldingsGainPercent": "- - -",
    "AnnualizedGain": "-",
    "HoldingsGain": null,
    "HoldingsGainPercentRealtime": "N/A - N/A",
    "HoldingsGainRealtime": null,
    "MoreInfo": "cnsprmiIed",
    "OrderBookRealtime": "N/A",
    "MarketCapitalization": "19.446B",
    "MarketCapRealtime": null,
    "EBITDA": "1.323B",
    "ChangeFromYearLow": "+0.16",
    "PercentChangeFromYearLow": "+1.15%",
    "LastTradeRealtimeWithTime": "N/A - <b>14.04</b>",
    "ChangePercentRealtime": "N/A - 0.00%",
    "ChangeFromYearHigh": "-5.08",
    "PercebtChangeFromYearHigh": "-26.57%",
    "LastTradeWithTime": "Jun 29 - <b>14.04</b>",
    "LastTradePriceOnly": "14.04",
    "HighLimit": null,
    "LowLimit": null,
    "DaysRange": "N/A - N/A",
    "DaysRangeRealtime": "N/A - N/A",
    "FiftydayMovingAverage": "15.4047",
    "TwoHundreddayMovingAverage": "16.1174",
    "ChangeFromTwoHundreddayMovingAverage": "-2.0774",
    "PercentChangeFromTwoHundreddayMovingAverage": "-12.89%",
    "ChangeFromFiftydayMovingAverage": "-1.3647",
    "PercentChangeFromFiftydayMovingAverage": "-8.86%",
    "Name": "Yahoo! Inc.",
    "Notes": "-",
    "Open": null,
    "PreviousClose": "14.04",
    "PricePaid": null,
    "ChangeinPercent": "0.00%",
    "PriceSales": "3.00",
    "PriceBook": "1.53",
    "ExDividendDate": "12-May-04",
    "PERatio": "25.21",
    "DividendPayDate": "N/A",
    "PERatioRealtime": null,
    "PEGRatio": "1.47",
    "PriceEPSEstimateCurrentYear": "20.65",
    "PriceEPSEstimateNextYear": "18.23",
    "Symbol": "YHOO",
    "SharesOwned": null,
    "ShortRatio": "1.40",
    "LastTradeTime": "4:00pm",
    "TickerTrend": " ====== ",
    "OneyrTargetPrice": "19.48",
    "Volume": "0",
    "HoldingsValue": null,
    "HoldingsValueRealtime": null,
    "YearRange": "13.88 - 19.12",
    "DaysValueChange": "- - 0.00%",
    "DaysValueChangeRealtime": "N/A - N/A",
    "StockExchange": "NasdaqNM",
    "DividendYield": null,
    "PercentChange": "0.00%"
}]


Getting Current Stock News

news = stocks.get_news_feed('YHOO')

get_news_feed() uses the rss data table to get rss feeds under the Headlines and Financial Blogs headings on a typical stock page. Here’s an example result-set:

[
    {
     "pubDate": "Wed, 30 Jun 2010 06:45:23 Etc/GMT",
     "title": "Update: Amazon Back To Normal; So Where's The Explanation? (at Barron's Online)",
     "link": "http://us.rd.yahoo.com/finance/external/xbarronsblog/rss/SIG=13fvfc6kn/*http%3A//blogs.barrons.com/techtraderdaily/2010/06/30/update-amazon-back-to-normal-so-wheres-the-explanation/?mod=yahoobarrons",
     "description": null
    },
    {
     "pubDate": "Wed, 30 Jun 2010 01:56:30 Etc/GMT",
     "title": "[$$] Andreessen Horowitz Leads $20 Million Investment in Foursquare (at The Wall Street Journal)",
     "link": "http://us.rd.yahoo.com/finance/external/wsj/rss/SIG=12icuornn/*http%3A//online.wsj.com/article/SB10001424052748704103904575337411584163440.html?ru=yahoo&mod=yahoo_hs",
     "description": "Andreessen Horowitz Leads Foursquare Investment Foursquare, a mobile-based startup that lets people \"check in\" at bars, restaurants and other places through their smartphones, has raised $20 million from a group of venture capitalists that will help fuel its expansion."
    },
    {
     "pubDate": "Wed, 30 Jun 2010 00:59:13 Etc/GMT",
     "title": "[$$] Foursquare Raises New Funds (at The Wall Street Journal)",
     "link": "http://us.rd.yahoo.com/finance/external/wsj/rss/SIG=12ircfrro/*http%3A//online.wsj.com/article/SB10001424052748703374104575337434242710928.html?ru=yahoo&mod=yahoo_hs",
     "description": "Foursquare Raises New Funds Foursquare, a location-based social networking startup, raised $20 million from a group of venture capitalists to help fuel its expansion."
    }
]


Getting Historical Stock Information

news = stocks.get_historical_info('YHOO')

get_historical_info() uses the csv datatable to retrieve all available historical data on a typical historical prices page. Here’s an example result-set (truncated):

[
    {
     "Date": "2010-06-29",
     "Open": "463.44",
     "High": "464.55",
     "Low": "451.12",
     "Close": "454.26",
     "Volume": "3502100",
     "AdjClose": "454.26"
    },
    {
     "Date": "2010-06-28",
     "Open": "472.59",
     "High": "477.55",
     "Low": "469.01",
     "Close": "472.08",
     "Volume": "1762300",
     "AdjClose": "472.08"
    }
]


GettingĀ Options Data

options = stocks.get_options_data('YHOO')

get_options_data() uses the yahoo.finance.options table to retrieve call and put options from the options page. Here’s a typical result-set:

[
    {
     "symbol": "YHOO",
     "option": [
      {
       "symbol": "YHOO100717C00007500",
       "type": "C",
       "strikePrice": "7.5",
       "lastPrice": "7.90",
       "change": "0",
       "changeDir": null,
       "bid": "NaN",
       "ask": "NaN",
       "vol": "1",
       "openInt": "363"
      },
      {
       "symbol": "YHOO100717C00010000",
       "type": "C",
       "strikePrice": "10",
       "lastPrice": "5.20",
       "change": "0",
       "changeDir": null,
       "bid": "NaN",
       "ask": "NaN",
       "vol": "0",
       "openInt": "486"
      }
    }
]

Summary

The script is available here. Although the python script provides quick and easy access to a bunch of finance data, it negates many of the benefits of using YQL in the first place; namely, you can’t add your own conditions to the where clause, do joins, or apply aggregate operators. I’ll write a post sometime in the future on some of the cool stuff you can do with YQL.

Comments

  1. Jeremy says:

    Hey, love the script. For my purposes, I need to dig back a bit into historical financial news feeds. I can’t seem to find this anywhere in the YQL tables. Is there a way to specify date ranges to query the old yahoo finance RSS news?

    Not sure if you’re keeping up with this blog or not, but I hope so!

  2. gurch says:

    Thanks Jeremy. While you can specify date ranges using simple regex (pubDate LIKE ‘%10 Oct 2010%’), I don’t know of any way to get old articles from Yahoo Finance. That being said, you could give other sources a shot.

    For example,
    http://newswire.ca/en/search?N=11&Nf=Epoch|BTWN%201312256067%201319427267
    is an rss feed which has filters right in the url and, afaik, you can grab any date range you want.

  3. sword says:

    Thahks,I want to build a stock website with django,how to use this app in django?

  4. Kingpin says:

    Hi, very interesting indeed, but it seems like we don’t have access to historical data for the FOREX values…

  5. Titus says:

    Hey awesome post!
    I was wondering if your script also looks up a symbol for a company, for example if I wanted the stock symbol for Apple, it would return AAPL…

  6. gurch says:

    I’m unaware of any way to do that with yql. You can usually download company listings straight off of an exchanges website. You can get NASDAQ,NYSE, and AMEX company listings here. You could use YQL to parse the CSV off that site – if you get it working please make a pull request!