Options Quotes using =googleFinance("") on google spreadsheet

[Marlin Cobb]

Will we, or can we get option quotes via the googlefinance call in google spreadsheets?

Thanks

[Karolina (Google Employee)]

Hello Marlin,

Currently we don't support getting option quotes via Google
Spreadsheets.

- Karolina

[Marlin Cobb]

You answered 1 of 2.  The first part was will we and is there a timeframe.  thanks

--
You received this message because you are subscribed to the Google Groups "Google Finance" group.
To post to this group, send email to google-...@googlegroups.com.
To unsubscribe from this group, send email to google-financ...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-finance?hl=en.

[Karolina (Google Employee)]

We do plan to eventually add this, but we don't have any specific
timeframe - i.e. it's not something that's imminent.

On Apr 15, 10:54 pm, Marlin Cobb <mar...@redliontrader.com> wrote:
> You answered 1 of 2.  The first part was will we and is there a timeframe.
>  thanks
>
> On Fri, Apr 15, 2011 at 12:08 PM, Karolina (Google Employee) <
>

[Kirk Otis]

The DoubleDividend website uses GoogleDocs to calculate the implicit returns on dividends and the call and put premiums when writing covered options. They recalculate the returns every 5 minutes based on links to the current stock and option pricing.  I can't tell how they do this but can see in the column  CALL OPTION EXPIRATION DATE - SYMBOL that they have some operands that are probably being used. 

The model fails to  properly annualize the dividend return (it presumes you continue to hold the stock) and it does not include the commissions for the stock and options.

https://www.doubledividendstocks.com/index.php?page=coveredcalltables

Bottom line, there is a way to get real time option pricing into GoogleDocs, these guys have done it. Perhaps they are going externally to a data feed, but it has been done.

[Scott K]

I know this is a few years late, but I ran into this same problem today. Google has not yet updated the GoogleFinance call to work for options. For those new people searching for a solution, I have found a relatively simple work-around using ImportHTML:

=index ( importhtml("http://finance.yahoo.com/q?s=GE160115C00025000","table",0),3,2)

GE160115C00025000 is the option ticker for the Jan 16, 2015 call on GE with a strike of 25. The 3 and 2 reference row and column, respectively, in table 0...the bid price in this case. Change those values if you want other table data.

Using a round about way I was also able to extract the delta and theta values from nasdaq.com's options page. Let me know if want to know how to do that. It's not pretty but at least works.

[John Macdonald]

Scott,

Thank you for posting this! It works a charm, I am able to get the real time info I need from the option quote plus more, I have been using it for 3 days now with no issues. It was alot of fun changing the table numbers and getting a new data point-like Christmas came early.

It's a few years late, but guess what? It's still current. ; )

On Saturday, March 1, 2014 8:30:05 PM UTC-8, Scott K wrote:

I know this is a few years late, but I ran into this same problem today. Google has not yet updated the GoogleFinance call to work for options. For those new people searching for a solution, I have found a relatively simple work-around using ImportHTML:

=index ( importhtml("http://finance.yahoo.com/q?s=GE160115C00025000","table",0),3,2)

GE160115C00025000 is the option ticker for the Jan 16, 2015 call on GE with a strike of 25. The 3 and 2 reference row and column, respectively, in table 0...the bid price in this case. Change those values if you want other table data.

Using a round about way I was also able to extract the delta and theta values from nasdaq.com's options page. Let me know if want to know how to do that. It's not pretty but at least works.

On Thursday, April 14, 2011 3:21:43 PM UTC-5, Marlin Cobb wrote:

[Scott K]

Hey John,

Glad it helped out. The formula is still working for me as well. It should continue until Yahoo changes the page layout. Then, we'll just have to play around with the row/table until it works again.

I gave up on the Nasdaq version of the formula. It worked for a few weeks and then Nasdaq must have changed something on the website to break it.

[090wi...@gmail.com]

I just found your patch for options pricing.  It sort of works, but I wonder if Yahoo! has changed their format.  What the formula gives now is bid, ask, and previous close--not the most recent close that I want.  I played around with the columns a bit but could not get the last closing price that I see in the heading.  Has anyone found the solution to this?  TIA

[Andrew Brick]

After a lengthy search and much experimenting, I've created the following solution.  It pulls from marketwatch.com, and it might be helpful to reference an option chain from that website as you read through this.  To get the full formula, skip to the bottom.

First, you need to have several helper columns:

• ticker, in plain text (referred to as "TICK" below)
• option expiration, in the format "Month Day, Year", e.g. July 21, 2017.  This must be plain text.  Google will interpret an entry like this and convert to a date, so be sure the type is plain text. (referred to as "EXP" below)
• strike price, as a number (referred to as "STRIKE" below)
  

Additionally, you will need to know if you want the bid or ask and whether the option is a put or call.  You can modify the formulas below to take advantage of additional helper columns with this information, but I've just chosen to hard-code the values as needed.

The first step is to get the option chain and find the row with the right expiration date:

=match(concatenate("Expires ", EXP), index(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1),,1),0)-6

The formula above pulls the entire list of options from the marketwatch website and finds the row index of the row matching your expiration date. I'll refer to the result of this formula as "SEARCHSTART" from here on out.  The formula decrements four rows for the next step (which isn't entirely necessary), which creates our second step:

=match(STRIKE, index(query(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1), concatenate("SELECT * LIMIT 50 OFFSET ", SEARCHSTART)),,8),-1)

The formula above again pulls the entire option chain from marketwatch, but then subsets the rows returned to 50 rows, starting from the row containing our expiration date.  50 is an arbitrary value, with the assumption that 50 rows will capture all of the strikes.  The "match" portion of the function looks in column 8 (near the tail end of the formula) for our strike price.  The last "-1" in the match formula (right at the end) seems to be necessary to ensure we pull the right expiration's strike when multiple expiration dates of the same strike are returned in our subsetted table (there's probably a more elegant way around this).  The formula ultimately returns the row containing the right strike price for the right expiration date.  I'll refer to this cell as "STRIKEROW" from here on out.  Time for step 3:

=index(query(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1), concatenate("SELECT * LIMIT 50 OFFSET ", SEARCHSTART)), STRIKEROW, 6)

This formula returns the ask price of the call option associated with our designated expiration date and strike price.  The last value (6, above) designates the return value as the ask price of the call option, as this is the column that contains that value in the marketwatch table.  Other values are:

• column 6: call ask
• column 5: call bid
• column 13: put bid
• column 14: put ask

So you just need to change that last value depending on what your needs are.

Put all together, the whole thing can reside in a single cell (references are in red, and the last call/put/bid/ask variable is in blue):

=index(query(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1), concatenate("SELECT * LIMIT 50 OFFSET ", match(concatenate("Expires ", EXP), index(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1),,1), 0)+2)), match(STRIKE, index(query(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1), concatenate("SELECT * LIMIT 50 OFFSET ", match(concatenate("Expires ", EXP), index(importhtml(concatenate("http://www.marketwatch.com/investing/stock/", TICK, "/options?countrycode=US&showAll=True"), "table", 1),,1),0)+2)),,8)),6)

There you have it. It can take some time for google sheets to evaluate everything, and there's a noticeable "hang" if you're pulling a number of quotes. Nonetheless, options quotes in google sheets are back in business!!

I've manually checked prices for about a dozen options, a mix of calls and puts, and everything seems good, but I wouldn't be surprised if the formula knackered up here and there. Note: In my experimentation, I did find that marketwatch, for whatever reason, doesn't seem to always show a complete option chain. At the time of this posting, it wasn't showing any August 2017 options for BKS, for instance.

[Andrew Brick]

Updated solution here: https://redd.it/69shrw

Things seem pretty slow to be posted in this forum...

[Brian Sherman]

If I wanted to generate pricing for WFC January 19, 2018 put with $50 strike price, what syntax would I enter?

Thanks,

Brian

[K. Richmond]

Wow! Thank you for this. Have not tried it out yet, but it looks to be my best bet since my Yahoo import patch stopped working last winter. I'm so grateful to find this.

Sent from my BlackBerry 10 smartphone on the Bell network.

From: Brian Sherman Sent: Tuesday, August 1, 2017 11:36 PM To: Google Finance Reply To: google-...@googlegroups.com Subject: Re: Options Quotes using =googleFinance("") on google spreadsheet

--

You received this message because you are subscribed to the Google Groups "Google Finance" group.

To unsubscribe from this group and stop receiving emails from it, send an email to google-financ...@googlegroups.com.

To post to this group, send email to google-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-finance/b7f8a4f8-9322-4215-b6c2-1b48b8377896%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Andrew Brick]

Just sub in your desired values where B7, R7, and O7 appear in the formula I posted at https://redd.it/69shrw. The last argument ("6" in the example formula) would be either 13 or 14, depending on whether you want the put's bid or ask (respectively).

The formula seems to work most of the time. There are certainly times when marketwatch.com has incomplete options chains, but even when they're complete, I've had sporadic issues that I've yet to be able to troubleshoot.

[Mike Costanti]

Andrew, great solution. This works perfectly.

I'm trying to find a similar method to display available strikes and expiration dates for a given symbol...I've tried to use importhtml() to discover a list or table on Market Watch to no avail. 

Based on the work that you've done, can you think of a clever way to do this? Goal would be to just return a column of expirations and a column of available strikes for a given symbol. If I can get that done, I'd then drop those into a data validation drop down list so that I could easily toggle between different strikes and expirations via a simple drop down picker.

Again, great work on this.

All best,

Mike

[Dave Labarthe]

HI,

Is there an update on retrieving options data (as you showed in 2014)? Thank you.

[Randy Harmelink]

You'll need to use Yahoo's new URL. For example:

=index(importhtml("https://finance.yahoo.com/quote/MMM180329C00217500","table",0),3,2)

[Zalikha Johar]

HI Karolina,

Do google finance dont update their usdmyr ? as the rate did not appear it my sheet

Regards

Zalikha

[Fabio KGA]

Hello Randy, Do you know if there is any other site, other than Yahoo, that I could import options quotes? I am looking at this Google sheets sollution because Yahoo sometimes is late in making certain options available. many thanks,, Fabio

[Randy Harmelink]

Sorry, but for my EXCEL add-in, I've pretty much unplugged all of the other data sources of option quotes because of too many issues. Yahoo has been flaky from time to time, but much better than other (free) sources overall.

The best source of option quotes would be a broker, as they would actually be trading the options. However, they are usually behind some type of security, which can create its own issues for automated retrieval.

--
You received this message because you are subscribed to the Google Groups "Google Finance" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-financ...@googlegroups.com.
To post to this group, send email to google-...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-finance/f46c3012-2080-462c-962c-b0ef2ef19a1b%40googlegroups.com.

[Fabio KGA]

Thank you Randy, this is helpful!! 

To view this discussion on the web visit https://groups.google.com/d/msgid/google-finance/CAJ-1-22pBoiDWd1ju_U0B_RKNJVtWsm_%2Bs8GFna2Uy1uOmUCmw%40mail.gmail.com.

[David Clark]

Thank you Randy.   All these years later, and your assistance is still greatly appreciated.