def _get_original_market(self, buy_sym_id, sell_sym_id):
if buy_sym_id > sell_sym_id:
temp = buy_sym_id
buy_sym_id = sell_sym_id
sell_sym_id = temp
original_market_result = list(
session.execute(
"""
select original_name from exchange_markets
inner join exchanges e on exchange_markets.exchange_id = e.id
where e.name = :exchange and first_coin_id= :buy_sym_id and second_coin_id= :sell_sym_id
""", {
"exchange": self.exchange,
"buy_sym_id": buy_sym_id,
"sell_sym_id": sell_sym_id
}))
if not len(original_market_result):
self.running = False
logging.info("No market '{}-{}' registered for '{}'".format(
buy_sym_id, sell_sym_id, self.exchange))
return None, None
else:
original_market = re.split(
"[_-]", (original_market_result[0]["original_name"]))
if len(original_market) > 1:
return original_market
elif buy_sym_id + sell_sym_id == original_market[0]:
return buy_sym_id, sell_sym_id
else:
return sell_sym_id, buy_sym_id
def get_exchange_trades(buy_sym_id, sell_sym_id, exchange):
query = ("select exchange_trade_id, name, timestamp, trade_type, buy_sym_id, sell_sym_id, price,"+
" size from trades inner join exchanges on exchanges.id = trades.exchange_id" +
" where name = '" + exchange.lower() +"' and buy_sym_id = '" + buy_sym_id.upper() +
"' and sell_sym_id = '" + sell_sym_id.upper() + "' order by timestamp asc")
print(query)
return session.execute(query)
def _get_all_trades(self, exchange, buy_sym_id, sell_sym_id):
return session.execute(
"""
select timestamp, buy_sym_id, sell_sym_id, price, size from trades join exchanges ex on trades.exchange_id=ex.id \
where buy_sym_id= :buy_sym_id and sell_sym_id= :sell_sym_id and ex.name = :exchange
""", {"exchange": exchange.lower(), "buy_sym_id": buy_sym_id.upper(), "sell_sym_id": sell_sym_id.upper()}
)
def query_orders(buy_sym_id, sell_sym_id):
query = (
"select exchanges.name, orders.exchange_order_id, orders.timestamp, orders.filled_at, orders.expiry,"+
" orders.cancelled_at, orders.buy_sym_id, orders.sell_sym_id, orders.user, orders.price from orders" +
" inner join exchanges on exchanges.id = orders.exchange_id where buy_sym_id = '"+buy_sym_id.upper() +"' and" +
" sell_sym_id = '" +sell_sym_id.upper()+"' order by timestamp asc"
)
return session.execute(query)
def _get_ob_mid_price(self):
""" queries the database for the latest orderbook for the given pair
- for visualisation purposes not all orders are retrieved
- orders are only retrieved if ask/bid prices lie below/above the median price, respectively
"""
query = ("""
with order_book as (
with latest_orders as (
select order_type, price, max(last_update_id) max_update_id, exchange_id
from aggregate_orders ag
group by ag.price, ag.order_type, ag.exchange_id)
select order_type,
price,
buy_sym_id,
size,
last_update_id,
timestamp,
name,
sell_sym_id
from aggregate_orders
inner join exchanges on aggregate_orders.exchange_id = exchanges.id
where (order_type, price, last_update_id, exchange_id) in (select * from latest_orders)
and size > 0
and buy_sym_id = :buy_sym_id
and sell_sym_id = :sell_sym_id
and name = :exchange
order by price asc
),
mid_price as (
with max_min_prices as (
with max_bid_price as (
select max(price) max_bid
from order_book
where (order_book.order_type = 'bid')
),
min_ask_price as (
select min(price) min_ask
from order_book
where (order_book.order_type = 'ask')
)
select *
from max_bid_price,
min_ask_price)
select ((max_min_prices.min_ask + max_min_prices.max_bid) / 2) mid, max_bid, min_ask
from max_min_prices
)
select order_type, price, size, timestamp, max_bid, min_ask, mid
from order_book, mid_price
where (order_book.order_type = 'bid' and order_book.price >= 0.99 * mid_price.mid)
or (order_book.order_type = 'ask' and order_book.price <= 1.01 * mid_price.mid) order by timestamp desc
""")
return session.execute(
query, {
"buy_sym_id": self.buy_sym_id.upper(),
"sell_sym_id": self.sell_sym_id.upper(),
"exchange": self.exchange.lower()
})
def is_original_market(buy_sym_id, sell_sym_id, exchange):
query = (
"select name, first_coin_id, second_coin_id, original_name from exchange_markets inner join exchanges on exchanges.id" +
" = exchange_markets.exchange_id where name = '" + exchange.lower() + "'"
)
all_markets = session.execute(query)
for row in list(all_markets):
if row[1] == buy_sym_id and row[2] == sell_sym_id:
market = parse_market(row[3], buy_sym_id, sell_sym_id)
return market
elif row[1] == sell_sym_id and row[2] == buy_sym_id:
market = parse_market(row[3], sell_sym_id, buy_sym_id)
return market
return None
def _get_ob_quartiles(self):
query = ("""
with order_book as (
with latest_orders as (
select order_type, price, max(last_update_id) max_update_id
from aggregate_orders
group by aggregate_orders.price, aggregate_orders.order_type)
select order_type,
price,
size,
last_update_id,
timestamp,
name,
buy_sym_id,
sell_sym_id
from aggregate_orders
inner join exchanges on aggregate_orders.exchange_id = exchanges.id
where (order_type, price, last_update_id) in (select * from latest_orders)
and size > 0
and buy_sym_id = :buy_sym_id
and sell_sym_id = :sell_sym_id
and name = :exchange
order by price asc
) select * from order_book where (order_book.order_type = 'bid' and order_book.price >= (
select percentile_disc(0.75) within group (order by order_book.price)
from order_book
where order_book.order_type = 'bid'
)
) or (order_book.order_type = 'ask' and order_book.price <= (
select percentile_disc(0.25) within group (order by order_book.price)
from order_book
where order_book.order_type = 'ask'
))
""")
return session.execute(
query, {
"buy_sym_id": self.buy_sym_id.upper(),
"sell_sym_id": self.sell_sym_id.upper(),
"exchange": self.exchange.lower()
})
def _get_all_associated_markets(self, exchange, symbol):
return session.execute("""
select original_name, quoted_volume_id buy_sym_id, first_coin_id, second_coin_id, ex.name from exchange_markets em inner join exchanges ex \
on ex.id = em.exchange_id where first_coin_id = :symbol or second_coin_id = :symbol and ex.name = :exchange
""", {"symbol": symbol, "exchange":exchange})