Good Data for Options "Greeks"?

[Gregg88 10]

Hey Everyone,

 

Since I'm trying to paper-trade an Options strategy that involves analyzing the "Greeks" (Delta, Gamma, etc.), I'm curious if anyone knew of any good tools for retrieving, searching, and analyzing the relevant data: especially if it's free!

 

You can find data on Options Chains everywhere, but I could only find data on the Greeks from the Nasdaq website (here's an example for SPY: http://www.nasdaq.com/symbol/spy/option-chain/greeks). Unfortunately, it doesn't allow me to go directly to a particular date; instead it lists out all the strike prices for every Friday in a particular month across several "pages". In other words, if I want to find some data on options that expire -say- June 19, 2015, I'm greeted with a bunch of June 5th options on sheet "1 of 10" and I kinda need to guess which sheet has the data for June 19th. Feel free to check out the link to see what I mean.

 

My initial solution was to import the data from each individual webpage to a spreadsheet, which is actually pretty easy to do with the "importHTML" function, then I could "massage" the data a little bit and find the appropriate data with a simple search (i.e. search "6/19/2015"). In fact, I made a rudimentary spreadsheet in Google Drive that brings up the Greeks from the Nasdaq website when you enter a ticker symbol, you can check it out with the following link and then clicking "File" > "Make a Copy" (https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing).

 

I might make some more updates in the future, for example I might have the spreadsheet ONLY display data for a particular date. In the meantime, though, I hope this helps you all with your Options Trading =)

 

Oh, and of course, feedback & suggestions would be fantastic! In fact, feel free to copy & modify my work: I just request that you share your contributions with the community

[Gregg88 10]

Hey everyone,

 

As a bit of an update, I was able to get the spreadsheet to display the Greeks for a particular Options date (You just need to enter the ticker symbol and the date of a particular Friday next month).

 

It isn't perfect, of course: I've found that the spreadsheet can't yet take in enough strike prices for some of the more popular securities (it can't handle SPY as of now, for example). Moreover, entering any date other than a Friday of next month will result in errors. Finally, it should be noted that it takes about 30 seconds for the spreadsheet to load up the new data whenever the ticker symbol is changed: leaving a bunch of cells that read "N/A" in the meantime.

 

That said, hopefully this helps people obtain the appropriate Greeks in a more timely (and economic) manner. Feel free to tweak and make suggestions to your liking, Woo-hoo!

 

As before, here is the link to the spreadsheet: https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing. As a reminder, you can only use the spreadsheet after you've made a copy to your Google Drive (click "File" > "Make a Copy").

[Gregg88 10]

Another Update,

 

I increased the "capacity" of the spreadsheet to take in the Greeks at more dates & strike prices for a particular month (in other words, I accommodated more "pages" from the Nasdaq website). I might go through the same process for the strike prices, but I'm not sure of too many other upgrades I might consider as I've pretty much found an easy way of getting the info I want.

 

That said, I'm always open to comments. What do you all think?

 

(As always, here's the link: https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing)

[Gregg88 10]

A few things I changed from yesterday:

 

I brought in data for both the Options Chain (Bid/Ask Price, Volume, etc.) and synthesized that data with the Greeks. While I previously had to travel to multiple pages to -say- find the price of a Call Option with a Delta value of 0.09, I can now just look at a single table. In fact, if you are hoping to set up a spreadsheet that automatically calculates your possible profit from a spread trade based on certain "Greeks" criteria, I hope this can save you a bit of time.

 

It should be noted that you can re-arrange the table as you see fit as I've "locked" the referenced columns. In other words, if you wanted to put the Strike Prices in the first column, you could simple copy cells P6 to P68 and paste it to A6 to A68.

 

Note also that you can check out the "Guts" to the spreadsheet by scrolling down (to see where we "Pull In" and "Organize" the data) and scrolling to the right (where we include some of the "logic" needed to identify the appropriate data). In fact, in cell "AL5" you'll find a variable called the "Date Index" (a name I derived from the Nasdaq URL): this variable allows you to adjust which month you'd like the spreadsheet to derive data from (0 = "This Month", 1 = "Next Month", etc.).

 

From here I might consider adding a few searches that bring up relevant data for Spread Trades (i.e. "Condors": selling an out-of-the-money option and buying an even FURTHER out-of-the-money option). Say you want your "short" (sold) option to have a particular delta, and you want your "long" (bought) option to have a strike price $XX more out-of-the-money. What strike prices should you choose for your Call and/or Put Spreads? How much money would you be risking? What kind of premium (max profit) could you expect to make?

 

I COULD pay for services that do this for me, or I could flip between web pages to find each relevant datapoint bit-by-bit... but if someone wants to start by paper trading (i.e. "practice"), it would be nice to not have to cough up a bunch of money to some data-steaming service.

 

Anyways, I'll get off my soap box =P Naturally, the spreadsheet certainly isn't perfect. For example, with most ticker symbols you'll notice that the spreadsheet lists the available strike prices at the relevant data, inserts a few blank spaces, and then repeats a few of the already-listed strike prices. This flaw ultimately stems from my effort to "accommodate" more data (some stocks & ETFs have more available strike prices than others): thus, removing the "repeated" strike prices may result in "cutting off" some strike prices from a different ticker symbol. I personally don't know how to fix this issue, but it doesn't seem too distracting for me (it's pretty obvious when data is "repeated"). If you know a fix to this issue, let me know =)

 

As I do for all my posts, here's the link: https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing . Thanks, folks, and take care!

[Gregg88 10]

Just a quick update. When I started the process of "searching" for the appropriate strike price for a given expiration data and Delta value, I found that it wasn't functioning properly due to the "repeated data" problem I described in an earlier post. I added a bit more logic to the "data massaging" stages of the spreadsheet, which basically amounts to telling the spreadsheet how to identify & exclude the repeated data.

 

In layman's terms, you shouldn't see a few strike prices re-listed near the bottom of the output data (after a few blank rows). I guess now I can finally start working on the so-called "end-game": getting the spreadsheet to give me the recommended strike prices for an Iron Condor trade (given an inputted expiration data and Delta values).

 

Feel free to copy and modify the spreadsheet as you see fit, or just sit back and watch for updates (https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing) Good luck, folks =)

[Gregg88 10]

I added a rudimentary "Iron Condor" analysis tool to the spreadsheet (see the "Condor Analysis" sheet after clicking on the link below).

 

Basically, I've allowed the user to enter the "Difference" in strike price between the Long & Short position for each "side" of the Iron Condor trade (i.e. selling an out-of-the-money Call & Put Option, and buying an even further out-of-the-money Call & Put: pocketing the difference). The user can also enter the "Delta" for the short position (sort of an indicator of an option's "riskiness") and the total number of contracts for each "Leg".

 

There's a lot of output Data so I'll be thinking of how to make it more streamlined, but in the mean time I've Bolded what I believe to be the "important" output data (along with adding a yellow background to the input data cells).

 

I'd like to account for commissions in my next update, along with the ability to calculate for several Long/Short strike-price spreads. Moreover, I'm also thinking about calculating some recommended "conditional orders" (exit criteria) to keep losses to a minimum if the trade goes against me.

 

As always, hope this helps =) https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing

[Gregg88 10]

I actually wanted to share some (hopefully constructive) criticism for my spreadsheet. On issue I have with Google Spreadsheets is the fact that you can't give a cell range (or an individual cell) a unique "name" as you can in Microsoft Excel, which makes it much harder to update and troubleshoot a somewhat complex "calculator" like this one.

 

Here's an example, say I want to expand the maximum number of web pages that my spreadsheet looks through (some securities might have a greater number of available strike prices, so the data requires more "pages" to display it all). Adding a few more rows and equations to retrieve the data is easy enough, but then I need to make sure that ALL of my other calculations account for this new data. If I could simply re-define the range named -say- "Call_Option_Bid_Price", then ALL of the formulas that search through this data will be updated automatically. Instead, I need to update all of these formulas manually (change "B7:B54" to "B7:B101", for example).

 

Although I'll continue working on my spreadsheet, I may eventually opt to continue in Microsoft Excel for the reason listed above. Thankfully, I think there's an option to "Convert" a spreadsheet from Excel to a Google Spreadsheet, so I can still share my progress relatively easily.

 

I do, however, have a quick question for you all. What would make this spreadsheet easier to use in the long-run? I hope I've made it clear that I'm VERY new to Options (I've never even traded a contract, in fact), and I figured that most of you have a lot more expertise in this area than I do. Any constructive criticism?

[Gregg88 10]

Just as a quick update, I noticed another bug I'm hoping to iron out. It seems that I mis-interpreted the part of the URL labeled "Date Index". I thought the number 1 represented "next month", but this doesn't seem to be the case until we're looking at the web page near the middle of the month. In other words, if I'm looking at the Nasdaq web page on June 3rd, the "Date Index" of value 1 still corresponds to June.

 

What I'm planning to do is instead just bring in the data for ALL available expiration dates (Date Index value of "-1"). Hopefully this will make the spreadsheet work more consistently.

 

Otherwise, I notice that the spreadsheet has more trouble looking up the data from some symbols than others. For example, you can bring up the data for the Russell 2000 (RUT) just fine, but I get a bunch of error messages if I do the same for the S&P 500 (SPX). I checked out the applicable URLs and the web pages worked fine, but I did notice they were pretty slow to load up. I wonder if the Nasdaq servers are just more dedicated to providing resources to some ticker symbols over others =/

 

Anyways, I'll resist sharing the link again until I've made the update. I might also split the data into another worksheet, too (one for "Greeks", one for the "Options Chain", and one that synthesizes the data of both). I'll get back to ya'll soon =)

[Gregg88 10]

Hey Everyone,

 

As a bit of an update, I've "expanded" the spreadsheet to bring in data for ALL available expiration dates for a particular ticker symbol's stock options. I had to do a few "tricks" to "massage" the data (removing the occasional blank line, etc.), but you can feel free to check out the "mechanics" of the spreadsheet on the link below.

 

As I've mentioned before, I notice that the spreadsheet has less "trouble" bringing in data for some ticker symbols than others. For example: it may take a LOT less time to bring in data for -say- Tesla Motors (TSLA) instead of the S&P 500 (SPX). Plus, I find the calculator somewhat inconsistent: sometimes it will pull up all the data in a matter of seconds, and sometimes it will "struggle" for 5 minutes and simply return an error message (even if the link works just fine). I'm guessing this has to do with the available bandwidth from the Nasdaq's servers during different times of the day. I'm guessing there's a "you get what you pay for" sort of lesson here =P

 

Regardless, I figure this spreadsheet would NOT be used for particularly time-sensitive trades. I'm 99% sure the quotes are delayed by at least 15 minutes, so I'd think this spreadsheet would only be suitable for a simpler, "trade once per month" kinds of strategies. In fact, I pretty much designed it around so-called "conservative" Iron Condors.

 

As always, you can check out my latest iteration at the following link: https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing. Remember to make a copy for yourself if you want to "play" with it (it's read-only otherwise).

 

As for my next update, I'm thinking it might be nice to account for trade commissions. Then later I might set up so-called "conditional" trades, which basically tell my broker to automatically close out my positions if any "exit conditions" are met (i.e. at what point should I "cut my losses" or "secure my profits", etc.)

 

I haven't gotten much feedback yet, but I'd love to hear some thoughts (particularly some constructive criticisms!). Thanks as always, everyone!

[Gregg88 10]

Hey everyone,

 

So I did a bit more "massaging" of the data to get rid of any "blank" lines (it can sometimes happen if all the strike prices for a particular expiration date end up on several different "pages"), but I think I have the issue of "messy data" solved for the time being.

 

Moreover, I also give the user to account for commissions in a hypothetical "Iron Condor" trade. I present the scenarios of either a) purchasing your options and letting them expire, or b) closing out of our options early (which adds more commissions since we make another trade).

 

I'm thinking that the next logical step is to include some "conditional" (exit) orders, along with a desired "Premium" between the Long & Short (we assume that the trader attempts to buy & sell in between the "Bid" and "Ask" quotes: aka the "Mark"). I might also re-iterate the security's name and current price (based on the ticker symbol). I hoe this helps!

 

And as always, here is the the link https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing Don't forget to make a copy if you want to play with the spreadsheet, as it will be "read only" otherwise =p

[Gregg88 10]

Hey All,

 

So I added the suggested "Long-Short Spread" to the spreadsheet in the "Iron Condor Analysis" sheet. Basically, the spreadsheet assumes that you want to use "Limit" orders to buy & sell options at prices in between the "Ask" price (the price you will pay by buying with a "Market" order) and the "Bid" price (the price you will be paid by selling with a "Market" order). The value smack dab in between the Bid & Ask is called the "Mark" price, and it's assumed that you have a reasonable chance of getting your trades filled if you use Limit orders at this price. In other words, if you say "You're selling this option for $2.85/share but you'll only pay $2.00? How about I give ya $2.43 for it", you might just get the price you want.

 

For "Spread" trades (selling an option and buying a further out-of-the-money option), it's my understanding that you can input a desired "Spread" in Limit orders (i.e. Sell and Buy these options from me at any price you want, so long as the difference between them is $1.10/share). Ergo, I've bolded the "Long-Short Spreads" for both the Call and Put sides of the Iron Condor.

 

Theoretically this is all you need for a "Set it and Forget it" Iron Condor strategy (you buy & sell the appropriate options and simply let them expire), but I might also want to input some conditional orders in case the trade "goes bad". After all, it's better to get out of a losing trade for a small loss instead of "holding out" at the risk of taking a HUGE loss. I guess that's what I'll try to program next, then =)

 

As always, here's the link: https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing

[Gregg88 10]

I was able to add some functionality to the spreadsheet in order to provide some "Conditional Orders" (i.e. close the respective leg of the Iron Condor if the price dips above XXX or below YYY, aka "Exit Criteria"), along with giving the user the ability to enter a few "pre-defined criteria" to the underlying security (did it change more than 5% in the past week? Would I get $0.40 or more from a 5-point spread? etc.).

 

I had to use the Greek value "Gamma" for calculating the "Exit Criteria", but unfortunately the value for Gamma can sometimes be so low that the feed from the Nasdaq website rounds it to zero! Ergo, I went ahead and tried to calculate Gamma (and a more "precise" version of Delta) using the excel formulas I found from the website "http://www.macroption.com/" (URL #1: Black-Scholes Excel Formulas and How to Create a Simple Option Pricing Spreadsheet, URL #2: Option Greeks Excel Formulas). Since a few of the necessary variables need to be "assumed", I should disclose that I used the 30-year U.S. Treasury bond rate for the "Risk-Free Interest Rate" and the Implied volatility for the "Volatility" (I can see why some people would prefer to use "Historical Volatility" instead).

 

As a reminder, the value "Delta" tells you how much the option price will change with a $1 price change in the Underlying security, and the "Gamma" tells us how much the DELTA will change with that same $1 price change. Therefore, we can estimate the price of the security that will change the Delta to a pre-defined "maximum allowable" value.

 

Otherwise, I also brought in some historical price data via the function "GoogleFinance": I did this to find the maximum % price change in the last Week, Month, and 3 Months. Theoretically, this data cal help the investor determine if the underlying security is stable enough to safely trade options.

 

I might think of new things to add in the future, but for now I'll just leave the link (remember to click "File" > "Make a Copy") and let you know if I think of anything. Thanks, everyone, and good luck! https://docs.google.com/spreadsheets/d/1X9VBq7TLUa0wreJG_6DOQAN7pz-k5lTBYhppty_EH24/edit?usp=sharing

[Gregg88 10]

Hey Everyone,

 

Just as an update, it looks like the Nasdaq website no longer provides Options Greeks for indices (S&P 500, Russell 2000, etc.). I was originally planning on doing Iron Condors on Index Options, does anyone know of another good source for Greeks data?

 

That said, it looks like you can still look up the Greeks for stocks, and even Indices still show the Implies Volatility (a key component for calculating many of the Greeks).

 

Hope this helps =)

 

Update: It looks like the solution to my earlier problem (not having the Options Greeks available) was to simply calculate the Delta and Gamma for myself based on the Implied Volatility. Any thoughts?

Edited July 14, 2015 by Gregg88