Tech Blog

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!

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

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.

Some existing approaches

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

CommandContext
=GOOGLEFINANCE(“NASDAQ:MSFT”)for Microsoft
=GOOGLEFINANCE(“NASDAQ:AAPL”)for Apple
=GOOGLEFINANCE(“NASDAQ:GOOG”) for Google
=GOOGLEFINANCE(“NASDAQ:FB”)for Meta (former Facebook)

Some NYSE examples

CommandContext
=GOOGLEFINANCE(“NYSE:ABT”)for Abbot Laboratories
=GOOGLEFINANCE(“NYSE:PINS”)for Pinterest Inc

Some currency examples

CommandContext
=GOOGLEFINANCE(“SGDINR”)SGD to INR conversion rate
=GOOGLEFINANCE(“USDSGD”)USD to SGD conversion rate

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.

Getting data values with AppScript

Photo by Jason Briscoe on Unsplash.

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.

Screenshot of Google Sheets by Author.

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.

/**
* Returns the value of given {@param symbol} from metals-api.
*/
function metalsApi(symbol) {
if (!symbol || symbol === “”) {
return “symbol is mandatory”
}
// This is a stub implementation
return symbol;
}

After saving you’d see output something like this

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

[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.

/**
* Returns the value of given {@param symbol} from metals-api.
*/
function metalsApi(symbol) {
if (!symbol || symbol === “”) {
return “symbol is mandatory”
}
// TODO(Minhaz): Add caching support, limited calls/hour.
const apiKey = “<Your api key here – get it from metals-api.com>”;
const url = “https://metals-api.com/api/latest?access_key=” + apiKey;
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
Logger.log(response);
if (data.success !== true) {
return “No Success”;
}
/**
* Data format {…, “rates”: {“XAU”: 0.22}, …}
*/
let value = parseFloat(data.rates[symbol]);
if (Number.isNaN(value)) {
return “NaN”;
}
// For this API the value is returned in this format.
return 1 / value;
}

view rawMetalApis.gs hosted with  by GitHub

After saving you’d see output something like this

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

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

/**
* Returns the value of given {@param symbol} from metals-api.
*/
function metalsApi(symbol) {
if (!symbol || symbol === “”) {
return “symbol is mandatory”
}
// If there is data in cache, return directly.
const cacheId = “MetalsApiCache_XXX”;
let cache = CacheService.getDocumentCache();
var cached = cache.get(cacheId);
if (cached != null) {
var data = JSON.parse(cached);
if (data.success) {
let value = parseFloat(data.rates[symbol]);
if (!Number.isNaN(value)) {
// For this API the value is returned in this format.
return 1 / value;
}
}
}
const apiKey = “<Your api key here – get it from metals-api.com>”;
const url = “https://metals-api.com/api/latest?access_key=” + apiKey;
var response = UrlFetchApp.fetch(url);
var data = JSON.parse(response.getContentText());
Logger.log(response);
if (data.success !== true) {
return “No Success”;
}
// Put API response text to cache with timeout of 24 hours = 60*60*5 seconds.
// Note: this value can be made as a variable too with some default value for
// different cache duration.
let cacheDuration = 60 * 60 * 24;
cache.put(cacheId, response.getContentText(), cacheDuration);
let value = parseFloat(data.rates[symbol]);
if (Number.isNaN(value)) {
return “NaN”;
}
// For this API the value is returned in this format.
return 1 / value;
}

After saving you’d see output something like this

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

** This is just for reference.

What else can you do beyond this

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.

Original Source: “How to get the latest commodity pricing in Google Sheet”, Data Driven Investor, May 23, 2022

https://www.datadriveninvestor.com/2022/05/23/how-to-get-the-latest-commodity-pricing-in-google-sheet/

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button