def chaikin(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
cur_date = pg_query(PSQL.client, "SELECT max(date) FROM portfolio.day_chaikin;")[0].values[0]
nxt_id, comp_cur = det_cur_chaikin(PSQL)
total_stocks = len(comp_idx)
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status = comp_name_conv[rh_id])
if rh_id in comp_cur.keys() and cur_date <= np.datetime64(comp_cur[rh_id]):
continue
comp_data = pg_query(PSQL.client, "SELECT date, adl FROM portfolio.adl_day where rh_id = '%s';" % (rh_id))
comp_data.rename(columns = {0:'date', 1:'adl'}, inplace = True)
comp_data.sort_values('date', ascending = True, inplace = True)
if len(comp_data) < 11:
continue
adl_3_ema = calc_ema(comp_data, 3, 'adl')
adl_10_ema = calc_ema(comp_data, 10, 'adl')
adl_ema = pd.merge(adl_3_ema, adl_10_ema, left_on = 'date', right_on = 'date')
adl_ema['chaikin'] = adl_ema['adl_3_ema'] - adl_ema['adl_10_ema']
for date, chaikin in adl_ema[['date', 'chaikin']].values:
if rh_id in comp_cur.keys() and date <= np.datetime64(comp_cur[rh_id]):
continue
if chaikin != chaikin:
continue
if chaikin == np.inf or chaikin == -np.inf:
continue
script = "INSERT INTO portfolio.day_chaikin(day_chaikin_id, rh_id, date, chaikin) VALUES (%i, '%s', '%s', %.4f);" % (nxt_id, rh_id, datetime.strptime(str(date).split('.')[0], '%Y-%m-%d %H:%M:%S'), chaikin)
pg_insert(PSQL.client, script)
nxt_id += 1
comp_cur[rh_id] = date
def rsi(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
# nxt_id = 0
nxt_id, comp_cur = det_cur_rsi(PSQL)
total_stocks = len(comp_idx)
cur_date = pg_query(
PSQL.client,
"SELECT max(date) FROM portfolio.rsi_day_14;")[0].values[0]
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status=comp_name_conv[rh_id])
if rh_id in comp_cur.keys() and cur_date <= np.datetime64(
comp_cur[rh_id]):
continue
comp_data = pg_query(
PSQL.client,
"SELECT date, close_price FROM portfolio.day_prices where rh_id = '%s';"
% (rh_id))
comp_data.rename(columns={0: 'date', 1: 'close_price'}, inplace=True)
comp_data.sort_values('date', ascending=True, inplace=True)
comp_data['diff'] = comp_data['close_price'].diff()
comp_data['gains'] = comp_data['diff'].apply(lambda x: x
if x > 0 else 0)
comp_data['losses'] = comp_data['diff'].apply(lambda x: x
if x < 0 else 0)
comp_data.dropna(inplace=True)
all_avg_gain = []
all_avg_loss = []
dates = []
all_avg_gain.append(comp_data.iloc[:14]['gains'].mean())
all_avg_loss.append(comp_data.iloc[:14]['losses'].mean())
dates.append(comp_data.iloc[14]['date'])
comp_data = comp_data.iloc[15:]
for _date, cur_gain, cur_loss in comp_data[['date', 'gains',
'losses']].values:
dates.append(_date)
all_avg_gain.append((all_avg_gain[-1] * 13 + cur_gain) / 14)
all_avg_loss.append((all_avg_loss[-1] * 13 + cur_loss) / 14)
for date, gain, loss in zip(dates, all_avg_gain, all_avg_loss):
if gain == 0:
rsi = 0
elif loss == 0:
rsi = 100
else:
rsi = 100 - (100 / 1 + (gain / loss))
if rh_id in comp_cur.keys() and comp_cur[rh_id] >= date:
continue
script = "INSERT INTO portfolio.rsi_day_14(rsi_day_14_id, rh_id, date, rsi) VALUES (%i, '%s', '%s', %.3f);" % (
nxt_id, rh_id,
datetime.strptime(
str(date).split('.')[0], '%Y-%m-%d %H:%M:%S'), rsi)
pg_insert(PSQL.client, script)
nxt_id += 1
comp_cur[rh_id] = date
def det_cur_obv(psql):
# psql = PSQL
script = "select max(obv_14_roc_id) from portfolio.obv_14_roc"
_cur = pg_query(psql.client, script)
_nxt_ids = _cur.values[0][0] + 1
script = "select rh_id, max(date) from portfolio.obv_14_roc group by rh_id"
_cur_comp = pg_query(PSQL.client, script)
_comp_cur = {k:v for k,v in _cur_comp.values}
return(_nxt_ids, _comp_cur)
def det_cur_cci(psql):
# psql = PSQL
script = "select max(cci_day_20_id) from portfolio.cci_day_20"
_cur = pg_query(psql.client, script)
_nxt_ids = _cur.values[0][0] + 1
script = "select rh_id, max(date) from portfolio.cci_day_20 group by rh_id"
_cur_comp = pg_query(PSQL.client, script)
_comp_cur = {k: v for k, v in _cur_comp.values}
return (_nxt_ids, _comp_cur)
def det_cur_ma(psql):
_nxt_ids = {}
_comp_cur = {}
for period in [200, 100, 50, 20, 10]:
script = "select max(ma_day_%s_id) from portfolio.ma_day_%s" % (period,
period)
_cur = pg_query(psql.client, script)
_nxt_ids[period] = _cur.values[0][0] + 1
script = "select rh_id, max(date) from portfolio.ma_day_%s group by rh_id" % (
period)
_cur_comp = pg_query(PSQL.client, script)
_comp_cur[period] = {k: v for k, v in _cur_comp.values}
return (_nxt_ids, _comp_cur)
def stoch_osc_k(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
# all_next_id, all_comp_cur = det_cur_ema(PSQL)
cur_date = pg_query(
PSQL.client,
"SELECT max(date) FROM portfolio.sto_osc_14;")[0].values[0]
total_stocks = len(comp_idx)
nxt_id, comp_cur = det_cur_ma(PSQL)
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status=comp_name_conv[rh_id])
if rh_id in comp_cur.keys() and cur_date <= np.datetime64(
comp_cur[rh_id]):
continue
# if rh_id in comp_cur.keys():
# missing_days = pg_query(PSQL.client, "SELECT date, close_price FROM portfolio.day_prices where rh_id = '%s' and date > '%s';" % (rh_id, all_comp_cur[period][rh_id]))
# if len(missing_days) == 0:
# continue
# else:
comp_data = pg_query(
PSQL.client,
"SELECT date, close_price FROM portfolio.day_prices where rh_id = '%s';"
% (rh_id))
comp_data.rename(columns={0: 'date', 1: 'close_price'}, inplace=True)
comp_data.sort_values('date', ascending=True, inplace=True)
per_high = comp_data['close_price'].rolling(window=14).max()
per_low = comp_data['close_price'].rolling(window=14).min()
for date, current_close, lowest_low, highest_high in zip(
comp_data.date.values, comp_data.close_price.values, per_low,
per_high.values):
if rh_id in comp_cur.keys() and comp_cur[rh_id] >= date:
continue
k = (current_close - lowest_low) / (highest_high -
lowest_low) * 100
if k != k:
continue
script = "INSERT INTO portfolio.sto_osc_14(sto_osc_14_id, rh_id, date, k) VALUES (%i, '%s', '%s', %.2f);" % (
nxt_id, rh_id,
datetime.strptime(
str(date).split('.')[0], '%Y-%m-%dT%H:%M:%S'), k)
pg_insert(PSQL.client, script)
nxt_id += 1
comp_cur[rh_id] = date
def cci(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
nxt_id, comp_cur = det_cur_cci(PSQL)
total_stocks = len(comp_idx)
cur_date = pg_query(
PSQL.client,
"SELECT max(date) FROM portfolio.cci_day_20;")[0].values[0]
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status=comp_name_conv[rh_id])
if rh_id in comp_cur.keys() and cur_date <= np.datetime64(
comp_cur[rh_id]):
continue
comp_ma = pg_query(
PSQL.client,
"SELECT date, avg_price from portfolio.ma_day_20 where rh_id = '%s'"
% (rh_id))
comp_ma.rename(columns={0: 'date', 1: 'ma'}, inplace=True)
comp_ma.sort_values('date', ascending=True, inplace=True)
comp_ma.set_index('date', inplace=True)
comp_typ_price = pg_query(
PSQL.client,
"SELECT date, (high_price + low_price + close_price)/3 from portfolio.day_prices where rh_id = '%s'"
% (rh_id))
comp_typ_price.rename(columns={0: 'date', 1: 'typical'}, inplace=True)
comp_typ_price.sort_values('date', ascending=True, inplace=True)
comp_typ_price.set_index('date', inplace=True)
comp_cci = comp_typ_price.join(comp_ma)
comp_cci['typ_avg'] = comp_cci['typical'].rolling(window=20).mean()
comp_cci['abs_dev'] = abs(comp_cci['typical'] - comp_cci['typ_avg'])
comp_cci['mean_dev'] = comp_cci['abs_dev'].rolling(window=20).mean()
comp_cci['cci'] = (comp_cci['typical'] -
comp_cci['ma']) / (comp_cci['mean_dev'] * .015)
comp_cci.dropna(inplace=True)
comp_cci.reset_index(inplace=True)
for date, cci in comp_cci[['date', 'cci']].values:
if rh_id in comp_cur.keys() and comp_cur[rh_id] >= date:
continue
if cci != cci or cci == np.inf or cci == -np.inf:
continue
script = "INSERT INTO portfolio.cci_day_20(cci_day_20_id, rh_id, date, cci) VALUES (%i, '%s', '%s', %.3f);" % (
nxt_id, rh_id,
datetime.strptime(
str(date).split('.')[0], '%Y-%m-%d %H:%M:%S'), cci)
pg_insert(PSQL.client, script)
nxt_id += 1
comp_cur[rh_id] = date
def obv_roc(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
cur_date = pg_query(PSQL.client, "SELECT max(date) FROM portfolio.adl_day;")[0].values[0]
nxt_id, comp_cur = det_cur_obv(PSQL)
total_stocks = len(comp_idx)
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status = comp_name_conv[rh_id])
if rh_id in comp_cur.keys() and cur_date <= np.datetime64(comp_cur[rh_id]):
continue
comp_data = pg_query(PSQL.client, "SELECT date, close_price, volume FROM portfolio.day_prices where rh_id = '%s';" % (rh_id))
comp_data.rename(columns = {0:'date', 1:'close_price', 2:'volume'}, inplace = True)
comp_data.sort_values('date', ascending = True, inplace = True)
last_close = comp_data.iloc[0]['close_price']
obvs = [comp_data.iloc[0]['volume']]
dates = [comp_data.iloc[0]['date']]
for date, close, volume in comp_data.iloc[1:].values:
if close > last_close:
obvs.append(obvs[-1] + volume)
elif close < last_close:
obvs.append(obvs[-1] - volume)
elif close == last_close:
obvs.append(obvs[-1])
dates.append(date)
last_close = close
obv = pd.DataFrame([dates, obvs]).T
obv.rename(columns = {0: 'date', 1: 'obv'}, inplace = True)
obv['ra'] = obv['obv'].rolling(window = 14).mean()
obv['obv_roc'] = (obv['obv'] - obv['ra'].shift()) / obv['ra'].shift()
obv.dropna(inplace = True)
for date, obv in obv[['date', 'obv_roc']].values:
if rh_id in comp_cur.keys() and date <= np.datetime64(comp_cur[rh_id]):
continue
if obv != obv:
continue
if obv == np.inf or obv == -np.inf:
continue
script = "INSERT INTO portfolio.obv_14_roc(obv_14_roc_id, rh_id, date, obv_roc) VALUES (%i, '%s', '%s', %.4f);" % (nxt_id, rh_id, datetime.strptime(str(date).split('.')[0], '%Y-%m-%d %H:%M:%S'), obv)
pg_insert(PSQL.client, script)
nxt_id += 1
comp_cur[rh_id] = date
def moving_average(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
cur_date = pg_query(
PSQL.client,
"SELECT max(date) FROM portfolio.day_prices;")[0].values[0]
all_next_id, all_comp_cur = det_cur_ma(PSQL)
total_stocks = len(comp_idx)
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status=comp_name_conv[rh_id])
for period in [200, 100, 50, 20, 10]:
if rh_id in all_comp_cur[period].keys(
) and cur_date <= np.datetime64(all_comp_cur[period][rh_id]):
continue
comp_data = pg_query(
PSQL.client,
"SELECT date, close_price FROM portfolio.day_prices where rh_id = '%s';"
% (rh_id))
comp_data.rename(columns={
0: 'date',
1: 'close_price'
},
inplace=True)
comp_data.sort_values('date', ascending=True)
date_idx = comp_data['date']
dma = calc_mov_avg(comp_data, period)
for date, avg_price in zip(date_idx.values, dma.values):
if rh_id in all_comp_cur[period].keys(
) and date <= np.datetime64(all_comp_cur[period][rh_id]):
continue
if avg_price != avg_price:
continue
script = "INSERT INTO portfolio.ma_day_%s(ma_day_%s_id, rh_id, date, period, avg_price) VALUES (%i, '%s', '%s', %i, %.2f);" % (
period, period, all_next_id[period], rh_id,
datetime.strptime(
str(date).split('.')[0],
'%Y-%m-%dT%H:%M:%S'), period, avg_price)
pg_insert(PSQL.client, script)
all_next_id[period] += 1
all_comp_cur[period][rh_id] = date
def adl(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
cur_date = pg_query(PSQL.client, "SELECT max(date) FROM portfolio.adl_day;")[0].values[0]
nxt_id, comp_cur = det_cur_adl(PSQL)
total_stocks = len(comp_idx)
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status = comp_name_conv[rh_id])
if rh_id in comp_cur.keys() and cur_date <= np.datetime64(comp_cur[rh_id]):
continue
comp_data = pg_query(PSQL.client, "SELECT date, close_price, high_price, low_price, volume FROM portfolio.day_prices where rh_id = '%s';" % (rh_id))
comp_data.rename(columns = {0:'date', 1:'close_price', 2: 'high_price', 3: 'low_price', 4:'volume'}, inplace = True)
comp_data.sort_values('date', ascending = True, inplace = True)
comp_data['mfm'] = ((comp_data['close_price'] - comp_data['low_price']) - (comp_data['high_price'] - comp_data['low_price'])) / (comp_data['high_price'] - comp_data['low_price'])
comp_data['mfv'] = comp_data['mfm'] * comp_data['volume']
comp_data.dropna(inplace = True)
adl = []
for mfv in comp_data['mfv'].values:
if len(adl) == 0:
adl.append(mfv)
else:
adl.append(mfv + adl[-1])
comp_data['adl'] = adl
for date, adl in comp_data[['date', 'adl']].values:
if rh_id in comp_cur.keys() and date <= np.datetime64(comp_cur[rh_id]):
continue
if adl != adl:
continue
if adl == np.inf or adl == -np.inf:
continue
script = "INSERT INTO portfolio.adl_day(adl_day_id, rh_id, date, adl) VALUES (%i, '%s', '%s', %.4f);" % (nxt_id, rh_id, datetime.strptime(str(date).split('.')[0], '%Y-%m-%d %H:%M:%S'), adl)
pg_insert(PSQL.client, script)
nxt_id += 1
comp_cur[rh_id] = date
def exp_moving_average(comp_idx, comp_name_conv):
# comp_idx, comp_name_conv = COMP_IDX, COMP_NAME_CONV
cur_date = pg_query(
PSQL.client,
"SELECT max(date) FROM portfolio.day_prices;")[0].values[0]
all_next_id, all_comp_cur = det_cur_ema(PSQL)
total_stocks = len(comp_idx)
for stock_num, (rh_id) in enumerate(comp_idx):
progress(stock_num, total_stocks, status=comp_name_conv[rh_id])
for period in [12, 26]:
if rh_id in all_comp_cur[period].keys(
) and cur_date <= np.datetime64(all_comp_cur[period][rh_id]):
continue
weighting_mult = 2 / (period + 1)
emas = []
dates = []
if rh_id in all_comp_cur[period].keys():
missing_days = pg_query(
PSQL.client,
"SELECT date, close_price FROM portfolio.day_prices where rh_id = '%s' and date > '%s';"
% (rh_id, all_comp_cur[period][rh_id]))
if len(missing_days) == 0:
continue
missing_days.rename(columns={
0: 'date',
1: 'close_price'
},
inplace=True)
start_ema = pg_query(
PSQL.client,
"SELECT date, avg_price FROM portfolio.ema_day_%s where rh_id = '%s' and date = '%s';"
% (period, rh_id, all_comp_cur[period][rh_id]))
emas.append(start_ema[1].values[0])
dates.append(start_ema[0].values[0])
for nxt_val, nxt_date in missing_days[['close_price',
'date']].values:
emas.append((nxt_val - emas[-1]) * weighting_mult +
emas[-1])
dates.append(nxt_date)
else:
comp_data = pg_query(
PSQL.client,
"SELECT date, close_price FROM portfolio.day_prices where rh_id = '%s';"
% (rh_id))
comp_data.rename(columns={
0: 'date',
1: 'close_price'
},
inplace=True)
comp_data.sort_values('date', ascending=True)
emas.append(comp_data.iloc[:period]['close_price'].mean())
dates.append(comp_data.iloc[period]['date'])
comp_data = comp_data.iloc[period + 1:]
for nxt_val, nxt_date in comp_data[['close_price',
'date']].values:
emas.append((nxt_val - emas[-1]) * weighting_mult +
emas[-1])
dates.append(nxt_date)
for date, avg_price in zip(dates, emas):
if rh_id in all_comp_cur[period].keys(
) and date <= np.datetime64(all_comp_cur[period][rh_id]):
continue
if avg_price != avg_price:
continue
script = "INSERT INTO portfolio.ema_day_%s(ema_day_%s_id, rh_id, date, period, avg_price) VALUES (%i, '%s', '%s', %i, %.2f);" % (
period, period, all_next_id[period], rh_id,
datetime.strptime(str(date),
'%Y-%m-%d %H:%M:%S'), period, avg_price)
pg_insert(PSQL.client, script)
all_next_id[period] += 1
all_comp_cur[period][rh_id] = date
emas[-1])
dates.append(nxt_date)
for date, avg_price in zip(dates, emas):
if rh_id in all_comp_cur[period].keys(
) and date <= np.datetime64(all_comp_cur[period][rh_id]):
continue
if avg_price != avg_price:
continue
script = "INSERT INTO portfolio.ema_day_%s(ema_day_%s_id, rh_id, date, period, avg_price) VALUES (%i, '%s', '%s', %i, %.2f);" % (
period, period, all_next_id[period], rh_id,
datetime.strptime(str(date),
'%Y-%m-%d %H:%M:%S'), period, avg_price)
pg_insert(PSQL.client, script)
all_next_id[period] += 1
all_comp_cur[period][rh_id] = date
if __name__ == '__main__':
PSQL = db_connection('psql')
COMP_IDX = [
i[0] for i in pg_query(PSQL.client,
"SELECT rh_id FROM portfolio.stocks;").values
]
COMP_NAME_CONV = pg_query(PSQL.client,
'select rh_id, rh_sym from portfolio.stocks')
COMP_NAME_CONV = {k: v for k, v in COMP_NAME_CONV.values}
moving_average(COMP_IDX, COMP_NAME_CONV)
exp_moving_average(COMP_IDX, COMP_NAME_CONV)
