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")