Making a Stock Options Database in Mathematica

Question

I am trying to use Mathematica to create a stock options database of sorts. That is I wish to write a function that imports the option chain of a given stock. Unfortunately Wolfram has yet to put stock options on the data server associated with FinancialData[] so I decided to obtain the necessary data from Yahoo Finance. Here is the function I wrote to do this:

rules = {"Jan" -> "01", "Feb" -> "02", "Mar" -> "03", "Apr" -> "04", 
"May" -> "05", "Jun" -> "06", "Jul" -> "07", "Aug" -> "08", 
"Sep" -> "09", "Oct" -> "10", "Nov" -> "11", "Dec" -> "12"};

acquireOptions[stock_String] := 
Module[{z = 
StringSplit[Import["http://finance.yahoo.com/q/op?s=" <> stock <>"+Options"]],index1, index2},
index1 = Position[z, "Expiration:"][[1, 1]] + 1;
index2 = Position[z, "Call"][[1, 1]] - 1;
ArrayFlatten[(Partition[(DeleteCases[z[[index1 ;; index2]], "|"] /. 
     rules), 2] /. {x_String, 
    y_String} :> {"20" <> y <> "-" <> x}) /. {x_String :> 
  Cases[Import[
    "http://finance.yahoo.com/q/op?s=" <> stock <> "&m=" <> x, 
    "Data"], {_?NumberQ, _String, __?NumberQ}, \[Infinity]]}]]

I should note that I had to study the structure of the Yahoo Finance website hard core to make this work. Although it is functional the problem I have is that it is simply too slow. Running this function for a single stock takes about 30 seconds. Lets assume there are 1000 stocks that have options that I would want to acquire (there are probably a lot more than 1000, I would literally want to be able to acquire every last option). This would take about 8 and a half hours which is just too long. So I'm interested in how I can do this more efficiently. If I used parallel table on my 8 core machine could I expect this to only take about 2 hours?

Besides for the code I've written, I saw that Yahoo has a query language YQL that can be used to pull data off their servers. I then saw that Mathematica has Database Link SQL operations. I don't know much about SQL/YQL but would this have been a better direction to go in? If so would anyone be able to show how to link Mathematica and YQL and provide an example where YQL is used in Mathematica to obtain options data?

Oh and in case anyone was wondering why I am doing this, it is for an investment model I'm working on.

Thanks

Answer 1

I implemented this function using YQL:

acquireOptions[stock_String, expiration_, type_] := 
Module[{options, list, data}, 
options = 
Cases[Import[
  "http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.option_chain%20WHERE%20symbol%3D'"
  <> stock <> "'%20AND%20expiration%3D'" 
  <> expiration <> "'%20and%20type%3D'" 
  <> type <> "'&format=json&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env&callback=", "JSON"] /. 
   Rule -> List, {"sym", x_} -> x, Infinity];
list = "\"" <> StringJoin @@ Riffle[options, "\"%2C%20\""] <> "\"";
data = Import["http://query.yahooapis.com/v1/public/yql?q=SELECT%20*%20FROM%20yahoo.finance.oquote%20WHERE%20symbol%20IN%20(" 
  <> list <> ")&format=json&diagnostics=true&env=http%3A%2F%2Fdatatables.org%2Falltables.env&callback=", "JSON"] /. Rule -> List;
Transpose[#][[2]] & /@ 
Cases[data, {"option", __}, Infinity][[1, 2, All]]]

For example,

acquireOptions["MSFT", "2013-07", "C"]//TableForm

yields a long list with all the option data for MSFT. It displays better here as a table:

If you want to experiment with YQL you should use the YQL console.

My function is optimized for one stock but you want to get data for several different stocks. The fastest way to do this is not to use this function on each stock, rather it's to rewrite the first YQL query the way my second query is written. Using the way my second query is written, with in, you may acquire the stock option symbols for many stocks at once. In principle what you want to do you should be able to do with only two HTTP calls (two Import). One that acquires all the stock option symbols, and one that acquires the data. Since most of your time was spent on waiting for HTTP responses, this should give you an incredible speed boost.

Here's an example of how to get stock option names for many stocks at once in YQL. The console will give you the URL that you need to use:

SELECT * FROM yahoo.finance.option_chain WHERE symbol in ("YHOO","AAPL","GOOG","MSFT") AND expiration='2013-07' and type='C'

If you're wondering which element is which in the list acquireOptions returns, you may consult the table above where the elements are in the right order. For your convenience, here's a list with the corresponding elements as well:

{"ask", "bid", "change", "expire", "open", "openInterest", "prevClose", "price", "strike", "sym", "volume"}

As a final note I will add that YQL is a wrapper for an underlying API and it might be even faster to use it directly, it is described here for stock data. But it's not official and I can't find a reference with the right symbols to use for finding stock option data. Sal Mangano finds stock option data this way, however, in the Mathematica Coobook and seems to have figured it all out. But he does not include enough information to do what you want; the link he provides for more information on the Yahoo Finance API is now broken.