0

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

op420
  • 1

1 Answers1

0

Yes, the structure is different. I've done this in Python so I know exactly your problem.

The 1/share API (first example) returns a simple dictionary of name:value pairs so you can easily reference the value.

The "prices" version gives you a list of daily values under the data element. Even though your example only returns one day, it is a list with one value. (Notice the [square] brackets around it?

So you need to go to the "data" element to get the list, then reference the first (only) item of the list and then reference close_price.

I don't know this language but it's probably something like:

var  data = json["data"][0]["close_price"];

Let me know if this helps.

InnocentBystander
  • 541
  • 4
  • 12