Skip to Content

Stock Portfolio Tracker – The Portfolio History Feature

For newer readers, you might not be aware that I do share my current portfolio on my blog.

You can take a look at My Current Portfolio.

The tables are published from a Stock Portfolio Tracker, which is based off Google Spreadsheet.

It is FREE and to use it, you can read the instructions here.

This stock portfolio tracker is suitable for you if:

  1. You are more of a buy and hold investor
  2. Wants to track your portfolio based on transactions (buy, sell, dividend, bonus shares, rights issues, capital reductions)
  3. Auto-updates prices for stock exchanges supported by Google and Yahoo
  4. Familiar with how to use spreadsheets
  5. Likes to DIY

In the last few months, I added a feature called Portfolio History to it. Portfolio history simply takes the daily portfolio summary snapshot and archive it so that you can review it in the future.

Stock Portfolio Tracker - Portfolio History

Data from Portfolio Summary gets ported to Portfolio History (click to view larger image)

This is quite easily done in that you could create a system that:

  1. Every night after dinner, you review your portfolio
  2. Type each entry porting the information over from the Portfolio Summary

Then you can have very nice looking charts like this:

Of course, I made the spreadsheet auto-copy the data over to Portfolio History.

Here is how.

The Auto-Copy at Night Feature Explain

Google spreadsheet allows you to run some script in the background. What is nifty about this is that you can set a timer when some of these functions are triggered.

So what I did was to

  1. create a function that copy the live data from Portfolio Summary to Portfolio History
  2. set a trigger to run this function every 1 am for example

For those of you with the new sheet, you will need to manually set #2 (set the trigger to run nightly or in the day if you prefer)

For those of you with an existing version of Stock Portfolio Tracker, you will need to

  1. add the Portfolio History sheet to your existing Stock Portfolio Tracker
  2. copy the functions over to your existing Stock Portfolio Tracker
  3. set the trigger to run the function

Step 1: Add the Portfolio History sheet

For folks with the existing spreadsheet, you need to add the Portfolio History sheet.

First make a copy of my existing version of spreadsheet. You can do this by going to File > Make a copy (don’t use the Share function on the top right side)

When you click on the Portfolio History > Copy to…..

Secondly,search for your existing Stock Portfolio Tracker and add this Portfolio History to it.

Third, clear my data on the Portfolio History you have copied over to your version of Stock Portfolio Tracker. You do not need my history. You will be populating your own portfolio history.

Step 2: Add the Functions from My Version of Stock Portfolio Tracker to Your Stock Portfolio Tracker

This is for people with an existing older version of Stock Portfolio Tracker.

We will need to port the functions from the script editor to your current Stock Portfolio Tracker.

First, at MY version of Stock Portfolio Tracker, launch the Script Editor by going to Tools > Script Editor…

Second, do the same for YOUR version of Stock Portfolio Tracker. Be careful, recognize which tab is my version and which is your version.

Third, copy the Global Variables section from My version of Script Editor to YOUR version of Script Editor.

Fourth, copy the function copyLivePortfolioDataToHistory() from MY version to YOUR version.

Do note that the whole function starts from function copyLivePortfolioDataToHistory(){ to the closing bracket }.

Fifth, click the save button on YOUR version of script editor to save the function.

Step 3: Set the Trigger to run at Periodic Interval

This step should be done if you have MY version or you are using an existing older version.

First, select the function copyLivePortfolioDataToHistory in the drop down next to the bug icon.

Second, click on the clock icon which lets us set the triggers.

Third, Click on the link “No triggers set up. Click here to add one now”

Fourth, we set the permutations when this function will run.

I prefer to set it to run daily, so I select Day timer, and then I select the interval to run. I run mine at 1 to 2 am.

Fifth, click the Save button below.

Step 4: Test your Script

To see if your script is running properly, we click the Run button when copyLivePortfolioDatatoHistory is selected.

You should see a new row populated. Your charts should be updating as well. somehow Row 2 seems to be always empty. If you have a solution do let me know.

That’s it

If you need more help

I hope that you will find this functionality addition useful.

It is my wish that you guys take this spreadsheet and go crazy with it. If you are new to spreadsheet, treat playing around with it as an experience gain.

If you need help porting, even after this guide, my support is open at [email protected].

I do levy a support charge of USD10.00 through Paypal. Email me and I will guide you through the details.

Kyith

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Albert

Tuesday 2nd of November 2021

Hi kyith, Thank you for the work and for sharing with us. I have been using the tracker for some time however recently I am unable to retrieve the price and I am not really sure why. I have shared with you the sheet can you kindly help to see what's the issue? thanks!

Albert

Monday 8th of November 2021

@Kyith hi kyith, I am currently using YPrice3

Kyith

Wednesday 3rd of November 2021

Hi Albert, are you using the formula yPrice1,2 or 3?

JR

Wednesday 10th of March 2021

Hi Keith,

Thank you for this very useful tool. The auto price grab works every time when I run the script manually. However when I was using the auto trigger, the prices wasn't always updated and hence giving a wrong profit and loss. Do any of your readers face this issue? Thank you!

Zl

Tuesday 23rd of March 2021

Hi @Kyith, I face the same issue too. Should I share my sheet with u too?

Kyith

Thursday 11th of March 2021

Hi JR, I think not many people reflected that. If you would like me to take a look you can share it with me at [email protected] and i can take a look at it.

Liam

Sunday 24th of January 2021

Kyith

This is a top notch spreadsheet. I appreciate not only the amount of time it must have taken to build this, but also you sharing it with us.

I do have one question, I would like to set the trigger to run daily, but at the correct time for me. I am Eastern Standard Time (EST) as I live on the eastern seaboard of the United States. I can't for the life of me determine how to set this up in the script.

Let's say it is 3:30 Am. When I run the script it shows as a date/time of 2021 Jan 24 16:30.

Can you share how to modify this setting? It would be very much appreciated.

Kyith

Sunday 24th of January 2021

HI Liam, maybe you can go to try File > Spreadsheet Settings. Overthere, you can change the Timezone.

kelvin

Friday 4th of December 2020

Hi How do i download the excel sheet can you help to provide the url?

Kyith

Saturday 5th of December 2020

Hi kelvin, this is not an excel sheet but a Google Spreadsheet. You will make a copy of this spreadsheet online and then use it.

Gabriel

Thursday 5th of March 2020

Hi Kyith,

Hopefully you get time to read this. Wonderful work on this tracker. It has pushed me to finally start investing last year. I've been recently getting this:

"TypeError: Cannot read property 'split' of undefined (line 77, file "Code")"

function getSGPrice(string) { var urlarr = string.split("?"); var csvContent = UrlFetchApp.fetch(urlarr[0]).getContentText(); var csvData = Utilities.parseCsv(csvContent,','); for(i=0; i<csvData.length;i++){ var tmp = csvData[i][3]; var toNum = parseFloat(tmp); csvData[i][3] = toNum; } return csvData; }

and the portfolio is unable to retrieve ticker prices from Yahoo Finance.

I have not changed any of the code, so I'm not sure what gives...

Gabriel

Kyith

Thursday 5th of March 2020

Hi Gabriel, i get that complain on and off but after a while, users said it is ok.

This site uses Akismet to reduce spam. Learn how your comment data is processed.