Skip to Content

Some Stock Portfolio Tracker Problems and How to get Your Singapore Prices Updated in your Spreadsheet

For the users of my Stock Portfolio Tracker, you might notice that there are some issues getting the end of day Singapore prices.

You would notice a “#NA“, where it says failed to fetch data.

I am puzzled why this happened.

I investigated for 2 days and realize its a combination of:

  1. How my web host handles the caching of the price file “sp.csv”
  2. How ImportData function in Google Spreadsheet works

Suffice to say, I was deeply frustrated. So much so it nearly caused me to down the whole Investment Moats and put me out of commission for 2 full days.

Some times price can be retrieved.

Then it stopped working after a while.

Sometimes you get an old cache version of the file.

The Solution

The solution, which might be temporary or alternative, is to use my custom function getSGPrice() instead of ImportData function.

This however, means we need to make use of Google Backend Script Editor.

Here is how we do it.

There are 3 Steps.

Step #1: At your Google Spreadsheet, Go to Tools > Script Editor…

This will launch the Google Script Editor, which provides you with the back end functions to do funky things.

Step #2: At the script editor, find an empty space and put in the following function:

function getSGPrice(string) {
var urlarr = string.split("?");
var csvContent = UrlFetchApp.fetch(urlarr[0]).getContentText();
var csvData = Utilities.parseCsv(csvContent,',');
for(i=0; i<csvData.length;i++){
var tmp = csvData[i][3];
var toNum = parseFloat(tmp);
csvData[i][3] = toNum;
}
return csvData;
}

See image below.

This function takes in the formula from your spreadsheet, then split it to remove the part behind the “?”, then fetches the data.

Click on the Floppy Drive Icon to Save the Functions. See below

Step #3: Let’s go back to your Stock Portfolio Tracker, Yahoo Data Ref, we need to change the function from using ImportData to using this getSGPrice

In cell A2, which is where the magic happens, change the formula to:

=getSGPrice("http://investmentmoats.com/shoyo/sp.csv?&"&'Read This First'!$B$5)

This will make use of our custom getSGPrice function rather than the build in importdata function.

That is it.

You should see your data being updated.

We need the  'Read This First'!$B$5 because, if we do not include it, cell A2 will not be modified. When cell A2 is not modified, Google Spreadsheet will not go into the function getSGPrice and re-execute the price retrieval again.

Note that:

  1. Prices are still based on data retrieved from investmentmoats.com at the end of every trading day, so don’t expect live prices
  2. This works in conjunction with the refreshReadMeFirstTime() function to refresh ‘Read This First’!$B$5

For more on the Stock Portfolio Tracker, do read:

  1. Free Online Investment Stock Portfolio Tracker Spreadsheet
  2. Yahoo Finance Data Shuts Down – My Modification to My Stock Portfolio Tracker
To get started with dividend investing, start by bookmarking my Dividend Stock Tracker which shows the prevailing yields of blue chip dividend stocks, utilities, REITs updated nightly.
Subscribe to my email list, or Like Investment Moats on Facebook today
Kyith

This site uses Akismet to reduce spam. Learn how your comment data is processed.

YS

Thursday 25th of January 2018

Hi Kyith,

Thanks for your spreadsheet! Some users from HardwareZone have managed to find a workaround.(Source: http://forums.hardwarezone.com.sg/stocks-shares-indices-92/what-alternatives-after-yahoo-finance-stops-intra-day-stock-quotes-5717817-3.html)

They suggested the Bloomberg and Wall Street Journal sites as sources. I tried the one from WSJ, it seemed to work perfectly fine.

Hope this little tweak will bring the spreadsheet back to an easy update state.

Cheers! :)

Jasmin

Saturday 20th of January 2018

thanks Keith! Much appreciated

Kyith

Sunday 21st of January 2018

no problem

david

Monday 15th of January 2018

Hi Keith,

he end of day data fetch works for Singapore but is there anyway to fetch stock EOD price from Australia, USA and Malaysia market? Look forward to your reply.

David

Kyith

Tuesday 16th of January 2018

I think there are other data sources you can try but it requires you to do some experimentation. This post will explain a fair bit > https://investmentmoats.com/uncategorized/yahoo-finance-data-shuts-down-my-modification-to-my-stock-portfolio-tracker/

Alex

Sunday 24th of December 2017

Hi Kyith, I posted in your spreadsheet post regarding the “TypeError: Cannot call method “split” of undefined. (line 77, file “Code”)” in the getsgprice script editor. I realized the error came from the 2nd line of your code mentioned in this post "var urlarr = string.split("?");". May i know this line requires which cell in which worksheet to be filled?

Thank you and Merry Christmas!

Kyith

Sunday 24th of December 2017

Hi Alex. Tha tline splits a string "www.xxx.com?abc" into www.xxx.com and abc. if there isn't a ? there will be an error. how are you testing it?

ci3lo

Tuesday 19th of December 2017

Hi Keith, I found your stock tracker really helpful and it has inspired me to learn more about how spreadsheets work in order to get my formulas working. Thanks a million. Can we also know around what time is the SG stock information on your website updated?

Kyith

Wednesday 20th of December 2017

Sorry ci3lo, I try to get it to update at 7 pm but that is not always going to be the case. Today I forgot about it until 11pm.

This site uses Akismet to reduce spam. Learn how your comment data is processed.