To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.
********
This is what you get today.
stock portfolio tracker spreadsheet using google drive
The Best Stock Portfolio Tracking Spreadsheet
At Old School Value, we focus on providing the best stock analysis software for value investors.
There are plenty of tools that analyze stocks, but not much in the arena of (well made) stock portfolio trackers.
That’s why every serious DIY investor still uses stock portfolio tracking spreadsheets – and why you’re here today.
I’ve probably used 10 or so different portfolio trackers, but nothing met my needs.
I don’t do complicated transactions, options, shorts, ETF’s or even dividends.
But still, nothing suited my basic needs.
I don’t need crazy bells and whistles that only complicate things and cause stock portfolio tracking to be a bigger nightmare than it already is.
Well, not anymore.
Over the past years, I’ve been using a Google Docs version that does what I need. There are options like personal finance websites that sync with your brokerage account and automatically update your transactions to make life easier.
However, my investment accounts are with Interactive Brokers which is not supported by any of the personal finance software.
So I’m stuck doing things manually.
Main Needs from a Stock Portfolio Tracking Spreadsheet
Again, my needs are simple.
- 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.
I am not an expert in options, so for that, it will be best for you to take this and edit it to match your own needs.
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.
For it to work for me, I cut out parts that I felt overcomplicated it.
Also made some edits to tailor it for the US exchanges – including pink sheets, OTC and ADR’s.
So you have two options. Use the original or use my edited US version.
How to Save the Stock Tracking Spreadsheet to Your Account
Important:
- DO NOT request to share the spreadsheet. Just make a copy yourself. See below.
- Yellow 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
- Click this link to open Stock Portfolio Tracking Spreadsheet
- Go to File > Make a Copy
- Rename and press OK to save to your account
While you’re at it, you can also get more spreadsheets and checklists by registering your email to Old School Value.
Click the image below and I’ll send you checklists and more immediately.
How to Use This Portfolio Tracking Spreadsheet
Once you’ve saved the spreadsheet to your account, there are basic areas to cover.
Open up the spreadsheet you just copied to your account. You must open your copy as you cannot edit my version directly.
Easiest way to go through this tutorial is to start backwards with the spreadsheet.
Start from
- Transactions_OSV
- to Summary_OSV
- to Portfolio Summary
Step 1: Editing Transactions
How to enter transactions
- Date: Enter the transaction date
- Type: Select the type of transaction. Select from buy, sell, dividend, fee, or split.
- Stock: Enter the stock ticker
- Transacted Units: Enter the number of shares purchased, sold, receiving dividends
- Fees: Enter the trading commission or any other related fees like tendering fees or other one time fees related to a transaction
- Stock Split Ratio: Enter the split factor to update the spreadsheet with the proper number of shares now held in your account
To enter new transactions, delete the values and edit it with your own as I show in this video.
The default spreadsheet you save to your account has the pre-loaded template data you can follow enter your own transactions.
Step 2: Updating the Summary Transactions Data
The purpose of the summary tab is to simplify all the transactions you enter into the transactions section of the stock portfolio tracker into an easier to understand format.
Rather than going through hundreds or thousands of transaction data to figure out how you are doing, the Summary tab condenses it to a single line per stock.
Here’s what I mean.
Just in this sample data alone, there are 20 transactions.
stock portfolio tracker transactions data
If you buy and sell the same stock multiple times, this list can grow out of control.
But in the Summary data, each row represents the full details of a single stock.
stock portfolio summary data
Remember that the yellow cells are the ones you fill up and the blue cells contain formulas which you copy and paste to the next row.
stock portfolio tracker summary instructions
Step 3: Interpreting the Portfolio Summary
The Portfolio Summary grabs the data from the Summary_OSV tab. That’s why you don’t want to enter the same stock multiple times in the Summary_OSV tab. Otherwise, you’ll be double counting your investments.
stock portfolio summary dashboard
This section is calculated automatically. Nothing to do here.
The focus is a simple profit and loss analysis of your portfolio. No % returns, CAGR calculations, YTD, Sharpe ratios, Beta and so on.
Just a straight up “how much money did I make or lose” display.
The different colors match the investment category you select in the Summary_OSV.
This way, you can break down the investments styles that do the best for you.
Step 4: How to Edit the Table in the Spreadsheet
I’ve added three charts to the Portfolio Summary tab.
For every new position, you have to edit the range of the cells for the data to be updated in the graphs.
First, there is a tab called “ChartData-DONTEDIT” which holds and sorts the data for the graphs by market value.
Do not change anything in the “ChartsData” tab unless you know what you are doing.
This tab grabs data from Summary_OSV, sorts it and used to create the charts.
One of the graphs/tables in the Portfolio Summary section you see looks like the following image.
Click on the table once and a small menu icon appears in the corner. Click it and then select “Advanced Edit”
stock portfolio edit charts
The process looks like this.
Then with the chart editor open, click on Chart types.
Use the Chart Editor to Edit Ranges of the Table and Chart
You can see that the spreadsheet range used to create the table is:
‘ChartData-DONTEDIT’!B1:B15, ‘ChartData-DONTEDIT’!D1:D15, ‘ChartData-DONTEDIT’!M1:N15, ‘ChartData-DONTEDIT’!Q1:R15
Columns B, D, M, N, Q and R are used in the table with values from row 1 to 15. In the spreadsheet, there are 15 positions.
Row 1 is the table heading and the data is contained within row 2 to row 15.
As you register more stocks into Summary_OSV, update the range of the cells to the last row in your spreadsheet.
If you add 10 more holdings, the data range will now be:
‘ChartData-DONTEDIT’!B1:B25, ‘ChartData-DONTEDIT’!D1:D25, ‘ChartData-DONTEDIT’!M1:N25, ‘ChartData-DONTEDIT’!Q1:R25
To see the ranges in more detail, click the grid icon next to the range values to bring up this window.
Update the Range Based on Your Total Positions
You can update ranges from this window too.
Press ok and save.
Step 5: How to Edit the Charts in the Spreadsheet
The other two graphs you need to update include a pie chart displaying sizing and gain/loss.
stock tracker portfolio gain/loss chart
The process is the same as editing the table.
Follow along with this gif.
Truly the Best Stock Portfolio Tracker
There you have it.
After plugging in all my historical transactions, all I can say is that this is my go to portfolio tracker.
It’s simple, there’s no external service trying to access my accounts (other than google) and while it’s not 100% perfect, it has made tracking my portfolio and other stocks much easier.
I will continue to add good features to this Stock Portfolio Tracking Spreadsheet when it becomes a need, but until then, enjoy.
Coming Up
We are working on adding a similar portfolio tracker to OSV Online to make life even easier.
With our current offering of stock screening, side by side comparing, valuations and other features you get with Old School Value, the upcoming portfolio tracker is going to be powerful and simple way to keep up to date.
If you need a quicker and streamlined way to find new ideas, analyze and value stocks – play with the full live demo.
You can get all the live data for AMZN, MSFT, AAPL, BAC and RHI.
To get this kind of information and other exclusive articles before regular readers, get on the VIP Mailing List today.
Article by Jae Jun, Old School Value
