Setup - Authenticate to WRDS

Setup - Authenticate to WRDS#

Make a Hidden Credential File#

In order to connect to WRDS remotely, you will first need to create a .pgpass file. You can place this file in your KLC home directory. On KLC, navigate to your home directory. From a terminal session, choose your preferred text editor to create a file, such as the nano editor demonstrated below.

! nano ~/.pgpass

Within this hidden pgpass file, save one line with your WRDS login credentials:

wrds-pgdata.wharton.upenn.edu:9737:wrds:user_name:password

You can view the contents of this file with:

# To view the contents of this file, you should actually type:
! head ~/.pgpass

wrds-pgdata.wharton.upenn.edu:9737:wrds:best_user_ever:passwordy_goodness

After creating the pgpass file, you also need to restrict its permissions by running chmod 600 ~/.pgpass; this setting disallows access to the file by anyone other than you, ensuring that only your user account can read or modify it.

# restrict file permissions
!chmod 600 ~/.pgpass

Connect through Python#

To connect to the WRDS API through Python, install the wrds package. From the command line, type:

# install the `wrds` package.
! pip install wrds

Next, import the wrds package in Python.

# packages
import wrds
import pandas as pd

You can establish a connection with the WRDS server by inserting your username in the following Python command. If you have already created a .pgpass file, this line will look for the file that contains your username and corresponding password.

conn = wrds.Connection(wrds_username='best-user-ever')
#db = wrds.Connection()
# AT THIS POINT, EXPECT A DUO PUSH

Loading library list...
Done

Note that the first time you perform this step, it will trigger two-factor-authentication. Please follow the instructions here to set this up: https://wrds-www.wharton.upenn.edu/pages/about/log-in-to-wrds-using-two-factor-authentication/

You can test your connection with:

df = conn.get_table(library="crsp", table="dsf", obs=10)

# print column names
df.columns

Index(['permno', 'namedt', 'nameenddt', 'shrcd', 'exchcd', 'siccd', 'ncusip',
       'ticker', 'comnam', 'shrcls', 'permco', 'hexcd', 'cusip', 'st_date',
       'end_date', 'namedum'],
      dtype='object')

# print number of rows
len(df)

10

Sample python setup code can be found here: Python Setup Code

Connect through R#

To connect to the WRDS through R, you’ll need to install the RPostgres library.

(Note that the lines in the cell below are used to call R in this jupyter notebook.)

import rpy2
%load_ext rpy2.ipython

The rpy2.ipython extension is already loaded. To reload it, use:
  %reload_ext rpy2.ipython

Within R, type:

%%R
#install.packages("RPostgres")

NULL

Next, load the library.

%%R
library(DBI)
library(RPostgres)

Finally, create a connection with the following:

%%R
wrds <- dbConnect(
   Postgres(),
   host = 'wrds-pgdata.wharton.upenn.edu',
   port = 9737,
   dbname = 'wrds',
   sslmode = 'require',
   user = 'best-user-ever'
   )

# AT THIS POINT, EXPECT A DUO PUSH

You can test your connection with:

%%R
res <- dbSendQuery(wrds, "select * from crsp.dsf")
data <- dbFetch(res, n=10)

%%R
# print column names
names(data)

 [1] "cusip"   "permno"  "permco"  "issuno"  "hexcd"   "hsiccd"  "date"   
 [8] "bidlo"   "askhi"   "prc"     "vol"     "ret"     "bid"     "ask"    
[15] "shrout"  "cfacpr"  "cfacshr" "openprc" "numtrd"  "retx"   

%%R
# print number of rows
nrow(data)

[1] 10

Sample R setup code can be found here: R Setup Code

Lab 1#

Lab 1 - Create your pgpass file and connect to WRDS

Either in R or Python:

  1. Create and set permissions on your own hidden pgpass file.

  2. Establish a wrds connection and grab the first 10 rows of data from “company” table in Compustat (“comp”)

  3. Save the results as a csv file in your home directory and version control your file with git.

Lab 1 Answer Key

Python Code

R Code

Contents