MSN Money Options

MSN Money Options

Usage

Excel formula for using option codes:

=RTD("gartle.rtd",,"MsnMoneyOptions","<Option Code>","<Data Field>")

Option code format:

[.]<Option Symbol><Expiration Date><Option Type Char><Strike>

Expiration date format:

yymmdd

Option type char: C or P

Strike format:

8 digits with leading zero as strike * 1000 (Google Finance format)
7 digits with leading zero as strike * 100
6 digits with leading zero as strike * 10
strike as is with or without decimal part (common strike format)

Examples:

=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO170120C00050000","OpenInt") 
=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO170120C00050","OpenInt")
=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO170120C50","OpenInt")
=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO170120C50.0","OpenInt")
=RTD("gartle.rtd",,"MsnMoneyOptions",".YHOO170120C50.0","OpenInt") (Thinkorswim format)

Excel formula for using option specifications:

=RTD("gartle.rtd",,"MsnMoneyOptions","<Option Symbol>",<Expiration Date>,<Strike>,"<Option Type>","<Data Field>")

Expiration date formats:

yymmdd
yyyy-mm-dd
mmmdd,yy
mmmdd'yy

Option type format: C or CALL, P or PUT

Examples:

=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO", "170120", 50, "CALL","OpenInt")
=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO", "2017-01-20", 50, "C","OpenInt")
=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO", "JAN20,17", 50, "PUT","OpenInt")
=RTD("gartle.rtd",,"MsnMoneyOptions","YHOO", "JAN20'17", 50, "P","OpenInt")

Use https://www.msn.com/en-us/money to find option codes. Options data is available for US exchanges and can be not available for other world's exchanges.

You can use MSN Money stock codes like fi-126.1.AAPL.NAS as a symbol. You can copy such codes directly from the MSN Money URLs.

Important Notes

Do not request the MSN Money website frequently. Otherwise, your IP can be banned by the MSN Money website.

RealTimeToExcel loads one page to get the data for all options of the one expiration date. This feature significantly reduces amount of requests to the MSN Money website.

Data Fields and Excel Formulas

Formulas for getting data by option codes:

Excel ColumnExcel Formula
CodeAAPL170120C00100000
OptionCode=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"OptionCode")
Symbol=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Symbol")
OptionSymbol=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"OptionSymbol")
ExpDate=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Exp")
Strike=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Strike")
Type=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Type")
Last=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Last")
Change=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Change")
ChangeInPercent=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"ChangeInPercent")
ChangePercent=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"ChangePercent")
PercentChange=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"PercentChange")
TimeValue=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"TimeValue")
Mark=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Mark")
Bid=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Bid")
Ask=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Ask")
Volume=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Volume")
OpenInt=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"OpenInt")
Strike~0=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Strike~0")
Strike+0=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Strike+0")
Strike-0=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Strike-0")
Strike+1=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Strike+1")
Strike-1=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"Strike-1")
rtd_LastError=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"rtd_LastError")
rtd_LastMessage=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"rtd_LastMessage")
rtd_LastUpdate=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"rtd_LastUpdate")
rtd_LastUpdateDate=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"rtd_LastUpdateDate")
rtd_LastUpdateTime=RTD("gartle.rtd",,"MsnMoneyOptions",[Code],"rtd_LastUpdateTime")

Formulas for getting data by option contract specifications:

Excel ColumnExcel Formula
OptionSymbolAAPL
ExpDate170120
Strike100
TypeCALL
OptionCode=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"OptionCode")
Symbol=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Symbol")
Last=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Last")
Change=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Change")
ChangeInPercent=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"ChangeInPercent")
ChangePercent=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"ChangePercent")
PercentChange=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"PercentChange")
TimeValue=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"TimeValue")
Mark=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Mark")
Bid=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Bid")
Ask=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Ask")
Volume=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Volume")
OpenInt=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"OpenInt")
Strike~0=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike~0")
Strike+0=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike+0")
Strike-0=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike-0")
Strike+1=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike+1")
Strike-1=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike-1")
rtd_LastError=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastError")
rtd_LastMessage=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastMessage")
rtd_LastUpdate=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastUpdate")
rtd_LastUpdateDate=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastUpdateDate")
rtd_LastUpdateTime=RTD("gartle.rtd",,"MsnMoneyOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastUpdateTime")

Symbol, OptionSymbol, Exp, Strike, and Type are calculated by parsing the option code.

OptionCode is the option code in the standard OCC format.

OptionSymbol ends with 7 for Mini Options, and with 1 for some options before splits.

The ChangeInPercent, ChangePercent, and Percent values are calculated by RealTimeToExcel and are the same. The Mark value is calculated as (Bid+Ask)/2.

Use Strike~0 to find the ATM strike, Strike+0 or Strike-0 to find equal or the nearest strikes, and Strike+1 and Strike-1 to find the nearest strikes above and below the strike.

See Copying formulas about inserting formulas into Excel spreadsheets.

Data Provider Settings

The data provider is configured to loading data every hour when the Excel workbook is open.

See Data provider settings.

ParameterValue
RefreshInterval01:00:00, one hour
NextRequestDelay1000, one second
DataStartTime00:00:00 -05:00
DataEndTime24:00:00 -05:00
LoadEndTime24:00:00 -05:00
LoadOnSaturday1, loading Friday's data once.
LoadOnSunday1, loading Friday's data once.

Data Source

The MSN Money website.

Example:

https://www.msn.com/en-us/money/stockdetails/options/fi-126.1.AAPL.NAS