Tracking investments with Google Spreadsheet

[Jazzbie]

Investment Moat has a fix for those using his stock tracker version on his blog.

 

[thegodfather]

Yes I am aware of that. It has been months since they stopped.

However functions like importdata() don't seem to be working. It says unable to overwrite the cell or smth zzzz

SGX Stopped Supporting Google Finance

The first thing is that SGX have stopped allowing investors to use GoogleFinance() to get update to date last trading price information of their stocks.

This affected many folks including myself who rely on GoogleFinance to pull latest stock prices.

This means that you either have to rely on manually entering the prices on a frequent basis, or rely on Yahoo Finance.

 

[bencheongcm]

Maybe you guys can try out this simple google script for a more interval update of the prices. this method is a bit sloppy but it seem to be working fine. it update about 1min interval which is the smallest interval of the google script trigger.

The steps are:

1) Open the stock portfolio tracker -> go to Tools -> Script Editor

2) Paste the code below and save it.

3) Resource -> Current project's trigger -> Add new trigger -> chose the following:
- Run: intervalUpdate
- Events: Time-driven
- Minutes Timer
- Every Minute

4) Save again.

How it work?
It should be because the "Yahoo Data Ref" take reference from the column F of "Stock Summary", when u edit it, it will refresh the data inside the yahoo data which in turn pulled out new updated data for column G of "Stock Summary". The script change the data at every minute interval and thus it like getting new data every minute.

function intervalUpdate(){
  SpreadsheetApp.getActiveSpreadsheet().toast("","1-Min Update");
  refreshData();
  SpreadsheetApp.getActiveSpreadsheet().toast("","1-Min Update Done!"); 
}

function refreshData(){
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock Summary");
  var range1 = sheet1.getRange('F2');
  var data1 = range1.getValue();
  
  range1.setValue("Temp"); 
  //Utilities.sleep(200);
  var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stock Summary");
  var range2 = sheet2.getRange('F2');
  
  range2.setValue(data1); 
}

This seems a little odd. It keep giving an error that there is an issue in this line

var range1 = sheet1.getRange('F2');

any ideas ?

 

[stjoe1]

any of you facing issue with googlesheet not updating the portfolio tracking using Yahoo Finance? seems like since last few days, could not get my googlesheet tracker updates ..

 

[chuanz]

I dunno what codes he writing
I using just simple one liner and i get my prices fine

It's bad code like those that are abusing the free price feeds...

The trigger runs every minute regardless whether you have the sheet open or not. So every day, ONE COPY of the spreadsheet with the trigger script attached will fire 24 x 60 = 1440 requests to fetch price data, even when nobody is looking.

 

[keithwb]

It's bad code like those that are abusing the free price feeds...

The trigger runs every minute regardless whether you have the sheet open or not. So every day, ONE COPY of the spreadsheet with the trigger script attached will fire 24 x 60 = 1440 requests to fetch price data, even when nobody is looking.

yea guys, it is not efficient. i not longer using that in fact, was just testing around.

Already changed to invoke trigger onOpen().

 

[Darkzi0n]

jus use this
https://www.sgxcafe.com/

everything done for u.

all u need to do is to input ur buy sell transaction.

even track dividend and and the next div date also.

but onli for sgx listed stocks.

 

[peterchan75]

Sometimes I wonder why must see price jump every minutes or seconds ? Is it this really necessary ? Even day traders don't see the jumping prices. They use real time charts.

 

[Mecisteus]

If you are a newbie investor/trader, looking at prices too frequently going to be detrimental to your portfolio performance. I can assure you that.

 

[angtc11]

Hi, has anyone faced the issue of Google sheets crashing? My sheet is an adapted sheet from investment moats and after opening it for the first time in 2 weeks, it's crashing and causing Firefox browser to crash too. Even opening in the Android app causes the app to crash.

 

[WindBoi]

Hi, has anyone faced the issue of Google sheets crashing? My sheet is an adapted sheet from investment moats and after opening it for the first time in 2 weeks, it's crashing and causing Firefox browser to crash too. Even opening in the Android app causes the app to crash.

hi there, this is kyith from investment moats. perhaps you can try creating a copy of the same spreadsheet and see if it solves the issue. Mine crash like no tomorrow as well. Creating a new one solves that.

 

[angtc11]

hi there, this is kyith from investment moats. perhaps you can try creating a copy of the same spreadsheet and see if it solves the issue. Mine crash like no tomorrow as well. Creating a new one solves that.

Hi Kyith
A week or so after this post, the spreadsheet is working ok again, even though I didn't do anything besides sending feedback to Google each time it crashed. Thanks for the solution, will try it out next time.

Lesson learned to backup the raw transaction data at a minimum since the formulas can be rewritten in the worse case scenario

 

[Average]

Yea, its working now but i already ditched it. Now using sgxcafe

 

[pai000000]

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 Yahoo's latest update has broken this code. Any skillful coder here who can fix it?

 

[cmanders]

Hi everyone,

this stopped working for me, but, if you change the url to sg.finance.yahoo.... it works