How to get the latest commodity pricing in Google Sheet | Minhaz’s Blog

[utterances-bot]

How to get the latest commodity pricing in Google Sheet | Minhaz’s Blog

I use Google Sheets for tracking my expenses, assets, and liabilities. Google Sheet has a 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 be writing about how to write an AppScript that will allow you to crawl such information from some API repository and mildly mention the option to cache some data if there is a limited API call per hour. I’ll be using the example of how to get the latest gold price, silver price, and platinum price in USD in Goo

https://blog.minhazav.dev/how-to-get-commodity-price-in-google-sheet/

[mariusbjansen]

Great articles. I tried it out and it worked great for me. Thanks for sharing!

[jparrie]

So I copied that code, added my API and when I run it all I see is "Execution started, execution completed", no prices?

[KSMEDM]

Updated 8/22/21: =Dollar(Index(ImportHTML("https://www.apmex.com/silver-price","table",9),2,2)) =Dollar(Index(ImportHTML("https://www.apmex.com/GOLD-price","table",9),2,2))

[jparrie]

Awesome, thanks. Could you tell me how you know that? I looked at the HTML code and I can't find it anywhere?

[KSMEDM]

I pretty much saw a change in the web page then adjusted the url then I poked until it worked.

[maxhugen]

Great, thanks. BUT, running the function =metalsApi("XAU") in Sheets returns error Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 3). Which is weird, as it Runs just fine in the Script Editor, and there's no call to SpreadsheetApp.getUi() in this script file. Struggling to figure this out, as a GAS newbie. :(

[mebjas]

@jparrie

You need to get an API key from metals-api.com and replace

const apiKey = "<Your api key here - get it from metals-api.com>";

with that, did you do that?

[mebjas]

@jparrie @KSMEDM

Updated 8/22/21: =Dollar(Index(ImportHTML("https://www.apmex.com/silver-price","table",9),2,2)) =Dollar(Index(ImportHTML("https://www.apmex.com/GOLD-price","table",9),2,2))

Awesome, thanks. Could you tell me how you know that? I looked at the HTML code and I can't find it anywhere?

While this is cool, the problem with this approach is the author can change their DOM structure and thus this will require us to figure out the new structure again. (Also, I find it non intuitive).

There was another way I tried which would load the whole HTML in app script as text and get the information but that is like scraping, if it's interesting I can share the information.

[mebjas]

@maxhugen

Great, thanks. BUT, running the function =metalsApi("XAU") in Sheets returns error Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 3). Which is weird, as it Runs just fine in the Script Editor, and there's no call to SpreadsheetApp.getUi() in this script file. Struggling to figure this out, as a GAS newbie. :(

Yea this is surprising, as there is not such API call. Maybe you can change the function name to something else and try calling to make sure it's not coming from something else you might have tried before?

[mebjas]

Just today I came across this

I just wrote another article that uses scraping the HTML data as text and scrape a certain known format (not very clean) if it helps - https://blog.minhazav.dev/getting-gold-price-in-google-sheet/

[ghost]

FYI. A NEW price list on https://metals-api.com/pricing# The Free tier API key allows no longer "50 times per hour", which would have been great, but now it's only 50 API calls/mo and only 1 hour updates. This makes a huge difference. Perhaps you may wish to update your text. However, the information on your web page is great. Thank you.

[kesor]

You can also use the ImportXML function in Google Sheets to do this, like so:

=DOLLAR(REGEXREPLACE(ImportXML("https://www.apmex.com/silver-price","//p[@class='price']/text()"),"[^.\d]*",""))

[emlickwid]

this is excellent, thanks very much Minhaz. I am very new to coding and i need to use the historical end point. I want to pull in the last closing price for commodities (or yesterdays price!) but i want to be able to enter the date in a field (B1) in google sheets to choose the date - this 'var yesterday' is not working - any suggestions would be good

var yesterday = SpreadsheetApp.getActiveSheet().getRange('B1').getValue();
const apiKey = "<my key>";
const url = "https://metals-api.com/api/" + yesterday + "?access_key=" + apiKey;

thanks in advance em

[kamilkobak]

Silver:

=regexextract(IMPORTXML("https://www.apmex.com/silver-price","//*[@class='currencyExchange']"),"([\.0-9]{2,6})")

Gold:

=regexextract(IMPORTXML("https://www.apmex.com/GOLD-price","//*[@class='currencyExchange']"),"([,.0-9]{2,9})")

[ctu1121]

Silver:

=regexextract(IMPORTXML("https://www.apmex.com/silver-price","//*[@class='currencyExchange']"),"([\.0-9]{2,6})")

Gold:

=regexextract(IMPORTXML("https://www.apmex.com/GOLD-price","//*[@class='currencyExchange']"),"([,.0-9]{2,9})")

Thanks. After input above link on Googesheet, I can see gold and Silver price. However, the price keep the same, no change as real price. Please advise how to setup. Thanks.

[kamilkobak]

The importXML() is updating every 2 hours by it self.

[ctu1121]

understood. thanks.

Kamil Kobak @.***> 於 2023年2月9日 週四 下午2:37寫道：

The importXML() is updating every 2 hours by it self.

— Reply to this email directly, view it on GitHub https://github.com/mebjas/mebjas.github.io/issues/16#issuecomment-1423708078, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAICUZUXVHBZ6KBW6S6I6B3WWSGEBANCNFSM4ZRO2VEQ . You are receiving this because you commented.Message ID: <mebjas/mebjas. @.***>

[muratsever]

https://metals-api.com/api/latest?access_key= returns this: {"data":{"success":false,"error":{"code":502,"type":"symbols_not_string","info":"The 'symbols' parameter must be a string."}}} Any idea why? Thanks

[n3ckf]

i think metals.api changed their API URL slightly. I got it to work by modifying the function slightly as follows:

const url = "https://metals-api.com/api/latest?access_key=" + apiKey + "&base=USD" + "&symbols=" + symbol;

Note you have to pass in the base currency (that may default, i didnt try it) and the symbols you want. (which for this instance is just the one you're looking up).

[NetSpeedz]

Just tried this script with the modification given by n3ckf and received a formula parse error. I'm assuming I'm missing a parameter that needs to be parsed but don't know enough about Google Sheet scripts to fully troubleshoot.

Any assistance would be appreciated.

[n3ckf]

my exact script is: /**

• Returns the value of given @.** symbol} from metals-api. / function metalsApi(symbol) { if (!symbol || symbol === "") { return "symbol is mandatory" } // TODO(Minhaz): Add caching support, limited calls/hour. const apiKey = ""; const url = "https://metals-api.com/api/latest?access_key=" + apiKey + "&base=USD" + "&symbols=" + symbol; 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; }

On Thu, Dec 21, 2023 at 12:23 PM Netspeedz @.***> wrote:

Just tried this script with the modification given by n3ckf and received a formula parse error. I'm assuming I'm missing a parameter that needs to be parsed but don't know enough about Google Sheet scripts to fully troubleshoot.

Any assistance would be appreciated.

— Reply to this email directly, view it on GitHub https://github.com/mebjas/mebjas.github.io/issues/16#issuecomment-1866678289, or unsubscribe https://github.com/notifications/unsubscribe-auth/AP46PRHG5NJRKTHECO37UX3YKRWARAVCNFSM4ZRO2VE2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TCOBWGY3DOOBSHA4Q . You are receiving this because you commented.Message ID: <mebjas/mebjas. @.***>