def read_data(market='KrakenRest',
starting_timestamp=1567267200,
duration=86400,
forcasting_period=300,
sample_number=1000):
pgmanager = DB.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
ending_timestamp = starting_timestamp + duration
# 取出depths的数据
sql = 'select * from depths where market=\'' + market + '\' and timestamp between ' + str(
starting_timestamp) + ' and ' + str(
ending_timestamp) + ' order by timestamp'
rows = pgmanager.select(sql)
depths = []
askss = []
bidss = []
supports = []
resistances = []
# 取出trades的数据
ending_timestamp = ending_timestamp + forcasting_period
sql2 = 'select * from trades where market=\'Kraken\' and timestamp between ' + str(
starting_timestamp) + ' and ' + str(
ending_timestamp) + ' order by timestamp'
rows2 = pgmanager.select(sql2)
# 对逐笔数据进行处理
for row in rows:
timestamp = row[2]
support = 99999999
resistance = -99999999
# iterate over rows2 to determine the high and low
for cnt in range(0, len(rows2)):
row2 = rows2[cnt]
timestamp_for_rows2 = row2[3]
if timestamp_for_rows2 > timestamp and timestamp_for_rows2 <= timestamp + forcasting_period:
price = row2[5]
if price < support:
support = price
if price > resistance:
resistance = price
if timestamp_for_rows2 > timestamp + forcasting_period:
break
d = json.loads(row[3])
asks = d['asks']
bids = d['bids']
askss.append(asks)
bidss.append(bids)
supports.append(support)
resistances.append(resistance)
askss = np.array(askss)
bidss = np.array(bidss)
resistances = np.array(resistances)
supports = np.array(supports)
return {
'bidss': bidss,
'askss': askss,
'supports': supports,
'resistances': resistances
}
def read_data_from_db(conn):
'''
从数据库读文件，并返回一个ndarray
:param conn:
:return:
'''
from packages import db
import CONSTANTS
myPGManager = db.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
def load_data_to_memory_database(starting_timestamp, ending_timestamp,
forcasting_period):
global conn, cur
pgmanager = DB.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
# 记录时间
t0 = time.time()
sql = 'select * from depths where timestamp between ' + str(
starting_timestamp) + ' and ' + str(
ending_timestamp) + ' order by timestamp'
rows_for_depths = pgmanager.select(sql)
sql = 'select * from trades where timestamp between ' + str(
starting_timestamp) + ' and ' + str(
ending_timestamp + forcasting_period) + ' order by timestamp'
rows_for_trades = pgmanager.select(sql)
print(time.time() - t0)
t1 = time.time()
# 创建表
sql = 'CREATE TABLE processed_depths (id integer primary key ,market varchar(64),timestamp integer, depth text )'
cur.execute(sql)
conn.commit()
sql = 'CREATE TABLE processed_trades (id integer primary key ,market varchar(64),tid varchar(64),timestamp integer, trade_type integer ,price numeric , amount numeric , status integer , order_type varchar(4))'
cur.execute(sql)
conn.commit()
# 将psql的行写入内存数据库
sql = "insert into processed_depths values(?,?,?,?)"
cur.executemany(sql, rows_for_depths)
conn.commit()
sql = 'insert into processed_trades values(?,?,?,?,?,?,?,?,?)'
cur.executemany(sql, rows_for_trades)
conn.commit()
print(time.time() - t1)
# 测试查询
sql = 'select min(price) from processed_trades where timestamp>1567267200 and timestamp<=1567267500'
cur.execute(sql)
rows = cur.fetchall()
# 创建索引
sql = 'CREATE INDEX index_timestamp_for_depths ON processed_depths (timestamp);'
cur.execute(sql)
conn.commit()
sql = 'CREATE INDEX index_timestamp_for_trades ON processed_trades (timestamp);'
cur.execute(sql)
conn.commit()
def to_db(klines, tablename, normalize=False):
pgmanager = DB.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
sqls = []
if normalize:
pass
if klines.klines[0].__class__ == UNIVERSAL.Kline:
for kline in klines.klines:
param = {
'timestamp': kline.timestamp,
'open': kline.open,
'high': kline.high,
'low': kline.low,
'close': kline.close,
'volume': kline.vol
}
sqls.append(param)
pgmanager.execute_many(
'insert into ' + tablename +
'(timestamp,o,h,l,c,vol) values(%(timestamp)s,%(open)s,%(high)s,%(low)s,%(close)s,%(volume)s)',
sqls)
if klines.klines[0].__class__ == UNIVERSAL.KlineWithVol:
for kline in klines.klines:
param = {
'timestamp': kline.timestamp,
'open': kline.open,
'high': kline.high,
'low': kline.low,
'close': kline.close,
'volume': kline.vol,
'vol_buy': kline.vol_bid,
'vol_sell': kline.vol_ask,
'avg_buy': kline.avg_buy,
'avg_sell': kline.avg_sell,
'avg_amount': kline.avg_amount_per_trade
}
sqls.append(param)
pgmanager.execute_many(
'insert into ' + tablename +
'(timestamp,o,h,l,c,vol,vol_buy,vol_sell,avg_buy,avg_sell,avg_amount_per_trade) values'
'(%(timestamp)s,%(open)s,%(high)s,%(low)s,'
'%(close)s,%(volume)s,%(vol_buy)s,%(vol_sell)s,'
'%(avg_buy)s,%(avg_sell)s,%(avg_amount)s)', sqls)
1. 按照什么生成k线序列？
1.1 按照等时间间隔，例如1分钟生成一根k线
1.2 按照成交量，例如每100btc成交量生成一根k线，或者每100000美元生成一根k线
1.3 按照笔数（存疑），例如每1000笔交易生成一根k线
1.4 按照波动区间生成k线
2.
'''
from packages import universal as UNIVERSAL
from packages import currency_pair as CP
from packages import data as DATA
from packages import db as DB
import CONSTANTS
tablename = 'klines_full_time_86400'
mypgmanager = DB.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
rows = mypgmanager.select('select * from ' + tablename + ' order by timestamp')
len_rows = len(rows)
sqls = []
for cnt1 in range(50, len_rows):
if cnt1 % 10000 == 0:
print("已完成", cnt1 / len_rows * 100, "%")
row = rows[cnt1]
timestamp = row[1]
open = row[2]
high = row[3]
low = row[4]
close = row[5]
vol = row[6]
vol_buy = row[7]
from packages import bittrex as BTT
from packages import coinbase as CB
from packages import digifinex as DF
from packages import gate as G
from packages import huobi as HB
from packages import itbit as IB
from packages import kraken_rest1 as KK
from packages import kucoin as KC
from packages import liquid as LQ
from packages import okex as OK
from packages import poloniex as PL
from packages import zb as ZB
pgmanager = DB.PGManager(database='quantum',
user='******',
pw='Caichong416',
host='localhost',
port='5432')
virtual_account = ACCOUNT.Account('', '')
binance = BN.Binance(virtual_account)
bitfinex = BF.Bitfinex(virtual_account)
bitso = BS.Bitso(virtual_account)
bitstamp = BST.Bitstamp(virtual_account)
bittrex = BTT.Bittrex(virtual_account)
coinbase = CB.Coinbase(virtual_account)
digifinex = DF.DigiFinex(virtual_account)
gate = G.Gateio(virtual_account)
huobi = HB.Huobi(virtual_account)
itbit = IB.Itbit(virtual_account)
kraken = KK.KrakenRest(virtual_account)
def from_db(
query='select * from trades_for_kraken order by timestamp desc'):
pgmanager = DB.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
trades = pgmanager.select(query)
return trades
def read_data(markets=[],
starting_timestamp=1567267200,
duration=86400,
forcasting_period=2400,
forcasting_amount=100,
exchange_rates={},
sample_number=1000):
'''
这个模块实现的功能是：
在指定的时间内取出对应的深度列表、支撑和阻力位
:param markets:
:param starting_timestamp:
:param duration:
:param forcasting_period:
:param forcasting_amount:
:param exchange_rates:
:param sample_number:
:return:
'''
global conn, cur
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
# 猜测的开始时间戳和结束时间戳
ending_timestamp = starting_timestamp + duration
# 读汇率
load_exchange_rates()
# 要返回的值
depths = {}
supports = []
resistances = []
pgmanager = DB.PGManager(**CONSTANTS.DB_CONNECT_ARGS_LOCAL)
# 0. 将数据读入到内存数据库中
load_data_to_memory_database(starting_timestamp, ending_timestamp,
forcasting_period)
# 1. 取出所有的交易市场，并将其映射到depths的键集合上
sql = 'select distinct(market) from processed_depths'
rows_for_all_markets = cur.execute(sql)
for row in rows_for_all_markets:
depths[row[0]] = []
# 2. 先取出timestamp序列存入timestamps列表中
cur.execute(
'select distinct(timestamp) from processed_depths order by timestamp')
rows_for_timestamps = cur.fetchall()
starting_timestamp = rows_for_timestamps[0][0]
ending_timestamp = rows_for_timestamps[-1][0]
# 3. 取出trades数据
ending_timestamp2 = ending_timestamp + forcasting_period
sql2 = 'select * from trades where market=\'Kraken\' and timestamp between ' + str(
starting_timestamp) + ' and ' + str(
ending_timestamp2) + ' order by timestamp'
rows_for_trades = pgmanager.select(sql2)
# # 3.1 取出exchange rates数据
# sql1='select * from processed_exchange_rates where currency_pair=\'USDJPY\' and timestamp>='+str(starting_timestamp)+' and timestamp<='+str(ending_timestamp)+' order by timestamp'
# sql2='select * from processed_exchange_rates where currency_pair=\'USDMXN\' and timestamp>='+str(starting_timestamp)+' and timestamp<='+str(ending_timestamp)+' order by timestamp'
# cur.execute(sql1)
# rows_for_rates_of_USDJPY=cur.fetchall()
# cur.execute(sql2)
# rows_for_rates_of_USDUSDMXN=cur.fetchall()
# 4. 遍历timestamps列表中的所有timestamp，并分析processed_depths表和trades表中的数据
# 此处需要修改current_timestamp为即将被迭代的那个timestamp
t1 = time.time()
for row in rows_for_timestamps:
current_timestamp = row[0]
# 取出当前时间戳的depth数据
sql = 'select * from processed_depths where timestamp=' + str(
current_timestamp)
cur.execute(sql)
rows_for_current_timestamp = cur.fetchall()
# 一共大约16条数据，隶属于16个不同的交易市场，对每个单独的条目进行标准化，然后写入到一个列表中
# 将此次查询的数据从list变为dict形式，方便后续索引
temp_rows_for_current_timestamp = {}
for row in rows_for_current_timestamp:
temp_rows_for_current_timestamp[row[1]] = json.loads(row[3])
# 将此次查询的大约16条数据的depths数据中的bids和asks分别附加到depths的bids和asks中，注意Okex的asks要做一次reverse
# 同时要注意异常时间戳，有时无法得到16家交易所的完整数据，此时，用一个默认的数据代替缺失数据
# 默认的填充交易所：['Binance','Bittrex','Bitstamp','KrakenRest']
default_markets = [
'Coinbase', 'Binance', 'Bittrex', 'Bitstamp', 'KrakenRest',
'Poloniex', 'Bitfinex'
]
for market in default_markets:
if temp_rows_for_current_timestamp.__contains__(market):
asks = temp_rows_for_current_timestamp[market]['asks']
bids = temp_rows_for_current_timestamp[market]['bids']
break
default_depth = {'asks': asks, 'bids': bids}
# 将抽离出来的bids和asks数据写入到对应的键值对的bids、asks的序列中
for key in depths.keys():
if temp_rows_for_current_timestamp.__contains__(key):
depth = temp_rows_for_current_timestamp[key]
if key == 'Okex':
depth['asks'].reverse()
if key == 'Bitstamp':
depth['asks'] = depth['asks'][:200]
depth['bids'] = depth['bids'][:200]
if key == 'Liquid':
# 因为liquid交易所只有BTC-JPY交易的，所以要对JPY定价的BYC进行换权处理，变成USD定价
# 而又因为每时每刻的JPY-USD定价都在变动，所以选取MT4上面的历史数据作为JPY-USD定价标准
sql = 'select rate from processed_exchange_rates where currency_pair=\'USDJPY\' and timestamp=(select max(timestamp) from processed_exchange_rates where timestamp<%s)' % (
current_timestamp)
cur.execute(sql)
rows = cur.fetchall()
rate = rows[0][0]
for bid in depth['bids']:
bid[0] = bid[0] / rate
for ask in depth['asks']:
ask[0] = ask[0] / rate
a = 1
if key == 'Bitso':
# 同上面的，这次先取得墨西哥比索-美元的定价，在进行换权处理
sql = 'select rate from processed_exchange_rates where currency_pair=\'USDMXN\' and timestamp=(select max(timestamp) from processed_exchange_rates where timestamp<%s)' % (
current_timestamp)
cur.execute(sql)
rows = cur.fetchall()
rate = rows[0][0]
for bid in depth['bids']:
bid[0] = bid[0] / rate
for ask in depth['asks']:
ask[0] = ask[0] / rate
a = 1
else:
depth = default_depth
depths[key].append(depth)
# 计算trades中未来n秒所触及到的高点和低点
# 取出阻力点位
sql = 'select max(price) from processed_trades where timestamp>' + str(
current_timestamp) + ' and timestamp<=' + str(current_timestamp +
forcasting_period)
cur.execute(sql)
rows = cur.fetchall()
resistance = rows[0][0]
# 取出支撑点位
sql = 'select min(price) from processed_trades where timestamp>' + str(
current_timestamp) + ' and timestamp<=' + str(current_timestamp +
forcasting_period)
cur.execute(sql)
rows = cur.fetchall()
support = rows[0][0]
supports.append(support)
resistances.append(resistance)
print('一共用了', time.time() - t1, '秒，完成了', len(rows_for_timestamps), '次大迭代')
conn.close()
return {'depths': depths, 'supports': supports, 'resistances': resistances}
