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