Sometimes the simplest information is the hardest to find.
Investors used to have to wait until the annual Beating the TSX update in The Canadian MoneySaver to have access to the portfolio. Now you can find it here any time.
But what if you want the complete list of TSX 60 stocks organized by dividend yield? Perhaps you want to build a portfolio of more than ten stocks? Or maybe you just need a starting point to investigate large cap Canadian equities with characteristics other than high yield (dividend growth rate, other sectors, market cap, etc.). It would be handy to have an up to date spreadsheet to refer to, especially one that you could copy and paste into your own software.
Alas, what should be readily available to DIY investors has been irritatingly hard to find.
- TMX Money has the list of TSX 60 constituents, but lacks dividend data
- Sure Dividend has a list, but it’s out of date and requires you to give your email and download a file
- Dividendyields.org has a list which is also out of date, containing stocks that are no longer part of the TSX 60
- The Globe and Mail used to have a TSX 60 table, but stopped updating it last year
Time to fix this problem. As of today, you will be able to access the complete list of TSX 60 stocks by dividend yield right on this website from the navigation bar. Here is what I did (feel free to make your own, or just keep coming back here):
The method – updated
1.Compiled the entire TSX 60 index as a portfolio in Yahoo Finance
2.Edited the columns to include the relevant data
3. Copy and pasted this data into Google Sheets and changed the ticker symbols to the Google format: “TSE:BCE” (for example). Then made it look nice with some colours.
4. Use the following command to pull real time prices:
=GoogleFinance(“TSE:TICKER”,”PRICE”)
5. Use the following command for 52 week low:
=GoogleFinance(TSE:TICKER,”low52″)
6. Use the following formula for Change from low:
=(last price – 52w low)/(52w low)
7. Used the following command to calculate dividend yield:
=annual div/last price
8. Use the following command to pull real time market cap:
=GoogleFinance(A5,”marketcap”)
9. Used the following command to pull real PE ratio:
=GoogleFinance(A5,”PE”)
10. Use the following command to pull real time beta:
=GoogleFinance(A5,”beta”)
11. Sort the data based on dividend yield by highlighting a cell in the dividend yield column and using the “Data – Sort” function:
That’s all! It’s not rocket surgery, but no one else seems to be doing it.
Notes
Please note: This data is pulled from Google Finance and may be delayed by about 20 minutes. Also the feed may be temporarily interrupted on occasion – if it is, check back later.
Also, note that the actual dividend amounts are entered manually (unfortunately, there is no way to pull this data from Google Finance). I will endeavor to be on top of dividend raises and cuts, but if you are using this data to make investment decisions, please (as always) double check this information.
Fantastic Matt. Well done. May I suggest one small enhancement? Perhaps you could include the price date in the header. So when I copy into my own spreadsheet I have a reference date of the price. Keep up the good work! Geoff W
Will do, Geoff.
In fact, David Stanley has directed me to some additional resources that will make the spreadsheet even better. I am working on making it more comprehensive with automatically updating data.
Great work Matt. I have been investing for 24 years and finding a source of information about Canadian stock is frustrating. I have used many of the sites you talked about including Canadian Moneysaver. Thank you
Wonderful! Great job!
Thank you for this information. New to BTSX and self management. It would be great to have a Starter Toolkit/Manual to get one up and running.
Hi Nancy, feel free to send me an email telling me what kind of information you had in mind: contact@dividendstrategy.ca
Hi Matt, I note with much interest your process to import current (delayed) ticker price into Yahoo finance: Use the following command to pull real time prices: =GoogleFinance(“TSE:TICKER”,”PRICE”) Do you know if there is a similar formula that can be entered direct into an MS Excel cell to get a similar ticker price? Perhaps Lookup? Thanks a lot – keep up the good work.
I don’t have excel anymore, but I believe the same command will work. Perhaps you can try it out and let us know?
Hi Matt, I already tried your syntax in Excel to no avail. Thanks
Hi Matt. I tried it and ‘no cigar’. I can get to a linked web page using an insert command, but not the actual ticker value. I guess my XL skills are limited! Cheers, Geoff W
Thank You Matt, I have subscribed to Money Saver for many years following David Stanley’s Beating the TSX! It has made our retirement so much more enjoyable. I have enjoyed following you and your family’s travels around the world and you will soon be on your way home! Pleased to have you following in David Stanley’s footsteps Beating The TSX !!! Good Luck Herb
Has anyone used the data and model in Mac Numbers? Any issues?
I was working with IEX interface to get at some data….
However, it lacks the dividend information on stocks listed on the TSE.. Also as of late its gotten to be extremely slow. Will update everyone if I can get it working again.
Pingback: The alpha and beta of dividend investing —
Hello Matt.
This is great work. Thanks for providing it. Quick question. I assume you are sharing an Google sheet, right? By any chance, do you know of a way to get TSX dividends in Microsoft Excel. So weird that Microsoft Stocks Data Types doesn’t provide Dividend info. Basically I just need the Ex-Dividend and the pay amount. If there is no easy way, I will have to develop and pull the info with a Questrade API. Kind of a crazy way to do it for such a thing.
Yes, I use Google Sheets. Unfortunately, to my knowledge, there is not way to pull Canadian dividend data for either Excel or Sheets. I input that data manually using Yahoo! Finance as a source. It’s a bit of a pain, but I know it’s useful for a lot of people.
Any thought to making the Google doc you have PUBLIC ?
I’ve done that with other spreadsheets I have so that others can get the data anytime they wish….