Help with finding firm data for sample
Posted: 31 August 2014 06:11 PM   [ Ignore ]
Newbie
Rank
Total Posts:  6
Joined  2014-08-31

I would like to find out the firms (individually and total number) that fit a certain criteria (example listed below) for a sample I am interested in. I am unsure of the steps/process of how you drill down using a multi-criteria process through Compustat. (In other words, how can I do this myself.)

Sample Criteria:
- Only publicly traded service organizations.
- Service firms with greater than $10 million in sales and more than 50 employees.
- Service firms that operate mainly in a single industry (receiving a minimum of 70% of their sales from one industry).

Thank you so much in advance!

Profile
 
 
Posted: 31 August 2014 07:12 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

Filtering on the first 2 critera is strightforward. In the web interface to get data from Compustat you can add ‘conditinal statements’ (last part of step 2): http://wrds-web.wharton.upenn.edu/wrds/ds/compm/funda/index.cfm?navGroupHeader=Compustat Monthly Updates&navGroup=North America

If you can restrict the ‘service organizations’ to a SIC range, you can enter it there. Also, the $10 million sales cutoff can be entered there as well (sales are in millions). If you get the #employees as part of the output, you can filter on this in Excel/Stata/SAS, etc.

Info on segments is in (I am not 100% sure this is the right dataset): http://wrds-web.wharton.upenn.edu/wrds/ds/comp/seghist/index.cfm?navGroupHeader=Compustat Monthly Updates&navGroup=Historical Segments

In step 2 (how would you like to search this database), upload the gvkeys of the firms that match your first 2 criterea. Get segment data as output and filter on your third criterion.

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: 31 August 2014 08:55 PM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  6
Joined  2014-08-31

First, thank you so much for the quick reply. I really appreciate it.

Also, thanks for explaining how to use the conditional statements. After your explanation, I understand how I can locate service firms greater than $10 mill in sales and more than 50 employees.

Apologies, I am still having trouble with understanding how to determine firms that operate in a single industry (70% of sales in one industry).

Profile
 
 
Posted: 01 September 2014 08:01 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

You’re welcome smile

The segment data is in another dataset then the info on sales, #employees. So, one way of doing it is to first select the firms with sufficient sales, employees, in the right industry, and then, take this firms and retrieve their segment data. It could also be done the other way around: first find all firms with >70% sales in one segment, and then take these firms’ employees, sales, etc data to see which ones meet your criterea. It can’t be done in a single step.

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: 06 September 2014 06:07 PM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  6
Joined  2014-08-31

Thank you again!

One more question, I am not sure why I am having trouble entering a range of SIC or NAICS codes. For example, if I want to search a range of NAICS codes for service firms (51, 54, 56, 61, 62, 71, 81) how would that work? I find if I even put them individually (e.g. just 54), it won’t work. However, if I drill down to the exact 6 digit segment (e.g. 541611), then it will work. Is there something I am doing wrong?

Profile
 
 
Posted: 06 September 2014 06:38 PM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

In which dataset are you filtering on industry? In some datasets the industry code is ‘text’, like “541611”, in other it is a number, like 541611. If it is a numeric field, you could specify a range, for a 6-digit NAICS that could be 540000 <= .. <= 549999; that would give all firms with NAICS starting with 54. If the industry code is a string (text), then a range will not work.

Another strategy is to pull out all firms, and filter them in Excel/SAS/Stata, etc.

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: 06 September 2014 06:56 PM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  6
Joined  2014-08-31

Thanks for the reply.

I am trying to use the conditional statements to find firms in an industry. NAICS breaks down the first two-digits as the Sector, Three-digit NAICS level represents the Subsector, and the Six-digit NAICS level represents the
National Industry. 

For example, 54 is for “Professional, Scientific, and Technical Services”, drill it down to 541 and its “Professional, Scientific, and Technical Services”, drill down to 54111 to get “Offices of Lawyers”. What do I do if I want to search all public firms within 54 of NAICS? Can I use the two digit “54” classifier or “540000” and if its the later, do I need to use both conditional statements (NAICS > 540000, NAICS < 549999) to do it?

Thanks for all your help again!

Profile
 
 
Posted: 06 September 2014 07:25 PM   [ Ignore ]   [ # 7 ]
Newbie
Rank
Total Posts:  6
Joined  2014-08-31

Quick update, I was able to locate firms by using the two conditional statements and creating a range that way (NAICS > 540000, NAICS < 549999). From there I use excel to parse out the data I need (e.g. number of employees and sales).

For finding out if firms with >70% sales in one segment, is there a way to do this without having to look up each company’s financials. From one set of ranges I am looking at it, it yielded over 800 companies. I can’t imagine I have time to look up each one to determine if they are operating in a single industry.

Thanks in advance for any guidance on this! smile

Profile
 
 
Posted: 06 September 2014 07:49 PM   [ Ignore ]   [ # 8 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

The web interface to download data from historica segments allows you to specify a list of firms to download the data for, see: http://wrds-web.wharton.upenn.edu/wrds/ds/comp/seghist/index.cfm?navGroupHeader=Compustat Monthly Updates&navGroup=Historical Segments

Use ‘step 2 - upload a file containing company codes’ - holding a list of gvkeys or another firm identifier.

Note that the segment data may appear as to be holding duplicate data:
- data from 1 year may appear multiple years (because an annual report for year X will include data on years X, X-1 and X-2)
- there may be both geographical as well as industrial segments, it sounds like you only need industrial segments

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: 06 September 2014 08:01 PM   [ Ignore ]   [ # 9 ]
Newbie
Rank
Total Posts:  6
Joined  2014-08-31

Hi, if I enter all the companies that way, I will be able to see their net sales in each segment. However, I will still have to go one by one to determine which one has or does not have 70% in one industry, correct? Is there a way to filter that without me having to do it?

Sorry for all the questions. You have been great answering all my questions!

Profile
 
 
Posted: 06 September 2014 08:11 PM   [ Ignore ]   [ # 10 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

Thanks - you’re welcome smile

You can download all your firms in one go (if you have the gvkeys in one file, and upload this file as a filter as I described in previous post). You can then use your ‘favorite’ program (Excel, SAS, etc) to clean up the data and determine if each firm-year makes it through your 70% requirement.

Doing repetitive things (or ‘one-by-one’) would not be fun - it should be possible to automate this to a pretty large extent, even with Excel.

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