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

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
djsykora
Posts: 3
Joined: Sat Nov 14, 2015 3:55 am

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

Post by 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
User avatar
siamond
Posts: 5732
Joined: Mon May 28, 2012 5:50 am

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

Post by 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.
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

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

Post by 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
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

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

Post by 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
Topic Author
djsykora
Posts: 3
Joined: Sat Nov 14, 2015 3:55 am

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

Post by 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
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

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

Post by 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
Posts: 72
Joined: Tue May 20, 2008 1:06 pm

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

Post by tman9999 »

sheetzilla wrote: Sat Nov 14, 2015 11:57 am 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!
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

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

Post by sheetzilla »

tman9999 wrote: Fri Jan 10, 2020 7:23 pm
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
Posts: 72
Joined: Tue May 20, 2008 1:06 pm

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

Post by tman9999 »

sheetzilla wrote: Fri Jan 10, 2020 10:01 pm 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.
:sharebeer

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)
User avatar
sheetzilla
Posts: 36
Joined: Sat Jan 10, 2015 4:07 pm

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

Post by sheetzilla »

Perhaps =SUBSTITUTE() will help here.

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

-sz
realtoraz
Posts: 1
Joined: Sun May 03, 2020 2:03 pm

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

Post by 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
User avatar
sperry8
Posts: 2286
Joined: Sat Mar 29, 2008 9:25 pm
Location: Miami FL

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

Post by sperry8 »

sheetzilla wrote: Sat Nov 14, 2015 11:52 am 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)?
BH contests: 2020 #253 of 664 | 19 #233 of 645 | 18 #150 of 493 | 17 #516 of 647 | 16 #121 of 610 | 15 #18 of 552 | 14 #225 of 503 | 13 #383 of 433 | 12 #366 of 410 | 11 #113 of 369 | 10 #53 of 282
User avatar
GreatOdinsRaven
Posts: 564
Joined: Thu Apr 23, 2015 8:47 pm

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

Post by GreatOdinsRaven »

sperry8 wrote: Sat Aug 15, 2020 11:24 am
sheetzilla wrote: Sat Nov 14, 2015 11:52 am 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. :oops:

Any one know how to fix it?

GOR
"The greatest enemies of the equity investor are expenses and emotions." -John C. Bogle, Little Book of Common Sense Investing. | | "Winter is coming." Lord Eddard Stark.
Post Reply