Introduction to CRSP and Computstat via WRDS: Part 4

Using CRSP to find market data for all companies matching a filter

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.

⬅️ Part 3 | ⬆️ Back to beginning

Contents of Part 4

1. What is the conditional statement builder?
2. Downloading daily price data for all companies matching a criteria from CRSP

We will continue to look at the CRSP database. We will not provide the selection of companies; instead, we will search the entire CRSP database and filter it to only return companies matching a chosen criteria. Again, we will search for some market data then download that data to a CSV file.

1. What is the conditional statement builder?

Screenshot example of the conditional statement builder. Click all images to enlarge them.

The ‘Conditional Statement Builder’ allows you to add rules to your query, filtering the results. For example, you may wish restrict your search by:

• Excluding all Finance companies (their SIC Code is between 6000 and 6999)
• Including companies whose number of publicly held shares (number of shares outstanding) is greater than 1,000,000

There are a few points to consider when using conditional statements:

• You can combine rules using Boolean operators AND and OR.
• You can only use variables which you have already selected in your query.
• The units may be in thousands or millions. (You already need to know about that to interpret the data correctly.)
• The query will start with the list of companies you have chosen, then the conditional statements can reduce the list further.

We will use the conditional statement builder in the next section.

^ Back to contents of Part 4

2. Downloading daily price data for all companies matching a criteria from CRSP

In Part 3, we saw how to:

• Navigate to the ‘CRSP Daily Stock’ page
• Select dates and upload list of companies
• Select variables types and run a query

The process for searching the entire CRSP database is very similar. In ‘Step 2: Apply your company codes’, you will choose to search the entire database.

Note: Warning: If you choose to search an entire WRDS database, your query might take a very long time to run and download (perhaps hours). We strongly recommend you practice such queries with a small range of dates to make sure you have done everything correctly before making a time commitment.

We will also use the conditional statement builder to limit the search down from the entire CRSP database to just companies trading on the Nasdaq index. In other words, we will search for the exchange code data item and use it in a rule. We saw in Part 3 that the exchange code for Nasdaq is 3.

Downloading daily price data for Nasdaq companies in CRSP from January 2017

Use the exchange codes definition where Nasdaq = 3 (left). Search the entire CRSP database (centre) and add filter later.

Steps to select dates and search entire CRSP database:

1. From the page titled ‘CRSP Daily Stock’, begin ‘Step 1: Choose your date range.’ Select the ‘Date range’ from 2017–01–01 to 2017–01–31.
2. Begin ‘Step 2: Apply your company codes.’ There are four options for entering company codes. This time we will use the fourth one where we ‘Search the entire database’.

Steps to select variable types:

1. Continuing on the page titled ‘CRSP Daily Stock’, begin ‘Step 3: Choose Variable Types.’ There are tabs which group the different variable types (data items) such as ‘Time Series Information’. 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 the PERMNO item by default although it does not show up here. Click to select the common items of ‘Company Name’ and ‘CUSIP’ which will appear on the right.
3. We will search for the price item. Click in the ‘Search All’ box and type ‘price’. The results will update automatically. Click to select ‘Price’ (with no word before it). (Note that the search is very precise about word order and punctuation.)
4. We will search for the exchange code item. Click in the ‘Search All’ box again and type ‘exchange’. Click to select ‘Exchange Code’.

Set up the conditional statements builder.

Steps to set up conditional statements and run a query:

1. Click in ‘Conditional Statements (Optional)’ area. (You may need to click a button labelled ‘Activate Conditional Statements Builder’ first.)
2. In the first rule, click the first drop-down menu and select ‘Exchange Code’.
3. For the condition, keep the selection ‘equal’.
4. For the value, type 3 then press Enter.
5. The ‘Query Preview’ text (white on black) should update to WHERE exchcd = 3 which completes the conditional statement.
6. 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)’. (In Part 2 we discussed how the Excel format can corrupt CUSIP codes.)
7. Your query is now ready. Click the ‘Submit Query’ button.
8. 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 crsp nasdaq.csv to a new folder. You may notice that this file is much larger than our previous ones.

Download the CSV Nasdaq CRSP data. Open it carefully in Excel.

Activity 5

• Using the process above, use CRSP to find the price data for January 2017 for all Nasdaq trading companies except in the Financial sector. (You may wish to use the Wikipedia page on SIC codes as a reference.)
• Download the data to a CSV file named crsp nasdaq no-fin.csv.
• Use the Text Import Wizard (from Part 2) to carefully open the data, preserving the format of the CUSIP column.
• Save the new file as crsp nasdaq no-fin.xlsx (as a regular Excel workbook).

^ Back to contents of Part 4

⬅️ Part 3 | ⬆️ Back to beginning