# Making a Stock Options Database in Mathematica

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

• @Anon thank you for your detailed response. The function you provided is very helpful and significantly faster than mine. The one thing I really liked about my function though is that you don't have to specify a date or a type, it just groups together all existing options that pertain to a stock. Is there a way you can tell YQL to give you everything so that you don't have to enter an expiration date or whether the option is a call or a put? – John Smith Jul 4 '13 at 21:33
• I'm not sure exactly what date functions YQL has if any, but what you can do is just give a year and it will select all options for that year. Also you could use IN and a list of years. As for type, you can use OR I think: SELECT * FROM yahoo.finance.option_chain WHERE symbol='GOOG' AND expiration=2013 AND (type='C' OR type='P'). However, you cannot omit either symbol, expiration or type. They are all required. – C. E. Jul 4 '13 at 21:52

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.

• Your code is (somehow) not working for me... there is an undefined part of the code (optionsList) which returns an error message for me... Am I forgetting something? – Rod Jul 4 '13 at 12:37
• @RodLm Thanks for pointing that out, fixed it! – C. E. Jul 4 '13 at 14:26
• There is another error in your code. Please see that inside the first Import[] the ticker MSFT is fixed! So everytime you look for the options chain of any symbol you get it only for MSFT! – Rod Jul 4 '13 at 14:56
• This is easy to solve... just replace the first Import[] by 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"]. – Rod Jul 4 '13 at 15:05
• @RodLm Does that work for you? I'm still having problems, of a different kind though. But try for example YHOO or AAPL or GOOG. – C. E. Jul 4 '13 at 15:10