The Best Free Stock Tracking Spreadsheet using Google Sheets

This is the stock tracking spreadsheet for your investment portfolio you get today:

Stock tracking spreadsheet for Google Sheets

The Best Stock 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 way of (well made) stock trackers.

That’s why every serious DIY investor still uses stock tracking spreadsheets – and why you’re here today.

Before Old School Value introduced its own investment tracker, which is what I now use for everything, I’d 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 tracking to be a bigger nightmare than it already is.

Before the OSV portfolio tool, I’d 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 was stuck doing things manually.

Stock Tracking Spreadsheet Must-Haves

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 Investment Tracking 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 overcomplicated it.

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

So you have two options. Use the original or use my edited US version.

Saving the Google Sheets Stock Spreadsheet

Important:

• DO NOT request to share the spreadsheet. Just make a copy yourself. See below.
• Yellow cells are 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:

While you’re at it, you can also get more spreadsheets and checklists by registering your email with Old School Value.

But before we get deep into portfolio tracking, fill in your information below and I’ll immediately send you the best free investment checklists and more investment resources to help boost your investing.

How to Use The Investment Tracking Spreadsheet

Once you’ve saved the stock spreadsheet to your account, there are just a couple 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

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.

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

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.

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.

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.

The Best Excel Stock Tracker

There you have it. It’s even better than an Excel stock tracker because it’s in Google Sheets.

After plugging in all my historical transactions, this became my go to portfolio tracker. Then, we added all the same features and functionality into the Old School Value app, so it’s even easier and more automated for you.

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 Tracking Spreadsheet when it becomes a need, but until then, enjoy.