Tracking investments with Google Spreadsheet

[lcornwisky]

lol lots of hardcore traders here!

 

[K|muRa^84]

i use this for sg stock prices

=importData("http://finance.yahoo.com/d/quotes.csv?s=es3.SI&f=l1")

ok it's working now

 

[peterchan75]

For those using google spreadsheet that who want almost real time quote. Here is the google apps script that will scrape quote from Yahoo ecn e.g. http://finance.yahoo.com/q/ecn;?s=YHOO.

Just go to Tool > Script Editor and cust & paste.

function getYahooRTprice(symbol) {
  var rtPriceStr=UrlFetchApp.fetch('http://finance.yahoo.com/q/ecn?s=' + symbol).getContentText();
  rtPriceStr=rtPriceStr.replace(/\n/g,"");
  var strlength=rtPriceStr.length;
  var loc = rtPriceStr.search("time_rtq_ticker");
  loc=rtPriceStr.indexOf(">",loc);
  var loc2=rtPriceStr.indexOf("<",loc);
  while (loc2-loc<=1 && loc2 < strlength) {
    loc=rtPriceStr.indexOf(">",loc2);
    loc2=rtPriceStr.indexOf("<",loc);
  }
  return rtPriceStr.substr(loc+1,loc2-loc-1)*1;
}

 

[Joeil-]

Bookmarked. Thanks TS

 

[Some-one]

For those using google spreadsheet that who want almost real time quote. Here is the google apps script that will scrape quote from Yahoo ecn e.g. http://finance.yahoo.com/q/ecn;?s=YHOO.

Just go to Tool > Script Editor and cust & paste.

function getYahooRTprice(symbol) {
  var rtPriceStr=UrlFetchApp.fetch('http://finance.yahoo.com/q/ecn?s=' + symbol).getContentText();
  rtPriceStr=rtPriceStr.replace(/\n/g,"");
  var strlength=rtPriceStr.length;
  var loc = rtPriceStr.search("time_rtq_ticker");
  loc=rtPriceStr.indexOf(">",loc);
  var loc2=rtPriceStr.indexOf("<",loc);
  while (loc2-loc<=1 && loc2 < strlength) {
    loc=rtPriceStr.indexOf(">",loc2);
    loc2=rtPriceStr.indexOf("<",loc);
  }
  return rtPriceStr.substr(loc+1,loc2-loc-1)*1;
}

Seems like it is not accurate for bonds

For example, the last closing price of BIOZ.SI is 1.008 but when I use the function, it returns me 1.010. Are there some rounding off in use?

ok. Just found out that the source is wrong and the function takes in the wrong value. Looks like the importdata is more accurate.

</span>SES </span><span class="wl_sign"></span></div></div><div class="yfi_rt_quote_summary_rt_top sigfig_promo_1"><div> <span class="time_rtq_ticker"><span id="yfs_l10_bioz.si">1.01</span></span> <span class="down_r time_rtq_content"><span id="yfs_c10_bioz.si">

http://finance.yahoo.com/q/ecn?s=BIOZ.SI

 

[peterchan75]

It depends on your needs. If you like frequent update during trading hours, then this is closer to real time. For EOD, then use importdata.

 

[dkhchan]

another way of getting real time quotes. get it directly from google finance:

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

change "SGX:Z74" accordingly

 

[keithwb]

removed post as code posted are not efficient and not longer using.

 

[chuanz]

Pls note there's a trigger time consumed daily limit. If you set the trigger too frequent and say Yahoo site does a timeout, you're screwed for the day as far as the "auto update" is concerned.

Just sharing some learning from my personal GS experiences.

 

[Flex11]

Anybody encountered error recently?

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

Error
Could not fetch url: https://www.google.com/finance?q=(SGX:S63

 

[iam1e5]

read these 2 websites

http://www.labnol.org/internet/import-html-in-google-docs/28125/

https://kx.cloudingenium.com/conten...hoo-finance-you-can-query-them-via-excel-too/

tldr:

use "=importData("http://finance.yahoo.com/d/quotes.csv?s=s63.SI&f=l1")"

to get data for SGX ticker S63, that is ST Eng

 

[godbowen]

Google sheet on YahooFinance stock price API is not working.
Anyone has this issue?

 

[dotreddot]

Can consider to have a separate on-line copy in Google Finance? ...
Create you portfolio there ... When spreadsheet down still can see on-line?

mine down too kan sianz

cannot see the value of my positions

heng is bull not bear day

 

[Average]

Mine still lagging like siao, cant use or view.

 

[peterchan75]

In a big corporation like google, this is a sign of de-emphasizing. The job on maintaining has been assigned to some obscure department where nobody gives a sheet. It's time to move on.

 

[peterchan75]

end up now switch to manual keying in the stock prices
sianz

maybe everyday market close key in once

GG

Using Excel macro and pull from Yahoo Finance or SGX if you are tracking stock price. This is better then manual data entry.

 

[rerear]

how to resolve the investmentmoats googlefinance issue with sgx?

 

[thegodfather]

I think there is an issue w Google sheets my importdata functions all not working. Laggy too. And I thought I was the only one

 

[Average]

Browser din crash but the particular tab will be damn lag, like 3min lag. Cam still use other tabs normally.

 

[Average]

Google, 1wk liao still cant fix...