You've successfully subscribed to MyPad Blog
Great! Next, complete checkout for full access to MyPad Blog
Welcome back! You've successfully signed in.
Success! Your account is fully activated, you now have access to all content.
Success! Your billing info is updated.
Billing info update failed.

Reading a list of sqlite files using R, manipulating them and a simple plot

Reading a list of sqlite files using R,  manipulating them and a simple plot

Continuing the topic of analyzing options data traded on the market, in this post we will cover the process of reading data from sqlite3 files, consolidating and munging the data.

Finally, we plot a simple timeseries of the call price vs. underlying for a particular instrument.

# install.packages("RSQLite")

setwd("~/Dropbox/pandora/My-Projects/repos/diary/writing/")
rm(list = ls())


library(dplyr)

library(RSQLite)
library(DBI)
require("data.table")

# Get the file list
fileList <- dir(path="./data", pattern = "*.sqlite3",full.names=TRUE, ignore.case = TRUE)
fileListShrtName <- dir(path="./data", pattern = "*.sqlite3",full.names=FALSE, ignore.case = TRUE)

counter <- 1
d_options <- list()

for (file in fileList){
  print(file)
  con <- DBI::dbConnect(RSQLite::SQLite(), dbname = file)
  dbListTables(con)
  d_options[[counter]] <- dbGetQuery(con, "SELECT * FROM options")
  counter <- counter + 1
  dbDisconnect(con)
}

dt_options <- rbindlist(d_options)

dt_options[,strike_vs_underlying:=100*(strike/underlying-1)]

dt_options[abs(strike_vs_underlying)<=10][ticker == "NAS:TSLA"][order(abs(strike_vs_underlying), decreasing=T)]

require("lubridate")

dt_options[,timestamp_f:=ymd_hms(timestamp)]
dt_options[,expiry_f:=ymd_hms(expiry)]

timestamps <- unique(dt_options$timestamp_f)

dt_options[,list(count=NROW(.I)), by=list(timestamp_f, ticker)][order(count, decreasing = T)]

dt_options[ticker == "NAS:ASPS"][,list(call_last, put_last, expiry_f)]

dt_options_tlsa <- dt_options[(call_last>0) | (put_last>0)][,list(timestamp_f, expiry_f, ticker, strike, underlying, call_quote, call_last, put_quote, put_last, strike_vs_underlying, call_openint, put_openint,count=NROW(.I))][ticker=="NAS:TSLA"][abs(strike_vs_underlying)<=10]

dt_options[(call_last>0) | (put_last>0)][,list(call_last, put_last)]

require("zoo")

d.z <- read.zoo(dt_options_tlsa[call_quote=="TSLA#B0720C795000"][,list(timestamp_f, call_last, underlying)])

plot(d.z, type = "l")