dcu.centennial://toasthandler/Create/UpdatesAppliedRequiresReboot Element Not Found Popup

Encountered the following popup after restarted my Dell laptop today.

This is message from Dell Command Update and the version in my laptop is 3.01. Updated Dell Command Update to version 3.10 seems to fix this popup issue. I am using the "Universal Windows Platform version supported only on Windows 10 Redstone 1 v1607, build 14393 or above for 64-bit operating system" link.

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.

Google App Scripts - Holiday Date Checker

This script help to check whether your supplied date is holiday or not. The script rely on Google Public Holiday Calendar to determine whether the date is holiday or not.

You can open Script Editor under Tools Menu

Once you in Script editor page, you can paste the following code in the editor. Alternatively, you can also go to this link to get the latest code.

function myFunction() { var checkDate = new Date('2021-01-01'); if( holiday(checkDate) == 1){ Logger.log(checkDate + " is holiday"); } else{ Logger.log(checkDate + " is not holiday"); } } 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.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[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; }

Before you can run this script, you need to add in the Google Public Holiday Calendar. For this example, i am using Singapore Public Holiday Calendar but you should be able to use this script for different country calendar.

Go to calendar page, under Other Calendars, select Browse calendars of interest.

Expand Regional holidays and tick  Holidays in Singapore. As you can see, Google has extensive list of holiday calendar for a lot of countries.

Once the calendar is setup, you can try out the script. If you are not familiar with Google App Scripts, running the script for the very first time will usually has permission prompt. Just follow the instruction and allow this script to access.

To test the script, you can just edit the following line to supply the date you want to test. The format is 'yyyy-mm-dd'.

var checkDate = new Date('2021-01-01');

Under Tools Menu, click Run - Run Function - myFunction

Once the script is done, under Tools menu, you can cick View - Logs to check the result.

You can see some sample log output below

CentOS 7 dracut-initqueue timeout and could not boot – warning /dev/disk/by-id/uuid does not exist

I encounter this issue after i convert vmdk image to vhd image using Microsoft Virtual Machine Converter with the following PowerShell commands.

Import-Module "C:\Program Files\Microsoft Virtual Machine Converter\mvmcCmdlet.psd1"

ConvertTo-MvmcVirtualHardDisk -SourceLiteralPath E:\imageLocation\source.vmdk -VhdType DynamicHardDisk -VhdFormat Vhd -DestinationLiteralPath E:\vhd\destination.vhd

And this is the screenshot of the issue when i import this VHD. The warning is /dev/disk/by-uuid/uuid does not exist and this partition disk contain the root file system.

To fix this, i boot this VM with CentOS installation disk and select Troubleshooting follow by Rescue a CentOS system

Select 1 to continue

Run chroot /mnt/sysimage to make your system the root environment.

Go to /boot and you can see the list of initramfs images, as i wanted to run this OS using the latest kernel, i will recreate this image - initramfs-3.10.0-862.3.3.el7.x86_64.img

Perform the following commands

cp initramfs-3.10.0-862.3.3.el7.x86_64.img initramfs-3.10.0-862.3.3.el7.x86_64.img.bk

dracut -f initramfs-3.10.0-862.3.3.el7.x86_64.img 3.10.0-862.3.3

After reboot, i am able to see the login screen and can now login to the system.

Self Signed Cert with IP address as Subject Alternate Name

Most of the guide i found assume you are using domain name. As such i will write down this guide for those who use IP address.

We will create our own root CA cert and later on we will be using this to sign the website certificate. You will be prompted for password to protect your root CA secret key. You need to remember this password as it will be used later.

openssl genrsa -des3 -out rootCA.key 2048

openssl req -x509 -new -nodes -key rootCA.key -sha256 -days 365 -out rootCA.pem

Create a config file (csr.cnf) for generating Certificate Signing Request (CSR)

server.csr.cnf

[req]
default_bits = 2048
prompt = no
default_md = sha256
distinguished_name = dn

[dn]
C=SG
ST=Singapore
L=Singapore
O=test
OU=test
emailAddress=test@192.168.35.123
CN=192.168.35.123

Generate CSR

openssl req -new -sha256 -nodes -out server.csr -newkey rsa:2048 -keyout server.key -config server.csr.cnf

Create a v3.ext for Subject Alternate Name (SAN). So for IP address, you need to use IP instead of DNS

v3.ext

authorityKeyIdentifier=keyid,issuer
basicConstraints=CA:FALSE
keyUsage = digitalSignature, nonRepudiation, keyEncipherment, dataEncipherment
subjectAltName = @alt_names

[alt_names]
IP.1 = 192.168.35.123

Sign this certificate with your root CA

openssl x509 -req -in server.csr -CA rootCA.pem -CAkey rootCA.key -CAcreateserial -out server.crt -days 365 -sha256 -extfile v3.ext

You can now put both server.crt and server.key in your web server.

In order for your browser to accept this self signed certificate, you need to import your rootCA.pem into your system trusted root certificate authorities repository. for Windows system, please refer to this link .

Create stock portfolio on Google Sheets using SGX price data

18 Oct 2019: Please refer to this link for the updated post on how to create stock portfolio using SGX price Data.

It used to be very easy to create your SGX stock portfolio on Google Sheets as we can rely on Yahoo Finance or even Google Finance data. However, this changed when both stop the services. As such, the next best thing is to just rely on SGX daily price file which is available after market close. However, to automatically update your Google Sheets with this data is no longer a simple process. Below i share my steps on how i download the SGX price list from within Google sheets, do some data formatting and use formula to update the stock price accordingly.

1. Create a new Google Sheets and click "Script Editor" under Tools menu.

2. Paste the following code and click save. If you have issue with the code below like incorrect special character, please copy the code from this link.

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();
   
  // Since the data is only available after market close
  // codes below will make sure when it is run before market close, pick yesterday date
  // Current assumption is data is available after 6pm
  var todayDateTime = new Date();
  var currentHour = todayDateTime.getHours();

  if (currentHour >= 18) {
    var dataDate = Utilities.formatDate(new Date(), "GMT+8", "yyyy-MM-dd");
    Logger.log(dataDate);
  }else{
    var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
    var now = new Date();
    var dataDate = Utilities.formatDate(new Date(now.getTime() - MILLIS_PER_DAY), "GMT+8", "yyyy-MM-dd");
    Logger.log(dataDate);
  }

  var importString = "http://infopub.sgx.com/Apps?A=COW_Prices_Content&B=SecuritiesHistoricalPrice&F=5254&G=SESprice.dat&H=" + dataDate;
  
  // 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());
      }
   }
}

3. There will be prompt to Edit Project Name. Just put any name.

4. Go back to your Google Sheets and reload the browser.  After reload, you should be able to see a new menu called Import appear.

5. Create a new sheet and named it "Data". After rename, click on Import > Import SGX data...

6. When run this script for the first time, Google Sheets will prompt Authorisation Required message. Just click Continue and follow the processes to allow script to run. Once it is done, you can see the Data sheet populated with SGX price list data.

7. Go back to first sheet and 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.

This is the formula used on Last Done Price column, B2 is cell for stock quotes.

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

8. You can also configure trigger to have this script run at certain time everyday. Again go to Script editor and under Edit menu, click Current project's trigger.

9. Click on No trigger set up. Click here to add one now.

10. Select importSGX function to run, Time-driven, Day timer event with time set to 6:00 p.m. to 7:00 p.m. and click Save. And this will make this script run between 6 pm to 7 pm daily.

So that's it, you can start construct your own portfolio for for your shares in SGX.

I/O error, dev fd0, sector 0

This happens in my Ubuntu VM and the log usually have these 2 messages

[  148.462686] blk_update_request: I/O error, dev fd0, sector 0
[  148.462735] floppy: error -5 while reading block 0

To get rid of those messages:

echo "blacklist floppy" | sudo tee /etc/modprobe.d/blacklist-floppy.conf

sudo rmmod floppy

sudo update-initramfs -u

After reboot, you can verify that floppy module (lsmod | grep floppy) no longer get loaded automatically and there is no such messages in the log anymore.