This is the stock tracking spreadsheet for your investment portfolio you get today:
Stock tracking spreadsheet for Google Sheets
Table of Contents show
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 usesstock 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:
- Sign into your Google Docs account
- Click this link to open Stock 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 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
- toSummary_OSV
- toPortfolio 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 thefull 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 Spreadsheetwhen it becomes a need, but until then, enjoy.
As an experienced investor and enthusiast in the realm of stock tracking and portfolio management, I have extensively explored various tools and methodologies to optimize investment strategies. Throughout my journey, I have encountered the challenges faced by DIY investors in finding a comprehensive stock tracking solution that aligns with their specific needs. It was in this pursuit that I came across the stock tracking spreadsheet for Google Sheets provided by Old School Value.
Before adopting this spreadsheet, I had experimented with numerous portfolio trackers, yet none fulfilled my basic requirements. As someone who doesn't engage in complicated transactions like options, shorts, ETFs, or dividends, I needed a tool that could seamlessly handle straightforward tasks such as entering transactions in a single column, automatically updating share counts, and accounting for dividends and stock splits. The Old School Value investment tracker, specifically designed for value investors, proved to be the solution I was searching for.
The spreadsheet's simplicity and efficiency were apparent from the outset. It allowed me to enter transactions with ease, specifying transaction type, stock ticker, transacted units, fees, and stock split ratios. The user-friendly interface ensured that even individuals with minimal spreadsheet expertise could navigate and utilize the tool effectively.
One notable feature of this spreadsheet is its adaptability to different needs. For instance, the original version by Investment Moats, a Singaporean investor, served as the foundation, but I tailored it for the US exchanges, including pink sheets, OTC, and ADRs. This customization showcases the flexibility and versatility of the stock tracking spreadsheet.
Navigating the spreadsheet is straightforward, and users can follow a step-by-step guide on entering transactions, updating summary data, and interpreting portfolio summaries. The Summary tab condenses transaction data into an easy-to-understand format, making it convenient for users to assess their portfolio's performance without going through extensive transaction logs.
The Portfolio Summary section provides a clear profit and loss analysis, avoiding complex metrics and focusing on essential financial insights. The color-coded investment categories in the summary dashboard enhance the user's ability to identify and evaluate different investment styles effectively.
Furthermore, the inclusion of charts in the Portfolio Summary tab adds a visual dimension to the analysis. Users can edit the ranges of cells for data to be updated in the graphs, providing a dynamic and interactive experience. The charts, including a pie chart displaying sizing and gain/loss, contribute to a comprehensive understanding of the portfolio's composition and performance.
In conclusion, the Old School Value stock tracking spreadsheet for Google Sheets has proven to be a valuable asset in my investment journey. Its simplicity, customization options, and focus on essential tracking features make it an excellent choice for DIY investors. Whether used independently or in conjunction with the Old School Value app, this tool has significantly simplified my portfolio tracking process, offering a reliable and efficient solution for managing investments.