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

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

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

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

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

      Scott

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

      Bobby,

      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.

          Scott

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

        Scott

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

        Scott

  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.
    Thanks!
    Rick

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

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

            Rick

  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?

    Thanks,
    Dave

  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?

    thanks

    kevin

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

      kevin

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

  13. Yoav says:

    Hi,
    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!
    Yoav

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

  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.

      Scott

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

    Scott,

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

    Thanks,
    Venkata

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

      Scott

  21. Jean Peuplu says:

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

  22. Cathy Kelley says:

    Scott:

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

  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 have populated the currency for each of the stocks. The same error exists. can you assist if i have to update anything else ?

  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

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

Leave a Reply

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

CommentLuv badge