Tutorial on Financial Data - WRDS

Tutorial on Financial Data - WRDS

Navigating WRDS: Overview of Financial Data on WRDS CRSP, Compustat, and IBES Denys Glushkov Rabih Moussawi WRDS Librarian Colloquium Washington, D.C., June 15, 2009 Agenda What is WRDS? Sources of Financial Data Overview of Compustat, CRSP, and IBES Data WRDS Tools and Support: How to Get the Help You Need? How Can Researchers Use WRDS More Efficiently? WRDS Overview Single and flexible source to extract data from leading research databases

Variety of disciplines: Accounting, Finance, Economics, Banking, Insurance, Marketing, Statistics, + Standardized queries: Every database webpage has the same format: query + variable documentation + list of manuals, overviews, & research applications Ideal for Data linking: e.g. CRSP (stock prices, dividends, splits), Linking CRSP and COMPUSTAT, Matching IBES and CRSP Data, + linking sample programs Internet-based platform with multiple access options Web queries: compatible with various browsers and operating systems (nothing to install) Standardized, easy-to-use, point-and-click interface, unlimited downloads (~ 2GB size limit per query) Easy variable and company name searches, and other tools Fast extracts and record retrievals through TerraBytes of data PC SAS CONNECT remote libraries UNIX X-Windows and batch job capabilities

Full-time research and technical support Online help (variable definitions, data sources, equations, manuals, tutorials, sample programs, FAQs), email support, and 24/7 network monitoring Data Overviews, Research Applications, Knowledge Base and [email protected] We are WRDS users too WRDS History Originally developed for Wharton needs: Centralize data management and research support Provide faculty and PhD candidates with web-based, and interactive research tools

Grown from 2 subscribers in 1997 to 250+ subscribers in 2009: All top academic and non-profit research institutions We support over 18,000 faculty and other researchers accounts Data increased from 60 GB to 30+ TB Originally accounting and financial data; now includes economic, banking, insurance and marketing data Several Free Data Sources Partnerships with leading data vendors: Grown from 2 to 40+ data providers Data Licensing and Access with WRDS Databases la carte WRDS Subscriber

Data Licensing Agreement Data Vendor WRDS Subscription Agreement Re-distribution agreement WRDS WRDS Technology Flexibility to Connect and Get Data WRDS server User PC Libraries/Labs

On the road WRDS Account Types Web Faculty Full time Staff UNIX queries Expiration login** x x x x

PhD Candidates x Master Students Research Assistants Visitors* x x x x Shared Class Accounts Library & IP-based x x dates x x x

x x x * Visitors must have formal, temporary appointment ** Also allows for PC SAS CONNECT sessions x Sell-Side Analysts (brokers) Accounting Data (Analyst) Forecasts Data Compustat & others IBES, FirstCall, & others - BS, IS, and CF numbers from 10-K, 10-Q filings - Quarterly and Annual Freqs (observed with lags)

- Financial, Accounting, and Industry Specific Data (e.g. SSS, Verified Oil Reserves) - Executive Compensation Data SEC EDGAR, IAPD, - Analyst expectations about companies future Earnings (future EPS, g(sales), or SSS) - Valuation assessments: Recommendations, Price Target - Revisions and new estimates on daily frequency Market Data CRSP, OptionMetrics, & others - Prices and Returns of equities and bonds - Shares Outstanding, Volume, and Market Capitalization - Historical Data, no selection or survival biases - Prices (NAVs) and Returns of REITS and Closed end funds - Prices, Volumes, and Implied Volatilities of traded Options - Bond Prices, Returns, and Ratings (S&P Ratings, FISD, Trace)

Exchanges, & Ownership Data Thomson Reuters Ownership Data - Ownership by companies insiders and large blockholders - Aggregate Ownership by institutions with >$100million in discretionary assets , at the security level (e.g. hedge funds, mutual funds Vanguard Group) - Ownership by mutual fund management companies, at the fund level (e.g. Vanguard S&P500 Index Fund, etc) Data on WRDS Easy and Flexible Extracts S&Ps Compustat Accounting and Financial Data Compustat North-America: U.S. and Canadian fundamental and market information on around 31,000 active and inactive publicly held companies, from 1950-present Compustat Global: fundamental, market, and currency data for more than 30,000

publicly traded companies in global markets (>80 countries), from 1988-present Fundamental data available on an annual and quarterly frequencies with thousands of Income Statement, Balance Sheet, Statement of Cash Flows, and supplemental & industry-specific data items Market data available on a monthly and daily frequencies with Prices, Dividends, Returns, Trading Volume , Shares Outstanding and Short-Interest Information Information on Indices, Segments, Banks, Incentive plans, Pension Data Items, as well as: Executive Compensation and S&P Credit Ratings Xpress In the summer of 2007, WRDS adopted the Xpressfeed format of Compustat North

America, containing more details on a wider array of companies Compustat Example Live Demo Get Current Assets, Inventories, and Long-Term Debt for Microsoft, Dell, and IBM between Jan 2000 and April 2009 http://wrds-web.wharton.upenn.edu/wrds/ds/comp/funda/index.cfm Basic Compustat Structure Fundamental Data Compustat Annual & Quarterly Fundamentals: Other Data Executive Compensatio n North America & Global

Security Data Company Financial and Accounting Data North America & Global Point-InTime S&P Credit Ratings Xpress Segment Industry Specific WRDS Support: Compustat Overview to Compustat XPressfeed Database, FAQs, and WRDS Reference Materials http://wrds-web.wharton.upenn.edu/wrds/support/Data/_001Manuals%20and%20Overviews/_001Compustat/index.cfm

Comprehensive Dataset and Variable List http://wrds-web.wharton.upenn.edu/wrds/support/Data/_002Dataset%20List.cfm?dataVendor=comp Financial Statements, S&P 500 Index Constituents, and other Tools: http://wrds-web.wharton.upenn.edu/wrds/aux_/finstate/indexqtr.cfm http://wrds-web.wharton.upenn.edu/wrds/ds/comp/spindex/index.cfm Sample Programs: extracts, filing dates, portfolios, earnings surprises, # of segments, etc. http://wrds-web.wharton.upenn.edu/wrds/support/Data/_003Sample%20Programs/Compustat/index.cfm Research Applications: book-to-market, linking, etc. http://wrds-web.wharton.upenn.edu/wrds/support/Data/_004Research%20Applications/index.cfm CRSP Stock Market Data Center for Research in Security Prices (CRSP) is a research center at the Booth School of Business of the University of Chicago Comprehensive collection of daily and monthly security price, return, and volume data for the NYSE, AMEX and NASDAQ stock markets

Daily and Monthly data for roughly 28,000 securities of Domestic companies and ADRs traded on major exchanges (no OTC), from 1925present Complete historical information (bias-free): Accurate accounting of special distributions and stock splits in return calculation Keep delisting companies (pre-M&A or bankruptcies), and delisting returns Additionally provide stock indices, beta- and cap-based portfolios, treasury bond and risk-free rates, CRSP/Compustat Merged Database, REITs, and mutual fund databases CRSP Example Live Demo 1. Get monthly prices, return and volume information for Microsoft and Ford from 1925 to 2008 2. Find all stocks that were trading in NYSE at the end of November 1929, and get their month-end prices, return and volume information http://wrds-web.wharton.upenn.edu/wrds/ds/crsp/mstk/index.cfm?navGroup=AnnualUpdateMonthlyStocks Basic CRSP Structure Monthly Security Data CRSP

Other Market Data Security Data Other Products: - CRSP-Compustat Merged Product (CCM) - CRSP Mutual Fund Database - CRSP Ziman REITs Database Stock Data Event Files Indices and Decile Portfolios Treasuries

and Inflation WRDS Support: CRSP Comprehensive Dataset and Variable Lists, FAQs, Manuals & Data Guides http://wrds-web.wharton.upenn.edu/wrds/support/Data/_002Dataset%20List.cfm?dataVendor=crsp http://wrds-web.wharton.upenn.edu/wrds/support/Data/_001Manuals%20and%20Overviews/_002CRSP/index.cfm Linking CRSP-Compustat Data (with and without CCM Product) http://wrds-web.wharton.upenn.edu/wrds/support/Data/_001Manuals%20and%20Overviews/_002CRSP/index.cfm Tools: Returns + Decile Assignments , Translate to PERMNO/PERMCO , Mutual Fund Returns & Fama-French , Market Indices , Events and Names http://wrds.wharton.upenn.edu/home/tools.shtml

Sample Programs: Data Extracts, CCM and merging by CUSIP, Calculate CAPM beta, Excess returns, Portfolio formations, plots, Event studies, Mutual fund data http://wrds-web.wharton.upenn.edu/wrds/support/Data/_003Sample%20Programs/CRSP/index.cfm Research Applications: Compounded returns, Momentum and Governance Portfolios, Rolling Regressions, Beta Estimation, Event Studies etc. http://wrds-web.wharton.upenn.edu/wrds/support/Data/_004Research%20Applications/index.cfm Thomson Reuters IBES Analyst Forecast Data Analysts earnings and sales forecasts (+), consensus estimates, and Buy/Hold/Sell Recommendations Comprehensive Global Coverage: Domestic and International public companies from 1980present Features up to 26 forecast measures including GAAP and pro-forma

EPS, revenue/sales, net income, ROA, ROE, pre-tax profit and operating profit, EBIDTA, etc. Company indentifying information and exchange rates NEW data on price targets, company level footnotes and restated actuals (all in detailed and summary formats) IBES Example Live Demo Get price target data for Lehman Brothers between July -Sep 2008 (e.g., value, horizon, announcement date, analyst name) http://wrds-web.wharton.upenn.edu/wrds/ds/ibes/ptgdet/index.cfm Basic IBES Structure IBES Adjusted Unadjusted Recommendation s Detail

Summary Detail Summary WRDS Support: IBES Detailed overview of IBES and empirical issues http://wrds-web.wharton.upenn.edu/wrds/support/Additional%20Support/WRDS%20Presentations/_000user2007/analyst_data.pdf Linking IBES and CRSP http://wrds-web.wharton.upenn.edu/wrds/support/Data/_005Linking%20Databases/_000Linking%20IBES%20and%20CRSP%20Data.cfm Important updates on data quality and changes to IBES vintages http://wrds-web.wharton.upenn.edu/wrds/support/Data/_001Manuals%20and%20Overviews/_003I-B-E-S/index.cfm Sample programs and Research Applications: Calculate earnings surprises, work with unadjusted data, link recommendations and estimates, and many more

http://wrds-web.wharton.upenn.edu/wrds/support/Data/_003Sample%20Programs/I-B-E-S/index.cfm Other Popular Databases on WRDS NYSEs Trades And Quotes (TAQ) Database: High-frequency data Intraday trades and quotes for all securities on Major US Exchanges Thomson Reuters Ownership Databases: 13F, Mutual Funds, Insiders RiskMetrics Governance, and KLD Social Rating datasets: Governance Provisions: Dual Class, Poison Pills, and other antitakeover provision dummies Board of Directors: list of board members, with additional information (ownership, bio,) Shareholder Proposals and voting records: NEW Datasets on types of proposals by shareholders and votes garnered during annual meetings KLD Social and Environmental Records: criteria to measure corporate social responsibility, such as environment, employee relations, human rights, governance, community issues

S&P Credit Ratings Xpress, TRACE and FISD Bond Data Free Datasets (CBOE Indexes, DJ Averages, Fama-French, Liquidity factors) WRDS Tools: Company & Variable Search Company search tool http://wrds.wharton.upenn.edu/cgi-bin/tools/lookupcomp.cgi Allows users to search for a company by CUSIP, Ticker or company name across a host of different databases on WRDS (CRSP, Compustat, Thomson, TAQ, Insiders, etc) Variable search tool http://wrds.wharton.upenn.edu/cgi-bin/tools/lookupvar.cgi Allows users to search for a given variable within either variable name or label or both across a wide range of various databases on WRDS (CRSP, Compustat, IBES, TAQ, Global Insight, etc)

How to Get Support Research and Technical Support Online Help (24/7) Database Manuals plus additional support documentation Data Overviews: http://wrds.wharton.upenn.edu/support/dataoverview.shtml e.g. OptionMetrics Overview Research Applications: http://wrds.wharton.upenn.edu/support/researchapp.shtml e.g. Portfolio Construction, Event Studies Sample Programs: http://wrds.wharton.upenn.edu/support/dslist/dsl.shtml e.g. Merging CRSP and Compustat Variable Search: http://wrds.wharton.upenn.edu/cgi-bin/tools/lookupvar.cgi

e.g. Compensation Company Search: http://wrds.wharton.upenn.edu/cgi-bin/tools/lookupcomp.cgi e.g. Microsoft WRDS Knowledge Base: FAQ archive of answers to common user questions http://wrds.wharton.upenn.edu/cgi-bin/mywrds/knowledgebase.cgi e.g. How to find IPO data [email protected]: Interactive users questions and suggestions http://forum.wharton.upenn.edu/forums/ e.g. Fama and MacBeth Regressions Email support at [email protected] (Monday-Friday, 9a-5p EST) Researchers and Technical Experts ready to assist with:

Data extraction, merging, and management. Programming and technical problems Other research concerns Advanced WRDS Features Access Data Remotely on WRDS Server What if a web query does not do all that you need it to do? Example: Find all companies in 1997 with sales greater than 1 billion, total assets greater than 5 billions, and with more than 30 years of publicly reported financial statements. Advantages Disadvantages Unix Simple set up. Line-based editing. Only batch runs. PC-SAS/Connect

Interactive runs. Limited UNIX commands. Each method can perform a basic data extract, combining different variables from various datasets, and serve as the first step in a full-fledged statistical program. There is no need to download and import data into a separate system or even a second program to complete the analysis. SAS Connect makes use of both SAS menus and windows (program/log/output) for batch-style sessions with extras. Can alternate between server and PC processing. PC-SAS / Connect Concept SAS Windows software installed on your desktop Standalone: your dataset in your PC Remote access WRDS data by connecting to wrds.wharton.upenn.edu data server Use WRDS powerful Unix server processing resources

Access Unix permanent (750MB) & temp (4.5TB) disk spaces Steps: 1. Connect to WRDS server %let wrds = wrds.wharton.upenn.edu 4016; options comamid = TCP remote = WRDS; signon username = _prompt_; 2. Remote Submit rsubmit; {Program} endrsubmit; PC-SAS / Connect Example Find all companies in Compustat in 1997, with sales greater than 1 billion, total assets greater than 5 billions, and more than 30 years of publicly reported financial statements as of fiscal year 1997 Compustat Variables: http://wrds.wharton.upenn.edu/ds/compq/funda/doc.shtml PC-SAS Solution: %let wrds = wrds.wharton.upenn.edu 4016;

options comamid=TCP remote=WRDS; signon username=_prompt_; libname home remote "~" server=wrds; rsubmit; libname home "~"; proc sql; create table home.demo (where=(fyear=1997 and sale>=1000 and at>=5000 and firm_age>=30)) as select fyear, conm, tic, gvkey, sale, at, (fyear - min(fyear)) as Firm_Age from comp.funda where not missing(at) and consol="C" and indfmt="INDL" and datafmt="STD" and popsrc="D" group by gvkey; quit; endrsubmit; PC-SAS / Connect WRDS Support We are happy to assist you with all your inquiries. For questions, please to contact us at: [email protected]

Recently Viewed Presentations

  • Improving Oxygen Prescription and Titration using novel ...

    Improving Oxygen Prescription and Titration using novel ...

    Improving Oxygen Prescription and Titration using novel silicone wristbands . Dr Sarah Forster. Academic Clinical Fellow in Respiratory Medicine. National COPD Audit Programme 27th July 2016
  • Class 03. The Lady Tasting Tea - University of Virginia

    Class 03. The Lady Tasting Tea - University of Virginia

    Class 03. The Lady Tasting Tea. R A Fisher1890-1962. Rothamstead Experimental Station. Circa 1917. ... The Null Hypothesis. She is guessing. ... The drug and placebo are equally effective. There is no such thing as a "hot hand" in sports....


    It was created so that it could go through clothing and protective gear, such as gas masks. It was called mustard gas because of its smell, it was also the most effective chemical weapon of WWI because it penetrated anything,...
  • Identity and Inverse Matrices

    Identity and Inverse Matrices

    In this case, a graphingcalculator can be used to find the inverse matrix._____In the matrix equation Ax=B, matrix A is the coefficient matrix, X is the matrix ofvariables, and B is the matrix of constants. Finding the Inverse of a...
  • Witness from Science--Biomimetics  -- Romans 1:20 For the

    Witness from Science--Biomimetics -- Romans 1:20 For the

    Witness from Science--Biomimetics科学见证--仿生学 Romans 1:20 "For the invisible things of him from the creation of the world are clearly seen, being understood by the things that are made, even his eternal power and Godhead; so that they are without excuse:"...
  • On Demand Writing - rowan.k12.ky.us

    On Demand Writing - rowan.k12.ky.us

    On Demand Writing Original PowerPoint from Muhlenberg County, Kentucky ... to respond to a text/ graphic/ chart. Format? The type of writing you are to do: letter feature article, editorial, or speech. ... Pre-write Do any of the pre-write techniques...
  • Dynamics of Human Resource Management

    Dynamics of Human Resource Management

    Human Resource Management. HRM is the process of managing people in organizations in a structured and thorough manner. ... He considers that HRM policies & practices include compensation, culture of reward, team work, harmonization, quality - all the activities related...
  • Diapositiva 1

    Diapositiva 1

    Moras. Las hojas de la mora. No tiene efectos tóxicos. Han demostrado efecto protector contra el cáncer de hígado. En la India, la paratha es una mezcla de hojas de mora con harina.