While FreeTrade is building out their invest platform and other sexy features such as “benchmark” and “stock insights” which are due to land within the next few months according to their Trello road map. We have to take it upon ourselves to track our portfolios and include things we want to track, such as market cap, 52-week high/low, dividend yield & more. Whether you’re using something like Stockopedia or a simple Google Sheet.
I personally use both, Stockopedia & Google Sheets. I thought I would write a quick tutorial on how to create an automatic daily chart for your portfolio value as well as a daily P/L in Google Sheets using the script function.
Step 1.
You will need to include a total value of your portfolio in a cell, as well as it’s daily P/L something like I have done with mine, as you can see here:
Remember the cells which hold the data. It will come in handy later.
Step 2.
You will want to create a blank sheet and call it something like History. I would suggest you call it history because it would make it easier to follow along.
Step 3.
Head over to the script editor. Which you can find under Tools > Script Editor. This will open a new Project for your GSheet, call the project something like “Portfolio Tracker”
Now for the first script which you can find here (You’re more than welcome to just copy and paste mine, you will need to edit some things):
function plotDailyValuation() {
//read the counter (#) in cell A1 of History, number in cell is the row of column to input data
var counter = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("D2");
// record DATE in cell A#
var date = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("A"+counter.getValue());
date.setValue(new Date(new Date().getFullYear(),new Date().getMonth(), new Date().getDate()));
//read portfolio value in "Portfolio Sheet"
var portfolioValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Portfolio").getRange("H18");
var value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("B"+counter.getValue());
value.setValue(portfolioValue.getValue());
//+1 to the counter in cell A1
counter.setValue(counter.getValue() + 1);
}
Let me explain what the above code does line by line.
var counter = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("D2");
This line grabs a number from a cell so you can produce a counter, which will increment by 1 every time the script is run. This will make sense in the next couple of lines.
var date = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("A"+counter.getValue());
date.setValue(new Date(new Date().getFullYear(),new Date().getMonth(), new Date().getDate()));
These two lines produce the date, the following .getRange("A"+counter.getValue());
tells the script where you want the date to be placed. Using the counter, for example:
counter = 4
The date will be placed in A4.
This line takes the value from your portfolio sheet. Therefore .getRange("H18")
will need to have the cell which produces your portfolio value. Which in my case if you look at the screenshot is "H18"
var portfolioValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Portfolio").getRange("H18");
This line will use the counter variable to place the data in the relevant cell. So using the example above you would need to use col B.
var value = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("History").getRange("B"+counter.getValue());
This line takes the value generated from the variable portfolioValue
and assigns it to value
value.setValue(portfolioValue.getValue());
This line is kind of the most important one. This is the line that increments the counter by 1 every time it has run. Therefore allowing the script to produce row after row of data.
counter.setValue(counter.getValue() + 1);
Step 4.
I bet you’re asking how do I get the script to run every day? Before I started this little project I didn’t know either. Enter @DavidM he pointed me in the direction of the above script and how to trigger, so this is really all down to him.
To place a trigger on the script you will need to first click on the Trigger button which can be found here:
Which will then load a tab into the triggers for the project, click the big blue button in the bottom right to create your trigger, and use the following settings:
Of course, the function name could be different if you called your function something different.
Step 5
This is how my “History” sheer currently looks:
As you can see the counters at the top, this should help explain their use a bit more 33 & 7. This will tell the script to place the data in row 33 for the valuation and row 7 for the Daily P/L.
All that is left to do next, is highlight the two columns and create a graph from it. To make sure you’re not caught out when selecting the range go back and edit it to the max rows you have. So you don’t have to worry about it in the future.
The End
And Ladies & Gents…that’s more or less how I created my daily chart. For the Daily P/L its the same process, just using a different script I will place both scripts below. In your scripts project window to create a new script, just go to File > Script file just make sure you call the function name something different.
Daily Valuation Script
Daily P/L Script
And if you’re curious about my working GSheet you can find it here.
Any questions about the above reply here and ill try and answer them, I hope you all find the above useful and Merry Christmas!
Thanks Paul