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)