def get_opts(stx=None):
stxdb.db_create_missing_table('options', sql_create_options)
stx_list = [] if stx is None else stx.split(',')
dt = stxcal.move_busdays(datetime.strftime(datetime.now(), '%Y-%m-%d'), 0)
l_exps = stxcal.long_expiries()
c = pycurl.Curl()
c.setopt(pycurl.SSL_VERIFYPEER, 0)
c.setopt(pycurl.SSL_VERIFYHOST, 0)
first = True
for stk in stx_list:
y_exps = get_chain(c, stk, l_exps[0], dt, 'w' if first else 'a')
first = False
exps = [val for val in l_exps if val in y_exps]
for exp in exps[1:]:
get_chain(c, stk, exp, dt, 'a')
stxdb.db_upload_file(file_name, 'options', 5)
def upload_prices(self, in_dir, stx):
df = pd.read_csv('{0:s}/prices.txt'.format(self.upload_dir),
sep='\t',
header=0)
df['symbol'] = df['symbol'].str.replace('_', '.')
df['symbol'] = df['symbol'].str.replace('-', '.')
df['symbol'] = df['symbol'].str.replace('$', '^')
for stk in stx:
stk_df = df.query("symbol=='{0:s}'".format(stk))
stk_df, splits = self.get_and_adjust_for_splits(stk_df)
for dt, ratio in splits.items():
stxdb.db_write_cmd(
"INSERT INTO dividends VALUES ('{0:s}','{1:s}',{2:f},0) ON"
" CONFLICT (stk, dt) DO NOTHING".format(stk, dt, ratio))
fname = '{0:s}/eod_upload.txt'.format(self.upload_dir)
stk_df.to_csv(fname, sep='\t', header=False, index=False)
stxdb.db_upload_file(fname, 'eods')
def load_eoddata_file(self, ifname, dt, dtc, stks='', batch=False):
upload_lines = []
stk_list = [] if stks == '' else stks.split(',')
# db_stx, _ = self.create_exchange()
with open(ifname, 'r') as ifile:
lines = ifile.readlines()
for line in lines[1:]:
tokens = line.replace(dtc, dt).strip().split(',')
stk = tokens[0].strip()
if (stk_list and stk not in stk_list) or ('/' in stk) or \
('*' in stk) or (stk in ['AUX', 'PRN']):
continue
# if stk not in db_stx:
# insert_stx = "INSERT INTO equities VALUES "\
# "('{0:s}', '', 'US Stocks', 'US')".format(stk)
# stxdb.db_write_cmd(insert_stx)
o = int(100 * float(tokens[2]))
hi = int(100 * float(tokens[3]))
lo = int(100 * float(tokens[4]))
c = int(100 * float(tokens[5]))
v = int(tokens[6])
if v == 0 or o < lo or o > hi or c < lo or c > hi or \
len(tokens[0]) > 6 or o >= 2147483647 or hi >= 2147483647 \
or lo >= 2147483647 or c >= 2147483647:
continue
v = v // 1000
if v == 0:
v = 1
if batch:
upload_lines.append(
'{0:s}\t{1:s}\t{2:d}\t{3:d}\t{4:d}\t{5:d}\t{6:d}\t0\n'.
format(stk, dt, o, hi, lo, c, v))
else:
upload_lines.append([stk, dt, o, hi, lo, c, v, 0])
if batch:
with open(self.eod_name, 'w') as f:
for line in upload_lines:
f.write(line)
stxdb.db_upload_file(self.eod_name, self.eod_tbl, '\t')
else:
stxdb.db_insert_eods(upload_lines)
def upload_splits(self):
with open(self.fname, 'r') as f:
lines = f.readlines()
lines = list(set(lines))
dct = {}
for l in lines:
tokens = l.split('\t')
ticker = tokens[0].strip()
dct[ticker] = ''
db_stx = {x[0]: 0 for x in stxdb.db_read_cmd("select * from equities")}
print('len(dct) = {0:d}, len(db_stx) = {1:d}'.
format(len(dct), len(db_stx)))
for stk in dct:
if stk not in db_stx:
insert_stx = "INSERT INTO equities VALUES "\
"('{0:s}', '', 'US Stocks', 'US')".format(stk)
print('insert_stx = {0:s}'.format(insert_stx))
stxdb.db_write_cmd(insert_stx)
try:
stxdb.db_upload_file(self.fname, 'dividends', '\t')
except Exception as ex:
print('Failed to upload {0:s}: {1:s}'.format(self.fname, str(ex)))
args = parser.parse_args()
print('stx_hd: stock name = {0:s}, data file = {1:s}'.format(
args.stockname, args.filepath))
df = pd.read_csv(args.filepath, )
df.drop(labels='Adj Close', axis=1, inplace=True)
df.insert(loc=0, column='stk', value=args.stockname)
df['stk'] = args.stockname
df['oi'] = 0
print(df.head())
print("The dataframe has {0:d} rows".format(len(df)))
print("Columns has {} elements: {}".format(len(df.columns), df.columns))
df.columns = ['stk', 'dt', 'o', 'hi', 'lo', 'c', 'v', 'oi']
df['o'] *= 100
df['hi'] *= 100
df['lo'] *= 100
df['c'] *= 100
df['v'] /= 1000
df.o = df.o.round(0)
df.hi = df.hi.round(0)
df.lo = df.lo.round(0)
df.c = df.c.round(0)
df['o'] = df['o'].apply(int)
df['hi'] = df['hi'].apply(int)
df['lo'] = df['lo'].apply(int)
df['c'] = df['c'].apply(int)
df['v'] = df['v'].apply(int)
print(df.head())
df.to_csv('/tmp/a.csv', sep='\t', header=False, index=False)
stxdb.db_upload_file('/tmp/a.csv', 'eods')
def load_opts_daily(self, opt_fname):
# print('{0:s} -load_opts_daily'.format(stxcal.print_current_time()))
invalid_days = [
'2002-02-01', '2002-02-04', '2002-02-05', '2002-02-06',
'2002-02-07', '2002-05-30', '2002-05-31', '2002-06-14',
'2002-06-17', '2002-12-02', '2002-12-03', '2002-12-04',
'2002-12-05', '2002-12-06', '2002-12-09', '2002-12-10'
]
dt = '{0:s}-{1:s}-{2:s}'.format(opt_fname[-12:-8], opt_fname[-8:-6],
opt_fname[-6:-4])
if dt in invalid_days:
return 0, 0
# print('dt = {0:s}'.format(dt))
exp_0 = stxcal.next_expiry(dt, min_days=0)
# print('exp_0 = {0:s}'.format(exp_0))
exp_1 = stxcal.next_expiry(exp_0)
# print('exp_1 = {0:s}'.format(exp_1))
exps = [str(exp_0), str(exp_1)]
spot_dct, opt_dct, spots, opts = {}, {}, [], []
stx = {}
sep = ' '
# print('opt_fname = {0:s}'.format(opt_fname))
with open(opt_fname) as csvfile:
frdr = csv.reader(csvfile)
for row in frdr:
stk = row[0]
# print('dt = {0:s}, stk = {1:s}, exps = {2:s}'.
# format(dt, stk, str(exps)))
# print('row = {0:s}'.format(str(row)))
try:
spot = int(100 * float(row[1]))
cp = row[5][:1]
exp = str(datetime.strptime(row[6], '%m/%d/%Y').date())
strike = int(100 * float(row[8]))
bid = int(100 * float(row[10]))
ask = int(100 * float(row[11]))
volume = int(row[12])
# print('exp = {0:s}'.format(exp))
except:
# print(traceback.print_exc())
continue
# print('1')
if exp not in exps or ask == 0 or spot >= 2147483647 or \
strike >= 2147483647 or bid >= 2147483647 or \
ask >= 2147483647 or volume >= 2147483647:
continue
# print('2')
if stk not in spot_dct:
spot_dct[stk] = spot
spots.append([stk, dt, spot])
opt_key = ':'.join([exp, stk, cp, str(strike)])
if opt_key not in opt_dct:
opt_dct[opt_key] = [bid, ask, volume]
opts.append([exp, stk, cp, strike, dt, bid, ask, volume])
# print('len(spots) {0:d}, len(opts) = {1:d}'.format(
# len(spots), len(opts)))
spots_upload_file = '{0:s}/spots.txt'.format(self.upload_dir)
opts_upload_file = '{0:s}/opts.txt'.format(self.upload_dir)
with open(spots_upload_file, 'w') as spots_file:
for s in spots:
spots_file.write('{0:s}\t{1:s}\t{2:d}\n'.format(
s[0], s[1], s[2]))
with open(opts_upload_file, 'w') as opts_file:
for o in opts:
opts_file.write('{0:s}\t{1:s}\t{2:s}\t{3:d}\t{4:s}\t{5:d}\t'
'{6:d}\t{7:d}\t0\n'.format(
o[0], o[1], o[2], o[3], o[4], o[5], o[6],
o[7]))
if self.upload_spots:
stxdb.db_upload_file(spots_upload_file, self.spot_tbl, '\t')
if self.upload_options:
stxdb.db_upload_file(opts_upload_file, self.opt_tbl, '\t')
d_spots, d_opts = len(spots), len(opts)
print('{0:s}\t{1:s}\t{2:5d}\t{3:6d}'.format(
stxcal.print_current_time(), dt, d_spots, d_opts))
return d_spots, d_opts
def test_4_bulk_upload(self):
with open(self.file_name, 'w') as f:
f.write(self.bulk_data)
stxdb.db_upload_file(self.file_name, self.tbl_name, ',')
res = stxdb.db_read_cmd(self.sql_select)
self.assertEqual(len(res), 4)