Parse JSON data from ASX into Google Sheets for Exchange Traded Products - not companies

I had asked this question on the StackOverflow website – but no luck.

I am trying to develop a Google Sheets-based portfolio tracking sheet that is able to retrieve daily prices for the securities in the Australian (ASX) and US markets.

For US market securities the GoogleFinance function works well enough. However for the ASX the ability for GoogleFinance to retrieve information is a bit hit and miss.

Ruben had asked a similar question to which Ian Finlay provided a solution that works in most instances, i.e. listed companies, but not for Exchange Traded Products that such as PMGOLD.

Ian Finlay‘s solution using apps script to parse json data was:

<code>
function AsxPrice(asx_stock) {
  var url = "https://www.asx.com.au/asx/1/share/" + asx_stock +"/";
  var response = UrlFetchApp.fetch(url);
  var content = response.getContentText();
  Logger.log(content);
  var json = JSON.parse(content);
  var last_price = json["last_price"]; 
  return last_price;
}

For a ‘normal’ company such as NAB = asx_stock, the script works well, however for a exchange traded product such as PMGOLD, it does not.

With some basic searching an experimentation, the reason seems to be that the url that is in the script does not point to the information required.

For NAB = asx_stock, the url reponse is

{"code":"NAB","isin_code":"AU000000NAB4","desc_full":"Ordinary Fully Paid","last_price":23.77,"open_price":24.11,"day_high_price":24.21,"day_low_price":23.74,"change_price":-0.15,"change_in_percent":"-0.627%","volume":1469971,"bid_price":23.75,"offer_price":23.77,"previous_close_price":23.92,"previous_day_percentage_change":"-1.239%","year_high_price":27.49,"last_trade_date":"2021-01-29T00:00:00+1100","year_high_date":"2020-02-20T00:00:00+1100","year_low_price":13.195,"year_low_date":"2020-03-23T00:00:00+1100","year_open_price":34.51,"year_open_date":"2014-02-25T11:00:00+1100","year_change_price":-10.74,"year_change_in_percentage":"-31.121%","pe":29.12,"eps":0.8214,"average_daily_volume":6578117,"annual_dividend_yield":2.51,"market_cap":-1,"number_of_shares":3297132657,"deprecated_market_cap":78636614000,"deprecated_number_of_shares":3297132657,"suspended":false}

However, for PMGOLD = asx_stock, the url reponse is:

{"code":"PMGOLD","isin_code":"AU000PMGOLD8","desc_full":"Perth Mint Gold","suspended":false}

Conducting some relatively ‘non-code qualified person’ type research, looks like the actual url for an Exchange Listed Product should be:

https://www.asx.com.au/asx/1/share/PMGOLD/prices?interval=daily&count=1

The url reponse for this is:

{"data":[{"code":"PMGOLD","close_date":"2021-01-28T00:00:00+1100","close_price":24.12,"change_price":0.19,"volume":98132,"day_high_price":24.2,"day_low_price":23.9,"change_in_percent":"0.794%"}]}

When I substitute this url into Ian Finlay‘s code and rename the var as ‘close_price’ instead of ‘last_price’ there is nothing retrieved. The code used is:

function AskPrice(asx) {
  var url = "https://www.asx.com.au/asx/1/share/"+ asx +"/prices?interval=daily&count=1";
  var response = UrlFetchApp.fetch(url);
  var content = response.getContentText();
  Logger.log(content);
  var json = JSON.parse(content);
  var  data = json["data"]; 
  return data;
}

I suspect this is due to the structure of the url response being formatted differently for the two different url types. Maybe nested? – I am not sure.

Can someone please help point out what mistake(s) I am making?

Thank you