Tracking Your Portfolio Automatically
A few weeks ago, I was forced to transfer my portfolio to Google Sheets after the Python library pandas_datareader for Yahoo! Finance started to break for a number of my key tickers.
It had been years since I last tinkered with Google Sheets in earnest, but I was immediately impressed by the amount of progress Google has made since then.
Between importhtml() and googlefinance(), I was able to obtain quotes automatically across the asset classes. I include the hyperlinks to the respective documentation that could accessed when the function name above is clicked on.
And then I had an idea.
What if we could automatically take a snapshot at the end of each day to see where your portfolio is at in terms of closing value and return — raw or annualised?
It turns out that there is a solution. A shout out to Gadget Apps Hacks who shared the original code in this post. For fervent Pythonistas, I regret to inform you that the language favoured by Google in this case is JavaScript, hence there is no escape from curly brackets and semi-colons. (Well, you could technically get away with the latter but that would not have been idiomatic).
I have refactored the code slightly as I do not need to fetch live (or 15-minute delayed) prices from another server — that process is executed when importhtml() or googlefinance() is called earlier in the chain of activities. I have also coded with a more ES6 style where I limit the scope within the block via let.
To code the script, we click on Tools in the ribbon bar near the top of the window and select Script Editor.
Some of the variables would need to be adjusted in the code above, e.g. the actual input and output sheets’ names and the data ranges referenced, but otherwise the code should work fine. One point to note is to ensure that both sheets actually exist, and the output tab contains nothing but the table to append the additional rows. The functions used are self-documenting — thanks Google — so the script was a joy to write.
In terms of what should be included in the data ranges, some key information detailing the composition of the portfolio, the cost price, the market price, return metrics, etc. are good candidates.
The rest of the instructions are in line with what Gadget Apps Hacks describe in the post shared earlier. Google’s Script Editor has a clock icon to the left of the code editor where you could set the frequency of new rows to be added. We could try the minute frequency to test if the deployment is working, before switching to a longer interval. Do note that an hourly update would result in 876,576 rows if this workbook lasts 100 years. A daily frequency might suffice for the purpose of evaluating the trajectory of your investments.
Enjoy!
