README.md

In erikcs/wrdsr: Connect to WRDS

wrdsr

Connect to and query WRDS trough an R session (simple and convenient wrapper around RJDBC and the WRDS Java drivers)

Installation

install.packages("devtools")
devtools::install_github("erikcs/wrdsr")

Usage

Add your WRDS credentials to ~/.wrdsrc (X:\Users\YourName\Documents\.wrdsrc on Windows)

myusername
{SAS002}MySAS-HashedPassword

or provide them as arguments to connect("user", "password")

library(wrdsr)
wrds = connect()

Fetch data with query

>query(wrds, "SELECT * FROM COMP.NAMES (OBS=5)")
 # A tibble: 5 × 9
   gvkey                    conm   tic     cusip        cik   sic  naics year1 year2
*  <chr>                   <chr> <chr>     <chr>      <chr> <chr>  <chr> <dbl> <dbl>
1 001000   A & E PLASTIK PAK INC  AE.2 000032102       <NA>  3089   <NA>  1961  1977
2 001001 A & M FOOD SERVICES INC AMFD. 000165100 0000723576  5812    722  1978  1985
3 001002                AAI CORP  AAIC 000352104 0000001745  3825   <NA>  1960  1972
4 001003   A.A. IMPORTING CO INC  ANTQ 000354100 0000730052  5712 442110  1980  1989
5 001004                AAR CORP   AIR 000361105 0000001750  5080 423860  1965  2015

Look at what data is available

# What libraries are available
listLibraries(wrds)

# What are the tables in the CRSP libraries
listTables(wrds, "CRSP")

# What are the variables in the DSF table in CRSP
listColumns(wrds, "CRSP", "DSF")

Issues (OS X)

Java is required. rJava is installed automatically, but if R throws some Java error, the following may help

1. $ sudo R CMD javareconf

2. install.packages("rJava", type='source') (done with R run from the shell, this requires the OSX SDK)

3. $ sudo ln -f -s $(/usr/libexec/java_home)/jre/lib/server/libjvm.dylib /usr/local/lib

Extended query examples

The number of daily security prices in CRSP by share code

> query(wrds, "SELECT HSHRCD, count(PERMNO) as numPERMNOs
+             FROM CRSPA.DSFHDR
+             GROUP BY HSHRCD
+             ORDER BY numPERMNOs DESC")
# A tibble: 22 × 2
   HSHRCD numPERMNOs
*   <dbl>      <dbl>
1      11      15924
2      10       8429
3      73       2190
4      12       1655
5      31        984
6      14        703
7      44        496
8      18        442
9      71        292
10     48        158
# ... with 12 more rows

Joining some columns from the daily stock file and header file in CRSP

> query(wrds,
+       "SELECT ABS(PRC) as PRC, date,  HTICK, HSECSTAT
+       FROM CRSPA.DSF
+       JOIN CRSPA.DSFHDR
+       ON CRSPA.DSF.PERMNO = CRSPA.DSFHDR.PERMNO
+       WHERE CRSPA.DSFHDR.HTICK='AAPL")
# A tibble: 9,093 × 4
       PRC       DATE HTICK HSECSTAT
*    <dbl>      <chr> <chr>    <chr>
1  28.8125 1980-12-12  AAPL        R
2  27.3125 1980-12-15  AAPL        R
3  25.3125 1980-12-16  AAPL        R
4  25.9375 1980-12-17  AAPL        R
5  26.6875 1980-12-18  AAPL        R
6  28.3125 1980-12-19  AAPL        R
7  29.6875 1980-12-22  AAPL        R
8  30.9375 1980-12-23  AAPL        R
9  32.5625 1980-12-24  AAPL        R
10 35.5625 1980-12-26  AAPL        R
# ... with 9,083 more rows

The number of firms in the CRSP universe by year

> query(wrds,
      "SELECT count(unique(PERMCO)) as cnt, year(date) as yr
      FROM CRSPA.DSF
      GROUP BY yr
      ORDER BY yr DESC")
# A tibble: 92 × 2
     cnt    yr
*  <dbl> <dbl>
1   5930  2016
2   6046  2015
3   6011  2014
4   5896  2013
5   5885  2012
6   5990  2011
7   6133  2010
8   6300  2009
9   6613  2008
10  7045  2007
# ... with 82 more rows

Joining CRSP tables DSF and DSFNAMES as provided through the WRDS web interface

> query(wrds,
      "SELECT A.DATE, A.PERMNO, A.RETX, B.TICKER, B.COMNAM, B.TRDSTAT, B.NAICS
      FROM CRSPA.DSF A
      LEFT JOIN CRSPA.DSENAMES B
      ON A.PERMNO = B.PERMNO
      AND B.NAMEDT <= A.DATE <= B.NAMEENDT
      WHERE A.PERMNO=14593")
# A tibble: 9,093 × 7
       DATE PERMNO         RETX TICKER             COMNAM TRDSTAT NAICS
*       <chr>  <dbl>        <dbl>  <chr>              <chr>   <chr> <chr>
1  1982-06-21  14593 -0.004807692   AAPL APPLE COMPUTER INC       A  <NA>
2  1982-03-19  14593  0.089795917   AAPL APPLE COMPUTER INC       A  <NA>
3  1981-09-16  14593 -0.020202020   AAPL APPLE COMPUTER INC       A  <NA>
4  1982-09-21  14593  0.020905923   AAPL APPLE COMPUTER INC       A  <NA>
5  1982-02-02  14593  0.006172840   AAPL APPLE COMPUTER INC       A  <NA>
6  1981-07-31  14593  0.012626262   AAPL APPLE COMPUTER INC       A  <NA>
7  1982-05-05  14593 -0.011857707   AAPL APPLE COMPUTER INC       A  <NA>
8  1981-04-30  14593  0.020134227   AAPL APPLE COMPUTER INC       A  <NA>
9  1982-08-05  14593 -0.038647342   AAPL APPLE COMPUTER INC       A  <NA>
10 1981-10-30  14593  0.012618297   AAPL APPLE COMPUTER INC       A  <NA>
# ... with 9,083 more rows

erikcs/wrdsr documentation built on May 22, 2019, 12:23 p.m.