How to retrieve KLSE, SGX, and gold prices in Google Sheets (Updated for 2022Q1)

2 years ago

source link: https://anonoz.github.io/finance/2022/02/28/google-sheets-klse-sgx.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

How to retrieve KLSE, SGX, and gold prices in Google Sheets (Updated for 2022Q1)

For Bursa Malaysia (KLSE) and Singapore Exchange (SGX), we can scrape from i3investor websites. This is how.

KLSE & SGX

This is the new formula for KLSE after the site revamp:

=index(ImportXML("https://klse.i3investor.com/web/stock/overview/5176?randomstring", "//div[contains(@id, 'stock-price-info')]//p[2]"), 1, 1)

And this is for SGX:

=index(ImportXML("https://sgx.i3investor.com/servlets/stk/g3b.jsp", "//td[contains(@class, 'big16')]"), 1, 1)

Just remember to replace the stock code with whatever you want, the part right before .jsp.

Gold Prices

You can use this formula to approximate USD per oz:

=GOOGLEFINANCE("GLD", "price")*10/POW(0.996,YEARFRAC(TODAY(), "18/11/2004", 1))

GLD is the ticker for SPDR gold trust ETF, the largest one out there. They charge 0.4% per annum since inception.

Recommend

anonoz.github.io 3 years ago
Cache

How to retrieve KLSE, SGX, and gold prices in Google Sheets (Updated for 2020)

How to retrieve KLSE, SGX, and gold prices in Google Sheets (Updated for 2020) For Bursa Malaysia (KLSE) and Singapore Exchange (SGX), we can scrape from i3investor webs...

yy-mylifediary.blogspot.com 3 years ago
Cache

KLSE TALK - 歪歪理财记事本

2021之股票买贵了，不一定是坏事

Github github.com 2 years ago
Cache

GitHub - levinunnink/html-form-to-google-sheet: How to submit HTML forms to Goog...

Submit a HTML form to Google Sheets How to submit a simple HTML form to a Google Sheet using only HTML and JavaScript. Updated for Google Script Editor 2021 Version. This example shows how to set up a mailing list for...