[TUTORIAL] Automatic Daily Chart in Google Sheets

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

13 Likes

Your speadsheet is really nice :ok_hand: Thanks for sharing.

Do you have any plans for adding portfolio target allocations to aid re-balancing.

Thanks man! Not at the current time, I won’t be rebalancing till April. I will just be keeping an eye on it closely and waiting for stuff to hit either up 20% or down by 8% before or buy or sell

1 Like

Thank you for the amazing tutorial. I love Google spreadsheets and this will help me to bring it to the next level. Bookmarked so I can tinker with this over the holidays. Freetrade gold :large_orange_diamond: :large_orange_diamond: :large_orange_diamond:

1 Like

Nice! Glad you got it working properly

1 Like