The BEST Stock Portfolio Tracker Spreadsheet Ever! – How I Keep Track of My Dividend Growth Portfolio Using Google Sheets
Page Contents
If you have multiple brokerages/ accounts to manage your investment portfolio, you know keeping track of all of them can be a hassle sometimes.
In this post, I will show you how I use Google Sheets to create an automated portfolio tracker that requires minimal input from the user.
I am planning for this post to be a series where I will break down the features of my spreadsheet in different posts and show you how you can create one of your own from scratch.
Why Have More than 1 Account?
Free stocks, that’s why.
In the past few years, investment brokers are starting to offer free stocks whenever you sign up with them and I am never one to pass up on free money/ assets.
For example, for the month of October, when you sign up for an account on WeBull using the link below and deposit $100, you will get 2 free stocks valued up to $1,600. Even if you ended up not buying any stocks and withdraw the $100, you still get to own the 2 free stocks
You will then repeat this process with Robinhood, where you can get a free stock when you use the link below.
So, in about 15 minutes worth of work, you just get 3 free stocks with virtually no capital.
Different Account Serves Different Purpose
Another reason is because you may have a tax advantaged account, retirement account and a regular tax-able portfolio account.
Just from the above reasons, if you are like me, you are going to already have at least 4 accounts which may belong to different brokerages.
Spreadsheet to the Rescue!
Anybody that knows me knows that I am a huge spreadsheet nerd. So, when I couldn’t find a portfolio tracker that ticks all the boxes like I need it, I decided to create my own using Microsoft Excel at first and later, Google Sheets.
I knew when I started my portfolio tracker, I wanted:
- Low maintenance (preferably once a month or once a quarter) and requires minimal input
- Cost Basis for my stocks after DRIP (Dividend Reinvestment Program)
- Number of shares after DRIP
- Dividend Tracking
- Deposit/ Capital Tracking
- The ability to access the spreadsheet anywhere
Portfolio Dashboard
The first page in my spreadsheet is my portfolio dashboard where I can see key information of my portfolio in a glance:
- Portfolio Value and total gain/ loss
- Cash available to purchase securities
- Average dividend yield of the portfolio and the yield on cost
- Annual income from dividend
I also included key charts and graphs because I like colorful things and charts and graphs allow a different visual perspective of my portfolio.
Charts and graphs include:
- Holdings by sectors
- Holdings by companies
- Monthly dividend income year to year
There is absolutely nothing that I need to input in this sheet and this sheet is strictly for aesthetic value and when I just need to look at key infos.
Capital
The next sheet is the capital page and where I keep track of how much I have invested into the accounts.
Every time I inject more capital into my portfolio, this page is where I record the entry, whether or not I purchase anything with said cash
Stock Portfolio Overview
This sheet lists all my portfolio values and holdings in detail and shows
- Ticker
- Number of shares and the cost basis
- Current price
- Today Change in price (in $ and %)
- Total Gain/ Loss (in $ and %)
- Annual Dividend, Dividend Yield & Yield on Cost
- Account that purchases the stock
- Sectors and Assets Type
- Allocation of the stocks in proportion to the whole portfolio
- Real time currency conversion based on USD
In this page, I only need to input cash available in each account, the ticker symbol the first time the stock is purchased and the account that purchases the said stock. Everything else will automatically be populated by the spreadsheet.
Lately I have been having problems with Google Sheets’ function to fetch the dividend information, as it keeps on timing out and returning “#N/A” result or errors. To tackle this, I set up 2 back-up functions using add-ons and iex token (which I will elaborate more on how to set up in my next posts).
Essentially when Google Sheets’ function returns an error, the spreadsheet will automatically fetch the dividend information using the iex token method or the “IMPORTFROMWEB” add-on method
I also included a real time currency converter in this page so based on the currency chosen (converted based on USD), every information in this page will be in the selected currency.
In the near future, I’m planning to make the cash part to be populated automatically by the spreadsheet based on the information from the capital sheet
Trade Log
This sheet records all trade transactions that I have done and will automatically update all the information in the stock portfolio sheet.
Every month, I recorded all the reinvested dividend, stock purchase or sales and my cost basis and number of shares will be updated automatically.
Dividend Overview
This sheet is where I keep track of everything dividend in my portfolio.
- How much dividend I earn by sector
- How much dividend I earn every month year to year
- How much dividend I earn by account
Of course I included charts.
I do have to input the monthly dividend manually because I set up my portfolio to reinvest its dividend but my plan is in the near future, even this will be automated based on the information from the trade log sheet.
Future Updates
I constantly update this spreadsheet based on my needs and suggestions from people that use this tracker.
For now, I am planning to include the following in the future updates:
- Automated available cash information
- Automated monthly dividend earned information
- Notifications via email when certain cells (e.g stock price, gain/ loss %) reach a value that interests the user
- Options contracts
Where Can You Get This Spreadsheet?
Like I mentioned above, I mean to create a series of posts where I will explain in detail how you can DIY this spreadsheet from scratch and this first post is just meant to be a walkthrough of what I am using
But, if you can’t wait or the DIY road is not something that you are interested in, this spreadsheet is also available for purchase via Gumroad.
Also, don’t forget, when you use these links, you will be able to claim your free stocks
As usual, leave a comment or hit me up if you have an suggestions on how you think I can do better.
Update #1: Continue to Part 2 (“Creating a Trade Log”) HERE!
