Using_CRSP_on_WRDS FINA 863 Fall 2019

pdf

School

University of Nebraska, Lincoln *

*We aren’t endorsed by this school

Course

474

Subject

Finance

Date

Feb 20, 2024

Type

pdf

Pages

Uploaded by SuperHumanRockRhinoceros42

1 Using CRSP on the Wharton Database (WRDS) FINA 863 Logging into the class WRDS account is simple. The URL is http://wrds.wharton.upenn.edu/. The username and password are listed on Canvas. Please do not share this information with anyone outside of our class. After logging into WRDS you might need to accept the terms of use. You can then choose HOME and you will see Your Subscriptions. Select the CRSP database (on the left hand side ). Annual Updates, select Stock/Security Files and then Monthly Stocks. Note: Be sure you are under Annual Updates (there are also sections for Quarterly and Monthly Updates). There are four steps to follow to collect the monthly stock return as follows: STEP 1: Date Range Frequency – select the beginning and ending dates that you want. STEP 2: Apply your company codes Searching by TICKER is OK if you know the ticker symbol. Scroll down a bit and you will see the space provided to manually enter the Tickers. You can save the ticker symbols is a simple text file, or type them in the space provided under Select an option for entering company codes. STEP 3: Query Variables Identifying Information : Choose: Company Name and Ticker Time Series Information: Choose: Holding period return Market Information: Choose: Value weighted return with Distributions (for Market info you might need to scroll across)

2 STEP 4: Select Query Output Output Format : Comma delimited text (*.csv) or Excel Compression type: None is OK Date format YYMMDD is OK Now hit SUBMIT. You will be notified when the job is complete. Just follow the instructions to download the data to your computer (in Internet Explorer, right click on the data set and then choose Save Target As). The returns for each stock will be stacked on top of one another. You will also notice that the market returns are repeated next to the returns for each stock. To analyze the return data in EXCEL, we will need to rearrange the data so that each stock’s return is in its own separate column. These steps should help with this task. 1. The stock return data for the companies will be stacked. That is if you were to collect 60 monthly returns for 3 companies, your Excel sheet will have 180 rows. The market return will be repeated for each of the companies. Be sure that Excel has not stored your return data as text (this has happened to me when I’ve asked for WRDS to save the output as and Excel file; hasn’t happened when I ask for .csv type files). 2. Our goal is to have each company’s return be a separate column in the Excel sheet along with the market return. So if we have collected data for 3 companies we want one column of dates, and then 3 columns of stock returns. We will use a Pivot Table to accomplish this task. 3. First, move the returns for the market to another sheet (you should also copy the date column). Delete the PERMNO column if it appears. If the remaining data formatting looks odd, select all the data and then clear the formats. 4. Highlight the remaining data (the columns are: DATE, TICKER, and RET). 5. From the Insert Tab on the ribbon, select Insert Pivot Table. Then hit FINISH. 6. You will get a new worksheet that contains the Pivot Table Field List and the Pivot table itself. 7. The pivot table has row and column fields. The main body of the table contains the data field. 8. You can select and drag items from the Pivot Table Field List and place them in the pivot table. Select DATE and drag and drop it into the ROW FIELD (you should see the date appear). Select TICKER and drag and drop it into the COLUMN FIELD. Lastly, select RET and drag and drop it into the space in the table where it says DROP DATA ITEMS HERE. Voila, the returns are now in separate columns. By default the pivot table will add the returns by row and column (this will appear in the last row and column of the table). If after dragging returns to the Value position on the Pivot Table, you don’t see Sum of Return, click the down arrow and then Value Field Setting and choose Sum.

3 Since there is only one return per cell, the sum of the returns is the monthly return for each stock. 9. You can copy the returns in the pivot table into another worksheet (I recommend this) and then add the market returns. 10. If you have questions, just ask.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version