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 Column | Excel Formula |
|---|---|
| Code | AAPL170120C00100000 |
| 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 Column | Excel Formula |
|---|---|
| OptionSymbol | AAPL |
| ExpDate | 170120 |
| Strike | 100 |
| Type | CALL |
| 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.
| Parameter | Value |
|---|---|
| RefreshInterval | 01:00:00, one hour |
| NextRequestDelay | 1000, one second |
| DataStartTime | 00:00:00 -05:00 |
| DataEndTime | 24:00:00 -05:00 |
| LoadEndTime | 24:00:00 -05:00 |
| LoadOnSaturday | 1, loading Friday's data once. |
| LoadOnSunday | 1, loading Friday's data once. |
Data Source
The Google Finance website.
Example:
https://www.google.com/finance/option_chain?q=AAPL