The Best Free Stock Portfolio Tracking Spreadsheet

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 do-it-yourself 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, ETFs 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 web sites 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.

I also made some edits to tailor it for the U.S. exchanges – including pink sheets, OTC and ADRs.

So you have two options. Use the original or use my edited U.S. 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:

1. Sign in to your Google Docs account.
2. Click this link to open Stock Portfolio Tracking Spreadsheet.
3. Go to File > Make a Copy.
4. 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.

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 backward 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 preloaded 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 that 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 percentage returns, CAGR calculations, year to date, 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” that 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 uses it 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 a 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.

Disclosure:Â No stocks held.

Start a free seven-day trial of Premium Membership to GuruFocus.