Option Pricing with Quotemedia
Learn how to use various Options Pricing functions with Quotemedia's options data in Excel.
Common Options Functions
Get All Option Chains for a Stock
=QM_List("getOptionChain","Symbol","MSFT")
Get Last Price of an Option Symbol
MarketXLS internally understands both symbologies:
=QM_Last("@MSFT 180629P00090000")=QM_Last("MSFT180629P00090000")Quotemedia Option Symbol Structure
Example: 2011 Jan 22 $20.00 Call for Microsoft Corp.
Symbol: @MSFT 110122C00020000
@: All options begin with the "@" character
MSFT (with spaces): Six character option root symbol, left-justified, space-padded
11: Two least significant digits of contract year
01: Two-digit contract month
22: Two-digit contract day
C: "C" for CALL or "P" for PUT
00020: Five-digit whole portion of strike price (e.g., $26 = 00026)
000: Three-digit fractional portion (e.g., $26.50 = 500)
MarketXLS Also Supports Standard Format
Root Symbol + Expiration Year(yy) + Month(mm) + Day(dd) + Call/Put (C or P) + Strike Price
Get Expiry Date of a Contract
=QM_ExpiryDate("@MSFT 180629P00090500")=QM_ExpiryDate("MSFT180629P00090000")Check if Contract is Call or Put
=QM_CallPut("@MSFT 180629P00090500")Extract Strike Price
=QM_Strike("@MSFT 180629P00090500")Get Highest Price of Contract
=QM_ContractHigh("@MSFT 180629P00090500")Get Lowest Price of Contract
=QM_ContractLow("@MSFT 180629P00090500")Get Open Interest
=QM_OpenInterest("@MSFT 180629P00090500")Get In-the-Money Options
To get all option contracts that are In-the-Money:
=QM_List("getOptionChain","Symbol","FDX","money","In")