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

Question

Following this post, where I had listed some of the interesting ETF¹s 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 ISIN² 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 1

This post answers the basic question of how to web scrape via Google Sheets and why it's not working.

Some additional information:

If a webpage changes its tags or how it renders data then any IMPORT function you previously wrote to pull data won't work. (Examples of two methods that previously worked but have since failed due to sites rendering data via Javascript and therefore making data unavailable via Google Sheets IMPORT Functions: NASDAQ site and Yahoo Finance)

In your Google Sheets, the reason you're getting "N/A" for =GOOGLEFINANCE("AMS:IWDA", "return1") and similar performance functions is because those attributes are specific to Mutual Funds and the function does not calculate them for ETFs or stocks.

For Stocks and ETFs, the GOOGLEFINANCE function allows you to pull historic data for a ticker and returns a range of Dates and a small set of attributes (i.e., open, close, volume). The post you linked to uses this method in the spreadsheet it shared.

Below is a breakdown of how to use the historic data feature of the GOOGLEFINANCE function.

This line of code would attempt to pull 5 years of weekly data from the current date and returns a range of dates and close prices for this one ticker: =GOOGLEFINANCE("AMS:IWDA","close",today()-365*5,today())

You can add a QUERY function to it to return only the attribute column: =QUERY(GOOGLEFINANCE("AMS:IWDA","price",today()-365*5,today()), "Select Col2")

Once you have the column of values, you can then apply math functions (i.e., Average, Standard Deviation, Mode, etc). For example, this line of code would return the 5-year average weekly close price and give you one value in one cell so it can be used for other tickers: =AVERAGE(QUERY(GOOGLEFINANCE("AMS:IWDA","price",today()-365*5,today()), "Select Col2"))

However you're defining performance, the above could help because wheater you pull a long-range or a single day, the GOOGLEFINANCE function returns the Date and Attribute column. The QUERY function is just one method to return only the attribute column so you can use it in calculations for a single row instance.