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