Understanding WRDS: Data Services Workshop Overview

Slide Note

Explore the Wharton Research Data Services (WRDS) workshop held at Carson College of Business, covering topics like accessing WRDS, CRSP, S&P Global Market Intelligence, Eventus, 13-F Filings, data licensing, and more. Learn about the award-winning research platform, comprehensive services, and data access with WRDS, supported by various client environments.

kwebe Follow

Uploaded on Sep 14, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

E N D

Presentation Transcript

Wharton Research Data Services (WRDS) Workshop CARSON COLLEGE OF BUSINESS CARSON COLLEGE OF BUSINESS Department of Finance & Management Science

Outline of Workshop Introduction ................................................Hongwei Zhao Live Demo of Accessing WRDS .Jingjing Chen Center for Research in Security Prices (CRSP) Cuyler Strong S&P Global Market Intelligence s Compustat Yoonsoo Nam CARSON COLLEGE OF BUSINESS Eventus .................................................Feiwei Chen Thomson Reuters 13-F Filings ................Ao Wang Trials and Unsubscribed Datasets. .Adrian Tippit Conclusion .Hongwei Zhao

Introduction Presenter: Hongwei Zhao CARSON COLLEGE OF BUSINESS

What Is WRDS? Wharton Research Data Services (WRDS) is the award- winning research platform and business intelligence tool Developed by the Wharton School of the University of Pennsylvania CARSON COLLEGE OF BUSINESS Offers over 250 terabytes of data Used by 400+ institutions in 30+ countries.

WRDS Website CARSON COLLEGE OF BUSINESS

Comprehensive Services Research applications Applications, utilities and an extensive suite of template and sample programs Online specialists support E-learning A library of video sessions covering a host of topics presented by research experts CARSON COLLEGE OF BUSINESS Timely updates Convert and install database updates for immediate access Learning environment Enable to demonstrate real-world applications of principles with actual company data

Data Licensing and Access with WRDS WRDS Subscriber Data Licensing Agreement WRDS Subscription Agreement CARSON COLLEGE OF BUSINESS Data Vendor WRDS Re-distribution agreement

Friendly Support Environments WRDS supports the following client environments Product OS X Windows Office Suite Microsoft Office 2011 SP3 Microsoft Office 2010 SP1 & 2013 SP1 Web Browser Firefox [current version] Firefox 38esr Safari 6.2.x Safari 7.1.x Safari 8.0.x Chrome [current version] Firefox [current version] Firefox 38esr Internet Explorer 10 Internet Explorer 11 Chrome [current version] CARSON COLLEGE OF BUSINESS

Subscribed Data Audit Analytics Event Study by WRDS Public Bank Regulatory Eventus Research Quotient Beta Suite by WRDS Fama French & Liquidity Factors SAS Visual Analytics Blockholders SEC Order Execution Federal Reserve Bank BoardEx Thomson Reuters ISS (Formerly RiskMetrics) CBOE Indexes TRACE CARSON COLLEGE OF BUSINESS Compustat Capital IQ World Indices by WRDS MSCI (Formerly KLD and GMI) CRSP WRDS SEC Analytics Suite MSRB CUSIP OTC Markets DMEF Academic Data Penn World Tables Dow Jones Peters and Taylor Total Q Efficient Frontier by WRDS PHLX

Live Demo of Accessing WRDS Presenter: Jingjing Chen CARSON COLLEGE OF BUSINESS

Creating WRDS Account Step 1: Register at WRDS website https://wrds-web.wharton.upenn.edu/wrds/ Step 2: Get the email to set your password The WRDS Representatives for our institution are: CARSON COLLEGE OF BUSINESS Gregory Alan Neunherz greg_neun@wsu.edu 509-335-1315 Tyson Livingston tyson_livingston@wsu.edu

Getting data from WRDS Wed-based access WRDS Cloud: offers access to all WRDS data SAS studio Remote access to WRDS CARSON COLLEGE OF BUSINESS via PC-SAS, MATLAB, R, Python, Fortran, C Command line access via SSH

Downloading from the website Log in to the WRDS platform: https://wrds-web.wharton.upenn.edu/wrds/ Create a data query and view the results Advantages CARSON COLLEGE OF BUSINESS Simple, powerful queries with a few clicks Download up to 2GB of data per query Export into the format of your choice Import into popular programs with ease Disadvantages Lack of flexibility

Accessing WRDS via SAS studio SAS Studio is an interface to the SAS server running on the WRDS environment. https://wrds-cloud.wharton.upenn.edu/SASStudio Advantages Convenient: Already-established libraries is listed on the left under SAS Libraries CARSON COLLEGE OF BUSINESS Free: for all WRDS subscribers, no license needed Just like PC-SAS Disadvantages The WRDS users permanent file storage directory is located: /home/[group name]/[username]. Store up to 10GB of data in this directory

Accessing WRDS via PC-SAS Submit the code in the Editor window to sign in and establish the remote connections. Write the code to subset the data you requested within the rsubmit/endrsubmit commands to submit the code remotely. Download and sign off CARSON COLLEGE OF BUSINESS Advantages Allows users to access, manage, analyze, and present data Disadvantages Need coding and identifying the path

Accessing WRDS via PC-SSH Run customized programs with several sources at once 750MB of disk space in your own directory Access up to 2TB of temporary disk space to process programs. Instruction for using SSH connection: https://wrds- web.wharton.upenn.edu/wrds/support/Accessing%20and%20Manipulating %20the%20Data/_002Unix%20Access/Remote%20Access%20to%20WRDS %20using%20SSH.cfm CARSON COLLEGE OF BUSINESS Advantages Process and save on the cloud Disadvantages Requires knowledge of Unix commands, as well as an understanding of programming languages

Temporary File Storage Members of your institution may store up to 500GB of data in this directory, shared between all members. Files in your shared scratch directory are deleted after one week (168 hours) CARSON COLLEGE OF BUSINESS

Center for Research in Security Prices (CRSP) Presenter: Cuyler Strong CARSON COLLEGE OF BUSINESS

Overview Center for Research in Securities and Prices (CRSP) is a database of financial market information. Contains data for Stock Prices Monthly data from 1925 Daily data from 1962 CARSON COLLEGE OF BUSINESS Market Indices and Factors Bonds

Data Monthly and Daily Data Stock and Index Data Stock Variables Ticker CUSIP PERMNO SIC Code Price Bid and Ask Bid-Ask Spread Volume Holding Period Return Number of Shares Outstanding Dividend Information CARSON COLLEGE OF BUSINESS

CARSON COLLEGE OF BUSINESS

CARSON COLLEGE OF BUSINESS

Collecting CRSP Variables from PC-SAS %let wrds=wrds.wharton.upenn.edu 4016; options comamid=TCP remote=WRDS; signon username=_prompt_; run; rsubmit; data dsf; libname a_stock "/wrds/crsp/sasdata/a_stock/"; set a_stock.dsf; if 2007 le year(date) le 2008; if permno ^= 14593 then delete; keep permno date cusip bidlo askhi prc vol bid ask shrout ret; run; endrsubmit; CARSON COLLEGE OF BUSINESS

CUSIP PERMNO Date BidLow AskHigh Price Vol Ret Bid Ask SHROUT 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 3783310 14593 20070103 81.9 20070104 83.82 20070105 84.4 20070108 85.28 20070109 85.15 20070110 93.45 20070111 95.1 20070112 93.23 20070116 95.45 20070117 94.82 20070118 89.05 20070119 88.12 20070122 85.65 20070123 85.51 20070124 86.08 86.58 85.95 86.2 86.53 92.98 97.8 96.78 95.06 97.25 97.6 92.11 89.65 89.16 87.51 87.15 83.8 85.66 85.05 85.47 92.57 97 95.8 94.62 97.1 94.95 89.07 88.5 86.79 85.7 86.7 44545733 34097551 29842335 28490191 119827814 0.08307 105660143 0.047856 97.15 51550184 -0.01237 46920920 -0.01232 44489274 0.02621 60053850 -0.02214 84566630 -0.06193 48789206 -0.0064 52005857 -0.01932 43198158 -0.01256 33186526 0.011669 86.69 -0.01226 0.022196 85.62 -0.00712 0.004938 85.46 83.76 83.79 85.64 85.15 85.46 92.55 97.15 95.84 94.69 97.06 95.02 89.08 88.5 86.79 85.76 86.7 860220 860220 860220 860220 860220 860220 860220 860220 860220 860220 860220 860220 860220 860220 861875 85.15 92.53 95.85 94.67 97.05 95 89.07 88.49 86.77 85.69 CARSON COLLEGE OF BUSINESS

Additional Information CRSP has several datasets with different variables. This means you may need to merge datasets. For example, share code is under Stock Event not Stock File . When using CRSP you need to use absolute value of price. CARSON COLLEGE OF BUSINESS

Research Using CRSP Fama, Eugene F., and Kenneth R. French. "Common risk factors in the returns on stocks and bonds." Journal of Financial Economics 33.1 (1993): 3-56. 19,517 Google Citations Jiang, George J., Tong Yao, and Tong Yu. "Do mutual funds time the market? Evidence from portfolio holdings." Journal of Financial Economics 86.3 (2007): 724-758. 247 Google Citations CARSON COLLEGE OF BUSINESS Since 2008, eight of the top ten most cited papers in the Journal of Finance use CRSP data.

Fama French (1993) Use CRSP bond and stock data Return Shares Outstanding Price Treasury Bill Rate Find 3 factors that explain stock returns Market factor Book-to-Market factor Size factor CARSON COLLEGE OF BUSINESS Find 2 Factors that explain Bond returns Maturity Factor Risk Factor

Jiang et. al. (2007) Use Mutual Fund data from CRSP Returns Loads Expense ratios Find that Mutual Fund Managers have positive timing ability CARSON COLLEGE OF BUSINESS Also find evidence that managers use private information to time the market

S&P Global Market Intelligences Compustat Presenter: Yoonsoo Nam CARSON COLLEGE OF BUSINESS

Overview Produced by Standard & Poor s (S&P) Global Market Intelligence S&P Global Market Intelligence is a division of S&P Global (NYSE:SPGI) Provides financial and industry data, research, news and analytics to investment professionals, government agencies, corporations, and universities worldwide North America Data (as of June 2007) Company Fundamental Data About 15,000 active companies and 17,000 inactive companies About 16,000 active securities and 21,000 inactive securities Annual (1950 ~ ) Quarterly (1962 ~ ) Market Data Monthly (1962 ~ ) CARSON COLLEGE OF BUSINESS International (excluding U.S. and Canadian) Data Company Fundamental Data About 24,000 active companies and 10,000 inactive companies About 26,000 active securities and 13,000 inactive securities Annual (1988 ~ ) Quarterly (2003 ~ )

Downloading Compustat Variables from WRDS Website CARSON COLLEGE OF BUSINESS

Downloading Compustat Variables from WRDS Website CARSON COLLEGE OF BUSINESS

Collecting Compustat Variables from PC-SAS %let wrds=wrds.wharton.upenn.edu 4016; options comamid=TCP remote=WRDS; signon username=_prompt_; rsubmit; data funda; libname nam "/wrds/comp/sasdata/nam"; set nam.funda; cyear=year(datadate); keep gvkey fyear fyr cyear datadate at capx ceq csho dlc dltt dp ib intan invt itcb oibdp ppegt ppent prcc_f sale xrd; run; CARSON COLLEGE OF BUSINESS procmeans data=funda; run; endrsubmit;

gvkey datadate fyear fyr cyear at capx ceq csho dlc 1001 19831231 1983 12 1983 14.08 3.367 7.823 3.568 0.52 1001 19851231 1985 12 1985 39.495 2.006 13.014 3.988 8.336 1002 19711231 1971 12 1971 12.123 0.344 7.816 0.877 1.646 1002 19721231 1972 12 1972 15.676 0.423 8.591 0.877 3.45 1008 19840531 1983 5 1984 1.54 0.254 0.778 2.459 0.029 CARSON COLLEGE OF BUSINESS 1008 19850531 1984 5 1985 0.973 0.005 0.384 3.234 0.021 1008 19860531 1985 5 1986 0.965 0.006 0.636 11.41 0.013 1010 19630430 1962 4 1963 255.4 25.3 128.494 1.453 0.266 1010 19640430 1963 4 1964 272.6 22.4 138.533 2.949 0.266 1010 19650430 1964 4 1965 293.7 29.7 153.246 2.969 0

Research Using Compustat Brushwood, James, Dan Dhaliwal, Douglas Fairhurst, and Matthew Serfling. Property crime, earnings variability, and the cost of capital. Journal of Corporate Finance 40 (2016): 142-173. Titman, Sheridan, and Roberto Wessels. "The Determinants of Capital Structure Choice." The Journal of Finance 43, 1 (1988): 1-19. 6,040 Google Citations CARSON COLLEGE OF BUSINESS Since 2008, seven of the top ten most cited papers in the Journal of Finance use Compustat data.

Appendix A Brushwood et al. (2016) CARSON COLLEGE OF BUSINESS

Commonly Used Variables Brushwood et al. (2016) Variable Operating Cash Flow/Assets Total Accruals Definition ib total accruals at [ibqt (actqt actqt-1) (cheqt cheqt-1) (lctqt lctqt-1) + (dlcqt dlcqt-1) dpqt] ib ceq ?????? ?????? ? ????_? prcc_f csho xrd at dltt + dlc dltt + dlc + prcc_f csho Return on Equity (ROE) CARSON COLLEGE OF BUSINESS Earnings Surprise Market Value of Equity R&D Expenditures Market Leverage

Important Variables Brushwood et al. (2016) Position Variable Name Type Length Description 40 actq Num 8 Current Assets - Total Cash and Short-Term Investments Debt in Current Liabilities Depreciation and Amortization - Total Income Before Extraordinary Items Current Liabilities - Total 69 cheq Num 8 100 dlcq Num 8 105 dpq Num 8 170 ibq Num 8 185 lctq Num 8 95 at Num 8 Assets - Total Common/Ordinary Equity - Total Common Shares Outstanding Debt in Current Liabilities Total CARSON COLLEGE OF BUSINESS 127 ceq Num 8 176 csho Num 8 213 dlc Num 8 220 dltt Num 8 Long-Term Debt Total Earnings Per Share (Diluted) - Including Extraordinary Items Income Before Extraordinary Items Research and Development Expense Price Close - Annual - Fiscal 280 epsfi Num 8 378 ib Num 8 906 xrd Num 8 939 prcc_f Num 8

Selected Variable Definitions from Titman and Wessels (1988) Variable Definition INT/TA Ratio of intangible assets to total assets Ratio of inventory plus gross plant and equipment to total assets Ratios of investment tax credits over total assets Depreciation over total assets IGP/TA ITC/TA CARSON COLLEGE OF BUSINESS D/TA CE/TA Capital expenditures over total asset Research and development over sales RD/S

Compustat Variables Corresponding to Titman and Wessels (1988) Position Variable Name Type Length Description 95 at Num 8 Assets Total 120 capx Num 8 Capital Expenditures 225 dp Num 8 Depreciation and Amortization 395 intan Num 8 Intangible Assets Total CARSON COLLEGE OF BUSINESS 407 invt Num 8 Inventories Total 453 itcb Num 8 Investment Tax Credit (Balance Sheet) 602 ppegt Num 8 Property, Plant, and Equipment Total (Gross) 704 sale Num 8 Sales/Turnover (Net) 906 xrd Num 8 Research and Development Expense

CRSP/COMPUSTAT Merged - Link History with Used Flag (Linking Table) CRSP COMPUSTAT Covers stock market data on major stock exchanges (NYSE, AMEX, NASDAQ) Main identifiers: PERMNO and PERMCO Data after initial public offering (IPO) Data since 1925 Covers accounting data for public and private companies. Main identifier: GVKEY In general, Compustat requires that a firm has some number of years of history as public company before including it in the dataset. Data since 1950 CARSON COLLEGE OF BUSINESS

Linking Table CARSON COLLEGE OF BUSINESS

Linking Table gvkey linktype lpermno lpermco USEDFLAG linkdt linkenddt 1000 LU 25881 23369 1 19701113 19780630 1001 LU 10015 6398 1 19830920 19860731 1002 LC 10023 22159 1 19721214 19730605 1003 LU 10031 6672 1 19831207 19890816 1005 LU 61903 11 1 19730131 19830131 CARSON COLLEGE OF BUSINESS 1007 LU 10058 20 1 19731001 19790130 1007 LU 10058 20 1 19790131 19840928 1008 LC 10066 6331 1 19830825 19871030 1009 LC 10074 5271 1 19820118 19960313 1010 LU 10006 22156 1 19500501 19620130 1010 LU 10006 22156 1 19620131 19840628 1011 LC 10082 5793 1 19830321 19950928 1012 LU 10103 52 1 19780131 19891229

Eventus Presenter: Feiwei Chen CARSON COLLEGE OF BUSINESS

Introduction of Eventus Initiated by Arnold R. Cowan SAS macro based product Perform event study estimation Provide fast event-oriented data retrieval from the CRSP CARSON COLLEGE OF BUSINESS Web query and UNIX environment are included by WRDS One of the most widely used event study instruments

Event Study In finance, accounting and other business fields A method to analyze the market reaction to a specific event Firm-specific or market-wide CARSON COLLEGE OF BUSINESS Use either returns or volume around the time when the event occurred Examples: earnings announcement, M&As, new capital issues, announcements of macroeconomic variables like unemployment or trade deficit

Event Study In law and economics Measure the impact on the value of a firm Change in the regulatory environment Assess the damages CARSON COLLEGE OF BUSINESS