def initialize():
sql = "select * from ticker"
ticker = repository.read_sql(database=database, sql=sql)
if ticker.empty:
message.info("initialize ticker")
sql = "insert into ticker values (now(),0,0)"
repository.execute(database=database, sql=sql, write=False)
def get_historical_price(self, limit):
sql = """
select
cast(Time as datetime) as Date,
Open,
High,
Low,
Close
from
(
select
date_format(
cast(op.date as datetime),
'%Y-%m-%d %H:%i:00'
) as Time,
op.price as Open,
ba.High as High,
ba.Low as Low,
cl.price as Close
from
(
select
max(price) as High,
min(price) as Low,
min(id) as open_id,
max(id) as close_id
from
execution_history
group by
year(date),
month(date),
day(date),
hour(date),
minute(date)
order by
max(date) desc
limit
{limit}
) ba
inner join execution_history op on op.id = ba.open_id
inner join execution_history cl on cl.id = ba.close_id
) as ohlc
order by
Time
""".format(limit=limit)
historical_price = repository.read_sql(database=self.DATABASE, sql=sql)
first_Date = historical_price.loc[0]["Date"]
sql = "delete from execution_history where date < '{first_Date}'"\
.format(first_Date=first_Date)
repository.execute(database=self.DATABASE, sql=sql, write=False)
return historical_price
def on_message(ws, message):
messages = json.loads(message)
# auth レスポンスの処理
if 'id' in messages and messages['id'] == self._JSONRPC_ID_AUTH:
if 'error' in messages:
print('auth error: {}'.format(messages["error"]))
elif 'result' in messages and messages['result']:
params = [{
'method': 'subscribe',
'params': {
'channel': c
}
} for c in self._private_channels]
ws.send(json.dumps(params))
if 'method' not in messages or messages[
'method'] != 'channelMessage':
return
params = messages["params"]
channel = params["channel"]
recept_data = params["message"]
if channel == "lightning_executions_FX_BTC_JPY":
for r in recept_data:
date = r["exec_date"][:26]
date = date.replace("T", " ").replace("Z", "")
date = \
dtdt.strptime(date, '%Y-%m-%d %H:%M:%S.%f')
date = date + dt.timedelta(hours=9)
side = r["side"]
price = r["price"]
size = str(r["size"])
sql = "insert into execution_history values (null,'{date}','{side}',{price},'{size}')"\
.format(date=date, side=side, price=price, size=size)
repository.execute(database=database, sql=sql, log=False)
if channel == "lightning_ticker_FX_BTC_JPY":
date = recept_data["timestamp"][:26]
date = date.replace("T", " ").replace("Z", "")
date = \
dtdt.strptime(date, '%Y-%m-%d %H:%M:%S.%f')
date = date + dt.timedelta(hours=9)
best_bid = recept_data["best_bid"]
best_ask = recept_data["best_ask"]
sql = "update ticker set date='{date}',best_bid={best_bid},best_ask={best_ask}"\
.format(date=date, best_bid=best_bid, best_ask=best_ask)
repository.execute(database=database, sql=sql, log=False)
def save_entry(side):
message.info(side, "entry")
sql = "update entry set side='{side}'".format(side=side)
repository.execute(database=DATABASE, sql=sql, write=False)
def save_entry(date, side, price):
sql = "insert into backtest_entry values('{date}','{side}',{price},0)" \
.format(date=date, side=side, price=price)
repository.execute(database=database, sql=sql, log=False)
repository.execute(database=database, sql=sql, log=False)
volume_ma = 10
diff_ratio = 2
back_min = 5
print("----------------------------------------------")
print("volume_ma", volume_ma, "diff_ratio", diff_ratio, "back_min", back_min)
asset = 1000000
database = "tradingbot"
sql = "truncate backtest_entry"
repository.execute(database=database, sql=sql, log=False)
pd_op.display_max_columns()
pd_op.display_round_down()
sql = """
select
b2.Date as date,
b3.Close as fr_Price,
b2.Close as to_Price,
b1.Volume as v1,
b2.Volume as v2,
b1.Date as bb1,
b2.Date as bb2,
b3.Date as bb3
from
def insert_data():
message.info("initial entry")
sql = "insert into entry values('CLOSE')"
repository.execute(database=DATABASE, sql=sql, write=False)
def truncate_table():
sql = "truncate entry"
repository.execute(database=DATABASE, sql=sql)
print("CHANNEL_WIDTH", CHANNEL_WIDTH)
DATABASE = "tradingbot"
sql = """
select
*
from
bitflyer_btc_ohlc_1M
order by
Date
"""
bo = repository.read_sql(database=DATABASE, sql=sql)
sql = "truncate backtest_entry"
repository.execute(database=DATABASE, sql=sql, log=False)
backtest_no = 1
has_buy = False
has_sell = False
while True:
hp = get_historical_price()
if hp is None:
continue
channel = hp[:-1]
high_line = channel["High"].max()
low_line = channel["Low"].min()
i = len(hp) - 1
latest = hp.iloc[i]
