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