Download PostgreSQL

To use Postgres.app you must not have any previous installation of Postgres on you computer. Installation and setup is a breeze. We will be using the initial database that uses localhost, port 5432, and the user and database named with your system user name.

WRDS Server Environment

The R environment on your personal node on the WRDS server needs to be setup in order to query the data using R. This requires you to ssh into the server and edit the .Rprofile and .Renviron files. There are several text editing tools available to do so. The most ‘normal’ (i.e., not like vim) is nano. Additionally, you will need to encode your password using SAS following the hints page.

ssh <user_name>@wrds-cloud.wharton.upenn.edu
#then enter your password
nano .Rprofile

Once you have opened the blank .Rprofile page, copy-paste the following code and replace the username and password.

# .Rprofile
library(rJava)
.jinit(parameters="-Xmx8g")
library(RJDBC)

user <- "my_username"
pass <- '{SAS002}DBCC5712369DE1C65B19864C1564FB850F398DCF'

wrdsconnect <- function(user=<user_name>, pass=<password>){
    drv <- JDBC("com.sas.net.sharenet.ShareNetDriver", "/usr/local/sas/jdbc_driver/sas.intrnet.javatools.jar",          identifier.quote="`")
    wrds <- dbConnect(drv, "jdbc:sharenet://wrds-cloud-h.wharton.private:8551/", user, pass)
    return(wrds)
}

.First <- function(){
    wrds <<- wrdsconnect(user=user, pass=pass)
}
.Last <- function(){
    dbDisconnect(wrds)
}

After saving and exiting (ctrl+x), type the following to edit your .Rprofile.

nano .Renviron

Copy-paste the following into your .Rprofile.

# .Renviron
CLASSPATH="/usr/local/sas/jdbc_driver/sas.core.jar:/usr/local/sas/jdbc_driver/sas.intrnet.javatools.jar"

Extracting Data

Now that the environment is setup, type the following two commands to enter an R session:

qrsh #launch the wrds shell
R #start an R session

Run the following code to download the entire monthly CRSP file and write it to a csv.

sql <- "select * from CRSP.MSF" #the query to be passed to WRDS
res <- dbSendQuery(wrds, sql) #wrds is the connection from the .Rprofile; sql is the query from above
data <- fetch(res, n = -1) #connect, query, and save to data; n=-1 to select all
write.csv(data,"crsp_raw.csv") #write to a csv file

##Download the Data

Now that the data has been saved to our WRDS node, we need to download it to our computer. The easiest way is to ftp the data from WRDS to our computer using the get command.

sftp <user_name>@wrds-cloud.wharton.upenn.edu
get crsp_raw.csv /data/destination/path

##PostgreSQL Setup

Moving data to PostgreSQL is super easy. We will be following Win-Vector’s very helpful tutorial.

# read in the crsp data
crsp<-read_csv('crsp_raw.csv') 

# Create a connection to the database
library(RPostgreSQL)
library(DBI) #you will not that DBI is also used in the WRDS .Rprofile 

## Loading required package: DBI
pg = dbDriver("PostgreSQL")

# Local Postgres.app database; no password by default
# I simply use default database. If you want you could use a special crsp or wrds database.
con = dbConnect(pg, 
                user=<user_name>, 
                password="",
                host="localhost", 
                port=5432, 
                dbname=<user_name>)

# write the table into the database.
# use row.names=FALSE to prevent the query from adding the column 'row.names' to the table in the db
dbWriteTable(con,'crsp',crsp, row.names=FALSE)

Now that we have a CRSP PostgreSQL database, let’s read in the data and make a simple chart.

library(tidyverse) #for easy data manipulation
library(lubridate) #for easy date manipulation
library(ggthemes) #for economist theme

# crsp query
crsp_raw <- dbGetQuery(con, "select permno, date, shrout, shrcd, prc, cfacshr, cfacpr, ret, retx from crsp")

# process data using dplyr to get count of observations per year
crsp <- crsp_raw %>% 
    mutate(year = year(as.Date(date,"%d%b%Y"))) %>% 
    group_by(year) %>% tally()

# plot the counts by year
crsp %>% ggplot(., aes(x=year, y=n)) + geom_line(size=2,color="#E74C3C") + 
    theme_economist() +
    scale_x_continuous(breaks = round(seq(min(crsp$year), max(crsp$year), by = 10),1)) +
    annotate("text",x = 1966,y = 74000,label = "NASDAQ Listing") +
    annotate("text",x = 1979,y = 93000,label = "1980s IPO Craze") +
    annotate("text",x = 1990,y = 117542,label = "Tech Bubble")