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