Dividend Stock Portfolio Spreadsheet on Google Sheets
Welcome! Thanks for wanting to learn more about my Dividend Stock Portfolio Tracker on Google Sheets.
- Simple and easy to use
- Lives on Google Sheets, allowing your portfolio to be saved automatically and available on any of your devices
- Majority of functions are automated
- Includes a transaction sheet to record stock purchases, sales, dividends, and splits.
- Automatically looks up stock information including latest price, dividend amount, and dividend pay dates from IEX. No more waiting for “Loading…”
- Supports multiple currencies and currency conversions
- Dividend focused with information dense Summary Page and Dividend Calendar showing your yearly dividend history as well as the next dividend pay date. Both these pages are automatically filled in for you.
- Under active development (as my time permits!). First version released way back in 2013.
- Google Sheets only. This is both a pro and a con. If Google Sheets is down or Yahoo Finance is no longer working, the sheet will lose some of its automation. Also, the spreadsheet uses Google Sheets-specific functions and will not work in Microsoft Excel.
- If things break and you aren’t good at using Google Sheets, you’ll require me to fix things. I try to stay on top of this but my job (I’m a doctor) does limit my time.
The spreadsheet is free and will always be available for free. However, if you find this spreadsheet useful, please consider donating to support my coffee fund and hosting costs. Thanks!
Step 1a: Get the spreadsheet
Before you can begin editing the spreadsheet and adding your own accounts, you have to make a copy of it for yourself. After clicking on the link above choose File and then either “Make a copy…” or “Add to My Drive”. You will not be able to add your own stocks to the spreadsheet until you do this.
Step 1b: Sign up for IEX Cloud
This spreadsheet using the excellent IEX Cloud API to look up stock data. In order to access this data you will need to sign up for a free account on https://iexcloud.io/. Once your email is verified, log in and click “API Tokens” on the lefthand column. Copy the token key (which will start with “pk_”) and paste it into cell M6 of the ReferenceData sheet.
Step 2: Learn the basics
Orange-ish cells – These are the cells that you will be editing.
Light green cells – These contain formulas and are automatically calculated by the spreadsheet. If you enter data into these cells you are overwriting formulas. Edit these cells only at your own risk.
Red and dark green cells – These are a subset of the light green cells and reflect losses and gains.
Drop down selections – These allow you to pick from a predefined list. Picking a sector type is an example. You can change the validation for these drop down selections by choose Data > Data Validation… when selecting the desired cell or column of cells.
Step 3: Begin entering your stocks
Start first on the “Portfolio” sheet. This is the main page where you will enter the stocks you own. The orange-ish colored cells in rows 5 and 6 are my data. Delete that information and type in your stocks row by row. The company name can be whatever you want, but the symbols for US traded companies should match how they show up on IEX. You can adjust your symbols by searching for them here: IEX Stocks. This will allow you to search for stocks on the US exchanges. Remember not to edit the light green cells on the right side of the page. This will be populated once you enter data on the Transactions sheet.
For detailed information on each column please visit the Appendix.
Step 4: Entering transaction data
Transaction data is entered in the order that it happens on the Transactions sheet. This page is where you’ll enter all your stock buys, sells, dividends received, dividends reinvested, and stock splits. If you have a lot of historical data, you have a couple of choices. You can either 1) take the time to enter each and every purchase, sale, and received dividend with the dates they happened, or 2) figure out your current cost basis and make a single purchase for that amount in the year you originally purchased the stock.
If I had years and years of data to enter, I’d likely take the aggregate approach myself. I then would calculate the amount of dividends I received each year for each stock, if this information is available, and enter those as aggregate individual yearly transactions. This way I’d at least have the total dividend information for each stock per year.
For information on how to add dividends that you receive and how dividend reinvestment works for this spreadsheet, please visit the FAQ (still underdevelopment).
Step 5: Visit the ReferenceData sheet
This page lists all your stocks in the order that they appear on the Portfolio sheet. Using the script that I discussed above, the last price, dividend per share, ex-dividend date, and the dividend pay date are all retrieved from IEX. These functions will automatically be refreshed when you open the spreadsheet or change the ticker symbols on the Portfolio sheet.
You’ll see that most of this sheet is the light green do-not-touch color. If you are entering a stock, ETF, mutual fund, etc you have the option of manually entering a price and dividend amount per share (per year). Anything typed into those fields will overwrite the automatically retrieved values.
In general, you should not have to edit this sheet much, especially if just purchasing equities traded on the US market.
Step 6: Update the DivPayoutCalc sheet
This sheet is used to calculate the estimated monthly dividends. You’ll have to type in the company name or pick it from the drop down selection (matching what is on the Portfolio sheet exactly) and then type in the months that the stock pays. You’ll only have to come to this sheet when you buy a new dividend paying stock.
A more detailed explanation of this is available here: Automatic Estimation of Monthly Dividends for Google Sheets
Step 7: Visit the DivCalendar sheet
DivCalendar automatically keeps track of the dividends that you receive for each stock and tells you when you can expect the next dividend pay payment. It relies on entering the dividend transactions into the Transactions sheet. Please see the FAQ for further details.
What I’ve done on my own personal DivCalendar is to pre-highlight the months in orange that I expect each stock to pay and then to have conditional formatting change the highlight to a dark green as it gets automatically filled in. The conditional formatting for that is already in place.
For further details, see the blog post discussing the release of this feature here: Automated Dividend Calendar
I got this inspiration from Hello Sucker’s Calendar page. At a glance it gives me a head’s up on the months and days that the companies I own payout. It also allows me to record the dividends as they come in.
Sector: Drop-down menu allows you to pick the appropriate sector. The drop down choices come from the Lists sheet.
Company name: The name of the company. This data will be used in the Transactions sheet.
Ticker: The stock’s ticker symbol as used on IEX or Alpha Vantage.
Cur: From the drop-down list pick the currency that the equity is traded in.
Cost Basis: Cost basis information is calculated from purchases minus net sales and includes reinvested dividends.
Unrealized Gain/Loss & %: These two columns show the current unrealized gain/loss in dollar value and percentage. Conditional formatting gives a green background for gains and a red background for losses.
XIRR: The XIRR function gives a dollar-weighted annualized return that takes into account the timing and amounts of cash flows into and out of your investment accounts. Please see Calculating Your Annualized Return – XIRR Function
Realized Gain/Loss: Money loss or gained in stock sales.
Dividends Collected: Total dividends collected throughout the entire period stock is held.
Total Gain/Loss: Unrealized gain/loss + Realized gain/loss + dividends collected
Annual Dividend: Forward 12-month dividends for the stock calculated by taking the current annual yield * the market value of the position
Dividend Yield: current dividend yield
At the top of this sheet is a portfolio summary section. This provides a breakdown of the performance of each separate account. It is dependent on picking an account for each stock from the drop-down menu on the Portfolio page.
This sheet also records the amount of dividends received each month and allows me at a glance to see how that has (hopefully) increased from year to year. The numbers on this page are based on the entries on the transactions page so make sure you enter and date those correctly. If you desire to update past years by hand, just replace the formula with whatever value you want.
The Summary sheet also calculates annual dividends as well as total realized gain/losses.
By default it displays monthly dividends for the past three years. There is a drop down selection for the year in the first column allowing you to quickly choose any year and have the monthly dividends displayed.
DivCalendar – Div Increases column:
This column is used to track the effect of an increased dividend each time the dividend is raised. It needs to be manually updated and is based on the current amount of shares held at the time you updated it. You can think of it like the amount extra each year that you will be receiving even if you did not add additional capital or reinvest the dividends. To calculate Div Increases I just make note of how much the annual dividend changes after a company increases its dividend.
This field is completely optional and so far this data is not used anywhere else.
Each stock purchase or sale, dividend, and split are recorded on this sheet. It is from this sheet that all the data on the Portfolio page is generated. As long as you keep the transaction sheet up-to-date, nearly everything else is automated.
The inspiration and the general formulas came from the wonderful spreadsheet created here. (Thanks, Kyith!) The formulas and layout have been fine-tuned to fit within my dividend focused Google Sheets portfolio.
Note: Do note delete Row 2. This row contains the formulas, which are pre-populated for the first 100 entries. Thereafter, for any additional rows behind the first 100, the formulas will have to be dragged down from the earlier formulas.
Manually edited cells are again highlighted in orange. The light green cells contain formulas and should not be edited. If these are edited accidentally, just drag the formulas down again from row 2.
Step 1: Enter the date.
Step 2: Choose the type of transaction: Buy, Sell, Div, or Split.
Step 3: Choose the stock. This drop down menu is created from the companies entered into the Portfolio sheet. The data is obtained from the ReferenceData sheet’s list of companies. If you don’t see your company listed, you probably entered a lot of companies on the Portfolio sheet. You’ll have to update the Validation, as below.
“Buy” transaction: Enter the # of shares, the per share price you paid, and any fees. Stock split ratio should be 1.0.
“Sell” transaction: Enter the # of shares, the per share price you sold the shares at, and any fees. Stock split ratio should be 1.0.
“Div” transaction: There are a number of ways to record dividends. You basically just want the “gain/loss from sale” column to reflect the amount of the dividend. What is easiest is to just enter 1 as transacted shares and then the dividend amount in the “transacted price/share” column.
Note: With dividend reinvestment you technically receive cash and then buy additional shares. So, if you are reinvesting dividends, an additional transaction must be entered with the partial shares purchased. This allows the cost basis to be calculated correctly. If entered correctly, the dividend amount should be at positive cash flow while the subsequent dividend reinvestment will be the same amount only at a negative cash flow.
“Split” transaction: Just enter the split ratio. Ensure that “transacted shares,” “transacted price,” and “fees” are either 0.0 or blank. Apple’s recent 1 to 7 split would be entered as 7/1 = 7.0.
Notes: Enter any notes that you may want to keep for a particular transaction
This sheet provides the drop down boxes for the sector and account type information in the Portfolio sheet. If you want to add or update the sectors or add new brokerage accounts/types, this is the place to do it.
If Needed: How To Authorize the Google Script
This spreadsheet uses the Google scripting language to refresh the latest price and dividend information from Yahoo Finance. I’ve found that using the standard =importhtml and/or =importxml codes are limited in a few ways. There’s a finite number that can be used per spreadsheet and they are frustratingly slow. Don’t you hate getting the “Loading…” notification in the cells trying to look up your stock price or dividend amount? The script that is attached to this spreadsheet should fix those issues.
- Go Tools > Script Editor
- Get authorization for your spreadsheet to use the script
- Click on the drop down and select IMPORTJSON
- Click on the Run button
You will then be prompted that “Authorization is required.” This allows the script to edit your spreadsheet and retrieve data from Yahoo Finance.
Click Review Permissions and then Allow on the next screen. Depending on your browser, you may get a warning that looks like this:
Click Go to PortfolioTracker (unsafe) to continue. (The script does nothing else but allow Yahoo Finance data on your stocks to be updated.)
Please do not hesitate to ask me any questions or offer suggestions about this template either via the comments section below or the Contact Us link. I’ll try to answer them as quickly as I can.
*** These spreadsheets are solely for entertainment purposes. Please don’t rely on them for any official/ tax-related functions. ***