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