How to fetch the ETF's data into a Google Spreadsheet?

Foad picture Foad · May 2, 2020 · Viewed 7.1k times · Source

Following this post, where I had listed some of the interesting ETF1s I had found on some of the relevant forums, I received some valuable criticism. Now I want to use a Google spreadsheet to get the updated information of those ETFs in one place, and maybe use it for personal portfolio management. What I need:

  • preferably fetching data using the ISIN2 code, as the tickers/symbols are not really reliable and consistent between different databases and markets
  • The information I need are:
    1. the current value of the ETF
    2. the 1-year, 3-year, and 5-year performances/returns
    3. current fund size
    4. TER: Total Expense Ratio
    5. TD: Tracking Differences
    6. 1-year volatility

For example, consider the "iShares Core MSCI World" ETF which is listed as "IWDA" on "Euronext Amsterdam" and an ISIN code of "IE00B4L5Y983". What I have read so far:

  1. From this page and this post, I have used the GOOGLEFINANCE function to fetch the current price and some other information from Google Finance. However, I don't know how to fetch/calculate the 1/3/5-year performances, the fund size, TER, and the TD. Moreover, according to this comment, Google Finance, like many other Google products seem to be dying. So it would be great to have a more sustainable solution.
  2. This post, suggests some syntax using index and IMPORTXML functions to fetch from etf.com. And this comment uses the same functions to fetch from nasdaq.com. But I couldn't make either to work, mainly because I don't know what symbol should I use.
  3. This and this comments suggest the DGET and IMPORTDATA functions, to fetch data from api.iextrading.com, but I could not make it to work either.
  4. This post and many other posts on the bogleheads.org forum, use all variants of syntax using the importHTML function to import and pars HTML tables from many different websites. There are many reliable websites that if I learn how to use this function I could fetch data from.

I would appreciate it if you could help me know how I can get the above information. Thanks for your kind support in advance.

P.S.1. From here, I was able to use the commands:

=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", C5), "table", 1),1,11), " "), 1, 3)

and

=INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", C5), "table", 1),1,13), " "), 1, 2)

to pull Total fund size and TER consequently. However, they do not deliver data fro most of the ETFs and also I'm not sure how reliable this website is.

Footnotes:

1: Exchange-Traded Fund

2: International Securities Identification Number

Answer