def main(self):
code = 'USD/JPY'
self.code = code
df_info = common.select_sql(
r'I07_fx.sqlite',
'select * from %(table)s where rowid > (select max(rowid) from %(table)s) - %(key1)s'
% {
'table': 'gmofx',
'key1': 100
})
del df_info['MXN/JPY']
del df_info['uptime']
del df_info['result']
for code in ['USD/JPY', 'EUR/USD', 'EUR/JPY', 'GBP/JPY']:
try:
del df_info[code.replace("/", "") + '_result']
except:
pass
for code in ['USD/JPY', 'EUR/USD', 'EUR/JPY', 'GBP/JPY']:
df_info = self.fx_data(df_info, 1)
x_data = info.add_avg(df_info, code)
result = self.model_save2(x_data, code)
print("result", result)
sqls = common.create_update_sql(
'I07_fx.sqlite', {code.replace("/", "") + '_result': result},
'gmofx')
def STR_C(self):
#カラムの初期化
sqls = "update kabu_list set L_PL_085 = NULL ,S_PL_085 = NULL"
common.db_update('B01_stock.sqlite', sqls)
files = os.listdir(compf.CODE_DIR)
for i in files:
year_e = 2018
code = i.replace(".txt", "")
try:
y = self.ATR_stg(code, str(year_e - 4), str(year_e),
"_base_" + str(year_e))
except:
print(code, "不明なエラー発生")
continue
if len(y) > 500 and int(y.O[1]) > 150 and common.stock_req(
code, "SHELL") == 1:
dict_pl = {}
dict_w = {}
L_PL = compf.check_PL(y['plb'])
L_PL['MEMO'] = "L_PL_085"
S_PL = compf.check_PL(y['pls'])
S_PL['MEMO'] = "S_PL_085"
for T_PL in [L_PL, S_PL]:
dict_w = {}
title = code + "_" + str(year_e) + T_PL['MEMO']
if T_PL['MEMO'] == "L_PL_085":
pl = 'plb'
if T_PL['MEMO'] == "S_PL_085":
pl = 'pls'
#5年間の成績
if (T_PL['WIN'] > 58 and T_PL['PL'] > 1.1):
dict_pl.update(T_PL)
#前年の成績
y = self.ATR_stg(code, str(year_e - 0), str(year_e),
"_" + title + "_" + str(year_e))
if len(y) == 0:
continue
T_PL = compf.check_PL(y[pl])
#前年と5年前を比較して前年の方が大きいこと
if (dict_pl['PL'] < T_PL['PL']):
dict_w[dict_pl['MEMO']] = dict_pl['WIN']
# rowid取得
sqls = "select *,rowid from %(table)s where コード = '%(key1)s' ;" % {
'table': 'kabu_list',
'key1': code
}
sql_pd = common.select_sql('B01_stock.sqlite',
sqls)
sqls = common.create_update_sql(
'B01_stock.sqlite', dict_w, 'kabu_list',
sql_pd['rowid'][0]) #最後の引数を削除すると自動的に最後の行
def N225_N(self):
table_name = "rashio"
rashio = info.TOPIX_get('05:30')
dict_w = s01_gmo.check_new_data()
rashio['N225openN'] = dict_w['N225openN']
rashio['N225highN'] = dict_w['N225highN']
rashio['N225lowN'] = dict_w['N225lowN']
rashio['N225closeN'] = dict_w['N225closeN']
# 最終行ROWID取得
rid = common.last_rowid(DB_INFO, table_name)
# DBアップデート
sqls = common.create_update_sql(DB_INFO, rashio, table_name, rid) #最後の引数を削除すると自動的に最後の行
def main(self):
df = sk.fx_data(100)
for code in ['USD/JPY', 'EUR/USD', 'EUR/JPY', 'GBP/JPY']:
x_data = sk.add_avg(df.copy(), code)
filename = os.path.join(
common.MODEL,
code.replace("/", "") + '_finalized_model.sav')
clf_2 = joblib.load(filename)
pred_test_2 = clf_2.predict(x_data)
print(pred_test_2[-1])
sqls = common.create_update_sql(
'I07_fx.sqlite',
{code.replace("/", "") + '_result': pred_test_2[-1]}, 'gmofx')
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 main_bak(self):
code = 'USD/JPY'
self.code = code
df_info = common.select_sql(
r'I07_fx.sqlite',
'select * from %(table)s where rowid > (select max(rowid) from %(table)s) - %(key1)s'
% {
'table': 'gmofx',
'key1': 100
})
del df_info['MXN/JPY']
del df_info['uptime']
del df_info['result']
df_info = self.fx_data(df_info, 1)
x_data = info.add_avg(df_info, code)
print(len(x_data.columns))
result = self.model_save2(x_data, code)
print("result", result)
sqls = common.create_update_sql('I07_fx.sqlite', {'result': result},
'gmofx')
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 bloomberg_rashio(self):
table_name = "rashio"
rashio = {}
# for tick in ["BDIY","USGG5YR","USGG10YR","VIX","DJI", "FVX","SPC","IXIC","TNX"]:
# "INDU"ダウ,"ES1"SP500,"CCMP"ナスダック
#https://www.bloomberg.com/quote/CCMP:IND
ua = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_3) ' 'AppleWebKit/537.36 (KHTML, like Gecko) ' 'Chrome/55.0.2883.95 Safari/537.36 '
s = requests.session()
s.headers.update({'User-Agent': ua})
for tick in [ "CCMP","BDIY", "USGG5YR", "USGG10YR","VIX", "INDU", "ES1"]:
UURL = "https://www.bloomberg.com/quote/" + tick + ":IND"
ret = s.get(UURL)
soup = BeautifulSoup(ret.content, "lxml")
stocktable = soup.find('div', {'class': 'price'})
try:
rashio[tick + "_IND"] = stocktable.text.replace(",", "").replace(".00", "")
except:
print(tick)
self.send_msg += tick + common.create_error(sys.exc_info()) + "\n"
# 最終行ROWID取得
rid = common.last_rowid(DB_INFO, table_name)
# DBアップデート
sqls = common.create_update_sql(DB_INFO, rashio, table_name, rid) #最後の引数を削除すると自動的に最後の行
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
def tocom_up(self):
AA = ['金', 'ゴールドスポット', '白金', 'プラチナスポット', 'プラッツドバイ原油', 'ゴム']
BB = ['金 標準取引 (1kg)', 'ゴールドスポット', '白金 標準取引 (500g)','プラチナスポット', 'プラッツドバイ原油', 'ゴム']
# 信用残の推移(週次)
UURL = "http://www.tocom.or.jp/jp/souba/baibai_top10/index.html"
# テーブル情報取得
dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能)
for ii in range(1, len(dfs)):
rashio = {}
for i in range(len(AA)):
if len(dfs[ii]) > 10:
if dfs[ii].columns[0].replace("\n", "").replace(" ", "") == BB[i] or dfs[ii].columns[0].replace("\n", "") == BB[i]:
table_name = AA[i]
print(dfs[ii].columns[0])
for idx, row in dfs[ii].iterrows():
if idx < 1:
continue
if idx % 2 == 0:
rashio[row[0].replace(" ", "")] = row[1]
# ヘッダー存在チェック
new_list = [l for l in rashio]
print(new_list)
aaa = common.column_check(DB_INFO, table_name, new_list)
# DBインサート
common.insertDB3(DB_INFO, table_name, rashio)
break
table_name = "ゴム"
UURL = "http://www.tocom.or.jp/jp/market/reference/kurani_rubber.html"
# テーブル情報取得
dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能)
for ii in range(1, len(dfs)):
rashio = {}
if dfs[ii].columns[0] == "指定倉庫":
for idx, row in dfs[ii].iterrows():
if idx == 4:
for i in range(len(dfs[ii].columns)):
if i > 0:
rashio[dfs[ii].columns[i].replace("\n\t\t\t", "").replace(" ", "").replace(".", "_")] = row[i]
break
# 最終行ROWID取得
rid = common.last_rowid(DB_INFO, table_name)
# DBアップデート
sqls = common.create_update_sql(DB_INFO, rashio, table_name, rid) #最後の引数を削除すると自動的に最後の行
break
table_name = "tocom"
UURL = "http://www.tocom.or.jp/jp/souba/torikumi/index.html"
# テーブル情報取得
dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能)
CC = ['カテゴリ', '当業者売', '当業者買', '商品先物取引業者売', '商品先物取引業者買', 'ファンド・投資信託売', 'ファンド・投資信託買', '投資家売', '投資家買','取次者経由売', '取次者経由買', '外国商品先物取引業者経由売', '外国商品先物取引業者経由買', '合計売', '合計買']
col_name = {}
col_name = {dfs[0].columns[c]: CC[c] for c in range(len(dfs[0].columns))}
dfs[0] = dfs[0].rename(columns=col_name)
# DBへのインポート
for idx, row in dfs[0].iterrows():
if idx == 0:
continue
dict_w = {}
for ii in range(len(row)):
dict_w[dfs[0].columns[ii]] = row[ii]
dict_w['日付'] = common.env_time()[0][:8]
common.insertDB3(DB_INFO, table_name, dict_w)