Analyst price targets and recommendation ratings are one of the criteria you can use to help evaluate stocks for the next year. An Excel spreadsheet is provided that can be downloaded where you can enter data to compare stocks. It also includes an area where you can download similar values from FinanceBase-Accounts to evaluation how your stocks have done over the past year and since you bought them.
Analysts spend a lot of time and effort on evaluating stocks and then making recommendations on the performance and future prospects of stocks. You can view summaries of these recommendations on a number of websites where you can enter a company and they will provide information. Three that I have found to be very informative are Yahoo, MarketBeat and TipRanks. As you enter the stock symbol you can select which exchange to use if it listed on more than one.
If you use a bank or other financial institution that provides trading of stocks you will also have access to their research.
The stock exchanges provide a lot of details on the past performance of stocks such a graphs and day by day prices, but not normally on future analysis.
While there are a lot of companies that provide analysis and recommendations for a fee, and I do not make a living from buying and trading or have a lot of money to be invested, I do not use them.
Download the Spreadsheet
The spreadsheet provided makes it easy to see which stocks that you have entered have the potential to meet your desired return. Click on Download Spreadsheet and then open it with Excel. It is empty but has 4 columns where you can enter stock information from whatever source you find most useful.
To add more columns, just copy the last column and paste it into one or more columns to the right.
Printing has been set for 4 stocks per page.
Each line on the spreadsheet is numbered and the numbers are used in the description below. These are different than the Row numbers.
Only enter data into the cells that are outlined. Changing any of the other cells will change the results and may change the background color of lines 18 to 21 and 33 to 35.
I used Yahoo for the examples below because it has many analysts, albeit Yahoo do not indicate the dates used and they could be as old as a year. You may want to use a source that provides when the analysts made the recommendations.
Spreadsheet Description – Desired Percent
1) % Desired per year – Enter the desired return you want from the stock. The returns calculated in lines 18 to 21 and 33 to 35 use the value entered. It they are below the desired percent, the background is red. If above, it is green. This color coding, plus the use of red text for negative numbers, makes it easy to see how a stock is expected to perform in the next year or has performed in the past year.
Spreadsheet Description – Next Year
There are a lot of cells that you can enter data into, but only a few are really needed to perform the calculations:
- – you need to identify the company in line 2 or 3,
- – provide the Dividend Payout (if provided) in line 9,
- – the Evaluation Price in line 11 and
- – the three target prices in lines 14, 15 and 16.
The remaining cells are there to provide more information for you when you come back a few months later to see how the stock is performing.
2) Name – This is the name of the company.
3) Sector – This can be found in a variety of places. Use this when you want to keep track of the companies that are in similar business.
4) Market Cap ($B) – This is useful when comparing companies in the same sector.
5) Evaluation Date – Enter the date that you use for the data below. It important to enter this date if you want to examine the analysis at a later time.
6) Source – Enter the source of the data that follows.
7) Symbol – Enter the Symbol used with the Source of line 6. Note that for the examples shown, the Yahoo symbol has a suffix of .TO for stocks on the TSX exchange. The full website address for the Enbridge example is https://ca.finance.yahoo.com/quote/ENB.TO/ which opens on the Summary tab. To show another company, change the symbol in the web address.
8) P/E Ratio – This is the Price to Earnings Ratio and is one of the most used metrics to evaluate a stock. It is most useful when comparing companies in the same sector and industry. For Yahoo, this is found on the Statistics tab. It has Trailing and Forward values, so be consistent on what you use.
9) Dividends Payout ($) – For companies that issue dividends, enter total for the year. For Yahoo, this is found on the Statistics tab. It has Trailing and Forward values. It is best to use the Forward value as you are evaluating the stock for the next year.
10) Dividends Payout Ratio – For Yahoo, this is found on the Statistics tab. It indicates how much of the earnings the company is paying. For the Enbridge example it is over 100% which should indicate it is not sustainable and should be examined in more detail before discarding this stock. For example, this article by The Motley Fool indicates that the situation is not a bad as it seems.
11) Price ($) – This is the price of the stock on the Evaluation Date. It is used in calculations below. For Yahoo, it is in the top-left corner of the page (below the always present advertisement),
12) Analysts – Enter the number of analysts that have been used in the evaluation. For Yahoo, this is on Summary or Analysis pages and is near the bottom on the right. Yahoo has a graphic which shows the total number and what they have recommended. This is why this line on the spreadsheet has (Sell, Under, Hold, Buy, StrongBuy). If you want to keep track of these values, enter them into the cell for the company. For example, Enbridge has 16 (0,0,7,8,1).
13) Analyst Rating – The ratings give you an indication of what analysts thing of the future performance of the stock. Most sources provide some rating, but they are not all the same. Get familiar with the meanings. For Yahoo, the rating is just below the Analysts numbers and is a value between 1 for a String Buy to 5 for a Sell with Hold being a 3. Enter the number or text as desired. For the example, I have entered both: 2.2 Buy/Hold.
14) Target Low ($) – Enter the lowest stock price provided by the analysts that were included in the rating. This is the lowest stock price expected 1 year from when they evaluated the stock. This may not be the Evaluation Date of line 5 above. For Yahoo, this is shown in a graphic below the rating. It also shows the current stock price.
15) Target Avg ($) – Enter the average stock price of all the analysts included in the rating. For Yahoo, this is shown in a graphic below the rating.
16) Target High ($) – Enter the highest stock price provided by the analysts that were included in the rating. This is the highest stock price expected 1 year from when they evaluated the stock. This may not be the Evaluation Date of line 5 above. For Yahoo, this is shown in a graphic below the rating.
17) Dividends Yield (Div/Price) – This is calculated from the Dividends Payout of line 9 and the Price of line 11. It is used on the Total Gain of line 21.
18) Target Low to Price – This is calculated from the Target Low of line 14 and the Price of line 11. If it is negative the number is red. If it is below the Desired percent of line 1, the fill is red, other wise it is green.
19) Target Avg to Price – This is calculated from the Target Avg of line 15 and the Price of line 11. If it is negative the number is red. If it is below the Desired percent of line 1, the fill is red, other wise it is green.
20) Target High to Price – This is calculated from the Target HIgh of line 16 and the Price of line 11. If it is negative the number is red. If it is below the Desired percent of line 1, the fill is red, other wise it is green.
21) Total Gain (Div+Avg) – This is calculated by adding the percentages of Dividends Yield of line 17 to the Target Avg to Price of line 19. If it is negative the number is red. If it is below the Desired percent of line 1, the fill is red, other wise it is green.
22) Performance – After evaluating the percentages, P/E, and any other metrics you normally use, enter what you think of the future for the stock. It can be any text you want. The cell will expand to fit the text.
Spreadsheet Description – Existing Account
You can enter data into this section of the spreadsheet for any stock that you already own. It consists of 3 sections: stock description and buying details, past returns, returns from the buy date and analysis of performance. If you use Accounts in FinanceBase, the past returns section can be downloaded and pasted into the area.
23) AccountName – Enter the name you have given to the stock. If Accounts is used, it is the AccountName field.
24) Industry – Enter either the sector or the industry in which the company competes. This will help you compare stocks of similar companies.
25) Buy Price – Enter the price at which you bought the stock.
26) Buy Date – Enter the date when you bought the stock.
Lines 27 to 38 (outlined with a dash) can be downloaded from FinanceBase’s Accounts as follows:
- a) Open a List window of your accounts.
- b) Select all of the accounts in the window.
- c) Under the Accounts add-on menu, select the “Accounts Export Shares Returns” item. This will ask you to name the text file to be exported. Place it in whatever folder is convenient for you. Only accounts with an AccountType field of Share(s) or Mutual will be exported. A Result window will open that indicates how many accounts have been exported and where the file is located.
- d) Open the exported file in Excel. Each account will be in a row with a number of columns.
- e) In the row for the account you want, select the cells starting at column C and going to column N. Using the Edit menu,copy these cells.
- f) Go to the spreadsheet and select the cell of line 27 (Return) and the coloumn of the account you have copied.
- g) Using the Edit menu, Paste Special using Formulas and Transpose. This will preserve the formatting and change the horizontal order to vertical.
- h) Repeat e to g for each stock you want on the spreadsheet.
If you do not use Accounts, you must either enter the data for whatever lines you want from you own analysis or skip to line 37. For details of each of lines 27 to 38, please refer to the FinanceBase User Manual.
27) Return – This is the return that you getting from the stock. If you use Accounts, the next line indicates which option is being used.
28) Using – This indicates which of the 7 options below (lines 29 to 35) you are using for the return of line 27.
29) Last Month – The change in the stock in the last month.
30) Linear Best-Fit – The long-term change in the stock value.
31) Compound Annual – The change in the stock using a compound annual percent since the buy date.
32) Compound+Dividends – The change in the stock using a compound annual percent since the buy date but including any dividends.
33) 1 Year Calculated – The change in the value of the account in the past year using the Calculated amounts on the Entry Page of the Account.
34) 1 Year Shares – The change in the price of the stock in the past year.
35) 1 Year with Dividends – The change in the value of the account including Dividends in the past year.
36) % Cum Gain/Start – The percent the account has gained since the buy date.
37) Evaluation Date – The date for the above values.
38) Evaluation Date Price – The stock price on the evaluation date.
39) Years Held – The number of years the stock has been held is calculated using the Buy Date and the Evaluation Date.
40) $ Price Change From Buy – This is calculated using the Buy Price and the Evaluation Day Price.
41) % Price Change From Buy – This is calculated using the Buy Price and the Evaluation Day Price.
42) Change in Gain Past Year – Enter any text to describe in a few words that describes how the stock price has changed in the past year. You can get this from the Accounts Input window or any source that charts the stock prices. Use it in the Decision below.
43) Decision – Enter a few words that describe what you want to do with the stock for the next year. Use the information in the Next Year section of the spreadsheet plus how the stock has done in the past year.
44) Decision Details – Enter more information on what went into your decision. This will be very helpful when you evaluate how the stock performed next year.