The BEST Stock Portfolio Tracker Spreadsheet Ever! – How I Keep Track of My Dividend Growth Portfolio Using Google Sheets (Part 3 – Stock Portfolio Overview)
Page Contents
Like I promised in part 2 of my portfolio tracker series, part 3 of my portfolio tracker post will show you how to create a portfolio overview. If you haven’t read part 2 of my post, I highly recommend you do so first because the formula in this sheet will be connected to the that of part 2: trade log.
A warning: This post is going to be lengthy and full of text. Feel free to stop at anytime to take a break and pick up where you left off. I will try to to break this into sections to make it easier.
This sheet lists all my holdings in detail and shows
- Ticker. You have to input this cell manually
- Name of the company.
- Number of shares and the cost basis
- Current price
- Today’s 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
The Layout
You should create your stock portfolio overview in a new sheet and it can look however you want it to look. Personally, I lay out my cells in the following way.
In this page, I only need to input
- Cash available in each account,
- Ticker symbol the first time the stock is purchased
- Account that first time purchases the said stock.
- 1 of 6 Currency available (converted from USD)
Everything else will automatically be populated by the spreadsheet.
A note about the currency conversion. All currency is converted from USD, so based on the currency chosen, every information in this page will be in the selected currency.
This is particularly useful when you are investing in the U.S stock market but your capital is from a different currency, like me.
Key Info Section
I put my cash balance and, total gain/ loss, annual dividend and income on the top of the sheet because you don’t want to scroll all the way down just to find key information about your portfolio.
Now for the header of the portfolio section, I include horizontally:
- Name of the company
- # of shares
- Cost Basis
- Total Cost Basis
- Current Price
- Today’s Change (in $ and %)
- Gain/ Loss (in $ and %)
- Total Gain/ Loss for that stock
- Annual Dividend Income per share
- Dividend Yield
- Total Annual Income of the Dividend
- Assets Type
- Sector
- Account that purchases the stock
- % allocation to the portfolio for the stock
Since I am using screen shot, I am sorry for the quality of the picture but here goes:
The Formula
The Basic
A lot of the formula that I used is just designating the right cells for the formula to refer to in the equation, thus a basic understanding of how Google Sheets’ formula works will help in understanding the rest of this post.
=sumifs
A formula that tells the spreadsheet to sum specified cells IF a number of criteria is found.
‘Sheet name’!A1
The cell will go to sheet “Sheet name” and refer to that sheet’s cell A1
Name of the company
=googlefinance(B16,”name”).
B16 is the cell for the ticker that you want the spreadsheet to refer to when fetching the name. For example, if you put “AAPL” in B16, then this cell will refer to “AAPL” and produce “Apple Computers Inc.”
# of shares
=SUMIFS(‘Trade Log’!$E$1:’Trade Log’!$E$1000,’Trade Log’!$C$1:’Trade Log’!$C$1000,B16,’Trade Log’!$B$1:’Trade Log’!$B$1000,”Buy”,’Trade Log’!$D$1:’Trade Log’!$D$1000,W16)-(sumifs(‘Trade Log’!$E$1:’Trade Log’!$E$1000,’Trade Log’!$C$1:’Trade Log’!$C$1000,B16,’Trade Log’!$B$1:’Trade Log’!$B$1000,”Sell”,’Trade Log’!$D$1:’Trade Log’!$D$1000,W16))
‘Trade Log’!$E$1:‘Trade Log’!$E$1000 = Column E1 to E1000 of the Trade Log sheet where you input # of shares bought or sold
‘Trade Log’!$C$1:’Trade Log’!$C$1000 = Column C1 to C1000 of the Trade Log Sheet which is the column where you input your ticker symbol
B16 = The cell where you input the ticker symbol for the stock in the equation
‘Trade Log’!$B$1:’Trade Log’!$B$1000, “Buy” = Column B1 to B1000 of the Trade Log Sheet which is the column where you input the action relevant to the stock. In this case, you are telling the cell to refer to column B of the Trade Log Sheet to search for action “Buy”
‘Trade Log’!$D$1:’Trade Log’!$D$1000 = Column D1 to D1000 of the Trade Log Sheet which is the column where you input the account that holds the stock
W16 = The cell where you input the Account Name for the stock in the equation
Essentially, with this formula, you are telling the cell to look in the Trade Log Sheet for the ticker symbol (B16) and the Account Name (W16) that has the action “Buy” (Column B in the Trade Log Sheet) and deduct amount by the same ticker symbol and Account Name that has the action “Sell”, giving you the final amount of shares that you own.
Cost Basis
=((SUMIFS(‘Trade Log’!$H$1:’Trade Log’!$H$1399,’Trade Log’!$C$1:’Trade Log’!$C$1399,B16,’Trade Log’!$B$1:’Trade Log’!$B$1399,”Buy”,’Trade Log’!$D$1:’Trade Log’!$D$1399,W16))/(SUMIFS(‘Trade Log’!$E$1:’Trade Log’!$E$1116,’Trade Log’!$C$1:’Trade Log’!$C$1116,B16,’Trade Log’!$B$1:’Trade Log’!B$1116,”Buy”,’Trade Log’!$D$1:’Trade Log’!$D$1399,W16)))*$F$6
‘Trade Log’!$H$1:’Trade Log’!$H$1399 = Column H1 to H1399 of the Trade Log which is the column for the total price of the stock purchased/ sold.
If you follow along the explanations above, essentially this lengthy formula is telling the spreadsheet to sum up the total purchase price for a stock divided by the number of shares purchased (which the formula will validate from action “Buy”) giving us the average cost basis. We then multiply this number by the currency exchange rate (which the formula will find from cell F6)
Don’t worry about the currency exchange formula, as it will be explained in future posts.
Current Price
=GOOGLEFINANCE(B16, “Price”)*$F$6
Again B16 is the cell that has the ticker symbol we want the equation to look for
F6 is the currency exchange rate
Today’s Change (in $ and %) & Today’s Change Total
=googlefinance (B13, “change”)
This formula will return today’s change in $ of the stock ticker in cell B13
=googlefinance (B13, “changepct”)/100
By default, Google Sheets will returns this formula as an absolute value, for example 1% will become 100, so I divided by 100 to get the right value.
You would then go to Menu > Format > Number > Percent to get 1%
In order for you to get your Today’s Change Total, simply multiply the cell for Today’s Change (in $) with the cell for # of Shares
Gain/ Loss (in $ and %)
To get your gain/ loss of the stock,
=(Current Price Cell) – (Cost Basis Cell)
and to get the %
=(Current Price Cell) – (Cost Basis Cell)/ (Cost Basis Cell)
and to get the total gain/ loss for that stock, just multiply the cell for gain/ loss (in $) with the cell for # of Shares
Annual Dividend
Brace yourself, this will be one of the lengthiest section of this post. Here goes:
=iferror(iferror(if(B16=””,””,split(index(importhtml(CONCATENATE(“https://finviz.com/quote.ashx?t=”,B16,””),”table”,9),7,2),”*”)),if(isblank(B16),,GETDIVIDEND2(B16,$N$9)))*$F$6,”-“)
B16 = The cell where you input the ticker symbol for the stock in the equation
N9 = The cell where you input your IEX Token Key
F6 is the currency exchange rate
With this formula, you are telling the spreadsheet to find the dividend yield from finviz.com (don’t worry about the table 9, 7, 2. That’s simply where the dividend yield information is located in finviz.com)
The iferror formula is telling the spreadsheet that IF the spreadsheet generates an error return when trying to get the information from finviz, it will switch to another formula where it will get the information from IEX Cloud. IF that also generates an error, then the cell will show “-“.
The formula to get the dividend yield from finviz.com is
=iferror(if(B16=””,””,split(index(importhtml(CONCATENATE(“https://finviz.com/quote.ashx?t=”,B16,””),”table”,9),7,2),”*”)),”-“)
The back up formula is
=if(isblank(B16),,GETDIVIDEND2(B16,$N$9))
This is not a Google Sheets formula but a script that you need to create in Google Sheets. A custom formula so to speak.
In order to create this custom formula, you need to go to Menu > Tools > Script Editor like so:
You will then see a new screen like so
On the right side is where you are going to insert the code. Just copy and paste from this link, click save, name your code whatever you want and your formula is good to go.
Dividend Yield & Annual Income
To get your dividend yield, you need to input into the cell
= (Annual Dividend Cell) / (Current Price Cell)
To get your annual income from that stock, you would use
= (Annual Dividend Cell) x (# of Shares Cell)
And that’s it. You have just finished creating your Stock Portfolio Overview Sheet.
Pat yourself in the back, because this was a long tutorial and you have made it to the end.
As usual, leave a comment or hit me up if you have an suggestions on how you think I can do better or to show how you create your Trade Log.
If you can’t wait and you want to get your hands on the final product as soon as possible, this template is available to purchase from Gumroad.
Also, don’t forget, when you use my referral links, you will be able to claim your free stocks from Robinhood and WeBull. You will help support this blog and I’ll jump and click my heel in gratitude.
