Documentation for smfGetOptionQuotes() function


Description

    Returns delayed quotes and related data for options from a number of different data sources, including Yahoo or Google.
    Most other sources are no longer accessible.

Syntax

    =smfGetOptionQuotes( tickerlist, datacodelist, [headers], [source] )

Parameters

    tickerlist= A list of special format ticker symbols to get quotes and/or other data on options. This list can either be a single ticker symbol, a comma-delimited list of ticker symbols, a cell reference, or a range reference. The ticker symbols being used for this function are a special format of the add-in -- not ticker symbols used by the different data sources. Each ticker symbol consists of five strings with a space in between (e.g. "SPY Jun 2010 $100 Call"). The five strings are:

� Ticker symbol = The ticker symbol of the underlying equity (e.g. "MMM" or "SPY").
� Expiration Period = There are a number of choices for this string:

StringDescription
Jan thru DecMonth-end option expiration
1 thru 12Month-end option expiration
Q1 thru Q4Quarter-end option expiration
W or Week or W1 thru W7Week-end option expiration
M or M1 thru M5Relative month-end option expiration
m/dAn actual month and day option expiration (e.g. 9/10)

Note that week-end and quarter-end option expirations are only available for a limited number of equities.
� Expiration Year = A 4-digit expiration year (e.g. "2010").
� Strike Price = The desired strike price, either with or without a leading dollar sign. In addition, you can use values like "OTM1", "OTM2", "ITM1", or "ITM2" to get the first or second in-the-money or out-of-the-money strike prices (currently only available for Yahoo and MarketWatch data sources).
� Call/Put = A literal value of "Put" or "Call" to indicate the type of option data that is desired. Can be abbreviated to "P" or "C".

    datacodelist= A list of data codes telling indicating which data elements you want retrieved. This list can either be a string concatenation of all desired data codes, a cell reference, or a range reference. Here is a table indicating the data codes available from the various data sources:

CodeDescriptionData sources
(1)
Y
(3)
OX
(5)
OX2
(6)
Google
(7)
OX3
(8)
8
--
%% ChangeSlow----Fast--Fast--
aAsk PriceSlowSlowSlowFastSlowFast--
bBid PriceSlowSlowSlowFastSlowFast--
c$ ChangeSlow--SlowFastSlowFast--
eBid Size--------------
fAsk Size--------------
gDaily Low--------------
hDaily High--------------
iOpen InterestSlowSlow--FastSlowFast--
jContract Low--------------
kContract High--------------
lLast PriceSlowSlowSlowFastSlowFast--
oOpen PriceSlow------------
pPrevious CloseSlow------------
sStrike PriceFastSlowSlowFastSlowFast--
tLast Trade TimeSlow------------
uUnderlying Equity Price--SlowSlowFastSlowFast--
vVolumeSlow----FastSlowFast--
xExpiration DateSlowSlowSlowFastSlowFast--
yTime/Theo. Value--Slow----------
zOption SymbolFastSlowSlowFastSlowFast--
1Vega--Slow------Fast--
2Theta--Slow------Fast--
3Rho--Slow------Fast--
4Gamma--Slow------Fast--
5Delta--SlowSlow---- --
6Implied Volatility----Slow----Fast--
7Alpha----------Fast--

The difference between the "Fast" and "Slow" data items is that you can retrieve all of the "Fast" items for all of the different options in the same expiration month with a single Internet access, while the "Slow" data items require a new Internet access for each individual option for which the various items are collected.

 
    headers= An optional parameter that allows column headings to be generated for the returned columns of data. Defaults to 0. Set to 1 if you desire a row of headers to be displayed.

    source= An optional parameter that indicates the source you want to use for options data. Defaults to "Y". Possible values:

Value"DataCode" prefixData Source
G6Google
OX3OptionsXPress (Greeks of calls only, not puts)
OX25OptionsXPress (limited Put and Call data items, including delta and implied volatity)
OX37OptionsXPress (limited Put and Call data items, including volume and open interest)
Y1Yahoo
88888options.com (requires security cookie)
2--Multiple sources. If this value is passed, all datacodes will be 2-byte values, where the first byte is the "DataCode" prefix shown in this table.

Examples Usage notes
Updates

2015-02-21 = Obsoleted MSN and MarketWatch items
2015-02-21 = Struck out OTMx and ITMx reference. May be added back later.
2015-02-21 = Added W1 thru W7 and M1 thru M5 relative date references.
2016-08-05 = Dropped MSN and MW and added 8 (888options.com) as data sources.