Google spreadsheet tips and tricks

[Fat Pitch]

Get the google script ImportJSON from https://github.com/fastfedora/google-docs/blob/master/scripts/ImportJSON/Code.gs

 

then in google spreadsheet, use this script and give the URL that fetches the JSON as a parameter. I used this to show data in econtracker.com

 

Thanks for the replies!

 

I was able to install the JSON script and I ran the following command:

 

=importjson("http://financials.morningstar.com/financials/getFinancePart.html?&t=XNAS:MSFT&region=usa&culture=en-US&cur=&order=asc",,"noInherit,NoTruncate,rawHeaders")

 

Now the data that gets pulled in only shows this:

 

/componentData

<div class="col5" style="display:inline;"><div id="financials"><div class="hspacer6"></div><div class="r_tbar0 positionrelative"><h3>Financials</h3><div class="export_list_financials"><div class="list_select"><li id="Li2" class="r_dd1" onclick="showDropdow

 

It looks like this is the minimized version of the data since I was able to insert the URL into a JSON viewer website and I was able to get everything to display. Any ideas on how to expand the data?

 

[Vish_ram]

sorry, i don't know much coding.

 

but this one works

 

=ImportJSON("http://api.stlouisfed.org/fred/series/observations?series_id=DGS1&api_key=bfa8a53421e527e0d002630712500fd9&sort_order=desc&limit=1&frequency=d&file_type=json","/observations", "noInherit, noTruncate")

 

you need to pass the second parameter, you left it blank.

[racemize]

does anyone have a good way of importing the S&P 500 total return?

 

I may need to attempt the importJSON for:

http://us.spindices.com/indices/equity/sp-500

 

(You can get a link to export an xls showing the current YTD return, but I have no idea how to automatically import a value of a cell from an xls file into a google spreadsheet)

 

This used to work, but is very very flakey these days (morning star gives a lot of errors, it appears):

=(Index(ImportHtml("http://quicktake.morningstar.com/index/IndexCharts.aspx?Symbol=SPX",0), "table", 10),3,7))/100

 

Anyway, it's driving me a bit crazy.  If someone else is pulling this and has a different way, please let me know.

 

Thanks!

 

 

[meiroy]

does anyone have a good way of importing the S&P 500 total return?

 

I may need to attempt the importJSON for:

http://us.spindices.com/indices/equity/sp-500

 

(You can get a link to export an xls showing the current YTD return, but I have no idea how to automatically import a value of a cell from an xls file into a google spreadsheet)

 

This used to work, but is very very flakey these days (morning star gives a lot of errors, it appears):

=(Index(ImportHtml("http://quicktake.morningstar.com/index/IndexCharts.aspx?Symbol=SPX",0), "table", 10),3,7))/100

 

Anyway, it's driving me a bit crazy.  If someone else is pulling this and has a different way, please let me know.

 

Thanks!

 

Pardon, which value do you need exactly off the spindices page?  The morningstar page indeed does not work, is it possible they moved it somewhere else?

[rishig]

Here is my spreadsheet for SP500 TR (populated automatically using yahoo finance).

https://docs.google.com/spreadsheets/d/1zUXc6FnUeIFR9NsAAd5bh3Yjporo_ymtEl2fS6NPFS8/edit#gid=0

 

Using the adjusted close column in my spreadsheet, the data I get agrees with what Morningstar reports when comparing funds performance to sp500 tr. See here:

http://performance.morningstar.com/fund/performance-return.action?t=SEQUX&region=usa&culture=en-US

[SpecOps]

I prefer to use importxml with Bloomberg. Yahoo and Google are a bit hit and miss I find.

 

I did a full blog post on it here https://investingsidekick.com/monitor-portfolio-google-docs/ but since then I've got into the Google Apps Script and have been doing some funky stuff with that. Hoping to release some pre-made spreadsheets at some point utilizing it all

[bargainman]

Is anybody having problems importing warrants and options from Yahoo finance into their Google spreadsheet? For the last week or so warrants have been kind of sketchy, and yesterday the options stopped working altogether. I'm using import XML and it looks like there's an error. I haven't really debugged it yet but just thought I would ask to see if anybody else is having a similar issue

[rishig]

Is anybody having problems importing warrants and options from Yahoo finance into their Google spreadsheet? For the last week or so warrants have been kind of sketchy, and yesterday the options stopped working altogether. I'm using import XML and it looks like there's an error. I haven't really debugged it yet but just thought I would ask to see if anybody else is having a similar issue

 

Stopped working for me as well.

[meiroy]

 

Haven't used it in a long time so just took a quick look but it seems what was quoted on the first post doesn't work anymore, need to point directly to the id within that class, then it's OK. There's probably an easier way to do it.

[racemize]

This is working for me:

 

=Substitute(importXML("http://finance.yahoo.com/q?s=GM-WTB","//span[@class=time_rtq_ticker]"),"*","")+0

[bargainman]

Problem is with ss loading.  When I first load SS it has #N/A

 

ex:

=Substitute(importXML("http://finance.yahoo.com/q?s=AAPL170120C00110000","//span[@class=time_rtq_ticker]/span[1]"),"*","")

 

if I manually change xpath to:

=Substitute(importXML("http://finance.yahoo.com/q?s=AAPL170120C00110000","//span[@class=time_rtq_ticker]/span[ 1]"),"*","")

 

notice extra " " after "span["

it updates and get the value!  (or vice versa...)

 

so it's just not getting stuff on load..  very annoying...

 

I suggest you click Help menu, and report problem to google.. I have...

[SmallCap]

i have used the importXML function to import option data from Yahoo for a few years but for the last few months it isn't working and from looking around it seems that yahoo did away with this functionality.

 

I am trying to find another solution for options data, the only thing I have found is the ability to import the data for the next 3 months of options from NASDAX.

 

Any other solutions out there?

[DW]

i have used the importXML function to import option data from Yahoo for a few years but for the last few months it isn't working and from looking around it seems that yahoo did away with this functionality.

 

I am trying to find another solution for options data, the only thing I have found is the ability to import the data for the next 3 months of options from NASDAX.

 

Any other solutions out there?

 

 

I have given up and moved to Google Sheets for my downloads. It does everything well except for dividends which can be done although it's not as simple as the other functions (i.e. stock price)

[LC]

I was just going to post, asking about how to import a company's most recent dividend into google sheets. Anyone have a snippet I can copy? Cheers  8)

[DW]

I was just going to post, asking about how to import a company's most recent dividend into google sheets. Anyone have a snippet I can copy? Cheers  8)

 

Even though I'm using Google Sheets, I still get that from Yahoo:

 

=importdata("http://download.finance.yahoo.com/d/quotes.csv?s="&$A3&"&f=y")

[SmallCap]

i have used the importXML function to import option data from Yahoo for a few years but for the last few months it isn't working and from looking around it seems that yahoo did away with this functionality.

 

I am trying to find another solution for options data, the only thing I have found is the ability to import the data for the next 3 months of options from NASDAX.

 

Any other solutions out there?

 

 

I have given up and moved to Google Sheets for my downloads. It does everything well except for dividends which can be done although it's not as simple as the other functions (i.e. stock price)

 

i have been using this yahoo function in google sheets but I am trying to get options data and I used to be able to import it from Yahoo but I can't do that now.

Do you have a way of getting options data into a google sheet?

[DW]

 

i have been using this yahoo function in google sheets but I am trying to get options data and I used to be able to import it from Yahoo but I can't do that now.

Do you have a way of getting options data into a google sheet?

 

I haven't tried to pull in the options info, sorry.

[bargainman]

I've moved to script editor based..

 

eg:

=getMidPrice("AAPL180119C00100000")

 

where tools->script editor, then enter:

 

function getMidPrice(ticker) {

  ticker = encodeURI(ticker);

  var response = UrlFetchApp.fetch("https://query2.finance.yahoo.com/v7/finance/options/" + ticker);

  var chain = JSON.parse(response.getContentText());

  var ask = parseFloat(chain.optionChain.result[0].quote.ask);

  var bid = parseFloat(chain.optionChain.result[0].quote.bid);

  var mid = (bid + ask) / 2;

  return mid;

}

 

ymmv

[racemize]

I've moved to script editor based..

 

eg:

=getMidPrice("AAPL180119C00100000")

 

where tools->script editor, then enter:

 

function getMidPrice(ticker) {

  ticker = encodeURI(ticker);

  var response = UrlFetchApp.fetch("https://query2.finance.yahoo.com/v7/finance/options/" + ticker);

  var chain = JSON.parse(response.getContentText());

  var ask = parseFloat(chain.optionChain.result[0].quote.ask);

  var bid = parseFloat(chain.optionChain.result[0].quote.bid);

  var mid = (bid + ask) / 2;

  return mid;

}

 

ymmv

 

That's really helpful.  Thanks.

[misterkrusty]

anybody know why google sheets is not recognizing google's own symbols for Singapore stocks?  For example, you can pull up a quote on google.com/finance for this company:

 

https://finance.google.com/finance?q=SGX%3AAVM&ei=ygkCWun3LsvCe4LOnugL

 

but this formula in google sheets comes up "N/A"  =googlefinance("SGX:AVM","price")

 

thx

 

[writser]

If you hover your mouse over the cell in question you'll see that 'Google Docs isn't authorized for exchange SGX', or something similar. Same for Japanese stocks, they don't work either. Try something like this:

 

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

 

=ImportXML("http://markets.ft.com/research/Markets/Tearsheets/Summary?s=AVM:SES", "/descendant::span[@class='mod-ui-data-list__value'][1]")

 

=ImportXML("https://www.bloomberg.com/quote/BOCS:SP", "//div[@class='price']")

 

To import from one of the websites in question.

[KinAlberta]

Not sure if this is related but I'm seeing the message quoted below on https://finance.google.ca/. 

 

I assumed they were shutting down the service but is it just a brief interruption?  If it's permanent, can anyone recommend another simple but decent free stock tracking service?  Yahoo Finance? Others?

 

 

 

"Google Finance is under renovation. As a part of this process, the Portfolios feature won't be available after mid-November 2017. To keep a copy, download your portfolio.

 

[/quote}

[misterkrusty]

thanks writser!  all those work perfectly.  can you adjust for other attributes, like volume, % change, etc.?

[writser]

Yeah, should be possible but haven't tried myself.

[misterkrusty]

2 more newbie questions (I searched for answers already but no luck):

 

Is it possible to set a different zoom level for each sheet?  Currently if I set one sheet to, say, 90%, then every other sheet reverts to the same level.  I'd like to keep some sheets at 75%, another at 125%, etc.

 

Is it possible to change the default fill and text colors (from white fill and black text)?  In excel you'd just highlight the whole sheet and change the settings.  But with Google Sheets, if I set a sheet to a black background and white text, then cut and paste, the cell(s) I cut from revert to white background and black text.

 

thx!!