Google Finance Options

Google Finance Options

Usage

Excel formula for using option codes:

=RTD("gartle.rtd",,"GoogleFinanceOptions","<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",,"GoogleFinanceOptions","YHOO170120C00050000","OpenInt") 
=RTD("gartle.rtd",,"GoogleFinanceOptions","YHOO170120C00050","OpenInt")
=RTD("gartle.rtd",,"GoogleFinanceOptions","YHOO170120C50","OpenInt")
=RTD("gartle.rtd",,"GoogleFinanceOptions","YHOO170120C50.0","OpenInt")
=RTD("gartle.rtd",,"GoogleFinanceOptions",".YHOO170120C50.0","OpenInt") (Thinkorswim format)

Excel formula for using option specifications:

=RTD("gartle.rtd",,"GoogleFinanceOptions","<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",,"GoogleFinanceOptions","YHOO", "170120", 50, "CALL","OpenInt")
=RTD("gartle.rtd",,"GoogleFinanceOptions","YHOO", "2017-01-20", 50, "C","OpenInt")
=RTD("gartle.rtd",,"GoogleFinanceOptions","YHOO", "JAN20,17", 50, "PUT","OpenInt")
=RTD("gartle.rtd",,"GoogleFinanceOptions","YHOO", "JAN20'17", 50, "P","OpenInt")

Use https://www.google.com/finance/ to find option codes. Options data is available for US exchanges and can be not available for other world's exchanges.

Important Notes

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

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

Data Fields and Excel Formulas

Formulas for getting data by option codes:

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

Formulas for getting data by option contract specifications:

Excel ColumnExcel Formula
OptionSymbolAAPL
ExpDate170120
Strike100
TypeCALL
OptionCode=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"OptionCode")
Symbol=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Symbol")
Last=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Last")
Change=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Change")
ChangeInPercent=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"ChangeInPercent")
ChangePercent=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"ChangePercent")
PercentChange=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"PercentChange")
Mark=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Mark")
Bid=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Bid")
Ask=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Ask")
Volume=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Volume")
OpenInt=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"OpenInt")
Strike~0=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike~0")
Strike+0=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike+0")
Strike-0=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike-0")
Strike+1=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike+1")
Strike-1=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"Strike-1")
rtd_LastError=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastError")
rtd_LastMessage=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastMessage")
rtd_LastUpdate=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastUpdate")
rtd_LastUpdateDate=RTD("gartle.rtd",,"GoogleFinanceOptions",[OptionSymbol],[ExpDate],[Strike],[Type],"rtd_LastUpdateDate")
rtd_LastUpdateTime=RTD("gartle.rtd",,"GoogleFinanceOptions",[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 Google Finance website.

Example:

https://www.google.com/finance/option_chain?q=AAPL