This is my approach to sync options data from ivolatility with mysql.
I created a cronjob (/etc/crontab) which will run every 4 hours a shell script "run.sh"
I use lftp to sync a folder with the option data,
lftp is called with another script "ivol", which logs transfered files in the transfer.log file, this file is needed to unzip new files
IMPORTER is a program I have written which parses the extracted csv files and writes the data to mysql. I have for each expiration a new table.
Table schema is
the source for the importer is at https://gitlab.com/an.langer/ivol-options-importer keep in mind i just wrote it for myself, so its not optimized.
I created a cronjob (/etc/crontab) which will run every 4 hours a shell script "run.sh"
Code:
0 */4 * * * root /mnt/HC_Volume_3161081/scripts/run.sh > /mnt/HC_Volume_3161081/logs/`date +\%Y\%m\%d\%H\%M\%S`-cron.log 2 >&1
I use lftp to sync a folder with the option data,
Code:
#!/bin/bash
SRC_FOLDER=/mnt/HC_Volume_3161081
TRANS_LOG=$SRC_FOLDER/ftp/transfer.log
ZIP_FOLDER=$SRC_FOLDER/unzipped/
IMPORTER=/mnt/HC_Volume_3161081/scripts/ivol-options-importer
rm -f $TRANS_LOG
rm -rf $ZIP_FOLDER
mkdir $ZIP_FOLDER
lftp -f $SRC_FOLDER/scripts/ivol
if [ ! -f $TRANS_LOG ]; then
echo "no new updates"
exit 0
fi
for F in $(awk '{print $5}' $TRANS_LOG); do
unzip $F -d $ZIP_FOLDER
done
$IMPORTER -input="$ZIP_FOLDER" -mysql="user:[email protected](127.0.0.1:3306)/options_history"
lftp is called with another script "ivol", which logs transfered files in the transfer.log file, this file is needed to unzip new files
Code:
set xfer:log true
set xfer:log-file "/mnt/HC_Volume_3161081/ftp/transfer.log"
open ftp://user:[email protected]
mirror -v --only-newer / /mnt/HC_Volume_3161081/ftp/
IMPORTER is a program I have written which parses the extracted csv files and writes the data to mysql. I have for each expiration a new table.
Table schema is
Code:
CREATE DATABASE `options_history` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N'*/;
CREATE TABLE `expirations` (
`symbol` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
`expiration` date NOT NULL,
`class` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
PRIMARY KEY (`symbol`,`expiration`,`class`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS %table_name% (
symbol varchar(20),
exchange varchar(20),
company_name varchar(20),
trade_date date,
stock_price_close float,
option_symbol varchar(30),
option_expiration date,
strike float,
call_put varchar(5),
style varchar(10),
ask float,
bid float,
mean_price float,
settlement float,
iv float,
volume int,
open_interest int,
stock_price_for_iv float,
forward_price float,
delta float,
vega float,
gamma float,
theta float,
rho float,
primary key(trade_date,strike,call_put)
);
the source for the importer is at https://gitlab.com/an.langer/ivol-options-importer keep in mind i just wrote it for myself, so its not optimized.
Last edited: