Introduction to CRSP and Computstat via WRDS: Part 1

Using Compustat to find accounts data for one company

Graphic of keyboard

Note: This is part of a series of posts about using CRSP and Compustat via WRDS. The order of these posts is important. We will create and edit text files, CSV files and Excel files in some activities which we will then use in later ones. We suggest you start at the beginning then work through parts 1 to 4.

⬆️ Back to beginning | Part 2 ➡️️

Contents of Part 1

1. Overall process of running a Compustat query
2. Navigating to the ‘Compustat Daily Updates — Fundamentals Annual’ page
3. Demonstration: Download Apple’s total revenue 2012–2017

1. Overall process of running an Compustat query

We will start by using Compustat North America database to look up one company, search for some accounts data then download that data to a comma-delimited/separated values (CSV) file.

The overall process is:

1. Log into WRDS.
2. Navigate to the ‘Compustat Daily Updates — Fundamentals Annual’ page.
3. Complete then submit the query form.
4. Wait for the query to complete then download the data.

The query form has several steps within, clearly labelled as follows:

1. ‘Choose your date range’
2. ‘Apply your company codes’
3. ‘Choose variable types’
4. ‘Select query output’

We will explain these steps in the following demonstration. They are similar in all databases within WRDS.

Note: Compustat North America supports GVKEY codes and CUSIP codes to identify companies. CRSP supports CUSIP codes. We will use CUSIP codes in our queries to ensure compatibility between Compustat and CRSP. See the WRDS guide to Understanding Identifiers for more details.

^ Back to contents of Part 1

2. Navigating to the ‘Compustat Daily Updates — Fundamentals Annual’ page

Screenshots of navigating to the Compustat North America fundamentals page. Click all images to enlarge them.

Steps to navigate to the query form:

1. From the WRDS home page, look under ‘Subscriptions’ and click on ‘Compustat-Capital IQ’.
2. From the page titled ‘Compustat — Capital IQ from Standard & Poor’s’, look under ‘Compustat’ and click ‘North America-Daily’. The ‘Daily’ refers to how often the data is updated.
3. From the page titled ‘North America — Daily’, look in the first column and click ‘Fundamentals Annual’. The ‘Annual’ refers to the period of the data within for each company, although you can search by the nearest month.

^ Back to contents of Part 1

3. Demonstration: Download Apple’s total revenue 2012–2017

Choose the date range in ‘Step 1’ and look up one company in ‘Step 2’.

Steps to select dates and look up one company:

1. From the page titled ‘Compustat Daily Updates — Fundamentals Annual’, begin ‘Step 1: Choose your date range.’ Select the ‘Date range’ from January 2012 to December 2017.
2. Begin ‘Step 2: Apply your company codes.’ There are four options for entering company codes. This time we will use the first one where codes can be entered individually. Click the ‘Code Lookup’ link below the ‘Company Codes’ text box.
3. A new window will pop up, titled ‘Compustat Daily Updates — Fundamentals Annual Code Lookup’. Type ‘apple’ in the search box and click the ‘Starts With’ button.
4. All companies whose name starts with ‘apple’ are listed. Tick the correct match which has ‘ENTITY_NAME’ = ‘APPLE INC’ and ‘FIRST_DATE’ = ‘1980’.
5. Under ‘Select Your Identifier’, select ‘CUSIP_FULL’.
6. Click the ‘Add Codes to List’ button then ‘OK’.
7. Your selected codes appear at the bottom of the window. (Later, if you need to search for more than one company, you can scroll back to the top and search for the next company, repeating steps 3 to 6.)
8. Click the ‘Add Codes to Query’ button then ‘OK’ to finish this stage and return to the query builder. The CUSIP code for Apple Inc (037833100) has been inserted.

Search for variable types (data items) in ‘Step 3’ and check their definitions. Select CSV query output in ‘Step 4’.

Steps to select variables types and run query:

1. Continuing on the page titled ‘Compustat Daily Updates-Fundamentals Annual’, begin ‘Step 3: Choose Variable Types.’ There are tabs which group the different variable types (data items) such as ‘Balance Sheet Items’. The first tab is labelled ‘Search All’ and gives access to all the items. Click in here.
2. The list on the left is all the items, the list on the right is your selection. You get ‘GVKEY — Global Company Key’ by default. Click to select the common items of ‘Company Name’ and ‘CUSIP’ which will appear on the right.
3. We will search for the total revenue item. Click in the ‘Search All’ box and type ‘revenue’. The results will update automatically. You can use the ‘?’ blue circle icon next to each item name to open a pop-up window to read a definition. Click to select ‘REVT — Revenue -Total’. (Note that if you search for ‘total revenue’ this item will not appear; the search is very precise about word order and punctuation.)
4. We are now ready for ‘Step 4: Select query output.’ There are many ‘Output Format’ choices including ‘fixed-width text (*.txt)’ and ‘Excel spreadsheet (*.xlsx)’. Click to select the ‘comma-delimited text (*.csv)’. (You will see why later.)
5. Your query is now ready. Click the ‘Submit Query’ button.
6. The ‘Data Request Summary’ page will open in a new tab. It refreshes every five seconds while the query is running. When your query is finished, you will see the message ‘Your output is complete. Click on the link below to open the output file.’ Save this file with the name compustat apple.csvto a new folder.

The file ‘compustat apple.csv’ opened in Excel, important columns highlighted.

If you click to open the new file, it should open in Excel (or a similar spreadsheet application). There is a row for each year for each company. (Don’t worry about any mistakes with the CUSIP or GVKEY columns, we will look at that later.)

^ Back to contents of Part 1

⬆️ Back to beginning | Part 2 ➡️️