Import stock quote in Google sheets

by Jan

In this "Be free and wealthy" post you will learn :

  • How to import stock prices and option prices in google sheet
  • How calculate a simple option yield in google sheets
  • Show how to generate 6% to 10% from S&P Index.

I love trading options. But beware for the novice, option trading is not for everyone. I usually do simple strategies. One of my favorite strategies is to increase yield by writing covered calls. Another one is to write naked put (with the intention of buying the underlying stock sometime in the future).

For the covered call, I usually use it to create yield. I like to give myself a target (say a need a 15% appreciation in 1 year) and write a covered call on this. I like to use the SPY (Standard & Poors index). This is a heavily traded stock and the options market is very liquid (as opposed to most stocks if you trade a lot you will soon notice some stocks have no volume in their option chains).

Now here comes the Google Sheet magic. You can directly import the stock price in your google sheet:

=ImportXML("http://www.google.com/finance?q=SPY", "//span[@class='pr']")

This will display the latest price.

Now let's say you want to get option price for that same underlying stock, now let's look at the Yahoo finance option chain for January 2018:

http://finance.yahoo.com/quote/SPY/options?date=1547769600

You can pick a strike price, let's say a call for 230$. Find the contract name, in this case : SPY180119C00230000

=importXML("http://finance.yahoo.com/q/op?s=SPY&date=1516320000","//tr[contains(.,'SPY180119C00230000')]/td[3]")

Now what I like to do is to calculate the yield if I keep this position until maturity. In case it gets exercised, I get.

  • ~2% dividend per year
  • Gain of 230-221 (9/221= ~4% )
  • 8.8 premium for selling the option (8.8/221= 3.9%)

So the total yield is around ~10%. Or worse case of 6%.