IBES detail unadjusted file duplications
Posted: 12 March 2014 09:41 AM   [ Ignore ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Hello Joost and Everyone here,

After I delete the stopped and excluded observations from the detu_epsus (for quarter data only) file, I sort nodupkey with ticker fpedats anndats analys, I found 53,000 duplicaitons out of 8,592,000 observations (~0.6%).

I just want to confirm with everyone here having similar experience with me about this data (or Am i missing anything here?).

Thanks and have a great day!
Zenghui

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 12 March 2014 09:50 AM   [ Ignore ]   [ # 1 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

/*here is my code for the above job*/

/*setup the periododicty of data from FPI variable in the detail data for future merge purpose*/

  %let afpi = ‘XIHGFE54321’;
  %let qfpi = ‘YQPON9876’;
  %let sfpi = ‘ZDCBA’;
  %let lfpi = ‘0’;
  %let ufpi = ‘RST’;

  /* for fpi = R , S , T, the fpedats - anndats range from 2-3 years,IBES manual didn’t explain, it could be qtr or ann*/
  /* therefore, i left them as unknown FPI */

data detu1;
set ibes.detu_epsus;
if index(&afpi;,fpi) >0 then pdicity = ‘A’;
else if index(&qfpi;,fpi) > 0 then pdicity = ‘Q’;
  else if index(&sfpi;,fpi) >0 then pdicity = ‘S’;
  else if index(&lfpi;,fpi) >0 then pdicity = ‘L’;
  else pdicity = .; /*unknown*/

if USFIRM ne ‘1’ then delete;
if report_curr ne ‘USD’ then delete;
if missing(fpedats) then delete;
if measure ne ‘EPS’ then delete;
if missing(anndats) then delete;


drop OFTIC CNAME CUSIP curr CURRFL; *if missing(CURRFL) = 0 then delete; /*WRDS suggests ignore this variable Q100 of IBES FAQ*/

key_obs1 = ticker||fpedats||anndats||analys;

if pdicity = ‘Q’ then output;
*if year(fpedats) >=1987 and pdicity = ‘Q’ then output;
*if ticker in (‘AMZN’,‘IBM’ ) and fpedats = ‘30sep1999’d and pdicity = ‘Q’ then output;
run;

*Based on IBES FAQ on exc and stop files:
Basically, if you want to remove stopped and excluded estimates
(see more in the IBES manual on what those are),
you need to merge the Detail history using left join in sql with EXC table (for “excluded”)
by ticker, fpedats (fiscal period end), estimator, analys, fpi, 
measure and actdats and remove all estimates by the same estimator-analys
which were issued after excdats
(i.e., if anndats>excdats then delete).

You follow similar logic when you bring stopped estimates.;

PROC SQL;
  create table detu2 as
  select a.*, b.EXCFLA as exc_flag
  from detu1 a  
    LEFT JOIN
  ibes.excu_epsus b
  on
  a.ticker =  b.ticker
and
      a.fpedats =  b.fpedats
and
  a.estimator = b.estimator
and
      a.analys =  b.analys   /*forecasts from other analyst*/
and
  a.fpi =    b.fpi
  and
      a.anndats >=  b.excdats /*if anndats>excdats then delete*/
  ;
quit;


data detu2;
set detu2;
if missing(exc_flag) = 0 then delete;
run;


/*
Variables used in STOP file;

ticker
cusip
oftic
cname
astdats
estimator
pdcity Q L A, L = long term—-> actual file has this variable
measure
usfirm
fpedats

astptims—> stop time

*/


PROC SQL;
  create table detu3 as
  select a.*, b.ASTPDATS as stop_flag
  from detu2 a  
  LEFT JOIN
  ibes.stopu_epsus b
  on
  a.ticker = b.ticker
and
  a.estimator = b.estimator
and
  a.pdicity = b.pdicity
and
  a.fpedats = b.fpedats
  and
      a.anndats >= b.ASTPDATS /*if anndats>excdats then delete*/
  ;
quit;

data detu3;
set detu3;
if missing(stop_flag) = 0 then delete;
run;

proc sort data = detu3 nodupkey out = detu4 dupout = test;
by ticker fpedats anndats analys;
run;


data detu_ready;
set detu4;
drop stop_flag exc_flag;
run;

 

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 12 March 2014 10:14 AM   [ Ignore ]   [ # 2 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Zenghui,

If I understand correctly, the ANNDATS is the date that the analyst has issued the forecasts, and each period is a row in the detail file. So, in a single ‘action’ an analyst can issue several forecasts (q1, q2, fy1, fy2, growth, etc).

Could it be that after the analyst has filed the forecasts, he changes his mind on portions and resubmits? Obviously, the forecasts that changed will not be a duplicate, but the unchanged periods may enter the dataset another time. I suppose the quickest way to verify this is to inspect a few of these cases and look at the activation time.

Thanks for the code, by the way; I didn’t realize stopped/excluded forecasts needed attention.

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 14 March 2014 09:48 AM   [ Ignore ]   [ # 3 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

       
         
IBES Ticker Activation Date, Contributing Analyst Code Primary/Diluted Forecast Measure Estimate USFIRM=0 Forecast Period Activation Time, Review Date, SAS Review Time, Announce Date, Announce time,
Symbol SAS Format estimator Flag (Estimate Period (Data Type Value if from End Date, SAS SAS Format Format SAS Format SAS Format SAS Format
  code Level) Indicator Indicator)  .INT file Format  
      and USFIRM=1    
      if from    
      .US file    

[Edit Joost: removed the data]

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 14 March 2014 09:55 AM   [ Ignore ]   [ # 4 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Joost,

By accident I posted the duplications from my program. Please help me to delete above data.

from the duplication pattern, it seems the activation time are similar however the revdats are different. Maybe I should just take the later revdats observation for the duplications.

another quick question here. IBES said they already adjusted Primary and Diluted to the Diluted basis, does that mean I don’t need to worry about this at all when I use the unadjusted detail data?

thanks,
Zenghui

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 14 March 2014 10:01 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Zenghui,

I edited the data in your post; indeed, the posts are not 100% duplicates, there were differences between some variables.

About primary vs diluted: do I understand correctly if your question is if the unadjusted data is adjusted for primary vs diluted? I am not sure; where does IBES mention this?

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 14 March 2014 10:52 AM   [ Ignore ]   [ # 6 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Joost,

1. Since the 0.6% is low, and I am not using other variables (activation time), I guess it is ok to sort these duplication obs out of my sample. Agree?

2. in IBES detail manual, they mentioned:
Diluation factor, and currency factor in the detail estimate file, from the manual of Detail file:
It is very important to note that all data in the Detail file is completely adjusted. That is, if an analyst’s
forecast is received with different Currency or Primary/Diluted indicators on the estimate level versus
the existing company level, the estimate value will appear as adjusted to the company level basis. The
indicators reflect the basis in which the estimate was received. Users may “unadjust” the data if they
choose using data provided in the Identifier or Exchange Rate files.

Thanks for the discussion,
Zenghui

 Signature 

Zenghui
A humble student of business

Profile
 
 
Posted: 14 March 2014 11:03 AM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Zenghui,

Thanks for clearing that up. Dropping duplicate forecasts made on the same day makes sense to me smile

best regards,

Joost

 Signature 

To reply/post new questions: Please use the group WRDS/SAS on Google Groups! http://groups.google.com/d/forum/wrdssas

Profile
 
 
Posted: 14 March 2014 12:59 PM   [ Ignore ]   [ # 8 ]
Sr. Member
RankRankRankRank
Total Posts:  169
Joined  2011-09-20

Thanks a lot.

 Signature 

Zenghui
A humble student of business

Profile