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.

Ubuntu download deb packages without installing

To download without installaing, just use the following command

apt-get -d install package

Once done, you can go /var/cache/apt/archives to retrieve the packages you had downloaded.

How to print Gmail email without header information

First click on the printer button

Click cancel when prompted

Go to Developer Tools

Once you in Deveoper tools mode, you will notice there is a new pane below that show all the HTML code. Expand bodycontainer div class, when you mouse over the first table element, you will notice the section on top will be highlighted as well. This means that particular HTML code is the one that generated the highlighted section. Delete this table element.

And you will notice that Gmail header is no longer there.

Repeat these steps with other element such as

hr - to remove the seperator
first table element under maincontent div class - to remove message header
first and second tr element under message table - to remove sender and receipeint email.

After deleting the unwanted header information, you will get a clean message content. And you can proceed to print it.

How to test UDP port is responding

Using the following command will do the trick.

# nc -zuv 3 192.168.123.123 5140
Connection to 192.168.123.123 5140 port [udp/*] succeeded!

Linux Biscuit!