Trigger daily scripts to take snapshots and draw charts with Google Spreadsheets
We already learned how to live track our investment portfolio performance with Google Spreadsheets. Now it’s time to automatically take snapshots in order to be able to visualize the evolution of it. We will learn how to:
- Write a script that takes the current value of our portfolio and copies it into a new row adding a timestamp.
- Configure a trigger that runs this script once a day.
- Setup an updated chart that represents the evolution of our portfolio.
Adding the date
In order to being able to draw charts we need two axis. The Y-axis being € and the X-axis being time: we need to add a column that represents time.
The row that we already have in this sheet will always represent the realtime value of our portfolio while every new row will representvpast values.
Writing the script
Once we have the date column we will start with the interesting part: writing the Google Apps Script that will perform the snapshot. It will basically copy the current value of our portfolio copying it into the first empty row it finds, adding the current date to the first column of the new row.
The first step is to go into the Script Editor, located in Tools -> Script Editor.
A new window will appear with an empty function. That’s where we will write our instructions. Rename the project to something like “Personal Finance” and the function to “snapshot” (instead of myFuncion) so you can later identify what it does.
Write the following code into your function. You will see it is very easy to understand. I added comments to every line of code so you can follow the instructions.
Once you’ve written the code, you are ready to run it for the first time and see if everything works. Click on the “Play” button next to the bug, and see what happens.
The first time you run it you’ll get a security warning. Review permissions, click on “Advanced” when it tells you the app is not verified and allow it to create spreadsheets.
If everything worked as a charm you should now see a new row in the dashboard spreadsheet with the current date and current value of your portfolio.
Configuring the trigger
Now we have a script that works when you manually run it. But how do we make it fully autonomous? With the help of triggers it will be an easy task.
From the script editor click on the clock icon next to the play button we used to run the script.
The “Triggers” window will open where you will be able to setup a trigger that daily runs your script.
Click on “Add Trigger” on the bottom right corner. A trigger menu will popup and will be able to select the function you want to run (in this case the “snapshot” function), how you want to run it (Time Driven with Day Timer) and finally you will be able to choose time frames when you want your script to run (I like to run it between 00:00 and 01:00). Scroll down the popup and save the trigger.
Now you have your trigger setup and ready to run in the time frame you selected.
Drawing the graph
We can now draw the data we are generating to get a graphical representation of the evolution of our portfolio.
Select all your columns, and then go to the “Insert” menu and click on “Chart”
You can choose your favorite chart style. For this kind of information I like to go with the classic line chart. If you have significantly different orders of magnitude between your crypto, stock and fund you might want to consider using area charts or even drawing separate charts.
In order to fully automate the generation of the chart, we now have to tell it to look for the whole column instead of only the data we have now.
We can do so by editing the “X-axis” and each of the “Series” elements. Click on the three vertical dots and “Edit” for every element of the chart.
You’ll be able to select the range. Use A:A (date), B:B (Funds), C:C (Stock), D:D (Crypto) to tell it to use the full column and not only a limited range.
And that’s it! You now have an automated dashboard that always shows the current value of your portfolio and also the daily evolution of every asset in it.
It will look boring at the begining because you only have one day of information, but give it some time to start showing some interesting (and hopefuly positive) curves.