A stock portfolio tracker using Google Docs with advanced functions than your average tracker. Get it for yourself.
What is a Stock Portfolio Tracking Spreadsheet?
Stock Portfolio Tracking Spreadsheet
A project that I’ve always had, was to improve on my stock portfolio tracking spreadsheets.
During this time, I’ve probably used 10 or so different portfolio trackers, but nothing met my needs. I don’t do complicated transactions, but still, nothing could really satisfy me.
But those four long years have passed, and I believe I finally have a version that will serve my needs and fulfill its purpose for a long time.
Main Needs from a Portfolio Spreadsheet
- Enter transactions into a single column without splitting up different transactions
- Spreadsheet should be able to automatically update how many shares I’m holding for any company
- Account for dividends
- Account for splits
I don’t do options so I have no need for such transactions. Don’t see why it would be hard for you to edit though.
Previous Versions
The original portfolio spreadsheet could only factor in simple buy and sell transactions and had to separate it.
Then a Google spreadsheet version was released which made it easier to track but not being able to automatically update the cumulative number of shares held for each position, made keeping track difficult.
The improved portfolio spreadsheet even had an interactive time line to track the portfolio growth but it never really took off. Too much hassle of having to update the portfolio values regularly. Also bad to look at your total portfolio that often as well.
Now this new stock portfolio tracking spreadsheet blows it all out of the water. It doesn’t solve everything but it does most of what I need.
New Stock Portfolio Tracker Spreadsheet
Full credit goes to Investment Moats for his amazing spreadsheet. See it in action.
The creator is a Singaporean investor and it will work right away with what he has, but I made some edits to tailor it for the US exchanges (including pink sheets, OTC and ADR’s) and to clean it up a little.
So you have two options. Use the original or use my edited version.
How to Use and Save a Copy
Full details and instructions can be found on Investment Moats’ website. Read the “Read me section” in the spreadsheet as well.
Yellow highlight cells is where you manually enter data. Aqua colored cells are formulas so do not overwrite.
To save a copy into your own account, do the following
- Sign into your Google Docs account
- Open Stock Portfolio Tracking Spreadsheet
- Go to File > Make a Copy
- Rename and press OK to save to your account
How to Edit the Charts
I’ve added three charts to the summary tab. For every new position, you will have to edit the ranges for it to be reflected in the graphs. I will show you how here.
There is a tab called “ChartsData” which holds and sorts the data for the graphs by market value. No need to change anything in the “ChartsData” tab. This tab is only used to display and sort data. Do not enter anything in this section.
Below is one of the graphs/tables in the Summary section.
Click on the table once and a “Chart” menu will appear. Click that and in the menu you can edit the chart.
Select Edit Chart to bring up the chart editor.
The data range is: ChartData!B1:B11, ChartData!D1:D11, ChartData!M1:N11, ChartData!Q1:R11
Columns B, D, M, N, Q and R are used in the table with values from row 1 to 11. In the spreadsheet, I only have 10 holding positions where row 1 is the table headings and the data is contained within row 2 to row 11.
E.g. if you have 20 current holdings and need to update the graph, the data ranges would be
ChartData!B1:B21, ChartData!D1:D21, ChartData!M1:N21, ChartData!Q1:R21
To see the ranges in more detail, click the grid icon next to the range values to bring up this window.
You can add more detail to your table by adding another range.
Press ok and save.
The other two graphs you will need to update include a pie chart displaying sizing and gain/loss.
Another Portfolio Tracker made in Java
Maybe you don’t like the idea of storing your information on the cloud.
Then as an alternative, here is a free, lite, portable and java portfolio tracker that you can use. Again thanks to Investment Moats for bringing it to my attention.
You need to have Java installed on your computer for this to run though. It is still in beta stage so expect bugs and limited documentation but a very good freebie.
Import feature doesn’t work too well so if you have hundreds of transactions to enter, it will take you a long time. But still, better than most.
Truly the Best Stock Portfolio Tracker
After plugging in all my historical transactions, all I can say is that I’m hooked with this version. It has made tracking so much easier. I will continue to add good features to this Stock Portfolio Tracking Spreadsheet when it becomes a need, but until then, enjoy.
Don’t forget to Like, share or tweet it if you like this.
