def main_TP(self): #トラリピ dict_ww = {} code = '米NQ100' table_name = 'NQ100_TP' #情報取得 dict_w = f02_gmo.info_get() dict_w = common.to_number(dict_w) sqls = "select *,rowid from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': '_gmo_info' } sql_pd = common.select_sql('B05_cfd_stg.sqlite', sqls) dict_t = sql_pd.to_dict('records') dict_t = common.to_number(dict_t[0]) dict_ww['H'] = max(dict_w[code + '_高'], dict_t[code + '_高']) dict_ww['L'] = min(dict_w[code + '_安'], dict_t[code + '_安']) dict_ww['C'] = dict_w[code] #前日の仕掛け情報取得 sqls = "select *,rowid from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': table_name } sql_pd = common.select_sql('B05_cfd_stg.sqlite', sqls) if len(sql_pd) == 0: common.insertDB3('B05_cfd_stg.sqlite', table_name, dict_ww) return dict_l = sql_pd.to_dict('records') dict_l = common.to_number(dict_l[0]) sp_work = [] #決済確認 if dict_l.get('poji'): sp_work = common.to_number(dict_l['poji'].split("_")) for i in reversed(range(len(sp_work))): if dict_ww['L'] > sp_work[i] + 100: dict_ww['LongPL'] += 100 sp_work.pop(i) dict_ww['poji'] = "_".join([str(n) for n in sp_work]) print(dict_ww) #前日トレード確認 if dict_l.get('trade'): poji = [] sp_work2 = common.to_number(dict_l['trade'].split("_")) if len(sp_work2) > 0: for i in reversed(range(len(sp_work2))): if dict_ww['H'] > sp_work2[i]: print("追加", sp_work2[i], dict_ww['L']) sp_work.append(sp_work2[i]) dict_ww['poji'] = "_".join([str(n) for n in sp_work]) #仕掛け trade = [] VAL = dict_ww['C'] - dict_ww['C'] % 100 + 100 for ii in range(3): VVAL = VAL + 100 * ii if VVAL not in sp_work: trade.append(str(VVAL)) dict_ww['trade'] = "_".join(trade) print(dict_ww) common.insertDB3('B05_cfd_stg.sqlite', table_name, dict_ww)
def TOPIX_get(self, Stime='15:15'): table_name = 'topixL' yest_day = str(datetime.date.today() - datetime.timedelta(days=0)).replace("-", "/") + ' ' + Stime dict_e = {} # 限月取得 dict = {'table': table_name, 'key1': yest_day, 'key2': ''} sqls = "select *,rowid from %(table)s where now > '%(key1)s'" % dict sql_pd = common.select_sql('I08_futures.sqlite', sqls) num = len(sql_pd)-2 gen = sql_pd.loc[num, '限月'] # 限月の妥当性チェック 使えるか? if sql_pd.loc[num, '現在値'] == '--': gen = sql_pd.loc[num+1, '限月'] # 終値期限now取得 dict = {'table': table_name, 'key2': yest_day, 'key3': gen} sqls = "select *,SUBSTR(now,12,2) as T,rowid from %(table)s where 限月 = '%(key3)s' and now > '%(key2)s'" % dict sql_pd = common.select_sql('I08_futures.sqlite', sqls) num = len(sql_pd)-1 dict_e['TOPIXnow' + Stime[:2]] = sql_pd.loc[0, 'now'] dict_e['TOPIX_S' + Stime[:2]] = sql_pd.loc[0, '始値'] dict_e['TOPIX_H' + Stime[:2]] = sql_pd.loc[0, '高値'] dict_e['TOPIX_L' + Stime[:2]] = sql_pd.loc[0, '安値'] dict_e['TOPIX_C' + Stime[:2]] = sql_pd.loc[0, '現在値'] dict_e['TOPIX_CL' + Stime[:2]] = sql_pd.loc[0, '前日終値'] dict_e = common.to_number(dict_e) return dict_e
def retry_check(self): sqls = 'select *,rowid from retry where status < 0 ;' sql_pd = common.select_sql(self.INFO_DB, sqls) for i, row in sql_pd.iterrows(): common.to_number(row) dict_w = {} bybypara = dict(row) try: result, msg = f02_gmo.gmo_cfd_exec(bybypara) if str(msg).count('正常終了'): dict_w['status'] = 0 else: dict_w['status'] = row['status'] + 1 except: dict_w['status'] = row['status'] + 1 self.send_msg += u'CFDトレードリトライ異常終了_' + bybypara['code'] + "\n" sqls = common.create_update_sql( self.INFO_DB, dict_w, 'retry', bybypara['rowid']) #最後の引数を削除すると自動的に最後の行
def n225_topix_avg(self): dict_w = {} yest_day = str(datetime.date.today() - datetime.timedelta(days=30)).replace("-", "/") dict = {'table': 'rashio', 'key1': yest_day} sqls = "select *,rowid from %(table)s where now > '%(key1)s'" % dict sql_pd = common.select_sql('I01_all.sqlite', sqls) num = len(sql_pd)-1 dict_w['N225_乖離avg30'] = round(sql_pd.loc[num, 'N225closeD'] / sql_pd.N225closeD.rolling(num).mean()[num],3) dict_w['N225_HighLow30'] = round((sql_pd.loc[num, 'N225closeD'] - sql_pd['N225closeD'].min()) / (sql_pd['N225closeD'].max() - sql_pd['N225closeD'].min()),3) dict_w['TOPIX_乖離avg30'] = round(sql_pd.loc[num, 'TOPIX_C15'] / sql_pd.TOPIX_C15.rolling(num).mean()[num],3) dict_w['TOPIX_HighLow30'] = round((sql_pd.loc[num, 'TOPIX_C15'] - sql_pd['TOPIX_C15'].min()) / (sql_pd['TOPIX_C15'].max() - sql_pd['TOPIX_C15'].min()),3) #DBの型変換する。 dict_w = common.to_number(dict_w) # 最終行ROWID取得 rid = common.last_rowid('I01_all.sqlite', 'rashio') # DBアップデート sqls = common.create_update_sql('I01_all.sqlite', dict_w, 'rashio', rid) #最後の引数を削除すると自動的に最後の行 return dict_w
def breakout_ma_two(self, window0, window9, col, table): status = 0 data = {'L_flag': "", 'S_flag': "", 'S_PL': "", 'L_PL': ""} sqls = "select %(key1)s from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': table, 'key1': col } sql_pd = common.select_sql(self.INFO_DB, sqls) data['S3_R'] = float(sql_pd.loc[0, col]) # データ更新、データフレームに引き出す tablename = col + "_breakout_ma_two" common.insertDB3(self.INFO_DB, tablename, data) sqls = "select *,rowid from " + tablename tsd = common.select_sql(self.INFO_DB, sqls) tsd.S3_R.dropna() cnt = len(tsd) - 1 if cnt < 10: return status data['avg_' + str(window0)] = tsd.S3_R.rolling(window0).mean().shift(1)[cnt] data['avg_' + str(window9)] = tsd.S3_R.rolling(window9).mean().shift(1)[cnt] cnt2 = len(tsd) - 2 if tsd.loc[cnt2, 'S_flag'] is None or tsd.loc[cnt2, 'S_flag'] == "": S_flag = 0 else: S_flag = float(tsd.loc[cnt2, 'S_flag']) if tsd.loc[cnt2, 'L_flag'] is None or tsd.loc[cnt2, 'L_flag'] == "": L_flag = 0 else: L_flag = float(tsd.loc[cnt2, 'L_flag']) # window0 = 32 797.98438 # window9 = 12 798.41667 data['S_flag'] = tsd.loc[cnt2, 'S_flag'] data['L_flag'] = tsd.loc[cnt2, 'L_flag'] common.to_number(data) status = 0 #仕切り if data['avg_' + str(window0)] > data[ 'avg_' + str(window9)] and S_flag != 0: # exit short-position data['S_PL'] = S_flag - data['S3_R'] # レポート用 data['S_flag'] = 0 status = -2 elif data['avg_' + str(window0)] < data[ 'avg_' + str(window9)] and L_flag != 0: # exit short-position data['L_PL'] = data['S3_R'] - L_flag # レポート用 data['L_flag'] = 0 #仕掛け elif data['avg_' + str(window0)] < data[ 'avg_' + str(window9)] and S_flag == 0: # entry short-position data['S_flag'] = data['S3_R'] status = -1 elif data['avg_' + str(window0)] > data[ 'avg_' + str(window9)] and L_flag == 0: # entry short-position data['L_flag'] = data['S3_R'] status = 1 # status = 2 # rowid取得 sqls = "select *,rowid from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': tablename } sql_pd = common.select_sql(self.INFO_DB, sqls) sqls = common.create_update_sql(self.INFO_DB, data, tablename, sql_pd['rowid'][0]) return status
def breakout_ma_three(self, window0, window9, window5, col, table): status = 0 data = {'L_flag': "", 'S_flag': "", 'S_PL': "", 'L_PL': "", 'S3_R': ""} sqls = 'select "%(key1)s" from %(table)s where rowid=(select max(rowid) from %(table)s) ;' % { 'table': table, 'key1': col } sql_pd = common.select_sql(self.INFO_DB, sqls) data['S3_R'] = float(sql_pd.loc[0, col]) # データ更新、データフレームに引き出す tablename = col + "_breakout_ma_three" common.insertDB3(self.INFO_DB, tablename, data) col_name = ', '.join([k for k in data.keys()]) sqls = "select *,rowid from %(table)s" % {'table': tablename} tsd = common.select_sql(self.INFO_DB, sqls) tsd.S3_R.dropna() cnt = len(tsd) - 1 if cnt < 10: return status data['avg_' + str(window0)] = tsd.S3_R.rolling(window0).mean().shift(1)[cnt] data['avg_' + str(window9)] = tsd.S3_R.rolling(window9).mean().shift(1)[cnt] data['avg_' + str(window5)] = tsd.S3_R.rolling(window5).mean().shift(1)[cnt] # レポート用 # init---------------------------------- cnt2 = len(tsd) - 2 if tsd.loc[cnt2, 'S_flag'] is None or tsd.loc[cnt2, 'S_flag'] == "": S_flag = 0 else: S_flag = float(tsd.loc[cnt2, 'S_flag']) if tsd.loc[cnt2, 'L_flag'] is None or tsd.loc[cnt2, 'L_flag'] == "": L_flag = 0 else: L_flag = float(tsd.loc[cnt2, 'L_flag']) data['S_flag'] = tsd.loc[cnt2, 'S_flag'] data['L_flag'] = tsd.loc[cnt2, 'L_flag'] common.to_number(data) # entry short-position if S_flag == 0 and data['avg_' + str(window0)] < data[ 'avg_' + str(window5)] < data['avg_' + str(window9)]: data['S_flag'] = data['S3_R'] status = -1 elif (data['avg_' + str(window0)] > data['avg_' + str(window5)] or data['avg_' + str(window5)] > data['avg_' + str(window9)] ) and S_flag != 0: # exit short-position data['S_PL'] = S_flag - data['S3_R'] data['S_flag'] = 0 status = -2 # entry short-position elif L_flag == 0 and data['avg_' + str(window0)] > data[ 'avg_' + str(window5)] > data['avg_' + str(window9)]: data['L_flag'] = data['S3_R'] status = 1 elif (data['avg_' + str(window0)] < data['avg_' + str(window5)] or data['avg_' + str(window5)] < data['avg_' + str(window9)] ) and L_flag != 0: # exit short-position data['L_PL'] = data['S3_R'] - L_flag data['L_flag'] = 0 # rowid取得 sqls = "select *,rowid from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': tablename } sql_pd = common.select_sql(self.INFO_DB, sqls) sqls = common.create_update_sql(self.INFO_DB, data, tablename, sql_pd['rowid'][0])
def breakout_simple_f(self, window0, window9, f0, f9, col, table): status = 0 data = { 'L_flag': "", 'S_flag': "", 'L_SUM': "", 'S_PL': "", 'L_PL': "" } sqls = "select %(key1)s from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': table, 'key1': col } sql_pd = common.select_sql(self.INFO_DB, sqls) data['S3_R'] = float(sql_pd.loc[0, col]) # データ更新、データフレームに引き出す tablename = col + "_breakout_simple_f" common.insertDB3(self.INFO_DB, tablename, data) sqls = "select *,rowid from " + tablename tsd = common.select_sql(self.INFO_DB, sqls) tsd.S3_R.dropna() cnt = len(tsd) - 1 if cnt < 10: return 0 data['max_s' + str(window0)] = tsd.S3_R.rolling(window0).max().shift( 1)[cnt] #ub0 data['min_s' + str(window0)] = tsd.S3_R.rolling(window0).min().shift(1)[cnt] #2 data['max_e' + str(window9)] = tsd.S3_R.rolling(window9).max().shift( 1)[cnt] #ub9 data['min_e' + str(window9)] = tsd.S3_R.rolling(window9).min().shift( 1)[cnt] #lb9 data['avg_l' + str(f0)] = tsd.S3_R.rolling(f0).mean().shift(1)[cnt] #f0 data['avg_s' + str(f9)] = tsd.S3_R.rolling(f9).mean().shift(1)[cnt] #f9 # init---------------------------------- cnt2 = len(tsd) - 2 if tsd.loc[cnt2, 'S_flag'] is None or tsd.loc[cnt2, 'S_flag'] == "": S_flag = 0 else: S_flag = float(tsd.loc[cnt2, 'S_flag']) if tsd.loc[cnt2, 'L_flag'] is None or tsd.loc[cnt2, 'L_flag'] == "": L_flag = 0 else: L_flag = float(tsd.loc[cnt2, 'L_flag']) data['S_flag'] = tsd.loc[cnt2, 'S_flag'] data['L_flag'] = tsd.loc[cnt2, 'L_flag'] common.to_number(data) c = data['S3_R'] status = 0 if c > data['max_e' + str(window9)] and S_flag != 0: # exit short-position data['S_PL'] = S_flag - c # レポート用 data['S_flag'] = 0 status = -2 elif c < data['min_e' + str(window9)] and L_flag != 0: # exit short-position data['L_PL'] = c - L_flag # レポート用 data['L_flag'] = 0 status = 2 elif c < data['min_s' + str(window0)] and S_flag == 0 and L_flag == 0 and data[ 'avg_s' + str(f9)] > data['avg_l' + str(f0)]: data['S_flag'] = c status = -1 elif c > data['max_s' + str(window0)] and S_flag == 0 and L_flag == 0 and data[ 'avg_s' + str(f9)] < data['avg_l' + str(f0)]: data['L_flag'] = c status = 1 """ #仕切りチェック if status == -1 and L_flag != 0: print("仕切り1") self.byby_exec_fx(2, col, 1) if status == 1 and S_flag != 0: print("仕切り2") self.byby_exec_fx(-2, col, 1) """ # rowid取得 sqls = "select *,rowid from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': tablename } sql_pd = common.select_sql(self.INFO_DB, sqls) sqls = common.create_update_sql(self.INFO_DB, data, tablename, sql_pd['rowid'][0]) return status