-1

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

5
  • 1
    Change 'var data = json["data"];' to 'var data=json.data[0].close_price'. In essence what the statement means is get me the 'close_price' attribute of the 0th element of the array called data in the response. – Ironluca Feb 5 at 9:29
  • Thank you Ironluca - that worked perfectly. – op420 Feb 8 at 7:39
  • @Ironluca you should post this as an answer, so I can upvote it – Fred Stark Feb 15 at 21:24
  • Also this is 100% a stack overflow question, not personal finance... It's about accessing a part of a JSON response! They're just really unfriendly to anyone that doesn't have years of experience in development over there – Fred Stark Feb 15 at 21:25
  • @op420, this indeed is a Stack Overflow question, I am a contributor in the forum myself, sorry to hear that you did not get response to your question. Nonetheless, i have added the answer. – Ironluca Feb 16 at 4:52
0

In the JSON structure, the data is in an element of an array. Change 'var data = json["data"];' to 'var data=json.data[0].close_price'. In essence what the statement means is get me the 'close_price' attribute of the 0th element of the array called data in the response.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.