Dividend Stock Portfolio Spreadsheet on Google Sheets

Welcome! Thanks for wanting to learn more about my Dividend Stock Portfolio Tracker on Google Sheets.

Advantages:

  1. Simple and easy to use
  2. Lives on Google Sheets, allowing your portfolio to be saved automatically and available on any of your devices
  3. Majority of functions are automated
  4. Includes a transaction sheet to record stock purchases, sales, dividends, and splits.
  5. Automatically looks up stock information including latest price, dividend amount, and dividend pay dates from IEX. International stocks are updated via the Alpha Vantage API. No more waiting for “Loading…”
  6. 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.
  7. Under active development (as my time permits!). First version released way back in 2013.

Cons:

  1. 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.
  2. 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.

Click here to access the Dividend Stock Portfolio Spreadsheet

 

151xbHsMKpNBtHPb1tsacGWzoaCKYG9QH6   

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: 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.

  1. Go Tools > Script Editor
  2. Get authorization for your spreadsheet to use the script
  3. Click on the drop down and select IMPORTJSON
  4. 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.)

 

 

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. 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.

International stocks will be entered by their exchange and ticker symbol. I.e., Toronto-Dominion on the TSX is entered as TSX:TD. Alpha Vantage API is also used to provide the latest stock price information for international stocks. See: Replacing Yahoo Finance with the IEX API and Alpha Vantage in Google Sheets

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

divpayoutcalcThis 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

Automated Dividend Calendar

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.

 

Appendix

 

Portfolio sheet:

Sector: Drop-down menu allows you to pick the appropriate sector. The drop down choices come from the Lists sheet.
Account: Use this drop-down menu to choose the type of account that the stock is held in. Brokerage, Roth IRA, etc. The choices can be updated on the Lists sheet. The account information is currently used to help the Summary sheet tabulate information for each separate account.
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.
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
YOC: yield-on-cost

 

Summary sheet:

Summary

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.

 

Transactions:

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.
drop down menu

To update the drop down menu, first choose Validation from the Data menu:
validation1

Then update the range to include all the companies listed on the Portfolio sheet:
validation2

“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

 

Lists:

lists

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.

 


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. ***

You may also like...

29 Responses

  1. Borja says:

    Hi Scott, great improvements to the spreadsheet, as usual.

    Will you modify the international version with these new updates? It’d be awesome.

    Regards.

  2. Venkata says:

    Is there a link to access the srepadsheet ?
    When I try the above link, I get to a page where I see this message “Welcome! Thanks for checking out my Dividend Stock Portfolio tracking spreadsheet. To get started please follow the directions on the website.” Here is the page link I got to https://docs.google.com/spreadsheets/d/1JJlY3HIy1zwOT36TScrWYORf4HvuKR43VEhSUJS_8Ps/edit#gid=1209285505

    • scott says:

      Yep. You got it already. You first need Make a Copy. Follow the directions at the top of this page. The data is entered on the Portfolio sheet.

      Scott

  3. Nuno says:

    Hi Scott,
    thank you for the spreadsheet

    can you tell me in which sheet do i add cash (money injection)?
    Regards

    • scott says:

      Hi Nuno,

      I currently don’t provide a way to track cash inflows and outflows. There is a section on the Portfolio sheet to type in available cash, but that is really only used to add up total portfolio value. How were you wanting to use the money injections?

      Scott

  4. Philip says:

    i get a strange error message …

    Fehler
    Request failed for https://download.finance.yahoo.com/d/quotes.csv?s=O&f=l1&p=.csv returned code 403. Truncated server response: Yahoo! – 403 Forbidden — error 403It has come to our attention that this service is being used in violati… (use muteHttpExceptions option to examine full response) (Zeile 90).

    what did i do wrong?

  5. Andras says:

    Hello Scott,
    I’m using an older version of your Spreadsheet and I want to update to the latest version. What is the easiest way?
    Copy over the transactions and reset my stocks on the Portfolio tab?
    Thanks.

    • scott says:

      Hi Andras,

      It is very easy to do. You have it exactly right. Just copy over the data you typed into the orange-ish colored cells on the Portfolio sheet and then copy over the same orange-ish colored cells from the Transactions sheet. Let me know if you have any further questions.

      Scott

  6. Venkata Annambhotla says:

    I get this message:
    Request failed for https://download.finance.yahoo.com/d/quotes.csv?s=AFL&f=l1&p=.csv returned code 403. Truncated server response: Yahoo! – 403 Forbidden — error 403It has come to our attention that this service is being used in violati… (use muteHttpExceptions option to examine full response) (line 90).

    Can you tell me if this is something that can corrected ?

  7. Bobby says:

    I cant seem to get TSX tickers to work… any idea?

  8. Ross says:

    Scott, I noticed on the transactions page I think its calculating the total number of shares wrong.

    I have 6 entries for a stock the first 3 were a buy and the next 3 were dividends but the total number of shares remained unchanged. (0, 100, 230,. 321, 321, 321) Is this normal?

  9. aukalou says:

    Hi! I am inputing transactions for stocks I used to own. Kohl’s Corp, symbol KSS was one of them. All the other stocks I’m using seem to be pulling data just fine. However, I get an error on this one on the DivPayoutCalc page in the Annual Dividend and Dividend Payout columns. Error says: Error
    Unable to parse query string for Function QUERY parameter 2: PARSE_ERROR: Encountered “s” at line 1, column 24. Was expecting one of: “group” … “pivot” … “order” … “skipping” … “limit” … “offset” … “label” … “format” … “options” … “and” … “or” …

  1. November 12, 2017

    […] After struggling for a week or so, I finally found a couple guys who had been building a similar spreadsheet on Google Sheets and were sharing how they pulled data from iextrading – so far it seems to be working well … BUT I’ve lost years of data and don’t intend to rebuild it again. So for now, I’ll start tracking from November which will give me a good start for 2018 unless we get shut off again. If you have a dividend investing and trading strategy, check out twoinvesting.com.  […]

Leave a Reply

Your email address will not be published. Required fields are marked *

CommentLuv badge