def analyze():
res = db.run(
'select side,count(*),sum(quantity*price) from trades where user_id = ? group by side',
(db.user['id'], )).fetchall()
print(res)
pass
def get_dates(fields='*'):
return {
'yesterday':
db.run(
f"SELECT {fields},max(begin) FROM historical GROUP BY ticker ORDER BY begin DESC"
).fetchall(),
'month':
db.run(
f"SELECT {fields},min(begin) FROM historical WHERE begin > strftime('%Y-%m-%d', 'now', '-1 month') GROUP BY ticker"
).fetchall(),
'year':
db.run(
f"SELECT {fields},min(begin) FROM historical WHERE begin > strftime('%Y-%m-%d', 'now', '-1 year') GROUP BY ticker"
).fetchall(),
'decade':
db.run(
f"SELECT {fields},min(begin) FROM historical WHERE begin > strftime('%Y-%m-%d', 'now', '-10 year') GROUP BY ticker"
).fetchall()
}
def l():
symbolList = []
for k, v in lib.r.hgetall('inst').items():
v = json.loads(v)
trades = db.run('select count(*) from trades where instrument = ?',
(k, )).fetchone()
if trades[0] > 0:
name = v.get('simple_name')
if name is None:
name = v.get('name')
symbolList.append("[{:4}] {:<5} ({})".format(
trades[0], v.get('symbol'), name))
print("\n".join(sorted(symbolList)))
def hello_world():
try:
query = request.args.get('query')
params = request.args.to_dict()
params.pop('query', None)
results = db.run(query, params)
l = []
for result in results:
d = {}
for key, value in result.items():
d[key] = decode(value)
l.append(d)
return jsonify(l)
except Exception as e:
abort(400, str(e))
def launch_run(arguments):
logger.info("run({})".format(arguments))
return run(arguments)
def run():
db.run()
def get_yesterday(fields='*'):
return db.run(
'select {} from historical group by ticker order by begin desc'.format(
fields)).fetchall()
def hist(ticker):
ticker = ticker.lower()
uid = False
symbolList = []
for k, v in lib.r.hgetall('inst').items():
v = json.loads(v)
if v.get('symbol').lower() == ticker:
uid = k
symbolList.append(v.get('symbol'))
if not uid:
print("woooah, wtf is {}".format(ticker))
return
ttl_buy = 0
avg_buy = 0
ttl_sell = 0
shares_sell = 0
rec = {
'buy': {
'first': 0,
'low': float("inf"),
'high': 0
},
'sell': {
'low': float("inf"),
'high': 0
}
}
atmax = None
atmax_avg = 0
atmax_time = 0
net_buy = 0
net_sell = 0
avg_sell = 0
money = 0
shares = 0
week = 24 * 7 * 3600
last_week = 0
max_shares = -float('inf')
min_shares = 0
first = None
trades = list(
db.run(
'select side,price,created,quantity,strftime("%s",created),id as unix from trades where instrument = ? order by created asc',
(uid, )).fetchall())
max_holding = 0
for row in trades:
sign = 1
quant = row[3]
if row[0] == 'dividend':
continue
if row[0] == 'sell':
sign = -1
if not first:
first = row
shares += sign * quant
if shares > max_shares:
atmax = row[5]
max_shares = max(max_shares, shares)
min_shares = min(min_shares, shares)
shares = 0
if min_shares < 0:
min_shares *= -1
max_shares += min_shares
shares += min_shares
if max_shares == 0:
unit = 25.0 / 50
else:
unit = 25.0 / max_shares
rec['buy']['first'] = first[1]
for row in trades:
sign = 1
if row[0] == 'dividend':
continue
side = row[0]
price = row[1]
quant = row[3]
ttl = quant * price
epoch = int(row[4])
wk = ''
if epoch - last_week > week:
last_week = epoch
wk = '-----'
if row[0] == 'sell':
sign = -1
if shares > 0:
avg_buy = ttl_buy / shares
ttl_buy -= (avg_buy * quant)
ttl_sell += ttl
shares_sell += quant
net_sell += ttl
rec['sell']['high'] = max(rec['sell']['high'], price)
rec['sell']['low'] = min(rec['sell']['low'], price)
else:
rec['buy']['high'] = max(rec['buy']['high'], price)
rec['buy']['low'] = min(rec['buy']['low'], price)
net_buy += ttl
ttl_buy += ttl
if shares_sell > 0:
avg_sell = ttl_sell / shares_sell
#ttl_sell -= (price * quant)
else:
avg_sell = 0
shares_sell = 0
ttl_sell = 0
shares += sign * quant
money += sign * ttl
if shares > 0:
avg_buy = ttl_buy / shares
else:
avg_sell = 0
shares_sell = 0
ttl_sell = 0
if shares_sell > 0:
avg_sell = ttl_sell / shares_sell
if sign == 1:
sign = ' '
else:
sign = '-'
if row[5] == atmax:
atmax_avg = avg_buy
atmax_time = epoch
atmax_date = row[2][:10]
rep = ''.join(int(shares * unit) * ['*'])
margin = 0
if avg_buy > 0:
margin = round(100 * avg_sell / avg_buy)
if margin == 0:
margin = ''
else:
margin -= 100
print("{} {:<7g} {:<25} {:<5g} {:<10g} {:4} {:4} {:>3} {:5} {} {} ".
format(sign, round(shares, 3), rep, round(price),
round(shares * row[1]), round(row[1]), round(avg_buy),
round(avg_sell), margin, row[2][:10], wk))
reality = net_sell - net_buy + trades[-1][1] * shares
buy_low_and_sell_high = round(
max_shares * max(rec['buy']['high'], rec['sell']['high']) -
max_shares * min(rec['sell']['low'], rec['buy']['low']))
buy_and_hold = round(max_shares * trades[-1][1] - max_shares * first[1])
hold_at_max = round(max_shares * trades[-1][1] - max_shares * atmax_avg)
duration = (time.time() - int(first[4])) / (365.2475 * 24 * 60 * 60)
atmax_delta = (time.time() - atmax_time) / (365.2475 * 24 * 60 * 60)
roi = {
'blash': 100 * abs(buy_low_and_sell_high / reality - 1),
'ham': 100 * abs(hold_at_max / reality - 1),
'bah': 100 * abs(buy_and_hold / reality - 1)
}
print("\n".join([
"({}) buy: {} | sell {} | diff: {}".format(ticker, round(net_buy),
round(net_sell),
net_sell - net_buy),
"records:", " buy: {:8.2f} - {:8.2f}".format(rec['buy']['low'],
rec['buy']['high']),
" sell: {:8.2f} - {:8.2f}".format(rec['sell']['low'],
rec['sell']['high']), "",
"if you bought max low and sold high:",
"{} ({}% / {}%)".format(buy_low_and_sell_high, round(roi['blash']),
round(roi['blash'] / duration)), "",
"if you held when you had max ({} - {}):".format(
atmax_date, max_shares),
"{} ({}% / {}%)".format(hold_at_max, round(roi['ham']),
round(roi['ham'] / atmax_delta)), "",
"if you bought max at the beginning ({}):".format(first[2][:10]),
"{} ({}% / {}%)".format(buy_and_hold, round(roi['bah']),
round(roi['bah'] / duration)), "",
"your strategy:", "{}".format(round(reality))
]))