Minhaz Vadakekara I am Software Engineer who is looking to retire ASAP (FIRE). I invest a lot of my money on different derivatives and tend to write about data driven investment at times. This publication looks like a good fit to publish.

How to get the latest commodity pricing in Google Sheet

4 min read

Screenshot of Google Sheet by Author using a custom hand written function.

How to get the latest gold price in Google Sheet or How to get the latest metal price in Google Sheet in general.

Some existing approaches

Using GOOGLEFINANCE macro

ImportHtml and ImportXml

=IMPORTXML("https://sg.finance.yahoo.com/quote/RW0U.SI",
    "//span[@class='Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)']")
=Dollar(Index(ImportHTML("http://www.apmex.com/spotprices/silver-price","table",8),2,2))

Getting data values with AppScript

Photo by Jason Briscoe on Unsplash.
Screenshot of Google Sheets by Author.

[1] Create a new script and a stub function

Response with the skeleton code so far. Screenshot by the Author.

[2] Call the API and return the response

  • Register yourself with metals-api.com
  • Copy the API-Access key from the dashboard

Result of running the new macro with updated script. Screenshot by the Author. Note the values are from when this article was originally written.

[3] (Optional) Add caching support

Result of running the new macro with updated script. Screenshot by the Author. Note the values are from when this article was originally written.

What else can you do beyond this

  • Get the price of Gold in India in specific cities.
  • Get the price of some Stock on the Singapore Exchange.
  • Get the price of some stock in BSE or NSE.
  • Get the price of a certain Mutual Fund or ETF
Minhaz Vadakekara I am Software Engineer who is looking to retire ASAP (FIRE). I invest a lot of my money on different derivatives and tend to write about data driven investment at times. This publication looks like a good fit to publish.

Leave a Reply

Your email address will not be published.