Getting GVKEY Compustat from list of CIK codes Audit Analytics
Posted: 27 March 2015 05:24 AM   [ Ignore ]
Newbie
Rank
Total Posts:  3
Joined  2015-03-27

Hi,

I would like to lookup the GVKEY that belongs to the CIK code from a dataset that I’ve downloaded from Audit Analytics (see txt file attached). (Edit Joost: I removed the file)

It looks like this is possible with the ‘‘FUNDA’’ table but I can’t find it. Who can help me with this?

Thanks so much for any kind of assistance!

Note: When I have found the GVKEY’s for each of the CIK code from my Audit Analytics list, then I want to use the new list with GVKEY’s to upload it in Compustat Global so that I can lookup financial data like accruals, assets, equity, inventory etc.

I’m Dutch so answers in Dutch are welcome as well grin

Profile
 
 
Posted: 27 March 2015 06:55 AM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Hoi KStohr,

Gezellig smile

Compustat (Fundamental Annual) has both gvkey and cik, so you can match AA and Compustat using that table.

Groetjes,

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: 27 March 2015 08:29 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  3
Joined  2015-03-27

Yep, gezellig grin

I’m in the section ‘‘Fundamental annual’’ from Compustat Global but here I can see (under step 2) that I can only upload a list with company codes in format GVKEY, ISIN, NAICS, SEDOL and SIC but not CIK. So that’s why I want to know how I can lookup the GVKEY for a given CIK code so that I can upload a list with GVKEY’s under step 2 in this query under ‘‘Fundamental annual’‘.

So what I did first was: I did an upload under step 2 with a txt file with the CIK codes that I need to have in my selection (see attachment previous post) but since I had no option to choose format ‘‘CIK’’ under step 2 I didn’t select a format under step 2 so it automatically puts the bullet at the GVKEY format. Then I ran the query but I think it just read my list with CIK codes as if these are GVKEY’s. Or is that not possible?

Now I tried something else: Step 1 I selected the date range (jan 2004-dec 2014) Under step 2 I chose ‘‘search entire database’’
Under step 3 I chose the variables that I want including: variable ‘‘CIK’‘. I assume now that it gives me all companies available in the entire compustat between 2004-2014 and it shows a column with the CIK code (if known, otherwise the cell will be empty). This gave me an excel file of 256.000 line items (over 2004-2014) from which 10.000 line items had a CIK code known. But from these 10.000 line items (2004-2014) there are only 1000 unique CIK codes. Does that automatically mean that these are all the CIK codes available in the whole Compustat database? Seems quit a low number of companies or do you think this is all there is? Cause when I look at my data from AA I have more than 3000 unique CIK codes.

Or doesn’t this make sense at all?

I received this answer from one of the professors at the university:

Waar het op neerkomt is dat je met behulp van je statistisch pakket drie datasets aan elkaar gaat koppelen: je dataset uit Audit Analytics (met de CIK codes), je dataset uit Compustat (met de GVKEY codes), en de FUNDA tabel (met de CIK en GVKEY codes). Hoe je dit in je statistisch pakket precies moet uitvoeren zul je zelf moeten uitzoeken, maar het zijn commando’s (of opties) die worden aangeduid met “merge”. Geef daarbij in het softwareprogramma aan dat je observaties alleen wilt behouden als er zowel een CIK code als een GVKEY code voor de observatie bekend is. Je houdt dan een dataset over waarin alleen observaties zitten die zowel in de Audit Analytics als de Compustat database zitten.

Do you have any idea where to find this FUNDA table? Maybe I can use this table to map it to my AA data?

Sorry, I’m a beginner with all of this data collecting!

Profile
 
 
Posted: 27 March 2015 10:09 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hoi,

Compustat Global is non-US; you need Compustat North America, Fundamentals Annual (called Funda for short);  there you can upload a file with CIK codes. https://wrds-web.wharton.upenn.edu/wrds/ds/compm/funda/index.cfm?navGroupHeader=Compustat Monthly Updates&navGroup=North America

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: 27 March 2015 01:22 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  3
Joined  2015-03-27

Great Joost!

For my first dataset this works! I used a Vlookup between the AA dataset and the compustat dataset to see if the compustat database could find all AA items. Only 130 items were not found in the compustat database out of a total list of 2950 items but I think that’s quit normal right? So 2820 left items which I can lookup now.

I’ll try this for my other datasets as well. Will have to find a way to match the fiscal years since compustat has a different defenition of the fiscal year compared to AA. But will try some excel tricks.

Thanks so much! Really appreciate your quick response!

Groetjes Kelly

Profile
 
 
Posted: 27 March 2015 03:22 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Kelly,

I’m glad it worked. Try matching AA’s “fiscal_year_of_op” with the year of Funda’s “datadate” (=end of month closest to fiscal year end) (please check a few records, especially some with non-December year end, to make sure).

Success met je scriptie!

Joost

 Signature 

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

Profile