Real Time Stock Option Pricing in Excel (Any version)

Get Stock Options Live Feed in Excel in 3 Simple Steps

01 Get MarketXLS

Any default package of MarketXLS listed at here support access to Option Pricing in Excel. Option Prices in MarketXLS come from our data partner Quotemedia.com. The Pro Plus package has the delayed option price built into the MarketXLS Price. The option pricing data bundle can also be purchased separately from here and can be used with Any MarketXLS Plan. 

02 Next Step

Once you buy Pro plus package or any package along with option's data bundle you will get a username and the password for your Quotemedia account.

03 Finally

Once, you have your username and password, it can be entered and saved into MarketXLS to start getting Options Prices and Option's historical data in Excel with MarketXLS like shown below.

Option Pricing in Excel with Quotemedia's Addon and 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")

These will return all "At the money" option contracts.

To get all the option contracts use the following function

=QM_List("getOptionChain","Symbol","FDX","money","All")

You should be able to use many Input parameters from structure the result the way you like. The Input parameters are listed at this link. You may need to sign up as a fresh support user and your Quotemedia username and password for the data access is different. 

2. To get the last price of an option symbol

=QM_Last("@MSFT  180629P00090000") or =QM_Last("MSFT180629P00090000")

MarketXLS internally is able to understand both symbologies as shown below...

Quotemedia's option Symbols are structured as follows...

 Example: 2011 Jan 22 20.00 Call for Microsoft Corp.
Symbol : @MSFT   110122C00020000
@ : All Options begin with the " @ " character.


MSFT (and two spaces) : Six character option root symbol using any prefix/suffix as defined by the OSI definition; left-justified, space-padded. In the case where the option root symbol is three characters, it will be followed by three spaces.

11 : two least significant digits of the Contract Date year

01 : two-digit Contract Date month

22 : two-digit Contract Date day of month

C : "C" for CALL or "P" for PUT

00020 : five-digit whole portion of the strike price; right-justified and padded with zeros on the left; for instance, if the strike price is $26.50, then this field would contain 00026

000 : three-digit fractional portion of the strike price; left-justified and padded with zeros on the right; for instance, if the strike price is $26.50, then this field would contain 500

MarketXLS also understands option symbols structured 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)

3. To get the expiry date of a contract
=QM_ExpiryDate("@MSFT  180629P00090500") 
or
=QM_ExpiryDate("MSFT180629P00090000")

4. To see if the contract is a call or a put
=QM_CallPut("@MSFT  180629P00090500")

5. To extract the strike price of an option

=QM_Strike("@MSFT  180629P00090500")

6. To get the highest price of an options contract
=QM_ContractHigh("@MSFT  180629P00090500")

7. To get the lowest price of an options contract
=QM_ContractLow("@MSFT  180629P00090500")

8. To get the Open Interest for an options contract 

=QM_OpenInterest("@MSFT  180629P00090500")

Please see the Video tutorials on Option Pricing with Excel

Get MarketXLS Pro Plus Today

Included in your plan

  • Get Option Pricing in Excel 
  • Get Option contract's full history in Excel
  • Reliable data source for your option data

Included in your plan

  • Upgrade to real time streaming of option prices if you like
  • Prices and history of underlying assets of your options
  • Simple pricing no surprises

Check out MarketXLS plans today