def update_trade_data(table_name): create_trades_table(table_name) # tradesテーブルからOPENのtrade_idを取得 open_ids = list( pd.read_sql_query( 'select tradeId from ' + table_name + ' ' + 'where state=\'OPEN\'', conn)['tradeId']) # 1件も無ければreturn if len(open_ids) < 1: return # リストの中身を文字列型に変換(joinするため) open_ids = list(map(str, open_ids)) # APIからopen_idのtradeを取得し、DataFrameに追加していく header = table_defs.get_columns('trades') fetched_trades = pd.DataFrame(columns=header) for id in open_ids: try: trade = oanda_api.get_trade(id) except Exception as e: raise RecorderError(e) s = pd.Series(trade) fetched_trades = fetched_trades.append(s, ignore_index=True) # open_idのレコードをtradesテーブルから削除 conn.execute('delete from ' + table_name + ' where tradeId in (' + ','.join(open_ids) + ');') conn.commit() # APIから取得したデータをtradesテーブルに追加 fetched_trades.to_sql(table_name, conn, if_exists="append", index=False)
def update_bollinger(table_name): max_records = 60 df = pd.read_sql_query( 'select * from ' + table_name + ' ' + 'order by datetime desc ' + 'limit ' + str(max_records) + ';', conn).sort_values('datetime') df = price_util.calc_bollinger(df) header = table_defs.get_columns('prices') df.reindex(columns=header) \ .to_sql(table_name, conn, if_exists="replace", index=False)
def update_trade_states(): create_trade_tweet_state_table() table_name = 'trade_tweet_states' header = table_defs.get_columns(table_name) state_records = pd.read_sql_query('select * from ' + table_name + ';', conn).reindex(columns=header) # tradesテーブルにあるデータから、すでにstatesに存在するものを取得 exist_trades = pd.read_sql_query( 'select tradeId, state from trades ' + 'where exists (' + 'select * from ' + table_name + ' as states ' + 'where trades.tradeId = states.trade_id ' + ');', conn) new_trades = pd.read_sql_query( 'select tradeId, openTime, state from trades ' + 'where not exists (' + 'select * from ' + table_name + ' as states ' + 'where trades.tradeId = states.trade_id ' + ');', conn) # statesテーブルから取ったレコードとtradesテーブルから取ったレコードを結合 merge_exist = pd.merge(state_records, exist_trades, left_on='trade_id', right_on='tradeId') # 既にstatesテーブルにあるtrade_stateの値を # tradeテーブルから取得したstateで上書き for i, row in merge_exist.iterrows(): merge_exist.at[i, 'trade_state'] = row['state'] # 結合したtradeの列を削除して代入 state_records = merge_exist.drop(['tradeId', 'state'], axis=1) # statesテーブルにないtradeの行を追加する for i, row in new_trades.iterrows(): new_record = pd.Series() new_record['trade_id'] = row['tradeId'] new_record['open_time'] = row['openTime'] new_record['trade_state'] = row['state'] new_record['tweeted_state'] = '' # 行をappend state_records = state_records.append(new_record, ignore_index=True) # ソートして、DBに書き込み state_records.to_sql(table_name, conn, if_exists='replace', index=False) logger.debug('trade state updated')
def update_price_data(time_unit='M', time_count=5, count=60): table_name = 'prices_{0}{1}'.format(time_unit, time_count) create_prices_table(table_name) granularity = '{0}{1}'.format(time_unit, time_count) params = {'granularity': granularity, 'count': count} # APIから取得してDFに入れる try: candles = pd.DataFrame(oanda_api.get_candles(params=params))\ .sort_values('datetime') except Exception as e: raise RecorderError(e) # DBから最新のレコードを取得 last_record = pd.read_sql_query( 'select * from ' + table_name + ' ' 'order by datetime desc limit 1;', conn) # DBにレコードがある時 if not (last_record.empty): # DBの最新レコードより古いcandleは削除 while not (candles.empty): last_record_datetime = \ datetime.datetime.strptime(last_record.iloc[0]['datetime'], time_format) candle_datetime = \ datetime.datetime.strptime(candles.iloc[0]['datetime'], time_format) if candle_datetime <= last_record_datetime: # 一番最初の行を削除 candles = candles.drop(candles.head(1).index, axis=0) else: break # DBに書き込み header = table_defs.get_columns('prices') candles.reindex(columns=header) \ .to_sql(table_name, conn, if_exists="append", index=False) # macdを計算 update_macd(table_name) # bollinger bandを計算 update_bollinger(table_name)
def trade_tweet(test=False): update_trade_states() table_name = 'trade_tweet_states' header = table_defs.get_columns(table_name) state_records = pd.read_sql_query( 'select * from ' + table_name + ' ' + ' order by open_time;', conn).reindex(columns=header) unsent_records = state_records.query('trade_state != tweeted_state') for i, row in unsent_records.iterrows(): # trade_idが一致するレコードをtradesテーブルから取得 trade = pd.read_sql_query( 'select * from trades where tradeId = {};'.format(row['trade_id']), conn) if len(trade) > 0: # tradeのSeriesを代入 # trade_idが一致するレコードは1行だけのはずなのでdfの1番目を取る trade = trade.iloc[0] else: # trade_idが一致するレコードが無ければcontinue continue # エントリー時のツイートを投稿 if row['trade_state'] == 'OPEN': action = 'entry' feeling = 'neutral' instrument = trade['instrument'].replace('_', '/') start_side = '買い' if int(trade['initialUnits']) > 0 else '売り' start_price = format(float(trade['price']), '.3f') kunits = format(abs(trade['initialUnits']) / 1000, '.1f') emoji_head = tweet_messages.get_emoji('neutral') info = "【エントリー" + emoji_head + "】\n"\ + start_side + " " + instrument + "@" + start_price\ + " ×" + kunits + "kUnits" # tweet message = tweet_messages.get_message(action) kaomoji = tweet_messages.get_kaomoji(feeling) tags = "#USDJPY #FX" content = (message + kaomoji + "\n" + info + "\n" + tags) if test: print(content) else: twitter_api.tweet(content) # tweeted_state更新 state_records.at[i, 'tweeted_state'] = 'OPEN' # イグジット時のツイートを投稿 if row['trade_state'] == 'CLOSED': instrument = trade['instrument'].replace('_', '/') start_side = '買い' if int(trade['initialUnits']) > 0 else '売り' start_price = format(float(trade['price']), '.3f') kunits = format(abs(trade['initialUnits']) / 1000, '.1f') end_side = '買い' if start_side == '売り' else '売り' end_price = format(float(trade['averageClosePrice']), '.3f') pips = format( trade['realizedPL'] / abs(trade['initialUnits']) * 100, '.1f') money = format(trade['realizedPL'], '.1f') plus = "+" if trade['realizedPL'] > 0 else "" action = 'take_profit' if trade['realizedPL'] > 0 else 'losscut' feeling = 'positive' if trade['realizedPL'] > 0 else 'negative' emoji_head = tweet_messages.get_emoji('neutral') info = "【トレード終了" + emoji_head + "】\n"\ + start_side + " " + instrument + "@" + start_price\ + " ×" + kunits + "kUnits\n"\ + end_side + " " + instrument + "@" + end_price\ + " ×" + kunits + "kUnits\n"\ + plus + money + "円(" + plus + pips + " pips)" if trade['realizedPL'] > 0: action = 'take_profit' feeling = 'positive' else: action = 'losscut' feeling = 'negative' # tweet message = tweet_messages.get_message(action) kaomoji = tweet_messages.get_kaomoji(feeling) tags = "#USDJPY #FX" content = (message + kaomoji + "\n" + info + "\n" + tags) if test: print(content) else: twitter_api.tweet(content) # tweeted_state更新 state_records.at[i, 'tweeted_state'] = 'CLOSED' # 高速連投を避けるためのsleep time.sleep(5) # DBに書き込み state_records.to_sql(table_name, conn, if_exists='replace', index=False)