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.
You are watching: Can Google Sheets Pull Data From Sgx In Google Finance
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.
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.
So how to do this?
See more : Where To Buy Renq Finance Crypto
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)
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.
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.
See more : Does Gm Finance Lease Buyouts
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)
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
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.
Source: https://tholansonnha.com
Category: Finance
