def get_open():
"""get open trades from mysql"""
global DATA
local_data = {}
LOGGER.debug("Getting open trades")
dbase = Mysql()
results = dbase.fetch_sql_data("select * from open_trades", header=False)
redis = redis_conn.Redis(interval='4h', test=False)
for entry in results:
pair, open_price, open_time, current_price, perc, interval, name = entry
matching = redis.get_action(pair=pair, interval=interval)[-1]
local_data[pair] = {
"open_price": open_price,
"open_time": open_time,
"matching": "Buy:{},Sell:{}".format(matching["buy"],
matching["sell"]),
"current_price": current_price,
"perc": perc,
"interval": interval,
"graph": get_latest_graph(pair, "html"),
"name": name,
"strategy": get_keys_by_value(config.pairs, pair),
"thumbnail": get_latest_graph(pair, "resized.png")
}
DATA[pair] = local_data[pair]
DATA = local_data
del redis
def setUp(self):
"""
Define static instance variables and create redis/mysql objects as well as working test
directory
"""
self.pairs = [pairs]
self.startdate = startdate
self.sum = xsum
self.max = xmax
self.min = xmin
self.drawup = drawup
self.drawdown = drawdown
os.system("configstore package process_templates {} /etc".format(
config_env))
config.create_config() # reload config
self.days = days
self.outputdir = "/tmp/test_data"
self.intervals = [interval]
self.logger = get_logger(__name__)
self.logger.info("Setting up environment")
self.redis = Redis(interval=self.intervals[0],
test=True,
test_data=True)
self.dbase = Mysql(test=True, interval=self.intervals[0])
if not os.path.exists(self.outputdir):
os.mkdir(self.outputdir)
def main():
"""Main function"""
if len(sys.argv) > 1 and sys.argv[1] == '--help':
usage()
sys.exit(0)
elif len(sys.argv) < 3:
usage()
sys.exit(1)
interval = sys.argv[1]
dbase = Mysql(interval=interval)
data = dbase.fetch_sql_data("select perc, drawdown_perc from profit",
header=False)
dframe = pd.DataFrame.from_records(data, columns=("perc", "drawdown"))
fig = go.Figure(data=[
go.Scatter(x=dframe['drawdown'],
y=dframe['perc'],
name="events",
mode='markers')
])
filename = sys.argv[2]
fig.update_xaxes(title_text='drawdown')
fig.update_yaxes(title_text='perc')
fig.update_layout(title_text="Drawdown Scatter -{}".format(filename))
py.plot(fig, filename=filename, auto_open=False)
def sell():
"""sell a given trade"""
global TEST
print("SELL", file=sys.stderr)
pair = request.args.get('pair')
name = config.main.name
current_price = request.args.get('price')
print(pair, name, file=sys.stderr)
test_trade = bool(os.environ['HOST'] in ["test", "stag"])
trade = Trade(interval='4h', test_data=TEST, test_trade=test_trade)
current_time = strftime("%Y-%m-%d %H:%M:%S", gmtime())
sells = [(pair, current_time, current_price)]
# Sell, then update page
trade.sell(sells, name=name)
sleep(1)
dbase = Mysql(interval='4h', test=test_trade)
dbase.get_active_trades()
del dbase
get_open(SCHED)
get_closed(SCHED)
return redirect("/api", code=302)
def get_open(scheduler):
"""get open trades from mysql"""
global DATA
local_data = {}
print("Getting open trades", file=sys.stderr)
DATA = {}
dbase = Mysql()
interval = '4h'
results = dbase.fetch_sql_data("select * from open_trades", header=False)
redis = redis_conn.Redis(interval=interval, test=False, db=0)
for entry in results:
pair, buy_price, buy_time, current_price, perc, name = entry
matching = redis.get_action(pair=pair, interval=interval)[-1]
print(entry, file=sys.stdout)
local_data[pair] = {"buy_price": buy_price, "buy_time": buy_time,
"matching": "Buy:{},Sell:{}".format(matching["buy"], matching["sell"]),
"current_price": current_price, "perc": perc,
"graph": get_latest_graph(pair, "html"), "name": name,
"strategy": get_keys_by_value(config.pairs, pair),
"thumbnail": get_latest_graph(pair, "resized.png")}
DATA = local_data
del redis
SCHED.enter(60, 60, get_open, (scheduler, ))
def test_get_action(self):
"""
Test get action method
"""
redis = Redis(interval="4h", test_data=True, test=True)
dbase = Mysql(test=True, interval="4h")
redis.clear_all()
dbase.delete_data()
action = redis.get_action('BTCUSDT', '4h')
self.assertEqual(action[0], 'HOLD')
self.assertEqual(action[1], 'Not enough data')
self.assertEqual(action[2], 0)
self.assertEqual(action[4]['buy'], [])
self.assertEqual(action[4]['sell'], [])
redis.clear_all()
dbase.delete_data()
self.insert_data('buy', redis)
action = redis.get_action('BTCUSDT', '4h')
self.assertEqual(action[0], 'OPEN')
self.assertEqual(action[1], 'long_spot_NormalOPEN')
self.assertEqual(action[2], '2019-09-03 19:59:59')
self.assertEqual(action[3], 10647.37)
self.assertEqual(action[4]['buy'], [1])
self.assertEqual(action[4]['sell'], [])
self.insert_data('sell', redis)
action = redis.get_action('BTCUSDT', '4h')
self.assertEqual(action[0], 'NOITEM')
self.assertEqual(action[1], 'long_spot_NOITEM')
self.assertEqual(action[2], '2019-09-06 23:59:59')
self.assertEqual(action[3], 10298.73)
self.assertEqual(action[4]['buy'], [])
# Sell rule matched but no item to sell
self.assertEqual(action[4]['sell'], [1])
dbase.insert_trade("BTCUSDT", "2019-09-06 23:59:59", "10647.37", "333", "0.03130663")
action = redis.get_action('BTCUSDT', '4h')
self.assertEqual(action[0], 'CLOSE')
self.assertEqual(action[1], 'long_spot_NormalCLOSE')
self.assertEqual(action[2], '2019-09-06 23:59:59')
self.assertEqual(action[3], 10298.73)
self.assertEqual(action[4]['buy'], [])
self.assertEqual(action[4]['sell'], [1])
dbase.update_trades("BTCUSDT", "2019-09-07 23:59:59", "10999", "444", "0.0313066")
self.insert_data('random', redis)
action = redis.get_action('BTCUSDT', '4h')
self.assertEqual(action[0], 'NOITEM')
self.assertEqual(action[1], 'long_spot_NOITEM')
self.assertEqual(action[2], '2019-09-16 19:59:59')
self.assertEqual(action[3], 10121.39)
self.assertEqual(action[4]['buy'], [])
self.assertEqual(action[4]['sell'], [1])
def setUp(self):
"""
Define static instance variables and create redis/mysql objects as well as working test
directory
"""
LOGGER.info("Setting up environment")
for container in ['mysql-unit', 'redis-unit']:
command = "docker-compose -f install/docker-compose_unit.yml up -d " + container
time.sleep(6)
self.dbase = Mysql(test=True, interval="1h")
def buy():
"""Buy a given pair"""
global TEST
pair = request.args.get('pair')
LOGGER.info("Buying pair %s" % pair)
trade = Trade(interval='4h', test_data=TEST, test_trade=TEST_TRADE)
current_time = strftime("%Y-%m-%d %H:%M:%S", gmtime())
current_price = get_current_price(pair)
buys = [(pair, current_time, current_price)]
trade.open_trade(buys)
sleep(1)
dbase = Mysql()
dbase.get_active_trades()
del dbase
return redirect("/api", code=302)
def main():
"""
main function
"""
if len(sys.argv) > 1 and sys.argv[1] == '--help':
print("Generate Excel report from database entries")
sys.exit(0)
if len(sys.argv) != 3:
sys.stderr.write("Usage: report <interval> <filename>\n")
sys.exit(1)
interval = sys.argv[1]
filename = sys.argv[2]
workbook = openpyxl.Workbook()
workbook.remove(workbook.get_sheet_by_name('Sheet'))
mysql = Mysql(test=True, interval=interval)
queries = {"weekly": "select perc, pair, week(sell_time) as week from profit",
"monthly": "select perc, pair, month(sell_time) as month from profit",
"average-day": "select pair, hour(timediff(sell_time,buy_time)) as hours from \
trades where sell_time != '0000-00-00 00:00:00' group by pair;",
"perc-month": "select pair, sum(perc) as perc, month(sell_time) as month from \
profit where sell_time != '0000-00-00 00:00:00' group by pair, month \
order by month,perc;",
"profit-pair": "select pair, sum(perc) as perc from profit where \
sell_time != '0000-00-00 00:00:00' group by pair;",
"trades": "select buy_time, sell_time, buy_price, sell_price, base_profit, \
perc, drawdown_perc from profit;",
"hours-pair": "select pair, sum(hour(timediff(sell_time,buy_time))) \
as hours from trades where sell_time \
!= '0000-00-00 00:00:00' group by pair",
"profit-factor": "select IFNULL((select sum(base_profit) from profit where \
base_profit >0)/-(select sum(base_profit) from profit where \
base_profit <0),100) as profit_factor",
"buy-hold-return": "select (select buy_price from profit order by buy_time limit 1) \
as first_buy, (select sell_price from profit order by \
buy_time desc limit 1) as last_sell, (select \
(last_sell-first_buy)/first_buy)*100 as buy_hold"}
for name, query in queries.items():
result = mysql.fetch_sql_data(query)
workbook.create_sheet(title=name)
sheet = workbook.get_sheet_by_name(name)
for row_no, row in enumerate(result):
for col_no, col in enumerate(row):
sheet.cell(row=row_no+1, column=col_no+1).value = col
workbook.save(filename)
def buy():
"""Buy a given pair"""
global TEST
print("BUY", file=sys.stderr)
pair = request.args.get('pair')
test_trade = bool(os.environ['HOST'] in ["test", "stag"])
trade = Trade(interval='4h', test_data=TEST, test_trade=test_trade)
current_time = strftime("%Y-%m-%d %H:%M:%S", gmtime())
current_price = get_current_price(pair)
buys = [(pair, current_time, current_price)]
trade.buy(buys)
sleep(1)
dbase = Mysql()
dbase.get_active_trades()
del dbase
get_open(SCHED)
get_closed(SCHED)
return redirect("/api", code=302)
def setUp(self):
"""
Define static instance variables and create redis/mysql objects as well as working test
directory
"""
self.pairs = [pairs]
self.startdate = startdate
self.sum = xsum
self.max = xmax
self.min = xmin
self.days = 15
self.outputdir = "/tmp/test_data"
self.intervals = ["1h"]
self.logger = get_logger(__name__)
self.logger.info("Setting up environment")
self.redis = Redis(interval=self.intervals[0], test=True, db=0)
self.dbase = Mysql(test=True, interval=self.intervals[0])
if not os.path.exists(self.outputdir):
os.mkdir(self.outputdir)
def sell():
"""sell a given trade"""
global TEST
pair = request.args.get('pair')
LOGGER.info("Selling pair %s" % pair)
current_price = request.args.get('price')
interval = DATA[pair]['interval']
trade = Trade(interval=interval, test_data=TEST, test_trade=TEST_TRADE)
current_time = strftime("%Y-%m-%d %H:%M:%S", gmtime())
sells = [(pair, current_time, current_price)]
# Sell, then update page
trade.close_trade(sells)
sleep(1)
dbase = Mysql(interval='4h', test=TEST_TRADE)
dbase.get_active_trades()
del dbase
return redirect("/api", code=302)
class TestMysql(OrderedTest):
"""Test mysql class methods"""
def setUp(self):
"""
Define static instance variables and create redis/mysql objects as well as working test
directory
"""
LOGGER.info("Setting up environment")
for container in ['mysql-unit', 'redis-unit']:
command = "docker-compose -f install/docker-compose_unit.yml up -d " + container
time.sleep(6)
self.dbase = Mysql(test=True, interval="1h")
def step_1(self):
"""Check insert and update trades"""
self.date = '2018-05-07 22:44:59'
self.sell_date = '2018-05-07 22:44:59'
self.pair = 'XXXYYY'
self.buy_price = 100
self.sell_price = 500
base_in = 20
self.dbase.insert_trade(self.pair, self.date, self.buy_price, base_in,
30)
sql = 'select buy_time, sell_time from trades'
buy_time, sell_time = self.dbase.fetch_sql_data(sql)[-1]
current_time = buy_time.strftime("%Y-%m-%d %H:%M:%S")
assert current_time == self.date
assert sell_time is None
quote = self.dbase.get_quantity(self.pair)
perc_inc = perc_diff(self.buy_price, self.sell_price)
base_out = add_perc(perc_inc, base_in)
self.dbase.update_trades(self.pair,
self.sell_date,
self.sell_price,
quote=quote,
base_out=base_out)
sell_time = self.dbase.fetch_sql_data(
'select sell_time from trades')[-1]
assert sell_time is not None
def tearDown(self):
del self.dbase
def main():
"""
Main function
"""
mysql = Mysql()
if len(sys.argv) > 1 and sys.argv[1] == '--help':
print("Create OHLC graph using daily balance")
sys.exit(0)
# temp table we will use to filter out incomplete balance records
drop = "drop table if exists `temp_table`"
# There needs to be 4 records for each date representing 3 exchanges
clean = (
"create table temp_table as SELECT ctime FROM balance where coin='TOTALS' "
"GROUP BY ctime HAVING count(*) <4")
delete1 = "delete from balance where ctime in (select * from temp_table)"
# Update balance summary and clean out old balance records
update = (
"insert into balance_summary (select ctime, sum(usd) as usd, sum(btc) as btc "
"from balance where coin='TOTALS' and left(ctime,10) != CURDATE() group by ctime)"
)
delete2 = "delete from balance where left(ctime,10) != CURDATE()"
# Generate OHLC data
query = (
"select c1.ctime as closeTime, (SELECT c2.{0} FROM balance_summary c2 WHERE "
"c2.ctime = MIN(c1.ctime) limit 1) AS open, MAX(c1.{0}) AS high, MIN(c1.{0}) AS low, "
"(SELECT c2.{0} FROM balance_summary c2 WHERE c2.ctime = MAX(c1.ctime) limit 1) "
"AS close FROM balance_summary c1 GROUP BY left(ctime,10) "
"ORDER BY c1.ctime ASC")
btc_query = query.format("btc")
usd_query = query.format("usd")
# Run queries
mysql.run_sql_statement(drop)
mysql.run_sql_statement(clean)
mysql.run_sql_statement(delete1)
mysql.run_sql_statement(drop)
mysql.run_sql_statement(update)
usd_results = mysql.fetch_sql_data(usd_query)
btc_results = mysql.fetch_sql_data(btc_query)
mysql.run_sql_statement(delete2)
# Convert results into pandas dataframe using header as column title
usd_dframe = pandas.DataFrame(usd_results, columns=usd_results.pop(0))
usd_dframe['closeTime'] = pandas.to_datetime(usd_dframe['closeTime']) \
.values.astype(numpy.int64) / 10**6
btc_dframe = pandas.DataFrame(btc_results, columns=btc_results.pop(0))
btc_dframe['closeTime'] = pandas.to_datetime(btc_dframe['closeTime']) \
.values.astype(numpy.int64) / 10**6
# Generate graph
graph = Graph(False, pair="Balance-usd", interval='1d', volume=False)
graph.insert_data(usd_dframe)
graph.create_graph('/data/graphs')
graph = Graph(False, pair="Balance-btc", interval='1d', volume=False)
graph.insert_data(btc_dframe)
graph.create_graph('/data/graphs')
class UnitRun(OrderedTest):
"""
Sequential unit tests which download data from binance, run process, and collect results
Items checked:
* pickle file
* mysql/redis connection
* results
"""
def setUp(self):
"""
Define static instance variables and create redis/mysql objects as well as working test
directory
"""
self.pairs = [pairs]
self.startdate = startdate
self.sum = xsum
self.max = xmax
self.min = xmin
self.days = 15
self.outputdir = "/tmp/test_data"
self.intervals = ["1h"]
self.logger = get_logger(__name__)
self.logger.info("Setting up environment")
self.redis = Redis(interval=self.intervals[0], test=True, db=0)
self.dbase = Mysql(test=True, interval=self.intervals[0])
if not os.path.exists(self.outputdir):
os.mkdir(self.outputdir)
def step_1(self):
"""
Step 1 - get test data
"""
self.logger.info("Getting test data")
get_data(self.startdate,
self.intervals,
self.pairs,
self.days,
self.outputdir,
extra=200)
filename = self.outputdir + '/' + self.pairs[
0] + '_' + self.intervals[0] + '.p'
self.logger.info("Filename: %s", filename)
assert os.path.exists(filename) == 1
def step_2(self):
"""
Step 2 - execute test run
"""
self.logger.info("Executing serial test run")
main_indicators = config.main.indicators.split()
serial_test(self.pairs, self.intervals, self.outputdir,
main_indicators)
assert True
def step_3(self):
"""
Step 3 - collect and compare data
"""
self.logger.info("Comparing mysql data")
db_sum = self.dbase.fetch_sql_data("select sum(perc) from profit",
header=False)[0][0]
db_min = self.dbase.fetch_sql_data("select min(perc) from profit",
header=False)[0][0]
db_max = self.dbase.fetch_sql_data("select max(perc) from profit",
header=False)[0][0]
self.logger.info("DB_SUM: %s", db_sum)
self.logger.info("DB_MAX: %s", db_max)
self.logger.info("DB_MIN: %s", db_min)
self.assertGreaterEqual(float(db_sum), self.sum)
self.assertGreaterEqual(float(db_max), self.max)
self.assertGreaterEqual(float(db_min), self.min)
def tearDown(self):
"""Cleanup DB and files"""
self.redis.clear_all()
self.dbase.delete_data()
del self.redis
del self.dbase
shutil.rmtree(self.outputdir)
