Option Pricing In Excel Functions

Discussion in 'Formulae' started by marketxls, Apr 10, 2017.

Option Pricing In Excel Functions

  1. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    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:

    030816_1133_OptionPrici1[1].png

    With is, you have all the important option pricing in excel functions in your spreadsheet.
     
    #1 marketxls, Apr 10, 2017
    Last edited: Apr 10, 2017
  2. Skip Egley

    Skip Egley New Member

    Joined:
    Jul 28, 2017
    Messages:
    10
    Likes Received:
    0
    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
     
  3. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    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
     
  4. 277jah

    277jah New Member

    Joined:
    May 12, 2016
    Messages:
    13
    Likes Received:
    0
    utilitities option function has not been working for 3 weeks, when will it be fixed? I use it regularly.
     
  5. marketxls

    marketxls Administrator
    Staff Member

    Joined:
    Dec 21, 2015
    Messages:
    1,119
    Likes Received:
    10
    Hi,

    We are working with Quotemedia for new option feed to resolve all Option functions.

    Thank you for your patience.

    Kind regards
    Ankur
     
  6. 277jah

    277jah New Member

    Joined:
    May 12, 2016
    Messages:
    13
    Likes Received:
    0
    it is still not fixed 4 months later ?
     

Share This Page