Build a SQLite Database From Quandl Data

Building a SQLlite Database in R

• The goal of this project is to build a SQLite database for end of day data for the Eurodollar Futures Market.
• After constructing the database I want to be able to pull the end of day data from the front 6 contracts.

Download Eurodollar Futures End of Day Files From Quandl

  library(Quandl)
    library(lubridate)

    ## Quandl
  quandl_password <- read.csv("C:/Users/Analytics/Desktop/Work/Credentials/quandl_credentials.csv")
    Quandl.auth(quandl_password)

    ## Create contract names
    allContracts <- vector()
    for(i in 1982:2024) {
        conH <- paste("EDH",i,sep = "")
        conM <- paste("EDM",i,sep = "")
        conU <- paste("EDU",i,sep = "")
        conZ <- paste("EDZ", i, sep = "")
        allContracts <- c(allContracts, conH, conM, conU, conZ)
    }
    allContracts <- paste("CME", allContracts, sep = "/")
    
    ## Download Contract Historical End of Day Data
    setwd("C:/Users/Analytics/Desktop/Work/Eurodollar Futures/contractMonths")
    for (con in allContracts) {
        data <- Quandl(con)
        names(data) <- c("Date", "Open", "High", "Low", "Last", "Change", "Settle", "Volume", "OpenInterest")
        data$Contract <- paste(substring(con,8,11), substring(con,5,7), sep = "")
        write.csv(data, file = paste(substring(con,5,11),"csv",sep = "."), row.names = F)
    }

Build the Database

  library(sqldf)
  setwd("C:/Users/Analytics/Desktop/Work/Eurodollar Futures/contractMonths")
  db <- dbConnect(SQLite(), dbname = "GE_eod.sqlite")

Add Each Contract File into Data Base

  for(l in 1:(length(list.files())-2)) {
    tableName <- "GE"
    dbWriteTable(conn = db, name = tableName, value = list.files()[l], row.names = F,
                 header = T, append = T)
  }

Examine Database

  dbListTables(db)

## [1] "GE"

  dbListFields(db,"GE")

##  [1] "Date"         "Open"         "High"         "Low"         
##  [5] "Last"         "Change"       "Settle"       "Volume"      
##  [9] "OpenInterest" "Contract"

  head(dbReadTable(db, "GE"))

##         Date  Open  High   Low Last Change Settle Volume OpenInterest
## 1 1982-03-15 84.96 85.00 84.88   NA     NA  84.88     17           83
## 2 1982-03-12 84.84 85.05 84.84   NA     NA  84.88    168          167
## 3 1982-03-11 85.18 85.19 84.99   NA     NA  84.99     57          203
## 4 1982-03-10 85.38 85.41 85.25   NA     NA  85.28     51          207
## 5 1982-03-09 85.50 85.58 85.35   NA     NA  85.40     89          223
## 6 1982-03-08 85.86 85.96 85.63   NA     NA  85.63    162          272
##      Contract
## 1 "1982EDH"\r
## 2 "1982EDH"\r
## 3 "1982EDH"\r
## 4 "1982EDH"\r
## 5 "1982EDH"\r
## 6 "1982EDH"\r

  dbGetQuery(db, "SELECT * FROM GE WHERE Date = '2015-12-01' ORDER BY Contract LIMIT 6")

##         Date   Open   High     Low   Last Change  Settle Volume
## 1 2015-12-01 99.530 99.540 99.5225 99.535 0.0025 99.5325 164302
## 2 2015-12-01 99.365 99.385 99.3500 99.375   0.01 99.3750 194853
## 3 2015-12-01 99.205 99.235 99.1950 99.225  0.015 99.2250 190837
## 4 2015-12-01 99.040 99.075 99.0250  99.07   0.02 99.0650 176560
## 5 2015-12-01 98.880 98.925 98.8600  98.91  0.025 98.9100 281592
## 6 2015-12-01 98.745 98.800 98.7250  98.78  0.035 98.7800 167262
##   OpenInterest    Contract
## 1      1168524 "2015EDZ"\r
## 2      1345733 "2016EDH"\r
## 3      1185995 "2016EDM"\r
## 4      1040617 "2016EDU"\r
## 5      1338895 "2016EDZ"\r
## 6       774610 "2017EDH"\r