Dividend Stock Portfolio Spreadsheet on Google Sheets

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


  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. Supports multiple currencies and currency conversions
  7. 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.
  8. Under active development (as my time permits!). First version released way back in 2013.


  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



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.




Portfolio sheet:

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
YOC: yield-on-cost


Summary sheet:


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

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

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

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


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

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


  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.


  3. Nuno says:

    Hi Scott,
    thank you for the spreadsheet

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

    • 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?


  4. Philip says:

    i get a strange error message …

    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?

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


  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?

    • scott says:

      Yahoo Finance recently was turned off so I had to switch the method I use to pull the financial data. I’ll have to look into stocks trading on non-US exchanges and see how I can get them working.


      • Bobby says:

        I was able to only get current prices updating on the “Reference” Page.

        Still having no success in pulling data for Dividend/Share, Ex. Div. Date and Div. Pay Date.

        Really appreciate the hard work!

        • scott says:

          What kind of error messages are you getting? Are you using ticker symbols as listed on IEX?

          • Bobby says:

            No, I am trying to get data for Canadian stocks. IEX doesn’t have support for TSX.

            I was attempting to use GOOGLEFINANCE api for date.

          • scott says:

            You’re correct. IEX doesn’t yet support non-US markets. I am working on a version that using Bloomberg to retrieve prices from foreign exchanges. Hopefully will release that tonight.

    • scott says:


      I just integrated support for the Alpha Vantage API. Just grab the latest version and scripts and make sure your ticker symbols are in the correct format. TSX:TD will work.

      More details on this feature are here: Replacing Yahoo Finance with the IEX API and Alpha Vantage in Google Sheets

      • Bobby says:

        Appreciate the work put into this, I ended up tweaking this myself using GOOGLEFINANCE for stock prices, and using manual dividend for annual dividends.

        Are you able to put in a column to add manual Ex. dividend & pay dates? Or supply the formula would be sufficient.

        • scott says:

          Hey Bobby,

          Was the IEX API not working? I’d just overwrite the formulas in lieu of creating separate columns to manually enter the dividend and pay dates.


          • Bobby says:

            I found that stocks were appearing correctly at times but then disappearing.

            Right, I will just overwrite the formulas then. I will keep my eye out if you come across any more updates on the spreadsheet.

          • scott says:

            Hmm. I’d like to look into that. Do you know if it was happening consistently with a certain stock? Did it happen with just a certain type of formula or did it involve last price and dividend amount, as an example, at the same time?

            Thanks for the help!

  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?

    • Ross says:

      Nevermind – I guess a DRIP is a Buy! Silly me.

      • scott says:

        Hi Ross. Yep, you’re right. Partial shares of stock purchased through a DRIP do affect cost basis so will be classified as a Buy on the Transactions sheet.


    • Billy says:

      Hello Ross, When entering DRIP transactions, be sure to enter a negative number in the Price/Share column. This will ensure that the spreadsheet calculates the gain/loss correctly.

      • scott says:

        Hi Billy,

        Thanks for the comment. DRIP transactions should be entered as such: 1) Dividends should be entered as type Div. I then put in number of shares as zero and the price as the amount of the dividend.
        2) Enter a second entry for a type Buy for the amount of shares purchased with the DRIP. This will allow correct tracking of dividends and cost basis.

        Why should it be entered as a negative number?


        • Tim says:

          Hi Scott,
          For Drips, here you recommend Div with zero shares and price as amount of the divided. then a buy transaction for the partial shares. the zero shares results in no cashflow. In your article, under transactions you suggest enter 1 share and the price as the dividend paid followed by a buy transaction. this results in the proper cashflow (as I see it), but always adds an unreal share to the total. Is there a best way? Thanks, Tim

  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” …

  10. Rick says:

    Hi Scott,
    It seems that my Reference Data Sheet is not updating for stock price changes at all. I tried to review how to replacing Yahoo Finance with the IEX API if that is the reason why it does not update…, but script editor under Tools is grayed out and cant open so I am not able to make the script changes necessary to update the reference data? Can you help figure out why? then I will follow the instructions in your 11/14 update.

    • scott says:

      Hey Rick,

      I’m at my girlfriend’s family for Thanksgiving so won’t be able to look too much into it. Did you grab a new copy of the spreadsheet and then copy your old data into it? I’m not sure why the script editor is grayed out…

      • Rick says:

        Hi Scott
        Yes I did do that do not sure why it is grayed out
        Thanks for checking.

        • scott says:

          Can you send me an email at the Contact Us link at the top of the page. Seeing some screenshots of what you mean might be helpful.

          • Rick says:

            Hi Scott, with the issues I was having, i decided to start over and transferred all of my information to the new spreadsheet again. Now all prices and information is updating very well! Must have been a glitch of sorts, and with this new transfer, I did not have to work with script editor as it looks like you have taken care of that with the change to the IEX API- thanks! am I am good to go for now.. BTW, are you planning on adding the watch list to the new version?


  11. Dave says:

    Hi Scott

    Trying to download the latest spreadsheet, when i try “copy” or “download to” a send it to my drive the spreadsheet remains in “view only” mode. Am I doing something incorrectly?


  12. kevin says:

    Scott – thanks for your work in putting this spreadsheet together and maintaining it. the spreadsheet i used before was linked with yahoo so i was left in the cold. i am in the process of setting up my holdings on your spreadsheet but have one question: there are two options on the ACCOUNT column. i also have holdings in my 401k. Can i add 401k to the drop down menu and it not be affected when you issue updates?



    • kevin says:

      Scott — after getting deeper into entering my portfolio, i am not going to worry about separating the holdings by account but enter it all as one portfolio. i really like the spreadsheet!! i see a lot of the same features i liked in the excel / yahoo sheet i built and some additional features that i had not thought of.

      thanks again!


      • scott says:

        In the current version there is a hidden sheet called “Lists” on which you can add other accounts. However, in a soon to be released future version I’m going to get rid of the separate accounts column.

        If you own the same stock in two accounts, just name them different to distinguish them.

    • Tyler says:

      I am Having an issue getting my data to transfer over to the “summary” page. I see that it is ran based on the “account” column and I see it mentioned here. I do not see the account column anywhere and feel that may be the issue. Help?

  13. Yoav says:

    Thank you very much! Your work is just great!
    Someone here was mentioning an international version?
    Where can I find it?
    Right now (with the regular version) there is a problem with the currency.
    There is no conversion to USD.
    Also for example in the London exchange prices are in pence, but the market value seems to be calculated by a simple multiplication (without dividing by 100).

    Again, thank you very much!

    • scott says:

      Hi Yoav,

      I’m actually combining some of the features of the international version into the standard portfolio spreadsheet. Currency conversion is one of those features.

  14. Doug says:

    Hello, Thank you for the spread sheet.

    One comment: Should the market value of the holding include all of the dividends paid too? In the spread sheet the market value only includes the initial purchase.

    • scott says:

      Hi Doug,

      The dividends (if they are used to purchase more shares) are included. The formula takes the number of shares times the stock price. If the dividends are not reinvested, then they are not including the stock’s market value.

  15. Alexander says:

    Hey Scott,

    wonderful sheet – thank you very much!

    One question – It seems has trouble to get ex dates and div pay dates from IEX. Is it a common problem, or are the dates not announced yet? Error getting data is the message – to be seen in ReferenceData and DivCalaendar.

    Thank You

  16. Deepa M says:

    hi Scott,
    I am trying to use this spreadsheet. Entered aggregated data ..most stuff seems getting populated but ReferenceData sheet is failing to populate the wx-DIV date, DivPayDate columns ..seems like it is making an API call to IEX but not getting any response back … I did run the IMPORTJSON script as well ..any clues what could be going wrong
    Error in those two columns is “Error getting data”.
    I tried the API in browser too: https://api.iextrading.com/1.0/stock/AAPL/dividends/ytd
    Seems to return empty JSON array.

    • Deepa M says:

      any updates @Scott?

    • scott says:

      Hi Deepa,

      I’ve been working on this Sunday while also watching some playoff football. I’ve found that changing “ytd” to “1y” ends up working better. This has been implemented in an updated version I’m working on and will be releasing soon.

      Also, there have been some known issues related to the IEX API. These can be viewed at https://github.com/iexg/IEX-API/issues.

      If the ex-div date and pay dates don’t work, the majority of the spreadsheet will still work…the ex-div date and pay dates are only added for convenience.


      • Deepa M says:

        thank you Scott. Do I need to do something to get the fixed version or it will be automatic for the local copy?

        • scott says:

          You’ll need to copy the new version and paste in your data that you entered on the current one. Typically you just copy and paste the data in the orangish cells as everything else is calculated by formulas.

  17. Glenn Dixon says:

    Same errors as Alexander and Deepa M – was all working before…

    • scott says:

      Also, re: ex-div date and pay dates calculations: These will only work for IEX data (namely US stocks). Alpha Vantage does not yet have that data as far as I know.

  18. Josh says:

    Same issue as Deepa , Alexander and Glenn and I also have an issue with the “Last price” column in the “ReferenceData” -tab. It is not updating and has not done that for a while.

    • scott says:

      Is it displaying nothing or 0? If it is displaying 0, it might be that you are trying to access the API too quickly. I have another function which puts in a delay and seems to work for the 0 issue.

      If it is displaying nothing, either the ticker symbol is wrong or there’s an issue with the Alpha Vantage API. I’m favoring the troubles being with the API if it was once working.

  19. Tim says:

    Market value of Canadian stocks are not correct. It multiplies the Canadian price with the amount of shares you have and doesn’t convert the price to usd.

  20. Venkata says:


    Thanks for the for the spreadsheet and have been using for the past few months. I updated to the recent version and am trying to see the breakdown of accounts on the Summary page. Since the “Account” is missing on the Portfolio page, where do I select the Account (Brokerage/IRA) ?


    • scott says:

      Hi Venkata,

      I removed the separate of the accounts in the latest version. Keeping it in would have made some of the calculations difficult.

      If you own the same stock in multiple accounts, just name them differently. For example, “AAPL (IRA)” and “AAPL (brokerage)”.


  21. Jean Peuplu says:

    In “DividPayouCalc”,… select O where C='”&A2&”‘ … should be …select O where B='”&A2&”‘…

  22. Cathy Kelley says:


    As of today, I am getting the message “error getting data” on all stocks on the ReferenceData Sheet. The weird thing is I made a new purchase today and that stock at the bottom of the page on ReferenceData loaded LAST PRICE and DIV PER SHARE correctly. The Fields EX DIV DATE and DIV PAY DATE didn’t load and haven’t loaded on any of my stocks for some time but I don’t use that information so that doesn’t bother me. I am using version 5.0.1 .
    Any idea what might be wrong?

    Thanks in advance for your help, it is much appreciated.

  23. Scott says:

    Hi. When I enter “T”, the correct symbol for AT&T, it defaults to “Total”. Any fixes for this? thanks

    • scott says:

      It is “auto correcting” to Total since that is typed in another cell. That’s a feature of Excel/ Google Sheets. You’ll just have to delete the “otal” from “Total.”

  24. Venkata Annambhotla says:

    Thanks for your reply.

    I see “#N/A” in “Annual Dividends” and “Dividend Payout” columns on the DicPayoutCalc tab of the spreadsheet. Is there something that I need to do to correct this error ? I have tried to re-enter my data in a new spreadsheet as well, but, the error still exists. Can you take a look ?

    • Greg says:

      Make sure that the currency value is set in your portfolio tab. It’s in the green section but does not seem to be updating. I have to set it anytime I add a new stock to the sheet. This will also clear up the errors on the portfolio sheet (like total calculations).

      • scott says:

        The column that does the currency calculations is hidden. If a new row is added, you’ll have to make sure those formulas propagate. Thanks for the tips, Greg!

    • Venkata says:

      I get an error on the divpayoutcalc tab for annualdiv column –> #N/A. How do I resolve this error ?

      • Steve says:

        The formula is broken on the current live site. You need to update it. Goto the DivPayoutCalc and update C2 with the below:

        =if(isblank(A2),,query(Portfolio!$B$5:$O$70,”select O where C='”&A2&”‘”))

        =if(isblank(A2),,query(Portfolio!$B$5:$O$70,”select O where B='”&A2&”‘”))

        After fixing C2, you will need to copy that formula down to fix all of them.


  25. Wingdub says:

    Scott- extremely impressive worksheet! I thank you for all the time, energy, and expertise. Some of my mutual fund symbols aren’t auto-populating the dividend-focused tabs. Any way to auto-populate the dividend data from mutual funds? I understand the Transactions tab needs to be populated manually as Buy for the initial purchase, Div for each dividend, and Buy for each reinvested dividend. Then, once I view the ReferenceData tab I have all incomplete data (except the Symbol, Name, Last Price, and Cost Per Share) as everything says “Error…”. I understand I can fill the Orange cells (Manual Price and Manual Div) but I’m not sure what to put in those cells (is Manual Div a sum of all the shares of reinvested dividends)? What is the Manual Price (seems way to complex to match it to the ongoing adjusted reinvested dividend prices as I’m unfamiliar with any way to automate this). Accordingly, I assume it’s best to disregard the Orange cells and enter manual data in place of any “Error…” messages but then I’m unfamiliar with what rows E, F, H, and I signify and how to find the data.

    Also, is it necessary that I input all dividends and reinvested dividends in the aforementioned manner even for common stocks which have pre-filled data in the ReferenceData tab?

  26. Krister says:

    Thanks for great work!
    Trying to get it work for me and looks promising so far.
    Do you know a good site for correct notations to be used for international stock exchanges?

  27. Timothy says:

    I love this spreadsheet i added in everything in the orange field some of the field show nothing at all, like market value, market weight, Unrealized Gain/Loss %, XIRR, Realized Gain/Loss, Dividends Collected, Total Gain/Loss, Dividend Yield show > #DIV/O!, the DIV calendar doesn’t seem to be updating either. i filled everything in orange all transactions in transactions all buys and DIV. DIV PAYOUT CALC that too in green shows n/a, in reference data shows getting errors, in the orange field not sure what i put in price manual and manual DIV. in summary historical show nothing. in currency its all in gray and not being updated as well. i did everything right only in the orange fields only, still not updating says a delay of 20 minutes should of updated by now. any suggestions? i can give access to my spreadsheet if you like??

  28. Kevin says:

    Thanks so much for the wonderful Spreadsheet. I have combined the Options and Dividend spreadsheet in order to have everything in one place. Was also nice to build some charts to show the overlapping of income on a yearly basis from both.

    Question – I am having trouble with both CVS and O. CVS isn’t pulling in anything for market value. This might be a temp problem with CVS, but i am a little worried as a played around with moving a few extra sheets in that I have done something wrong. With O its doing almost the opposite by giving me a market value much higher than it should. Currently I have 115.52 shares which should have a market value of $5771, but the value is showing $8306. I am sure I am just over looking something late at night. Any help you have would be wonderful.

    Thanks again for the wonderful spreadsheets

    • Greg says:

      Make sure your reference data sheet tickers are in the same order as your portfolio sheet. Some actions like deleting a row from the portfolio sheet can throw off this 1-1 match.

    • scott says:

      Hey Kevin,

      Sorry for the late reply. Some big news coming up that is taking up my time (in a good way!) Anytime I run into issues like this I double check the formulas. Usually it is an issue where I added new rows and the formulas are multiplying data from one row with what is above or below it. I.e., using made up stuff, it might be =E7*D8 when it should be =E7*D7.

      I’d double check that. But, given my late reply you may have figured this out already!

      • Kevin says:

        Scott. Thanks for getting back to me. Yea the rows were pulling off the wrong formula. I removed the two rows and added the two values down below. Right now I am working on a stock watch list spreadsheet but having trouble coming up with Douglas for dividend and payout ratio. I have other parameters I want and have the sheet giving initial signal of buy, watch or avoid. Do you have a good way to pull these two data points into Google sheets

  29. Richard says:

    Having a problem on the “Portfolio” sheet. Row 4 Totals . . most of the cells show the same error: Did not find value ” in HLOOKUP evaluation. Also, out of curiosity, I don’t understand the formulas in those cells as they all refer to a column T (which is not present on the sheet) as well as the column on which they are located. Anyway, thanks in advance for any help with the error.

    • Richard says:

      I was able to fix the problem by setting the currency parameter correctly for each of my stocks. Column T has also completely vanished.

      However I just noticed that I no longer have my stocks sorted into a correct account . . actually there are no accounts listed on “Portfolio” sheet, not even a drop-down column for them. I do have my accounts listed properly on the “Lists” sheet. Haven’t yet found how to fix this, so hoping you may have a suggestion. Thanks much.

      • david says:

        Column T is a hidden column as are R and S. if you want to see them click the small arrow against the Q column and it will give you the option to then display R, S and T. You can then hide them again when you are done.. If currency is not specified then T will get an error.

        • Richard says:

          Wow . . thanks so much. I had completely missed that. Mystery cleared up. However, I am still left with the problem of sorting my stocks into the proper groups. Right now I have two accounts on the “Lists” sheet. I have not figured out how to get an “Account” column to display on the “Portfolio” sheet. On version 4 this was Column B. Maybe someone has an idea??

  30. Todd says:

    Great spreadsheet. I cant seem to get TSX:REI.UN to work on my portfolio sheet. All other stocks I hold on the TSX work fine. Any idea?

  31. Michał says:

    Hi Scott,

    Thanks for the amazing template!

    The default priviledges felt too broad so I started digging into Google Apps docs.
    It is possible to limit the privileges granted to the script to just the Dividend spreadsheet itself.

    * @OnlyCurrentDoc

    at the top of the script, the app does not need full Drive access but can modify the spreadsheets in which the functions are used. Refer to the documentation for the details: https://developers.google.com/apps-script/guides/services/authorization#permissions_and_types_of_scripts

    Keep up the good work!


  32. Greg says:

    My profolio page started showing #Div 0 errors and no Dividend return on my stocks..

    I tracked it down to the reference sheet being full of “error getting data” notifications.

    I created a test function to see what was happening and found that I had exceeded Google’s Daily URL Fetch quota. ( https://developers.google.com/apps-script/guides/services/quotas ).

    So if your sheet is having calculation problems or numbers that don’t make sense, check your Reference page. If it’s full of error receiving data message, you’ll have to wait until the counter resets (done daily but not indication of when) so you can refresh the data..

  33. Gary says:

    The XIRR column of the Portfolio Sheet started returning 0.00% a few weeks ago. I thought it might be something associated with Google so I didn’t give the issue any further consideration until recently.

    I am using Version 5.01 of your template (a wonderful piece of work by the way and much appreciated – I have come to rely on it heavily) but the problem seems to be prevalent with the latest version also. I have tried to pin point the problem but so far haven’t had any luck.

    I have broken down the XIRR formula for the cells into the basic parts and tried to determine which part wasn’t working. It appears the “query” function isn’t working because I get the “Query completed with an empty output” error for individual equities in the portfolio – there are entries in the locations being queried.

    If I do the same thing for the entire portfolio (Cell K4) the query returns dollars and dates correctly separately (the query and join functions work properly when retrieving dollars or dates individually but I get “Result was not automatically expanded, please insert more columns (861)” error – I have 877 transactions) when I try to use the “split” command. The XIRR function doesn’t work on this data either.

    Since I am not a Google Sheets guru (or guru of any kind for that matter), I am hoping you can provide me with some guidance regarding how I might fix this issue.

    Thanks in advance.


    • Matthew says:

      Selecting the entire “R” column in the “Transaction” sheet and formatting it as “plain text” solved this issue for me. I’m not sure if there is a more graceful solution.

      The issue:
      In my spreadsheet the “R” column in the transaction sheet is formatted as a currency (ie $1,000.00). The join command used in the XIRR calculation apparently uses comma as a delimiter. Since the “R” cell is formatted as a currency and has a comma, it throws the join command off. I don’t know why this suddenly stopped working a few weeks ago.

      • Gary says:

        Thanks Matthew. Your suggestion to format the “R” column in the Transaction sheet as plain text worked for the positions I have closed – zero shares – but for some reason the XIRR still returns “0.00%” for the rows in which I have positions. I’ll try the “|” suggestion to see if I get any different results.



      • scott says:

        Great suggestion, Mathew. I’ve updated the latest version 5.1.1 to address the XIRR issue.


  34. JB says:

    Hi Scott — I’ve been using your spreadsheet and love it, but also had the XIRR column switch to returning 0.00% for just two stocks — VOO and VTI. Similar to Gary, this happened around the beginning of March. I tried checking the XIRR formula and figured out that it’s returning an error for the iferror() part, but can’t figure out what the error is. I tried repasting the formulas in the Transactions tab and reentering the VOO and VTI transactions, without success. If you have any suggestions for troubleshooting, I’d appreciate it!

    Thanks and appreciate all the hard work you have put into this.

  35. Matthew says:

    Perhaps a more graceful solution is to replace the comma delimiters in the join and split commands with some other type of delimiter. I picked the bar delimiter (ie “|”, below the backspace key for me). You will only want to replace the commas in quotes (ie “,” and not just the bare , )

    • Gary says:

      Changing the “,” to a “|” worked great. I changed the formatting of column “R” of the Transaction sheet back to currency and using the “|” delimiter worked great with that formatting too. Thanks for checking this out – I would never have thought to change delimiters to make the XIRR calculation work. Your help is much appreciated.


    • scott says:

      You are amazing Matthew! I’ll change the delimiter right now and getting the XIRR back working again.


  36. JB says:

    Matthew — your solution worked for me, too! Thanks so much!

  37. david says:

    I think this statement is incorrect in DIVPAYOUTCALC tab:
    =if(isblank(A7),,query(Portfolio!$B$5:$O$70,”select O where C='”&A7&”‘”))

    I believe that it should be:
    =if(isblank(A7),,query(Portfolio!$B$5:$O$70,”select O where B='”&A7&”‘”))

  38. david says:

    Also, In the Portfolio tab , the Currency column E is in green but it appears that it is necessary to select the $ symbol manually else an error occurs in the hidden column T..
    david recently posted…February 2018 IncomeMy Profile

    • scott says:

      Another good point, david. I’ve updated the spreadsheet to the orange color so that it is more clear that you have to manually choose the currency.

  39. Eli says:

    AWESOME spreadsheet! Thanks so much for making this available!!

    For some reason, however, the numbers aren’t updating in the reference data and portfolio pages.. Any way to fix this to get accurate numbers?


  40. Anonymous says:

    There are actually loads of details like that to take into consideration. That may be a great level to bring up. I supply the thoughts above as basic inspiration however clearly there are questions like the one you bring up where crucial thing shall be working in trustworthy good faith. I don?t know if best practices have emerged round things like that, however I’m sure that your job is clearly recognized as a good game. Each boys and girls really feel the influence of just a momentpleasure, for the rest of their lives.

  41. SNT says:

    Instead of using split and join functions, I used curly braces to create an array for XIRR function and it worked. Unfortunately cannot post the code here as I get an error message when I post the code.

  42. SNT says:

    Used following formula for XIRR, worked for me…
    =if(isblank(C5),,iferror(xirr({query(Transactions!A$2:R,\”select R WHERE (B=\’Buy\’ OR B=\’Sell\’ OR B=\’Div\’) AND C=\”\”\”&B5&\”\”\” \”);R5},{query(Transactions!A$2:R,\”select A WHERE (B=\’Buy\’ OR B=\’Sell\’ OR B=\’Div\’) AND C=\”\”\”&B5&\”\”\” \”);TODAY()}),0))

    Thanks scott for excellent work!!!

  43. Greg says:

    Just figured out an easy way to track multiple accounts with this.

    First, have a master sheet with all your account info in it.
    In the master sheet add a column on the Right to the Transaction table and label it Account.
    Make sure all your transactions are labeled by account.

    Create a new empty version of this sheet for a separate account and do the following:

    Add your stocks in the portfolio tab.
    Add the account column to the transactions sheet.
    Create a Duplicate of the Transaction Sheet and relabel it “All Transactions”.
    In Cell A2 of the All Transactions Sheet add an ImportRange formula to pull in transactions from the Master sheet. Like the one below except use the URL for you master sheet. NOTE: You will have to allow access to do this (click on initial error / allow).

    =IMPORTRANGE(“https://docs.google.com/spreadsheets/[your master sheet”,”Transactions!A2:U500″)

    In the Transaction sheet, add a formula in A2 to pull the account specific transactions from the All Transactions sheet. It looks like this (replace with the account name used in the transactions).

    =query(‘All Transactions’!$A$2:$U$200,”select * where U=’my-account'”)

    To create another account sheet, make a copy of the first account sheet. Add the stocks in this account and change the query on the transaction page to use the correct account name.

    Now updating the master sheet transactions will trickle down to the account sheets.

  44. Bruno says:

    Getting errors on the Reference Data sheet, doesn’t seem to be pulling the data from iextrading
    Bruno recently posted…March & April IncomeMy Profile

  45. Jim Terryberry says:

    Thanks for looking into it.

  46. Alex Tveit says:

    Been using the spreadsheet for 1 1/2 years now, and it’s been great. Just had N/A issues show up all over the place. Seems to be having issues pulling stock info, which is creating issues down the line. Anyone else having issues?

  47. Cris says:

    Hi Scott

    This is an amazing spreadsheet.
    Anyway, I have an issue that after some point any new addition in Portfolio page is not recognize in Transaction page… Do you know the reason

  48. Mike says:

    Scott I LOVE your spreadsheet. However I have over 100 different stocks. Can it be modified so I can enter and track all of them?

    • scott says:

      Hi Mike, Yes, it can. You just have to add some more rows and propagate the cells downward to maintain the calculations.

      • Mike says:

        I’ve got to admit I’m a basic Google Sheets user. I tried to do it and it got all messed up. Even after bringing the calculations formulas down they didn’t work.

  49. Stephen says:

    Scott, love the spreadsheet. Through IEX, I see your importing/downloading 4 fields. Is it possible to download others to calc payout ratios, valuation ratios etc? Think that would be great to supplement the already great spreadsheet. Thanks

  50. Bill says:

    Hi Scott, great spreadsheet. Does IEX support preferred stock symbols? Thanks

  51. Bill says:

    Found answer to my question. i.e. CMO-E. Sorry.

  52. Matt says:

    Any updates on fixing the .TO extensions? Thanks.

  53. frederick says:

    hell can i say one question, if i want to add adr (FRFHF , tlsyy )in this excel , how can i do ,thx

  54. Lucio says:

    Hi, first thanks for this tool, it’s what I looking for, but I have a problem in DivCalendar and ReferenceData. It looks like the year is still going to start, the dates of next Div Pay Date are Jan, Feb of 2018 …. and nothing is filled
    PS: I’m form Brazil

  55. Marcus a Daig says:

    hi scott,
    this looks like a great idea, i follow your instructions but there must be a step im missing. once i allow access i do not see any orange or any cells?

  56. Josh says:

    Is there a way to pull iex “adjusted closing prices” into prior purchase prices to reflect honest returns on dividend reinvestment? The prices used for “Transacted Price/ Share” are manually inputed, and therefore are static. They should be adjusted by dividends paid going forward from that date though, to figure total returns, and to benchmark against the sp500 index.

    In other words, if I buy stock ABC for $100 per share in 2017, and in 2018 it pays out $10 in dividends which I reinvest, the adjusted price for that purchase should be reduced by $10 to reflect the payout (and compensate for the reinvestment which adds to cost basis).


  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.  […]

  2. December 17, 2017

    […] many of you know, Two Investing has published – and periodically updates – a Google spreadsheet for portfolio […]

  3. July 23, 2018

    […] Dividend Stock Portfolio Spreadsheet on Google Sheets … […]

  4. July 25, 2018

    […] Dividend Stock Portfolio Spreadsheet on Google Sheets … […]

  5. August 10, 2018

    […] Being a fan of the mantra “work smarter, not harder”, I usually try not to reinvent the wheel when it comes to my spreadsheets. Therefore I leveraged the great work from the team over at Two Investing when they shared their dividend portfolio spreadsheet. […]

Leave a Reply

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

CommentLuv badge

This site uses Akismet to reduce spam. Learn how your comment data is processed.