Investigate on diversified firms and their segments using Compustat
Posted: 12 January 2013 05:27 AM   [ Ignore ]
Newbie
Rank
Total Posts:  17
Joined  2013-01-12

Dear Forum,

I would like to investigate on diversified firms and their segments (using STATA). Specifically, I want to have a Dataset of diversified firms, accounting data on firm Level (Balance Sheet data, Cash Flow), but also the number of segments per firm and the industries (SIC?) of the segments, as well as accounting data on Segment Level. I want to be able to e.g. compare the CapEx of the diversified firm with the CapEx of the segments of that firm, i.e. the Distribution within the firm.

Could you please give me some hints on:

1. which data base to use in WRDS (Compustat Monthly Updates Fundamentals Annually?)
2. How to design the query in order to be able to get the Information described above

I am afraid of being required to use several data bases and combine them. If so, I would be very glad to get some support.

Thank you very much!

Profile
 
 
Posted: 12 January 2013 09:19 PM   [ Ignore ]   [ # 1 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Chris,

This is the table with the segment info:
http://wrds-web.wharton.upenn.edu/wrds/tools/variable.cfm?library_id=129&file_id=77282

Matching with Fundamental Annual (Funda) will be easy, because both Funda and the segment dataset are from S&P. (You can match them using gvkey and datadate).

There are probably several challenges:
- (roughly) one third of the companies does not have segment data, so you won’t be able to match all firms in Funda
- the firms that do file segment info, and are in the segment files, have often very ‘dirty’ data; for example, the sum of the sales of the segments very often does not add up to firm sales in Funda
- sales data is best disclosed on the segment; net income reasonably, other stuff (assets, capex) probably not as good as you would hope
- firms can cut up their segments by geographic region, or by operations; both types of disclosures are in the same table, so you probably need to decide what kind of segments you will use (by region, or by operations) and throw away the other type

I may be mistaking, but it looks like the segment data cannot be downloaded through the web interface. Instead, you will probably need to use SAS (or use SSH to ‘ftp’ the data, and then convert into Stata with Stata transfer).

good luck!

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 January 2013 06:43 AM   [ Ignore ]   [ # 2 ]
Newbie
Rank
Total Posts:  17
Joined  2013-01-12

Hi Joost,

thank you for your reply. By now I have understood that I need to use data from Funda as well as from a Segment data base. Via WRDS, I am able to download data from “Compustat Historical Segments” - isn’t this the suitable segment data to match with the Funda? However, there is no datadate variable in this segment data which makes it unclear for me how to (which variables to use besides gvkey to) merge the data sets.

WRDS often says sth. about different “segment files” that lie on their servers - are these for SAS only? I am a bit confused because I am using Stata and Compustat both for the first time and just thought Compustat access is only possible via WRDS web query.

Thanks for any help.

Chris

Profile
 
 
Posted: 13 January 2013 08:50 AM   [ Ignore ]   [ # 3 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Chris,

Ok, after you submit the query on the interface, it takes you to a page with the query details. That page will refresh every few seconds until the data is compiled. On the summary it will tell which dataset is used. The page I referred to above is in this library:
COMP:NAA:segments_current [ /wrds/comp/sasdata/naa/segments_current ]

Comp => Compustat, NAA => North America, annually updated

And, has this name: WRDS_SEGMERGED Merged Segments File (Non-Historical)

If the query shows another table, then that would be something to further look into.

The most ‘value-for-money’ usage of WRDS is to use SAS on your own computer, and use remote connect (‘rsubmit’) to execute SAS code on the WRDS serves and download the data. This is much more time efficient (at least, when you’ve learned SAS smile) and flexible compared with the web interface. There are tutorials/sample code on WRDS, and on this website as well.

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: 16 January 2013 09:10 AM   [ Ignore ]   [ # 4 ]
Newbie
Rank
Total Posts:  17
Joined  2013-01-12

Thank you for this post, I have much more orientation now.

In Addition, this video is very helpful when working with segments data:

http://wrds-web.wharton.upenn.edu/wrds/E-Learning/_000Video/Compustat_Segments_2011/index.cfm

Profile
 
 
Posted: 17 January 2013 08:56 AM   [ Ignore ]   [ # 5 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

thanks for the link!

 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 January 2013 06:18 AM   [ Ignore ]   [ # 6 ]
Newbie
Rank
Total Posts:  17
Joined  2013-01-12

I have an additional question on the different types of segments (business, operating, geographic, or state). I want to make my analysis from the viewpoint of the company. For example, if a company structures its segments by region, the regions are competing against each other for resources within the firm. If a company, however, structures its segments by operations or any other definition, this is also totally fair for me.

I therefore do not care about the type of the segment according to any accounting standards. At the same time, I do not want to double-count any Segment info if, e.g., business segments A and B are included in geographic segment x.

So, which segment types (“stype”) should I use? All of them or only one type, e.g. “BUSSEG”? If I use all, how can I Aggregate them (if necessary)? How can I find out what the companies’ original segment classification is? Is that the “Business” segments?

Thank you for any help.

Profile
 
 
Posted: 18 January 2013 03:50 PM   [ Ignore ]   [ # 7 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi Chris,

I would expect you would run into the following kind of disclosures:
- firms with no segment disclosures
- firms that disclose geographic segments
- firms that disclose operational (‘business’) segments
- firms that disclose both types of segments (geographic as well as business segments)

For the last type of segment I do not expect you will get the full ‘matrix’ in the segment database. If in a given year a firm has reported 4 geographic segments and 3 business segments, there will be 7 (3 + 4) items in the database (not 3 x 4). Note that there will also be the ‘corporate’ headquarters as a possible segment.

Looking up a few 10K’s and seeing how the data is represented in the database is probably a good idea.

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: 09 March 2014 08:03 PM   [ Ignore ]   [ # 8 ]
Administrator
Avatar
RankRankRankRank
Total Posts:  901
Joined  2011-09-19

hi,

I have made a SAS macro available that counts the number of segments (either industry segments or geographic segments).

See: http://www.wrds.us/index.php/repository/view/34

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