Merging IBES with Compustat (WRDS)
Posted: 13 August 2015 08:23 PM   [ Ignore ]
Newbie
Rank
Total Posts:  2
Joined  2015-08-12

Hi,

I am trying to merge financial statement variables in Compustat with analyst forecasts in IBES using Stata. I have downloaded the two data sets separately. I tried to contact WRDS to seek help on unique identifiers and it seems from their reply that I have to link the GVKey string in Compustat to the IBES Ticker string in IBES. However, they are unable to provide a detailed method on how to do it. I wish to merge the two data sets by company and year (for example, one row of the merged data set would contain financial statement variables from Compustat for a particular year and analyst forecasts for that year from IBES).

Could any of you guide me on what is the best way to do this and where do I start?

P.S. WRDS states that no extra work is needed for linking IBES with Compustat, since Compustat already provides the linking information in table ‘SECURITY’(/wrds/comp/sasdata/nam/security) for North America companies. I have only North American companies in my sample, but I do not know how to use this table.

Thanks much!

Profile
 
 
Posted: 14 August 2015 07:20 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Rishi,

As far as I know, the match between Compustat and IBES is not easy.

WRDS has a SAS script that makes a link between permno (CRSP) and IBES ticker, see https://wrds-web.wharton.upenn.edu/wrds/support/code_show.cfm?path=I-B-E-S/iclink.sas

If you run this remotely (unfortunately for you, using SAS rsubmit, can’t run it with Stata), it will create a link file in your WRDS home directory. You would need to get permno for this approach (see next).

If you don’t want (or can’t) use this, an alternative way is to:
- match Compustat with CRSP using crsp.ccmxpf_linktable (and get a permno for each gvkey-year)
- using the permno, get historical cusip from crsp.dsenames
- using the historical cusip, get ibes ticker from ibes.idsum

SAS code for inspiration:

data a_funda (keep key gvkey fyear datadate conm);
set comp.funda;
/* limit to firms with more than $20 mln sales and fiscal years 2010-2012 */
if sale 20;
if 
2010 <= fyear <= 2012;
/* create key to uniquely identify firm-year */
key gvkey || fyear
/* general filter to drop doubles from Compustat Funda */
if indfmt='INDL' and datafmt='STD' and popsrc='D' and consol='C' ;
run;
 
/* get permno */
proc sql;
  
create table b_permno as
  
select a.*, b.lpermno as permno
  from a_funda a left join crsp
.ccmxpf_linktable b
    on a
.gvkey b.gvkey
    
and b.lpermno ne .
    and 
b.linktype in ("LC" "LN" "LU" "LX" "LD" "LS")
    and 
b.linkprim IN ("C""P"
    and ((
a.datadate >= b.LINKDT) or b.LINKDT = .B) and 
       ((
a.datadate <= b.LINKENDDT) or b.LINKENDDT = .E)   ;
quit;

/* retrieve historic cusip */
proc sql;
  
create table c_cusip as
  
select a.*, b.ncusip
  from b_permno a
crsp.dsenames b
  where 
        a
.permno b.PERMNO
    
and b.namedt <= a.datadate <= b.nameendt
    
and b.ncusip ne "";
  
quit;
 
/* force unique records */
proc sort data=c_cusip nodupkeyby key;run;
 
/* get ibes ticker */
proc sql;
  
create table d_ibestick as
  
select distinct a.*, b.ticker as ibes_ticker
  from c_cusip a
ibes.idsum b
  where 
        a
.NCUSIP b.CUSIP
    
and a.datadate b.SDATES 
;
quit

I notice that ‘cusip’ is also in the table you are referring to (https://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=129&file_id=82444), but I think (not 100% sure) this is the ‘header’ (current) cusip, and I understand IBES uses the historic cusip.

Hope this helps,

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 August 2015 08:43 AM   [ Ignore ]   [ # 2 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Rishi,

I just realized the security table has the variable ‘IBTIC’ (IBES Ticker - current); this is indeed the easiest way to get to the IBES ticker symbol (simple match on gvkey), but I am not sure how ‘complete’ the match is with respect to possible changes in the IBES ticker over time.

Best,

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 August 2015 08:10 PM   [ Ignore ]   [ # 3 ]
Newbie
Rank
Total Posts:  2
Joined  2015-08-12

Hi Joost,

Thanks a lot for your reply. I got IBTIC using SAS code and then I managed to link the IBTIC to the GVKEY in Compustat using Stata. This is indeed the easiest way to link the 2 databases!!

Appreciate your help.

Profile