YQL Options expiration

Question

I'm migrating some Yahoo Finance CSV/screen-scraping interfaces to use YQL, and struggling with the yahoo.finance.options table. If I query for all options for a given symbol, I don't find the expiration dates associated with the options. If I query for symbol and expiration, then I find the expiration date associated with the chain, but not the options therein. While I'm familiar with the cycle of options expiration and can bootstrap it from a given date, that's a poor solution; for one thing, it will generate more queries. I'd much prefer to introspect it from the data, since it should be available (it can be screen-scraped). Anybody know how to get at this in YQL, or am I out of luck?

Here's some python code I'm using:

from xml.etree.ElementTree import ElementTree
import urllib, urllib2

class YQL(object):
    url = 'http://query.yahooapis.com/v1/public/yql'
    env = 'store://datatables.org/alltableswithkeys'
    format = 'xml'

    @classmethod
    def query(cls, string):
        q = urllib.quote(string)
        url = cls.url + '&'.join(('?q=%s' % q, 'env=%s' % cls.env,
                                   'format=%s' % cls.format))
        resp = urllib2.urlopen(url)
        return ElementTree(file=resp).getroot().find('results')[:]

chain = YQL.query('select * from yahoo.finance.options where symbol="WFC"')[0]
chain.attrib
option = chain[0]
option.attrib
for attr in option:
    print attr.tag, attr.text

score 4 · Answer 1 · answered Feb 02 '12 at 05:10

You can further take advantage of YQL's ability to chain queries with

SELECT * FROM yahoo.finance.options WHERE symbol="%s" AND expiration in (SELECT contract FROM yahoo.finance.option_contracts WHERE symbol="%s")

Where %s is the symbol you're seeking, obviously. This will pull all option chains from all available expiration dates, and save you several queries.

score 1 · Answer 2 · answered Aug 19 '12 at 00:13

If you don't set the expiration date in the YQL query, then I think the data set will be returned for the contracts expiring soonest this month (but later than today). Those are not necessarily the monthly options, expiring on third Friday. The returned dataset could be for weeklies or quartelies options expiring this month (if your underlying has ones). Even if you issue the query a few days after expiration of the monthlies (3-rd Friday) there is a chance that you'll get data for something expiring this month rather than next month monthlies.

So, as unutbu suggested, it makes sense always issuing queries with expiration month specified to receive expiration date in the returned data set, and know exactly what you are getting. Or even better, with expiration date specified.

Too bad that querying the yahoo.finance.option_contracts table does not return expiration date, just the month. And it does not help finding out if your underlying has options other than monthlies, and their expiration dates.

unutbu · Answer 3 · 2011-06-26T14:41:30.243

If you don't set the expiration date in the YQL query, then I think the expiration date of the data set will be the upcoming third Friday of the month. Using dateutil, you could define this date in Python with:

import dateutil.relativedelta as relativedelta
import dateutil.rrule as rrule
import datetime as dt

expiration=rrule.rrule(
    rrule.MONTHLY,
    byweekday=(relativedelta.FR(3)), dtstart=dt.datetime.now())[0]

(Note: the above code ignores holidays, in which case the expiration date would be the third Thursday.... If you use this code, be sure to also check what Yahoo decides to return when the current day is the third Friday of the month -- I'm not sure if expiration date will be the current date, or the third Friday of the next month.)

To see the option chain for a particular expiration year/month (other than the upcoming third Friday), you might use a YQL query such as:

chain = YQL.query('''
    select * from yahoo.finance.options
    where symbol="WFC" and expiration="2011-08"''')[0]

It is possible to obtain data on multiple expirations all in one YQL query:

chains = YQL.query('''
    select * from yahoo.finance.options
    where symbol="WFC" and (
        expiration="2011-08" or
        expiration="2011-10" or
        expiration="2012-01"
        )
    ''')

Interestingly, when data on multiple expirations is requested, the chain.attrib does include an expiration key:

for chain in chains:
    print(chain.attrib)
    # for option in chain:
    #     print(option.attrib)
    #     for attr in option:
    #         print attr.tag, attr.text
    # print('-'*80)

yields

{'symbol': 'WFC', 'expiration': '2011-08-19'}
{'symbol': 'WFC', 'expiration': '2011-10-21'}
{'symbol': 'WFC', 'expiration': '2012-01-20'}

That's what I'm afraid of, but my initial suspicion is that I'm missing something, not that the table data model has an obvious flaw (making expiration date an optional input, yet not returning it anywhere in the query result). — csingley, Jun 25 '11 at 13:10
thanks unutbu, that's helpful. it makes sense that the expiration would default to the front month. however, if this interface needs a separate query for each expiration, and it requires one to discover the available expirations out of band (e.g. whether or not LEAPS are available for the back years), then I suppose I'll stick to screen-scraping the Y! Finance options pages for the time being. — csingley, Jun 25 '11 at 14:51
How do you this for the options data. Not only the expirations. Can it be used? — Merlin, Oct 24 '14 at 15:27

Andy Novocin · Answer 4 · 2011-10-12T21:13:26.847

In order to get the list of available contract dates use the yahoo.finance.option_contracts. To continue your code (which I appreciated) you could print all possible put options like this:

xml_dates = YQL.query('select * from yahoo.finance.option_contracts where ' + 
                                                         'symbol="HYG"')[0]
dates = []
for attr in xml_dates:
    print attr.tag, attr.text
    dates.append(attr.text)

for expiration in dates:
    xml_contracts = YQL.query('select * from yahoo.finance.options where '
                             +'symbol="HYG" AND expiration="' + expiration +'"')
    for option in xml_contracts[0]:
        if (option.attrib['type']=='P'):
            print 'Put: strike=' + option.findtext('strikePrice')+ ', lowball '
                      + 'ask=' + option.findtext('ask') + ', date='+ expiration

YQL Options expiration

4 Answers4