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. 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) Getting all option chains for a stocks The pro version of MarketXLS allows you to get even more option pricing in excel functions. With pro version of MarketXLS you can also get all stock option chain for a specific stock. To do this simply select a cell which has the name of the symbol in it and click on utilities and Option chain as shown below: With is, you have all the important option pricing in excel functions in your spreadsheet.

Sorry, but the explanation is confusing at the beginning. 1) Why use FOX for a Google option, why not the actual symbol (GOOG)? It would be much clearer. 2) Don't say the first 4 symbols are FOX (perhaps you were thinking of GOOG - which does have 4 symbols?). The rest is pretty clear. However, it does not work for me. I've been through the instructions above many times and am quite sure there is nothing wrong with my argument. I've also tried with and without quotes (above you do not use quotes), but it gives me the #VALUE! error without quotes. I am looking for the BABA Jan 19 of 2019 Call @190. Here is the argument I pass "BABA190119C00190000" This is the output in the cell: N/A For completeness sake here is the entire line in the formula bar: =Option_Last_Price("BABA190119C00190000") Interestingly, I put just "BABA" as the argument and it comes back with the last price of BABA. What is wrong with this? Is it me or is there a problem with the function? Skip

Hi Skip, =Option_Last_Price("BABA190119C00190000") this is how it should work. So, if the option is valid it should return the price. However, as you may have noticed recently due to some changes in the feed we are having some issues with existing feed. So, we looking to integrate option pricing via our new integration with Quotemedia. This is almost done, I will keep you posted on email about the changes. We are looking to make this a minimum or no change for you as much as we can. Kind regards Ankur

utilitities option function has not been working for 3 weeks, when will it be fixed? I use it regularly.

Hi, We are working with Quotemedia for new option feed to resolve all Option functions. Thank you for your patience. Kind regards Ankur

We have added a =OptionSymbol function in this release which can get you the correct option symbol to use when you provide the parameters like expiry, stock symbol, option type and strike price as shown below. =OptionSymbol("msft","2019-12-20","Call",70) will return "@MSFT 191011C00100000" We also have created a function for MarketXLS to understand option symbology as used in other popular platforms. The following platforms' option symbols can be recognized and converted into MarketXLS option format. These symbols can also be used straightaway. The platform codes to use in the convert functions are as below. cs = Charles Shwab, es = eSIGNAL, yf = Yahoo Finance, qm = Quotemedia, ts = Trade Station, td = TD Ameritrade, ms = MetaStock. So, to convert an option symbol from cs platform, you can use the function like =OptionSymbolConvert("Option Symbol as in cs","cs","qm") *note we are not affiliated/or have any relationship with any of the above except Quotemedia. This function is only to make it easier for you to save time while getting option prices with correct symbols.

I'm having problems pulling QM option prices into my spread sheet. I am using the function, =QM_Last(), with the argument @QQQ 050531C00430000. NA is returned. I get the same result with any symbol string. Using version 9.2.0.0, and stock and mutual fund prices download properly.