1 of 2
1
merging I/B/E/S and compustat global
Posted: 17 April 2015 08:45 AM   [ Ignore ]
Newbie
Rank
Total Posts:  2
Joined  2015-04-17

Hi all,

I am trying (and struggeling) to merge Compustat Global fundamentals with analyst forecasts from I/B/E/S. However, I do not find a common identifier.

Can anybody give me a hint on how to do that?

Best,

DomLi

Profile
 
 
Posted: 17 April 2015 09:00 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi DomLi,

How about this code: https://wrds-web.wharton.upenn.edu/wrds/support/code_show.cfm?path=I-B-E-S/iclink.sas

First get permno (CRSP identifier) through CCM linktable (gvkey -> permno).

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: 18 April 2015 05:23 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  2
Joined  2015-04-17

Hi Joost,

thank you for your answer. I have to use stata for my thesis, is there also a linking table available for stata, or only for SAS? Can I somehow download the linking table in an stata format?

Best,
DomLi

Profile
 
 
Posted: 18 April 2015 06:48 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi DomLi,

It looks like the linktable is available in the web interface. https://wrds-web.wharton.upenn.edu/wrds/ds/crsp/ccm_a/linktable/index.cfm?navGroupHeader=Annual Update&navGroup=CRSP/Compustat Merged

In general (if it is not, or would not work), this is a way to get files downloaded without using SAS (with SAS, use rsubmit with proc download):

1. Use WRDS’ website to find out the location of the CCM linktable (support -> dataset list -> crsp), locations look like “/wrds/crsp/sasdata/a_ccm”
2. Use SSH-FTP to download the dataset
3. Use Stat Transfer to convert to Stata format https://www.stattransfer.com/

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: 03 June 2015 01:49 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  2
Joined  2015-06-03

Hey,
I also want to merge international, so non-US, firms from Compustat Global with analyst data from IBES but I am not able to do it.
I tried to use the CCM linking table, but then I am left with Canadian firms only, so this is no opportunity.
I tried to use the ISIN and SEDOL from Compustat to obtain the IBES CUSIP but that does not work either.

Is there a way to combine the two databases for international (also not cross-listed) firms? If yes, how can I do that?
By the way, I am also using Stata.

Thanks for your help!

Profile
 
 
Posted: 03 June 2015 06:59 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Anna,

I don’t have much experience matching Compustat Global and IBES so I posted your question on the Google group. I will post any replies here. Feel free to post your findings here as this is a challenging match and useful for others.

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: 04 June 2015 03:43 AM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  2
Joined  2015-06-03

Ok, I will do so! Thanks for you help! Hopefully someone knows how to do it.

Profile
 
 
Posted: 02 February 2016 09:34 AM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

I received a reply from the Google group:

I believe that the trick to match IBES to Compustat Global is to understand how they report SEDOL/CUSIP. Compustat Global uses 7-digit SEDOLs: 6-digit SEDOL plus the check digit. IBES reports 8-digit SEDOL under the variable CUSIP: the first two digits are for the country and the last 6 are the 6-digit SEDOL. To match the two datasets we need to use the first 6 digits in Compustat SEDOL and the last 6 digits of in IBES CUSIP.

Here is a Stata code, assuming that you want to match Compustat with analysts’ forecasts 4 months after the report:

use ibesclear
keep 
if usfirm==0
gen sedol 
substr(cusip,3,6)
gen month=mofd(statpers)        /* if "statpers" is correctly recorded as a date variable */
save 1replace
 
use compustat_globalclear
gen month 
mofd(datadate) + 4
drop 
if sedol==""
replace sedol substr(sedol,1,6)
replace sedol "0" sedol if length(sedol)==5
replace sedol 
"00" sedol if length(sedol)==4
keep sedol month
duplicates drop
merge 1
:m sedol month using 1keep(match

 

 Signature 

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

Profile
 
 
Posted: 08 March 2016 07:20 PM   [ Ignore ]   [ # 8 ]
Newbie
Rank
Total Posts:  4
Joined  2016-03-08

Hi Joost,
Thank you for this post. I was wondering if modifying the second line of the above code will validly apply the merge for US firms as well.  Intuitively, I can delete the “keep if usfirm==0” to capture US firms as well, or replace it with “keep if usfirm==1” to apply the merge to US firms only. Will this be valid in your view?
Many thanks,
Samuel.

 

Profile
 
 
Posted: 09 March 2016 09:51 AM   [ Ignore ]   [ # 9 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Samuel,

Are US firms in Compustat Global? If they are, it could work. How about giving it a try?

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: 13 March 2016 11:38 AM   [ Ignore ]   [ # 10 ]
Newbie
Rank
Total Posts:  4
Joined  2016-03-08
Joost Impink - 09 March 2016 09:51 AM

hi Samuel,

Are US firms in Compustat Global? If they are, it could work. How about giving it a try?

Best,

Joost

Hi Joost,

Thank you for your response. I thought so, but just realized they are not. I will see how I may apply the procedure to US firms in Compustat North America.

Best regards,

Samuel.

 

Profile
 
 
Posted: 13 March 2016 04:54 PM   [ Ignore ]   [ # 11 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Samuel,

The second post in this thread has a link to iclink.sas; that script basically creates a linktable between Compustat and IBES (sounds like that is what you need).

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: 17 March 2016 10:17 AM   [ Ignore ]   [ # 12 ]
Newbie
Rank
Total Posts:  4
Joined  2016-03-08

Hi Joost,

Many thanks for this. Really helpful.

Best regards,

Samuel.

Profile
 
 
Posted: 23 March 2016 09:35 AM   [ Ignore ]   [ # 13 ]
Newbie
Rank
Total Posts:  5
Joined  2016-03-23

Hi everybody,

I am also trying to merge IBES and Compustat North America Fundamentals. I have done so using CUSIPS (by using the CUSIP converter to convert 8-digit CUSIPS from IBES to 9-digit CUSIPS as in Compustat)).

However, I was wondering whether I have a problem now with regard to the dates. My Compustat data are sorted by fiscal years, while in IBES summary I looked at Forecast Period End Date (which I understand as being the year to which the estimates apply). But I am not sure whether the match that I made is valid with regard to the dates, because of different definitions in Compustat and IBES.

For example:
A fiscal year in Compustat of 1996 can refer to, for instance, 31/3/1997 (according to the definition of fiscal years in Compustat). If that is the case, does the information that I read out of IBES (in my case, number of estimates) in year 1996 (Forecast Period End Date) correspond correctly to fiscal year 1996 from Compustat? I doubt it, but I have no idea how to fix this. I am not familiar with SAS codes or anything, I am trying to merge in Excel.

Any help would be greatly appreciated.

Profile
 
 
Posted: 23 March 2016 08:35 PM   [ Ignore ]   [ # 14 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Elisa,

The variable ‘datadate’ in Compustat should be the same or very close to fiscal period end date in IBES (wouldn’t use fyear for matching).

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: 23 March 2016 09:12 PM   [ Ignore ]   [ # 15 ]
Newbie
Rank
Total Posts:  4
Joined  2016-03-08

Hi Elisa,

I agree with Joost; I have done as suggested and it worked fine for me. As fyear makes adjustment for fiscal period end, you will note a few mismatch for firms with fiscal period end from January through May if you match using fyear.

Best regards,

Samuel.

Profile
 
 
   
1 of 2
1