Dividend Stock Portfolio Tracker with Transactions Page

An international version of this dividend tracker with support for multiple currencies is now available.

To see a list of all my spreadsheets, including a 30-year dollar cost averaging model, click the Spreadsheets tab at the top of the page.

1. Dividend Portfolio Tracker with Transactions Page
(revision history)

For references to the importxml and importhtml formulas that I used in my spreadsheets, please see here: Investing Formulas for Google Spreadsheets


Over the last few years I’ve transitioned into a conservative and proven investing approach for the long term involving purchases of high-quality dividend paying and raising companies.

Milestones:

  1. The major November 2014 revision, which updated the color scheme and layout was based on Ryan’s excellent spreadsheet available on his site, My Dividend Growth Portfolio.
  2. The major December 2014 update added a transaction sheet, which gives the ability to record every dividend, stock purchase and sale, and stock split. It was inspired by Kyith NG of Investment Moats.
  3. The major June 2015 update created a “clean” version of the spreadsheet making it super easy for the beginning spreadsheet user to begin entering his or her stocks.
  4. The February 2016 update automated the DivCalendar.

There are two versions available in the templates gallery. The first is the original Google Spreadsheets version which includes the transactions page as well as the “old” version, discussed below. The original version will be left available to use but will no longer be maintained. The new version is the one that will get future updates.

If you find this useful, please remember to rate it well on Google Docs Templates. Thanks!

Enjoy!


A template for the new Google Spreadsheet version is available here or below, if logged into your Google account:

If you are logged into Google, you’ll be able to preview the spreadsheets from the direct links above, otherwise, please visit my templates page at Google to see them all.

Orange cells – user needs to fill in appropriate data
Light green cells – these contain formulas and are automatically calculated by the spreadsheet. Do not fill in your own data!

Portfolio.
Portfolio
The Portfolio page is the main page which gives a summary of your current stocks. The orangish highlighted columns (Sector, Company Name, and Ticker) are what you manually update. Everything else is automatically updated based on information provided in the Transactions sheet, which is discussed below.

Sector: Drop-down menu allows you to pick the appropriate GCIS sector. The drop down choices come from the Lists sheet.
Account: Use this drop-down menu to choose the type of account that the stock is held in. Brokerage, Roth IRA, etc. The choices can be updated on the Lists sheet. The account information is currently used to help the Summary sheet tabulate information for each separate account.
Company name: The name of the company. This data will be used in the Transactions sheet.
Ticker: The stock’s ticker symbol as used on Google Finance. It is especially important to use the Google Finance version for Canadian exchange traded stocks. If it fails for whatever reason, the formula from the ReferenceData sheet defaults to try and pull the data from Yahoo Finance instead.
Cost Basis: Cost basis information is calculated from purchases 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

Finally, a pie chart shows the sector diversification of your portfolio. Note that there is also a cash section, which is manually edited under the market value column.

Summary.
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. See revision history for further details.

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.

DivCalendar.

Automated Dividend Calendar

Automated Dividend Calendar

The DivCalendar became automated in version 3.5 released on 2/4/2016. See the blog post discussing that here: Automated Dividend Calendar
The estimated monthly dividends became automated in version 4.1 released on 9/17/2016, blog post here: Automatic Estimation of Monthly Dividends for Google Sheets

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.

The automated version is not pre-highlighted with the expected dividend paying months, so that will still need to be done manually, if desired. However, any transactions that are classified as “Div” will show up on the DivCalendar and be highlighted a slightly darker color of green, as indicated above.

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 green as it gets automatically filled in. The conditional formatting for that is already in place.

personal DivCalendar

I also tabulate each month’s total dividend distributions, which is noted along the top and at the bottom of the DivCalendar table.

As of version 4.1, the template also includes automation of the estimated monthly dividend payout as calculated on the DivPayoutCalc sheet, described below.

In the future, this calendar will be very helpful to gauge the amount of dividend income coming in and to adjust expenditures accordingly.

If everything is working correctly, the total value on the DivCalendar sheet should match the monthly totals on the Summary sheet.

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

DivPayoutCalc.
divpayoutcalc

This sheet is used to calculate the estimated monthly dividends. You’ll have to type in the company name (matching what is on the Portfolio sheet) and then type in the months that the stock pays. A more detailed explanation of this is available here: Automatic Estimation of Monthly Dividends for Google Sheets

Transactions.
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. If you still want to try the old layout, please see the bottom of this page.

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

transactions_2

Note: Do note delete Row 2. This row contains the formulas, which are pre-populated for the first 100 entries. Thereafter, new transactions will have to be created by dragging the formula down from the entry above it. I tend to drag down from the very top.

New rows are able to be created in order to add additional entries. However, before the formulas will work correctly, you will again have to drag the formulas down from the top.

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. This is not technically needed but will be helpful for future versions of this spreadsheet, which will use the date data to tabulate annual dividends and calculate growth rate.

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 ArrayFormula generated list. As additional companies are entered, the drop down menu will need to be updated via the data validation section.
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

ReferenceData.
Reference Data

The data in this sheet is automatically updated. Other than dragging down the formulas as more stocks are purchased, you will not need to manually edit it.

Portfolio value.
Portfolio Value
Dividend Mantra updates his portfolio value monthly. I just update mine quarterly with data provided by Schwab’s Quarterly Portfolio Performance report.

Watchlist.
Watchlist
This sheet provides a basic watchlist of potential dividend stocks. I’m sure it is not nearly as sophisticated as what is available at any of the discount brokerage sites, but it works fine for its purpose. Basically, you just update the light blue sections of company name, symbol, date added to the watchlist, and its price and PE at the time it was added. The other fields then give the current price and PE and the 52 week high and low. It also calculates the % off the 52 week high and % from the 52 week low.

It also shows the annual dividend yield, payout ratio (based on EPS), 5-year dividend growth rate, and calculates the Chowder number for each stock. The Chowder Rule is described in detail here: Chowder Rule Breakdown – High Growth Vs. High Yield In Your Portfolio and by Chowder himself here. Basically, the Chowder Rule says that the combination of current yield with the 5-year compounded annual dividend growth rate should be equal to or more than 12%.

The goal of the watchlist is that you can add your top picks to this list and then buy the ones that seem to now have the best “value” as determined by change in PE, poor performance recently, greatest percentage from its 52 week high, and/or passing the Chowder Rule.

Lists.
lists
This hidden 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.


Revision History.

——–


OLD VERSION:
Note: The old version is still available for the time being and, for those individuals who buy-and-hold as well as reinvest their dividends, it may be perfectly adequate. However, it will not be maintained and will shortly be removed from the spreadsheet.

Portfolio Old.

This is the main sheet that includes the entire dividend portfolio. Any time I buy a stock or reinvest dividends, I update this sheet to reflect the new share count. Once the received dividends are updated in the DivCalender sheet, everything else is updated automatically. Only the Ticker and Shares columns are editable.

Sector: GCIS sector data is automatically updated for US stocks.
Ticker: ticker symbol for your stock (reference this as how Google Finance sees it. Canadian stocks require the exchange. For example, TSE:EXE for the Canadian company, Extendicare.
Market Value: latest stock price * number of shares
Cost Basis: purchase price of the stock plus any reinvested dividends (this is automatically calculated by data entered into DivCalendar and into the Cost Basis & DGR sheet)
Gain/Loss: cost basis – market value
Market Weight: weighting of each stock
Yield: current dividend yield
Annual Dividend: forward 12-month dividends
YOC: yield-on-cost
Div/Sh: quarterly dividend per share
Dividends Collected: total dividends received
Payback %: dividends as a percentage of the cost basis net reinvested dividends; reference

This sheet also calculates the total portfolio value, overall cost basis, overall gain/loss, 12-month forward estimated dividends, current dividend yield, and the yield on cost (YOC). It also shows the total dividends collected for each stock and the payback percentage. (The formulas as written assumes dividend reinvestment…if you are not doing that, you’ll have to edit the formulas.) They will also have to be updated each year to reflect the new DivCalendar data.

Payback percentage is the percentage of invested capital that has been returned as dividends. The amount of invested capital does not, obviously, take into account the dividends, which, for tax purposes, are included in the cost basis. I added this to my spreadsheet after reading about it on Passive Income Pursuit’s Payback period blog post.

Finally, a pie chart shows the sector diversification of your portfolio. Note that there is a cash section, which also contributes to the total portfolio value.

DivCalendar_Old.

See the DivCalendar information section above for further details.

DivCalendar is very important to keep up-to-date and accurate as the Income Old and Cost Basis & DGR Old sheets (discussed below) rely on its data.

Div Increases: 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 when I plug in the new quarterly dividend amount into Portfolio.

Income Old.

This sheet 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 reference the monthly totals listed on the DivCalendar Old sheet.

Cost Basis & DGR Old.

Company: the ticker symbol or name of each company you own
Capital Invested: the amount spent on each stock purchase including any fees; dividends are not included in this figure
Date Initially Purchased: date of stock purchase
Initial Dividend: annual dividend at time of purchase
Current Dividend: current annual dividend is automatically calculated from Div/Sh value in the Portfolio sheet
CAGR: compound annual growth rate

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

429 Responses

  1. Martin says:

    These are a heck of great spreadsheets you have. I think I will go for hunt on your site more often 🙂 I like a few which I didn’t know how to best make them.
    And thanks for the mention as well.

  2. scott says:

    No problem, Martin! I love making these so I thought I might as well make them available to others. I’m constantly looking for improvements so thanks for the dividend calendar concept. Much better than the system I had before!

    Still have to fine tune the way I keep track of options income. You can see how I do that currently on my own portfolio spreadsheet. Definitely not the most elegant solution. Once I begin selling more puts I’ll have to readdress that.

  3. Nice looking sheets. I am jealous. Now if I will only learn spreadsheets!
    Asset-Grinder recently posted…New Buys , Sells , Brewery and Real Estate UpdatesMy Profile

    • scott says:

      Thanks! Feel free to use them however you like. They are available on the Google Docs templates link above. I’m constantly trying to improve them too so if you can think of anything that you feel is missing or could be improved, please let me know.

      • Gary says:

        Scott-
        I can’t seem to figure-out how to create 3 separate portfolios- TIRA, ROTH, Taxable. I see it can be done, but it is a real problem on how to separate them– I tried creating duplicates and changing, but the transactions are not separate. Help!
        Love the spreadsheets.
        Gary

        • scott says:

          Hi Gary,

          Been busy with a new job the last month or so…apologize for not responding promptly to many of the comments. You could either make three separate copies of the spreadsheet or name the companies differently but keep the same ticker symbol. For example, GE (Roth), GE (TIRA), GE (taxable). Then choose the name in the transaction sheet that fits the type of account the transaction occurred in.

          Does this help?

          • Gary says:

            Thanks for the quick reply. I saw mention of the (IRA) etc- it seems to work–does the label have to be on the transaction and the portfolio page?
            When I try to get a different template to start a new one, I seem to get the same one I’ve already done ( same stuff that I filled in already. So far, using ‘duplicate’ to get a blank seems to be working , so hoping for the best.
            thanks-
            G

          • scott says:

            Yes, the label should initially be typed on the Portfolio page. Then you should be able to find it from the drop down on the Transactions page.

  4. Tawcan says:

    Great looking sheets! I’ll have to take a look them later and try to incorporate into my sheets. Thanks.
    Tawcan recently posted…Recent TransactionsMy Profile

  5. Nick says:

    Scott, Thanks for sharing your great spreadsheet. Do you think it might be possible to add a column for the holding sector/industry? We could then create a pie chart or graph showing the portfolio industry diversification.

    Do you know if there is a formula similar to the one you used to pull the current yield from yahoo finance that will instead pull the stock sector/industry from yahoo or google?

    Thanks again!
    Nick

    • scott says:

      Hey Nick,

      Good suggestions on adding the sector/industry and making a pie chart. I’ll see what I can do. There is probably a way to pull that information automatically, but since it is static, it might just be simpler to enter it manually.
      scott recently posted…Put Options on ANF and CRMMy Profile

    • scott says:

      Hi Nick,

      I’m still working on the pie chart, but I found two ways to import sector data:

      1) uses the GICS sector names:
      =index(importxml(“https://eresearch.fidelity.com/eresearch/goto/evaluate/snapshot.jhtml?symbols=”&A2&””,”//div[@class=’sub-heading’]//span[@class=’right’]”),1)

      2) uses whatever Yahoo references, ICB or Morningstar?:

      =Index(ImportHTML(“http://finance.yahoo.com/q/pr?s=”&A2&””, “table”, 9), 2 ,2)

      A2 in the above examples is the cell that contains the ticker symbol. Additionally, the portfolio template above has been updated to include sector data.

    • scott says:

      Nick,

      I just completed a major update to the spreadsheet template which includes, among other changes, a pie chart showing sector diversification.

      More information is available in the post above and/or on this page: http://www.twoinvesting.com/2014/11/major-portfolio-spreadsheet-update/

  6. John says:

    Nick thanks for sharing your spreadsheets! There are some great ideas that I will incorporate into mine. There are stocks that pay dividends monthly and I did not see any examples or how these would be tracked in your spreadsheet. Also, I wanted to confirm that there was no method to get the yield to come into the spreadsheet for Canadian stocks? I could get the quote to come in but the yield came up as “value”. Since it looks like you like spreadsheets as me have you ever tried using the Excel Stock Market Functions Add-In (files can be found here http://ogres-crypt.com/SMF/) Thanks again, JB

    • scott says:

      Hi John,

      I only have a single stock (Realty Income) which pays monthly. For that, I just manually update the spreadsheet to reflect monthly payments rather than quarterly.

      You’re right that the spreadsheet does not calculate Canadian stocks correctly. I use a GoogleFinance function to grab the current stock price but Yahoo Finance to grab the dividend. The problem arises because Google and Yahoo Finance use different ticker symbols.

      For example, CCL Industries Inc. is CCL.B in Google Finance but CCL-B.TO in Yahoo Finance. I just updated the formula in the Google Spreadsheets template so that both data is now obtained from Google Finance. So, it will now work to reference Canadian stocks by the ticker symbol that Google Finance recognizes and both the price and dividend should be updated.

      Please let me know if this works for you, John. Since I really like the online features of Google Docs, I actually don’t use Excel that much anymore.

      For reference, here’s the spreadsheet formula I used (A4 is the cell with the ticker symbol):

      =index((split(ImportXML(“http://finance.google.com/finance?q=” & A4, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”),”/”)),1,1)

  7. John says:

    Scott, thanks for updating to include Canadian symbols. I never really looked at Google Docs until now and the importXML function is powerful. In my spare time I will see if I can find a work around in excel which does not have the importXLM function. I would like to build this dividend tracking portion into the excel portfolio tracker I have been working on for a while. Also, since I am lazy I would also like to see if I can get the DivCalendar tab to update automatically, which at first glance looks to be a bit of an issue since there are no Yahoo or Google Finance fields that offer this data directly.

    Thanks again for all the great ideas.
    JB

    • scott says:

      John, No problem. Let me know if you come up with anything automatic for the DivCalendar tab. The things that are automatic right now (and based on data in other sheets) are the estimated dividends. I actually like manually updating the actual dividends I received each month on the DivCalendar tab because there’s something satisfying about putting in that number.

      The importXML function is very powerful. I just started learning about it. The tutorial I used was here: https://www.distilled.net/blog/distilled/guide-to-google-docs-importxml/

  8. scott says:

    I’ve made a few updates today. Improved and, I think, cleaner layout on the portfolio page. I’ve also added columns for cost basis as well as gain/loss. Also, more of the data on Payback & DGR sheet is automatically calculated based on entries in the DivCalendar sheet. Less things to manually edit is always good.

    Updates to the public template on Google Docs Templates already includes the formula updates to work with Canadian stocks.

    Sector data has also been added. Next on the list is using sector data and market weighting to construct a pie chart showing portfolio diversification.

  9. It looks like you put a ton of work into these. I like how everything updates itself unless you buy more shares or different holdings. Thanks for sharing this article.
    DividendMongrel recently posted…Recent Buys, 6 New Blogsters, and a New GoalMy Profile

    • scott says:

      Thanks! Yeah, it has been quite a bit of work. I’ve been making updates to the public version of it for over a year now and my personal version for over 3 years. Your version was before the latest changes went live on Google Docs Templates so now it is even more automated.

  10. I just came across your blog and I definitely like what I have seen thus far. Very nice spreadsheets! I seen some of them before on other blogs but you’ve still done a nice job putting them all together in a central location. Thanks for sharing. 🙂 AFFJ
    A Frugal Family’s Journey recently posted…P2P Accounts (Update) – November 2014My Profile

    • scott says:

      Thanks, AFFJ. I’m glad people find them useful since I have so much fun making them. Though, I hope the pending “upgrade” to the new Google Spreadsheets won’t break too many things. I’m constantly making changes so if there is something anyone wants available, let me know and I’ll see if I can get it done.

  11. Joe says:

    I am having difficulty using the template. I am not “spreadsheet savy” but I absolutely love all of the functions on your spreadsheet.

    Is there any instructions (vey simplified!!) that I can use to upload my portfolio? I tried manually but it is not working.

    Any help would be appreciated!!!!!

    Thanks,

    Joe

    • scott says:

      Hi Joe,

      I’m glad you like the spreadsheets! Hopefully I can help make it work for you. Maybe others are having the same problems. Could you just provide me some more information?

      Which one are you having trouble with? The dividend portfolio template? If that’s the case, are you able to open the template from the link above and start editing it? Unfortunately, there’s no easy way to import a preexisting portfolio…everything has to be put in manually at the beginning. For example, it won’t be able to connect to Schwab or TD Ameritrade and update the new trades for you.

      What portions weren’t manually working?

      Thanks,

      Scott

      • Joe says:

        I guess I am having difficulty trying to figure out what cells I manually need to put in and after entering the data, what automatically updates?

        • scott says:

          If you see a formula when you click on a cell, then that cell updates automatically.

          In the portfolio sheet, the ticker and # of shares are manually entered. The cost basis is updated based on each non-dividend reinvestment purchase (see cost basis & DGR sheet). The DivCalendar page is where you enter the dividends that you receive. In my version, the estimated dividend, which is tabulated at the bottom of each monthly column, is based on the annual dividends calculated in the portfolio sheet. Some of the cells, obviously, will have to be edited to reflect your own stocks. The Income sheet gets its data from the DivCalendar sheet.

          If you’ve had a dividend paying portfolio for some time, it might be easier to add the historical cost basis and total dividends received in manually.

          What I would do to start out would be to change one of the tickers on the portfolio sheet to something you own and just watch what changes.

          Please let me know if there’s something unclear in the post above. Thanks!

          • Joe says:

            Great – now I see how it work!

            One more question – how do you add transactions? Do you just manually enter the new cost basis and the new total shares manually? Or id there a way to enter it so you can record the actual transaction of the purchase?

            Thanks for your help!!!

          • scott says:

            Awesome. Glad it is working for you!

            I currently have it set up so that transactions are added in one of two ways:

            1) Since this spreadsheet was primarily created to keep track of dividend reinvestment, I have it set up so that every time you receive a dividend, you fill in its highlighted cell in the DivCalendar sheet. This does a few things. First, it provides a way to, at a glance, see how many companies pay each month and allows me to see how much money I have coming in. These dividends are also automatically added to the cost basis (since I am reinvesting them at this point) and also are automatically included in the “Dividends Collected” column. If you choose to not reinvest the dividends, then you will just have to edit the formula that calculates the cost basis.

            2) Each new purchase of stock apart from dividend reinvestment is included on the “Cost Basis & DGR” sheet. The blue highlighted cells are what you change in that sheet. For example, on the template I have two entries for KMI, reflecting the two times that I purchased it. (To see for examples of this, please visit my own portfolio, which uses the same template.) The “capital invested” column is the cost basis (including commissions) for each of those individual purchases. It is set up now to also keep track of the dividend’s compound annual growth rate.

            So, as of now there is not a specific transaction sheet per se. Since I don’t sell too often, it really hasn’t been much of an issue. It is something that I am going to be working on for a future version, though.

            Keep checking back on this page to stay updated. Thanks!

  12. Joe says:

    I am also trying to add rows but the functions do not carry over to the cells – is there a tip for doing this?

    Thanks,

    Joe

    • scott says:

      When you click on a cell that has a formula the cell will have a blue border and you will see a little blue dot in the bottom right corner. See the screenshot here:

      Click and drag down at the blue dot and the formula will carry over. If you want one part of the formula to stay constant, you will have to surround that cell reference with a ‘$’, for example, C$7.

      • Joe says:

        Thanks!! I’ll try that.

        • Joe says:

          The problem that I am having now is that after loading about 15 stocks into the template, when I click and drag columns such as div/share, a pop-up says that spreadsheets in Google Sheets only support up to 50 ImportXml functions in a single sheet- have you run across a problem like this before and if so, is there a fix?

          Thanks
          Joe

          • scott says:

            With 15 stocks, that shouldn’t be a problem, but, yes, the old Google spreadsheets does have a limit to the importxml fxn. Google is slowly transitioning everyone over to the new Google Sheets, which does not have that same problem. Unfortunately, they do not let you upload templates created in the new Google Sheets onto the templates page. I’m expecting them to allow that any day now…

            I have 26 stocks on mine and it is working fine. You could try deleting the watchlist page. That might free up some of the importxml commands.

            If you only have US stocks, another thing you could do is to replace the market value formula to be =GoogleFinance(A3), instead of the current importxml fxn. It will then use the built-in stock price lookup fxn, which works well for US traded stocks but does not work for Canadian exchange traded stocks.

            Also, I’m about to release an update to the portfolio that includes a transactions sheet. This might make things a bit easier for you. Look for an update soon!

  13. scott says:

    The Dividend Portfolio Tracker has just undergone a major update. It now includes a sheet to keep track of each transaction and dividend. It is more automated than ever!
    http://www.twoinvesting.com/2014/12/transactions-sheet-in-dividend-portfolio-tracker/

    • Joe says:

      Scott,

      I started over with the updated tracker and everything was going ok until I inserted additional rows. Everything on the transaction sheet went fine but when I looked over at the portfolio page, the only data that crossed over was the number of shares- all other items (market value was 0.0% etc). Do I need to change the functions or validation criteria? Or is there something else that needs to be done?

      Thanks,

      Joe

  14. Joe says:

    Forgot, the cost basis as well transferred over.

    • scott says:

      Go to the ReferenceData sheet. That is where the current stock price and dividend data is retrieved. Sometimes Google is a little slow in updating that page. If the values there say “Loading,” close the spreadsheet and then re-open it. Sometimes that helps it pop back up. All the data on that sheet is calculated automatically so no manually updating is needed.

      If the cost basis information and # of shares is correct, then the transaction sheet is working properly. The ReferenceData sheet is the likely problem.

  15. Joe says:

    Oh No!! – I think it is not loading because the reference data sheet won’t allow the rest of the cells to fill because of the “exceeds the 50 Importxml function!!!

    I think I am going to wait until the new google spreadsheets are released that will support more stocks. I have about 60 stocks that I need to upload.

    How will we know when the update is ready/released? I really love these spreadsheets but I don’t want to waste time trying to upload data that may be lost in the upgrade.

    Again thanks for your help,

    Joe

    • scott says:

      Joe,

      That could be the problem as well.

      But, guess what!?, I actually just checked and the ability to upload new Google Spreadsheets to the template gallery is now working.

      You could either wait until Google updates the version that you’ve been working on or switch over to the new version instead. To know that you’re using the new Google Sheets, you’ll see a little green checkbox in the bottom right corner of the spreadsheet.

      Scott

  16. Joe says:

    I can’t figure out how to upload the new version – is there a website to do this?

  17. Joe says:

    Ok – Finally loaded all of my 62 stocks into the new spreadsheets and overall I am very impressed!!!!
    It is really easy once you get used to it!!!

    I only have one minor problem with one of the stocks that being WP Carey (WPC) – it won’t load the dividend into the reference data – the current price uploads but that’s it.

    Also, on the reference sheet the dividend pay date comes up as a number ( i.e 41683) instead of the date on the rows I had to add.

    Thanks again,

    Joe

    • scott says:

      In the ReferenceData sheet, replace the current formula with this (updating the cell reference the correct ticker symbol). =iferror(iferror(REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&B11&”&ql=1”, “table”, 3), 8, 2), “[()]”, “”) , “([^/]*) “)/4))

      Google Finance does not show the dividend correctly. This forces it to only use Yahoo. I’ll have to fix the error checking in the original formula, but this should work fine for you now.

      Glad the spreadsheet is working otherwise for you!

      • Joe says:

        What column does this go? I plugged it- =iferror(iferror(REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&B11&”&ql=1″, “table”, 3), 8, 2), “[()]”, “”) , “([^/]*) “)/4)) – into column C and E and it said “Error”?

        • scott says:

          That formula provides the quarterly dividend per share so it would go into column E. If the latest price is working already, just leave column C alone.

          • Joe says:

            It is still showing error in column e

          • scott says:

            I just tried this formula in the template and it works fine:

            =iferror(iferror(REGEXextract(REGEXreplace(index (importhtml(“http://finance.yahoo.com/q?s=”&B14&”&ql=1”, “table”, 3), 8, 2), “[()]”, “”) , “([^/]*) “)/4))

            Sometimes when you copy and paste, the quotes don’t paste correctly. Go through and re-type the quotation marks and it should work for you. Everything within the quotation marks should be a green color. This seems like an error that Google needs to fix. And remember that the B14 cell above should be changed to reflect the cell that your WPC ticker symbol is in.

          • Joe says:

            It keeps saying error- Formula parse error

        • Joe says:

          I still cannot get it to work – I have hand typed it in the fct .

          1. I cannot get the part between the quotes to turn green
          2. I don’t understand what you mean by the cell above the symbol should be changed

          • Joe says:

            Ok it finally took the fct but the qty div/share and div yield cells have no numbers – the ex div date filled in though. Still don’t understand what you mean by changing the cell above

        • Joe says:

          This is what it looks like:

          WP Carey wpc 69.69 0.00 0.00% 9/26/2014 10/15/2014

          • Joe says:

            I finally got it to work!!!!

            Sorry about pestering you but since I am new to these spreadsheets, it is driving me nuts!!!

            Again, thanks for taking time out to help!!!!!

          • scott says:

            Great! Glad to hear that it is working. To help others, what did you change to fix it?

          • Joe says:

            To be honest, I really don’t know why it finally took. I retyped it and waited a minute and then all of a sudden it populated.

            Thanks again for helping!!

    • scott says:

      As far as the number for a date, just change the format of that cell to a “date” and then it should look correct.

  18. Joe says:

    Quick question:

    If I want to close out of a position and no longer want it on the spreadsheet, how do I delete it without messing up the rows in the spreadsheet?

    I tried to delete a position at row 30 and it messed up the portfolio page – it looked like rows and cells were no matching up when I looked at the function.

    Thanks,

    Joe

    • scott says:

      Drag the formulas down from the top to restore them all. If you do this, you will lose the ability to see historic realized gains, accumulated dividends, etc. You could always start a new sheet that uses the portfolio page as a template but only shows stocks that have 0 shares. I might do something like this in a future version.

      • Joe says:

        Thanks,

        I’ll try that instead of “deleting ” the row.

        Joe

        • scott says:

          You’d still want to delete the row on the portfolio sheet, but then drag down from the formulas in the first entry to “refresh” the lower rows. If you select all the green highlighted columns, you can drag down many formulas at once.

  19. So, I just got back to review your updated versions of your spreadsheets. You are really a geek 🙂

    I love the spreadsheets too and have them linked all together so I do not have to fill some data twice, thus once filled in one sheet, it automatically updates the other sheets.

    But lately Google spreadsheets experienced some issues with loading data 🙁
    Martin@hellosuckers recently posted…New purchase – Legacy Reserves LP (LGCY) MLP with 17.80% dividend yield (ROTH IRA)My Profile

    • scott says:

      Thanks, Martin! I guess I am a big geek. At least it is for something somewhat productive. 🙂

      I’m running into loading issues with Google’s new spreadsheets too. It’s especially frustrating when I write a new formula and don’t know if it is failing because it is written wrong or due to a server side issue.

      Thanks again for visiting!

  20. Joe says:

    Each day ( or even if I close and reopen on the same day) I go to my spreadsheet, a different amount appears in the annual dividend total in the portfolio section. Is this the problem you are referring to when you say the data from Google spreadsheets is not loading correctly?

    The annual dividend should not change in the same day. Even if a company increases/decreases the amount, I assume it should not change that frequently.

    Have you observed this happening?

    Thanks,

    Joe

    • scott says:

      No, I am not seeing this happen. You’re right that the annual dividend should update about once a year.

      It is calculated by taking the annual dividend (from the ReferenceData sheet) times the number of shares. Is that correct on the referencedata data?

      It could very well be a Google problem, but I can’t replicate it on my end.

  21. Joe says:

    It must be a Google problem because I just opened the spreadsheet again and it gives a different total at the bottom for the annual dividend amount than it did a few hours ago.

    • scott says:

      That’s really weird. Must be some Google issue. You are using the new Google spreadsheets version, right? The one with the green check mark along the bottom right portion of the page. If you start a new one using the initial stocks I picked (on original template), are you still running into the annual dividends being different?

      • Joe says:

        Yes I am using the new spreadsheets.

        After researching the reference data, one of my stocks that pays a one time yearly dividend of .83 per year is listed as paying it quarterly – Novo Nordisk (NVO) – the spreadsheet is accounting for it to pay quarterly thereby inflating the number.

        But as far as the amount that changes, I am not sure what is going on – I will keep an eye on it.

        • scott says:

          Yeah, the calculations assume a quarterly dividend. If that varies, you could manually update it or at least correct it so that it reflects a quarterly payment.

  22. Joe says:

    I recently purchased Helmerich and Payne (HP) and the reference data seems to be pulling in Hewlett-Packard (HPQ) by mistake (at least I think it is since all of the data for Hewlett Packard is the same).

    Is there any remedy for this except for having to manually type in all of the data and manually keep it up to date?

    • scott says:

      I just tried putting HP into my template and it worked here. Got both the latest stock price and the correct dividend. Might want to make sure that the dividend column isn’t incorrectly dividing by 4 or something.

      • Joe says:

        No the price is showing 40.70 – the exact price of Hewlett Packard.

        • scott says:

          If you change the ticker symbol to HPQ, what happens?

          • Joe says:

            when I change the symbol in the reference data to HPQ, the price changes from 40.77 to 40.73 only – the dividend stays the same

          • scott says:

            Try changing it to HP on the porfolio page and then just letting it sit for awhile. I can’t replicate your problem here so it may be an issue on Google’s side. If you want to use Yahoo finance to look up this data, please see one of the more recent blog posts. I’ve listed formulas for both Google and Yahoo Finance.

          • scott says:

            Also, could you check that the ticker symbol says is HP both on the portfolio page as well as on the ReferenceData page? Thanks!

    • scott says:

      I might add another column to allow people to more easily manually edit dividends as well. I’ll post more details tonight.

      With regard to the HP vs HPQ thing, is the latest price showing correctly for HP but the dividend is incorrectly showing HPQ, or are both incorrect?

  23. Joe says:

    Is there a way to change it to Yahoo finance? – you wrote a function for WP Carey for the quarterly dividend which worked but on HP both the price and the dividend is wrong

  24. Joe says:

    Both symbols are HP in the reference and portfolio sheets.

    I am having trouble getting the yahoo finance fct to work.

    I replace it in the last price(column C) in the reference data sheet and it comes up error.

    What formula are you using for yahoo finance?

    • scott says:

      Did you use the one on the blog post? I’ll check when I get back from work this evening. I’d just let it sit and see if it fixes itself. It is working fine here so must be a Google issue. Lastly, did you try dragging down from the top the formulas in the ReferenceData page? Those might need to be updated.

      • Joe says:

        yes, I tried that as well
        If it is a google issue, why would yours work and mine not?

        • scott says:

          Because part of the problem with Google’s spreadsheets is that there can be huge delays in the formulas updating. I’ve waited over a day for the initial formulas to fill correctly before. My guess is that you have to refresh all the formulas by dragging down from the top. And then wait…like I said, I tried using HP here and I am getting the correct data.

  25. Joe says:

    Interesting – when I go to Google finance and type HP in the search, Hewlett Packard comes up – it is definitely reading HP as Hewlett Packard and not Helmerich Payne in the spreadsheet

    • scott says:

      That is weird. The same thing happens to me now that I’m at home. A work around is to change the symbol on the portfolio page to NYSE:HP.

      Leave the formulas as they were initially (i.e., don’t change them over to Yahoo). It should work then.

      • Joe says:

        Yes, it does bring the correct price but then the quarterly dividend , ex dividend date and dividend yield does not work

        • scott says:

          Hmm. You’re right. For the time being I’d just update those manually. An accurate price is the most important thing since that changes daily while the dividend is usually more static. Sorry about that!

  26. scott says:

    The best I can do is using the Yahoo Finance API here: http://www.jarloo.com/yahoo_finance/

    Latest price: =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”&B4&”&f=l1”)
    Trailing Annual Dividend: =ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”&B4&”&f=d”)
    Forward Annual Dividend: =(index (importhtml(“http://finance.yahoo.com/q/ks?s=”&B4&””, “table”, 32), 2, 2))
    (B4 references the cell with the ticker symbol.)

    The forward annual dividend is retrieved from Yahoo Finance’s key statistics page. Table 32, row 2, column 2. In fact, you can play around with those numbers to grab any value you want from the key statistics page.

    • Joe says:

      I am confused – I am not familiar with writing the actual fct – do I type what you listed in each cell on the reference data page?

      • scott says:

        Yes. I’d copy and paste though. For example, if HP is the symbol in cell B4, I’d replace the formula to calculate the last price with the entire formula above. Then, choose the formula for trailing or forward annual dividend and put that into the appropriate cell in column E.

  27. Joe says:

    Great – It worked!

    Thanks again for your help!!

  28. scott says:

    Version 2.4 of the dividend portfolio template was just released. The latest version adds XIRR formulas to calculate the annualized return of individual stocks as well as the entire portfolio. See the Revision History above for further details.
    scott recently posted…Calculating Your Annualized Return – XIRR FunctionMy Profile

  29. Sarah says:

    Hello,

    This is really helpful!!

    Is there any way you could illustrate on excel how to calculate the return of a stock over one year including reinvested dividends?

  30. Pat says:

    Hello Scott!

    I am very new at using Google Spreadsheets and for the life of me cannot add a new stock to the Reference List in order to have it available in the Transactions Stock dropdown menu. By adding the name and trade symbol in A15 and B15 it makes all the previous stocks vanish with an error saying “Error Array was not expanded because it would overwrite data in cells A15/B16.. How do i add more?

    Thanks!

    • Pat says:

      Looks like I figured it out, The Portfolio data sets the Reference Data, not the other way around. :)!

      • scott says:

        Hi Pat! Looks like you figured it out. If you have any more questions, please don’t hesitate to ask.

        I’m going to be trying to release some videos soon showing how to use the template as well.

        Scott

  31. Jonathan Roy says:

    Great spreadsheet! So happy to have found this. I’ve been entering my portfolio and transactions all morning. 🙂

    One snag I’m having is with my MORL, BDCL, and OXLCO (preferred shares). Yahoo has no Key Statistics for those tickers which is part of the problem. Yahoo on OXLCO (maybe all preferred shares?) doesn’t show dividend or yield info. Google Finance does show a yield %, just not a last dividend paid figure. I’m not sure if there are alternate data sources I could plug in to get those tickers working?

    Also on RDSB Yahoo uses RDS-B and Google uses RDS.B. I went ahead and changed the formula so it wouldn’t cause an issue on Shell. If anyone else has an issue with a stock like that in the future, just change the ReferenceData tab to either use the same source for both columns or manually enter the alternate ticker into the forumla for that stock.

    • Jonathan Roy says:

      I had a typo in my OXLCO ticker, price works fine. So it’s just a matter of bringing in a yield now. I’ll see if my spreadsheet and regex skills are up to the task.

      • scott says:

        If you have any suggestions on improvements to the formulas, let me know. The issue I was having as well was differences in the way Yahoo and Google look up ticker symbols. I tried to account for some of those errors in my (long) formulas but can’t account for every scenario. Thanks!

    • scott says:

      Thanks and great suggestions, Jonathan. What I do in my personal one is create a separate column where I manually update the quarterly dividends. That way I can make sure it is accurate (for the ones that pay annually or semi-annually) and I kind of like updating it for dividend increases.

      The one issue with the way the arrayformula works on the ReferenceData tab, is that if you later add a stock anywhere other than the very bottom, the manually edited rows will not get moved to reflect the added stock. Just something to keep in mind if the numbers seem off.

      • Jonathan Roy says:

        Great tip. I’ll be sure to only add new stocks to the bottom of the list each time on the portfolio page and resist the urge to alphabetize them. 😉

        • scott says:

          I’ve added stocks in the middle of the list. It just takes some minimal rearranging but can be easily done, especially given your knowledge of spreadsheets.

    • Jonathan Roy says:

      So for preferred shares, this is what I came up with. Works with OXLCO. Instead of using the dividend history to create an annual yield, and then dividing that by 4 for quarterly yield, I pull the annual yield directly from Google.

      Column F:

      =iferror(index(split(ImportXML(“http://finance.google.com/finance?q=” & B22, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”), “/”),1,2) / 100)

      Column E:

      =iferror(C22*F22/4)

      • scott says:

        Thanks, Jonathan. I’ll take a look later today.

        • Jonathan Roy says:

          I could have just made changes in column E and not even changed F. That’d have been smarter.

          Maybe you can make E conditional if the dividend history fetch fails, and if so, then fall back on using google’s raw yield. That could be a forumla that’d work in all cases with no special work needed by end users.

        • Jonathan Roy says:

          On further study, I don’t know why I thought you were pulling dividend history and making it an annual figure (useful for monthly ETN/ETF/etc where monthly dividends vary). It appears you’re just taking latest quarterly dividend. (I wonder if Google adds up most recent 3 months monthly dividends for that figure on monthly payers.)

          A simple change then might be to take the annual yield instead and divide it by 4? The annual yield appears on Google correctly for BDCD, MORL, and OXLCO, etc. Or use that as the first iferror() fallback before resorting to Yahoo.

          • scott says:

            That’s a good point. It would be easiest to just take the overall yield * the value of the owned shares to determine the expected dividends. It sure would make the calculations simpler since there would be no need to adjust things for stocks that pay annually or semi-annually. DIS and BBL, for example, warranted special attention due to not paying quarterly. Using the annual yield would fix that.

            The formula to grab the annual yield works well, for the most part. I’ve been having trouble with DE in Google Finance. It only works with NSYE:DE, which really messes things up!

          • scott says:

            This will greatly simply things. However, it will mean that the calculated dividends are slightly off.

            Using WFC, as an example:
            Last price: 54.32
            Annual dividend: 1.4
            Annual dividend yield: 1.4/54.32 = 2.57732%
            Google Finance gives a yield of 2.58%

            30 shares of WFC = $1629.60
            Dividend calculated using annual dividend amount: 30*1.4 = $42
            Dividend calculated using Google Finance annual dividend yield: $1629.60*0.0258 = $42.04

            The differences are due to the rounding errors noted above.

            Since this does make things much easier for the general user, I will update the investing template.

            The new formula will first grab the annual dividend yield from Google and then if it fails will revert to grabbing the annual dividend amount from Yahoo and calculating the annual yield from the current stock price. This seems to work for both US and Canadian exchanges and also for the DE situation discussed below.

            Thanks so much for your help, Jonathan!

          • Jonathan Roy says:

            My pleasure! It’s strange how the free sites don’t have better data on things like MORL or preferred shares, Fidelity’s site has the sort of detailed info you’d expect. Thankfully Google does have the annual yield and ultimately that’s all we need.

  32. Jonathan Roy says:

    Should Annual Dividend @ Purchase be entered as a dollar amount or a percentage?

    • scott says:

      Dollar amount. That field is currently unused for calculations, but my goal in future versions is to use that field to calculate the dividend’s CAGR

  33. Dave says:

    Hi,
    Do you guys use any specific sheets for stock research or valuation metrics beyond DCF or DDM ? I just downloaded one from AII (https://www.aaii.com/computerized-investing/article/stock-valuation-spreadsheet.touch). Is something like that helpful you think ? Or do you just calculate things on the fly without saving a standard spreadsheet of formulas?

    • scott says:

      Hey Dave,

      I just calculate those sorts of things on the fly but incorporating them into the watchlist section of the spreadsheet might be quite useful. I’ll see if I have time to get around to doing that. Thanks for the suggestion and the website!

  34. Jonathan Roy says:

    A tip to people who buy foreign stocks. Google Finance provides quotes in the native currency of the quote, which will confuse the portfolio figures some. So if you are showing more/less gain on your main page than you expect, that might be why. In my case it was CPG rocking the boat a bit as Google Finance gives me quotes in CAD instead of USD.

    • Jonathan Roy says:

      So what I can do for my personal use is multiply the stock quote by the conversion rate. I found some great info here:

      https://support.google.com/docs/table/25273?hl=en&rd=1

      and here:

      https://support.google.com/docs/answer/3093281

      • scott says:

        Thanks for the tips, Jonathan!

        • Jonathan Roy says:

          So as not to misled… here’s a weird thing. The web page gives it in CAD, but =GoogleFinance(“CPG”, “price”) gives it in USD. GoogleFinance(“CPG”, “currency”) says “USD” not “CAD”. Very odd.

          I wonder if the results of GoogleFinance() API calls in Google Spreadsheets are based on your own country, or always in USD.

          • scott says:

            You bring up a good point. I’m in the US and when I use the formula =GoogleFinance(“CPG”, “price”), I get the price in USD.

            When I go to the Google Finance website and start typing CPG, it shows that CPG is traded on TSE (first option in the drop down) and the NYSE. (Also, a different company by the name of Compass Group plc trades on LON.)

            To get it to preferentially choose the TSE quote, I type this =GoogleFinance(“TSE:CPG”,”price”). It appears that the GoogleFinance() API might default to US exchanges.

  35. Borja says:

    Hi.

    Thanks so much for this awsome sheet, the best I found after a lot of investigation.

    Are you planning to add a features for deal easily with a portfolio in different currencies? It would be great as many of us invest in 2 or 3 different currencies.

    Regards..

  36. Sensim says:

    Hi! Thanks so much for your great Dividend Portfolio Tracker! I would also want to track stocks in different currencies. The stocks I follow are mainly swedish (SEK) but also european stocks (EUR) and US stocks (USD). What changes would you suggest so your sheets work the best for me?

    • scott says:

      Hi Sensim,

      It gets more and more difficult to make a relatively simple spreadsheet that tracks multiple exchanges. I’m mostly using GoogleFinance data, which works best with US markets. I’ll have to see what I can do with regards to multiple currencies as well as exchanges.

  37. Sensim says:

    Hi again! What if I buy the same stock in two (or more) different Accounts? How do I input that?

    • scott says:

      Unfortunately, that gets a little tricky. The spreadsheet isn’t currently set up to handle that. You could create an entirely new Transactions sheet for your second account, but then you’d have to update all the formulas initially yourself. If you have multiple stocks owned in different accounts, it might just make more sense to forego the account distinction and have all the stocks listed as if in one account. Or, you could also make a copy of the spreadsheet and use that second spreadsheet for that other account.

      It’s not the best work-around, but it’s really the only way to do it right now. Sorry I can’t be more of a help.

      • Jonathan Roy says:

        I have 1 stock that is in my SEP-IRA and my Traditional IRA. What I did to track it separately is on the Portfolio page, I named one “Royal Dutch Shell” and one “RDS.B Traditional”. Since the transactions list is based on the stock name you enter and not the ticker, this has worked fine for me so far.

  38. Jim says:

    Thanks for sharing the investing spreadsheet to everyone. I just started using it and i found it very useful for dividend investor. I have unilever in my portfolio, the dividend yield on it is wrong for some reason. I wonder if its because its a international stock. Also i have Fidelity and i use DRIP with them, What is the best way to add DRIP to the transaction page?

    • scott says:

      Hi Jim, I’ll take a look at the Unilever dividend yield as well. What ticker symbol do you use? UL? I own them as well so hope that it is actually working. As far as DRIPS go, just mark the dividends received on the transaction sheet as Div and then immediately make a Buy entry on the next row down with partial shares purchased. The cash flow for both should be the same so the net change is zero. Check out my transaction page if you want to see how it works.

  39. Jim says:

    Yes, I use the ticker UL. The dividend yield should be 2.86 right now for UL but it show 3.86 on the portfolio page and referencedata page. Thanks for the tips on drips.

    • scott says:

      Hey Jim,

      Sorry for the late reply. I figured out the issue if you are referencing my portfolio page and ReferenceData page. If you look at the last few quarters of UL dividend amounts on the Nasdaq dividend page, you’ll see that the quarterly dividend amount is quite variable but that lately the overall annual dividend amount has been increasing (except for a 1% decline from 2011 to 2012). I was basing my yield calculation on a quarterly dividend of 0.36, which was back in 11/2014. I just revised the ReferenceData number to 0.3282, dropping the yield to 2.92%. This matches what Yahoo Finance gives.

      If you don’t want manually update the yield, you can just use the formula for Annual Dividend Yield, which is available on my Investing Formulas for Google Spreadsheets page. The Yahoo Finance version seems to work the best. The Google Finance version produces the correct results for most stocks (but for UL it only brings up the quarterly yield, necessitating a multiplication by 4 to give the annual yield).

  40. Laurel C says:

    Hi. I just found this and am adding my portfolio. The Dividend calendar does not update for me. How do I get it to populate with the months colored and estimates to populate?

    Laurel C

    • scott says:

      Hi Laurel, the dividend calendar estimates is something that I have to manually update. That sheet is more for reference to see how much dividends will be coming in each month. The data on that sheet isn’t really used elsewhere to calculate anything. The dividend pay dates, however, should update automatically. Let me know if that isn’t working for you.

      Automating it is something that I might do in the future, but it would take some work.

      Scott

      • Laurel C says:

        Thanks for the quick reply. And, thanks for sharing the tool. I am finding it very useful and educational. I notice that I seem to have some obscure stocks so had to change some of the formulas by adding nyse: in front of some of the stock symbols.

        Another question.. How can I retrieve the dividend frequency for a given stock?

        Laurel

      • Laurel C says:

        I have the dividend calendar updated for the dividend pay date but without knowing the dividend frequency for each stock, I can’t estimate the rest. I have searched the internet for a way to get the frequency, but can’t seem to find anything. Any help in getting the frequency would be greatly appreciated.

        Laurel

        • scott says:

          Hi Laurel,

          Sorry for the late reply! For most US stocks, I’ve found that the dividend frequency is every quarter. However, European and other exchanges typically pay bi-annually or even annually. A great site to check the dividend payout frequency is nasdaq.com. Click the “Dividend History” link on the left. For example, here’s GE’s dividend history: http://www.nasdaq.com/symbol/ge/dividend-history

          Scott

  41. jim says:

    Scott, thanks for answering all my questions. I’ve been using it for a few months now and its been great. I was just wondering if your going to release another update anytime soon?

    • scott says:

      Hey Jim,

      Glad it’s working out okay for you. The next update will be a spreadsheet for tracking options. I’ve been very busy with work but will try to release another dividend spreadsheet update sometime soon. Some people have been asking about tracking different currencies so I’ll try to look into that for the next version.

      Anything in particular you were hoping to see?

      Scott

      • Jim says:

        I would like to see a feature that when you add a dividend transaction into the transaction page, it will automatically transfer it to the dividend calender.

        • scott says:

          I’ll see what I can do. I personally like doing it manually since I use that to double check that the numbers match and that I didn’t make a typing mistake somewhere. I’ll see how I can automate it, however.

  42. jim says:

    Once again thanks for your promptly reply Scott. Also can you also add the payout ratio for the watchlist in the next update if possible.

  43. jim says:

    Scott,

    Thanks for updating it, ill check it out

  44. Adam says:

    I would like to be able to import into google spreadsheets the 1-year and 5-year dividend growth for a stock from Fidelity’s website. It’s listed under the “Dividend Analytics” section on the dividends page, for example, here: https://eresearch.fidelity.com/eresearch/evaluate/fundamentals/dividends.jhtml?symbols=xom.

    I tried ImportXML and I found the XPath using a “Find XPath” Firefox plugin, but the result is “#N/A: Imported content is empty”.

    Does anyone know what formula to use?

    Thanks.

    • scott says:

      Hey Adam,

      I’m having trouble as well. I think the issue is that Fidelity is doing some extra javascript processing that is messing things up. See here. If you take a look at the page source code (before any javascript processing), I can’t find any of the numerical data or their headings on the original page source. I searched the source code for “Annualized Dividend” and it found nothing. It appears that the tables we need do not exist in the page source and are instead dynamically created. If you follow the link on the post above, someone does go into how to extract data from these dynamically created elements.

      I don’t have time right now to play with that but will try to in the future. Let me know if you have any luck as well!

      Scott

      • Adam says:

        Scott,

        Unfortunately, I’m not a programmer or anything and have no training in computer languages, so I don’t have a clue how to write these codes/commands, and when I read about how to do it I don’t understand it. So I was really just hoping that someone that does understand XML etc. would be able to figure it out and provide me with the code that I can paste into Google Sheets.

        The codes I’ve got in my spreadsheet so far I just copied off of other websites such as this one. And the googlefinance formula in google sheets is pretty simple to use by laypeople like me and it’s explained well in the help section. I’m actually somewhat surprised I couldn’t find someone online that had already developed/figured out a formula to use to get the dividend growth numbers from Fidelity’s website. They have annual dividend info from google and yahoo, but not dividend growth, which I find most easily through Fidelity.

        Anyway, if you’re able to figure it out, please let me know as I’m very interested in having it. It would make my stock analysis research so much easier, because right now I have to copy that info for each and every stock in my watchlist, which takes forever, and it would be so nice to have it automatically update in my spreadsheet.

        Thanks!

        Adam

        • scott says:

          Hey Adam,

          Extracting the data from Fidelity will be very hard since it is dynamically created on the fly.

          However, If you’ll look at the current watchlist, you’ll see a column called 5-year Dividend CAGR. That is the 5-year dividend growth rate from Gurufocus. You’ll see how I used the index formula to extract the cell from row 2 and column 3, corresponding to the 5-year value. If you want to get the one year value, just choose row 2, column 1; likewise for the 10-year value, choose row 2, column 4. That formula is already available on the watchlist and would just involve you creating a new column and changing the necessary column values for the index formula. Here’s the direct link to Gurufocus to see what data is available on that site: http://www.gurufocus.com/dividend/XOM

          If you want to use Reuters data instead, the importhtml formula can also be used. Each of these sites has slightly different values, but similar enough that it shouldn’t pose too big a problem.

          Here’s the code to import the 1 and 5-year dividend growth from Reuters:
          1-year: =index(importhtml(“http://www.reuters.com/finance/stocks/financialHighlights?symbol=xom”, “table”, 9), 4, 2)
          5-year: =index(importhtml(“http://www.reuters.com/finance/stocks/financialHighlights?symbol=xom”, “table”, 9), 4, 4)

          • Adam says:

            Scott,

            Thanks for the formulas. The info on gurufocus is a little more consolidated than Reuters, which I like, and the values between the two sites are pretty similar.

            I am used to looking at the values on Fidelity’s dividend page, and I see that their 1-year and 5-year growth rates are quite different from those on gurufocus and Reuters. For example, for PG, Fidelity lists 1 year growth rate as 3% vs 5.7% or 5.9% on the other two sites, and 5 year growth rate as 6.59% vs 7.5% on the other two sites. I could understand a few decimals of a difference, but that’s a full 1% difference. (The 1 year growth rate seems to be the most different between Reuters/gurufocus and Fidelity. For XOM, Fidelity lists it as 5.8% whereas the other two sites list it as 8.5%-9.7%.)

            I know this is because they get their numbers from different sources or they are updated at different times, but I wouldn’t expect Reuters and gurufocus to have such different numbers from Fidelity. Is that because Fidelity is not using a CAGR value whereas the other two sites are? Is it a different type of calculation that Fidelity is doing? I looked up how Fidelity calculates their 5-year dividend growth and they say “5 Years is calculated by taking the absolute value of the Annual Dividend for Twelve Trailing Months divided by the absolute value of Indicated Annual Dividend adjusted or non adjusted if not available, then multiplied by the Split Factor. The result is taken to the 1/5 power, subtract 1, and multiplied by 100.” Then I looked up what “Indicated annual dividend” means, and according to Investopedia, it seems like it’s equal to the current annual dividend. But if you plug that into their formula, I think you’d get 0. I guess I don’t fully understand how these numbers are calculated on either website, and I don’t know which numbers would be more meaningful to me, gurufocus’s, or Fidelity’s. CAGR isn’t a true return rate, it’s an imaginary number, whereas Fidelity claims their growth rate numbers are “a fundamental measure of a company’s real growth rate.” So I’m pretty confused as to which numbers I should use. Most of what I find online is the CAGR, though.

            I also don’t get how gurufocus/reuters get their 1-year growth rate. For example, for XOM, the last dividend was $0.73 on 9/10/15. The dividend paid on 9/10/14 was $0.69. That’s an increase of 5.8%, which is the value that Fidelity shows, and is what you’d get using the CAGR formula for 1 year. However, gurufocus/Reuters list the 1-year dividend growth rate as 8.5%.

            What I’m getting at with all of this is if I use Fidelity’s dividend growth numbers, I might come to a different conclusion on whether to buy a stock than if I were to use the numbers from Reuters/gurufocus. Right now, it’d be easiest to use the CAGR because I have a formula for it and thus can get that to automatically update in my spreadsheet. I guess since you use gurufocus, I’ll use that too.

            (btw, I copy/pasted your formula for the 1 and 5-year dividend growth from Reuters into my googlesheets spreadsheet and I got a “formula parse error”.)

          • scott says:

            Adam,

            I’m not sure how the sites calculate their growth rates. It does seem strange that they are so far off. It probably has something to do with TTM (trailing-twelve-month) values vs current vs expected, though I’m not entirely sure. There’s a Seeking Alpha article here that may clarify some of these differences.

            As far as the Reuters formulas, go through and change all the quotation marks to be straight. When I copied my formulas over from Google Sheets it displayed them in my comment with slanted (or curly) quotation marks. Google Sheets has problems with the slanted variety and needs them all to be straight. I had tons of trouble with this before I figured it out.

            Scott

          • Adam says:

            Thanks for the explanation.

            I think I prefer the 1 year dividend growth rates on Fidelity’s site to those on gurufocus, so I took the formula you had for 5-yr growth rate using gurufocus data and modified it to grab some values from the historical dividends table for the same webpage. I basically just calculated the TTM 1-yr growth rate myself using the formula ((last dividend paid-dividend paid 12 months ago)/dividend paid 12 months ago)*100. That’s what the CAGR formula simplifies to if the time period is 1 year. And then I just substituted the appropriate importhtml formula for each of the dividend variables. So for AXP, for example, it’s (($0.29-$0.26)/$0.26)*100 = 11.54%. This number matches Fidelity’s.

            The formula was =(((INDEX(importhtml(“http://www.gurufocus.com/dividend/”&A3&””, “table”, 5), 2, 1))-(INDEX(importhtml(“http://www.gurufocus.com/dividend/”&A3&””, “table”, 5), 6, 1)))/(INDEX(importhtml(“http://www.gurufocus.com/dividend/”&A3&””, “table”, 5), 6, 1)))*100
            where “A3” is the cell in which I have the stock ticker.

            (Gurufocus shows 92.8% one-year growth which is very misleading. It may be because AXPdidn’t pay a dividend last quarter so that value is skewed. It’s an exception, but it illustrates why I like Fidelity’s 1-year growth value).

            I think you are right about what the growth rates have to do with. I know Fidelity uses TTM which I like and understand (since I have no problem calculating myself if I wish to verify their number)

            So perhaps Fidelity uses all linear growth numbers whereas gurufocus and Reuters use exponential. I think the seeking alpha article recommended exponential growth for all stocks besides MLPs, REITs, and other higher-yielding equities, but he makes his own chart that’s a little different than the numbers posted on the finance sites. And I’m certainly not going through the trouble of making a table to calculate the growth for each and every stock.
            Right now I guess I’m using linear 1-year growth for all my stocks and exponential (CAGR) 5-year growth for all my stocks.

            (After reading about all the different ways people go about calculating this or that metric, I always wonder if I’m using the “best” or “most robust” formula for calculating various metrics. I don’t think I’ll ever know.)

  45. This is my first visit to your website and I want to say that it’s great. I stumbled here researching some spreadsheet coding and wanted to comment on Adam’s Fidelity questions. I’m familiar with Fidelity and have worked with a few of their representatives and advisors over the years. In a nutshell the 1 yr growth you see is calculated exactly as the formula you are using – it’s literally the percentage increase from the same quarter year over year. The 5 yr description is somewhat “legalese” or another way for them to say “hey it may vary from time to time” hence the use of the word “indicated” also to disclaim liability for inaccuracies from where they get their data. Before I ramble the 5 year growth will be very close to 5 yr CAGR same quarter year over year from most recent announcement. Often it will be exact if you calculate it yourself, other times it will vary slightly depending on how Fidelity is pulling information. Best, Devin
    DividendChimp recently posted…Sortable LIVE WATCH LISTMy Profile

    • scott says:

      Awesome comment, Devin! Thanks for clarifying the questions about Fidelity.

      • Hi Scott,

        Do you know of a formula to pull next EPS report date from anywhere?

        I would really like to have it on my options spreadsheet as it’s such an important factor in selecting expiration dates. Zack’ seems to be the best as it at least gives an approximate date where yahoo only seems to list the date if it’s actually been announced and often just says “N/A”. I see you are into options so I thought you might pull that info from Guru or somewhere.

        Thanks,
        Devin
        DividendChimp recently posted…NEW FEATURESMy Profile

    • Adam says:

      Thanks, Devin. I decided it was easiest for me to use 5-year CAGR for my spreadsheet as I could easily extract it from a few websites and therefore easily get it to update automatically in google sheets since others have posted the appropriate formula for google sheets. So it’s nice to know that Fidelity’s 5-year growth will be very close to the 5 year CAGR.

  46. You’re welcome, we want to be as precise as possible. As long as we are using information that is consistent in the way it’s calculated we can compare the relativity of that information.
    DividendChimp recently posted…Sortable LIVE WATCH LISTMy Profile

  47. scott says:

    Hello everyone,

    I just wanted to let you know that I just released an international version of the dividend portfolio spreadsheet with support for multiple currencies. International Dividend Portfolio Tracker.

    Please direct any questions/comments regarding it to its dedicated page. Thanks!!

  48. great spread sheet… i am currently using it for my blog portfolio page.. ur the best…
    i have a question tho.. lets say i sold a position in my portfolio… do i delete the row after putting the data in the transaction page?? let me know thanks!!
    Dividend Growth Bunny recently posted…Why Start Investing Early!My Profile

    • scott says:

      Hi Dividend Growth Bunny,

      If you sold a position completely then, yes, just delete that row on the Portfolio sheet. However, in doing so you’ll lose the total dividends received and the realized gain/loss for that position. When I sold MCD a few months ago I didn’t like to have an empty position on my Portfolio sheet but didn’t want to lose track of the dividends that I had received from MCD.

      What I ended up doing was creating a “Closed Position” sheet that kept that information for me. I then also added a new row on the Summary sheet called Closed, which tracks the dividends received from any closed positions.

      You can see the Closed Positions sheet by click the tab at the bottom right of my portfolio spreadsheet. (You may have to click on the arrow at the bottom right to scroll all the way over.)

      I will shortly update the dividend tracker template to include this functionality.

      Let me know if you have any other questions.

      Scott

  49. Don From Canonsburg says:

    Hello,
    I like your template. I’m wondering if the cumulative transaction formula on the transactions page is missing the addition of shares added through dividend reinvestment? I’m tempted to edit the formula but thought I’d ask.
    Thanks for providing the template.

    • scott says:

      Hi Don,

      Thanks for commenting. The formula does correctly account for dividend reinvestment. You just need to make sure you enter the dividends twice. By this I mean you first account for the dividend by choosing the type as “Div” and then enter a subsequent Buy order for the partial shares. (The cash flows should match.) This is how the dividends show up in your account and allows them to correctly change the cost basis. It also allows you to receive dividends as cash (if you don’t want to reinvest) and still adds up the dividends received from each company.

      Let me know if you have any more questions.

      Thanks,

      Scott

      • Don From Canonsburg says:

        Scott,
        I managed to copy ‘div’ transactions and paste, then edit them to ‘buy’ transactions.
        My transaction sheet now contains ‘div’ rows and ‘buy’ rows, both derived from dividends.

        Should the ‘div’ row contain share price and number of shares transacted or is it a moot point since the cumulative cell does not update on ‘div’ type transactions?

        The ‘buy’ row (derived from dividend reinvestment) contains share price and number of shares transacted. Therefore, the cumulative cell reflects added shares.

        Thanks.

        • scott says:

          The div column could contain shares and price; the final “cost” just needs to come out to the dividend received. I find it easiest to just enter 1 as transacted shares and then the dividend amount in the “transacted price/share” column, but either way works.

  50. Dave says:

    In a taxable account if you collect cash dividends from multiple companies (123 & ABC & XYZ) and selectively reinvest those cash dividends into only XYZ, how does that affect the cost basis on XYZ? Are the dividends issued from XYZ always supposed to increase the cost basis in XYZ? Isn’t cash just cash?

    • scott says:

      Hi Dave,

      You’re right that cash dividends don’t affect the cost basis until they are used to buy more shares. If the pooled dividends from multiple stocks are used to purchase XYZ, then only XYZ’s cost basis will be affected. If the dividends end up purchasing the stock at a lower price than what the portfolio holds it at, then the cost basis would actually decrease.

      The template is set up so that dividends are first received as cash and then reinvested if desired. They have to be entered twice, essentially, because of the cost basis issue you discussed above.

      Good comment!

  51. Don From Canonsburg says:

    Maybe you can help with this problem Scott.
    Cell D17, ReferenceData sheet, (KMP is the issue), has a value of -36,343,126,505,556,300.00, obviously not correct.
    I follow the formula back to Portfolio sheet, E17=0, H17=$169.47
    For some reason, ReferenceData D17’s formula does not return 0, based on Portfolio E17 value of 0.
    I realize Portfolio E17 has an underlying formula that could be the cause but I don’t understand the formula.
    Regarding my Transactions entries for KMP, as we wrote on Nov 19 & 20, my KMP dividend income entries are separated from KMP div reinvestment purchases by rows of transactions for other issues. However, this is the only issue where a problem occurs on the ReferenceData sheet. I think it’s because it’s the only issue I’ve “sold” and has 0 shares.
    I also cannot figure out why Portfolio shows a cost basis of $169.47 or an unrealized loss of ($169.47) for KMP other than to think the formula at Portfolio H17 is looking at the wrong row in Transactions due to the separation of KMP entries.

    • scott says:

      Hi Don,

      I emailed you directly yesterday. If you want to converse here we can as well, but getting a little more details about your KMP holding is needed. Details are in the email.

      Thanks,

      Scott

  52. Don From Canonsburg says:

    To anyone following my problem, here’s an abbreviated update re the solution.

    I began using Scotts template a couple of weeks ago and since then, I’ve been entering a few years worth of data.

    I owned KMP and reinvested dividends over a couple of years. Last year, KMP became KMI through means that are not important here, so, I entered a sell transaction, taking my position to zero.

    Later, on the Transactions page, I entered share purchase transactions (through div reinvestment) out of chronological order i.e. after I entered the sale transaction.

    Therefore the sale entry taking my position in KMP to zero was entered on a lower-numbered row than my reinvested dividend buys. This caused havoc with some calculations, particularly Cost per Share on ReferenceData, Cost Basis and Unrealized Gain/Loss on Portfolio.

    The solution was to delete the Sell row in Transactions and enter the same Sell data in the next empty row at the bottom of Transactions (below the reinvested dividend purchases). Transactions are now in chronological order and all calcs appear to be correct.

    Thanks again Scott, for your assistance and for providing the template. I think it’s great.
    Don

    • scott says:

      You’re welcome, Don, and thanks for the update. I’m sure others are having similar problems and this info is very helpful in keeping the template working correctly.

  53. James Stech says:

    Scott,

    I have the same position in my brokerage account as well as in my IRA but on the “portfolio” page, it shows the amount of shares in each account as being 292. I own that in total not in each account separately. Is there a way around this so it accounts for the same holding in both accounts to show up correct?

    • scott says:

      Good question. I ran into that same issue with my account. I own Chevon in both my Roth and brokerage. I listed my taxable as “Chevon (taxable)” and Roth as “Chevon (Roth).” You can overwrite the auto populated company lookups. Then, under Transactions, just pick the correct one under the drop down menu. Let me know if you have any other questions.

      • James Stech says:

        I appreciate the quick response. Right after I posted the question I fooled around and did exactly what you said. – taxable and -Roth behind the same holding on the “portfolio” page.

        Thanks,

        Jimmy

      • James Stech says:

        Scott,

        One thing I would love to see which wouldn’t be too hard is a comparison to some index. For example, using Google Finance Spreadsheet it compares the total return of a portfolio to the S&P 500 or any index you want on a graph over the selected time frame.

        What’s the chances of adding something like that to your spreadsheet?

        • scott says:

          That’s a great idea. It might be hard to retrieve old data from prior transactions, but I might be able to make it work with new SPY data. I’ll work on it soon and let you know.

  54. Laurence says:

    Hi all,
    I’m a newbie and need to do this. How can I get the Sheet to show SINGLE SHARE PRICES-both cost basis, and current price. Then do the multiplication automatically? (I want to see e.g. what KO is selling for, what I paid for it first and foremost per share ))

    • scott says:

      Once you have the transactions listed on the Transactions sheet, the per share cost basis information is shown on the ReferenceData sheet.

      • Laurence says:

        Hi Scott:
        I just entered 40+ transactions into the transaction page. When I now open the portfolio, nothing has migrated over? What to do?

        Laurence

        • scott says:

          Could you email me directly via the Contact Us link at the top left of the blog? I need some more information in order to help.

          Thanks,
          Scott

      • Laurence says:

        i NEED SOME DUMMY HELP WITH THIS. I started entering portfolio into a blank spreadsheet copy. The first 4 entries all autofilled properly. Then auto fill stopped. The data reference page likewise stopped migrating cost basis, and market value. Some of the info, like dividend dates appear, but not all. Something’s amiss.

        • scott says:

          Laurence,

          First, you do realize that ALL CAPS is shouting for Internet comments? Right? I had to work today (in the hospital). I’m not sitting around checking my blog every second and just now got some free moments to respond to your first comment. I am doing this for free and don’t mind helping…

          If you’re completely new with spreadsheets (including Excel), maybe this Google Sheets template isn’t for you because it does require basic maintenance and minimal knowledge of spreadsheets.

          I’ll do my best to help answer your question in my next comment.

          Scott

        • scott says:

          Hey Laurence,
          Still having trouble? I’m less tired and cranky now. Had been a busy day at work before. Haha. Sorry for coming across as so mean!

          I have found that Google Sheets takes awhile to fill in some of the data, particularly those that are grabbed from websites.

          Also, if you’re having trouble with formulas autofilling, make sure that you’re only entering data into the “orange-ish” colored cells. The light green contains formulas that shouldn’t be messed with unless you know what you’re doing. If the cells containing formulas have been edited, then just drag them down from the cells that are working, as Richard said worked for him below. Any more help please contact me on the “Contact Us” button at the top of the blog.

          Thanks,
          Scott

  55. Richard says:

    Guys,

    The Dividend Portfolio Tracker with Transaction Page is sweet. Thanks.

    I just ran into an issue where the Portfolio Sheet is not showing the correct number of shares from the Transaction Sheet.

    Any thoughts?

    • Don From Canonsburg says:

      Does the transactions sheet display the correct number of shares?

      • Richard says:

        Happy New Year Scott and Don,

        The transaction sheet does show the correct previous number of shares and correctly shows the cumulative shares.

        I just dragged the shares formula up from a blank cell on the Portfolio sheet and everything was corrected.

        Cheers
        Richard recently posted…Hello world!My Profile

        • scott says:

          Happy New Year to you too, Richard!

          Yeah, dragging the formulas down from cells that work is a great troubleshooting technique that I use all the time!

          Scott

    • scott says:

      Hi Richard,

      You’re welcome!

      I’d check what Don wrote (thanks, Don!). Is it showing correctly on the Transactions sheet? Also, is it a specific stock that runs into problems or every stock on your Portfolio page?

  56. Richard says:

    Hey Guys,

    How do I enter a reverse split on the transaction page?

    • scott says:

      Hi Richard,

      For a reverse split where every 2 shares becomes 1, you’d enter 0.5. Every 3 shares, enter 0.3333, etc.

      All you need to enter is the date, the company name and then the split. Hope this helps!

      Scott

  57. Eric Martin says:

    Is the new template still available? I click on the links above and it doesn’t seem to pulling this up just a blank google templates page. I am new to google sheets etc so it could be a user error with it.
    Either way I can’t get to the template. Can someone please email it to me?

    Thanks for any help.

    • scott says:

      Hi Eric,
      Yep, the spreadsheets are still available. You can access them all at the link to my template gallery on Google Drive, located here: https://drive.google.com/templates?view=public&authorId=12641558499014907742&ddrp=1#
      You’ll need to access it on an non-mobile device. Let me know if you’re still having trouble.

      Scott

      • Eric Martin says:

        Thanks Scott and there lies the problem. I have been trying to access it from my iPad becuase my work PC doesnt have the latest IE installed and nothing in Google+ seems to work.

        As I said I am new to Google products so why would a mobile device not work? Is it a google thing?

        Thanks again. I’ll email the link to myself and see if that works.

  58. Eric Martin says:

    Well that did work and I am able to see the templates now and can open them. Problem now is nothing is editable and I can’t save it. The menu bar is all grayed out. This I would guess is related to the unsupported browser.

    Thanks again I guess I will be waiting till I get home.

    • scott says:

      Hi Eric,

      It used to work on mobile devices, but seems to be having issues now. It seems to be a Google issue at this point. You can also access the spreadsheet once you saved it via the Google Sheets app on your iPad.

      Are you logged into your Google account when viewing the list of templates? Did you click on the “Use this template” button? If you’re seeing a non-editable template, click on File and then Save a Copy to get a version that you can edit.

      Scott

      • Eric says:

        Hi Scott, I did not try on my iPad but at home on PC it is working like a charm.

        I do have a question about accounts. Right now I manage my wife and my ROTH IRAs. Dividend investing I am new at and what I have done so far is trade the same stocks in both accounts. This is causing my total shares to appear doubled on the other pages. Is there a solution to this? Other than rewriting the formulas or having a separate sheet for each account I am buy same stock.

        Thanks again and look forward to using it.

        • scott says:

          What I do is just manually edit the company name. Use the same ticker symbol for example, GE, but for Roth the company name is General Electric (Roth) and taxable is General Electric (taxable). Then just pick the appropriate one on the transaction sheet.

          • Eric says:

            Thanks I’ll try that.

            What about stocks with same ticker on different markets? EDF specifically pulls from EPA exchange not NYSE. Causing issue with pricing but strangely enough not with the name it pulls in.
            I tried it on Google Finance and by default it pulls the one I didn’t buy.

            Thanks again

          • scott says:

            I’ll have to look into that. I’ve found you can force the exchange by doing NYSE:GE as ticker. Might work the same for EDF.

  59. Juanita says:

    How do you “save” the spread sheets? Thanks in advance.

    • scott says:

      Hi Juanita,

      To get a copy for yourself, click the “Use this template” button. If you can see the spreadsheet but not edit, then click on File (at the top left of the spreadsheet) and then “Save a Copy.”

      If you are editing the spreadsheet, it will automatically save anytime you make a change or you can force a save via the File menu as well. (This is not the File menu associated with your web browser but rather with the Google Sheets template itself.)

      Let me know if this addresses your question.

      Scott

  60. Jacob says:

    Great sheet!
    Until recently I’ve been tracking everything in my portfolio manually, which is a headache and bound to have some inaccuracies, so this is a pleasure to work with.
    I’m attempting to port over my portfolio’s transaction history from 2007 to today, but that includes stocks that have delisted or change ticker symbols.
    Do you know if there is anyway to make reference to the historic symbol/price associated with a date using the Google finance API? What do you suggest as a workaround to get these symbols to work in your sheet?

    • scott says:

      Hi Jacob,
      I only how to import historic values through Yahoo’s API. However, that may not be needed. I’d try copying over the old transaction history using the new symbols or whatever it was before it was delisted. You could do all the transactions or a lump calculation for the cost basis from 2007 until today, if you’d want. You’d lose the yearly tracking of capital gains/losses and dividends doing it like that. The stock price info is updated on the ReferenceData page, which can also be manually updated.

      At work now so can’t write much more but will be able to look into this more tonight.

      Scott

  61. wayne j says:

    Hello Scott and Johnny. I’ve run into a problem with mutual funds and some etf’s. On the Reference tab it won’t find the dividend yield, ex-div date or div pay date. This causes an issue on the portfolio tab with annual dividend, div yield and yoc. Any thoughts? Do you have a paypal account? Another issue I’ve run across is on the Reference tab, the last price acts up. It will show the current price for a second or two and then changes it to a big number i.e. Dow is 44 and it changes it to 16151. I’ll wait to see if it corrects in the next day or two but on the other hand, I should cash out the million plus dollars and run. While words just don’t seem to say enough, Thanx for all your work on this.

    • scott says:

      Hi Wayne,
      You’re welcome! I’d cash out too with that amount! Haha.

      Could you email me directly via the Contact Us link at the top left? I’ll try to help you with those errors.
      Scott

  62. Cathy says:

    Hi Johnny and Scott,

    Do you have any formula’s to get the consecutive years of dividend growth in a google sheet?

    Cathy

    • scott says:

      Hi Cathy! A great resource for that is available here by Dave Fish: http://www.dripinvesting.org/tools/tools.asp

      There’s an Excel spreadsheet version available, which can be imported into Google Sheets. That would be one way to do it. I’m sure there’s other websites that could be used to extract the data from as well, but Dave’s resource is great for exactly what you’re looking for!

      Scott

      • Cathy says:

        Hi Scot,

        Thank you for your reply.

        I already use the the spreadsheet by Dave Fish but I had hoped that somebody might know a formula to pull the consecutive years of growth from a site like morningstar, yahoo or finviz, rather then manually copying and pasting it.

        Any suggestions are welcome.

        Cathy

        • scott says:

          I’ll look into it. Thanks, Cathy!

        • scott says:

          Hi Cathy,

          I’m having trouble finding any websites that do as good a job of grabbing that data as Dave Fish’s spreadsheet. Could you send me a link to any websites that you can find that information?

          I may be able to construct something where you copy the latest Excel spreadsheet version of Dave’s spreadsheet and then Google Sheets would grab data from that. I’ll have to look into it.

          Scott

          • Cathy says:

            Hi Scott,

            Thank you for your help.

            I managed to hack something together, please find my solution below for your reference. 😃

            =SUBSTITUTE(importxml(“http://www.dividendinvestor.com/dividendhistory.php?symbol=” & $A$1, “//tr[td/text() = ‘Consecutive Div. Increases:’]/td[2]”),”years”,””)

            Cathy

          • scott says:

            Thanks, Cathy! I’ll include it on the next version of the watchlist. I’ve visited that site before but forgot that it included a section regarding consecutive years of dividend increases.

            Thanks for sharing it!!

  63. scott says:

    @John, The latest version of the spreadsheet automates the Dividend Calendar.

  64. Harish says:

    Thanks so much Cathy for your hack…but unfortunately it doesn’t work for me….Can you please share your spreadsheet?

    Scott, great work! Please let know when you update your spreadsheet with Consecutive Dividend increases. Awesome job so far!

    • scott says:

      Hi Harish,

      You’re welcome! The Consecutive Dividend Increases is now included in the Watchlist section.

      Cathy’s formula works, you just have to change the quotation marks. Google Sheets only works with straight single and double quotes so you’ll have to change those manually if you copy and paste it from her comment. (That or just grab the latest version of the spreadsheet, where I made that change already!)

      Scott

  65. Ryan says:

    Hi Scott,

    I have the same company but in difference portfolios. Is there a method to separate them on the portfolio page so they update. Currently the number of shares is being added and throwing off the calculations.

    Thanks
    Ryan

  66. John says:

    Hi Scott,

    First of all let me tell you this spreadsheet is incredible. Thank you very much for posting it to the public and allowing us to also benefit from your hard work. I had a quick question on how you prefer to properly account for dividends in a DRIP account. All my dividends are invested directly back into the stock. What I believe you instructed was to create a dividend entry on the transaction page, then create a buy entry for the same shares but without having any information in the transacted share price to show that it did not cost anything. Any light you could shed on this would be very helpful.

    Once again, thank you very much for your charts,

    John

    • wayne says:

      Hi John! If I understand it right……

      1. Enter a transaction for the dividend on the “Transactions” tab
      a. For the transacted shares enter 1. It won’t change the shares held as its a dividend “type”
      entry and the 1 is just a place holder.

      Date 2-23-16
      Type DIV
      Stock ABC
      Shares Transacted 1
      Shares $/per share $ ( dividend amount)

      2. Enter a buy transaction on the “Transactions” tab. This will reflect the re-investment.
      a. Shares will be the number of shares your dividends purchased
      b. The share cost should be listed on your re-investment transaction
      i.e. bought .317 shares of ABC at $48.95 share

      Date 2-22-16
      Type Buy
      Stock ABC
      Shares Transacted X (number of shares bought)
      Transacted $/per share $ (the cost per share when the dividend was reinvested)

    • scott says:

      Hi John,

      Thanks for the comment. You’re very close to properly accounting for the dividends. You just need to enter in the transacted share price. Here’s an example: Let’s say I receive a dividend of $5 for a stock trading at $100. If this is being “DRIPed” I would create two transactions. I find it easiest to create the first as type “Div” and 1 share at a price of $5. Then you need to create a second transaction, this one being a “Buy” with the # of shares and the share price, 0.05 shares at a price of $100/share. While the dividends seem like free money, reinvesting them is the same as taking cash from elsewhere and using that to buy shares in the company. It does affect your cost basis. (You can leave the fee section blank for both because this aspect is free.) The transaction price info will be available on your brokerage account.

      It makes a little more sense to think about what you’d do in the future if you start receiving $1000s in dividends a month. You may want to turn off automatic reinvestment and selectively invest that cash in a different stock or even withdraw it to spend on something else.

      Hope this answers your question. Let me know if there’s anything else I can help with.

      Scott

      • John says:

        Scott and Wayne,

        Thank you very much for your informative responses, they were very helpful and it all makes perfect sense now. Very true about the cash, I see why I would want toe cost basis to reflect that. I just began my DRIP fund about 6 months ago andI cant tell you how grateful I am that i stumbled across your doc. Very helpful and I look forward to following the updates!

        Best,

        John

  67. Andre says:

    hi,
    thanks for creating this and answering questions. after a bit of trail and error ( i have no experience with spreadsheets), i got this to reflect my portfolio holdings. but i noticed that no sooner did i have it set up, the spreadsheet became extremely buggy and crashed often, requiring reload, and seldom ever does all of the called data from google finance import correctly into RefData to populate all the fields and do the calculations. the pages essentially just hang until i get an error message to reload. i’ve tried re-downloading the template and re-filling it out. is anyone else experience this? i run it on a Mac, Windows and mobile all to the same effect. I can’t figure out the cause.

    • Andre says:

      I tried to view Scott’s portfolio on this site, and notice that it links to an embedded copy of a modified version of this tempplate. on his RefData page, although he seems to have modified it for extra fields, I also see “error” fields that read merely, “REF!#” under Dividend Date. my own attempt to work with the template is giving me similar results, but in many more fields. is this a problem with Google Finance or Google Sheets? or is there something i’m not understanding.
      Thanks.

      • Jeff says:

        Andre,

        I’m having the same problem. I think it might be a Google problem because I was using the spreadsheet for about a month without any issues.

      • scott says:

        Hi Jeff and Andre,

        I’m just getting to work now but will take a look tonight. The spreadsheet was working fine for me a few days ago and I didn’t do any updates to it in the meantime.

        It sounds like an issue on Google’s end, especially since it appears to be affecting many people.

        Hopefully they’ll fix it soon.

        I’ll post an update tonight after I look at my version.

        Scott

  68. Don says:

    I really love this template. Thanks for the ton of work you put into it. I have been experiencing significant loading and saving time and then, most often, a crash. This pattern means I can only enter data into one cell in the transaction tab before the crash. And I only have 12 stocks and 20 transactions currently loaded. I’ve read through many of the comments above to see if anyone else has a similar experience but could not locate someone with a similar problem. And advice is MUCH appreciated.

    • scott says:

      Hi Don,

      It looks like it is a Google issue. You’re the 4th person in that last hour or two that has described similar problems.

      I’ll take a look when I get back from work tonight.

      Scott

  69. hent00 says:

    Hi,

    I have been using your spreadsheet for over a year now and really enjoy it. However, over the last week or so, some of the data imports stopped working. It looks like other people have been having similar issues.

    I figured out a way around this by changing the Yahoo Finance URL:
    http://finance.yahoo.com… CHANGE TO THIS … http://download.finance.yahoo.com

    I also stopped using the GoogleFinance calls on the ReferenceData sheet:
    For price I’m now using this:
    =iferror(ImportData(ʺhttp://download.finance.yahoo.com/d/quotes.csv?s=ʺ&B4&ʺ&f=pʺ))

    For dividend yield I’m now using this:
    =iferror(ImportData(ʺhttp://download.finance.yahoo.com/d/quotes.csv?s=ʺ&B4&ʺ&f=yʺ)) / 100

    Hope this helps people.

    • scott says:

      Thanks so much for the updated formulas! I’ll make the changes later today.

    • Donald Lesniakowski says:

      Thanks for posting. I noticed import problems this morning and will try your fixes.

    • Don From Canonsburg says:

      Hi,
      For the reference data sheet dividend yield, this works,

      =if(isblank(B4),,iferror((index(split(ImportXML(“http://finance.google.com/finance?q=” & B4, “//td[@data-snapfield=’latest_dividend-dividend_yield’]/following-sibling::*”), “/”),1,2) / 100),ImportData(“http://download.finance.yahoo.com/d/quotes.csv?s=”&B4&”&f=d”)/C4))

      I think that all I did was add as per your instruction. However, on the watchlist sheet, I’ve substituted your dividend yield formula –

      =iferror(ImportData(ʺhttp://download.finance.yahoo.com/d/quotes.csv?s=ʺ&B4&ʺ&f=yʺ)) / 100

      and it results in a parse error. I’m not up to speed on the formulas.

      • scott says:

        I’m away from a computer that I can use to access this at the moment. Did you make sure the quotation marks are all straight quotes? Google Sheets can’t handle the angled ones.

        Scott

        • Don From Canonsburg says:

          Scott,
          I changed the quotes in the formula and the error cleared. Odd, because the quotes looked the same.
          Now I have a problem with the Graham Number calculation. Here’s the formula –

          =SQRT(22.5*((index (importhtml(“http://download.finance.yahoo.com/q/ks?s=”&B2&””, “table”, 20), 8, 2)))*((index (importhtml(“http://download.finance.yahoo.com/q/ks?s=”&B2&””, “table”, 22), 7, 2))))

          Here is the error. Function INDEX parameter 2 value is 8. Valid values are between 0 and 1 inclusive.

          Is this why the template has been running so slow for me over the last few days?

          Thanks for your help.

    • RBD says:

      I’ll add my thanks to this solution and the thread here. Two Investing, first time I’ve seen your spreadsheet. Learned a lot from it. Thanks all!
      -RBD
      RBD recently posted…5 Truths About Traveling The World In Your 20sMy Profile

  70. scott says:

    I have implemented a few of the formula changes that hent00 suggested above. It appears that some of the errors occurred because Yahoo Finance might have updated the link used to access their Finance API. Just append “download” without the quotes as hent00 noted above.

    Let me know if this fixes things. It seems to be back to normal for me.

    Scott

  71. JM says:

    I am totally lost!! I am not very familiar with google sheets but was able to use your older version which worked up until a few days ago – is there an easy way to do this without losing all of my data/transactions for the last 2 years!

  72. JM says:

    By the way, I am using the old version of your spreadsheet – I can’t figure a way to use the new version without starting all over and losing my transactions etc…

    • scott says:

      Which version are you using? Does it include a Transactions sheet? If it does, grab a copy of the latest version. Then copy your stock ticker symbols from the Portfolio page and copy your Transactions sheet transactions (everything in the orange on the right). Everything should just start working again. Let me know if you need more help.

      Scott

  73. JM says:

    what version do I download with the changes you made the other day? How do you copy the transaction sheet? Sorry I am so illiterate at this!!

    • Borja says:

      Download the lastest version (3.5.3). Simply select all the old transactions sheet (Ctrl + A), copy it (Ctrl + C) and paste it (Ctrl + v) in your new transactions sheet.

      For the Portfolio sheet you have to copy only the orange data (Sector, account an ticker columns) and do the same as before to move to the new version.

      P.S: Made a backup of your actual sheet before, just in case.

  74. JM says:

    How do you copy just the orange part? Thanks for your patience!

    • Borja says:

      Left-click with your mouse in A6 cell of Portfolio sheet, keep clicked and move the mouse selecting the data on A and B columns. Copy -> Paste in the new document.

      Repeat the same with the Ticker column.

  75. JM says:

    BTW, Where is version 3.5.3 – I only see version 3.5.2

  76. JM says:

    I did that and it still doesn’t work – my transactions are all copied but it has a red flag in the stock tab on the transaction sheet stating cell contents invalid

    • scott says:

      I’m at work now so I can’t help out too much, but if by tonight you don’t get it figured out, share a copy of it (or a portion of it) with me and I can look through it tonight.

      Scott

  77. JM says:

    If I copy each row on the transaction it works – is there an easier way? It doesn’t work if I copy the transaction page like Borja suggested

  78. JM says:

    Yes – tell me the easiest way to share my screen with you so I can do it later tonight

    Thanks

  79. mwolfod says:

    So now it appears, there are NO links to the current revision of this Spreadsheet Template?

    Am I missing something

    • scott says:

      Google is having issues. The template is still available. I’ll look into the direct link to the template after work. In the meantime, click the link near the top where it says “please visit my templates page at Google to see them all.”

      I listed many ways to access the template at that section.

    • scott says:

      My bad, I just checked the template page and my template was removed! I did not intentionally do that. I’ve been getting annoyed with Google’s changes lately!

  80. mwolfod says:

    Thanks Scott.

    So I’ll check again tonight and see if you have been able to put it back up??

    Thank you for your reply, and all your work.

    • scott says:

      Yep. I just fixed it so it should be up and running again. Thanks for the head’s up!

      • mwolfod says:

        Thanks.

        I can’t wait to start using this tool.

        I see you trade options.

        How about a similar, interactive/updating template incorporating some of the unique attributes of options?

        Come on, Doctor. I know you have time.

        Hahaha.

        • scott says:

          Haha! Dealing with this spreadsheet AND an interactive options spreadsheet might be a little more than I can chew. There are so many variations of options that it makes for a substantially harder implementation of any automation!

          Scott

  81. Tom says:

    Thanks for the info. My spreadsheets were failing with multiple errors. The above examples fixed my problems.

  82. JM says:

    Mine still do not work – I thought they started to work but now I am getting errors

  83. wayne says:

    Question for those smarter then I…….(which isn’t that hard to do)

    Scott is understandably busy so for the other formula people;

    1. It doesn’t like mutual fund – anyone have a formula modification that works for mutual funds? I.e. VICEX, PONDX, FDAGX etc.

    2. In the portfolio, at the top and bottom where it totals individual columns, how do I modify the formula so that it will exclude things that aren’t numbers? This way if a value is coming back NA, Value etc, it will still add the numbers to give a total.

    • scott says:

      Great suggestions, Wayne. I’ll look into this as soon as I can too!

      • wayne says:

        Hello all. I think I found a solution for #2. Have to change the A’s to O’s etc. It seems to match my numbers when I compare the formula to using a calculator Thanx to Otavio.

        =arrayformula(sum(if(isNumber(A1:A);A1:A;iferror(value(regexextract(A1:A;”\d+”));0))))

        Best regards,
        Otávio

  84. Tom says:

    I have just started using your Portfolio sheet and have run into a problem. I first filled out the Portfolio Sheet with 67 stocks. Everything looks good on this sheet. When I started entering data on the Transactions Sheet, the first 61 stocks worked great, but for the next six, the name of the company, column “C”, was not in the drop down list. Again, the name did appear on the Portfolio Sheet. The stocks that didn’t show up on the Transaction sheet, colum C , did show up on the Reference Sheet. Do you have any suggestions I can try?

    • Tom says:

      I solved the problem. Because of the number of stocks I had, I had added new lines. I had to redefine the range of names used on the Transaction Sheet. The range involved the company names on the ReferenceData sheet. Once the added company names were included in the range, the problem went away.

      • wayne says:

        I ran into the same problem and had to do the same thing. I’m glad I wasn’t the only one who thought there was a problem only to realize the settings needed to be adjusted. Thanx for sharing 🙂

  85. CathyK says:

    I just started using your spreadsheet and love it. Thanks so much. In loading my data, I have two questions.
    1. I have a NYSE symbol TANN that doesn’t load (in doing some research is doesn’t appear on google finance, but yahoo finance has it). I have entered the transaction info in the transaction page and the portfolio page. The name will not load on the portfolio page and all the other Portfolio fields are 0. On the ReferenceData Page the only fields that load are the Ex-Div and Div Pay Date, I am assuming it is getting this info from Yahoo. Is there a way that I can get the yahoo data to load for that symbol on my other pages?
    2. On the Transaction Page, every field in the name column has a red triangle in the corner saying “invalid Content”. I select the name off the pull menu and the correct name appears but the “Invalid Content” is still there. Does this matter? What am I doing wrong?

    I believe I am using the version 3.5.3 .

    • wayne says:

      Hi. I’ll take a try at this. TANN also shows up in Yahoo and QuantumOnline for Travel Centers of America Senior Notes/Exchange traded debt 2028. It appears the issue is with the symbol as this is not a ‘normal’ stock but an exchange traded debt. I have problems with some ETF’s and all Mutual Funds as they aren’t recognized the same way stocks are so my vote is that ETD is going to be the same. This ultimately requires manual entry until someone can get a formula worked out.

      TANN for Yahoo brings up Travel Centers of America LLC. But doesn’t say anything about this being exchange traded debt in the summary

      TANN for Google brings up nothing

      Entering Travel Centers of America on Google brings up TA for Travel Centers of America but can’t get it to show any of the senior notes.

      TA on Yahoo shows travel centers of america also.

      Did try various market entries on google but couldn’t get it to recognize TANN.

    • wayne says:

      On #2, I believe it’s an issue with Data Validation as it’s not recognizing all the proper cells but I can’t remember which area to adjust.
      Transaction Names, column C are pulled from the Reference Tab column A which in turn pulls from Portfolio Tab column C. Another problem I ran into once was renaming the name in the Portfolio tab and not making the necessary changes elsewhere. Hope this helps.

  86. CathyK says:

    Wayne: Thank you! Based on your comments, I was able to correct both problems. I had to make some manual entries, but at least the Portfolio Values etc. are all correct now. Many thanks, I spent hours with no success before your response. 🙂

  87. CathyK says:

    Everything seems to be working well now except a few little errors that maybe someone can help me with:

    1. On the Watchlist, the Consecuative Years of Div Inc shows #N/A for all stocks.
    2. Also on the Watchlist, the Graham Number shows #REF! for all stocks
    3. On the Portfolio Page, is there a way to show 0 in the Dividend fields on stocks that do not pay a dividend? Right now I just deleted the formula on those stocks and manual placed a 0 there. I don’t know enough about google formulas to fix this.

    I am using version 3.5.3.

    I love this spreadsheet and only wish I had found it years ago.

    Thank you in advance to anyone who takes the time to answer these questions for me.

    • wayne says:

      Hi CathyR.

      1 – I have the same problem and it’s probably an issue with the formula. I believe the formula was provided by another user somewhere earlier.
      2 – Some of my do the same thing. Not sure but it seems that some things take longer to update then others but if it’s for all no sure.
      3 – I think I found a way to have the portfolio add just the numbers and ignore text. Not that it will show a zero but you don’t have to delete anything, just change the formula. Found this to be easier as some of the reference data/portfolio items take longer to load and creates ‘errors’. See about 9 or so comments up. It’s from me as I found a way to answer my own question:)

  88. Dave says:

    Hello,

    Does anyone have a formula for RDS.B?

    In the Ref Data under Divi Yeild = #value and Ex Divi Date = n/a

    In the Portfolio under Ann. Divi = #value, Divi% = #value, YOC = #value

    Thanks for the help
    Dave
    Dave recently posted…Roth IRA Conversions & Purchase of BACMy Profile

    • wayne says:

      Hi Dave. Not going to be much help but….

      RDS.B is right for Google. Might try changing the symbol to NYSE:RDS.B but not sure it will work or not.

      Yahoo lists it as RDS-B so if it can’t find it in Google, it may not find it in Yahoo as its not .B so you might try changing the symbol to the -B to see if Yahoo will find and populate.

      In testing this in the watchlist, the company name comes from Google so it populated, except for yield, using RDS.B but the yield come from Yahoo so trying RDS-B populated yield but nothing else.

      The reference data should pull from google and then yahoo which is why I’m thinking of using the yahoo ticker instead of the google one.

  89. Dave says:

    Wayne,

    Thanks everything is currently populating using RDS.B except in the reference date under dividend yield. Im still getting #value and I think thats leading to annual divi collected, divi yield % and YOC not populating on the portfolio page. They all show #value also.

    Thanks,
    Dave

  90. wayne says:

    Hi Dave!
    Portfolio Tab, Annual Dividend Collected -Formula looks at F, R and H on the portfolio tab
    F is symbol, R is yield and H is market value. R refers back to yield on the reference tab so you are right, the reference tab, yield is causing the problem as it’s either not recognizing it or is taking forever to retrieve it. I’ve got a couple symbols that load maybe 3 out of 10 logins. Yahoo is the back up reference data if it can’t be found on google but yahoo won’t recognize it as yahoo uses a different symbol.

    I hope someone smarter chimes in but I think either it takes forever (and a day) to load or you’ll have to manually enter the yield. I have to do this with mutual funds as it’s never found them yet and occasionally with etf/cef funds as they don’t always retrieve reliably. When I do this I highlight the box so I know which are manual entries so I can remember to update every now and then. Not perfect but works. If you have some that load on occasion, you can modify the formula that adds up the columns so that it only counts numbers. This way anything not a number is ignored as you still get totals.

    Good luck.

  91. Dave says:

    Wanye

    Thanks for the help, I will give it a try

    Dave

  92. RBD says:

    Is anyone else having problems? My sheets are down again. It’s been a few days.
    RBD recently posted…Spinning Off A New Website – AccessIPOs.comMy Profile

    • wayne says:

      mine have been extremely slow. even after hours of waiting many still have errors but today it seems slightly better as I have more filled in then I’ve had the last several days

  93. Tom J says:

    Love the spreadsheet! Have been using it for a few months but for the past few weeks I’ve been having loading errors on Reference Data for Dividend Yield. Tried changing formulas but no luck. Maybe an issue with Google Sheets?

  94. CathyK says:

    I am having a similar problem to Tom. Although for me the entire column for Dividend Yield on ReferenceData doesn’t load. It is always “Loading”. Since several of the other sheets get their Dividend Yield from this page, the Dividend Yield doesn’t show on any sheet except the Watchlist. I have never been able to get the Consecutive Years Div Inc or the Graham Number columns on the Watchlist to load—not even for one stock. I don’t know enough about Google formulas to troubleshoot this and am hoping that someone can come up with a solution or tell me what I might be doing wrong. I also love this Spreadsheet but the loading issue is very frustrating.

  95. Jeff says:

    Hello all,

    Yahoo is having a problem with their data. The Yahoo Finance website on my work computer has changed, however, it does not appear that way on my personal computer. I changed a few cells to rely less on Yahoo data links, which has helped reduce the loading error message in other cells.

    Dividend Yield formula – =annual dividend column’s cell/price column’s cell
    Dividend Payout – =annual dividend column’s cell/EPS column’s cell

  96. Adam says:

    Here are the problems I have in my spreadsheet, which some of you may share, but I haven’t seen any solutions that worked yet in the comments.

    1a) Portfolio tab – Annual dividend, Div yield, and YOC show “Loading” forever. May not be an issue with the formulas, could just simply be taking a long time to load.

    1b) Portfolio tab – When I entered my first tickers, I of course started at the top row (row 5) and added tickers through row 10. After adding a few transactions on the transactions tab, the info on the portfolio page automatically jumped down to rows 18-23. Is that supposed to happen? Or should I re-enter the tickers on lines 5-10?

    2) Div calendar tab – “Next pay date” shows up as #VALUE! for all stocks.

    3) Referencedata tab – Div yield forever shows “Loading”. Ex-div date and div pay date are blank.

    4) Watchlist – Div yield shows 0.00% for all stocks. 5 year Div growth shows #REF! for all stocks. Consecutive years div increases shows #N/A for some stocks and Loading for all others. Payout ratios are all “Loading…”. Payout ratio and Graham Number are #REF! for all stocks.

    Again, I think the ones that say “loading” may load if I give them enough time but that could be a long long time. Where I get an error like #VALUE!, #REF! and #N/A, I don’t know what the problem is. I have 124 stocks in my watchlist, if that means anything.

    I believe I am using the most up-to-date version, but I don’t see the version listed anywhere (so how would I know if I am using an old version?). I’m assuming I am using the most recent version since I just downloaded the template yesterday. I wish on the template download page it would list the version, like if it were in the template title.

    • scott says:

      Hey Adam,
      Google Sheets has become very frustrating. What used to work now no longer does. I have some ideas to simplify the number of importdata, importxml, etc queries to try and make it run better. However, when I update the formula on the template it is just stock at “Loading…” Even more strange is that that same formula works great when used on a brand new spreadsheet! I can’t explain it! I even deleted ever single sheet except for Portfolio and ReferenceData and it still is stuck on the same error message.

      I’ll have to continue to look into the issue.

      Scott

      • Adam says:

        That’s interesting. Thank you for all your hard work on this, it truly is a wonderful spreadsheet (when it works). I had been using one I made myself but scrapped it for yours as it offers so much more.
        Part of my problem is perhaps I have too big of a watchlist so there is a lot to load.

        • Jonathan Roy says:

          You can pull in blocks of data at once. Some changes I made in my copy for instance, in Reference Data G4 I use:

          =iferror(ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”& JOIN(“+”, ARRAY_CONSTRAIN($B4:$B, COUNTA($B4:$B), 1)) &”&f=q”))

          and in H4 I use:

          =iferror(ImportData(“http://finance.yahoo.com/d/quotes.csv?s=”& JOIN(“+”, ARRAY_CONSTRAIN($B4:$B, COUNTA($B4:$B), 1)) &”&f=r1″))

          That fills the entire column in a single call.

          • Adam says:

            I entered those array formulas you have as indicated and I didn’t see anything happen.
            I’m honestly hoping a new version comes out because there’s so many things in my spreadsheet that seem odd (like entries in my portfolio tab jumping down several lines like I mentioned earlier) or don’t seem to work.

          • scott says:

            Adam, not sure what’s up with the entries jumping down, but I’m working on an update that will hope fix some of the lagging issues with the portfolio hanging while loading Google and Yahoo Finance data.

            Scott

    • scott says:

      I’m sort of making some progress…switching everything to Yahoo Finance API with fewer overall calls. Hopefully this will speed things up. I won’t be able to finish it tonight though.

  97. Tom J says:

    Scott,
    Incredible spreadsheet. Thanks for sharing your hard work. I have a question about tracking stocks held in both Brokerage and IRA accounts.

    I’m currently only tracking my brokerage account but will begin tracking my IRA. When entering a new buy on the Transactions tab, how does the sheet keep track of whether the shares are bought in the Brokerage account or the IRA account?

    Thanks for your help,

    Tom

    • scott says:

      What works best is add a delineator at the end of the company name to differentiate the two. Keep the same ticker symbol but then choose Coca Cola (Roth) and Coca Cola (brokerage) as the company name. Then just pick the appropriate company name under the Transaction section.

      Scott

      • Tom J says:

        Thanks!

        On a side note, it appears most of the problems I’ve had with loading errors eventually go away. Values used to populate almost instantly but now take up to an hour. Not ideal but at least the formulas work. I was going crazy trying to alternate between Yahoo and Google Finance. Seems I just need a little patience. Thanks again.

        Tom

        • scott says:

          Hi Tom,

          Yeah, it is frustrating that the formulas take so long to load! I’m working on standardizing the formulas to use the Yahoo Finance API with fewer calls, to hopefully speed things up. That will be available in the next couple of days.

          Scott

      • Adam says:

        Would you change the company name on the Portfolio tab, even though it’s light blue and thus filled in by a formula?

        • Wayne says:

          Yes. It won’t have the formula any more. There is a similar question earlier in the comments that talks about this more have to also rename in transactions

  98. Adam says:

    Right now my stocks on my portfolio tab start at row 10 (because they jumped down from row 5 for some strange reason). If I add a new stock, does it matter which row I put it in? That is, can I add it above row 10 or should I add it to the next empty row below the last stock?

  99. scott says:

    Hey guys!

    Just released version 4.0. This version removes all calls to GoogleFinance and solely relies on Yahoo Finance (so ticker symbols will need to be updated accordingly). Also, the ReferenceData page uses Jonathan Roy’s single call to populate an entire column, as he described above. Thanks for the tips! I removed GoogleFinance from the spreadsheet because it is much cleaner to get the dividend data from Yahoo’s API than doing a round about method with Google.

    I’ve noticed that there are still occasionally times when it seems stuck at “Loading…”, however it does eventually work. I’ll ultimately implement a way to manually type in an annual dividend for the times when it doesn’t load.

    Another change is that I removed the WatchList sections that were no longer working. I might branch the WatchList off as a separate template at some point so loading errors on that sheet do not affect the rest of the spreadsheet. Also, you now have to type the company name into the Portfolio page. As before, anything that is orangish-colored is manually edited; green is automatically calculated.

    I’ll be posting a blog post shortly talking about these changes.

    Wayne and Jonathan, any suggestions you can think of? Thanks for your help in answering people’s questions above!!

    Scott

    • wayne says:

      Hi Scott! Wish I was smarter and able to better answer some of the questions. As for other things, for me it would be a couple things I can think of..
      1. On the Portfolio tab and anywhere else……the ability to total the columns even if there is an error, loading, na etc in the box. Just needs to add numbers and disregard non-numbers.
      2. Finding an API that will pull etf, cef, mutual fund info.
      3. Big project but a top 10 common questions or something like that as there are many questions that are asked regularly. Not that anyone will read them but may be easier to ask them to check the topic under help or common q’s etc. When a symbol has many results on various exchanges, I do I get it to pull the right information. When I have holdings that are similar in name, how do I differentiate between them.
      4. There are a couple columns I hide and replace with something else and after I get it working somewhat properly I’ll drop you a note to get your thoughts.

      Ps. Thanx for all your work on this, past and present. I’d suggest that somewhere down the road you write and article on Seeking Alpha in the Dividend/Income section on your spreadsheet. Many regulars there offer their spreadsheets for $10-$30 and thus far I think yours is better then some I’ve looked at.

      • scott says:

        Thanks, Wayne! Great suggestions. I’ll start working on some of them soon. I’d love to see whatever you come up with as well.

        Thanks,

        Scott

  100. Tom J says:

    Scott,
    The updated version works great! Thanks for sharing your hard work with us.

    Tom

    • scott says:

      Tom,

      Excellent news. Thanks for letting me know! I hope to keep refining things even further but feel that Yahoo Finance is the way to go. Makes grabbing things like dividend yield much more straight forward!

      Scott

  101. jack says:

    Spam message has been deleted

    • wayne says:

      Two things……..

      1. Request and suggest that Jacks email be squeezed like a pimple until it’s gone, scar and all.

      2. Having some trouble with the new version. So just a silly question, are there any add-ons, plug-ins etc that may be needed just to make sure I’ve got my bases covered.

      Thanx!

      • scott says:

        Hi Wayne,

        Jack’s comment got through the spam filter. It’s gone now. It looks like your comment might be removed too! (When I marked Jack’s message as spam your comment and my response disappeared…had to restore Jack’s comment but delete its content in order for our comments to show up!)

        What troubles are you having with the new version? There’s no add-ons or plug-ins that you need.

        Scott

        • John says:

          Scott & Wayne,

          I believe I am also having the same problems as Wayne. Whenever I implement the data to the transactions page on the new sheet, the “Portfolio” section comes back with all negative numbers in my unrealized gain/loss in the amount of what has been invested. Also, everything is either loading or reporting an error message. Not sure what the problem is.

          Thanks for all your hard work Scott

          • scott says:

            I’ll take a look tonight. Unless you made a ton of bad investments, the unrealized gain/loss should not all be negative! 🙂

            Scott

          • scott says:

            Hi John,

            Still having problems with this? I added some of my own transactions to the Portfolio and Transactions sheets and the unrealized gain/loss sections came back with the correct numbers.

            Could it be related to an issue with a delay in loading the data from Yahoo Finance?

  102. wayne says:

    In playing with it I found some formulas referencing the wrong boxes. Not sure if I did this when I cut and pasted or not. Don’t know if that helps or not John. Some areas with the new formulas took forever to load and until it did, it was an error message. Left on overnight and in the morning, much of the error/loading stuff was resolved.

  103. Dave says:

    Hello,
    Where is the new Portfolio tracker spreadsheet located all I am able to find is 3.1.2. Where is there a version 4.0?

    Thanks
    Dave
    Dave recently posted…Taxable and Tax-Free Portfolio AllocationMy Profile

  104. Dave says:

    Hi Scott

    Thanks, I went to the page you gave me the link for, I just didn’t see anything that said ver 4.0 but I do see the dividend tracker portfolio spreadsheet at the bottom of the page.

    Thanks
    Dave
    Dave recently posted…Taxable and Tax-Free Portfolio AllocationMy Profile

  105. Dave says:

    Scott,

    Sorry forgot one question. Am I able to copy all of the orange sections for the old portfolio pages and past them onto the newer version or will that bring over unwanted data and cause downloading issues?

    Thanks,
    Dave
    Dave recently posted…Taxable and Tax-Free Portfolio AllocationMy Profile

    • scott says:

      Yes, copying the orange cells in the Portfolio and the Transactions sheet would be the way to go. You’ll also have to copy the company name as well from the Portfolio sheet as the new version no longer looks that up automatically.

      I’m working on a Frequently Asked Questions page right now that will address these great questions.

      Scott

      • jethrobodine says:

        My version of Dividend Portfolio Tracker is at least a few months old. How do I go about updating to the latest template?
        Thanks.

        • scott says:

          You’ll want to copy the orange cells, including company name, from the Portfolio sheet and paste them into the latest version, which you can get from the top of this page.

          Then, copy the orange cells from the Transaction sheet to the new version. Everything else should load automatically then.

          Scott

  106. scott says:

    Question for anyone following these comments and using the latest version, 4.0:

    1) Are you having less loading errors now? I’ve switched to the Yahoo Finance API and it uses overall less calls. However, I’ve still noticed that it takes sometimes a few minutes for the Yahoo Finance data to be imported especially initially after adding a new stock…very frustrating. It does appear to load correctly now if you give it some time.

    2) The main thing that is causing loading issues is having to use a roundabout method to import dividend data, ex-dividend date, and dividend pay date via Yahoo Finance API (or using an importHTML/XML command to get it from Google…done in prior versions but no longer as of version 4.0). I feel that the Yahoo Finance method is a little more reliable but it still runs into issues. I could fix much of these errors by going a slightly different route, which, however, does require a bit more manual editing.

    What would you guys think of leaving the dividend section blank and having to add that data yourself? I could have an adjacent column attempting to look up the data. This would remove some automation but should eliminate any loading errors, NA messages, etc. I would leave a column adjacent to the manually edited one which would give dividend data as a reference only. This is actually the method I use on my own portfolio because I like typing in each dividend increase myself so that I can see how much it raises my annual dividend.

    Please let me know what you think about this change. It would slightly get rid of some automation but would be much more reliable.

    Thanks!!

    Scott

    PS I’m working on a Frequently Asked Questions section right now.

    • Michael Wolf says:

      My Two Cents would be to leave it as it is.

      I assume you are referring to the Dividend related columns on the Portfolio page? Mine work fine. Very cool.

      Now, the information under the Dividend Calendar tab does not populate automatically, so that is another story. Perhaps that is what you are referring to? Sorry, I haven’t had time to follow the conversation.

  107. wayne says:

    Hi Scott! For now I’d leave as is. There are enough formulas btwn the old and new to swap them around etc and since 4.0 hasn’t been out long let people play with it to see what problems and what solutions they come up with. v3, v3.5 and v4 all seemed to work for some and not for others. PS I did find an api for mutual fund dividends. It google and it ‘dividendincome’. Now I need to see if I can combine the mutuals and the stocks.

  108. Adam says:

    How can I tell which version I’m using? Is it indicated anywhere in the spreadsheet or in the file properties?

  109. Tom J says:

    Scott,
    Ver 4.0 has been working great for me. For my own tracking purposes, I use three separate copies for an IRA, Roth, and taxable investment account. I like to have a separate summary for each. No problems loading data except for one day last week when three stocks out of 28 loaded 0.00 for the dividend per share. I set conditional formatting on the Dividend Yield column on the Portfolio page just to highlight that something was amiss. Thanks for all the changes.

    Tom

    • Adam says:

      The dividend yield loads for the first 46 stocks in my watchlist (I have 124 stocks in my watchlist). For stocks 47-124, it shows 0.00. I see #REF! for the 5-year dividend growth for stocks 12-124, but it looks like those are slowly loading one at a time. After 5 min, the 5-year dividend growth for stocks 17 and 22-27 loaded, and some of the other yields loaded. So I think I just have to give it time and those will all eventually load.
      Many cells on the other pages show either “Loading…” or “#REF!” and I think most of that will load if I wait long enough.
      Definitely an improvement!
      I think I will also use a separate copy of the spreadsheet for each of my accounts, esp. so that all of my transactions in my taxable acct. will stay separate from the transactions in my IRA acct, rather than being intermixed on the transactions tab of a single spreadsheet. Perhaps some of the data will load faster that way too.

      • scott says:

        Hey Adam,

        One way of keeping both IRA and taxable transactions intermixed is to just change the name of the company to reflect what account it is from. Using the same ticker symbol but having “General Electric (taxable)” and “General Electric (IRA)” would then allow you to keep those transactions separate but on the same spreadsheet.

        Are your dividend portfolio and ReferenceData sheets getting updated correctly? That was the main issue I was hoping to correct in version 4.0. In fact, I had not changed the Watchlist formulas at all yet. I was just trying to get the other sheets to work correctly first and then I’d address the Watchlist secondarily.

        • Adam says:

          I read in a past post about revising company names like that, and I’ve done that, but I think you misunderstood what I meant. I meant that, even if the names are different to identify the account, on the transactions page if I have it organized by date, I might have a transaction for General Electric (taxable) right below a transaction for General Electric (IRA), or they might be several lines apart. I think it would be helpful if all the IRA transactions were grouped together separate from the taxable acct transactions, which could be accomplished by having them in separate spreadsheets. One other thing with separate spreadsheets is that the (uninvested) cash balance on the portfolio tab would reflect the cash in each account, rather than the sum; same with the account performance in the summary tab.

          I’m not sure if the dividend portfolio and ReferenceData sheets are getting updated correctly. When I opened it earlier today, they either said “Loading…” or “#REF!”. I didn’t keep it open more than about 10 min, so maybe if I waited longer they would have all loaded. I will say that last weekend I had the spreadsheet open for a few hours and on the ReferenceData tab, the annual dividend loaded for only 1 of 6 stocks.

          I basically started fresh with the new version. The only things I cut and pasted were the stock symbols since I have so many in my watchlist and some of the transactions. I think I dragged down all applicable formulas, because I can look at particular cells and see that the formula is the same as the one above it except for the reference to the cell that the stock’s symbol is in. But the value might load in one cell and not the other.

          I started from scratch with the portfolio and ReferenceData. If those sheets are not getting updated correctly, I’m not sure what to do different. If they are, then I don’t know how long to wait for everything to load. I don’t particularly want to have to plan to open the spreadsheet hours before I want to actually use it to give it time to load everything.

  110. Michael Wolf says:

    Sorry for tests, was getting for “403 Error” message for some reason.

    Are the data fields under the “Portfolio Value” tab designed to be completely manual? There do not appear to be any embedded formulae.

    • scott says:

      Hi Michael,

      No problem about the tests. Yes, the portfolio value sheet is manually edited. Choosing the frequency that the portfolio value sheet is updated at is such a personal preference that I wanted to leave that option open for people to change themselves.

  111. J Bodine says:

    I have an entry for KMP on my Portfolio tab. It has since been replaced by KMI through a conversion but the 0 share total remains. It’s causing a problem with numerous calculations such as Unrealized Gain/Loss, Unrealized Gain/Loss %, Total Gain/Loss, etc.
    Has anyone else had this problem? Suggested solution?
    Thanks.

    • scott says:

      Hi J Bodine,

      I’m going to be running into a similar issue with Baxalta getting acquired by Shire.

      What I would try is to find out what the cost per share for KMP is on the ReferenceData page. Then change the ticker symbol of KMP to KMI on the Portfolio page. You’ll also have to create a unique company name (something different than what you used for KMP). Finally create a new entry on the Transaction sheet for KMI with the buy price equal to the cost per share that you had at the end for KMP. I’d pick the buy date as the earliest purchase you made of KMP.

      This would work if the conversion was 1:1.

      Let me know if this doesn’t work for you and I’ll try to come up with other solutions.

      Scott

  112. Sunil says:

    Hi Scott, You have provided great information here. But I am an Indian and can I use this in India based stock exchanges and tickers as well?

    • scott says:

      Hi Sunil,

      This portfolio spreadsheet works best for US companies. However, if you can look up your stock traded on an Indian stock exchange on Yahoo Finance, then it should work here as well.

      You may be interested in the international version of this spreadsheet, which also allows easy currency conversion between a handful of major world currencies. You can find that spreadsheet here: http://www.twoinvesting.com/investing-spreadsheets/

      Scott

  113. Tim says:

    I am getting a #VALUE error on the following stocks on the dividend yield column of the REFERENCEDATA sheet.

    RCI.B
    CTC.A
    XFN.TO
    XRE.TO

    All stocks listed on the Toronto Exchange.

    • scott says:

      Had it been working before and just stopped worked? What version of the spreadsheet are you using (under the About sheet)?

      • Tim says:

        Using version 3.14

        It worked before but lately has been very inconsistent.

        • scott says:

          Google had made some changes to Sheets that negatively affected things. I updated some of the formulas in version 4 to try and fix some of those…namely by switching completely to using data from Yahoo Finance as well as by reducing the number of total calls to outside sites to obtain data. It seems to work better now.

          • Tim says:

            Thanks Scott. I tried 4.0 and am finding that the older version is loading up quicker. Down to CTC.A as the only symbol not loading…

          • scott says:

            Thanks, Tim. Interesting how each version seems to work better for different people!

  114. Dave says:

    Hi Scott

    Im transferring everything over to Ver 4.0. I have it downloaded but no info on it yet. The only thing I did was add additional lines on portfolio, div cal, transactions and ref data. I made sure all of the formulas copied over. When I on Divi Calendar it shows all but 6 of my stocks in the drop down though I have them all listed on my portfolio page. What am I missing????

    Thanks,
    Dave
    Dave recently posted…Latest Purchase of Amgen (Finally!)My Profile

  115. Jeff says:

    Hello, I clearly must be doing something wrong.

    I have approximately 20 or so stocks using your investment tracker but it doesn’t seem to update when I open it. What I mean is I know it is pulling a lot of data but there have been times when I opened it and walked away for 10-20 min as a test to come back and still see “loading” in some cells while others clearly have incorrect information (ex. last price). I figure it must be user error?

    What is the normal process/time frame when everyone opens the google spreadsheet?

  116. Mücahit says:

    Hi, is it me or am i doing something wrong as i cant acsess templates? it shows an empty page.

  117. Adam says:

    Hi Scott,

    I have several accounts and some of them have overlapping stocks. In Portfolio the same stock shows up in multiple accounts but with the same total thereby causing the total to be skewed. For example, I have 5 shares of ABC in account #1 and 5 shares of ABC in account #2, Portfolio shows 10 shares of ABC in both accounts. I think the filtering needs one more step. Thanks for a great tool. And congratulations on your new job. My daughter just took the MCAT, we’re keeping our fingers crossed.

  118. Adam says:

    Hi Scott, just went back and looked at the Spreadsheet, I don’t see any ways to handle the problem of skewed totals caused by having one stock in multiple accounts other than adding an “Account” column in the “Transaction” sheet. Will try to see if I can modify my copy to add this capability.

  119. Gary says:

    Scott-
    I can’t seem to figure-out how to create 3 separate portfolios- TIRA, ROTH, Taxable. I see it can be done, but it is a real problem on how to separate them– I tried creating duplicates and changing, but the transactions are not separate. Help!
    Love the spreadsheets.
    Gary

  120. Divi Cents says:

    What a great tool! I use this all the time for my portfolio.

    Thanks for making this

  121. Ilya Biervliet says:

    WOW from the bottom of my heart.
    THANK YOU!!

  122. Dave says:

    Hi Scott

    When I add any stock symbol past line 54 on the Portfolio Spreadsheet it does not show the symbol when I look for it on the Divicalendar. What do I need to add in to get those symbols to show up there?

    Under Reference Data all the ticker symbols are there if that helps with anything

    Thanks
    Dave

    • scott says:

      Hi Dave,

      Have you added more rows on the Portfolio sheet? Do that first and then make sure the formulas are populated down from the rows above. Then, in order for those symbols and company names to appear on the drop down under DivCalendar you have to update the validation.

      Here’s how to do that for the Company name on DivCalendar: Highlight all the rows in column A that you want to use. Then click the menu item “Data” followed by Validation. Under the Criteria section you’ll see the range, which defaults to row 54, hence why you are not seeing stocks below that. Just update that number for however many rows you need. Do the same for the symbol column and you should be set.

      Let me know if this doesn’t make sense and I can send you an email with some photos.

      Scott

  123. Dave says:

    Scott

    That did it !!!!!!!

    Thanks for all the help and the spreadsheet

    Dave
    Dave recently posted…Starting Speculative PortfolioMy Profile

  124. Dave says:

    Scott

    How do I add a sector to the pie chart on the portfolio page? Ive added it to the ‘list” spreadsheet, it comes up on the “sector” section on the portfolio page but I can’t get it onto the pie chart. Ive tried the “advanced edit” but can’t seem to get it.
    Thanks again
    Dave

    • scott says:

      When you add the new sector to the List sheet it should show up on the table to the right of the pie chart. You’ll want to drag down the formula for that Value column. Then under Advanced Edit for the pie chart click “Chart Types,” and update the range to include the newly created rows in that table on the right. For example, if I added a single sector at the bottom of the List section, the pie chart range should end with O70 rather than O69.

  125. Dave says:

    Got it !!

    Thanks Again For The Help

  126. wayne says:

    Hello Scott! Been busy lately so haven’t kept up as good as I should.

    Been tinkering and it’s a little early for thinking and even wrote the 4 different times as the previous ones were very long.

    The very short version is, how would I add columns/formulas to show shares ‘bought’ from ‘out of pocket’ funds vs shares ‘bought’ from Div Reinvestment? There is a lifetime dividends column but if I reinvest my dividends they bought x shares and would seem to have their own cost basis over time

    It would seem that I need to add a DBuy (Div Buy) in the transactions table selections so a formula could exclude dividend reinvestment buys

    I would also need to add columns to Portfolio Shares or Reference.
    Cost basis is then 2 columns ‘Buy’ and ‘DBuy’
    Shares would be 3 columns Buy, DBuy and Total
    Market Value is total shares x market price

    Not sure where my thoughts are right now other then to separate my ‘buys’ and ‘buys’ done with reinvestment’s so that I can see/track ‘the power’ of dividend reinvestment.

    If this doesn’t make sense, I’ll chalk it up to not having my morning soda yet.

    Thanx,
    Wayne

    • scott says:

      Hey Wayne,

      Good comment. I can kind of see what you mean. However, consider this scenario: You have a large investment in a dividend paying stock and just recently received a dividend payment of $2000. This would have been deposited as cash into your brokerage account. You can do whatever you want with that cash…withdraw it, use it to buy another stock, or even reinvest it back into the stock that it came from. It is no different than if you got paid $2000 at work and deposited it into the account yourself. If you have the dividend reinvestment box checked then your broker will automatically use that $2000 to buy more shares without any commission fee. Had you unchecked the reinvestment box and you elected to buy more shares of anything, then you would end up paying the usual commission.

      My spreadsheet accomplishes this by entering received dividends as Type “Div.” Then on a later transaction you put a Type “Buy”in in order to use the cash now sitting in your account to purchase more shares. Leave the fee section blank if it is a reinvestment or add whatever the commission is if you decide to spend that dividend elsewhere.

      You’re right in that reinvested dividends do affect the cost basis because they definitely do. That’s one of the reasons why dividend reinvestment in a “down” market is so valuable…the dividends are able to buy more shares of stock at a lower price, decreasing your cost basis and helping juice returns when the stock price goes back up.

      Currently the spreadsheet tabulates the total cash value of the dividends received for each stock. Are you looking for another column that displays the total shares purchased with those reinvested dividends? It would be easiest to construct a formula to search the Transactions sheet for purchases that fit that criteria rather than separating a “standard Buy” from a “Div Buy.”

      Scott
      scott recently posted…September 2016 IncomeMy Profile

  127. wayne says:

    Thanx Scott!

    I guess I was trying to get to ROI with ROI = my initial investment. If I put 2k into stock/etf/fund X and in 2 years it is at 4k I’ve hit 100% on my ROI. Of course this would be a combination of price appreciation but also purchase of new shares, dividends, dividend reinvestment etc all of which still only cost me 2k. I guess that’s why I was trying to figure out how to separate a regular by from a dividend/capital gain reinvestment to see the difference in shares in addition to a cost basis for dividend purchases.

    I see the spreadsheet tabulating the total cash value of dividends collected in Portfolio M but to me it appears that Portfolio N, Total Gain/Loss is off as it is adding Portfolio I + L + M (unrealized gain, realized gain, dividends collected).

    Portfolio I, unrealized gain, is a tabulation of Reference Data C + H (last price and cost per share) X Portfolio E, total shares which reflects all shares purchased i.e. Transaction Tab ‘Buy’

    Reference Data H is based on Portfolio E and H, Shares and Cost Basis

    So it would seem to me that adding dividends collected to come up with a total gain/loss is off as this money is already accounted for in a dividend reinvestment plan as shares were purchased which show up in Portfolio E, shares, which is used to determine cost basis in Reference Data H, cost per share, which is used by Portfolio N, Total Gain/Loss. So why is it adding the cash value of the dividends to come up with total gain/loss if it looks like it’s already factored in?

    Sorry if I’m confusing myself.

  128. Marc says:

    First of all, absolutely find your spreadsheet rather awesome! I have been playing with it the last few weeks and think that it is a great way to keep track of investments. I pointed it out to a few of my buddies who are looking to paper trade before putting their actual capital into the market.

    I built one several years ago ad decided of recent to go back and “modernize” it to make it more functional and include a variety of investments (ETFs, Stocks, Precious Metals, Alternatives). It’s a pretty big chore, but am seeing some significant progress and is coming together quite nicely. With being able to connect to a Bloomberg Terminal, I have some neat options.

    I do have one quick question: how did you develop the Buy, Sell, Div, or Split option that automatically comes up in ‘Type’. I have yet to figure this out; I know how to create lists but I am not sure how you were able to conjure this. Any help would certainly be appreciated.

    • scott says:

      Hi Marc,

      Thanks! That’s awesome that you are able to connect to a Bloomberg Terminal. Can you integrate that with Google Sheets or does that require Excel? As far as those options coming up in Type, I just typed “Buy,Sell,Div,Split” under the Criteria section when you go to Data and then Validation. Is this what you’re talking about?

      Scott

  129. Joel says:

    I am getting a lot of these error messages for the 5-yr dividend growth rate –
    Error
    Function INDEX parameter 2 value is 2. Valid values are between 0 and 1 inclusive.

    What’s going on with this?

  130. wayne says:

    HI Joel. Sometimes I’ll get the #REF if there is a problem with the formula, the data it’s referencing somewhere else on the spreadsheet or it can’t find something because it’s taking a long time to retrieve the information. If #REF is shown in the formula, may have to find a cell that has it right and ‘drag or copy’ or make sure the information it’s referencing is correct.

    • Joel says:

      Thanks, Wayne. Those things make sense. IN this case case it’s just the 5-yr dividend growth rate being pulled in from gurufocus via importhtml. What’s weird is that some of them pull in values and some don’t. Some have pulled in the number and then stopped. Very bizarre, I think.

      • scott says:

        Hi Joel,

        Wayne is right. The data is pulled from gurufocus. On my watchlist the majority of the 5-year dividend growth rates are being pulled in correctly. For you watchlist, is every 5-year not working or is it only a few of them? And, do those few that don’t work change over time?

        I’ve seen these kind of problems with Google Sheets occasionally. It might be something we just have to deal with since we are using a free product.

        Scott

        • Joel says:

          The formula is the same, some were working some weren’t. Last night all the 5-yr dividend growth rates were coming through. This morning, only about half. I think you’re right about being one of those things we just have to deal with. It just seems odd the the same data from the same website is being requested and some get pulled in and some don’t. Anyway, I appreciate the help and sharing this with everyone.

  131. Tom J says:

    Scott,
    Thanks again for the work you do on the spreadsheet. It’s made my job of tracking investments so much easier. As I start the new year, it also marks the one year point of when I started using the sheet. I track around 30 stocks with it and DRIP about half of those, some with monthly dividends. At years end, I have 320 transactions entered on the Transactions Tab. I like the way the spreadsheet tracks year to year performance but am concerned that there may be a problem lurking in my future as the number of transactions double by the end of 2017.

    Have you run into any issues with a large amount of transactions? I know I can simply save another sheet and use it for 2017 but would lose the YOY comparisons within the sheet. Thanks again for your shared work and good luck in the market of 2017.

    Tom J

    • scott says:

      Hi Tom,

      I have almost 1000 transactions on my Transaction right now and it still only takes a second or two for the changes to propagate to the other sheets. You should be fine just adding on.

      Have a great 2017!

      Scott

  132. Adam says:

    How do you account for interest on your account (that is, cash)? Apparently I left some money uninvested in my core account and it earned a small amount of interest.
    Would you add “Interest” to your account as a holding, as if it was a stock you bought, and then put a transaction in for a sell of 1 share with a share price equal to the interest payment, and just disregard the fact that it would show a negative # of shares?

    • scott says:

      Hi Adam,

      If you “purchase” interest as a transaction, most of the automated transactions on the Portfolio sheet won’t work. You would have to manually change some of the formulas for it to work like that, but it could be done.

      I currently just use that cash section on the Portfolio sheet as a rough estimate. Let me know if you come up with a way to handle the interest the way you’re talking about. I’d go about doing it exactly the way you discussed.

      Scott

      • wayne says:

        To make it easier, couldn’t you manually add ‘cash’ as a ticker? Then in the transaction page, you just select dividend to account for interest and dividends on your cash account?

        Maybe add ‘int’ as transaction in additional to div, buy, sell on the transaction page?

        Then possibly copy and paste one of the other formulas into the CASH box on the portfolio page?

        In theory this would make the CASH box on the portfolio page a ‘investment’ and any interest would reflect in the cash investment from the transactions tab entries. Any dividends that aren’t reinvested could be called interest vs dividend so they are accounted for in the cash account too as I believe the formula could be set to search for any ‘int’ entry.

        • scott says:

          Great suggestions, Wayne! You’d still have to edit the ReferenceData page to make the “stock” used for CASH to be valued at 1. I’ll see about adding this feature to the next version.

          • Adam says:

            Thanks for the ideas, guys! As we’re talking pennies of interest, for now I’m just going to update the cash section at the top of the portfolio sheet, like Scott does.

          • Ryan says:

            Hi Scott, thanks so much for sharing your creation. I’ve been thinking about how to handle the cash situation as well. Though I’m more concerned with deposits or withdrawals of larger cash transactions. Or perhaps a monthly EFT of funds that go to a cash position waiting to be invested.

            Can you comment on whether XIRR (or other performance metrics) care about the inflows and outflows of cash or is it strictly dealing with the transactions for each individual position?

            Thanks!

          • Ryan says:

            I should have mentioned that I’m totally tracking on the need to enter reinvested dividends as two rows of data on the Transactions tab.

  133. Lucas says:

    Fantastic spreadsheet! Thank you! I’ve just about imported all my transactions and it works great. I added a few scripts to automatically make Dividends reinvested (insert row below, copy the transaction, and change it to Buy).

    One request: Is it possible to add more tracking for performance on a time basis? IE: Each stock/fund’s yearly performance, last 3 years, last 5, etc..?

    • scott says:

      Hi Lucas, good suggestions! I’ll see if I can come up with something. Also, I’m not up-to-speed on using the scripting language in Google Sheets. Do you have any example spreadsheets you could share or have a good online reference to recommend? It seems like a powerful tool.

      Thanks,

      Scott

      • Lucas says:

        Scott, I’m having issues replying. I’m getting an HTTP 405 error after the robot check.

      • Lucas says:

        Disregard my previous post. It looks like it works now.

        Anyway, I’m definitely no script expert. I learned just barely enough to write the script for Div reinvestment for your sheet and that’s just by looking at examples for each step of what I needed to do. You can find a good starting tutorial https://www.google.com/script/start/ at this site.

        I’ve included my script at the bottom of this post. It’s definitely slow, clunky, and in fact sometimes takes running a few times to go through the whole dataset (not sure exactly why). It does the trick though.

        One more suggestion for your sheet though: Is it possible to move the zeroed out positions out of the main portfolio page? I have plenty of positions that I’ve closed out a long time ago, but they look like they’re necessary on the portfolio page to keep the longterm XIRR intact. Would be nice to clean those up and move them somewhere else, but I guess that might just be personal preference.

        Can’t wait to see any improvements to tracking performance over shorter time frames!

        I’ve tried to attach the script, but I think your website is preventing me from doing so. e-mail me if you’re interested.

  134. Cathy says:

    Scott: I just noticed that you updated the spreadsheet in September to show estimated dividends. Rather than starting from scratch and using the full spreadsheet I just added the sheets that were changed to my existing spreadsheet. Everything appears to be working except one thing. On DivPayoutCalc, the Pay Months only appear on the first 2 rows. I can’t figure out how or where you are drawing this information and hope you can help me complete this last step in getting this sheet to properly calculate. I so much appreciate all the work you have shared with us. Thank you in advance for your help.

  135. RIck says:

    Hi Scott,
    I am new to your spreadsheet and blog, great work and devotion to this! I have been reviewing different ones that I have found that are similar, but decided on yours and hope it works well for me. You’ve done a very nice job and I am going through it all right now. My question is what is the best way to import stocks ( and MFunds) that have a long history of dividends, transactions, costs and growth- should I add the grand total of shares, cost basis, and resulting gains or losses for each stock as it is today, or is there a way to import the entire history of detailed transactions for each investment that goes back years ?
    thanks much, and I will look forward to using this great template that you have created!

    • scott says:

      Hi Rick,

      If you have a long history of transactions you’d have two choices, 1) individually enter each of these on the Transaction sheet, or 2) figure out the cost basis for the position as it is now and then put a Buy type transaction in for the current number of shares and cost basis. I’d probably do option #2 and include the total transactions costs as well. If you chose the first date you purchased the stock, then the XIRR calculations would be the most accurate.

      There may be a way to create a script to import your data if it would be exported into a spreadsheet format. However, creating that script on an individual basis is currently beyond my abilities.

      Scott

  136. RIck says:

    easy question i think:I added my first transaction on transaction sheet but the number of share did not change on my portfolio sheet- what did I do wrong?

    • scott says:

      Hi Rick,

      There could be a couple things going on. Did you type the stock name and symbol on the Portfolio page? Then, did you choose that company from the drop-down choices on the Transactions page? If you are still having trouble, send me an email to scott (at) twoinvesting (dot) com and we’ll be able to go over things in more detail there.

      Scott

      • Rick says:

        Hi Scott- thanks for you reply(s)- I will use option 2, much easier!
        My real problem with transactions input has been that the cumulative shares do NOt update when I record a dividend- I am adding dividends for one stock for the year, and then I move on to the next stock, so the dates are not fully cronological for the full transaction sheet, they are in name of stock order, for example, pepsi, then 4 dividends, then say IBM, then 4 dividends, etc. The cumulative shares are not updating.. is this supposed to be done manually? I checked the formula but not sure of how that impacts the a dividend shares cumulative update.

        Thanks,
        Rick

        • Lucas says:

          Chronological order does not matter. However, if you’ve reinvested dividends and it’s just listed as “Div”, that won’t add shares to your account. You have to add a corresponding “Buy” for every “Div” with the same share # and price. Technically a Div was a withdrawal (sell) and then you’re using that money to buy more.

        • scott says:

          Hi Rick,

          Lucas is exactly correct. You first have to account for the dividends as type “Div” and then do a “Buy” transaction for each dividend that you want reinvested. It is only important that chronological order is maintained for each specific stock.

          Scott

          • rick says:

            Thanks Scot- this is now working well and I am in process of entering many transactions. The transaction sheet works well, and for some very long term stocks, I have just entered totals as it would be too difficult to go back 10-15 yrs for example. Two quick questions at this point as i get familiar with this spreadsheet- is there any mechanism to enter mutual funds where the dividends and gains get reinvested? is there a good and easy way to do this? I know that may not have been the intention but do you have some thoughts on that?
            and 2- how do we ensure this spreadsheet with all the work you have done and what it takes to enter so much information, stays active for years to come?
            thanks much!
            Rick

          • rick says:

            Scott- one question on recording both a “div” and a”buy” in the transaction sheet so that the cumulative shares are correct- Do I need to record the price for each entry, both div and buy- if I do that then this adds to my cost- if I leave the “buy” entry as price of o, then it doe snot add to my cost- which is correct?

        • Wayne says:

          Hi Rick, I’ll take a stab at this.

          Since my mutual funds are in a tax deferred account I count short and long term gain as dividends as it doesn’t make a difference to me tax wise. If you’r comfortable with formulas, you could add STerm and LTerm to the transactions in addition to buy, sell, div.

          As for the Div and whether to add share cost or leave at zero. I’ve pondered this also but it’s all on the Portfolio tab. Any purchase will change your cost basis. A little farther over there are the Div Collected and Total Gain/Loss and the formula adds the lifetime dividends to the cost basis. I too am a little confused with this one but it was giving me a headache so just left it. My confusion is that if a Div is reinvested i.e. Buy it adds it to my cost basis which the is reflected in the Unrealized Cost Basis column. So why would I add the Dividends Collected to the unrealized cost basis to arrive at a Total Gain/Loss. When I looked this up on the internet, it seemed to be the right way to do this but I’m still confused as to why it’s right.

          • scott says:

            Thanks for the help as always, Wayne!

            I’ll be posting a video and a little better explanation on how the dividends and dividend reinvestments are entered on the Transaction sheet.

            In a nutshell, here’s what I do for stocks that I have automatic reinvestment turned on:

            On 1/25/2017 I received a dividend from CSCO for $29.87. Then on 1/26/2017 I bought 0.9751 shares at 30.6321. You’ll see that I first received a cash amount in my account and then (only because I had dividend reinvestment turned on) it automatically shares of CSCO. The cost basis effect only comes from the repurchasing of the shares; if you reinvest the dividend when the stock is lower priced then you will reduce your cost basis and if purchased at a higher price then it will raise the cost basis.

            As far as recording the dividend, you have two options. What I do is choose type “Div”, use “1” as the Transacted Shares and the Transacted Price/Share is the dividend amount, 29.87 in the example above. Any values under type “Div” have no effect on the cost basis.

            The other option is to still choose type “Div” but then put in 0.9751 as the Transacted Shares and 30.6321 as the Transacted Price/Share. What matters isn’t these values but rather the Cash Flow in column R, which multiples those two values together.

            For me it is much easier to just have the Transacted Price/Share to be the exact dividend amount I received.

            A lot of information to put into a comment so I will be writing a short guide on how to use my spreadsheet soon.

            I’m actively using this spreadsheet too so it will stay active for many years to come. The only thing I can’t control is if Google does something to stop this service.

            Thanks for commenting, Rick. I apologize for the delayed response.

            Scott

  137. Cathy says:

    I have a problem that I am pretty sure is easy to fix but I can’t seem to figure it out. On my transaction page I can no longer pull up the company name on the dropdown menu. I have adjusted the validation range to include all of the rows in the DataReference Sheet, but the name still does not appear. The crazy thing is if I look at the menu say 10 rows above the row I am working with, the company name is there. So the name will appear in the dropdown menu for say row 2 thru 225 but will not appear in any row below 225. Hope someone can help with this.

    • scott says:

      Hi Cathy,

      Any luck with this yet? Can you see any companies in the drop down menu from below 225? You could also try typing the company name in. Send me an email at scott (at) twoinvesting.com if you still need more help.

      Scott

    • Wayne says:

      Hi Cathy! I think the problem is that the Transaction page pulls from the Portfolio page, not the Reference Data page. The Reference Data pulls from the Portfolio page also.

  138. Alex says:

    Hi Scott, Just started having a look at your spreadsheet, and may just scrap mine, though they are similar. That yours is transaction based makes a huge difference. Fantastic job.

    Just as a quick question. My portfolio is mainly CAD based. Would you suggest using the main version, or the international version? Also, will you continuously update both version?

    Thanks,

    Alex

    • Alex says:

      (forgot to check the update by email 🙂 )

    • scott says:

      Hi Alex. Glad you like it! You could try using both versions and see how they work. I will be continuously updating both, though the main version will likely have more frequent updates since more people are using it. If you are okay with mixing CAD and USD, then the main version may work just fine for you.

      Scott

      • Alex says:

        Thanks for the quick reply. I will have a go with the main version.

      • Alex says:

        Hi Scott, There seems to be continuous problems with the div cells ‘loading’ or resulting in N/A. I see that there have been ongoing issues based on the comments above. Have you managed to get it working in version 4.0 without issues now? (I am assuming version 4.0 is the one currently listed in your template bank.)

        Also, if you are not having issues, do you know if there are issues based on CAD stocks, because of the addition of .TO, for instance CP.TO.

        Thanks for your ongoing support of these spreadsheets. Love them.

  1. August 7, 2014

    […] I got a comment from Scott. When I explored his site, I realized he was the one who created all the powerful spreadsheets I had been using.  Scott’s portfolio recently cracked the 100k mark.  Be sure to stop by and […]

  2. January 27, 2016

    […] of all sorts of spreadsheets to track and analyze your dividend portfolio comes from Scott and his Two Investing blog. He says that he got an inspiration from my calendar page but for sure, he uplifted those […]

  3. February 13, 2016

    […] fidelity.comの場合は,Two Investingが分かりやすかったです。 […]

  4. January 29, 2017

    […] (each transaction is one row). It's a Google Sheets spreadsheet that someone else developed: Dividend Stock Portfolio Tracker with Transactions Page ? Two Investing It's not intended for active trading, and especially not day trading, but I find it useful for […]

Leave a Reply

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

CommentLuv badge