Create stock portfolio on Google Sheets using SGX price data (Updated)

This is the update to my previous post as the revamp of SGX site actually break the previous script where the download link is updated with no date embedded in the link. As there is no date in the link, this script will need to rely on other information which is the running number embedded in the link to automate the download of new data everyday.

Few preparations needed to be done.

Firstly, you need to add in Singapore Calendar in your Google calendar. This can be done as shown below as the script will use this calendar to determine whether the script is run on holiday day:

Secondly, you need to create a new Google Sheets. You need to create 2 sheets with first sheet use to store your portfolio and the second sheet (named as "Data") use to store your downloaded data. In this "Data" sheet, update the last 2 column with the date and counter value. You can get these value by looking at the download link when you download the data from SGX website.

Go to Google Chrome Download menu, and you can find the actual download link, note the number in the link 

So for this example, the date is 17 Oct 2019 and counter is 5569. Please enter the value to the "data" sheet as shown below.

Once you are done with above, open "Script Editor" under Tools menu and paste the following script or you can download from this link.

// Project: Script to download SGX price list for personal portfolio price update // Author: cwtan function onOpen() { var spreadsheet = SpreadsheetApp.getActive(); var menuItems = [ {name: 'Import SGX data...', functionName: 'importSGX'}, ]; spreadsheet.addMenu('Import', menuItems); } function importSGX() { var spreadsheet = SpreadsheetApp.getActive(); var dataSheet = spreadsheet.getSheetByName('Data'); dataSheet.activate(); // Getting date and the counter from 'Data' excel sheet var prevDate = new Date(dataSheet.getRange(1, 25).getValue()); var counter = dataSheet.getRange(1, 26).getValue(); // current date var todayDate = new Date(); // if prevDate is already indicate today's date, no need to run script as data already in if (sameDay(prevDate,todayDate)){ Logger.log("same date, no need to calculate, exit the script") return 0; } if (todayDate.getHours() < 19) { // set to yesterday date todayDate.setDate(todayDate.getDate() - 1) if (sameDay(prevDate,todayDate)){ Logger.log("script run before 7pm where today's file is not available for download, and yesterday date file is already processed, so no need to run script, exit the script.") return 0; } Logger.log("Script run before 7pm where today's file is not available for download, let the script run with yesterday date to retrieve yesterday file for processing.") } // calculate the number of days between today's date and prevDate (date recorded in the excel sheet) // if prevDate is 14 Oct 2019 and today's date is 16 Oct 2019, the number of days should be 2 const oneDay = 24 * 60 * 60 * 1000; // hours*minutes*seconds*milliseconds var nDays = Math.floor(Math.abs((todayDate - prevDate) / oneDay)); // loop through number of days to see what is the correct counter value to use var loopDate = prevDate; for (var i = 0; i < nDays; i++) { loopDate.setDate(loopDate.getDate() + 1); // day => Sunday = 0 ... Saturday = 6 var day = loopDate.getDay(); // only increase the counter is it is Monday to Friday (1 to 5) if (day != 0 && day != 6){ // on increase the counter if it is not holiday if (holiday(loopDate) == 1) { Logger.log("is holiday, no need to increase counter") } else{ counter = counter + 1; } } } // build the import url string // 5566 = 14 Oct 2019 var importString = "https://links.sgx.com/1.0.0/securities-historical/" + counter + "/SESprice.dat"; // import SGX price list data into datasheet var csvUrl = importString; var csvContent = UrlFetchApp.fetch(csvUrl).getContentText(); var csvData = Utilities.parseCsv(csvContent,';'); dataSheet.getRange(1, 1, csvData.length, csvData[0].length).setValues(csvData); // Check if data is correct, if there is valid data, cell(1,1) won't be blank. // If there is valid data, split the data using ; delimiter. var checkValidData = dataSheet.getRange(1,1); if (!checkValidData.isBlank()){ var startRow = 1; var endRow = dataSheet.getLastRow(); // Get the range value for stock codes, convert all the values to string and trim it. // Withut trim, matching could be troublesome because you need to // manually add space so to match var range = dataSheet.getRange("O1:O"); var rangeValues = range.getValues(); range.setNumberFormat("@"); for (var i = startRow; i <= endRow; i++) { // accessing the array data, array start from 0, that's why need to put i-1 var data = rangeValues[i-1][0]; dataSheet.getRange(i,15).setValue(String(data).trim()); } dataSheet.getRange(1, 20).setValue(endRow); // Update the "data" sheet for latest date and counter counterDate = dataSheet.getRange(1, 1).getValue(); // Get the value from the downloaded data dataSheet.getRange(1, 25).setValue(counterDate); // date dataSheet.getRange(1, 26).setValue(counter); // counter } } function holiday(dateToCheck) { // This function can help check whether the date you enter is public holiday // This is based on Google holiday calander, however, Google does not follow Minstry of Manpower (MOM) published public holiday, // therefore an exclusion list is created to filter out those holiday not listed in MOM list. // special holiday should not be included, example, polling day //var cal = CalendarApp.getCalendarById("en.singapore#holiday@group.v.calendar.google.com"); var cal = CalendarApp.getCalendarsByName("Holidays in Singapore"); // exclusion list of holidays which are not part of MOM published public holiday const nonHolidayArray = ["Christmas Eve","New Year's Eve","Children's Day","Easter Saturday","Easter Sunday"]; //var holidays = cal.getEventsForDay(dateToCheck); var holidays = cal[0].getEventsForDay(dateToCheck); var isHoliday = 0; // if there is only one holiday per day if (holidays.length >= 1){ for (var i = 0; i < holidays.length; i++){ var eventTitle = holidays[i].getTitle(); // if the title is not in the exclusion list, then is holiday if ( nonHolidayArray.indexOf(eventTitle) == -1 ){ isHoliday = 1; } } } return isHoliday; } function sameDay(date1, date2) { if (date1.getFullYear() == date2.getFullYear() && date1.getMonth() == date2.getMonth() && date1.getDate() == date2.getDate()) { return 1; }else{ return 0; } }

Once the data is downloaded to the "Data" sheet, you can now use formula to match stock quotes (column O) and retrieve the share last done price (column G).

Below is the sample table structure for the portfolio sheet.

For the last done price, enter the following formula

=INDEX(Data!$G$1:$G$1091, MATCH (B2, Data!$O$1:$O$1091, 0))

Please note that 1091 is the total row in the "Data" sheet. As there will be new stock list or delist, you might need to adjust this number from time to time.

You can configure trigger to have this script run at certain time everyday. As SGX only release this data after 630pm, i set the trigger time to between 7pm to 8pm.