In looking at this type of data, I've always been curious as to which comes first the price target or the price. As in do price targets follow a rise or drop in the price of a stock, or are stock prices actually influenced by the recommendations of an analyst? To help me look analyze this, I put
together a spreadsheet in Google Docs.
This spreadsheet has 3 different worksheets in it. Each of them are detailed below:
This worksheet makes the call to the Smart Money page. The call is made using the ImportHtml() function in cell B4. The url that is input to the function is a concatenation of the symbol and the base url for the Smart Money page. The results are then returned in columns B through G. In column H I then concatenate the data that I am interested in displaying in my Chart that will be on my Price Target worksheet. In column O I leverage a simple function where by I am calculating the average of the last 10 price targets. Again the purpose here is that I want to plot this as a simple moving average value in a chart.
This worksheet serves two purposes. The first is to access historical pricing data for the stock being analyzed from Yahoo Finance. This is accomplished by using the ImportData() function in cell A10 which is a concatenation of the base url from Yahoo Finance and the symbol being analyzed. We also pull in real time data from Yahoo which is being used in the header section of the following worksheet.
The PriceTarget worksheet is where the user specifies the symbol being analyzed in cell c2, and includes the Interactive Time Series google gadget. The data to be displayed in this gadget is pulled into columns H through L, which is pulled from both the Data and SmartMoney worksheet. The resulting chart shows the date as the x axis, price, price target, and moving average price target on the y axis. It also provides an overlay that displays the firm, their action, the resulting action, and the target price on the specific date.
One of the really nice features of this gadget is that you can filter the overlays that are displayed on the chart. So, if you just want to see price targets from UBS, you can set the filter accordingly. Same is true if you want to see just buy or sell actions. Essentially any text can be filtered on.
A screenshot of this spreadsheet is provided below / on the right.
One of the down sides I found of using the google gadget is that if the gadget is towards the top of your spreadsheet you can't see the menus of the spreadsheet as they always pop up behind the gadget. Same is true within the gadget itself. Also, I was extremely frustrated when trying to size the gadget appropriately as I got multiple errors when trying to resize the gadget. In the end I had to make multiple gradual size changes and it finally worked.
Now I can't say that I have answered my initial question, and I have a feeling the answer (as always) is going to be it depends. As in sometimes price follows price target, and other times price targets will follow price. But regardless it was an interesting exercise and hopefully provides an interesting example of how the Interactive Time Series gadget can be used in a spreadsheet.
This spreadsheet has been shared, and is accessible here:
It is also available as a template here:
Scroll down to see the Price Target template.
As always, please let me know what you think.