def __init__(self, config={}, fixperiod=''):
"""
"""
# config
self._config = config
self._database = self._config['mysql']['database']
self._table = self._config['mysql']['table']
self._symbols = self._config['symbolpairs']
self._start = self._config['time']['start']
self._end = self._config['time']['end']
self._fixperiod = fixperiod
# 从coinapi获取的str时间是utc格式转为local格式
self._UTCTIMEFORMAT = "%Y-%m-%dT%H:%M:%S.0000000Z" # coinapi的日期格式utc
self._LOCALTIMEFORMAT = "%Y-%m-%d %H:%M:%S" # mysql的日期格式local
# coinapi的数据接口
self._api = CoinAPIv1(self._config['coinapi']['apikey'])
# 'mysql+pymysql://ops:ops!@#[email protected]:3308/david'
self._engine = create_engine('mysql+pymysql://' +
self._config['mysql']['user'] + ':' +
self._config['mysql']['password'] + '@' +
self._config['mysql']['ip'] + ':' +
self._config['mysql']['port'] + '/' +
self._database)
def __init__(self, config, fixperiod):
"""
"""
self._config = config
self._database = self._config['mysql']['database']
self._table = self._config['mysql']['table']
self._symbols = self._config['symbolpairs']
self._start = self._config['time']['start']
self._end = self._config['time']['end']
self._fixperiod = fixperiod
self._UTCTIMEFORMAT = "%Y-%m-%dT%H:%M:%S.0000000Z"
self._LOCALTIMEFORMAT = "%Y-%m-%d %H:%M:%S"
self._api = CoinAPIv1(self._config['coinapi']['apikey'])
self._engine = create_engine('mysql+pymysql://' +
self._config['mysql']['user'] + ':' +
self._config['mysql']['password'] + '@' +
self._config['mysql']['ip'] + ':' +
self._config['mysql']['port'] + '/' +
self._database)
def __init__(self, config, fixperiod):
"""
"""
# config
self._config = config
self._database = self._config['mysql']['database']
self._table = self._config['mysql']['table']
self._symbols = self._config['symbolpairs']
self._start = self._config['time']['start']
self._end = self._config['time']['end']
# 补全的周期
self._fixperiod = fixperiod
# coinapi的日期格式utc
# mysql的日期格式local
self._UTCTIMEFORMAT = "%Y-%m-%dT%H:%M:%S.0000000Z"
self._LOCALTIMEFORMAT = "%Y-%m-%d %H:%M:%S"
# coinapi的数据接口
self._api = CoinAPIv1(self._config['coinapi']['apikey'])
# 'mysql+pymysql://ops:ops!@#[email protected]:3308/david'
self._engine = create_engine('mysql+pymysql://' +
self._config['mysql']['user'] + ':' +
self._config['mysql']['password'] + '@' +
self._config['mysql']['ip'] + ':' +
self._config['mysql']['port'] + '/' +
self._database)
# null
self._nullTs = {
'BINA--EOS--USDT': [],
'BINA--XRP--USDT': [],
'BINA--BTC--USDT': [],
'BINA--ETH--USDT': [],
'BINA--BCC--USDT': [],
'BINA--LTC--USDT': [],
'BINA--EOS--BTC': [],
'BINA--XRP--BTC': [],
'BINA--ETC--BTC': [],
'BINA--ETH--BTC': [],
'BINA--BCC--BTC': [],
'BINA--LTC--BTC': []
}
coinapi提供的是history数据，是utctime，
二者关系utctime+8h=localtime。
--Made by David
"""
from sqlalchemy import create_engine
from coinapi_v1 import CoinAPIv1
import datetime as dt
import pandas as pd
# key1和key2似乎互斥，流量不能叠加，不知道为什么
test_key = 'EA8CF467-3ECD-42D6-A59E-97D908AEB57D' # key1
# test_key = '778C65A3-E842-4C10-B5FF-C1AF0F56E78E' # key2
# test_key = 'F998EBA9-86E5-411A-B434-45D51B6FCBFE' # key3
api = CoinAPIv1(test_key)
# 使用179地址的david库1h表做测试
engine = create_engine('mysql+pymysql://' +
'ops:ops!@#[email protected]:3308/david') # ?charset=utf8&autocommit=true
with engine.connect() as conn:
# 日期格式调整
UTC_FORMAT = "%Y-%m-%dT%H:%M:%S.0000000Z" # coinapi的日期格式
LOCAL_FORMAT = "%Y-%m-%d %H:%M:%S" # live的日期格式
def time_format(inputT):
return dt.datetime.strptime(inputT, UTC_FORMAT).strftime(LOCAL_FORMAT)
# 需要更新的品种，每天分市场补全，对BINA--BTC--USDT，BINANCE_SPOT_BTC_USDT无数据，使用BITFINEX_SPOT_BTC_USD替换
symbols = {
'BINA--EOS--USDT': 'BITFINEX_SPOT_EOS_USD',
def main(argv):
# Parameter setting --------
day_start_str = "20180327"
day_end_str = "20180811"
apikey = "6BEE3909-BA2B-49FA-8751-2A4D14F9FB6F"
# 885A67CA-EA79-409F-B586-C72CAFAB2B7A
#6BEE3909-BA2B-49FA-8751-2A4D14F9FB6F
# apikey = "3DCACFD6-BD97-4FBF-9410-A4F0C6929154" # This is my personal free one (100 rate limit). Please use this first for testing
filepath = "/mnt/c/henryshawn/marketdata/ICO_data/patientory_data"
symbol_id = ""
opts, args = getopt.getopt(
argv, "s:e:k:p:S:",
["start_day=", "end_day=", "api_key=", "path=", "symbol_id="])
for opt, arg in opts:
if opt in ("-s", "--start_day"):
day_start_str = arg
elif opt in ("-e", "--end_day"):
day_end_str = arg
elif opt in ("-k", "--api_key"):
apikey = arg
elif opt in ("-p", "--path"):
filepath = arg
elif opt in ("-S", "--symbol_id"):
symbol_id = arg
# Environment setting --------
day_start = datetime.datetime.strptime(day_start_str, "%Y%m%d")
day_end = datetime.datetime.strptime(day_end_str, "%Y%m%d")
api = CoinAPIv1(apikey)
time.sleep(
0.01
) # use time.sleep to prevent from (104, 'Connection reset by peer')
symbols_raw = api.metadata_list_symbols()
symbols_list = []
for symbol in symbols_raw:
symbols_list.append(symbol["symbol_id"])
# Calculating VWAP
targetdate = day_start
# Target asset information --------
# target_exchange_list = ["BITHUMB", "OKEX","HUOBIPRO", "BINANCE","COINONE","GATEIO"]
# target_instrument_list = ["SPOT"]
# target_asset_list = ["KNC_KRW", "KNC_USDT", "KNC_BTC", "KNC_ETH" ]
target_exchange_list = ["BITTREX", "HITBTC"]
target_instrument_list = ["SPOT"]
target_asset_list = ["PTOY_BTC", "PTOY_ETH", "PTOY_USDT"]
# target_exchange_list = [ "BINANCE"]
# target_instrument_list = ["SPOT"]
# target_asset_list = ["KNC_BTC" ]
# target_asset_list = ["BTC_USD", "ETH_USD", "EOS_USD", "ETC_USD", "BCH_USD", "XRP_USD", "ETH_BTC"]
while True:
if symbol_id == "":
for exchange in target_exchange_list:
for instrument in target_instrument_list:
for asset in target_asset_list:
target_symbol_name = '_'.join(
[exchange, instrument, asset])
if target_symbol_name in symbols_list:
get_volume_and_close(target_symbol_name,
targetdate, api, filepath)
else:
print("\033[91m",
"====Warning====: [",
targetdate.strftime("%Y%m%d"),
"] There is no Symbol ID: ",
target_symbol_name,
".",
"\033[0m",
sep="")
else:
target_symbol_name = symbol_id
if target_symbol_name in symbols_list:
get_volume_and_close(target_symbol_name, day_start, day_end,
api, filepath)
else:
print("\033[91m",
"====Warning====: [",
targetdate.strftime("%Y%m%d"),
"] There is no Symbol ID: ",
target_symbol_name,
".",
"\033[0m",
sep="")
targetdate = datetime.datetime.strptime(
((targetdate + datetime.timedelta(days=100))).strftime("%Y-%m-%d"),
"%Y-%m-%d")
if targetdate > day_end:
break
def update_vic_1mk(self):
"""
"""
# coinapi的数据接口
apikey = self._config['coinapi']['apikey']
api = CoinAPIv1(apikey)
# 'engine': 'mysql+pymysql://ops:ops!@#[email protected]:3308/david',
engine = create_engine('mysql+pymysql://' +
self._config['mysql']['user'] + ':' +
self._config['mysql']['password'] + '@' +
self._config['mysql']['ip'] + ':' +
self._config['mysql']['port'] + '/' +
self._database)
# 从coinapi获取的str时间是utc格式转为local格式
UTCTIMEFORMAT = "%Y-%m-%dT%H:%M:%S.0000000Z" # coinapi的日期格式utc
LOCALTIMEFORMAT = "%Y-%m-%d %H:%M:%S" # mysql的日期格式local
def time_format(utcTimeStr):
return dt.strptime(utcTimeStr,
UTCTIMEFORMAT).strftime(LOCALTIMEFORMAT)
# 从mysql查询最新的日期更新config中的end
query = """
select distinct ts from %s.%s order by ts desc limit 1;
""" % (self._database, self._table)
self._end = engine.execute(query).fetchall()[0]._row[0].strftime(
LOCALTIMEFORMAT)
# 更新
with engine.connect() as conn:
# d:\\1mk.csv
with open('d:\\' + self._fixperiod + '.csv', 'a') as f:
# csv存一份，sql更新一份
s = 'ts,symbol,open,high,low,close,quantity\n'
f.write(s)
# 遍历每一个品种，先查日期是否间断，有则下载数据填充mysql
for key in self._symbols.keys():
# 某key的ts列
query = """
SELECT DISTINCT ts FROM %s.%s
WHERE symbol='%s' AND ts BETWEEN '%s' AND '%s'
ORDER BY ts;
""" % (self._database, self._table, key, self._start,
self._end)
df = pd.read_sql(sql=query, con=conn)
# 选出缺失的ts值
onemk = self._config['timedelta'][self._fixperiod]
timeDelta = pd.Timedelta(onemk)
df['diff'] = df['ts'].diff(1) / timeDelta
missingTs = list(df[df['diff'] > 1].index.values)
for i in missingTs:
# 间断期的起止ts值，去头去尾，中间才是缺少的日期段
start, end = df['ts'].ix[i - 1:i].values
if ((end - start) / timeDelta) > 1:
print('1 symbol:%s\t' % key, 'local time -\t',
str(start)[:19],
str(end)[:19])
start = start + \
timeDelta - \
pd.Timedelta('0 days 08:00:00') # 去头，再减去8小时
end = end - \
timeDelta - \
pd.Timedelta('0 days 08:00:00') # 去尾，再减去8小时
# coinapi参数是19位，'2018-01-01T08:00:00'
apiStart = str(start)[:10] + 'T' + str(start)[-8:]
apiEnd = str(end)[:10] + 'T' + str(end)[-8:]
print('2 symbol:%s\t' % key, ' utc time -\t',
apiStart, apiEnd)
name = self._symbols[key]
try:
data = api.ohlcv_historical_data(
name, {
'period_id':
self._config['coinapi']['period'][
self._fixperiod],
'time_start':
apiStart,
'time_end':
apiEnd
})
if data:
pass
else:
print(data)
except Exception as e:
print(e)
continue
for s in data:
sqlTs = (
dt.strptime(s['time_period_start'],
UTCTIMEFORMAT) +
pd.Timedelta('0 days 08:00:00')).strftime(
LOCALTIMEFORMAT) # 加上8小时
sqlSymbol = key
sqlOpen = s['price_open']
sqlHigh = s['price_high']
sqlLow = s['price_low']
sqlClose = s['price_close']
sqlQuantity = s['volume_traded']
f_data = [
sqlTs, sqlSymbol, sqlOpen, sqlHigh, sqlLow,
sqlClose, sqlQuantity
]
print(f_data)
sql = """
insert into %s.%s(ts,symbol,open,high,low,close,quantity) value('%s','%s',%f,%f,%f,%f,%f)
""" % (self._database, self._table, sqlTs,
sqlSymbol, sqlOpen, sqlHigh, sqlLow,
sqlClose, sqlQuantity)
conn.execute(sql)
s = str(f_data).replace('[',
'').replace(']', '')
s = s.replace("'", '') + '\n'
f.write(s)
def main(argv):
# Parameter setting --------
day_start_str = (datetime.datetime.now().replace(
tzinfo=pytz.timezone("Asia/Taipei")).astimezone(pytz.utc) -
datetime.timedelta(days=1)).strftime("%Y%m%d")
# apikey = "6BEE3909-BA2B-49FA-8751-2A4D14F9FB6F"
apikey = "885A67CA-EA79-409F-B586-C72CAFAB2B7A"
filepath = "/mnt/c/henryshawn/marketdata/reference/"
api = CoinAPIv1(apikey)
opts, args = getopt.getopt(argv, "s:k:p:",
["start_day=", "api_key=", "path="])
for opt, arg in opts:
if opt in ("-s", "--start_day"):
day_start_str = arg
elif opt in ("-k", "--api_key"):
apikey = arg
elif opt in ("-p", "--path"):
filepath = arg
# Read from watch list (watch-list contains the asset pair name of currency pairs that we would like to monitor) (using daily_assets.YYYYMMDD file)
watch_list = []
if os.path.isfile("".join([
"/mnt/c/henryshawn/marketdata/scripts/scripts_data/daily_assets.",
day_start_str
])):
with open("".join([
"/mnt/c/henryshawn/marketdata/scripts/scripts_data/daily_assets.",
day_start_str
]),
newline='') as csvfile:
rows = csv.reader(csvfile)
for row in rows:
watch_list.append((row[0], row[1], row[2], row[3]))
else:
with open("".join([
"/mnt/c/henryshawn/marketdata/scripts/scripts_data/daily_assets.",
"20180809"
]),
newline='') as csvfile:
rows = csv.reader(csvfile)
for row in rows:
watch_list.append((row[0], row[1], row[2], row[3]))
# Asset name-to-key mapping (using the assets.keys file)
asset_name_to_key = {}
with open('/mnt/c/henryshawn/marketdata/keys/assets.keys',
newline='') as csvfile:
rows = csv.DictReader(csvfile, delimiter=',')
for row in rows:
asset_name_to_key[row["asset_name"]] = row["asset_key"]
# Environment setting --------
day_start = datetime.datetime.strptime(day_start_str, "%Y%m%d")
targetdate = day_start
time_start = targetdate + datetime.timedelta(hours=0)
time_end = targetdate + datetime.timedelta(hours=24)
# result
result = {}
symbols = api.metadata_list_symbols()
parameters = {
"period_id": "1DAY",
"time_start": time_start.strftime("%Y-%m-%dT%H:%M:%S"),
"time_end": time_end.strftime("%Y-%m-%dT%H:%M:%S")
}
number_of_request = 0 # DEBUG_MESSAGE
for symbol in symbols:
assetA_name = symbol["asset_id_base"]
assetB_name = symbol["asset_id_quote"]
if (not assetA_name in asset_name_to_key.keys()) or (
not assetB_name in asset_name_to_key.keys()):
break
assetA_id = asset_name_to_key[assetA_name]
assetB_id = asset_name_to_key[assetB_name]
this_key = (assetA_id, assetB_id, assetA_name, assetB_name)
if (this_key in watch_list) and (symbol["symbol_type"] == "SPOT"):
ohlcv_historical = api.ohlcv_historical_data(
symbol["symbol_id"],
parameters) # ==== DATA REQUEST NOLY HAPPENED HERE ====
number_of_request += 1 # DEBUG_MESSAGE
if not ohlcv_historical == []:
if this_key in result.keys():
result[this_key][0] += ohlcv_historical[0]["volume_traded"]
# result[this_key][1] += ohlcv_historical[0]["volume_traded"] * ohlcv_historical[0]["price_close"]
result[this_key][1] += ohlcv_historical[0]["price_close"]
else:
result[this_key] = [
ohlcv_historical[0]["volume_traded"],
ohlcv_historical[0]["volume_traded"] *
ohlcv_historical[0]["price_close"]
]
print("For ",
time_start.strftime("%Y-%m-%d"),
", we request the OHLCV data ",
number_of_request,
" times.",
sep="") # DEBUG_MESSAGE
filepath_save = os.path.join(filepath, targetdate.strftime("%Y%m%d"))
if not os.path.isdir(filepath_save):
os.makedirs(filepath_save)
filename = "closing_prices.csv"
dest = os.path.join(filepath_save, filename)
with open(dest, "w", newline="") as csvfile:
writer = csv.writer(csvfile, delimiter=",")
writer.writerow([
"assetA_id", "assetB_id", "assetA_name", "assetB_name",
"closing_price", "volume"
])
for a_result in result:
assetA_id = a_result[0]
assetB_id = a_result[1]
assetA_name = a_result[2]
assetB_name = a_result[3]
closing_price = result[a_result][1]
volume = result[a_result][0]
writer.writerow([
assetA_id, assetB_id, assetA_name, assetB_name, closing_price
])
def main(argv):
# Parameter setting --------
day_start_str = "20160101"
day_end_str = "20180701"
apikey = "6BEE3909-BA2B-49FA-8751-2A4D14F9FB6F"
filepath = "/marketdata/vwap"
symbol_id = ""
logging.basicConfig(stream=sys.stdout, level=logging.DEBUG)
opts, args = getopt.getopt(
argv, "s:e:k:p:S:",
["start_day=", "end_day=", "api_key=", "path=", "symbol_id="])
for opt, arg in opts:
if opt in ("-s", "--start_day"):
day_start_str = arg
elif opt in ("-e", "--end_day"):
day_end_str = arg
elif opt in ("-k", "--api_key"):
apikey = arg
elif opt in ("-p", "--path"):
filepath = arg
elif opt in ("-S", "--symbol_id"):
symbol_id = arg
# Environment setting --------
day_start = datetime.datetime.strptime(day_start_str, "%Y%m%d")
day_end = datetime.datetime.strptime(day_end_str, "%Y%m%d")
api = CoinAPIv1(apikey)
time.sleep(
0.01
) # use time.sleep to prevent from (104, 'Connection reset by peer')
symbols_raw = api.metadata_list_symbols()
symbols_list = []
for symbol in symbols_raw:
symbols_list.append(symbol["symbol_id"])
# Calculating VWAP
targetdate = day_start
# Target asset information --------
target_exchange_list = [
"BINANCE", "BITTREX", "BITFINEX", "COINBASE", "KUCOIN", "HUOBI", "OKEX"
]
target_instrument_list = ["SPOT"]
target_asset_list = ["BTC_USD", "ETH_USD", "EOS_USD", "ETC_USD"]
# target_asset_list = ["BTC_USD", "ETH_USD", "EOS_USD", "ETC_USD", "BCH_USD", "XRP_USD", "ETH_BTC"]
while True:
if symbol_id == "":
for exchange in target_exchange_list:
for instrument in target_instrument_list:
for asset in target_asset_list:
target_symbol_name = '_'.join(
[exchange, instrument, asset])
if target_symbol_name in symbols_list:
cal_vwap(target_symbol_name, targetdate, api,
filepath)
else:
print(
"\033[91m",
"========Warning========: There is no Symbol ID: ",
target_symbol_name,
".",
"\033[0m",
sep="")
else:
target_symbol_name = symbol_id
if target_symbol_name in symbols_list:
cal_vwap(target_symbol_name, targetdate, api, filepath)
else:
print("\033[91m",
"========Warning========: There is no Symbol ID: ",
target_symbol_name,
".",
"\033[0m",
sep="")
targetdate += datetime.timedelta(days=1)
if targetdate > day_end:
break
def main():
api = CoinAPIv1(getenv('COINAPI_API_KEY'))
coinapi_collect_all_coins(api=api)