Step-By-Step Guide on How to Make a Dividend Portfolio Spreadsheet

This post was originally published in 2016. The key formula for dividend info from Google Finance was formulated back in 2014. Due to the changes in Google Finance in March 2018, it was necessary to update the formula for pulling the dividend data. As you may know, pulling dividend data directly Google Finance no longer works. After looking on the web, I found that Yahoo Finance is a good reliable alternate source to pull the dividend data from. 

 

A while ago I shared my tutorial how to use Google Spreadsheet for dividend portfolio. In the tutorial, I resolved the key problem of the build-in GOOGLEFINANCE function – its inability to pull stock dividend information, to be more specific, dividend per share and dividend yield. Although I shared the formula to pull dividend information automatically using Google Spreadsheet in the tutorial, many readers asked about sharing a Google Spreadsheet dividend portfolio template to track their dividend portfolio. As you can see, this post is a bit long over due (sorry!). After many months of revising my own Google spreadsheet dividend portfolio template, I thought it might be a good idea to write a “step by step guide” on how I created a Google finance dividend portfolio template.

I’m a self-proclaimed Excel nerd. I love Google Spreadsheet because I can access the spreadsheet anywhere in the world, as long as there’s internet access. I really hope this step by step guide will help you many of you.

Please note, since I am sharing the template I use to track our dividend portfolio, the design is based on owning both US and Canadian listed dividend stocks.

 

Step 1: Create a Google Account

Most people have a Google account but if you don’t, create one so you can use Google Drive. Once you have an account, access your Google Drive and create a new Google Spreadsheet.

 

Step 2: Create headings

The first sheet I rename it to Dividend Portfolio. I also create headings that I want to use to track my portfolio. After many changes, I decided to use the following headings:

  • Sector: The sector the stock belongs to
  • Accounts: Which accounts we hold the stock in
  • Currency: Since we hold US and CAN listed stocks, this allows us calculate the portfolio market value in Canadian currency
  • Ticker: Stock ticker symbol
  • Name: Name of the stock
  • Shares: Number of shares we own
  • Cost Basis: How much we paid to purchase the stock
  • Cost/Share: Price per share that we paid for the stock
  • Price: Current market price
  • Market value: Current market value of the stock
  • Profit: How much unrealized profit we are making
  • Weight: Stock weight of the portfolio as a percentage
  • Div/Sh: Div per share received per dividend payout
  • Yield %: The current dividend yield of the stock
  • Freq: How many times the stock pays dividend in a year (Update: This field is no longer needed in the new version of the template)
  • Div Est: Estimated dividend payout for the year
  • DRIP: Whether we are DRIPing the stock or not
  • YOC: Yield on cost

Dividend Google Spreadsheet1

Step 3: Populate drop-down list

For headlines like Sector, Accounts, Currency, Ticker, and DRIP, I populated drop-down lists. To do so, select the desired rows then go to Data -> Validation. Under Criteria, select List of items, then enter the desired items separated by columns. I like drop down list as it saves quite a bit of time when entering data.

 

Step 4: Enter data

Enter the respective data in each row. Data like sector, accounts, currency, ticker, name, shares, cost basis, and DRIP should be pretty easy to enter.

The rest of the fields are calculated automatically based on straight forward formula.

For Cost/Share, the formula that I used was Cost Basis / Shares. For example, for Royal Bank, I entered “G3/F3.”

The two columns that required some data pulling are the Div/Sh and Yield columns. For those I used the ImportXML function which I described in my original Google Spreadsheet tutorial.

For formula requires specifying XPath from Yahoo Finance. For some reason, I wasn’t able to incorporate it into the actual formula. So in the spreasheet template, I am referencing to a cell.

Also a key thing to note is that stocks with a “.” in their symbol are labelled differently for Google Finance and Yahoo Finance. For example, Rogers would be RCI.B on Google Finance, but it would be RCI-B.TO on Yahoo Finance. Since we utilize GOOGLEFINANCE in the Price Column, and use Yahoo Finance to pull the dividend data, you will need to manually tweak the ImportXML formula slightly. Again, please refer to my original Google Spreadsheet tutorial for more information.

 

Step 5: Data conditional formatting

To make it easy to see whether you’re making a profit on a particular stock, I used conditional formatting to colour the Profit column red or green based on whether there’s any profit or not. To do this, simply select data under Profit column, then go Format -> Conditional Formatting. A conditional format rules window will pop up on the right-hand side of the sheet. The rule to use is “Format cells if Greater than or equal to 0.” Then I labelled the formatting style to use green. Next I created another conditional format rule calling out “Format cells if less than 0” and labelled the formatting style to use red.

 

Step 6: Creating charts

For this Google Spreasheet dividend portfolio template, I created 3 charts to track Sector Diversification, Accounts Breakdown, and Dividend Income Breakdown. I like charts because they give good visual summaries.

To create these 3 charts, first I had to create three separate tables with the appropriate data. To get the data needed, I used a simple function called SUMIFS. Basically the function adds up a range depending on the criteria. For example, to add up the market value of all dividend stocks in financial sectors I used the formula “SUMIFS(data in market value column,data in sector column,Financials).”  (Actual formula: SUMIFS($J$3:$J$25,$A$3:$A$25,T19)

Next I calculated the percentage of each headline.

Once I had the tables completed, I selected the data range I wanted to use, clicked on the Insert Chart button and inserted the desire chart type.

Dividend Google Spreadsheet template 3 - charts

Step 7: Currency Exchange

Because our dividend portfolio has a mix of Canadian and US listed stocks, I decided to show a breakdown and the market portfolio value in Canadian dollar.

To query the current exchange rate, I used the function GoogleFinance(“CURRENCY:USDCAD”).

The SUMIFS function was utilized again to add up the market value of all Canadian and US stocks. With the US to CAN exchange rate available, I converted the US stock value into Canadian currency to show the entire portfolio value in Canadian dollar. The breakdown percentages are also calculated in the table.

Dividend Google Spreadsheet template 2 - exchange rate

Step 8: Summary Report

I’m a very visual person so I like to have a summary report to see all the important numbers. Hence, for this Google Spreadsheet dividend portfolio template, I created a table to list all the important values like Book Value, Market Value, Profit, Estimated Annual Dividend, and YOC.

 

Step 9: Dividend Income

A dividend portfolio template is no good if you don’t have a sheet to track your annual dividend income. Hence I created a new sheet called dividend income to track annual dividend income.

A long time ago I used to only list the stock tickers in the dividend income sheet. But it was confusing for Mrs. T when I got her involved in tracking our household finance. So I added stock names to make it easier. 🙂

Dividend Google Spreadsheet template 4 - dividend income

Whenever a stock pays dividend, you can populate the data under the respective month. Once the year is over, you can create another sheet to track dividend income.

 

Final Words

If you have read this far, you are probably wondering where’s the actual template is. Here is the Dividend Portfolio Template that you have been waiting for. I have added a few notes in the document to answer some questions you might have.

Please feel free to play around with the spreadsheet and adjust things to your liking. This is a long overdue post and I hope some of you will find this dividend portfolio template useful. If you have any questions, please either comment below or send me an e-mail. Even if you don’t have any questions, I would love to hear from you on what you think of the template or if there’s something you’d like me to add in the template. One of the things I would love to add is the 5 year annualized dividend growth rate. But that requires some complicated pivot table that I haven’t gotten around to figure out yet (one day).

Now if you are looking for tops 10 Canadian dividend stocks and/or top 10 Canadian dividend ETFs to own. You can check out these pages.

Dear readers, do you have any other tips for a dividend portfolio Google Spreadsheet? How do you keep track of your dividend portfolio and income?

You Might Also Like

75 Comments

  • Reply
    Matt
    August 17, 2016 at 11:32 am

    Neat Spreadsheet.
    Quick question – why do you track Yield on Cost (YoC)?
    Curiosity?

    ie: you don’t use that for investment decisions? ie: decide not to sell an investment that has a YoC of 10% because the replacement only has a yield of 4%?

    • Reply
      Tawcan
      August 17, 2016 at 11:37 am

      Hi Matt,

      Good question! I track YOC to give me an idea how much the portfolio is yielding. It’s not used for investment decisions, it’s just for tracking purposes.

      • Reply
        Matt
        August 17, 2016 at 11:41 am

        Ok, it’s ok for curiosity sakes I suppose – I just see so many people use it incorrectly.
        Won’t ‘yield’ give you what your portfolio is yielding?

        • Reply
          Tawcan
          August 17, 2016 at 11:51 am

          Correct, Yield would give me what my market value portfolio is yielding.

          YOC would give me what I’m getting based on my cost basis. It’s just a number I like to see. It shows how important & powerful dividend growth is.

  • Reply
    fred
    August 17, 2016 at 1:44 pm

    I like this for a “snapshot in time” look at the portfolio.
    I’d like to see something that shows how the portfolio value and income changes over time.
    That may not be easy though.

    • Reply
      Tawcan
      August 17, 2016 at 2:06 pm

      Hi Fred,

      I wouldn’t call it a snapshot in time as this provides real time portfolio information. 🙂

      If you want to see how the portfolio value and income changes over time, you can simply create another sheet and tabulate the values at specific time and generate a chart to show the results. Hope this helps.

  • Reply
    Jayson @ Monster Piggy Bank
    August 17, 2016 at 2:38 pm

    Tawcan, thank you for the tutorial. Having this kind of spreadsheet in detail makes it easy to track dividend portfolio.

    • Reply
      Tawcan
      August 18, 2016 at 9:42 am

      Glad to have helped you Jayson.

  • Reply
    Dividends Down Under
    August 17, 2016 at 3:50 pm

    Really appreciate this Tawcan. At the moment we have a fairly basic Excel sheet for tracking our (Aussie) dividends. Perhaps we could use yours, or really go to down on adding more information to ours..

    Tristan

    • Reply
      Tawcan
      August 18, 2016 at 9:42 am

      Feel free to use my template or add parts of mine into your existing one. This template I just shared has evolved over time. The original template was pretty simple too.

  • Reply
    Alex
    August 17, 2016 at 5:07 pm

    will it add the dividend to the total amount of shares automatically if the drip is on ? or does it do it when you add it to the dividend income page ? also when you add another year to the dividend income page how is that done ?

    • Reply
      Tawcan
      August 18, 2016 at 9:45 am

      Hi Alex,

      No it does not add the dividend to the total amount of shares automatically if DRIP is on. It’s not possible to do because I don’t know if you are enrolled in full DRIP or synthetic DRIP. Second, I don’t know what price the DRIP shares were purchased since sometimes companies offer DRIP discount. You’d need to add this DRIP information manually to keep track of your cost basis.

      To add another year to the dividend income, I simply create a new sheet, enter all the stocks, and enter the dividends received accordingly. Hope this helps.

  • Reply
    Mr. Tako @ Mr. Tako Escapes
    August 18, 2016 at 1:42 am

    I’ve built nearly the exact same spreadsheet for my own portfolio Tawacan. I track a few different things, such as dividend growth rate, but it’s pretty much the same spreadsheet!

    Great minds think alike I guess? Cheers!

    • Reply
      Tawcan
      August 18, 2016 at 9:45 am

      Great minds do think alike.

      How do you get the dividend growth rate? Is that something you enter manually? I’m trying to figure out a way to have DGR calculated automatically.

  • Reply
    FerdiS
    August 18, 2016 at 7:48 am

    Great post! I haven’t used ImportXML myself. Learning XPath doesn’t look like too much fun.

    I’m busy with research to revisit the use of ImportHTML for pulling in data from financial websites. See http://divgro.blogspot.com/2014/06/tracking-your-dgi-portfolio.html

    Thanks for sharing!

    • Reply
      Tawcan
      August 18, 2016 at 9:47 am

      ImportHTML is similar idea as ImportXML, you’re just importing from different sources. 🙂

  • Reply
    helen7777
    August 18, 2016 at 10:48 am

    Thank you so very much for this. I really, really needed this. I looked at other similar posts and tutorials, but they were not clear to me. I have yet to try your instructions, but I’m eager to do so. Question: Can I set up 2 (or more) spreadsheets? One for my registered account, and one for my non-registered account?

    Request: Since you’re an Excel nerd, can you provide instructions on how to custom sort the Dividend All-Stars Excel spreadsheet, or the CCC lists (David Fish lists), I mean, creating a sort based on 4-5 criteria. Example: (1) dividend streak >/=8 years, (2) dividend yield >2.8%, (3) 1-Yr, 3-Yr, and 5-Yr DGR >/=5.0% and (4) by the latest dividend increase >/=5.0%. 

    Thank you.

    • Reply
      Tawcan
      August 18, 2016 at 11:09 am

      Hi Helen,

      Glad to have helped. You certainly can set up 2 or more spreadsheets if you want. The template I shared have all the accounts in one sheet for easy tracking purposes.

      Rather than using Sort you can select all the data and Filter them. The trick is to create special rules as you indicated.

  • Reply
    Investment Hunting
    August 18, 2016 at 2:55 pm

    Thanks for sharing Tawcan. Scott over at http://www.twoinvesting.com/ has been building similar spreadsheets for a while. I’m gonna take the best of yours and his and make a master version for my use. Thanks

    • Reply
      Tawcan
      August 19, 2016 at 9:18 am

      Hi Investment Hunting,

      Scott has built great spreadsheets. 🙂

  • Reply
    The Dividend Mogul
    August 19, 2016 at 8:49 am

    Tawcan,

    Thanks for sharing! As the post above mentioned – I am actually using Scott’s at twoinvesting as well, but I also have my own in which I track some additional items I like to track. Your’s did include some items for me to make mine better as well! My spreadsheet has like 10 tabs on it though. Love the simplicity.

    -TDM

  • Reply
    Jack Luo
    August 19, 2016 at 8:55 am

    Hi Tawcan,

    Great instruction.

    However, I still couldn’t get the dividend/yield formula to work:

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

    It always show up as “Error Could not fetch url: http://finance.google.com/finance?q=TSE:TD“.

    Anyways, I’ll try other work arounds.

    • Reply
      Tawcan
      August 19, 2016 at 9:20 am

      Hi Jack,

      Unfortunately Google seems to have implemented some sort of security protection to limit ImportXML function. I’ve found that if you switch back and forth between http and https the dividend/yield info would show up. Other method (not ideal), is to simply enter the dividend/yield info manually.

  • Reply
    helen7777
    August 20, 2016 at 7:42 am

    Request to readers: Would you please share your spreadsheets? It would be great to see the variations people come up with.

    • Reply
      Tawcan
      August 22, 2016 at 9:49 am

      I’d love to see other people’s spreadsheets too.

  • Reply
    Finance Solver
    August 20, 2016 at 1:28 pm

    The spreadsheet looks very clean, thanks for sharing it! I love excel and I love investing and I need to figure out how to combine the two together in the coming years. I just might use your template because I don’t think I’m that skilled at tracking information.

    • Reply
      Tawcan
      August 22, 2016 at 9:49 am

      Thanks Finance Solver. Feel free to use my template.

  • Reply
    Jack Luo
    August 25, 2016 at 3:08 pm

    Hi Tawcan,

    Not meant to comment twice here, but I noticed some discrepancies in the information provided by Google Finance, and I hope you can shed some light. Some of the information showed on Google Finance deviates quiet a bit from other sources and the official financial reports.

    For example, Shaw Communication (SJR.B). The EPS shown on Google Finance is “1.16”, which is doesn’t match anything of the EPS stated on the financial reports, either the Basic or Diluted. TMX Money, which is the alternative source I use for verifying information, is shown the EPS is “2.78”.

    I wonder how Google Finance and other sites calculate the EPS, as well as other attributes.

    This is quiet concerning since the discrepancy is huge, and could potential impact many of our investment decisions.

  • Reply
    Tawcan
    August 25, 2016 at 10:02 pm

    Hi Jack,

    I have noticed that a bit but I typically don’t use Google Finance for evaluating things like EPS. I typically use Morningstar when it comes to stock evaluation. Yes it’s quite concerning that the discrepancy is so large. Without looking into it further is it possible that the discrepancy is due to GAPP vs. non-GAPP?

    • Reply
      Jack Luo
      August 26, 2016 at 3:23 pm

      Hi Tawcan,

      When I evaluating a stock prior to buying, I almost always cross-reference some of the values against the actual financial reports.

      Speaking of the evaluation and analysis process, it will be great if you can write a post and share some of your experiences and approaches as well, I am curious to learn it from an expert like you.

      Cheers,

      Jack

      • Reply
        Tawcan
        August 26, 2016 at 3:36 pm

        Hi Jack,

        Makes sense. Cross reference is always good. Good point on evaluation and analysis process. I’ll see what I can put together.

  • Reply
    Serge
    November 20, 2016 at 8:25 am

    Serge,

    Hi Tawcan,

    I have discovered your very interesting blog yesterday as well as your dividend portfolio template which meet my own rquirements. I am just a novice in googlesheets and am sure to learn a lot thanks to your expertise.

    When I click on the titles in the panels on the right part of your article, such as “Monthly dividend stocks” for example, I am always redirected to an Amazon advertisement page and can’t read anything more. How could I overcome this please ?

    Congratulations for your fantastic job !

    • Reply
      Tawcan
      November 20, 2016 at 3:46 pm

      Hi Serge,

      Thank you. You probably clicked on the ad on the page. Just go back in your browser and you should be able to see the rest of the page. 🙂

  • Reply
    TD Direct Investing Order Types and Weekend Reading
    January 6, 2017 at 5:35 am

    […] Tawcan offers a step by step guide on using google spreadsheets to track your dividend stocks.  Here’s my article on using Google spreadsheets to make a stock watchlist. […]

  • Reply
    wealth from thirty
    April 20, 2017 at 4:08 am

    Thanks for sharing this Tawcan 🙂

  • Reply
    helen7777
    April 20, 2017 at 2:41 pm

    Thank you for sharing the Google sheet instructions. I hope to try this set-up very soon. In the meantime, I shared the link to your instructions with the Facebook group called Dividend Growth Investing. There are over 5,000 members in the group, with the vast majority in the U.S.

    • Reply
      Tawcan
      April 20, 2017 at 6:45 pm

      You’re welcome, hopefully the guide is useful for you.

  • Reply
    NJ
    April 21, 2017 at 9:00 am

    Thank you for your tips and shares. Is there any way to pull sector through any function either google or yahoo instead of manual entry?

    • Reply
      Tawcan
      April 21, 2017 at 12:19 pm

      Good question, I’ll have to do some more investigations on whether this is possible or not.

  • Reply
    Harrison Delfino
    May 9, 2017 at 6:20 am

    I had been using MarketXLS and it’s great.

  • Reply
    Lincy
    August 1, 2017 at 8:10 pm

    Hi Tawcan,

    Thanks for sharing your google sheets. I have a very basic question. How do you purchase your own stocks without a financial advisor? What brokerage do you use and how do you avoid the fees that play into buying and selling or MERs? Thank You

    • Reply
      Tawcan
      August 2, 2017 at 8:43 am

      We use TD Waterhouse and Questrade which allow us to trade stocks on our own.

  • Reply
    Mike_T
    September 6, 2017 at 5:04 pm

    Hi Tawcan
    When I load my spreadsheet (which was originally an Excel spreadsheet) and converted it to a Google sheet, I replaced my macro’s that were yahoo based (no longer free) with your formulas and queries for both dividend and dividend yield. The Import XML functions seem to work as when I open my sheet all of the 38 stocks that I own show a “… loading” for both dividend and dividend yield for upwards of 20-30 minutes before they are all displayed. When I downloaded you sample sheet, those functions load immediately. My question is, could you potentially tell me what I should be looking for that might be causing the dividend and dividend yield to load so slowly. Your comments would be appreciated. Your site is great. Thanks for allowing others to benefit from your knowledge.

    • Reply
      Tawcan
      September 7, 2017 at 11:31 am

      Hi Mike_T,

      That’s odd, Google seems to have limited the Import XML function a bit due to security reasons so you might get loading or N/A from time to time. I’ve been trying to figure out how to get past this but haven’t found a solution. 🙁

  • Reply
    Scott
    October 24, 2017 at 10:44 pm

    Hi Tawcan,

    I had a bunch of people asking me about the “Loading…” and “N/A” issues as well. Definitely very annoying thing to happen. One of the ways that seems to sometimes help is to change the http to https and then change it back when that stops working. It has something to do with the cache not clearing on Google Sheets.

    However, I recently updated my dividend investing spreadsheet to include a different method that works even better. It uses the scripting language that Google has built into Sheets. There’s more information about how I did that on my site: http://www.twoinvesting.com/2017/10/more-details-about-the-new-dividend-stock-portfolio-spreadsheet/

    Scott

  • Reply
    Bill
    October 25, 2017 at 5:27 pm

    Hi Tawcan, thanks for the walkthrough of your chart, I was looking for a way to do the yield in a separate column, and lucked out when your site came up, very much appreciated!

    • Reply
      Tawcan
      October 26, 2017 at 5:16 am

      Glad to have helped Bill.

  • Reply
    Eric Chi
    December 12, 2017 at 11:55 pm

    This is a great walkthrough. A tip: for Canadian stocks, one can go “TSE:[TICKER]”.

    Question: is there any way to automate calculating the DRIP dividends? Especially a DRIP that gives a discount? I’ve setup an Excel sheet but I have to manually update it.

    • Reply
      Tawcan
      December 13, 2017 at 10:12 am

      Unfortunately there’s no automatic way to calculate the DRIP dividends, especially when a company gives a discount for DRIP.

  • Reply
    Google finance dividend portfolio template: A Step-by-step guide – Clean biofuel and food from waste residues
    January 10, 2018 at 5:13 am

    […] Fonte: Google finance dividend portfolio template: A Step-by-step guide […]

  • Reply
    Aaron
    February 4, 2018 at 11:33 am

    Thank you very much for this great spreadsheet. I follow your site closely. I have been inputting my portfolio into your spreadsheet and ran into a bit of a problem when it came to adding my USA stocks. For example, when I added BLK, I used USA currency (left side of table), added my book value (taken from brokerage) under the cost basis , which came up with the correct cost/share in USA dollars, however the profit and market value numbers are incorrect. Somewhere along the line it must not be reading the currency exchange properly, as when I add my Canadian stocks with the same method, my profit or loss is bang on to what my TD Waterhouse account shows. Is there a manual conversion that I need to complete? Any help would be great. Thank again for this great spreadsheet.

    • Reply
      Tawcan
      February 4, 2018 at 2:13 pm

      Is the currency conversion not working properly as expected? You need to make sure that you mark whether stocks are CAN or US.

  • Reply
    Aaron
    February 4, 2018 at 3:19 pm

    Thank you for the quick response. I have marked the stock US in this case and the exchange remains off. Maybe some more into might help solve my problem.

    I use TD Waterhouse. When I input my average cost per stock, TD Waterhouse uses CAD, which I have inputted into the spreadsheet under cost/share. I used the book value from TD Waterhouse, which also appears to be in CAD and inputted that into ‘cost basis’

    As an example, I inputted GOOG into the spreadsheet. TD Waterhouse has my average cost/share as $1,248 CAD and the cost per share as of Friday’s close was $1,111.90, hence the spreadsheet says I am down but given the exchange I am up more than 10%., which is not reflected on the spreadsheet. The cost basis or book value that I added to the table was in CAD, taken from TD Waterhouse. I think I am missing something that is straight forward but can’t figure it out. Any more help would be great.

    • Reply
      Tawcan
      February 5, 2018 at 12:51 pm

      Hi Aaron,

      I think the problem is that you put average cost/share with CAD and the price is being pulled as USD. What you need to do is cost/share in USD for the spreadsheet to calculate the correct gain. Hope this helps.

  • Reply
    Ian Bremner
    March 27, 2018 at 12:43 pm

    Hey Bob,
    Great info on your website. My question is this… I am currently using your spreadsheet to rebalance my ETFs portfolio and a couple of my ETFs(IEFA, IEMG) are now not being updated by Google finance. I just get an “N/A” in the cells that track name and current price. I have tried adding the exchange prefix for the ETFs and it still does not work. Without that data I am not able to rebalance. Any workaround?

    Thanks
    Ian

    • Reply
      Tawcan
      March 27, 2018 at 1:49 pm

      Looks like Googlefinance function is having some hiccups the last few days. TSE stocks don’t seem to return any price info. Hopefully Google will get there stuff together soon and fix this.

  • Reply
    Wayne
    April 2, 2018 at 3:32 pm

    Hi Bob,
    Thanks for all the work you’ve put into this. Very glad I found your website through RFD.
    Could you help point me in the right direction? I’m having trouble pulling the div/share data from yahoo finance, it appears the xpath is pointing sometimes to other data such as 3y beta.
    not sure if i’ve entered something wrong, part of me suspects it’s because the quote pages have different fields depending on the ticker symbol,
    Wayne

    • Reply
      Tawcan
      April 2, 2018 at 9:11 pm

      Did you modify the XPath in the original spreadsheet? The path should be correct and you shouldn’t be getting 3y beta.

  • Reply
    Wayne Chiu
    April 3, 2018 at 7:17 pm

    didn’t change xpath – i think i figured out why I’m pulling weird numbers for some of my holdings. it looks like XPATH is pulling the correct div info for individual stocks, but for ETFs the 6th field is 3y beta. (XLF pulling 0.92)
    thanks again!

  • Reply
    Jean
    April 18, 2018 at 5:56 pm

    Hi
    My sheet worked well for a few days but now some dividend data is incorrect and others are OK with the same formula. AFL returns 43 104,00 for dividend (actualy it is 1.04$) and the yeald is OK at 2.33%.

    Yahoo changed some of the data format?

    Thank you.

    • Reply
      Tawcan
      April 18, 2018 at 8:08 pm

      Hi Jean,

      Hmm I don’t see that. When I plug in AFL in the dividend spreadsheet the query returned 1.04 and 2.33%. Exactly what showed up on the Yahoo Finance page.
      https://finance.yahoo.com/quote/AFL?p=AFL

      Did you change the XPath somehow?

  • Reply
    Barry
    April 25, 2018 at 12:12 pm

    Hi TAWCAN
    I just wanted to say thank you for posting a method for importing dividend amounts and yields . It is really helpful and very much appreciated .
    Best

  • Reply
    Crockerfeller
    May 14, 2018 at 2:07 pm

    Hi, This has been very helpful for me.

    Can you shed some light on how to alter the XPath so that I can collect a different data point?

    For example I would like to get the Payout Ratio value from the Statistics tab of a stock quote. I see your XPath is pointing to:

    //*[@id=”quote-summary”]/div[2]/table/tbody/tr[6]/td[2]

    how could you modify this Xpath to collect Payout ratio from the statistics tab of a quote?
    For example…. Verizon (VZ) statistics are found here: https://finance.yahoo.com/quote/VZ/key-statistics?p=VZ

    I see the payout ratio under Dividiends & Splits. How would I identify the correct Xpath for that data point?

    • Reply
      Tawcan
      May 14, 2018 at 2:38 pm

      The easiest way is to use FireFox and use the Web Developer tool and inspect the code of the page. Once you find the code, then right click Copy -> XPath, then you need to clean up the XPath a bit.

      For example, the payout ratio entire XPath would be:

      /html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/section/div[2]/div[2]/div/div[3]/table/tbody/tr[6]/td[2]

      But you need to clean it up. You also have to point the concatenate formula to the correct URL.

      It’s a bit of trial and error. 🙂

  • Reply
    Kimm
    June 7, 2018 at 9:21 am

    Hello Tawcan, have you determined how to obtain the DGR?

    Regards

    • Reply
      Tawcan
      June 8, 2018 at 9:23 am

      Hi Kimm,

      There are ways to do that but they all involve some sort of manual work. There are websites that have DGR numbers you can pull from but most of these sites require monthly payments.

  • Reply
    Brandon Fong
    June 9, 2018 at 6:14 pm

    Hi Bob,

    Great insight and information. I came across your page as I was searching for a dividend portfolio template to reference in excel. Would you happen to have a google spreadsheet to share?

    • Reply
      Tawcan
      June 9, 2018 at 10:55 pm

      Hi,

      At end of the article you can download the Google spreadsheet template.

  • Reply
    Vincent Lin
    June 14, 2018 at 3:42 pm

    Hi.

    Thanks for the spreadsheet. I primarily use the function to pick up dividend and yield info from XML parsing from Yahoo Finance. It works great for my stocks. Yet, it always returns N/A for stocks that pay out dividend “monthly”. I will give you some examples, tickers such as “DMO”, “ETJ”, “GOF”, and “IGD”. Can’t figure out why so. Will you take a look at that? Thanks.

  • Reply
    Dividends on the Prairies
    June 15, 2018 at 10:44 am

    I love your template and use it all the time!! Thank you so much.

    Did you notice that the price function is no longer working for Canadian stocks? For example GoogleFinance(RY.TO,”PRICE”) now returns #N/A. Does anyone know how to fix this issue?

    • Reply
      Tawcan
      June 15, 2018 at 1:00 pm

      I noticed that earlier today. You can also try using TSE:RY instead of RY.TO in the formula.

  • Reply
    Cris
    July 13, 2018 at 10:19 am

    Hi Tawcan

    I don’t know too much about Excel and I wanted to ask if you have a template which can be downloaded.

    Thank you

    • Reply
      Tawcan
      July 13, 2018 at 10:35 am

      The template is at the end of the post for downloading.

    Leave a Reply

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