Screen Scraping Functions 2021

[RexM]

Hello everyone

I am trying to pull daily trailing returns from Morningstar into Google sheets - for ETFs.

I was able to find the earlier post below that works for a number of ETFs - many thanks!

However, I was unable to get the formulae to work for the ETF: ARKG

Any advise is appreciated.
***********************************************************

If anyone is using the import xml from Morningstar and wants to get ETF data, they use a different table. This worked for VTI:

=IMPORTXML("http://performance.morningstar.com/perform/Performance/cef/trailing-total-returns.action?&t=ARCX:VTI&region=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype=", "//table/tbody[1]/tr[1]/td[5]")


With the concatenate addition to pull the ticker from a cell:

=IMPORTXML(Concatenate("http://performance.morningstar.com/perform/Performance/cef/trailing-total-returns.action?&t=ARCX:",K28,"&region=usa&culture=en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSecId=&benchmarktype="), "//table/tbody[1]/tr[1]/td[5]")[/QUOTE]

 

[target2019]

Wild guess, but I think the registered symbol you have in the formula is incorrect.

Since you did not post a link to the original source, I couldn't check this.

 

[RexM]

The formulae below: YTD results, where T25 = ARKG, gives a parsing error

however if you try another ETF symbol - ARKK it works OK

=IMPORTXML(Concatenate("http://performance.morningstar.com/perform/Performance/cef/trailing-total-returns.action?&t=ARCX:",T25,"&region=usa&culture= en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&alig n=d&annlz=true&comparisonRemove=false&benchmarkSec Id=&benchmarktype="), "//table/tbody[1]/tr[1]/td[5]")/100

 

[cathy63]

Your URL has some extra "stuff" in it. You don't need the ampersand before the first parameter after the ?, "...action?&t=..." can just be "....action?t=..." and it doesn't seem to need the "ARCX:" to specify the exchange either.

I didn't try to parse this with importxml, but it looks like it gives the same layout as the ones you say are working, so try this.

http://performance.morningstar.com/...nRemove=false&benchmarkSec Id=&benchmarktype=

http://performance.morningstar.com/perform/Performance/cef/trailing-total-returns.action?t=ARKG&region=usa&culture=%20en-US&cur=&ops=clear&s=0P00001MK8&ndec=2&ep=true&align=d&annlz=true&comparisonRemove=false&benchmarkSec%20Id=&benchmarktype=

 

[RexM]

Hi Cathy,

Many thanks for your kind assistance. Your recommendation did the trick!

I have went back and made the edits for all ETFs.

Cheers
RexM