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

Answer 1

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.

Answer 2

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'}