def test_multi_chunk(self):
startTime = datetime.utcnow() - timedelta(days=26)
endTime = datetime.utcnow() - timedelta(days=1)
symbol = ['BTCUSDT']
candles = insert_hourly_candles(symbol,
startTime=startTime,
endTime=endTime,
debug=True)
hour_before = startTime - timedelta(hours=1)
hour_after = startTime + timedelta(hours=1)
assert hour_before < DateConvert(
candles.open_date.min()).datetime < hour_after
hour_before = endTime - timedelta(hours=1)
hour_after = endTime + timedelta(hours=1)
assert hour_before < DateConvert(
candles.open_date.max()).datetime < hour_after
dates = list(candles.open_date)
min_date = min(dates)
max_date = max(dates)
complete_range = pd.date_range(min_date, max_date, freq='1H')
complete_range = list(
map(lambda x: DateConvert(x).date, complete_range))
assert np.isin(complete_range, dates).all()
def get_oldest_dates(symbols=None, db='autonotrader'):
"""
Get the earliest candle date for a given symbol or list of symbols in the
database.
Parameters:
-------------
symbol: string
A valid cryptocurreny symbol
Returns:
-------------
most_recent_dates: dict
dict like {'<symbol>':<earliest_date>}
"""
if not symbols:
symbols = get_symbols()
if isinstance(symbols, str):
symbols = [symbols]
oldest_dates = {}
for symbol in symbols:
sql = f"SELECT MIN(open_date) FROM candles WHERE symbol = '{symbol}'"
date = Database(db=db).execute(sql)['MIN(open_date)'].iloc[0]
if date:
date = DateConvert(date).datetime
oldest_dates[symbol] = date
return oldest_dates
def test_no_endTime_and_no_startTime(self):
symbol = 'BTCUSDT'
candles = insert_hourly_candles(symbol, debug=True)
now = datetime.utcnow()
nearest_complete = now.replace(minute=0, second=0, microsecond=0)
hour_ago = now - timedelta(hours=1)
now, hour_ago = DateConvert(now).date, DateConvert(hour_ago).date
assert len(candles) == 1
assert hour_ago < candles.open_date.iloc[0] < now
assert candles.open_date.iloc[0] == DateConvert(nearest_complete).date
#-----------------------------------------------------------------------
symbol = ['BTCUSDT', 'ETHUSDT']
candles = insert_hourly_candles(symbol, debug=True)
assert len(candles) == 2
def insert_historical_candles(symbols, datestring, min_date=None, verbose=True):
"""
Insert <datestring> historical candles for a symbol(s) beyond the oldest
found candle in the database.
Example:
insert_historical_candles('BTCUSDT', '1M') will insert 1 month of
historical data beyond what is found in the database.
Parameters:
---------------
symbols: string | list of strings
Symbols of the cryptos to insert data for. Example: ['BTCUSDT', 'ETHBTC']
datestring: string
String designating how much data to collect. Format: integer followed by
'D', 'M', or 'Y'.
Examples:
'10D' ---> 10 days
'5M' ---> 5 months
'1Y' ---> 1 year
debug: boolean
Setting to true will return the data that would have been inserted into
the database.
"""
if isinstance(symbols, str):
symbols = [symbols]
# Convert datestring to timedelta object
dt = parse_datestring(datestring)
# Oldest dates from the DB as a dict, with None values if nothing's there
oldest_dates = get_oldest_dates(symbols=symbols)
for i, symbol in enumerate(symbols):
endTime = oldest_dates[symbol]
if endTime:
endTime = DateConvert(endTime).datetime
else:
endTime = datetime.utcnow()
startTime = endTime-dt
insert_hourly_candles(
symbol, endTime=endTime, startTime=startTime, verbose=verbose
)
if i != len(symbols)-1:
if verbose:
print('Sleeping...')
sleep(10)
def test_from_date(self):
symbol = get_symbols()[0]
date = get_most_recent_dates(symbols=symbol)[symbol]
from_date = date - timedelta(hours=10)
candles = Candles().get_raw(
symbol = symbol,
from_date = from_date
)
date = DateConvert(date).datetime
most_recent_date = DateConvert(candles.open_date.iloc[0]).datetime
from_date = DateConvert(from_date).datetime
oldest_date = DateConvert(candles.open_date.iloc[-1]).datetime
assert date == most_recent_date
assert from_date == oldest_date
def get_trades(self, symbol=None, from_date=None, to_date=None, type=None):
"""
Get bot trades from the database.
Parameters:
-----------
symbol: string
A valid cryptocurreny symbol.
from_date, to_date: string, format '%Y-%m-%d %H:%M:%S'
Dates for query, resulting in expression:
from_date < date < to_date
type: string; 'buy' | 'sell'
Filter trades by type
Returns
-----------
candles: pd.DataFrame
The results of the composed query.
"""
conditions = []
if from_date:
from_date = DateConvert(from_date).date
conditions.append(
dict(column='date', operator='>', value=from_date))
if to_date:
to_date = DateConvert(to_date).date
conditions.append(dict(column='date', operator='<', value=to_date))
if symbol:
conditions.append(dict(column='symbol', operator='=',
value=symbol))
if type:
conditions.append(dict(column='type', operator='=', value=type))
sql = self._assemble_sql('trades', conditions=conditions)
sql += ' ORDER BY date DESC;'
return Database().execute(sql)
def get_raw(self, symbol=None, from_date=None, to_date=None):
"""
Get raw candles from the database. With no parameters, returns entire
table.
Parameters:
-----------
symbol: string
A valid cryptocurreny symbol.
from_date, to_date: string, format '%Y-%m-%d %H:%M:%S'
Dates for query, resulting in expression:
from_date < open_date < to_date
Returns
-----------
candles: pd.DataFrame
The results of the composed query.
"""
conditions = []
if from_date:
from_date = DateConvert(from_date).date
conditions.append(
dict(column='open_date', operator='>=', value=from_date))
if to_date:
to_date = DateConvert(to_date).date
conditions.append(
dict(column='open_date', operator='<=', value=to_date))
if symbol:
conditions.append(dict(column='symbol', operator='=',
value=symbol))
sql = self._assemble_sql('candles', conditions=conditions)
sql += ' ORDER BY open_date DESC;'
return Database().execute(sql)
def insert_engineered_data(verbose=True):
# Get indicators from subclasses
indicators = list(CustomIndicator.__subclasses__())
# Get columns for comparison to incoming indicators
sql = 'SHOW COLUMNS IN engineered_data;'
candle_cols = list(Database().execute(sql).Field)
# TODO add_column assumes
for indicator in indicators:
if indicator.__name__ not in candle_cols:
db.add_column('engineered_data', indicator.__name__, 'float(20,9)')
# Get starting date for insert
from_date = db.get_max_from_column(table='engineered_data',
column='open_date')
# If there's nothing in the table, populate the entire thing
if not from_date:
from_date = db.get_min_from_column(column='open_date')
ins = engineer_data(from_date=from_date, verbose=verbose)
# Convert to sql-friendly dates
convert_to_sql = lambda x: DateConvert(x).date
ins.open_date = ins.open_date.map(convert_to_sql)
ins.close_date = ins.close_date.map(convert_to_sql)
if verbose:
print('Inserting engineered data into database...')
Database().insert('engineered_data',
ins,
verbose=verbose,
auto_format=True)
def get_min_from_column(table='candles',
column='open_date',
db='autonotrader'):
sql = f'SELECT MIN({column}) FROM {table};'
date = Database(db=db).execute(sql)[f'MIN({column})'].iloc[0]
return DateConvert(date).datetime
def candle(self, symbol, limit = 1, startTime = None, endTime = None):
"""
Get hourly candles for a single symbol from an exchange.
Parameters:
-----------
symbol: string
A valid cryptocurreny symbol for the given exchange.
limit: int; min 1, max 500
The max amount of candles to return.
startTime: datetime object
The start of candle interval
endTime: datetime object
The end of candle interval
Returns:
-----------
data: pandas.DataFrame
Columns:
'symbol', 'open_date', 'open', 'high', 'low', 'close', 'volume',
'close_date', 'quote_asset_volume', 'number_of_trades',
'taker_buy_base_asset_volume', 'taker_buy_quote_asset_volume',
"""
# Parse symbol into from_symbol and to_symbol for API call
try:
from_symbol = self._symbols.loc[symbol].from_symbol
to_symbol = self._symbols.loc[symbol].to_symbol
except KeyError as e:
print(f'''
{symbol} is not a valid symbol for {self.exchange} within the
Cryptocompare system.
Options:
{sorted(list(self._symbols.symbol))}
''')
raise e
# Parse startTime and endTime
if startTime:
if endTime:
endTime = DateConvert(endTime).datetime
else:
endTime = datetime.utcnow()
if endTime.minute:
endTime -= timedelta(hours=1)
endTime = endTime.replace(minute=0, second=0, microsecond=0)
startTime = DateConvert(startTime).datetime
date_range = pd.date_range(startTime, endTime, freq='1H')
limit = len(date_range)
elif not endTime:
endTime = datetime.utcnow()
endTime = DateConvert(endTime).timestamp
# URL parameters
params = {'fsym':from_symbol, 'tsym':to_symbol, 'e':self.exchange,
'toTs':endTime, 'limit':limit}
# Build URL
url = 'https://min-api.cryptocompare.com/data/histohour?'
count = 0
for param, value in params.items():
if value:
sym = '&' if count > 0 else ''
url += f'{sym}{param}={value}'
count+=1
# Get Response from GET call
data = decode_api_response(url)[1]
data = pd.DataFrame(data['Data'])
# Convert from timestamp to datetime, set as index
data['open_date'] = data.time.map(lambda x: DateConvert(x).date)
data['symbol'] = symbol
data = data.drop('time', axis=1)
# TODO Figure out volume config
reorder = [
'symbol', 'open_date', 'open', 'high', 'low', 'close', 'volume',
'close_date', 'quote_asset_volume', 'number_of_trades',
'taker_buy_base_asset_volume', 'taker_buy_quote_asset_volume',
]
print(limit)
if limit == 1:
return data.iloc[-1].T
else:
return data