Regular readers of my blog have probably noticed that I use Google Sheets for almost everything. I use it to track the live net asset values of various companies, I built my valuation models in Google Sheets and everything is tied together in a master portfolio sheet (some if its functionality you can glimpse from my performance review posts) . A few years ago I wrote a short tutorial that explained how you can add realtime price information in Google Sheets for stocks that aren’t support by the standaard
What I’ll be focusing on today is how you can pull data from multiple documents to create one sheet with a nice overview of everything. To make this easy it’s smart to standardize your documents a bit. What I do is that when I value a company there is always a sheet called “Thesis” that has a few standardized items at fixes positions, like this:
The price target links to a valuation model in a different sheet (but in the same document), the price (in this case) comes from the
=GoogleFinance() function and the last update cell is useful to keep track if I have updated my valuation recently. As you can see here I should probably take a little bit of time soon to update my Pardee Resources valuation. If you make it a habit of doing this for everything your research you can make a nice portfolio sheet that tracks realtime how much upside every position has remaining. Part of my portfolio sheet looks like this:
For obvious reasons I have hidden the number of shares I own, and the value of every position, but you can see how I have an price target for every position that is updated automatically based on the latest price. To pull data from a different document in the sheet we can use the
=ImportRange() command. It requires two arguments, one is the url of the sheet where you want to pull the data from, and the second one is the reference to the cell you want to pull the data from. So it would look something like this:
After entering this function in a cell you initially get an error message. Hover with the mouse cursor above the error message, and you will see that a small pop-up that asks if you want to give your sheet access to the other sheet. Do this, and the data will appear :).
Combined with the option to get realtime prices (as discussed in my old tutorial) you have an incredible toolset to make a fancy spreadsheet. Not only will this give a quick overview of which positions are perhaps becoming more or less attractive, it’s also easy to build a watchlist like this (I have done this as well). Everything that you researched, but didn’t buy can be put in that list together with positions that you have sold in the past.
One limitation to keep in mind is that Google isn’t happy if one sheet requires to much calls to outside sources, and by making one sheet dependent on tons of other sheets the number of calls can explode quickly. So you will have to try to not invoke to many
=ImportXML() functions in all the combined sheets in order not to break things.
Author is long the stuff in the sheet
Did you have a background in this stuff? I’ve tried to figure out the import xml thing but I started to feel like I was going to have to become a programmer to understand it. I have sometimes been able to get it to work by trial and error and looking at what other people have done but I don’t really understand it, and usually they change something about the page later on and the reference quits working (I’m guessing that’s what is going on) at which point I am lost. My computer skill level is probably fairly low though.
I have a master in computer science 😛
Ever used GitHub Alpha?
I am software dev myself, wondering if a “GitHub for Investing” would be a useful tool.
I don’t really have experience with GitHub, so can’t comment if it would be a useful tool.
How do you update RHDGF, google finance doesn’t list the subsidiaries on it’s platform.
See my older tutorial, linked in this article 🙂
I have a very similar setup. Yes, got inspired by your Google Sheets use long ago, but funny to see how much it converged.
How do you take care of the sorting on position size? The sorting function seems to break any dependent cells on the same or other sheets. Of course this can be settled by reorganizing the data or using a separate presentation sheet, but it’d rather avoid that.
I put everything in the same workbook, but I have the impression it gets slower the more sheets I add to the deck.
The random behavior on errors with ImportHTML and ImportXML are also not ideal. But it’s not as if there’s a real alternative.
I have no problems with the sorting function. In the sheet pictured in the post every company has one line, and if the data isn’t entered there directly it’s coming from a different sheet.
Very helpful! How do you track spreads or hedges?
Secondary question, how do you track dividends paid out?
I have a nice sheet the tracks spreads. Mostly a lot of manual work, but of course with real time prices.
And I don’t track dividends paid out.
What does the blue represent in the look through sheet?
Just items that I entered manually
Thanks for sharing. I also use Google sheets to track investments, do you know if it’s possible to set up an alert which would be triggered by a cell’s criteria, which would run periodically in the background i.e. Google finance update the data in the spreadsheet without a user actually opening the document?
Simple example: Cell has an IF formula to show true if stock price exceeds X, when shows true an alert should be sent.
I’ve tried to research this, and have found some attempts at stock alerts, but haven’t had much success.
I think this would be really useful, but not sure if possible, particularly as it needs Google finance data to refresh in the background i.e. without a user opening the document.
It’s probably possible, but would require to use Google Apps Script to program it.
It’s possible: https://firstname.lastname@example.org/free-sms-stock-alerts-with-twilio-and-google-spreadsheets-1aa5dec7ef4c
I set it to send an email, as haven’t signed up to Twilio yet, but it works great if you’re interested.