A couple weeks ago I shared a spreadsheet that tracked index returns via index funds. The Google Finance spreadsheet functions for mutual funds are really easy to use to figure out returns over different periods of time.
For stocks and ETFs, it’s more complicated.
That said, making it work with ETFs is the better way to go since ETFs exist for every imaginable index, but there were a few issues to work through.
Well, I got ambitious.
The first problem was finding a simple solution that pulls start and end date prices, then calculate the return over the period. The second problem was figuring how to automagically update the start date for year-to-date returns when January 1st rolls around because I’m lazy and don’t want the hassle of remembering to do it manually each year.
I found a solution to both issues. It may not be the cleanest but it works. Here’s the spreadsheet with a link and a few caveats underneath.
Here’s the link to grab a copy: ETF Index Return Tracker Spreadsheet
I made this as hands-off as possible to keep it simple. There are some caveats:
- It’s price return only. So no dividends included for any ETFs that pay dividends. Something to keep in mind.
- The 1, 3, 5, and 10-year returns are annually and over the period ending on the current date.
- It works for stocks, ETFs, mutual funds…any ticker Google Finance has data on (I did not test them all, feel free to prove me wrong).
- To change an ETF, just change the ticker symbol. The first two columns are all that need to be changed, the rest update automatically.
- If you run out of space and want to add something new, it takes a couple of steps. First, insert a new row on both sheets — Index Returns sheet and ETF Data sheet — at the same spot. Next, copy and paste the cells from the row above into the new rows on both sheets. (I purposely left a few empty spaces to make this easier).
- If there’s something missing you think should be added, then grab a copy and add it. I don’t do custom spreadsheets!
- There’s an error that pops up randomly sometimes. I don’t know why. I’ve tried a couple solutions to no avail. It’s mildly frustrating. If you can solve it, please share.
- Here’s the link to the Google Finance spreadsheet functions, just in case.
I think that’s all. Go grab a copy.
Last Call
- Why Do Investors Focus on the Wrong Things? – Behavioral Investment
- Daniel Kahneman: Four Keys to Better Decision Making – Enterprising Investor
- What Lunch with Warren Buffett Taught Me About Investing and Life – G. Spier
- Ten Ideas to Foster Long-Term Investing (pdf) – Brandes Institute
- Why We Need to Update Financial Reporting for the Digital Era – HBR
- Common Mistakes in Factor Investing – ValIdea
- False Promises: Going Passive is Not Momentum Investing – Factor Investor
- Hedge Funds’ Best Ideas? Those Are Just Stocks They’re Dumping – Bloomberg see also Talking Your Book: Evidence from Stock Pitches at Investment Conferences (pdf) – P. Luo
- How to Become a Centaur – MIT Press
- Tears ‘R’ Us: The World’s Biggest Toy Store Didn’t Have to Die – BusinessWeek
- The Scooter Economy – Stratechery
- Inside the Binge Factory – Vulture