How Can We Help?
Option Pricing In Excel Functions
Just like all other functions of MarketXLS, option pricing functions also take symbol as an argument. These option pricing in excel functions allow you to get all options related data right into your excel sheet. Options data is only accessible using =QM_ functions and requires an additional fee with Quotemedia.
Please refer to this article that explains Options pricing with Quotemedia’s addon
Stock options symbols are made as follows:
Root Symbol + Expiration Year(yy) + Expiration Month(mm) + Expiration Day(dd) + Call/Put Indicator (C or P) + Strike Price Dollars + Strike Price Fraction of Dollars (which can include decimals)
Root Symbols
Ultimately all stock options will use the stock ticker symbol for the underlying stock itself as the root symbol for the option. So, with the symbol, you can get option pricing in excel just like you would get the stock data. While this change will be transparent to the majority of traders, it represents a massive and long overdue simplification in the system. Consider that at times, and including LEAP options, a stock trading in the $500 range could have 12 or more option root symbols. Moving forward, all Google options will simply use the same root symbol as the stock itself – FOX. So the first four characters for all Google options will be the letters FOX (FOX160318C00025000)
The Expiration Year
After the root symbol will come the year designation for the option. This will be done using a two-digit value. So all options that expire in 2016 will appear as 16, all options that expire in 2017 will appear as 17, and so forth. So the next two characters for all FOX options moving forward will be the year designation (FOX160318C00025000).
The Expiration Month and Date
Rather than simply designating the expiration month, option symbols will spell out the actual expiration date. While most stock options expire on the third Friday of the month, some index options expire a day earlier and some stocks and indexes now trade quarterly or monthly options that expire on the last trading day of the indicated period. Therefore, spelling out the expiration date long hand clearly delineates between the different types of options.
So the next four characters for all FOX options moving forward will be the actual option expiration date of March 18th (FOX160318C00025000).
Call or Put
The next character for all FOX options moving forward will be either a “C” or a “P” designating the option as a call or a put (FOX160318C00025000)
Strike Price Dollars
The next part of the option symbol is used to designate the round dollar portion of the option’s strike price. A five-character field is set aside for this. So an option with a strike price of $20 would read 00020, an option with a strike price of $22 would read 000022 and an option with a strike price of $500.00 would read 00500 in this field.
So for the FOX 25 call the five characters after the letter “C” would designate the dollar portion of the strike price (FOX160318C00025000)
Strike Price Fractions of Dollars
The last part of the option symbol is used to designate the fractions of a dollar included in the strike price. Three characters are set aside for this field. So an option with a strike price of 22.50, this field would read 500. Other cases may involve instances where a stock split has occurred and option strike prices have been split accordingly. For example, let’s assume that a stock had an option trading on it with a strike price of 50, and that the stock split 3 for 1. The strike price of 50 would be divided by 3 and would from that point forward be the 16.667 strike.
So for the FOX 25 call the five characters would simply be all zeros indicating that there is no fraction of dollars in the strike price (FOX160318C00025000)
With is, you have all the important option pricing in excel functions in your spreadsheet.
