Sync ftp data with lftp automatically

[Andreas]

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"

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,

#!/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

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

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.