Packages

The list of packages tidyverse, data.table and RPostgres is run through:

Opening the connection

You then need to input your username and password from WRDS in wrds_username and wrds_password, and can open an SQL connection using PostGreSQL:

wrds <- dbConnect(Postgres(),
                  host = 'wrds-pgdata.wharton.upenn.edu',
                  port = 9737,
                  user = wrds_username,
                  password = wrds_password,
                  dbname = 'wrds',
                  sslmode = 'require')

Querying Data

SQL chunk, output database

In R-markdown, you must include output.var = "comp_funda" if you want to output the SQL query to a data frame, here called comp_funda:

```{sql, connection = wrds, output.var = "comp_funda"}
select GVKEY, CUSIP, DATADATE FYEAR, AT, LT, COGS, XINT, XSGA
from COMP.FUNDA where INDFMT='INDL' and DATAFMT='STD' and CONSOL='C' and POPSRC='D'
```

You may then use comp_funda in a regular R chunk:

comp_funda %>%
  head

##    gvkey     cusip      fyear    at    lt  cogs  xint  xsga
## 1 001000 000032102 1961-12-31    NA    NA    NA    NA    NA
## 2 001000 000032102 1962-12-31    NA    NA    NA 0.010    NA
## 3 001000 000032102 1963-12-31    NA 0.345 1.065 0.020 0.346
## 4 001000 000032102 1964-12-31 1.416 0.809 1.474 0.033 0.431
## 5 001000 000032102 1965-12-31 2.310 1.818 1.342 0.062 0.506
## 6 001000 000032102 1966-12-31 2.430 1.596 2.828 0.095 0.679

SQL chunk, no exporting

If you do not and only run the following chunk:

```{sql, connection = wrds}
select GVKEY, CUSIP, DATADATE FYEAR, AT, LT, COGS, XINT, XSGA
from COMP.FUNDA where INDFMT='INDL' and DATAFMT='STD' and CONSOL='C' and POPSRC='D'
```

you then get:

select GVKEY, CUSIP, DATADATE FYEAR, AT, LT, COGS, XINT, XSGA
from COMP.FUNDA where INDFMT='INDL' and DATAFMT='STD' and CONSOL='C' and POPSRC='D'

Table 1: Displaying records 1 - 10
gvkey	cusip	fyear	at	lt	cogs	xint	xsga
001000	000032102	1961-12-31	NA	NA	NA	NA	NA
001000	000032102	1962-12-31	NA	NA	NA	0.010	NA
001000	000032102	1963-12-31	NA	0.345	1.065	0.020	0.346
001000	000032102	1964-12-31	1.416	0.809	1.474	0.033	0.431
001000	000032102	1965-12-31	2.310	1.818	1.342	0.062	0.506
001000	000032102	1966-12-31	2.430	1.596	2.828	0.095	0.679
001000	000032102	1967-12-31	2.456	1.712	2.701	0.097	0.692
001000	000032102	1968-12-31	5.922	3.351	5.055	0.073	1.322
001000	000032102	1969-12-31	28.712	18.501	25.186	0.523	7.242
001000	000032102	1970-12-31	33.450	22.906	30.529	0.850	9.420

R chunk

A Final option is to path the wrds connection to a dbSendQuery function in a r chunk:

data.comp.funda <- wrds %>% 
  dbSendQuery(., 
  "select GVKEY, CUSIP, DATADATE FYEAR, AT, LT, COGS, XINT, XSGA
  from COMP.FUNDA where INDFMT='INDL' and DATAFMT='STD' and CONSOL='C' and POPSRC='D'") %>%
  dbFetch(., n = -1)

data.comp.funda %>% 
  head

##    gvkey     cusip      fyear    at    lt  cogs  xint  xsga
## 1 001000 000032102 1961-12-31    NA    NA    NA    NA    NA
## 2 001000 000032102 1962-12-31    NA    NA    NA 0.010    NA
## 3 001000 000032102 1963-12-31    NA 0.345 1.065 0.020 0.346
## 4 001000 000032102 1964-12-31 1.416 0.809 1.474 0.033 0.431
## 5 001000 000032102 1965-12-31 2.310 1.818 1.342 0.062 0.506
## 6 001000 000032102 1966-12-31 2.430 1.596 2.828 0.095 0.679

Intro to Structured Query Language (SQL)

Code