Get Option Prices in Excel with Easy to Use Excel formulas

Use MarketXLS to stream real time or delayed live Pricing (Last, Bid, Ask and so on) for getting Stock Option Pricing in Excel. You will need any version of plan of MarketXLS along with a data subscription for Options Pricing. 

This article describes various commonly used Options Pricing functions with Quotemedia’s options Data/prices and historical option pricing in Excel. 

1. To Get all option chain of a stock symbol

=QM_List(“getOptionChain”,”Symbol”,”MSFT“) or =qm_getOptionChain(“MSFT“)

2. To Get all option chain of a stock symbol that are in the Money

To get all the option contracts use the following function

=QM_List(“getOptionChain”,”Symbol”,”FDX“,”money”,”In”)

3. To get an option symbol 

=OptionSymbol(“MSFT“,”2020-08-21″,”Call”,200) 

4. To get the last option price of an option symbol

=QM_Last(“@MSFT  180629P00090000″) or =QM_Last(“MSFT180629P00090000″) or =QM_Stream_last(“Option Symbol”). You can also use qm_stream_ask, qm_stream_bid and many more real time function to stream real time prices. 

5. To get the expiry date of a contract =QM_ExpiryDate(“@MSFT  180629P00090500″) or=QM_ExpiryDate(“MSFT180629P00090000″)

6. To see if the contract is a call or a put=QM_CallPut(“@MSFT  180629P00090500″)

7. To extract the strike price of an option

=QM_Strike(“@MSFT  180629P00090500″)

8. To get the highest price of an options contract =QM_ContractHigh(“@MSFT  180629P00090500″)

9. To get the lowest price of an options contract =QM_ContractLow(“@MSFT  180629P00090500″)10. To get the Open Interest for an options contract 

=QM_OpenInterest(“@MSFT  180629P00090500″)

11. To get the Real time option greeks for an options contract 

=qm_stream_alpha(“@MSFT  180629P00090500″)

=qm_stream_beta(“@MSFT  180629P00090500″)

=qm_stream_gamma(“@MSFT  180629P00090500″)

12. To get the option chains 

=QM_GetOptionChain(“Stock Symbol”)Gets all available option contracts for stock, including the expired ones.=QM_GetOptionChainAtTheMoney(“Stock Symbol”)Gets all available ‘at the money’ option contracts=QM_GetOptionChainInTheMoney(“Stock Symbol”)Gets all available ‘in the money’ option contracts=QM_GetOptionChainMonthlies(“Stock Symbol”)Gets all available monthly options=QM_GetOptionChainWeekly(“Stock Symbol”)Gets all available weekly options=QM_GetOptionNearTerm(“Stock Symbol”)Gets all available expiring most near term=QM_GetOptionStructures(“Stock Symbol”)Gets all available calls to the left and puts to the right, with Expiry in the middle=QM_GetOptionChainExpireMaximum (“Stock Symbol”, OnDate -“YYYY-MM-DD”)Gets all available options that expire at the maximum “OnDate”=QM_GetOptionChainExpireMinimum (“Stock Symbol”, OnDate -“YYYY-MM-DD”)Gets all available options that expire at the minimum “OnDate”

12. Get the End of the Day History of a specific Option Contract

=QM_getHistory(“Option Symbol”)

13. Get the Intraday Live History of a specific Option Contract

=QM_getIntradayHistory(“Option Symbol”)

14. Scan for options

With MarketXLS Option scanner, you can select a few stocks (up to 50 stocks at a time) and load all the option contracts for these symbols into an easy to use screener.

15. Option Profit Calculator

Use our Profit Calculator template to analyse your option strategies. Get payoffs until the expiry on each day. Rank option contracts with your own crieteria. Learn more about our Options calculator here.

16. Option Filters

To get all options for AAL which are expiring between July 19th 2020 and September 18th 2020 use like below.=QM_List(“getOptionchain”,”symbol”,”AAL“,”expireMin”,”2020-07-19″,”expireMax”,”2020-09-18″)

To get all options for AAL which are expiring between July 19th 2020 and September 18th 2020 use like below where the minimum strike price is 12 try.=QM_List(“getOptionchain”,”symbol”,”AAL“,”expireMin”,”2020-07-19″,”expireMax”,”2020-09-18″,”strikeMin”,12)

To get all options for AAL which are expiring between July 19th 2020 and September 18th 2020 use like below where the minimum strike price is 12 and the maximum is 14 try.=QM_List(“getOptionchain”,”symbol”,”AAL“,”expireMin”,”2020-07-19″,”expireMax”,”2020-09-18″,”strikeMin”,12,”strikeMax”,14)

To get all options for AAL which are expiring between July 19th 2020 and September 18th 2020 use like below where the minimum strike price is 12 and the maximum is 14 try. And also there should be no zero volume contracts and only yet to expire options.

=QM_List(“getOptionchain”,”symbol”,”AAL“,”expireMin”,”2020-07-19″,”expireMax”,”2020-09-18″,”strikeMin”,12,”strikeMax”,14,”inclExpired”,”false”,”inclZeroVol”,”false”)