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. 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: Sign up for IEX Cloud

This spreadsheet using the excellent IEX Cloud API to look up stock data. In order to access this data you will need to sign up for a free account on https://iexcloud.io/. Once your email is verified, log in and click “API Tokens” on the lefthand column. Copy the token key (which will start with “pk_”) and paste it into cell M6 of the ReferenceData sheet.

Step 2: Learn the basics

Orange-ish cells – These are the cells that you will be editing.

Light green cells – These contain formulas and are automatically calculated by the spreadsheet. If you enter data into these cells you are overwriting formulas. Edit these cells only at your own risk.

Red and dark green cells – These are a subset of the light green cells and reflect losses and gains.

Drop down selections – These allow you to pick from a predefined list. Picking a sector type is an example. You can change the validation for these drop down selections by choose Data > Data Validation… when selecting the desired cell or column of cells.

Step 3: Begin entering your stocks

Start first on the “Portfolio” sheet. This is the main page where you will enter the stocks you own. The orange-ish colored cells in rows 5 and 6 are my data. Delete that information and type in your stocks row by row. The company name can be whatever you want, but the symbols for US traded companies should match how they show up on IEX. You can adjust your symbols by searching for them here: IEX Stocks. This will allow you to search for stocks on the US exchanges. Remember not to edit the light green cells on the right side of the page. This will be populated once you enter data on the Transactions sheet.

For detailed information on each column please visit the Appendix.

Step 4: Entering transaction data

Transaction data is entered in the order that it happens on the Transactions sheet.  This page is where you’ll enter all your stock buys, sells, dividends received, dividends reinvested, and stock splits. If you have a lot of historical data, you have a couple of choices. You can either 1) take the time to enter each and every purchase, sale, and received dividend with the dates they happened, or 2) figure out your current cost basis and make a single purchase for that amount in the year you originally purchased the stock.

If I had years and years of data to enter, I’d likely take the aggregate approach myself. I then would calculate the amount of dividends I received each year for each stock, if this information is available, and enter those as aggregate individual yearly transactions. This way I’d at least have the total dividend information for each stock per year.

For information on how to add dividends that you receive and how dividend reinvestment works for this spreadsheet, please visit the FAQ (still underdevelopment).

Step 5: Visit the ReferenceData sheet

This page lists all your stocks in the order that they appear on the Portfolio sheet. Using the script that I discussed above, the last price, dividend per share, ex-dividend date, and the dividend pay date are all retrieved from IEX. These functions will automatically be refreshed when you open the spreadsheet or change the ticker symbols on the Portfolio sheet.

You’ll see that most of this sheet is the light green do-not-touch color. If you are entering a stock, ETF, mutual fund, etc you have the option of manually entering a price and dividend amount per share (per year). Anything typed into those fields will overwrite the automatically retrieved values.

In general, you should not have to edit this sheet much, especially if just purchasing equities traded on the US market.

Step 6: Update the DivPayoutCalc sheet

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.

If Needed: How To Authorize the Google Script

This spreadsheet uses the Google scripting language to refresh the latest price and dividend information from Yahoo Finance. I’ve found that using the standard =importhtml and/or =importxml codes are limited in a few ways. There’s a finite number that can be used per spreadsheet and they are frustratingly slow. Don’t you hate getting the “Loading…” notification in the cells trying to look up your stock price or dividend amount? The script that is attached to this spreadsheet should fix those issues.

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


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

389 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

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

    • scott says:

      Could you send a screenshot to scott (at) twoinvesting. com? I’ll see if I can figure it out.

      Thanks.

      • ashmonke says:

        The problem is related to an apostrophe being in the company name. The formula in the Annual Dividend column needs to be updated to not use single quotes for the where clause.

        For example:
        Assuming the Kohl’s stock is in column A4 the formula should be:
        =if(isblank(A4),,query(Portfolio!$B$5:$O$67,”select O where B=”&””””&A4&””””))

        Instead of the original formula:
        if(isblank(A4),,query(Portfolio!$B$5:$O$67,”select O where B='”&A4&”‘”))

    • kevin says:

      i am having this same issue with MCD. Were you able to find a solution?

      thanks

      kevin

  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.

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

    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 ?

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

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

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

        -Steve

  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!

    Cheers,
    Michał

  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.

    Gary

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

        Thanks

        Gary

      • scott says:

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

        Scott

  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:

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

      Gary

    • scott says:

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

      Scott

  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?

    Thanks!!

  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?

    • Borja says:

      Same here for the last 2 days.

      • Alex Tveit says:

        Seems potentially to have something to do with the .TO extensions. Are you using it for Canadian stocks only like I am, or do you have a US portfolio as well?

        • scott says:

          I’m coming back from vacation right now. I’ll take a look later today. Seems to be an issue with the services the spreadsheet uses to look up stock prices.

          • Alex Tveit says:

            Seemed like I almost got it to work using TSE:”Stock” instead of “Stock”.TO, but still seems very wonky… 🙁

        • Matt says:

          I’m having issues with TSX stocks as well.

  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?
    Thanks
    Mike

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

    Thanks

  57. Franklin says:

    What’s this spreadsheet licensed under? I’d love to share some changes I’ve made. Would you be willing to license this under the MIT license?

  58. Chris says:

    I’ve just downloaded the sheet and am inputting all my DRIP holdings. Thank you for all your work on this project. It is a valuable tool for small dollar investors. I’ve been adding categories to the list tab and found that at line 20 nothing is an option in the pull down on the sector column on the portfolio tab. Is there a limit on the list?

  59. John says:

    Scott,

    I have been using this spreadsheet since 2016 and love it. Is there a simple way to take it into 2019 in regards to the Summary Page’s monthly tracker/chart? Thanks!

  60. haleyr14493 says:

    Hi Scott,

    The spreadsheet is awesome, but I would like to add an unrealized gain and total gain summary just like the realized gain summary on the summary/historical tab, in columns d and e, and I’m clueless about the formulas. Any help would be great!

  61. Aleks says:

    Truly an amazing piece of work, many thanks!!
    this is twoinvesting.com so I have 2 questions 🙂
    1) When I add cash to this portfolio do I have to enter this as an separate transaction or just adjust the amount in F3 on the portfolio page?
    2) anyone having issues with the dividend dates? The reference data sheet shows no Ex-Div dates beyond 04-02-2018. This in turn breaks the DivCalendar and DivPayoutCalc (partially). It looks like an issue with IEX but was wondering how people are working around this without breaking the sheet?
    screenshots posted here: https://postimg.cc/gallery/1pwdmkv9e/

    Cheers and a happy 2019!

    Aleks

  62. JLH says:

    I agree – I’m seeing problems with the dividend dates, no updates past 1st quarter 2018

  63. kasper kristensen says:

    the XIRR formula still seems to have issues – just downloaded the newest sheet.

    and the % are allover the place

  64. Jake Holeczy says:

    this sheet is awesome! just 2 things i’m noticing…i’m also having problems with the dividend dates, there’s no updates past mid 2018…and is there any way to CAGR or dividend growth % over 3 yr, 5 yr, or 10yr? thanks, great sheet!

  65. Rick says:

    Hi Scott
    I am having trouble recording a 2 for 1 stock split in the transaction page. Can you provide the detail of how to record this? Trans shares, trans price and fees are all left blank, but what goes into the Stock split column that will provide the correct number of shares that I now have?

    Thanks!

  66. Donald George says:

    Does the sheet quotes update during the day with the goggle sheets update function or do I have to force it manually?

  67. Marty says:

    Just wondering how you are going to handle the switch to the new IEX cloud for your spreadsheet. The IEX API is being sunsetted June 2019 (https://iextrading.com/developer/docs/), so URLs like https://api.iextrading.com/1.0/stock will no longer work after that. I have an Android app that has to be modified because of this. The kicker is that a free key will be required for the new IEX cloud APIs and if I use my personal key in the Android app, my users would only be allowed 500,000 (json) messages a day (total for all users) before needing to upgrade to a paid plan. I’m considering moving (back) to alpha vantage because of this.
    Marty recently posted…Sort your stocks by price, gain/loss, volume and more in new version of Stock Search 3.3!My Profile

  68. Chetan Sondagar says:

    Hi Scott

    My Yahoo api fetching dividend amount and yield stopped working after many years of use. Whilst searching for resolution I came across your spreadsheet with IEX and Alpha Vantage solution. You are a God-send! Thank you!

    I have question about dividend per share and dividend yield data for Canadian stocks. How do I get these? For example, for TSX:TD Alpha Vantage is not returning dividend per share and dividend yield. How do I get these for Canadian Stocks?

    Thanks!

  69. Rick C says:

    Hi Scott- pls help with recording a 1 for 15 reverse stock split? Doesn’t seem to be right when I enter- I am leaving the transaction cells blank but not having correct results – how can i record this transaction?
    Thanks!

    Rick

    • scott says:

      Hi Rick, make sure to enter the date, choose type “Split”, pick the correct stock, and then in the Stock Split column, enter 1/15. If you were doing 15 to 1 split, then you would enter 15. Depending on how many shares you have, you might have to sell some shares to match when your brokerage account shows since they will typically give you only full shares and then cash for the partial shares.

      Scott

  70. Rick C says:

    Hi Scott- thanks for the prompt reply. I am still having trouble with the stock split on the transaction sheet. The issues is when I enter the split, in this case 1 for 15 or 1/15 in the stock split column, in shows up (or is displayed) in top row of the spreadsheet as a date format, i.e. 1/15/2019? I have tried to format that cell as a number or “automatic” however it remains a date format causing an incorrect amount of shares. The original 500 share now become 2/4 in the cumulative shares column and become 21740000 in the portfolio. So I believe the issue is how do I format the cell to show the correct amount of shares after the 1/15 stock split which is 33.3?
    Im sure I am missing it and may be right in front of me!

    Thanks,

    Rick

  71. Rick C says:

    Scott- FYI, just seeing now that if I put the number .0666 ( rather than 1/15, in the stock split column, the result is correct in transaction and portfolio page.!

    Again, great work on this spreadsheet!

    Rick

    • scott says:

      Thanks, Rick! Yeah, I guess it does have to be a numerical number. Was thinking 1/15 might work better since it is otherwise a non-ending series of 6’s at the end.

  72. Christin Ocasio says:

    Hi Scott. I’m really enjoying your spreadsheet. I don’t have much to invest, but I try to live below my means and squirrel away what I can. When a statement arrives, I am excited to enter my reinvested dividends and watch my little nest egg grow. It’s empowering. I have been telling some younger folks about you and encouraging them to invest rather than dispose of their disposable income on daily fast food and convenience store purchases when they could just eat leftovers. I’ve run into a little snag that I hope you can tell me how to I can fix it. The stocks I have purchased are very diverse, and I’ve run into a limit on accounting for them. On the Summary tab, the sectors are set to 11 sectors on the List tab . I figured out how to show the additional sectors by dragging the sector and value fields down several places, but the pie chart does not include anything but the first 11, and the Sector pull down menus on the Summary tab do not show the new sectors. Another odd thing is that cash still shows on the Summary tab when I removed it from the List tab and have no holdings associated to cash.

  73. CafeHomestay says:

    i have a homestay in Viet nam, i sold it and invest money on Crypto. THank for your this spreadsheet. I read more to buy it!

  74. Greg says:

    FYI – The IEX folks have moved support for dividend information into their new cloud version effective June 1st. See: https://iextrading.com/developer/

    It seems like this information is still freely available via the new iexcloud API but you are required to sign up for an API key.

    • Chris says:

      Greg, what was the adjustment you made in the cell for both the Ex Div Date, and Div Pay Date?

      Can you provide examples?

      • Greg says:

        Had some time to play around with the new API. Seems the data is a bit spotty, e.g. some stocks I tried had month old dates, some were up to date. Anyway, you need to do the following:

        1) Go to the https://iexcloud.io/ site and register for the free level
        2) Verify e-mail, login, and get the public token key
        3) Go to the Reference Data Sheet in your portfolio google sheet
        4) Change the URL in the formulas in the top Exdate (IEX) and Div Payment(IEX) cells to use something like:

        “https://cloud.iexapis.com/stable/stock/”&A5&”/dividends/1y?token=PK_YOUR_PUBLIC_API_ KEY”

        5) Copy these two new formula cells into all the cells in these columns.

        • Aleks says:

          Hi Greg,
          Do we still need to use importjson function?
          I’ve tried placing this in a reference data cell:
          =if(isblank(A10),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&A10&”/dividends/1y?token=pk_123456789″,”0/exDate”))
          Which still gives the “Error getting data”
          I’ve tried placing this test code in a cell:
          =IMPORTDATA(“https://cloud.iexapis.com/stable/stock/”&A10&”/quote/latestPrice?token=pk_123456789”)
          Which gives the latest price so key en cloudio connectivity seems to be in order.

          Can you please provide an sample of a cell that works in your sheet?

          Cheers!

          • Jim says:

            I’ve tried that and a few variations as well that I found googling around – I still get the same Error. I’ve clicked on the URL https://cloud.iexapis.com/stable/stock/”&A10&”/quote/latestPrice?token=pk_123456789
            and it retrieves all the JSON data so I know it can at least see the data – it’s just not showing it in the spreadsheet.

          • Chris says:

            I also wasn’t having any luck with the token and after wasting a lot of time trying everything under the sun to get it to work I just gave up and wrote this instead.

            Ex Div Date (change “A5” out for cell where stock symbol is located)
            =IF(ISNUMBER(INDEX(IMPORTHTML(“http://www.nasdaq.com/symbol/”&$A5&”/dividend-history”,”table”,3),2,1)),INDEX(IMPORTHTML(“http://www.nasdaq.com/symbol/”&$A5&”/dividend-history”,”table”,3),2,1),0)

            Div Pay Date (change “A5” out for cell where stock symbol is located)
            =IFERROR(INDEX(IMPORTHTML(“http://www.nasdaq.com/symbol/”&$A5&”/dividend-history”,”table”,3),2,6),”error”)

            If there is nothing to show currently you will see “error”, but once a date gets announced it will appear.

  75. Greg says:

    That is what the formula should look like. You will get an error message if IEX does not have dividend information for the requested stock. This happens with about 10 or so of my stocks (seems to be mostly CEFs and MLPs).

    I would also suggest testing the URL / Token from a separate browser window to see if there any errors in it. To do this, use the https://….789 URL part of the formula and replace the “&A10&” with the ticker symbol you want to look up. The result is in json format but is still readable. If you see [], then the stock has no dividend info. If you see an error message, then something is wrong with the URL format or token..

    FWIW – I am finding this data as useful (less?) as the old IEX API was. I gave up on them and just manually lookup dividend info via NASDAQ and enter them manually in a new field I created on the DivPayout sheet. I also have a field with notes on when div are announced to help knowing when to look. E.g. 1mon 3w or 7 days or quarterly. Just a rough take on delta between announce and pay. Let me know not to waste time looking up PFF until the first of the month since they announce then and the like. Part of making this fairly easy is to filter the stocks to only show those paying during the upcoming month and work on them.

    • Aleks says:

      Hi Greg,
      Yeah you’re right MLP,CEFS and Preferred shares are mostly not available. it would seem *Sigh hopefully IEX will get their act together or we will have to do it manually which of course can be done but it gets tedious and is error-prone.

    • Jim says:

      I’m getting “unknown function: importJSON”

      I tried the original directions and made sure it was authorized (still) but….no good.

      • Jim says:

        Aha! It doesn’t like ”&A6&” if I replace it with AAPL then it works.

        I’ll continue playing and see if I can get the right syntax

        [two minutes later]…..it doesn’t like curly quotes. Changed to straight quotes and boom…

        ”&$A5&”

  76. Jake Holeczy says:

    i have novice experience with coding or API’s but i can not get anything to work. i have tried iterations of all the previous handful of comments. Downloaded the new API keys,verified, put all the different URL’s into the ex div date and/or div pay date column(s), tried to change curly quotes into straight (if i did that right, never really thought about typing it). nothing. this spreadsheet worked great for a few months besides not getting the exact ex dates and pay dates of dividends. but i didn’t mind doing that part cuz i had to input them into the transactions anyway. in this current form, it is completely unusable. what am i doing wrong?

  77. Brian Jones says:

    I am also having issues with this spreadsheet. I have no experience coding and minimal experience with spreadsheets. I am posting this to get updates to this thread as comments come in. I am hoping someone will figure out the issue with the API keys and post here. Thank you!

    • Jim says:

      Here’s my entry for E5

      =if(isblank(A5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&$A5&”/quote?token=pk_######”,”latestPrice”))

      Just enter your token correctly. Let me know if that works and I’ll give you the rest of my fields.

      • Brian says:

        Jim, I copied and pasted that formula, along with my API key as per above procedure and initially had no luck. I then went back in and changed the “curly” quotes to the straight version and that seemed to do the trick. All of column E is now populated.

        If you have a moment to show me how to correctly enter the formula for columns F-I I would sure appreciate it.

        Thank you for the help!

      • Brian says:

        Another quick update. After going through the top line cells of columns H and I (Ex Div and Div Pay dates), I found another set of curly quotes and fixed the issues. All set now.

        Any idea how to update column the “Dividend per Share” formula in column F?

  78. Cath says:

    Wow, I finally got it to work. I was only changing the quotes around the symbol and once I changed every quote mark it worked. The only thing I don’t have is the code for Dividend per share. Can someone help with that?

  79. Jake says:

    Jim that last price (column E) worked. thanks. i can’t get column F, H, or I to work. Brian.what do your formulas for columns H and I look like? what i’m doing now is copying links to each individual stock IEX page with pertinent info and it is not ideal. lol

    • Jim says:

      I can’t get F right yet. I’ll take a few stabs on it this morning once my coffee is finished.

      Here’s H:

      =if(isblank(A5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&$A5&”/dividends/1y?token=pk_######,”0/exDate”))

      Here’s I:

      =if(isblank(B5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&$A5&”/dividends/1y?token=pk_#####”,”0/paymentDate”))

    • Brian says:

      Jake, here are the formulas I have that seem to work, just be sure to replace all the ###### symbols with your own personal API key, and ensure EVERY quotation mark with the straight ones if they copy and paste as the curly ones.

      H (Ex Div Date)
      =if(isblank(A5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&A5&”/dividends/1y?token=pk_##########”,”0/exDate”))

      I (Div Pay Date)
      =if(isblank(A5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&A5&”/dividends/1y?token=pk_##########”,”0/exDate”))

  80. Bodkin says:

    =index(importhtml(“http://www.nasdaq.com/symbol/”&A5&”/dividend-history”,”table”,),2,3)*4

    • Nicholas says:

      Thanks Bodkin. This worked for me. However, keep in mind to change the last number accordingly to the frequency the dividend is paid for that particular stock.
      4 = quarterly
      12 = monthly
      so on…

  81. Jim says:

    Ok – I think I got F but I need someone who knows this better than me to confirm. IEX no longer has a DividendRate function they have ttmDividendRate and that’s the Trailing Twelve Month Dividend Rate. For Apple it gives me $2.92.

    =if(isblank(A5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&$A5&”/stats?token=pk_####”,”ttmDividendRate”))

  82. Brian says:

    Looks like the formula provided by Bodkin above pulls the current dividend rate as opposed to the TTM dividend, which will provide more accurate results. Hopefully the cloud.iexapis link will at some point have a current dividend as opposed to TTM.

    Thanks for all the help everyone.

    • Jim says:

      I guess I just need a little explanation for which formula we should use. Bodkin’s formula gives us a dividend for Apple of $3.08 which is their next payable dividend (.77) multiplied by 4. My formula gives us the last 4 dividends added together…$2.92. Both are correct numbers but for our purposes which is…corrector..

      • Brian says:

        I suppose it depends on what info you prefer. The TTM dividend rate gives you what it historically has been paying as annual dividend. My preference is for the current (and presumably future over the next 12 months) dividend rate to give me a better view of what my investments will be generating over the next several months.

        In the case of AAPL, the dividend rate of $3.08 matches what you will find published for dividend online (verified using Seeking Alpha and Yahoo Finanace)

        • Jim says:

          Thank you… I like your explanation and will adjust my spreadsheet with the futurecast formula that Bodkin gave us.

  83. Brian says:

    Any ideas how to get the IEX data for internationally held stocks with these new API keys? Example would be NSRGY (Nestle SA). All the IEX cells in my reference data sheet are coming up with error #value.

    • scott says:

      Thanks for running with this! I’ve been away/busy moving into a new house but should hopefully have time soon to play around with this and implement all the excellent additions/changes you guys have been working on into the public version.

      Scott

      • David Pasco says:

        Really glad to hear this. I can’t begin to tell you how useful this sheet has been to me, but even calling myself a novice at Google sheets would be an exaggeration of my skills, so I’m really nervous to try to edit it too much myself. Thank you for all your hard work, and thank you to all who have built upon it.

  84. Jake says:

    got everything up and running! thanks all for the help, i greatly appreciate it! happy fathers day to all out there!

  85. Nicholas says:

    ReferenceData “loading” or “Error getting data”. Anyone else having this issue?

    • Anshul says:

      I am getting “Error loading data” too. I have version 5.1.1…downloaded in just over a month ago. Worked beautifully for a few weeks. But now showing the same error as you Nicholas.

    • John says:

      I am having the same issues.

      • Jim says:

        Working fine here once I obtained the token from IEX and updated the formulas.

        • John says:

          Jim, could you please briefly describe how you did that? I see a bunch of entries and coding above, but if you have time to consolidate it would be much appreciated. I am getting error messages on every portion of the spreadsheet. Cheers!

          • scott says:

            Hey guys! Sorry I have been away. Got married and just bought a house so I haven’t had any time to work on this.

            I will read through the comments soon and make all the great changes that have been discussed. I understand that the API stuff has changed which will require updating the public spreadsheet.

            Scott

        • Anshul says:

          Jim/Bodkin/Brian….thanks for figuring this out. It works for me now. Well…for the most part. While it works for the individual stocks in my account, it is the ETFs where this approach is not able to pull up information on Dividend Per Share. Could this be because we are trying to use the TTM yield?

  86. David says:

    Scott, when you update the origin file, will all of ours update as well, or do we need to download the new original and then reenter our info?

  87. Pecunia says:

    iexcloud is gracious enough to let people chime in what we need from them:
    https://iexcloud.io/console/roadmap
    Would be better if everyone pitched in, I sent them the link to this blog article for reference.
    I sent them this as a new idea:

    Would really be nice if IEXCloud would be able to provide all data necessary for the dividend investor.
    (that means ex-div data, payment data, Dividend Yield, Current dividend (as opposed to TTM that is now offered)
    We need good data on dividend stock, BDC’s, CEFs (Closed End Funds), MLPs, REITS etc

    • Nicholas says:

      Well, it looks like they do have the latest dividend amount…

      https://iexcloud.io/docs/api/#dividends

      “symbol”: “AAPL”,
      “exDate”: “2017-08-10”,
      “paymentDate”: “2017-08-17”,
      “recordDate”: “2017-08-14”,
      “declaredDate”: “2017-08-01”,
      “amount”: 0.63,
      “flag”: “Dividend income”,
      “currency”: “USD”,
      “description”: “Apple declares dividend of .63”,
      “frequency”: “quarterly”

      • Greg says:

        They have a lot of dividend fields defined…however a lot of it is spotty (e.g. no/few CEFs, MLPs, and other securities dividend investors hold). And some of it is misleadingly partial. E.g., I have tried to manually calculate missing TTMDividendYield values from the 1yr dividend information and found monthly dividend payers listed with only 8 payments.

  88. Fernando says:

    Hi,

    the api.iextrading.com is not working anymore. Could you update the Sheet using another api?. Thanks!.

  89. Brent @ AAI says:

    having the same problem Fernando. I have my own sheet but have been using these same API’s.

  90. Siva says:

    I am using =if(isblank(A5),,IMPORTDATA(“https://cloud.iexapis.com/stable/stock/”&A5&”/price?token=”&$Q$5)) to get the last traded price. [NOTE: Q5 has my key]

    For dividend I wrote a custom google script function

    function IEXGetAnnualDividend(url) {
    try {
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);
    var annualDivi = json[0].amount + json[1].amount + json[2].amount + json[3].amount

    return annualDivi;
    }
    catch (err) {
    return “Error getting data”;
    }
    }

    then I do =if(isblank(A5),,IEXGetAnnualDividend(“https://cloud.iexapis.com/stable/stock/”&A5&”/dividends/1y?token=”&$Q$5))

    I just started messing with google scripts today. So not much of an expert. This works for now. Will wait for Scott or somebody else better at this stuff to give a better solution

  91. Brent @ AAI says:

    Thanks Siva. The new calls work for price. I’m going to play around with this and see if I can get all the calls working for my entire spreadsheet. For anyone copy and pasting code, make sure to re-type the quotation marks, “. They are translated different when pasting.

  92. Brent @ AAI says:

    It’s working fine with new calls. I just pulled in the 52 Week high information using this:
    =IMPORTDATA(“https://cloud.iexapis.com/stable/stock/”&A2&”/stats/week52high?token=”&$Q$5)

    where Q5 is your token.

    • Taylor Burnett says:

      Hi Guys,

      Im still pretty new to this writing scripts and connecting systems… So sorry for any questions with obvious answers.

      I was following the above because I am using the dividend tracker as well. Now I am trying to update the APIs myself and am honestly lost.

      If anyone can point me in a direction I can update them myself or explain step by step, I would be extremely grateful.

  93. mickey says:

    you can try following the article on my blog wealthcapitalist

    • Taylor Burnett says:

      Thanks! It seems with a bit pf patience, trial and error, and googling I managed to get it working.

      The only thing I can’t seem to fix now is the Dividend per share field in the ReferenceData Sheet.

      Also, for whatever reason, my holding in Alerian MLP (AMLP) is not updating.

  94. Taylor Burnett says:

    Thanks! It seems with a bit pf patience, trial and error, and googling I managed to get it working.

    The only thing I can’t seem to fix now is the Dividend per share field in the ReferenceData Sheet.

    Also, for whatever reason, my holding in Alerian MLP (AMLP) is not updating.

  95. Adam says:

    Scott, you think you are going to update this sheet with the new API changes?

    • scott says:

      Yep, I will. Hopefully soon. My wife and I just bought a house so are in the process of moving there right now. Will update once we get settled in.

      • Jim says:

        Thank you. It will be good to transfer and start fresh-ish. I’ve got it hobbling along for now but somehow somewhere I messed up and every time I enter a new stock I have to edit the data validation to include the new fields. I used to be able to enter new lines and things increased and calculated automagically but somehow I broke that…

        • scott says:

          Yeah, sorry Jim. I would love to be able to spend more time on my website, but life changes have made it difficult. I’ll send a reply here once I get to it.

          Scott

  96. Jim says:

    I am retired and have been using this spreadsheet for a little over 2 years. Somewhat disturbed by the fact that in no longer works. I am not code or software able, so all the multiple possible fixes I see in the email string are not of interest as I don’t have any idea of how to put them to work.

    If this is to be a real and legitimate analysis tool the owner needs to take ownership, fix it and not depend on multiple individual coming up with their own “cures”.

    • scott says:

      Jim,

      I am not retired and am the owner of this tool. It has been released for free. I would understand your frustration if you had paid for this….you would expect it to work. However, I’ve worked on this spreadsheet in various iterations since 2010 and put countless hours of work into it. I’m a full time radiologist in a busy practice, recently married, and recently (yesterday) moved into a new house. I learned how to use Google Sheets mostly by trial and error over the years in my free time. I am not a professional coder.

      I am very appreciative of all the people contributing their time and energy into improving this tool. I hope to update the spreadsheet soon with the changes to make it work again. However, as I am not retired I cannot give a definitive date when this will be completed. There are more important things in my life at the moment.

      In the meantime, feel free to research Google Sheets coding and learn how to update the sheet yourself.

      Scott

  97. scott says:

    I just released version 5.2, which includes support for the new IEX Cloud API. In order to limit calls to the API, I removed the need to use IMPORTJSON (although that script is still available). All the calls are made via standard IMPORTDATA calls.

    To use, please sign up for a free API at https://iexcloud.io/. You then paste your token into M6 of the ReferenceData page.

    Thanks to everyone above who helped provide updated formulas. I appreciate all your hard work!

    Scott

    Edit: Does anyone know if the maximum of 50 importData functions still exists? If that is the case, I might have to return to using importJSON. If anyone runs into the 50 importData function limit, please let me know and I’ll work on a fix. Thanks!

  98. scott says:

    Here are the formulas I used:
    Dividend Data: =importdata(“https://cloud.iexapis.com/stable/data-points/ticker_symbol/LAST-DIVIDEND-AMOUNT?token=”pk_123456789”))

    Latest Price: =importdata(“https://cloud.iexapis.com/stable/data-points/ticker_symbol/QUOTE-LATESTPRICE?token=”pk_123456789”))

    Ex Dividend Date: =importdata(“https://cloud.iexapis.com/stable/data-points/ticker_symbol/NEXTDIVIDENDDATE?token=”pk_123456789”))

    Dividend Pay Date: =importdata(“https://cloud.iexapis.com/stable/data-points/ticker_symbol/LAST-DIVIDEND-PAYMENT-DATE?token=”pk_123456789”))

    Just replace “ticker_symbol” and “pk_123456789” with the actual values for the formulas to work.

    Scott

    • Jeff says:

      Hi Scott,

      I just downloaded this TODAY (7-31-2019) with your latest edits. I have used your spreadsheet for last few years. I can’t get “Last Price” column to on “Reference Data” sheet to display anything? I put API key “pk_xxxxxxxx” specifically in M6 as directions stated.

      Column C “Last Price” has nothing yet I can see formula when selecting on any cell in that column. Column E “Last Price (IEX)” says….Error
      Could not fetch url: https://cloud.iexapis.com/stable/data-points/ABBV/QUOTE-LATESTPRICE?token=pk_xxxxxxxxxxxxxxxx (<– with my specific API key where x's are, and above for stock symbol ABBV it is pulling correctly "&A5&" from column A).

      If column F, H, and I (all that reference IEX data) didn't work then I would know it is ME who didn't get API key to work but they work just fine? Ahhh…any ideas? I have 51 stocks, is there a limit to API calls?

      On another note the only stock symbol to give me trouble (different from above issue) on this new sheet and all previous versions for last 2 years is Lowe's Companies (LOW). Only on "Dividend Payout Calculator" tab Column C and E does it say "#VALUE" and when highlight that cell it 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”

      thanks for assistance, this google sheet is the best I have ever seen!

      • Jeff says:

        I played around with formulas on reference data tab. Here is what I used for Column E “Last Price (IEX)” and it worked:

        =if(isblank(A5),,importdata(“https://cloud.iexapis.com/stable/stock/”&A5&”/quote/latestPrice?token=”&$M$6&””))

        I have never figured out why Lowe’s Companies (LOW) never worked on DivPayoutCalc Tab. Column C and E.

        • scott says:

          Thanks Jeff! I played around too and got the last price working with your same formula. There might be a limit to the number of importdata calls. As far as IEX API calls, there is a limit which you can see when logging into your free account. It will take awhile to reach it and it resets each month.

          The data lookup is going pretty slow, which I think is just a limitation in Google Sheets. I’ll have to see if using importJSON could be any more efficient.

          If LOW is not loading properly, you should still be able to manually enter the latest price and dividend. That should make column C and E of the DivPayoutCalc tab work work properly.

          Scott

    • Scott says:

      Hi Scott,

      Downloaded latest sheet and on the Reference Data tab, not getting latest prices to load from IEX – return in #N/A. Other data from IEX is populating (Dividend amount, Dividend Dates….). Tried varied configurations of the formula (data-points versus stock) with no luck. When reviewing the IEX options, is the price/quote data no longer free and now requires a subscription?

      • scott says:

        Hi Scott,

        Sorry about that. It appeared to be working when I released it. I updated the formulas in version 5.2.2 to use a slightly different URL and it is working now.

        Scott

  99. Jill says:

    Scott — thanks for updating! Looking forward to testing out the new formulas. Much appreciated.

  100. David Pasco says:

    I just downloaded the new sheet and followed the directions for setting it up. For some reason, it seems to think the quarterly dividend is the annual dividend, and it calculating the yield accordingly. Is anyone else having this problem, or aware of any way to fix it?

    • scott says:

      Hey David, what field is having the issue? I’ll check into it tonight. Also, what ticker symbol are you having the issues with?

      Scott

      • David Pasco says:

        Hey Scott, thanks so much for getting back to me so quickly. I’m having issues with all my stocks. The ticker symbols are HD, F, IRM, and SKT. Please forgive my ignorance on Google Sheets, but I’m not sure how to tell which field is giving me the problem. If you check the Portfolio tab, column O populates annual dividends that represent the quarterly payout. I double checked this against the dividends received, and the “annual” number listed on the sheet is indeed the exact amount I received for each to the past two quarterly payouts. If you look at the Reference Data tab column D is also incorrect across the board. I hope my answer makes sense. Please lemme know if I can elaborate on anything, or if you have and other questions. Thanks again for all your support.

        • scott says:

          Hi David,

          I found the error. You are correct that the way it is doing it now is just by multiplying the last dividend payment by the current shares held. For a stock that pays quarterly, you then have to multiple that number by 4 to get the annual amount.

          The way it works now is that as long as you have the correct payout number on the DivPayoutCalc sheet, the annual dividend on the Portfolio sheet will be correct. The payout number is determined by typing in the months that the stock pays its dividend.

          I’ll be updating the blog post soon explaining this with some graphics if this doesn’t make sense.

          Scott

          • David Pasco says:

            Thank you for looking into this, Scott. I think I may be misunderstanding something, or something may otherwise be amiss. I filled out my DivPayoutCalc sheet. The annual dividend in the green box on that sheet (which as it is green I have not touched) is showing the quarterly dividend. It then divided that by four to get what it uses for a quarterly dividend, if that makes any sense.

          • scott says:

            Did you grab the latest version? Column C in DivPayoutCalc should be showing the annual dividend while Column E (dividend payout) would be the quarterly payout amount (provided there are 4 payout periods in column D). In your spreadsheet, what are the values of C2, D2, and E2 on the DivPayoutCalc sheet?

            Scott

  101. David says:

    I did grab the newest version (5.3, correct). Both columns are listed, but the quarterly divedend populates as the annual dividend. Since this is a green box, I’m not sure where the info comes from that it is using, so I don’t know how to fix it. I can email you a screenshot, if you think it might be helpful.

    • David says:

      Sorry, I never even answered your other question. I’m assuming this is what you’re asking for, but on my sheet I have the following values:

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

      D2 :=if(isblank(A2),,counta(SPLIT(B2,”,”)))

      E2: =if(isblank(A2),,C2/D2)

    • scott says:

      Could you email a screenshot of the appropriate section of your divpayoutcalc and reference data sheets? scott @ twoinvesting. com

  102. scott says:

    Just released version 5.2.2 which fixes the latest price lookup. An issue that I’m running into at the moment is that the retrieval of all the data is taking quite awhile if you have a lot of stocks. It does seem to load slowly but you do have to wait.

    I’ll try to see if there is any way I can improve the efficiency of these functions.

    Thanks,

    Scott

  103. Tom J says:

    Welcome back Scott and congratulations. Your work on this sheet is GREATLY appreciated.
    Is there a reason you stopped using importJSON? It seemed when you began using it on ver 5.0, the data retrieval was much faster. I’ve been using the formula
    =if(isblank(A5),,IMPORTJSON(“https://cloud.iexapis.com/stable/stock/”&A5&”/quote?token=pk_XXXX”,”latestPrice”)) and it seems to be working.

    Thanks again
    Tom J

    • scott says:

      Hi Tom J,

      Thanks! I have a version that I’m experimenting with internally that is using importJSON and, when it works, it is much faster than using importdata. Certain functions, like latest price lookup, work reliably with importJSON but dividend data does not. I’d like to go back to importJSON for everything as well but the capability with IEX might just not yet be there.

      See my GitHub post for some of the issues I’m running into: https://github.com/iexg/IEX-API/issues/1169

      Scott

    • scott says:

      Tom J,

      Your importJSON function will continue to work in the latest version, but you will have to rename it to importJSON2. I’ve been experimenting with a different script for importJSON to allow it to work for other data. The original importJSON that you are using is now known as importJSON.

      Scott

  104. Jeff says:

    I think there is an issue with “Dividend Yield” on main Portfolio page as all of my stocks report less than 1% and I know that is not correct. As example Cisco (CSCO) has dividend Yield of about ~2.6% from quick look up on web, yet on Portfolio tab Column P (Dividend Yield) it is ~0.66%. Cell P4 seems to work just fine (The average Dividend Yield for all your stocks combined)

    • scott says:

      Does the DivPayoutCalc sheet show it correctly? There should be annual dividend and dividend payout (which is annual dividend divided by the number of months that pay).

      Scott

      • Aleks says:

        Could be that before in Reference Data: Dividend per Share (IEX) showed the dividend for the entire year.
        You’ve edited this cell to now to show: LAST-DIVIDEND-AMOUNT
        that is probably why in the portfolio Tab, column P now only shows the dividend for the last payment instead of the yield for the entire year.
        Hopefully there is a workaround/fix for this..?

    • scott says:

      I’ll take a look tonight. I think I might have introduced an error when updating the API formulas. Thanks for catching it!

      Scott

      • Billy Young says:

        Hello Scott, I think It’s because the Dividend per Share (IEX) on ReferenceData page is calculating quarterly dividend payout instead of annual.

  105. Jeff says:

    Yes, Column C and E work correctly on DivPayoutCal tab.

  106. scott says:

    Hey guys,

    Sorry for the error on Annual Dividend and Dividend Yield. It is working correctly now. The issue before was that it was returning the last dividend only. This meant that for a stock that pays quarterly you would have had to multiple it by 4 or for a stock that pays bi-annually, by 2. What happens now is that it multiples it by the number of payouts that you type into the DivPayoutCalc tab. So it should work correctly regardless of how often the stock pays.
    This has been updated on August 8th in version 5.2.3.

    Scott

  107. Brian says:

    I am just now getting around to downloading the latest (updated) version of this spreadsheet. Once I download it, will all the data and transactions I have in the older version automatically be imported to the newest version? I am trying to avoid having to reenter all the transactions.

    Thanks in advance!

    • scott says:

      Hi Brian, Yes it should. Just copy and paste the data from the transaction sheet into the new spreadsheet and the rest should start working. It will take awhile to refresh the latest prices, dividend amounts, pay dates, etc. Remember to get an IEX API code if you haven’t already. Instructions are at the top of this page.

      Let me know if you have any questions.

      Scott
      scott recently posted…May, June, and July 2019 Dividend IncomeMy Profile

  108. Jake H says:

    last week, the reference data column H and I (ex div and div pay date) worked fine. now they are showing a date in 2018. anyone have an idea? i tried to use Scott’s new guidelines with IEX token but that gave me a a five digit number that had no relation to a real life date. i then tried to download the new version and worked around with that but nothing worked right. in my trial and error, i’m getting #N/A in some, not all, of the results in column F (dividend per share IEX). any assistance would be grateful. thanks

    Jake

  109. AG says:

    First off, this sheet has been an absolute lifesaver for me for tracking my portfolio of 100+ stocks and the dividend income from them. I have been using this sheet for almost 6 months now. However, in the last week or so, it seems my reference data worksheet has decided to take ages to update. Both for current stock price as well as dividend information. Is there something I can do to fix this and make it refresh in a reasonable amount of time?

    • Jim says:

      IEX is dreadfully slow and I have 75 stocks I track so I run out of Calls by the end of the month. It’s better than nothing….but barely.

  110. MaxB says:

    Got this working… NIce spreadsheet.
    However, I need more of watch list so I created my own sheet.. Took a while to get all going since I was not familier with the api.. In the end it works.
    However, it is frightly slow.. I have 60 stocks on the watch list, retrieving name, pe, div, high/low. Takes over an hour at 11:00pm.. but it does do the job….

  111. MaxB says:

    Has IEX interface totally crashed ? No updates what so ever or so slow I don’t even see anything update (i have about 40 stocks and 5 fields)

    Even original is not updating…

    • CD says:

      IEX is terrible, after spending many wasted hours trying to adapt my spreadsheet just to help IEX process faster I finally gave up. Now I use a combination of Google Finance with Yahoo Finance on my Reference Data page. Here’s how I solved this very frustrating problem.

      Google Finance >for “Last Price (IEX)” column
      Formula-> =GoogleFinance(A5)
      *A5 is the Stock Ticker

      Yahoo Finance > for Div Per Share, Ex Div Date, Div Pay Date columns
      This one takes a small amount of legwork, but FAR SHORTER than waiting for IEX which will never happen in the end anyway.

      First, you can link almost any brokerage account in Yahoo Finance and it will refresh either when you log in or hit refresh manually; I use Robinhood and link my account in Yahoo Finance.

      Next, I create a “New View” in Yahoo Finance this view contains the following in order
      Symbol > Div/Share > Ex-Div Date> Div Payment Date

      Then, I create a “Copy & Paste” tab in my Dividend Spreadsheet where I simply dump the Yahoo data from my new view. **Important to note that my spreadsheet is sorted A>Z by stock symbol and I do the same in Yahoo.
      I highlight all the info from this view in Yahoo, Copy, then paste into my “Copy & Paste” tab in the Spreadsheet. Now it’s time to transfer the data into the “ReferenceData” Tab.
      1. Copy the “Div/Share” column that you got from Yahoo into ReferenceData Tab, column K-“Manual Div”. Change your Column F-“Div per Share (IEX)” formula to be-> =sum(K5/DivPayoutCalc!D2)
      This is pulling from your new Manual Dividend Column (K5) and dividing it by the number of times that yearly dividend gets paid (DivPayoutCalc!D2). Example: AAPL’s yearly dividend per share is 3.08, divided by 4 because it’s paid out quarterly, equals .77 in column F

      From here the rest of your spreadsheet shouldn’t need modifying, the ReferenceData sheet is really the driver in this whole spreadsheet. A few things to remember
      1. Make sure your sheet is sorted alpha by ticker symbol
      2. Change that formula in Column F-“Div per Share (IEX)” to be =sum(K5/DivPayoutCalc!D2) and so on down the sheet.
      3. When you copy from Yahoo Finance make sure you sort alpha by symbol before copying.

      The Ex-Div Date and Div Payment Date should be self-explanatory, copy both of these columns in their respective spots.

      This sounds like a lot of work, but once you set up the new formula it takes maybe 2 minutes tops to complete the whole copy-paste process; I do it on a weekly basis.

      • scott says:

        Great suggestions. I and pretty much everyone here is having a ton of trouble with IEX. I agree that it is very slow to load. I’ll see about reverting back to Yahoo for some data, though that is not without its problems as well.

        Scott

    • MaxB says:

      Even though it doesn’t work in the end, getting back into it (im retired) got me very interested in working towards something that may work… Can’t wait to see how people get some of this working… The spreadsheet does a lot of stuff that can be changed/adapted to many different applications… Can’t wait till a resolution is found.

  112. RW says:

    Anyone know how to handle the Multichoice Group & Prosus NV spin offs from Naspers? I’m having difficulty figuring out how to enter these in a way that makes sense.

  113. Venkata says:

    I see that the RDS.A and RDS.B symbols are not getting information using the IEX website. Anyone have the same issue or can suggest what the issue may be ?

  114. RB says:

    HI scott.

    Thank you for the spreadsheet.

    I have a question.
    I want to add dividends ‘frequency’ data.

    The problem is If I use this formula,
    =IMPORTDATA(“https://cloud.iexapis.com/stable/stock/AAPL/dividends/1y?token=pk_123456789”)

    It calls all of the dividends data.
    How can I get only ‘frequency’ data? thanks.

  115. Venkata says:

    Data updated using RDS.A and RDS.B. Weird that it was not working earlier and now it works.

  116. TJ says:

    Scott – such an amazing spreadsheet and thanks for making this open source to all.

    IEMG (iShares Core Emerging Markets) hasn’t been working for awhile using the IEX API. Anyone have a workaround or fix (or has the upgraded IEX account, and can flag for them)?
    TJ recently posted…Alternative Investments – Diversification Part 3My Profile

  117. Liana Roa says:

    Hi Scott,

    I am having some issues with the stocks ACT and S. its saying that resource at url not found in the transaction sheet. am i missing something?

  118. Brian says:

    I am having an issue with one of my holdings not showing as an option (dropdown list) in column A on the ‘DivCalendar’ sheet. I have the holding (SKT) correctly set up on the other sheets (Portfolio, DivPayoutCalc, and ReferenceDate), however I just cannot get it to show on the list of available holdings on the DivCalendar sheet.

    Has anyone experienced this issue? I cannot imagine there is a limit to how many rows/holdings this sheet can handle.

    Thanks!

    • Tom J says:

      Brian,
      I don’t think there is a limit to the number of rows but the sheet is initially setup for data up to row 64 on the Portfolio sheet. If you added rows to the Portfolio sheet, you also need to adjust the data validation range for columns A & B on the DivCalendar sheet.

      Tom J

  119. Ken says:

    Hi Scoot

    I see that the GLOP.PR.A symbol is not getting information using the IEX website. Anyone have the same issue or can suggest what the issue?

    Thanks in advance.

  120. Cassandra Edwards says:

    WORST EXPERIENCE EVER!!! It was our first time here. Called in to make an appointment for 3.
    We got there and waited an extra 30 minutes. The 2
    masseuses that did my girlfriend and I were on there phones while doing our massages.

    They were TERRIBLE!!! We’ve had bad ones before but this tops all
    of them. I DO NOT RECOMMEND THIS PLACE. There are better ones in the area.

    ***They need to replace those 2 masseuses***

    BEWARE!!! There is a mandatory gratuity for which type of
    massage you get.

    • Tom J says:

      Wow! I’ve been very impressed with the Dividend Stock Portfolio Spreadsheet but who knew they also had massages!! They obviously need to expand their advertising budget. 🙂

  121. Michael says:

    Is there a way to organize the portfolio and reference data sheets into alphabetical order without messing up the data? My OCD is KILLING me lol.

    Thanks for the Spreadsheet!

  122. Earl says:

    Is there a way to enter transactions using symbols rather than the stock name? I tried adding a column, but that messes up other formulas.

  123. Ryan says:

    Thank you for taking the time to put this together. I look forward to using it in the future. I’m assuming that international stocks aren’t being tracked? I have a position in TSM but none of the data seems to populate. Thank you

  124. Chris says:

    I would like to make a copy of the Dividend spreadsheet but am unable to do so. I click on the link and don’t get a “live” google spreadsheet that I can copy and save. What am I doing wrong?

  125. Hi guys. Thats a really handy Spreadsheet, but does it still work for you? Last month it did work and now, all of sudden i get only NA Errors.
    Anyway, Have a great one!!

  126. Ajit says:

    Hi Scott, thanks for all the work you’ve put into this, I found the sheet very useful! The ReferenceData sheet was very unstable for me because of all the individual calls to the IEX Cloud API, not to mention that the free API budget runs out fast as well. So I took a shot at using their batch API instead, to make a single call for all holdings and parsing the response to populate the sheet. Seems to be much more stable and efficient now. Please note that since the script only allows updating a contiguous set of columns, I had to move the “Cost per Share” column last which entailed changes to the Portfolio sheet. I’m pasting my apps script here, please feel free to integrate into your spreadsheet for the community’s benefit.

    // Returns price and dividend information about the given
    // comma-separate list `symbols`, in the same order as the input.
    // Output format: list of lists, each inner list containing:
    // [recent price (float), last div (float), ex date (string), payment date (string)].
    // Google sheets uses this format to paint contiguous cells starting
    // with the cell on which this formula is applied.
    //
    // Example output:
    // [[200.0, 2.4, ‘2020-05-02’, ‘2020-06-01’],
    // [145.0, 3.5, ‘2020-05-15’, ‘2020-06-15’]]
    //
    // Example invocation: =getPriceAndDividendInfo(JOIN(“,”, A5:A39), $M$6)
    // where M6 contains the IEX API token.
    function getPriceAndDividendInfo(symbols, token) {
    // Uncomment for local testing
    // var symbols = “MSFT,SWK”;
    // var token = “your_token_here”;
    var response = UrlFetchApp.fetch(
    “https://cloud.iexapis.com/stable/stock/market/batch?symbols=”
    + symbols + “&types=quote,dividends&range=3m&token=” + token).getContentText();
    var data = JSON.parse(response);
    var symbols_list = symbols.split(“,”);
    return extractInfo(symbols_list, data);
    }

    function extractInfo(symbols_list, data) {
    result = [];
    for (var idx in symbols_list) {
    var symbol = symbols_list[idx];
    var recentPrice = 0.0;
    var lastDiv = 0.0;
    var exDate = “”;
    var paymentDate = “”;
    if (“quote” in data[symbol] && “latestPrice” in data[symbol][“quote”])
    var recentPrice = parseFloat(data[symbol][“quote”][“latestPrice”])
    if (“dividends” in data[symbol] && data[symbol][“dividends”].length > 0)
    var dividendInfo = data[symbol][“dividends”][0]
    if (“amount” in dividendInfo)
    var lastDiv = parseFloat(dividendInfo[“amount”])
    if (“exDate” in dividendInfo)
    var exDate = dividendInfo[“exDate”]
    if (“paymentDate” in dividendInfo)
    var paymentDate = dividendInfo[“paymentDate”]
    result.push([recentPrice, lastDiv, exDate, paymentDate]);
    }
    return result;
    }

    • scott says:

      @Ajit, Wow. Thanks so much! I’ve tried this in a non-public version of the spreadsheet and it works well. There are a couple of issues that I’m wondering if you have run into.

      1) if an incorrect ticker symbol is entered, the enter array has an error and no data is populated for even the correct ticker symbols

      2) the dividend data (div per share, ex div, div pay date) is incorrect for non-dividend paying stocks and not just incorrect, but takes the values of an early dividend paying stock; for example, if I have AAPL on one row and BRK.B on the second row, BRK.B will be populated with all the dividend information from AAPL.

      Do you have any suggestions on how to deal with these issues?

      I had played with batch lookups before and I have always ran into these kind of issues in the past too.

      Thanks again for sharing your script!

      Scott

      • Ajit says:

        Hi Scott, thanks for trying it out! Unfortunately I only spent an hour on the script so didn’t get a chance to iron out all these bugs.
        For (2) it’s probably that the call returns no data for non dividend paying symbols. Can try pushing placeholder results in such cases:
        if symbol not in data:
        result.push([recentPrice, lastDiv, exDate, paymentDate]);
        break

        Not really sure how to fix (1) unless there’s a way to validate invalid symbols.

  127. Thomas says:

    Greetings,

    Thank you so much for this helpful utility. I am able to see all of my stock names and ticker symbols on the Portfolio tab, but I’m unable to find them on the Stock drop-down list on the Transactions tab. I’m able to see the symbols on the IEX website. Is there a fix for this? Please advise. Again many thanks!!

    • scott says:

      Can you see some of the stocks on the drop-down list?

      If so, this should fix it: Right click on a Stock cell and click on Data Validation. Under criteria, you’ll want to make sure that the Criteria includes the ReferenceData range for all of your stocks. The range currently goes to 64 so if you have more stocks listed beyond B64 on ReferenceData, you’ll have to edit that criteria to include everything.

      Scott

  128. Catherine Schaewe says:

    This is a truly amazing spreadsheet. Thank you.
    I have loaded in a couple of years’ worth of transactions, including lots of dividends.
    However, for some reason the DivCalendar only shows dividends from June2020. Am I doing something wrong?

    • TomJ says:

      Catherine,
      The DivCalendar only displays one year at a time. The top left cell, A1, is a dropdown list to select the desired year. Hope this helps.

      Tom

      • Catherine Schaewe says:

        I understand the drop down year. But the dividend calendar does not show dividends prior to June 2020, even though I have monthly dividends For every month for two years prior, which I entered into the transactions. It’s not a huge issue, I went back and entered the totals manually into the summary page, but it is odd.

  129. Jason Cortez says:

    I’m having a difficult time populating the spreadsheet. Under transactions tab, if I use “buy” under column B, then divcalendar and summary tabs are empty, but the portfolio tab is accurate. If i use “div” under column B (vice buy), then the divcalendar and summary tabs are accurate but the portfolio tab is inaccurate. Please help.
    Jason Cortez recently posted…Why Long-Term Investors Should Consider Dividend Stocks – Guest PostMy Profile

    • Tom J says:

      Jason,
      It sounds like you are trying to enter a DRIP transaction. This is accomplished with two line entries, a Div entry and a corresponding Buy entry. First, enter the Div using “1” for the “Transacted Shares” and the amount of the dividend in “Transacted Price/Share”. Then on the next line, enter the number of shares bought in “Transacted Shares” and the corresponding share price in “Transacted Price/Share”.

      Hopefully this helps. If you weren’t trying to enter a DRIP transaction I apologize.
      Good Luck
      Tom

  130. Joseph says:

    Im having an issue where im logging my dividends in the transaction tabs, but its not populating in the dividend calendar for Sep 2020

    • Tom J says:

      Joseph,
      On the Transactions tab, are the green cells displaying calculated values next to your entries in the brown cells?

      Tom

      • Joseph says:

        Tom,

        Thanks for your reply. Yes the green cells are populated with the correct values. It seems to be the that information transition over to the other tabs, as the divcal tab doesnt populate the information (There is a formula in the cell) but it also doesnt populate in the summary tab.

        Thanks for your continued help

        -Joe

  131. I live in UK and I own stocks from Europe, USA and UK but your script only seems to be working for US stocks only is there a IEX cloud database with world wide stocks.

  132. Michael says:

    in the last week or so I’ve been getting “error reporting” on everything from IEX for the fist part of the day, doesnt start getting info again until afternoon sometime. Anyone else having this issue? Just emailed support at IEX to see what has changed and if i need to alter the way calls are written but thought id check with the community here while i wait…

  133. Chris@TTL says:

    Been messing around with this updated version of the sheet for a little while now, testing out some of the calculations and data analysis. Pretty great, thanks for providing it!

    I was working on some of my own dividend projections (and messing with some old portfolio ideas) which this tool gave me some insight toward. Thank you!
    Chris@TTL recently posted…Index Funds vs Individual Stocks (My Worst Investing Mistake)My Profile

  134. John says:

    Will this track both usd & cad stocks/ETFs?

  135. Venkta says:

    Did anyone experience a problem with using IEX token to get stock data in the Reference tab ? For the past couple of weeks the data does not load on Reference tab.

  136. Terrie Brewer says:

    I’ve searched your Q&A and found nothing about how to change the “DUMMYFUNCTION” in all the formula entries. I must be doing something wrong, every time I try to delete “DUMMYFUNCTION” I get an Error. Please help me …. I guess I’m a beginner?

  137. Asj says:

    Hi Scott,
    First of all, thanks a lot for this marvelous piece of work and sharing this with everyone.
    I am copy pasting your response to a comment regarding DRIP below. Here you mentioned enter the `number of shares as zero` but I am also seeing in some other places you mentioned it as enter ‘number of shares as 1 ‘.
    Which one we should enter as number of shares 1 or 0 for DRIP in step1
    —————————————————
    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.
    —————————————————

    • Jim Terryberry says:

      I’ve always done DRIP as 1 as zero won’t calculate correctly

    • Till says:

      Maybe I’m doing it wrong, but I thought it should be in the case of 0.5c Div per Share, with a holding of 10 shares:
      Div Entry: Share = 10. Price = 0.5 (note that for a div entry, the cumulative share formula in column J is disabled)
      Buy Entry: Share = (sum of dividend payment / current market price) Price = Current market price

      This then gives the correct money in/out for cash flow as well as the correct share quantity I think?

  138. Tom says:

    Hey Scott,

    Thanks for doing this. I’ve got all the basics down and now I’m trying to create separate accounts (TIRA, ROTH, TAX), However, I’m unable to do this. I’ve got as far as creating the 3 different accounts on the LIST sheet, but I’m trying to figure out how to get it to work on the Summary page. I know how to create the column and validate so that I can select it from the drop down, but as far as entering transactions and making sure the total shows up correctly, I can’t figure it out.

    • Jim says:

      Anyone can help with this as well? in the screenshots, it does look like it support multiple accounts but in the actual spreadsheet the option is not available anymore?

      I would like to group stocks that are in my RRSP, TSFA and CASH accounts.

  139. Jim Kelley says:

    Hi
    I just recently discovered this spreadsheet and it’s looking good and very useful. I am seeing an error within DivCalendar, cells D1 through O1 all say “#ERROR!”. I just started buying stocks this year and haven’t yet received a dividend, could this be the cause of the error?
    Also in DivCalendar I’m getting errors in cells D50 through O59, also “#ERROR!”.

  140. Venkata says:

    I have a IEX token to update the stock price on the spreadsheet. The spreadsheet is showing errors and when I checked my account on IEX and found that there is a limit of (500,000) hits for free token. This may be the reason why I am receiving errors on my spreadsheet. Not sure if this is accurate. Did anyone havea similar issue ? Looks like I will need to pay $9/month for any additoinal hits. Anyone had this issue ?

    • Jim says:

      Venkata, depends on the error. I found that I had to change the formulas that refer to the cell the token is in to “&$M$6&”

  141. Venkata says:

    My formulas show “&$M$6&” in the cells that require updates from IEX using the token. These are the errors I have on the Reference tab.
    Dividend per Share column error = Could not fetch url: https://cloud.iexapis.com/stable/data-points/PG/LAST-DIVIDEND-AMOUNT?token=pk_*****
    Last Price column error = Could not fetch url: https://cloud.iexapis.com/stable/stock/PPG/quote/latestPrice?token=pk_*****
    Ex Div Date column error = Could not fetch url: https://cloud.iexapis.com/stable/data-points/PFZ/NEXTDIVIDENDDATE?token=pk_*****
    Div Pay Date column error = Could not fetch url: https://cloud.iexapis.com/stable/data-points/PFZ/LAST-DIVIDEND-PAYMENT-DATE?token=pk_*****

  142. Venkata says:

    Does anyone have any issues if there are more than 64 stocks on the DivCalendar tab ? Do I have to update any formulas if I am adding more than 64 rows in this tab ?

  143. Venkata says:

    Correction: It is DivPayoutCalc tab and not DivCalendar tab.

  144. hedge funds says:

    Right now it seems like WordPress is the preferred blogging platform
    out there right now. (from what I’ve read) Is that
    what you’re using on your blog?
    hedge funds recently posted…hedge fundsMy Profile

  145. Matt J Schenkel says:

    Anyone have an idea on how best to record spinoff company transactions? “O” and “T” are going to be spinning off new companies for people who currently own shares. I was trying to get opinions on how best to record these “newco” shares in the transactions page. Any ideas would be appreciated!

  146. Bernard says:

    Hi,

    The IEX console is very slow in loading Canadian stock prices, is there a way to accelerate this process?
    Thanks

  147. Hendo says:

    Scott, using your template has been extremely helpful for a novice like me. While there are too many errors to go into detail or hope for resolution, just basic functionality and having all data in one place has been great. The major impediment I’m hoping for help with keeping formulas intact when adding new rows. No problems with the transaction tab. However, the portfolio Tab, I think because it shows totals is now not registering new tickers, new portfolio additions. I moved the total row down one row and so on to create space then did the same as on transactions page where I dragged a row with formulas. Everything looked like it was going to work, but when I add a new stock to portfolio and then tab over to enter transactions, that new ticker symbol I just created is not there, doesn’t appear in the drop down box. I would really appreciate help as I’ve come to rely on the sheet heavily.

    • Tom J says:

      Hendo
      Sorry, I meant to reply to your message instead of making a new entry below. Not sure if you’d get a notification.

      Tom J

      • hendo says:

        Tom, While I have you and since your previous instructions were so clear, figured I’d check to see if 2 other issues are as easily resolved? Please forgive me if this has been answered.

        I wanted to adjust sectors. Hoped it would be in the ‘data validation’ but was not. Since this spreadsheet is primarily for combining accounts, to see the allocation is important. How to edit and perhaps add a sector?

        On tab 2 “summary” everything is blank. Row 4 with ‘totals’ all fields show #NA and have the maroon corner marked noting something is wrong.

        Maybe understanding HLOOKUP – could not find value error message would be a key starting point?

        • Tom J says:

          The first issue is easy. The sector column on the Portfolio Tab is controlled by a data validation function as you expected. It points to the list of possible sectors on the List Tab. I think the default for the list goes to line 20. You can change any of these and also add additional sectors. If you add additional sectors, you will need to go to the Summary Tab and scroll down until you see a similar list of sectors. These are being pulled over from the List Tab. Drag the formula down in the Sector column and the Value column. I think you’ll find that you need to add the exact number of additional lines that you added on the List Tab or you’ll get a false reading in the Value column.

          If you added additional sectors, you’ll need to make sure the data validation is looking at the increased number. On the Portfolio Tab, select the “A” column to highlight the whole column then hold down CTRL and select the “Sector” text cell to leave only the data cells in the column highlighted. On the menu bar, select Data, Data Validation to open the Data Validation window. On the Criteria line, click the Cell icon that looks like 4 squares. This is the data range on the List Tab. Change the last number from 20 to whatever size you increased on List Tab.
          On the Summary Tab, the Sector Weight Graph also need the correct range to select from. Click the Graph, then the 3 dot menu icon, then edit chart. Adjust the data range to correspond to the values at the bottom of the Summary page.

          As far as the issue with everything blank on the Summary Tab, I’ll have to think about that for a bit.

          Hope this helps.

          Tom J

  148. Tom J says:

    Hendo,
    I can try to help until Scott sees your question.
    The “Stock” dropdown box on the Transactions Tab uses a data validation function to tell it where to look for valid selections to display. Initially, its looking at the ReferenceData Tab B5 – B64. To increase the number of allowed stocks, you need to drag the formulas down on the ReferenceData Tab the same way you did on the Portfolio Tab.
    Then, go to the Transactions Tab. Select the “C” column which will highlight the whole column. We only want the data cells highlighted so hold down the CTRL key and select the “Stock” text cell. This should leave the rest of the data cells in the column highlighted. On the menu bar, select Data, Data Validation to open the Data Validation window.
    On the Criteria line, click the Cell icon that looks like 4 squares. This is the data range on the ReferenceData Tab. Change the last number from 64 to whatever size you increased on you ReferenceData Tab.

    This may sound a bit complicated if you haven’t worked with spreadsheets much so just step through it slowly before changing anything. Hopefully this helps until someone can explain it more clearly than me!
    Good Luck
    Tom J

    • hendo says:

      Hi Tom,

      I am so grateful for your effort in drafting that obviously time consuming and really easy to follow instructions response.

      Everything worked. I remember using the data validation once before so this was a really helpful response as I piece my spreadsheet skills together.

      Looking over some other questions on this site and it astonishing over the years how this template has been in use for so many – probably due to fantastic initial setup Scott put together, but without the detailed replies from yourself as well as many others it wouldn’t have the staying power it does.

      Thanks again your reply truly saved me.

  149. Michael says:

    Hi Scott,

    Thank you for this awesome spreadsheet!

    I ran into a problem with QYLD today. There seems to be no data available from IEXCloud about the dividend amounts. Since the QYLD dividend gets paid monthly and the amount is always changing from months to months what would be your recommendation to track those dividends?

    Thank you for your support!

    • Cathy S says:

      Michael – not sure how often Scott checks this, so I’ll just mention what I do with QYLD and other mutual funds. Since the reference data info on dividends is really only used to estimate dividend income, and you manually enter the exact amount when you receive it, I find that an approximation is good enough. You will see that column K in the ReferenceData sheet allows you to manually enter a dividend amount.
      Your income will be exact, but your estimated dividends may be off a tiny amount. I use $.19 myself.

      • Michael says:

        Cathy, thank you. That helped me put in all the data for QYLD. $0.19 is a good average amount. I might have some more questions. Thank you for helping me out today!

  150. Jim K says:

    Discovered that IEX API is going away…
    https://iextrading.com/alerts/#/171
    Effective November 19, 2021, and subject to the effectiveness of IEX rule filing SR-IEX-2021-13, IEX Exchange will retire the internet-based data products (i.e., IEX API, TOPS Viewer, Stocks application) that it currently publishes in parallel with its direct market data feeds. The final day of operation for these products will be November 18, 2021.

    is there a plan to switch to something else so the spreadsheet keeps working?

    • Jim K says:

      looks like IEX Cloud is sticking around, just IEX API going away. so… never mind

    • Dave says:

      Go into IEX and you will see a ‘secret’ token.
      I just inserted it into the ref data tab and it is working now.

      • JOHN says:

        Hi Dave, would you pls share how to make it work step by step?

        • Venkata says:

          I tried the token and there is a limit to the number of transactions for the token. Once this limit is reached, the spreadsheet is not updated. I think there is a fee for the token to continue to update the spreadsheet. If any of you have found a way around it, let me know.

          • Dave says:

            IEX free token is no longer free June 15th, 2023. Anyone have an alternative TOKEN to insert into the database?

  151. Simon says:

    Hi. This looks great at first glance. Can I add Swedish stocks to this sheet?
    Thanks.

  152. Cars says:

    I see you have a currency of Singapore $ there. Does this work with Singapore stocks? I couldn’t search much from IEX Cloud.

  153. Vijay Arora says:

    How can I add Canadian CDR stocks like appl, amzn, goog and so on? Thanks

  154. Hansen James says:

    This is a great spreadsheet, Scott. Using your template has been extremely helpful for a novice like me. The sheet is highly reliable.
    Hansen James recently posted…The Strategic Ways To Reduce Portfolio Risk When InvestingMy Profile

  155. Mike Peter says:

    This is really amazing. The functionality and having all data in one place has been great. The transaction tab also looks awesome.
    Mike Peter recently posted…How to Reduce Risk in Your Investment PortfolioMy Profile

  156. Chris Brown says:

    Glad the blog was helpful. Subscribe to get new posts in your inbox each week!

  157. Free live wallpapers for PC ⭐ Bring your desktop to life ❤️ Change the look of animated wallpapers on your PC ⚡ Live wallpaper pc download.

  158. Fady Ramzy says:

    I got the below error when I tried to run the script

    TypeError: Cannot call method “getRange” of null.
    copyLivePortfolioDataToHistory @ Code.gs:35

  159. Dave says:

    Does anyone know a free TOKEN we can insert into the spreadsheet?

    IEX Cloud free access is going away on June 15th, 2023.

    • David Pasco says:

      I was going to ask the same thing. Hoping something can be done to keep this spreadsheet usable.

  160. Bill says:

    I don’t know if Scott still reads the comments. I’d be willing to make a modest monetary contribution to him or anyone else who can modify the sheet or otherwise develop a work around to the loss of free access to IEX Cloud data. I’ve been using this since 2018 and would hate to lose everything but I’m just not skilled enough to figure out a solution.

  161. Skylar says:

    Echoing the previous few comments. I use this sheet religiously. While there are some newer and more automated options out there, I love the hands on facets of this sheet. Have been using it for years, and would hate to lose the data I’ve accumulated. Would be more than willing to help crowdfund a workaround.

  162. Cathy S says:

    There’s lots of workarounds. I stopped using IEX cloud at least a year ago.
    For example, to pull the annual dividend amount, you can get it from finviz with this formula:
    =if(isblank(A5),,(substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A5,”table”,10),7,2),”*”,””))*1)
    Where A5 is the ticker.
    To get the next dividend, you can use this formula, where E5 is the annual dividend you just got, A5 is the ticker, and it’s checking DivPayoutCalc for the number of dividends in a year:
    =E5/(query(DivPayoutCalc!$A$2:$F$80,”select D where F='”&A5&”‘”))
    You can get share price either from a google finance call (though it hasn’t been working well):
    =googlefinance(A5)
    Or from a finviz call:
    =substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A7&””,”table”,10),11,12),”*”,””)*1 where A7 is the ticker
    Any questions, let me know. I’ve massively customized this spreadsheet, but have been so glad to have it as a starting point.

    • Bill says:

      Hi Cathy, Can you share a blank copy of your google sheet with formulas? I tried copying an pasting what you have here in the Reference Data tab but it only returns #ERROR! so I’m clearly doing something wrong.

      • Marty says:

        Hi Bill, try replacing all the double quotes with your double own quotes. They get messed up on copy and paste.

        • Bill says:

          Thank you, Marty. I think that worked. I have stock price and annual dividend working. That’s good enough for now!

          • Cathy says:

            Hi Bill – if you’re not getting the quarterly (or monthly) dividend, make sure to put the ticker in column F in divpayoutcalc.

          • DEE says:

            Hi Cathy. Thank you for your time and valuable help. I am getting a #VALUE! error now on some of the tickers after reading and following the above posts from @Marty to replace the “”. Any idea’s how to correct?
            This is on the original download, ReferenceData tab. I am getting that error on both DOCU and SHOP. EBAY , AAPL and INTC return correctly.

          • Cathy says:

            Dee – see my answer below. I didn’t hit reply when I sent it, so you might not get a notification.

          • DEE says:

            Duh, Figured it out. No dividend so its returning that error. I added this to your formula so that I do not see the VALUE! error.
            =IFERROR(if(isblank(A5),,(substitute(index(importhtml(“https://finviz.com/quote.ashx?t=”&A5,”table”,10),7,2),”*”,””))*1),)

    • David Pasco says:

      Hey Cathy, I’m massively lost on how to input these changes, is there any chance you’d be willing to share a blank copy of the finished product, or explain where to input these values in a way that someone a little less knowledgeable (like me) coult better grasp? It would be hugely appreciated if so.

      • Cathy says:

        I’m not sure how to share a spreadsheet here
        But all these formulas I’ve mentioned are in the ReferenceData tab.
        So delete column e. Then rename the new column E from Dividend per share (IEX) to annual dividend.
        Then insert the first formula I mentioned above into E5. That will give you the annual dividend for the ticker that’s in A5. Make sure to fix the quotes, as mentioned by Marty, above.
        Then put the next formula I mentioned into D5. That will give you the quarterly/monthly (or whatever’s applicable) dividend for the ticker in A5. (Again, fix the quotes).
        Insert either of the formulas I mentioned above (googlefinance is easiest) into C5.
        That should replace the essential info you need. Getting ex-div dates is harder and more random, I don’t bother with that.
        Let me know if you have more questions.

        • Venkata says:

          Hi Cathy, I have copied this formula in D5 and getting N/A error. I have updated the double quotes and single quotes as well. Can you suggest if there is anything I am missing ? Here is the updated formula in D5.

          =E5/(query(DivPayoutCalc!$A$2:$F$80,”select D where F='”&A5&”‘”))

          • Cathy says:

            Venkata – I’m guessing it’s a quirk of my spreadsheet. I have the ticker symbol in column F of DivPayoutCalc, rather than column a. So this formula is looking at column F to see if it matches the ticker symbol in A5 of reference data. D, of course, is the number of dividends paid annually. So if your ticker in DivPayoutCalc is in column A, you need to change the F to A. LMK if that works.

          • Venkata says:

            Cathy,
            Thanks for the details. I was able to fix the issue.
            DivCalendar has #Value in the Pay Date and all month cells. Is there a fix for this issue?

          • Cathy says:

            Venkata – best guess is you have text that is supposed to be a number. If you click on one of the month cells you should be able to get a more specific error message which will tell you exactly what part of the formula has the problem.
            Take a look at the #value section here:
            https://www.benlcollins.com/spreadsheets/formula-parse-error/#errorValue
            If you tell me the specific error message I’ll look at it further.
            I never bother with the pay date, it’s almost impossible to get accurate ones.

        • Gina says:

          Does anyone know how to prepare a sheet similar to DivCalendar sheet where you can populate data by year from the drop-down list? I have the drop down list but can’t figure out how to link the sheet where the data is pulled from.

          • Cathy S says:

            Maybe I’m not understanding your question- but if you look at the formula in the div calendar sheet, you will see that it checks the transactions sheet in column a for transactions which are for the year in the drop-down list, and for the appropriate month, then looks for Div in column b and matches to the ticker in the div calendar row. Then it looks at column p when all of those things are true, in order to get the dividend(s). You can see another example in the summary sheet for the monthly dividends per year and the annual total dividends and total gain/loss.

          • Gina says:

            Thank you Cathy. I figured it out.

  163. Cathy says:

    Dee – you’re getting a value error on the dividend? That’s because neither of those stocks has a dividend, so there’s a dash in that location on finviz.
    https://finviz.com/quote.ashx?t=SHOP&ty=c&ta=1&p=d

    • DEE says:

      thank you. I’ve a slow mind. After I walked a bit, it dawned on me. Didn’t see your 3:02 post before I posted. thanks again! Started tinkering with the other formula’s to reduce the N/A errors as well.

      • Cathy says:

        Dee – I pull information from a number of sites. Let me know if there’s some metric you want, and I’ll tell you if I have a source for it.

  164. Skylar says:

    Thanks to all of the recent posters. My workbook is back from the dead! Much appreciated.

  165. Sebastian says:

    Wow this spreadsheet is awesome but looks like I’ll need to update it according to the predecessors in the comments. Looks super handy and hopefully can get it working since it’s a lovely tool!

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

  6. February 27, 2019

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

  7. June 27, 2020

    […] In some positive light, the dividend stock portfolio has returned some yet again larger dividend. I’m looking forward to seeing what the actual dividend will be when I am 100% percent done moving money from P2P lending to stocks. Although I have quite a good idea of the dividend I will receive each month, I want to see if the reality also matches the calculated dividend. For anyone interested, I’ tracking my dividend in a Google Sheet document using the template twoinvesting.com. […]

  8. October 10, 2023

    […] Dividend Stock Portfolio Tracker – TwoInvesting.com provides a free Google Sheet that you can copy and customize for your own purposes. You can input transactions on the Transactions tab and then see a detailed portfolio summary. In addition, you can use the dividend payout calculator to see distribution dates. […]

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.