class DatabasePopulator(ABC):
"""DatabasePopulator connects from one data source (typically an external one via a DatabaseSource eg. DatabaseNCFX)
downloads historical data from that and then dumps it locally
"""
def __init__(self,
temp_data_folder=constants.temp_data_folder,
temp_large_data_folder=constants.temp_large_data_folder,
tickers=None,
data_store=None):
self.temp_data_folder = temp_data_folder
self.temp_large_data_folder = temp_large_data_folder
self.tickers = None
self.util_func = UtilFunc()
self.time_series_ops = TimeSeriesOps()
self.data_store = data_store
logger = LoggerManager().getLogger(__name__)
if not (os.path.isdir(self.temp_data_folder)):
logger.warn("Temp data folder " + self.temp_data_folder +
" does not exist")
if not (os.path.isdir(self.temp_large_data_folder)):
logger.warn("Temp large data folder " + self.temp_data_folder +
" does not exist")
if tickers is not None:
self.tickers = tickers
@abc.abstractmethod
def _fetch_market_data(self,
start,
finish,
ticker,
web_proxies=constants.web_proxies):
"""Fetches market data in a single download for a ticker. We need to be careful not to specify chunks which are
too large, as many external sources will have a limit on how much data we can download in one chunk.
Parameters
----------
start : datetime
Start date/time of the download
finish : datetime
Finish date/time of the download
ticker : str
Ticker to be downloaded
web_proxies : dict
Addresses for web proxies
Returns
-------
"""
pass
def _get_postfix(self):
"""The postfix which represents this data source, eg. 'ncfx' for New Change FX or 'dukascopy' for Dukascopy
Returns
-------
str
"""
pass
@abc.abstractmethod
def _get_output_data_source(self):
"""Gets the DatabaseSource object which represents how we wish to store the market data internally
Returns
-------
DatabaseSource
"""
return
def _remove_weekend_points(self):
return True
@abc.abstractmethod
def _get_input_data_source(self):
"""Gets the DatabaseSource object which represents how we input the market data (typically, this will be from
an external data source)
Returns
-------
DatabaseSource
"""
return
@abc.abstractmethod
def _get_tickers(self):
"""List of tickers that can accessedd from the external/input DatabaseSource
Returns
-------
str (list)
"""
return
@abc.abstractmethod
def _get_threads(self, start_data_hist, finish_date_hist):
"""How many threads to use when downloading from our external/input DatabaseSource
Returns
-------
int
"""
return
def download_to_csv(self,
start_date,
finish_date,
tickers,
remove_duplicates=True,
split_size='monthly',
chunk_int_min=None,
include_partial_periods=False,
write_temp_to_disk=True,
write_large_csv=True,
write_large_hdf5_parquet=True,
csv_folder=constants.csv_folder,
csv_compression=None,
return_df=False,
web_proxies=constants.web_proxies):
start_date = self.time_series_ops.date_parse(start_date)
finish_date = self.time_series_ops.date_parse(finish_date)
dates = self.util_func.split_date_single_list(
start_date,
finish_date,
split_size=split_size,
add_partial_period_start_finish_dates=include_partial_periods)
df_dict = {}
msg = []
for i in range(0, len(dates) - 1):
msg_list, df_dict_list = self.download_from_external_source(
start_date=dates[i],
finish_date=dates[i + 1],
tickers=tickers,
chunk_int_min=chunk_int_min,
append_data=False,
remove_duplicates=remove_duplicates,
write_temp_to_disk=write_temp_to_disk,
write_to_disk_db=False,
write_large_csv=write_large_csv,
write_large_hdf5_parquet=write_large_hdf5_parquet,
csv_folder=csv_folder,
csv_compression=csv_compression,
return_df=return_df,
web_proxies=web_proxies)
if msg_list != []:
msg.append(msg_list)
if return_df:
for k in df_dict_list.keys():
if k in df_dict.keys():
df_dict[k] = df_dict[k].append(df_dict_list[k])
else:
df_dict[k] = df_dict_list[k]
return self.util_func.flatten_list_of_lists(msg), df_dict
def download_from_external_source(self,
append_data=True,
remove_duplicates=True,
if_exists_table='append',
if_exists_ticker='append',
number_of_days=30 * 7,
chunk_int_min=None,
start_date=None,
finish_date=None,
delete_cached_files=False,
tickers=None,
write_temp_to_disk=True,
write_to_disk_db=True,
read_cached_from_disk=True,
write_large_csv=False,
write_large_hdf5_parquet=True,
csv_folder=constants.csv_folder,
csv_compression=None,
return_df=False,
web_proxies=constants.web_proxies):
"""Downloads market data from an external source and then dumps to HDF5/Parquet files for temporary storage which is cached.
If HDF5/Parquet cached files already exist for a time segment we read them in, saving us to make an external data call.
Lastly, dumps it to an internal database.
Parameters
----------
append_data : bool
True - only start collecting later data not already in database (ignoring number_of_days parameter)
False - start collecting all data, ignoring anything stored in database
remove_duplicates : bool
True (default) - remove values which are repeated
False - leave in repeated values
if_exists_table : str
'append' - if database table already exists append data to it
'replace' - remove existing database table
if_exists_ticker : str
'append' - if ticker already exists in the database, append to it
'replace' - replace any data for this ticker
number_of_days : int
Number of days to download data for
chunk_int_min : int (None)
Size of each download (default - specified in constants)
Returns
-------
"""
# Swim()
logger = LoggerManager.getLogger(__name__)
if write_to_disk_db:
data_source_local = self._get_output_data_source()
if write_large_csv:
if not (os.path.isdir(csv_folder)):
logger.warn("CSV folder " + self.temp_data_folder +
" where we are about to write does not exist")
# What chunk size in minutes do we want for this data provider?
if chunk_int_min is None:
chunk_int_min = self._get_download_chunk_min_size()
if chunk_int_min is None:
chunk_size_str = None
else:
chunk_size_str = str(chunk_int_min) + "min"
if tickers is None:
tickers = self._get_tickers()
if isinstance(tickers, str):
tickers = [tickers]
# If there's no start or finish date, choose a default start finish data
if start_date is None and finish_date is None:
finish_date = datetime.datetime.utcnow()
finish_date = datetime.datetime(finish_date.year,
finish_date.month, finish_date.day,
0, 0, 0, 0)
start_date = finish_date - timedelta(days=number_of_days) # 30*7
else:
start_date = self.time_series_ops.date_parse(start_date)
finish_date = self.time_series_ops.date_parse(finish_date)
if finish_date < start_date:
logger.error("Download finish date is before start data!")
return
now = pd.Timestamp(datetime.datetime.utcnow(), tz='utc')
# Do not allow downloading of future data!
if finish_date > now:
finish_date = now
df_dict = {}
# Loop through each ticker
for ticker in tickers:
has_old = False
if delete_cached_files and write_to_disk_db:
logger.info("Deleting all cached temp files for " + ticker)
for name in glob.glob(self.temp_data_folder + '/*' + ticker +
"*"):
try:
os.remove(name)
except:
logger.warn("Couldn't delete file " + name)
logger.info("Finished deleting cached files for " + ticker)
# If we have been asked to append data, load up what you can from the internal database
# find the last point
if append_data and if_exists_ticker == 'append' and write_to_disk_db:
logger.info("Trying to download old data first for " + ticker)
try:
df_old = data_source_local.fetch_market_data(
start_date,
finish_date,
ticker,
web_proxies=web_proxies)
# This will vary between tickers (in particular if we happen to add a new ticker)
start_date = df_old.index[-1]
has_old = True
# Remove reference - big file!
df_old = None
except Exception as e:
logger.info("No data found for ticker " + ticker +
" with error: " + str(e))
else:
logger.info("Downloading new data for " + ticker + ".")
# Date range may not work with timezones
start_date = pd.Timestamp(start_date.replace(tzinfo=None))
finish_date = pd.Timestamp(finish_date.replace(tzinfo=None))
if finish_date - start_date < pd.Timedelta(days=1):
start_date_list = [start_date, finish_date]
else:
# download from that last point to the present day
start_date_list = pd.date_range(start_date, finish_date)
start_date_list = [
pd.Timestamp(x.to_pydatetime()) for x in start_date_list
]
if finish_date > start_date_list[-1]:
start_date_list.append(finish_date)
df = None
filename = os.path.join(self.temp_data_folder,
ticker) + '.' + fileformat
try:
# df = UtilFunc().read_dataframe_from_hdf(filename)
pass
except:
logger.info("Couldn't read HDF5/Parquet file for " + ticker)
# Create downloads in x minute chunks (if we request very large chunks of data with certain data providers,
# we could cause problems!)
if df is None:
df_remote_list = []
# Loop by day (otherwise can end up with too many open files!)
for i in range(0, len(start_date_list) - 1):
if chunk_size_str is not None:
if start_date_list[
i + 1] - start_date_list[i] < pd.Timedelta(
minutes=chunk_int_min):
start_date_hist = [start_date_list[i]]
finish_date_hist = [start_date_list[i + 1]]
else:
start_date_hist, finish_date_hist = UtilFunc(
).split_into_freq(start_date_list[i],
start_date_list[i + 1],
freq=chunk_size_str)
else:
start_date_hist = [start_date_list[i]]
finish_date_hist = [start_date_list[i + 1]]
# For FX and most other markets we should remove weekends (cryptocurrencies do have weekend data)
if self._remove_weekend_points():
start_date_hist, finish_date_hist = UtilFunc(
).remove_weekend_points(start_date_hist,
finish_date_hist)
output = []
if constants.use_multithreading:
# Create a multiprocess object for downloading data
swim = Swim(parallel_library=constants.
database_populator_threading_library)
pool = swim.create_pool(thread_no=self._get_threads())
result = []
for i in range(0, len(start_date_hist)):
# output.append(self._fetch_market_data(start_date_hist[i], finish_date_hist[i], ticker))
result.append(
pool.apply_async(
self._fetch_market_data,
args=(start_date_hist[i],
finish_date_hist[i], ticker,
write_temp_to_disk,
read_cached_from_disk, web_proxies)))
output = [p.get() for p in result]
swim.close_pool(pool, True)
else:
# Otherwise run in single threaded fashion
for i in range(0, len(start_date_hist)):
output.append(
self._fetch_market_data(
start_date_hist[i],
finish_date_hist[i],
ticker,
write_to_disk=write_temp_to_disk,
read_cached_from_disk=read_cached_from_disk,
web_proxies=web_proxies))
# Get all the dataframe chunks and returned messages
df_list = [
self._remove_duplicates_time_series(x,
remove_duplicates,
field='mid')
for x, y in output if x is not None
]
msg_list = [
y for x, y in output if x is not None and y is not None
]
# Concatenate all the 5 (or larger) minute data chunks
try:
if df_list != []:
df_temp = pd.concat(df_list)
if df_temp is not None:
if not (df_temp.empty):
df_remote_list.append(df_temp)
except Exception as e:
logger.error(str(e))
if df_remote_list != []:
df = pd.concat(df_remote_list)
# Need to sort data (database assumes sorted data for chunking/searches)
df = df.sort_index()
df = self.time_series_ops.localize_as_UTC(df)
if write_large_hdf5_parquet:
if df is not None:
if not (df.empty):
key = '_' + self._get_postfix() + "_" + \
(str(df.index[0]) + str(df.index[-1])).replace(":", '_').replace(" ", '_')
filename = os.path.join(
csv_folder,
ticker + key) + '.' + fileformat
# Temporary cache for testing purposes (also if the process crashes, we can read this back in)
UtilFunc().write_dataframe_to_binary(
df, filename, format=binary_format)
if df is not None:
# Assume UTC time (don't want to mix UTC and non-UTC in database!)
df = self.time_series_ops.localize_as_UTC(df)
# write CSV
if write_large_csv:
if df is not None:
if not (df.empty):
key = '_' + self._get_postfix() + "_" + \
(str(df.index[0]) + str(df.index[-1])).replace(":", '_').replace(" ", '_')
if csv_compression is 'gzip':
df.to_csv(os.path.join(csv_folder,
ticker + key + ".csv.gz"),
compression='gzip')
else:
df.to_csv(
os.path.join(csv_folder,
ticker + key + ".csv"))
if return_df:
df_dict[ticker] = df
# Dump what we have locally (or whatever DatabaseSource we have defined)
try:
start_date = start_date.replace(tzinfo=pytz.utc)
# Remove first point if matches last point from dataset
if has_old:
if df.index[0] == start_date:
df = df[-1:]
if df is not None:
df = df.sort_index()
df = self._remove_duplicates_time_series(df,
remove_duplicates,
field='mid')
if write_to_disk_db and df is not None:
data_source_local.append_market_data(
df,
ticker,
if_exists_table=if_exists_table,
if_exists_ticker=if_exists_ticker)
logger.info("Wrote to database for " + ticker)
except Exception as e:
final_err = "Data was missing for these dates " + str(start_date) + " - " + str(finish_date) + " for " \
+ str(tickers) + " Didn't write anything to disk or return any valid dataframe: " + str(e)
logger.error(final_err)
if df is None:
msg_list.append("No downloaded data for " + str(start_date) +
" - " + str(finish_date) +
". Is this a holiday?")
# Returns a status containing any failed downloads, which can be read by a user
return msg_list, df_dict
def _remove_duplicates_time_series(self,
df,
remove_duplicates,
field='mid'):
if remove_duplicates:
df = self.time_series_ops.drop_consecutive_duplicates(df, field)
return df
def combine_mini_df_from_disk(self, tickers=None, remove_duplicates=True):
"""Combines the mini HDF5/Parquet files for eg. 5 min chunks and combine into a very large HDF5/Parquet file, which is likely to be
for multiple months of data. Uses multithreading to speed up, by using a thread for each different ticker.
Parameters
----------
tickers : str (list or ditc)
Ticker of each ticker
remove_duplicates : bool
Remove duplicated market prices, which follow one another
Returns
-------
"""
if tickers is None: tickers = self.tickers.keys()
if isinstance(tickers, dict): tickers = tickers.keys()
if not (isinstance(tickers, list)):
tickers = [tickers]
if constants.use_multithreading:
swim = Swim(parallel_library=constants.
database_populator_threading_library)
pool = swim.create_pool(thread_no=self._get_threads())
result = []
for i in range(0, len(tickers)):
result.append(
pool.apply_async(
self._combine_mini_df_from_disk_single_thread,
args=(
tickers[i],
remove_duplicates,
)))
output = [p.get() for p in result]
swim.close_pool(pool, True)
else:
for i in range(0, len(tickers)):
self._combine_mini_df_from_disk_single_thread(
tickers[i], remove_duplicates)
def _combine_mini_df_from_disk_single_thread(self,
ticker,
remove_duplicates=True):
logger = LoggerManager.getLogger(__name__)
time_series_ops = TimeSeriesOps()
logger.info('Getting ' + ticker + ' filenames...')
temp_data_folder = self.temp_data_folder
filename_list = []
for root, dirnames, filenames in os.walk(temp_data_folder):
for filename in filenames:
if ticker in filename and '.' + fileformat in filename:
filename_h5_parquet = os.path.join(root, filename)
# if filename is less than 10MB add (otherwise likely a very large aggregated file!)
if os.path.getsize(filename_h5_parquet) < 10 * 1024 * 1024:
filename_list.append(filename_h5_parquet)
df_list = []
util_func = UtilFunc()
logger.info('Loading ' + ticker + ' mini dataframe into memory')
i = 0
if len(filename_list) == 0:
logger.warn("Looks like there are no files for " + ticker +
" in " + temp_data_folder +
". Are you sure path is correct?")
# Go through each mini file which represents a few minutes of data and append it
for filename in filename_list:
filesize = 0
try:
filesize = os.path.getsize(filename) / 1024.0
df = util_func.read_dataframe_from_binary(filename,
format=binary_format)
i = i + 1
# every 100 files print reading output@
if i % 100 == 0:
logger.info('Reading ' + filename + ' number ' + str(i))
if df is not None:
df = df.sort_index()
df = self._remove_duplicates_time_series(df,
remove_duplicates,
time_series_ops,
field='mid')
df_list.append(df)
except Exception as e:
logger.warn('Failed to parse ' + filename + " of " +
str(filesize) + "KB") # + str(e))
# if i > 1000:
# break
# Assume UTC time (don't want to mix UTC and non-UTC in database!)
if df_list == []:
logger.warn('No dataframe read for ' + ticker +
', cannot combine!')
return
logger.info('About to combine ' + ticker +
' into large dataframe to write to disk...')
df = pd.concat(df_list)
df = time_series_ops.localize_as_UTC(df)
df = df.sort_index()
df = self._remove_duplicates_time_series(df,
remove_duplicates,
time_series_ops,
field='mid')
postfix = '-' + self._get_postfix() + '-with-duplicates'
if remove_duplicates:
postfix = '-' + self._get_postfix() + '-no-duplicates'
filename = os.path.join(self.temp_large_data_folder,
ticker + postfix) + '.' + fileformat
df = time_series_ops.localize_as_UTC(df)
util_func.write_dataframe_to_binary(df, filename, format=binary_format)
def write_df_to_db(self,
tickers=None,
remove_duplicates=True,
if_exists_table='append',
if_exists_ticker='replace'):
"""Loads up a large HDF5/Parquet file from disk into a pd DataFrame and then dumps locally.
Uses multithreading to speed it up, by using a thread for each different ticker.
Parameters
----------
tickers : str (list or dict)
List of tickers
remove_duplicates : bool
True (default) - removes any follow on duplicates in the dataset
if_exists_table : str
'append' - if database table already exists append data to it
'replace' - remove existing database table
if_exists_ticker : str
'append' - if ticker already exists in the database, append to it
'replace' - replace any data for this ticker
Returns
-------
"""
if tickers is None: tickers = self.tickers.keys()
if isinstance(tickers, dict): tickers = tickers.keys()
if not (isinstance(tickers, list)):
tickers = [tickers]
if constants.use_multithreading:
swim = Swim(parallel_library=constants.
database_populator_threading_library)
pool = swim.create_pool(thread_no=self._get_threads())
result = []
for i in range(0, len(tickers)):
result.append(
pool.apply_async(self._write_df_to_db_single_thread,
args=(
tickers[i],
remove_duplicates,
if_exists_table,
if_exists_ticker,
)))
output = [p.get() for p in result]
swim.close_pool(pool, True)
else:
for i in range(0, len(tickers)):
self._write_df_to_db_single_thread(tickers[i],
remove_duplicates,
if_exists_table,
if_exists_ticker)
def _write_df_to_db_single_thread(self,
ticker,
remove_duplicates=True,
if_exists_table='append',
if_exists_ticker='replace'):
logger = LoggerManager.getLogger(__name__)
postfix = '-' + self._get_postfix() + '-with-duplicates'
if remove_duplicates:
postfix = '-' + self._get_postfix() + '-no-duplicates'
filename = os.path.join(self.temp_large_data_folder,
ticker + postfix) + '.' + fileformat
logger.info("Reading " + filename)
util_func = UtilFunc()
time_series_ops = TimeSeriesOps()
data_source_local = self._get_output_data_source()
df = util_func.read_dataframe_from_binary(filename,
format=binary_format)
if df is not None:
df = time_series_ops.localize_as_UTC(df)
data_source_local.append_market_data(
df,
ticker,
if_exists_table=if_exists_table,
if_exists_ticker=if_exists_ticker)
else:
logger.warn("Couldn't write dataframe for " + ticker +
" to database, appears it is empty!")
def _remove_saturday(self):
return True
class DataTestCreator(object):
"""This class copies market data/trade data to our database (by default: Arctic/MongoDB for market data and
MSSQL for trade data). It generates randomised test trades/orders based upon the market data, randomly perturbing
the bid/ask to simulate a traded price.
"""
def __init__(
self,
market_data_postfix='dukascopy',
csv_market_data=None,
write_to_db=True,
market_data_source='arctic',
sql_trade_database_type='mysql',
market_data_database_name=constants.
arctic_market_data_database_name,
market_data_database_table=constants.
arctic_market_data_database_table,
trade_data_database_name=constants.mysql_trade_data_database_name):
if csv_market_data is None:
if market_data_source in ['arctic', 'kdb', 'influxdb']:
self._market_data_source = market_data_source + '-' + market_data_postfix
else:
self._market_data_source = market_data_postfix
else:
self._market_data_source = csv_market_data
self._tca_market = Mediator.get_tca_market_trade_loader()
# Assumes MongoDB for tick data and MSSQL for trade/order data
if write_to_db:
if market_data_source == 'arctic':
self._database_source_market = DatabaseSourceArctic(
postfix=market_data_postfix) # market data source
elif market_data_source == 'kdb':
self._database_source_market = DatabaseSourceKDB(
postfix=market_data_postfix) # market data source
elif market_data_source == 'influxdb':
self._database_source_market = DatabaseSourceInfluxDB(
postfix=market_data_postfix) # market data source
self._market_data_database_name = market_data_database_name
self._market_data_database_table = market_data_database_table
if sql_trade_database_type == 'ms_sql_server':
self._database_source_trade = DatabaseSourceMSSQLServer(
) # trade data source
self._trade_data_database_name = trade_data_database_name
elif sql_trade_database_type == 'mysql':
self._database_source_trade = DatabaseSourceMySQL(
) # trade data source
self._trade_data_database_name = trade_data_database_name
elif sql_trade_database_type == 'sqlite':
self._database_source_trade = DatabaseSourceSQLite(
) # trade data source
self._trade_data_database_name = trade_data_database_name
self.time_series_ops = TimeSeriesOps()
self.rand_time_series = RandomiseTimeSeries()
def populate_test_database_with_csv(self,
csv_market_data=None,
ticker=None,
csv_trade_data=None,
if_exists_market_table='append',
if_exists_market_ticker='replace',
if_exists_trade_table='replace',
market_data_postfix='dukascopy',
remove_market_duplicates=False):
"""Populates both the market database and trade database with market data and trade/order data respectively, which
have been sourced in CSV/HDF5 files.
Parameters
----------
csv_market_data : str (list)
Path of CSV/HDF5 file with market data
ticker : str (list)
Ticker for market data
csv_trade_data : dict
Dictionary with name of trade/order and associated path of CSV/HDF5 file with trade/order data
if_exists_market_table : str
'replace' - deletes whole market data table
'append' (default) - adds to existing market data
if_exists_market_ticker : str
'replace' (default) - deletes existing data for the ticker
'append' - appends data for this this
if_exists_trade_table : str
'replace' - deletes data in trade table, before writing
market_data_postfix : str (default 'dukascopy')
data source for market data (typically broker or venue name)
remove_market_duplicates : bool (default: False)
Should we remove any duplicated values in market data (for TCA purposes, we can usually remove duplicated values
However, we need to be careful when using richer market data (eg. with volume data), where consecutive prices
might be the same but have different volume/other fields
Returns
-------
"""
logger = LoggerManager.getLogger(__name__)
# Populate the market data (eg. spot data)
if csv_market_data is not None:
self._database_source_market.set_postfix(market_data_postfix)
logger.info('Writing market data to database')
self._database_source_market.convert_csv_to_table(
csv_market_data,
ticker,
self._market_data_database_table,
database_name=self._market_data_database_name,
if_exists_table=if_exists_market_table,
if_exists_ticker=if_exists_market_ticker,
remove_duplicates=remove_market_duplicates)
# Populate the test trade/order data (which will have been randomly generated)
if csv_trade_data is not None:
logger.info('Writing trade data to database')
# Allow for writing of trades + orders each to a different database table
if isinstance(csv_trade_data, dict):
for key in csv_trade_data.keys():
# csv file name, trade/order name is key (eg. trade_df)
self._database_source_trade.convert_csv_to_table(
csv_trade_data[key],
None,
key,
database_name=self._trade_data_database_name,
if_exists_table=if_exists_trade_table)
# Otherwise simply assume we are writing trade data
else:
logger.error("Specify trade/orders hierarchy")
logger.info('Completed writing data to database')
def create_test_trade_order(self,
ticker,
start_date='01 Jan 2016',
finish_date='01 May 2018',
order_min_size=0.5 * constants.MILLION,
order_max_size=20.0 * constants.MILLION,
number_of_orders_min_per_year=252 * 20,
number_of_orders_max_per_year=252 * 200):
"""Create a randomised list of orders & trade using indicative market data as a source (and perturbing the
execution prices, within various constraints, such as the approximate size of orders trades, the orders per _year
Parameters
----------
ticker : str
Ticker
start_date : str
Start date of the orders
finish_date : str
Finish date of the orders
order_min_size : float
Minimum size of orders
order_max_size : float
Maximum size of orders
number_of_orders_min_per_year : int
Minimum orders per _year
number_of_orders_max_per_year : int
Maximum orders per _year
Returns
-------
DataFrame
"""
logger = LoggerManager.getLogger(__name__)
if isinstance(ticker, str):
ticker = [ticker]
order_list = []
trade_list = []
start_date = self.time_series_ops.date_parse(start_date,
assume_utc=True)
finish_date = self.time_series_ops.date_parse(finish_date,
assume_utc=True)
util_func = UtilFunc()
# Make this parallel? but may have memory issues
for tick in ticker:
logger.info("Loading market data for " + tick)
# split into yearly chunks (otherwise can run out of memory easily)
date_list = util_func.split_date_single_list(
start_date,
finish_date,
split_size='yearly',
add_partial_period_start_finish_dates=True)
# TODO do in a batch fashion
for i in range(0, len(date_list) - 1):
df = self._tca_market.get_market_data(
MarketRequest(start_date=date_list[i],
finish_date=date_list[i + 1],
ticker=tick,
data_store=self._market_data_source))
# self.database_source_market.fetch_market_data(start_date = start_date, finish_date = finish_date, ticker = tick)
# Need to make sure there's sufficient market data!
if df is not None:
if len(df.index) >= 2:
# Get the percentage of the _year represented by the difference between the start and finish dates
year_perc = float(
(df.index[-1] - df.index[0]).seconds /
(24.0 * 60.0 * 60.0)) / 365.0
logger.info("Constructing randomised trades for " +
tick)
number_of_orders_min = int(
year_perc * number_of_orders_min_per_year)
number_of_orders_max = int(
year_perc * number_of_orders_max_per_year)
# Split up the data frame into equally sized chunks
df_orders = self._derive_order_no(
self._strip_columns(df, tick),
number_of_orders_min, number_of_orders_max)
# Don't want a memory leak, so delete this as soon possible from memory!
del df
# order_counter = 0
logger.info("Now beginning order construction for " +
tick)
# For each order create randomised associated trades
# group together all the trades per day as orders
for df_order in df_orders:
# Set duration of the grandparent order (find randomised start/finish time)
# somewhere between 0-25% for start, and 75% to 100% for end point
df_order = self.rand_time_series.randomly_truncate_data_frame_within_bounds(
df_order, start_perc=0.25, finish_perc=0.75)
logger.debug("Creating order between " +
str(df_order.index[0]) + " - " +
str(df_order.index[-1]))
# Assume all orders/trades are in the same direction (which is randomly chosen)
buy_sell = randint(0, 1)
# Sell trades
if buy_sell == 0:
side_no = -1
side = 'bid'
# Buy trades
else:
side_no = 1
side = 'ask'
magnitude = 10000.0 * 2
if tick == 'USDJPY': magnitude = 100.0 * 2.0
if randint(0, 100) > 97:
new_tick = tick[3:6] + tick[0:3]
if 'ticker' in df_order.columns:
df_order['ticker'] = new_tick
if 'bid' in df_order.columns and 'ask' in df_order.columns:
ask = 1.0 / df_order['bid']
bid = 1.0 / df_order['ask']
df_order['bid'] = bid
df_order['ask'] = ask
df_order['mid'] = 1.0 / df_order['mid']
else:
new_tick = tick
# Get 'bid' for sells, and 'ask' for buys
df_order['trade_value'] = df_order[side]
# We want to simulate the executions by perturbing the buys randomly
df_order = self.rand_time_series.randomly_perturb_column(
df_order,
column='trade_value',
magnitude=magnitude)
# Assume notional is in base currency in vast majority of cases
if randint(0, 100) > 97:
notional_currency = new_tick[3:6]
else:
notional_currency = new_tick[0:3]
notional_multiplier = 1.0
if notional_currency == 'JPY':
notional_multiplier = 100.0
# Randomly choose a realistic order notional
# This will later be subdivided into trade notional
order_notional = randint(
order_min_size * notional_multiplier,
order_max_size * notional_multiplier)
order_additional_attributes = {
'broker_id':
constants.test_brokers_dictionary['All'],
'broker_sub_id':
constants.test_sub_brokers_dictionary['All'],
'algo_id':
constants.test_algos_dictionary['All'],
'algo_settings':
'default',
}
# Construct an order and add it to list
ind_order = self._construct_order(
df_order,
order_type='order',
notional=order_notional,
notional_currency=notional_currency,
side=side_no,
tick=new_tick,
additional_attributes=
order_additional_attributes)
order_list.append(ind_order)
trade_additional_attributes = self.grab_attributes_from_trade_order(
ind_order, [
'broker_id', 'broker_sub_id', 'algo_id',
'algo_settings'
])
# Now create all the broker messages for the order
# These will consist firstly of placement messages
# then potentionally cancels, cancel/replace and in most cases we randomly assign trade fills
trade_list = self._create_trades_from_order(
trade_list=trade_list,
df_order=df_order,
tick=new_tick,
ind_order=ind_order,
side_no=side_no,
order_notional=order_notional,
notional_currency=notional_currency,
additional_attributes=
trade_additional_attributes)
# order_counter = order_counter + 1
# Aggregate all the lists into DataFrames (setting 'date' as the index)
# For the trade dataframe also drop the 'index' column which was previous used to ensure that fills, were after placements
trade_order_dict = {
'trade_df':
self.time_series_ops.aggregate_dict_to_dataframe(
trade_list, 'date', 'index'),
'order_df':
self.time_series_ops.aggregate_dict_to_dataframe(
order_list, 'date')
}
return trade_order_dict
def _create_trades_from_order(self,
trade_list=None,
df_order=None,
tick=None,
ind_order=None,
side_no=None,
order_notional=None,
notional_currency=None,
additional_attributes=None):
trade_notional = order_notional
# Assume placement at start of order (a placement will have the order notional)
placement_event = self.construct_trade(
df_order,
order_notional=order_notional,
execution_venue=constants.test_venues_dictionary['All'],
order=ind_order,
side=side_no,
tick=tick,
event_type='placement',
notional_currency=notional_currency,
additional_attributes=additional_attributes)
trade_list.append(placement_event)
# Randomly choose an event (cancel/replace + fill, cancel or fill)
i = randint(0, 1000)
# Very rare event, same timestamp for a trade, same size too (but different ID)
if i < 1:
# executed trade
fill_event = self.construct_trade(
df_order,
order=ind_order,
order_notional=order_notional,
execution_venue=placement_event['venue'],
notional_currency=notional_currency,
executed_notional=int(float(trade_notional) * 0.5),
side=side_no,
tick=tick,
event_type='trade',
index=min(len(df_order.index), 5),
additional_attributes=additional_attributes)
trade_list.append(fill_event)
fill_event = self.construct_trade(
df_order.copy(),
order=ind_order,
order_notional=order_notional,
execution_venue=placement_event['venue'],
notional_currency=notional_currency,
executed_notional=int(float(trade_notional) * 0.5),
side=side_no,
tick=tick,
event_type='trade',
index=min(len(df_order.index), 5),
additional_attributes=additional_attributes)
trade_list.append(fill_event)
elif i < 50:
# Cancel/replace event
cancel_replace_index = randint(1, min(len(df_order.index), 20))
cancel_replace_event = self.construct_trade(
df_order,
order=ind_order,
execution_venue=placement_event['venue'],
notional_currency=notional_currency,
side=side_no,
tick=tick,
event_type='cancel/replace',
index=cancel_replace_index,
additional_attributes=additional_attributes)
trade_list.append(cancel_replace_event)
fill_event_index = randint(cancel_replace_index + 1,
min(len(df_order.index), 50))
# Executed fill event
fill_event = self.construct_trade(
df_order,
order=ind_order,
order_notional=order_notional,
execution_venue=placement_event['venue'],
executed_notional=trade_notional,
notional_currency=notional_currency,
side=side_no,
tick=tick,
event_type='trade',
index=fill_event_index,
additional_attributes=additional_attributes)
trade_list.append(fill_event)
# Rare event, full cancellation of order
elif i < 60:
cancel_index = randint(1, min(len(df_order.index), 20))
cancel_event = self.construct_trade(
df_order,
order=ind_order,
execution_venue=placement_event['venue'],
executed_notional=0,
notional_currency=notional_currency,
side=side_no,
tick=tick,
event_type='cancel',
index=cancel_index,
additional_attributes=additional_attributes)
trade_list.append(cancel_event)
elif i < 80:
# Where we have two trade fills for a single child order of different sizes
perc = float(randint(5, 95)) / 100.0
# executed trade
fill_event = self.construct_trade(
df_order,
order=ind_order,
execution_venue=placement_event['venue'],
notional_currency=notional_currency,
executed_notional=int(float(trade_notional) * perc),
side=side_no,
tick=tick,
event_type='trade',
index=randint(1, min(len(df_order.index), 50)),
additional_attributes=additional_attributes)
trade_list.append(fill_event)
fill_event = self.construct_trade(
df_order,
order=ind_order,
execution_venue=placement_event['venue'],
notional_currency=notional_currency,
executed_notional=int(float(trade_notional) * (1.0 - perc)),
side=side_no,
tick=tick,
event_type='trade',
index=randint(fill_event['index'], min(len(df_order.index),
100)),
additional_attributes=additional_attributes)
trade_list.append(fill_event)
# Most common event, single trade/fill
else:
# Executed trade
fill_event = self.construct_trade(
df_order,
order=ind_order,
order_notional=order_notional,
execution_venue=placement_event['venue'],
notional_currency=notional_currency,
executed_notional=trade_notional,
side=side_no,
tick=tick,
event_type='trade',
index=randint(1, min(len(df_order.index), 50)),
additional_attributes=additional_attributes)
trade_list.append(fill_event)
return trade_list
def _derive_order_no(self, df, orders_min, orders_max):
df_chunks_list = self.time_series_ops.split_array_chunks(
df, chunks=randint(orders_min, orders_max))
if isinstance(df_chunks_list, pd.DataFrame):
return [df_chunks_list]
return df_chunks_list
def _create_unique_trade_id(self, order_type, ticker, datetime_input):
return order_type + "_" + ticker + str(datetime_input) + "_" + str(
datetime.datetime.utcnow()) + '_' + str(randint(0, 100000))
def _construct_order(self,
df,
order_type=None,
notional=None,
notional_currency=None,
side=None,
tick=None,
additional_attributes=None,
**kwargs):
order = {}
# For internal purposes
order['ticker'] = tick
order['notional'] = notional
order['notional_currency'] = notional_currency
order['side'] = side
order['date'] = df.index[0]
order['benchmark_date_start'] = df.index[0]
order['benchmark_date_end'] = df.index[-1]
order['price_limit'] = df['mid'][0]
order['arrival_price'] = df['mid'][0]
order['portfolio_id'] = self.add_random_sample(
constants.test_portfolios_dictionary['All'])
order['portfolio_manager_id'] = self.add_random_sample(
constants.test_portfolio_managers_dictionary['All'])
order['trader_id'] = self.add_random_sample(
constants.test_traders_dictionary['All'])
order['account_id'] = self.add_random_sample(
constants.test_accounts_dictionary['All'])
order['id'] = self._create_unique_trade_id(order_type, tick,
order['date'])
kwargs['order'] = order
order = self.additional_order_processing(**kwargs)
# Add additional randomized attributes
if additional_attributes is not None:
# Merge list of additional attributes
if isinstance(additional_attributes, list):
result_dict = {}
for d in additional_attributes:
result_dict.update(d)
additional_attributes = result_dict
for k in additional_attributes.keys():
additional = additional_attributes[k]
if isinstance(additional, list):
additional = self.add_random_sample(additional)
order[k] = additional
return order
def additional_order_processing(self, **kwargs):
return kwargs['order']
def construct_trade(self,
df,
order_notional=None,
executed_notional=None,
notional_currency=None,
execution_venue=None,
side=None,
order=None,
tick=None,
event_type=None,
additional_attributes=None,
index=0):
trade = {}
if order_notional is None:
order_notional = 0
trade['order_notional'] = order_notional
trade['notional_currency'] = notional_currency
trade['ticker'] = tick
trade['side'] = side
trade['index'] = index
trade['date'] = df.index[index]
trade['market_bid'] = df['bid'][index]
trade['market_ask'] = df['ask'][index]
trade['market_mid'] = df['mid'][index]
trade['price_limit'] = df['mid'][index]
trade['event_type'] = event_type
trade['executed_price'] = 0
trade['venue'] = execution_venue
trade['executed_notional'] = 0
if event_type == 'trade':
trade['executed_notional'] = executed_notional
try:
if np.isnan(trade['executed_notional']):
pass
except:
print('w')
trade['executed_price'] = df['trade_value'][index]
trade['venue'] = self.add_random_sample(
constants.test_venues_dictionary['All'])
if order is not None:
trade[constants.order_name + '_pointer_id'] = order['id']
trade['price_limit'] = order['price_limit']
trade['portfolio_id'] = order['portfolio_id']
trade['portfolio_manager_id'] = order['portfolio_manager_id']
trade['trader_id'] = order['trader_id']
trade['account_id'] = order['account_id']
trade['id'] = self._create_unique_trade_id('execution', tick,
trade['date'])
if additional_attributes is not None:
for k in additional_attributes.keys():
trade[k] = additional_attributes[k]
return trade
def add_random_sample(self, lst):
return lst[randint(0, len(lst) - 1)]
def grab_attributes_from_trade_order(self, trade_order, attributes):
dict = {}
for a in attributes:
dict[a] = trade_order[a]
return dict
def _strip_columns(self, df, tick):
# filter market data so only includes specific asset (Arctic won't have this) and during "main" FX hours
# exclude any Saturday data
if 'ticker' in df.columns:
df = df[(df.index.hour >= 6) & (df.index.hour < 21) &
(df.index.dayofweek != 5) & (df['ticker'] == tick)]
else:
df = df[(df.index.hour >= 6) & (df.index.hour < 21) &
(df.index.dayofweek != 5)]
keep_cols = ['bid', 'ask', 'mid']
remove_cols = []
for k in df.columns:
if k not in keep_cols:
remove_cols.append(k)
if remove_cols != []:
df.drop(remove_cols, inplace=True, axis=1)
# Ensure that the market is data is properly sorted
df.sort_index(inplace=True)
# Calculate mid price (if it doesn't exist)
if 'mid' not in df.columns:
df['mid'] = (df['bid'].values + df['ask'].values) / 2.0
# Create synthetic bid/ask if they don't exist
if 'bid' not in df.columns:
df['bid'] = 0.9995 * df['mid'].values
if 'ask' not in df.columns:
df['ask'] = 1.0005 * df['mid'].values
# First strip away out of hours times
# remove any trades before 6am and after 9pm GMT
return df