How to get the latest commodity pricing in Google Sheet

Google sheet has easy macros to get price of US stocks and currency, but not for gold or silver. Here’s easy & free trick to get latest prices!

I use Google Sheets for tracking my expenses, assets, and liabilities. Google Sheet has first-class support for querying the latest values of stocks on US-based exchanges like NASDAQ or NYSE. On the other hand, it doesn’t have as well support for other exchanges like SGX: Singapore Exchange.

To overcome this we are supposed to run awkward hacks like crawling yahoo finance pages.

In this article, I’ll share how to write an AppScript code that will allow you to crawl such information from certain API repositories. Some of these API services only provide limited API call per month —so we shall look at the caching approach as well.

I’ll be using the example of

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

This can be easily extended to solve questions on how to get the latest gold prices in India or the latest price of Indian stocks or Singapore Exchange stocks.

Disclaimer: In this article I am not giving any financial advices. Any financial decision driven by using the approaches mentioned in this article are sole responsibility of every individual. Invest reasonably!

Also, the examples I have used in this article are arbitrary. They do not represent any form of financial advise.

I am not affiliated with metals-api.com.

Using GOOGLEFINANCE macro

There is a macro in Google Sheets, called =GOOGLEFINANCE(). You can use it to get latest price Microsoft stock listed on Nasdaq with =GOOGLEFINANCE("NASDAQ:MSFT"). Here are some more example

GOOGLEFINANCE function supports more options like getting values over a period of time for a given stock with different attributes. You can read more about it on the Google support page.

But it has limitations like it doesn’t provide spot price of commodities (as of 06/05/2022). Please share over comment if it gets updated.

ImportHtml and ImportXml

ImportHtml and ImportXml are macros that allow you to crawl a certain web page or XML respectively over http/s, parse the structured data and get some values from it if you can pinpoint the position of the content in the HTML document.

An example of how I use this is for getting the latest values of some Singapore Exchange: SGX listed stocks. An example of how to do this would be

=IMPORTXML("https://sg.finance.yahoo.com/quote/RW0U.SI",
    "//span[@class="Trsdu(0.3s) Fw(b) Fz(36px) Mb(-4px) D(ib)"]")

This allows us to get the value of stock RWOU.SI which is the ticker for Mapletree North Asia Commercial Trust (RW0U.SI).

Another example of this would be

=Dollar(Index(ImportHTML("http://www.apmex.com/spotprices/silver-price","table",8),2,2))

This allows us to get the latest spot value of Silver in USD per ounce. The Dollar function is used to convert the text/decimal value to Dollar directly.

In the past I was using this to get the price of metals / commodities. It’s painful to get this to work as you need to pin point which table in the HTML data of an arbitrary website has the right data you are looking for. Weeks after spending time on this you realize the website decided to change the structure a little and your macro is broken.

I needed something more robust and easier to work with. That’s exactly where AppScript comes in.

Google Apps Script lets you do new and cool things with Google Sheets. You can use Apps Script to add custom menus, dialogs, and sidebars to Google Sheets. It also lets you write custom functions for Sheets, as well as integrate Sheets with other Google services like Calendar, Drive, and Gmail.

Source: developer.google.com — Read more about AppScript here. The syntax are fairly identical to JavaScript.

You can create a new script in Google Sheets by going to Tools > Script Editor.

In this article, I would be using API from metals-api.com which provides the latest spot prices for commodities like Gold, Silver, Platinum, Rhodium etc. The API returns the data in JSON format.

Metals-Api has a free tier which allows us to query up to 50 times an hour. This should work fine for most of us. In interest of saving money — later I’ll share how we can cache the data in AppScript to ensure we don’t exceed this API limit.

Let’s get started!

[1] Create a new script and a stub function

Got to script editor as shared above and create a new function that takes some inputs and return the expected output. This new function can later be directly called from Google Sheets as a macro.

After saving you’d see output something like this

[2] Call the API and return the response

Now let’s update our AppScript code to make the API calls to metals-api.com. For this you need an API key. For this

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

Now let’s update the AppScript we wrote so far to make the API call. Replace the API key section with your own key.

After saving you’d see output something like this

[3] (Optional) Add caching support

As mentioned above, the platform we are using in this example has limited free API calls per hour. I expect the same from most of the platforms out there. In such cases we can leverage the caching layer support in AppScript. This might lead to slightly delayed data (based on cache timeout we set) but might just work out for our use-cases.

To add caching update the script like this

After saving you’d see output something like this

** This is just for reference.

You can use the same approach to do different things like:

• 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

Basically get any value you want from the internet in numeric format into Google Sheet if you know a reliable source of data or preferably an API endpoint.