0

I've got an extensive google sheet file, one of the sheets which are called "Ticker List" has a range of tickers on it. I would like there to be a column in that range which presents the date of the most recent announcement only (column "O"). The ticker symbol is in column "A".

Since I do not need the data to be backdated before today, I originally tried to create a importxml function, which goes to "today's announcements" on ASX website. URL: "https://www.asx.com.au/asx/statistics/todayAnns.do". Although the XPath confuses me.

None

I want the code to scan that website, if a ticker that is in my range (column A) has an announcement today, then to update column O to today's date, and keep that date there until the next announcement. The order of my current ticker range, is not update-able, although I do have a sheet in my file that is called "backend" and has a lot of queries and ranges to help sift through data.

player0
  • 124,011
  • 12
  • 67
  • 124
bart.s
  • 67
  • 6
  • 1
    share a copy of your sheet with example of desired output – player0 Nov 06 '19 at 17:37
  • 1
    No problem, i've copied the sheet. I need Column O ("News date") to show the most recent date for an announcement. I don't need it to show news dates earlier than 7th of November 2019, just moving onwards, if a Ticker/stock has an announcement and is on the "today's announcement" ASX webpage, then to update column O with the current date, and keep the date there until the next announcement. https://docs.google.com/spreadsheets/d/10t_6xhSA4DyI9LrnKrQp3gp0HUS0p8SEWxzKgCCHaQU/edit?usp=sharing – bart.s Nov 07 '19 at 04:07
  • So more specifically - is your problem the correct xpath for importing the tickers which have "today's announcement" or how to update Column O to reflect the tickers and dates imported (or both)? – Jack Fleeting Nov 07 '19 at 11:41
  • Specifically my end result is that i want to have the date of the most recent announcement for that ticker in Column O. I'm stuck on how i can obtain that. – bart.s Nov 08 '19 at 02:42

1 Answers1

1

You can create an auxiliary sheet that imports the whole table as rows.

Then you create an Apps Script function that reads this auxiliary sheet and updates the fields accordingly.

You then schedule that to run on a time-based trigger, every day.

ZektorH
  • 2,680
  • 1
  • 7
  • 20
  • Okay that sounds pretty good, i think i can do step 2 and 3 on my own, but step 1, which imports the whole table as rows is where i am stuck. – bart.s Nov 08 '19 at 02:43
  • 1
    @bart.s There is no way to do this in a straightforward way. The way the page is done, it does ajax calls and has [malformed structure everywhere](https://validator.w3.org/nu/?doc=https%3A%2F%2Fwww.asx.com.au%2Fasx%2Fstatistics%2FtodayAnns.do). You can try to use an external service to parse this page for you or create a script to do so manually. – ZektorH Nov 08 '19 at 09:08