How to track Singapore, US, HK stocks using Google Sheets?

I was googling recently to see how to import SG stock price into Google Sheets, and found that it seems like we can’t really import the stock price from SGX website.

What I did found was that it was actually easier to track overseas US/HK/UK stocks prices using Google Sheets, compared to tracking stock prices in SGX or the Bursa.

Using GOOGLEFINANCE to track US Stock Prices

For US stocks price tracking, one can simply use GOOGLEFINANCE function found inside Google Sheets.

=GOOGLEFINANCE(“stock symbol”,”price”)

For example, if you want to track apple stock price, type =GOOGLEFINANCE(“AAPL”,”price”) into the cell.

Example: Using GOOGLEFINANCE to import stock price of APPLE

Using IMPORTHTML to track US stocks

But what if you want to track more data like the dividends, PE and other financial ratios. I found that you can import the stock data found at Finviz.com into Google Sheets. See below for the stock information pertaining to Apple (AAPL). The stock price (bottom right), dividends, PE and other related financial ratios can be imported into Google Sheets.

Apple stock information at Finviz.com

So how to do this?

The first thing is to find the table. Through some trial and error, the table is table 8. From inputting the below into the cell, we are able to import all the financial data into Google Sheets. [B14 is the stock ticker for APPLE]

=(importhtml(“http://finviz.com/quote.ashx?t=”&B14 ,”table”,8)

Example: importing all stock information from Finviz.com

But this is not what we want. We want to find the market price, dividend etc. So we need to reference the relevant row and column to display the information required. For market price, it is at row 11 column 12. Dividends is at row 7 column 2. After entering

=substitute(index(importhtml(“http://finviz.com/quote.ashx?t=”&B14,”table”,8),11,12),”*”,””)

we can reference and import the stock price. The SUBSTITUTE function is to replace the asterisk * with blank.

Example: importing and displaying APPLE stock price and dividends

Do note that the table may shift or disappear if the webpage changes layout.

Using GOOGLEFINACE to track HK/UK Stock Prices

For HK/UK stocks, we can use GOOGLEFINANCE function to get the price. If you want to track Alibaba, just type =GOOGLEFINANCE(“9988″,”price”) into the cell

Using IMPORTHTML to track SG stocks

The problem comes when i tried to use GOOGLEFINANCE for SGX stocks. I tried to use GOOGLEFINANCE(“Z74″,”price”) to get Singtel stock price. But it doesn’t work. Seems like Google is not unable to access SGX data or something.

The work-around is to use a similar method like for Finviz.com. I explored Yahoo Finance but the best I could get is the previous closing price, using the below formula

=index(importhtml(“https://sg.finance.yahoo.com/quote/Z74.SI?p=Z74.SI&.tsrc=fin-srch”,”table”,1),1,2)

Example: Singtel previous closing price at Yahoo Finance

I was able to get the market price from Shareinvestor.com though, using the below formula.

=REGEXREPLACE(index(IMPORTHTML(“http://www.shareinvestor.com/fundamental/factsheet.html?counter=O39.SI”,”table”,5),1,1),”[^\d]”,””)/1000

Example: Tracking SG stocks using IMPORTHTML

While this may work at the time of this post, it may or may not continue to work in the future. So, please do take note. Also, I believe the method can also work for Bursa stocks.

You can find the above Tracking SGX portfolio Google Sheet here.

Hope you find these information useful. Thanks.

Author: Ci2fi

Average salaried man looking to FI in 10 to 20 years.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s