def bloomberg(self): AA = ["energy", "metals", "agriculture"] BB = ["energy", "markets/commodities/futures/metals","markets/commodities/futures/agriculture"] for num in range(len(AA)): table_name = 'bloomberg_list' UURL = r"https://www.bloomberg.co.jp/" + BB[num] dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能) # 新規追加確認 https://www.bloomberg.co.jp/markets/commodities/futures/metals for i in range(len(dfs)): for idx, row in dfs[i].iterrows(): dict_w = {} for ii in range(len(row)): dict_w[dfs[i].columns[ii]] = str(row[ii]) if "単位" in dict_w.keys(): pass else: dict_w["単位"] = "" dict_w["価格"] = "" dict_w["先物契約中心限月"] = "" common.insertDB3(DB_INFO, table_name, dict_w) table_name = "bonds" UURL = "https://www.bloomberg.co.jp/markets/rates-bonds/government-bonds/us" dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能) # 新規追加確認 for i in [0, 1]: for idx, row in dfs[i].iterrows(): dict_w = {} for ii in range(len(row)): dict_w[dfs[i].columns[ii]] = str(row[ii]) common.insertDB3(DB_INFO, table_name, dict_w)
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 work4(self): # 2222222本日の先物取引情報 table_name = "futures" UURL = "https://www.traders.co.jp/domestic_stocks/invest_tool/futures/futures_top.asp" dfs = common.read_html(common.Chorme_get(UURL), 1) # header=0,skiprows=0(省略可能) for ii in range(len(dfs)): print(ii,dfs[ii].columns[0]) if dfs[ii].columns[0] == "SELL": num = ii break # カラムの入れ替え CC = ['証券会社名', 'SELL_225', 'BUY_225', 'NET_225', '日付', 'SELL_TOPIX','BUY_TOPIX', 'NET_TOPIX', '更新日', 'SELL_225M', 'BUY_225M', 'NET_225M'] col_name = {} col_name = {dfs[num].columns[c]: CC[c] for c in range(len(dfs[num].columns))} dfs[num] = dfs[num].rename(columns=col_name) # DBへのインポート for idx, row in dfs[num].iterrows(): dict_w = {} for ii in range(len(row)): dict_w[dfs[num].columns[ii]] = row[ii] dict_w['更新日'] = common.env_time()[1] dict_w['日付'] = common.env_time()[0][:8] common.insertDB3(DB_INFO, table_name, dict_w) # 信用残の推移(週次) table_name = "rashio" UURL = "https://www.traders.co.jp/margin/transition/transition.asp" # テーブル情報取得 dfs = pd.read_html(common.Chorme_get(UURL), header=0) num = len(dfs)-1 # 最新のみ取得 print(dfs[num]) list_w = ["申込日", "売り株数", "売り前週比", "売り金額", "売り前週比","買い株数", "買い前週比", "買い金額", "買い前週比", "損益率", "信用倍率"] for idx, row in dfs[num].iterrows(): if idx == 3: cnt = 0 print(len(row)) print(len(list_w)) for ii in range(len(row)): rashio[list_w[ii]] = row[cnt] cnt += 2 if list_w[ii] == "信用倍率": break break common.insertDB3(DB_INFO, table_name, rashio)
def work3(self): allLines = open("test.txt",encoding="utf-8").read() print(allLines) exit() # f = open("TEST.CSV", 'r') # dataReader = csv.reader(f) # for row in dataReader: # print(row) # exit() # exit() # 新規追加確認 # for row in dataReader: # 2222222本日の先物取引情報 table_name = "futures" UURL = "https://www.traders.co.jp/domestic_stocks/invest_tool/futures/futures_top.asp" # res = common.Chorme_get(UURL) browser = webdriver.PhantomJS() # ライブスターログイン画面にアクセス browser.get(UURL) common.create_file("test.txt", browser.page_source) exit(9) dfs = pd.read_html(res, header=0) for ii in range(len(dfs)): print("XXX",ii,dfs[ii].columns[0]) if dfs[ii].columns[0] == "先物・手口情報": num = ii +1 break dfs[num].to_csv("TEST.CSV") common.create_file("test.txt",dfs[num]) # カラムの入れ替え CC = ['証券会社名', 'SELL_225', 'BUY_225', 'NET_225', '日付', 'SELL_TOPIX','BUY_TOPIX', 'NET_TOPIX', '更新日', 'SELL_225M', 'BUY_225M', 'NET_225M'] col_name = {} col_name = {dfs[num].columns[c]: CC[c] for c in range(len(dfs[num].columns))} dfs[num] = dfs[num].rename(columns=col_name) # DBへのインポート for idx, row in dfs[num].iterrows(): dict_w = {} for ii in range(len(row)): dict_w[dfs[num].columns[ii]] = row[ii] dict_w['更新日'] = common.env_time()[1] dict_w['日付'] = common.env_time()[0][:8] common.insertDB3(DB_INFO, table_name, dict_w)
def JPbond(self): table_name = "JPbond" rashio = {} UURL = "http://port.jpx.co.jp/jpx/template/quote.cgi?F=tmp/future_daytime" dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能) # 新規追加確認 for idx, row in dfs[0].iterrows(): dict_w = {} if row[0].count("長期国債先物"): dfs[0] = dfs[0].rename(columns={'Unnamed: 0': '銘柄名','日中取引': '始値', '清算値段': '高値', '制限値幅上限下限': '安値', '建玉残高': '現在値','Unnamed: 7': '前日比', 'Unnamed: 8': '取引高', 'Unnamed: 9': '売り気配', 'Unnamed: 10': '売り気配数量', 'Unnamed: 11': '買い気配','Unnamed: 12': '買い気配数量', 'Unnamed: 13': '清算値段', 'Unnamed: 14': '制限値幅上限下限', 'Unnamed: 15': '建玉残高'}) for ii in range(len(row)): if str(row[ii]).count("("): sp = str(row[ii]).split("(") dict_w[dfs[0].columns[ii]] = sp[0] else: dict_w[dfs[0].columns[ii]] = str(row[ii]) break common.insertDB3(DB_INFO, table_name, dict_w)
def fx_daily(self): AA = ["01", "02", "04", "08", "13", "06", "14", "03"] BB = ["USDJPY", "EURJPY", "GBPJPY", "AUDJPY","NZDJPY", "CADJPY", "CHFJPY", "EURUSD"] table_name = "FX_daily" for i in range(len(AA)): UURL = "https://info.ctfx.jp/service/market/csv/" + \ AA[i] + "_" + BB[i] + "_D.csv" dfs = pd.read_csv(UURL, header=0, encoding="cp932") dict_w = {} for idx, row in dfs.iterrows(): for ii in range(len(row)): if 1 < ii: dict_w[dfs.columns[ii]] = round(float(row[ii]), 4) else: dict_w[dfs.columns[ii]] = row[ii] break dict_w['通貨ペア'] = BB[i] common.insertDB3(DB_INFO, table_name, dict_w)
def traders_web_W(self): # 1111111投資動向週間 table_name = 'investment_weekly' UURL = "https://www.traders.co.jp/domestic_stocks/stocks_data/investment_3/investment_3.asp" dfs = common.read_html2(common.Chorme_get(UURL), 0) # header=0,skiprows=0(省略可能) temp = common.temp_path("csv", os.path.basename(__file__) + "investment.csv") dfs[1].to_csv(temp) f = open(temp, 'r') dataReader = csv.reader(f) # 新規追加確認 for row in dataReader: if row[1] == '最新週': dict_w = {'週間': common.env_time()[1][:10], '海外投資家': row[2], '生損保': row[3], '銀行': row[4], '信託銀行': row[5], 'その他金融': row[6], '小計_金融法人': row[7],'事業法人': row[8], 'その他法人': row[9], '投信': row[10], '計_法人': row[11], '現金': row[12], '信用': row[13], '計_現金信用': row[14]} # 重複チェック sqls = "select *,rowid from %(table)s where rowid=(select max(rowid) from %(table)s) ;" % { 'table': table_name} sql_pd = common.select_sql(DB_INFO, sqls) if len(sql_pd) > 0: if dict_w['海外投資家'] != sql_pd.loc[0, '海外投資家']: common.insertDB3(DB_INFO, table_name, dict_w) else: common.insertDB3(DB_INFO, table_name, dict_w) print(table_name, dict_w)
def tocom_gen(self): AA = ['金', '白金', 'プラッツドバイ原油', 'ゴム', 'とうもろこし', 'ゴールドスポット', 'プラチナスポット'] BB = [4, 20, 72, 84, 88, 12, 28] # 信用残の推移(週次) UURL = "http://www.tocom.or.jp/jp/souba/souba_sx/index.html" # テーブル情報取得 dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能) print(len(dfs)) print(dfs[100].ix[0, 0]) if dfs[100].ix[0, 0] != "日中取引計": self.send_msg += "TOCOM弦月チェックテーブル構成が変わりました。確認してください" + "\n" for ii in range(len(AA)): dict_w = {} table_name = "限月"+AA[ii] for c in range(2): cnt = 0 if c == 0: # 日中 col_list = list(dfs[BB[ii]].columns) num = BB[ii] else: # 夜間 col_list = [t + "L" for t in col_list] num -= 2 df = dfs[num].sort_index(ascending=False) # ソート for idx, row in df.iterrows(): for i in range(len(row)): if col_list[i] == ' - ': continue # スポットは現月がないのでスキップ if cnt == 0: dict_w[col_list[i]] = row[i] else: dict_w[col_list[i]+str(cnt)] = row[i] cnt += 1 print(DB_INFO, table_name, dict_w) common.insertDB3(DB_INFO, table_name, 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 byby_exec_fx(self, PL, code, amount): result = 0 if PL in (1, 2, -1, -2): if PL == 1: bybypara = { 'code': code, 'amount': amount, 'buysell': '買', 'kubun': '新規', 'nari_hiki': '', 'settle': 0, 'comment': code + '_成行買い' } if PL == 2: # 買い決済 bybypara = { 'code': code, 'amount': amount, 'buysell': '買', 'kubun': '決済', 'nari_hiki': '', 'settle': -1, 'comment': code + '_買い決済' } if PL == -1: bybypara = { 'code': code, 'amount': amount, 'buysell': '売', 'kubun': '新規', 'nari_hiki': '', 'settle': 0, 'comment': code + '_成行売り' } if PL == -2: # 売り決済 bybypara = { 'code': code, 'amount': amount, 'buysell': '売', 'kubun': '決済', 'nari_hiki': '', 'settle': -1, 'comment': code + '_売り決済' } else: return result print("bybypara1", bybypara) if code.count("JPY") or code.count("USD"): result, msg, browser = f03_ctfx.f03_ctfx_main(bybypara) else: for iii in range(3): try: result, msg = f02_gmo.gmo_cfd_exec(bybypara) print(msg) if msg.count('正常終了'): break except: pass else: bybypara['status'] = -5 msg = '異常終了' common.insertDB3(self.INFO_DB, "retry", bybypara) self.send_msg += bybypara['comment'] + msg + "\n" return result
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
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) if __name__ == '__main__': # 土曜日は5 datetime.datetime.now().weekday() info = e01_day_stg() argvs = sys.argv if argvs[1] == "daily_cfd": dict_w = f02_gmo.info_get() common.insertDB3('B05_cfd_stg.sqlite', '_gmo_info', dict_w) info.stg_main() info.cfd_poji_check() info.main_TP() #トラリピ hours = datetime.datetime.now().hour minutes = datetime.datetime.now().minute if argvs[1] == "retry_check" and minutes > 30: info.retry_check() #20190218 if minutes > 40: info.main_test(hours) common.mail_send(u'CFDトレード', info.send_msg) print("end", __file__)
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)
def rashio19(self): rashio = info.TOPIX_get() # 225日次情報 dict_w = s01_gmo.check_new_data() rashio['N225openD'] = dict_w['N225openD'] rashio['N225highD'] = dict_w['N225highD'] rashio['N225lowD'] = dict_w['N225lowD'] rashio['N225closeD'] = dict_w['N225closeD'] # 本日の相場情報 table_name = "rashio" UURL = "https://www.morningstar.co.jp/RankingWeb/IndicesTable.do" # テーブル情報取得 dfs = common.read_html2(UURL, 0) # header=0,skiprows=0(省略可能) flag = 0 for idx, row in dfs[0].iterrows(): if row[0] == "値上がり" or flag == 1: rashio[row[0]] = row[1] flag = 1 # 指数一覧取得 table_name = "rashio" UURL = "https://indexes.nikkei.co.jp/nkave/index" ret = requests.get(UURL) soup = BeautifulSoup(ret.content, "lxml") # カラム名取得 col_tmp = [] res = soup.find_all("a", attrs={"class": "list-title font-16 divlink"}) for a in res: col_tmp.append(a.string) if a.string == '日経総合株価指数': break # データ取得 val_tmp = [] res = soup.find_all("div", attrs={"class": "col-xs-6 col-sm-2"}) for a in res: val_tmp.append(a.string) # 辞書作成 for i in range(len(col_tmp)): # d = dict(zip(val_tmp,col_tmp)) if val_tmp[i] is None: break rashio[col_tmp[i]] = val_tmp[i] try: #一時的対応エラー2018/7/19 # 信用残の推移(週次) table_name = "rashio" UURL = "https://www.traders.co.jp/margin/transition/transition.asp" # テーブル情報取得 dfs = pd.read_html(common.Chorme_get(UURL), header=0) num = len(dfs)-1 # 最新のみ取得 print(dfs[num]) list_w = ["申込日", "売り株数", "売り前週比", "売り金額", "売り前週比","買い株数", "買い前週比", "買い金額", "買い前週比", "損益率", "信用倍率"] for idx, row in dfs[num].iterrows(): if idx == 3: cnt = 0 print(len(row)) print(len(list_w)) for ii in range(len(row)): rashio[list_w[ii]] = row[cnt] cnt += 2 if list_w[ii] == "信用倍率": break break except: self.send_msg += "traders.co.jp_信用残の推移(週次)エラー発生" + "\n" common.insertDB3(DB_INFO, table_name, rashio)
def traders_web_D(self): # 33333本日の先物取引情報 table_name = "futures_op" UURL = "https://www.traders.co.jp/domestic_stocks/invest_tool/futures/futures_op.asp" # テーブル情報取得 dfs = pd.read_html(common.Chorme_get(UURL), header=1) for ii in range(len(dfs)): # テーブル番号検索 if dfs[ii].columns[0] == "証券会社名": # 証券会社名 num = ii break # Webから取得した情報のカラム名をリスト化 col_list = [i for i in dfs[num].columns] # DB追加のカラム作成 col_tmp = [] H = '' for i in dfs[num].columns: if i.count("Unnamed"): if 'Unnamed: 2' == i: col_tmp.append('日付') H = 'P' else: col_tmp.append('PUT_CALL') H = 'C' else: col_tmp.append(H + i.replace(".1", "")) # カラムのリネームcol_list→col_tmp # col = dict(zip(col_list,col_tmp)) col = {} col = {col_list[i]: col_tmp[i] for i in range(len(col_list))} dfs[num] = dfs[num].rename(columns=col) # DBからカラム情報取得。ない場合は追加する。 set_new = [i for i in dfs[num].columns] res = common.column_check(DB_INFO, table_name, set_new) # DBへのインポート for idx, row in dfs[num].iterrows(): dict_w = {} for ii in range(len(row)): if str(row[ii]) != str(float("nan")): try: dict_w[dfs[num].columns[ii]] = int(row[ii]) except: dict_w[dfs[num].columns[ii]] = row[ii] else: dict_w[dfs[num].columns[ii]] = 0 dict_w['日付'] = common.env_time()[0][:8] common.insertDB3(DB_INFO, table_name, dict_w) # 本日のカラム取得 sqls = "select * from futures_op where 日付 = %(key1)s" % {'key1': common.env_time()[0][:8]} sql_pd = common.select_sql(DB_INFO, sqls) set_new = [i for i in sql_pd.columns if i != 'now' and i != '証券会社名' and i != '日付' and i != 'PUT_CALL'] # 本日のカラムを対象に合計取得 sqls = "select SUM(" + "),SUM(".join(set_new) + ") from futures_op where 日付 = '%(key1)s'" % {'key1': common.env_time()[0][:8]} sql_pd = common.select_sql(DB_INFO, sqls) for i, row in sql_pd.iterrows(): set_val = [] for ii in range(len(row)): if row[ii] is None: set_val.append(0) else: set_val.append(row[ii]) set_val = common.to_int(set_val) col = {} col = {set_new[i]: set_val[i] for i in range(len(set_new))} col['証券会社名'] = '合計' col['日付'] = common.env_time()[0][:8] col = common.to_int(col) common.insertDB3(DB_INFO, table_name, col) print(col) # 2222222本日の先物取引情報 table_name = "futures" UURL = "https://www.traders.co.jp/domestic_stocks/invest_tool/futures/futures_top.asp" dfs = common.read_html2(common.Chorme_get(UURL), 1) # header=0,skiprows=0(省略可能) for ii in range(len(dfs)): if dfs[ii].columns[0] == "SELL": num = ii break # カラムの入れ替え CC = ['証券会社名', 'SELL_225', 'BUY_225', 'NET_225', '日付', 'SELL_TOPIX','BUY_TOPIX', 'NET_TOPIX', '更新日', 'SELL_225M', 'BUY_225M', 'NET_225M'] col_name = {} col_name = {dfs[num].columns[c]: CC[c] for c in range(len(dfs[num].columns))} dfs[num] = dfs[num].rename(columns=col_name) # DBへのインポート for idx, row in dfs[num].iterrows(): dict_w = {} for ii in range(len(row)): dict_w[dfs[num].columns[ii]] = row[ii] dict_w['更新日'] = common.env_time()[1] dict_w['日付'] = common.env_time()[0][:8] common.insertDB3(DB_INFO, table_name, dict_w)