Spreadsheet - Fetching ETF expense ratio, Bid/Ask Spread & Turnover

[djsykora]

I would like to create a spreadsheet using Google Sheets or Excel to analyse and optimize my portfolio in terms of expenses, both on-going and trading related. Is there an Excel or Google Sheets function or a method that I can use to obtain these numbers for a list of ETFs? I know I can manually get these numbers from places like etf.com and Morningstar, but I would like to automate it so others can used the spreadsheet and so I can update it periodically because expenses, turnover, and bid/ask spreads change over time. Several brokers have commission free ETFs available. If I could get the values automatically it would make my work a lot easier and it could turn into something to share with others.

Background - I currently have about 14 asset classes in my portfolio covering domestic, international, Developed and Emerging markets, bonds, equities, commodities, and REITs. I am looking to place the portfolio in one of four brokers that have substantial commission free ETFs.

So, does anyone know of any spreadsheet functions that can help me in this endeavor? I know there are functions that can be used to get prices of ETFs, but how about expense ratios, bid/ask spreads, and turnover?

Thanks in Advance

[siamond]

Check here:

https://support.google.com/docs/answer/3093281?hl=en

Do check manually in a few cases if it works well. GoogleFinance() results aren't always that reliable.

[sheetzilla]

In Google Sheets, you can find a mutual fund expense ratio like this:

Code: Select all

=googlefinance(C14, "expenseratio")/100

Where column C contains the ticker symbol and the cell is formatted as "%".

The googlefinance() "expenseratio" function doesn't work on ETFs (when last I checked), so you can use this for ETFs:

Code: Select all

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

Where column C contains the ticker symbol and the cell is formatted as "%".

There is a limit to the number of "importHTML()" calls you can have in a spreadsheet, but it's pretty high (much higher than the number of ETFs I own).

I'll look into the BID/ASK.

Hope this helps.

-sz

[sheetzilla]

Regarding BID/ASK (and some other interesting stuff), you should look at what this web page looks like in your browser;

http://etfs.morningstar.com/quote-banner?&t=IVV

That's what that second command

Code: Select all

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

Is cutting up and returning one cell of (if spreadsheet cell C35 contains "IVV"). If you mess with the numbers in that formula you should be able to get it to return different cells in that data.

the googlefinance() command offers more data on mutual funds. It isn't really prepared to tell the difference between an ETF and a single stock.

-sz

[djsykora]

Thanks Siamond and Sheetzilla. Those suggestions were very helpful. This is what I have so far in my spreadsheet:

Ticker Exp Ratio 12 M Trk Dif Current Spread 60 D Avg Spd Turnover Broker Category
SCHB 0.0004 --- 0.0093 --- --- Charles Schwab Large Blend
SCHX 0.0003 --- 0.026 --- --- Charles Schwab Large Blend
SCHZ 0.0005 --- 0.0536 --- --- Charles Schwab Intermediate-Term Bond
SCHG 0.0007 --- 0.0375 --- --- Charles Schwab Large Growth
SCHM 0.0007 --- 0.0241 --- --- Charles Schwab Mid-Cap Blend
SCHP 0.0007 --- -2 --- --- Charles Schwab Inflation-Protected Bond
SCHD 0.0007 --- 2 --- --- Charles Schwab Large Value

Sorry that I haven't figured out how to get my table columns to light up with the headings. There are 8 columns with these headings.
Ticker
Exp Ratio
12 M Trk Dif
Current Spread
60 D Avg Spd
Turnover
Broker
Category

Only the table continues on for a total of 436 commission-free ETFs, from four different brokers; Schwab, Fidelity, TD AmeriTrade, and Vanguard.

The Exp Ratio, Current Spread, and the Category are all extracted from the Morningstar table using the technique that Sheetzilla gave for getting the Exp Ratio. Thanks Sheetzilla. What I really want instead of the current spread from the Morningstar website is the 60 day average spread from the etf.com website. I tried to get the spread data at etf.com using the tables on the etf.com website, but I couldn't find a table with the data value I want. So if you direct me to another programmatic technique that I could use to get the 60 day average bid/ask spread from the etf.com website I would really appreciate it.

I prefer to make decisions using the 60 day average spread. The Morningstar spread data is the current spread and seems broken when the markets are not open.

After I learn how to get the data from etf.com I will also be fetching the median tracking difference (12 Month) because comparing this to the Expense Ratio will be enlightening.

I will also be working on getting the turnover data values from the Morningstar website from the "fees and expenses" table.

The googlefinance() function could use some enhancements directed toward ETFs. It doesn't support expense ratios of ETfs. It would be nice if it also handled the other data values that I am interested in.


Thanks in advance

[sheetzilla]

The Exp Ratio, Current Spread, and the Category are all extracted from the Morningstar table using the technique that Sheetzilla gave for getting the Exp Ratio. Thanks Sheetzilla. What I really want instead of the current spread from the Morningstar website is the 60 day average spread from the etf.com website. I tried to get the spread data at etf.com using the tables on the etf.com website, but I couldn't find a table with the data value I want. So if you direct me to another programmatic technique that I could use to get the 60 day average bid/ask spread from the etf.com website I would really appreciate it.

I prefer to make decisions using the 60 day average spread. The Morningstar spread data is the current spread and seems broken when the markets are not open.

After I learn how to get the data from etf.com I will also be fetching the median tracking difference (12 Month) because comparing this to the Expense Ratio will be enlightening.

Have you had any luck with this?

At first glance, it doesn't look like the =IMPORTHTML() function will work with the etf.com data. (Looking at an example page like: http://www.etf.com/IVV).

=IMPORTHTML() only works with html tables and lists. That page has a couple of tables and a few lists on it, but the data you want (in the right hand sidebar) is not in them.

I kind of wonder if =IMPORTDATA() or =IMPORTXML() would work, but =IMPORTDATA("http://www.etf.com/IVV") crashed my browser so I gave up.

Let me know if you've had any further success, I'm always looking to learn more.

-sz

[tman9999]

Regarding BID/ASK (and some other interesting stuff), you should look at what this web page looks like in your browser;

http://etfs.morningstar.com/quote-banner?&t=IVV

That's what that second command

Code: Select all

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

Is cutting up and returning one cell of (if spreadsheet cell C35 contains "IVV"). If you mess with the numbers in that formula you should be able to get it to return different cells in that data.

-sz

This is really helpful. I've been messing with the formula in an attempt to grab other data points from that feed, but so far have only managed to be able to snag the column header, "expenses". No other changes I've made have produced anything but error messages - #value, #ref, #na.

I'd like to grab asset class, for instance, but I'm not sure how to modify your formula for that. Any advice?
Thanks!

[sheetzilla]

This is really helpful. I've been messing with the formula in an attempt to grab other data points from that feed, but so far have only managed to be able to snag the column header, "expenses". No other changes I've made have produced anything but error messages - #value, #ref, #na.

I'd like to grab asset class, for instance, but I'm not sure how to modify your formula for that. Any advice?
Thanks!

Glad you found it useful.

Try something like this (with A58) for the ticker symbol:

Code: Select all

=INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", A58), "table", 2),0,11)

Returns "Category Large Blend"
I'm sure you can find a way to remove the word "Category".

-sz

Edit: sometimes it helps to cut chunks off the formula to see what it's doing. Try removing everything before 'importhtml' and the matching )'s and it'll be more clear what's going on.

[tman9999]

Glad you found it useful.

Try something like this (with A58) for the ticker symbol:

Code: Select all

=INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", A58), "table", 2),0,11)

Returns "Category Large Blend"
I'm sure you can find a way to remove the word "Category".

-sz

Edit: sometimes it helps to cut chunks off the formula to see what it's doing. Try removing everything before 'importhtml' and the matching )'s and it'll be more clear what's going on.

Good idea. I did what you suggested and messed around with the formula to figure out a bit about what it's doing. My very inelegant solution is to pull up everything after the word, Category. It still requires two importHTML calls, so it's not terribly pretty or efficient, but it does work. The learning never stops.

Code: Select all

=right(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", B3), "table", 2),0,11),len(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", B3), "table", 2),0,11))-9)

[sheetzilla]

Perhaps =SUBSTITUTE() will help here.

https://support.google.com/docs/answer/3094215?

-sz

[realtoraz]

hello I use this =INDEX(SPLIT(INDEX(importHTML(CONCATENATE("http://etfs.morningstar.com/quote-banner?&t=", C35), "table", 1),1,13), " "), 1, 2)
and it works sometimes and also it don't work on some of my sheets not sure why anyone else see this on there sheets

[sperry8]

In Google Sheets, you can find a mutual fund expense ratio like this:

Code: Select all

=googlefinance(C14, "expenseratio")/100

Where column C contains the ticker symbol and the cell is formatted as "%".

The googlefinance() "expenseratio" function doesn't work on ETFs (when last I checked), so you can use this for ETFs:

Code: Select all

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

Where column C contains the ticker symbol and the cell is formatted as "%".

There is a limit to the number of "importHTML()" calls you can have in a spreadsheet, but it's pretty high (much higher than the number of ETFs I own).

I'll look into the BID/ASK.

Hope this helps.

-sz

This Morningstar link which used to pull in expense ratios no longer works. Anyone know a fix (or alternate solution)?

[GreatOdinsRaven]

In Google Sheets, you can find a mutual fund expense ratio like this:

Code: Select all

=googlefinance(C14, "expenseratio")/100

Where column C contains the ticker symbol and the cell is formatted as "%".

The googlefinance() "expenseratio" function doesn't work on ETFs (when last I checked), so you can use this for ETFs:

Code: Select all

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

Where column C contains the ticker symbol and the cell is formatted as "%".

There is a limit to the number of "importHTML()" calls you can have in a spreadsheet, but it's pretty high (much higher than the number of ETFs I own).

I'll look into the BID/ASK.

Hope this helps.

-sz

This Morningstar link which used to pull in expense ratios no longer works. Anyone know a fix (or alternate solution)?

Bump. Now my spreadsheet is wrecked, too.

Any one know how to fix it?

GOR