st: Calculating prior consensus under multiple conditions

[vskirnev@smail.uni-koeln.de]

Looking at this mailing list has been a great help for my research  
project so far - I hope you can help me with my current problem.

I need to calculate a prior consensus analyst earnings forecast (mean  
of prior forecasts by other analysts). This calculation needs to be  
done only for the last forecasts by every analyst for a specific  
period (e.g., a financial quarter). I have tagged them with variable  
last=1 (previous forecasts by the same analyst in the same period have  
last=0).

So the logic of this calculation looks like this:

If an observation is last=1 then {

go back 90 days from the date of this forecast (variable 'fordate')  
and calculate mean of variable 'value' (what was forecasted) using:

- forecasts of OTHER analysts (there is a variable 'analyst' unique  
for each one)
- of those use ONLY the last ones - meaning if within last 90 days  
analyst A issued 4 forecasts for the same company, I need to use only  
the most recent one

}

This has to be repeated for all observations with last=1.

My attempts so far were not successful, so I hope you can give me some  
good advice.


Just as an example of how data might look like - assuming all these  
were done for the end of 4th quarter (31dec2010):

Company  Analyst  ForecastDate  ForecastValue Last
3M       A        15oct2010     80            0
3M       B        17oct2010     85            0
3M       C        21oct2010     86            0
3M       B        07nov2010     79            1
3M       A        21nov2010     75            0
3M       A        29nov2010     83            1
3M       C        13dec2010     84            1

Prior consensus has to be calculated three times: for observations 4, 6 and 7.

For 4 (analyst B): consensus = (obs1 + obs3)/2
For 6 (analyst A): consensus = (obs3 + obs4)/2
For 7 (analyst C): consensus = (obs4 + obs6)/2

I hope this clarifies the problem. Thanks for your input in advance!


Vitaly Skirnevskiy
Graduate Student

+49(0)221 168 163 38
University of Cologne
Albertus-Magnus-Platz
50923 Cologne
Germany

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/