def __get_value_list_for_y_variable_options__(chart_type: str, predictor: str):
if chart_type == CHT.PREDICTOR:
if predictor == PRED.FOR_TRADE:
return TradeTable.get_label_columns_for_trades_statistics()
elif chart_type == CHT.MY_TRADES:
return [DC.TRADE_RESULT_PCT]
return TradeTable.get_columns_for_statistics_y_variable()
def __get_value_list_for_x_variable_options__(chart_type: str, predictor: str):
if chart_type == CHT.PREDICTOR:
if predictor == PRED.FOR_TRADE:
return TradeTable.get_feature_columns_for_trades_statistics()
elif chart_type == CHT.MY_TRADES:
return [DC.PATTERN_RANGE_BEGIN_DT]
return TradeTable.get_columns_for_statistics_x_variable()
def __init__(self, app: Dash, sys_config: SystemConfiguration,
trade_handler_online: PatternTradeHandler):
MyPatternDashBaseTab.__init__(self, app, sys_config)
self.exchange_config = self.sys_config.exchange_config
self._trade_handler_online = trade_handler_online
self._df_trade = self.sys_config.db_stock.get_trade_records_for_replay_as_dataframe(
)
self._df_trade_for_replay = self._df_trade[
TradeTable.get_columns_for_replay()]
self._trade_rows_for_data_table = MyDCC.get_rows_from_df_for_data_table(
self._df_trade_for_replay)
self._df_pattern = self.sys_config.db_stock.get_pattern_records_for_replay_as_dataframe(
)
self._df_pattern_for_replay = self._df_pattern[
PatternTable.get_columns_for_replay()]
self._pattern_rows_for_data_table = MyDCC.get_rows_from_df_for_data_table(
self._df_pattern_for_replay)
self.__init_selected_row__()
self.__init_replay_handlers__()
self._selected_pattern_trade = None
self._selected_buy_trigger = None
self._selected_trade_strategy = None
self._n_click_restart = 0
self._n_click_cancel_trade = 0
self._n_click_reset = 0
self._replay_speed = 4
self._trades_stored_number = 0
self._trades_online_active_number = 0
self._trades_online_all_number = 0
self._pattern_stored_number = 0
self._cached_trade_table = None
self._time_stamp_last_ticker_refresh = MyDate.time_stamp_now()
self._check_actual_trades_for_trade_handler_online_n_intervals = -1
self._print_callback_start_details = False
def __get_drop_down_value_dict__(self) -> dict:
return {
DDT.CHART_TYPE:
CHT.get_chart_types_for_trade_statistics(),
DDT.PREDICTOR:
PRED.get_for_trade_all(),
DDT.CATEGORY:
TradeTable.get_columns_for_statistics_category(),
DDT.X_VARIABLE:
TradeTable.get_columns_for_statistics_x_variable(),
DDT.Y_VARIABLE:
TradeTable.get_columns_for_statistics_y_variable(),
DDT.CHART_TEXT_VARIABLE:
TradeTable.get_columns_for_statistics_text_variable(),
DDT.PATTERN_TYPE:
FT.get_all_for_statistics()
}
def __init__(self, df_base: pd.DataFrame, color_handler: DashColorHandler,
db_stock: StockDatabase, predictor_optimizer: PatternPredictorOptimizer):
self.db_stock = db_stock
self.pattern_table = PatternTable()
self.trade_table = TradeTable()
self._predictor_optimizer = predictor_optimizer
MyDashTabStatisticsPlotter.__init__(self, df_base, color_handler)
self._df_base_cache_dict = {}
def __get_table_for_trades__(self, take_cached=False):
if take_cached and self._cached_trade_table is not None:
return self._cached_trade_table
rows = self.__get_table_rows_for_trades__()
selected_row_indices_for_table = self.__get_selected_row_indices_for_trade_table__(
rows)
if len(rows) == 0:
rows = self.__get_empty_data_row__()
self._cached_trade_table = MyDCC.data_table(
self._data_table_name,
rows=rows,
selected_row_indices=selected_row_indices_for_table,
style_cell_conditional=TradeTable.get_table_style_cell_conditional(
self._selected_trade_type),
style_data_conditional=TradeTable.get_table_style_data_conditional(
self._selected_trade_type, rows),
columns=self.__get_columns_for_trading_table__())
return self._cached_trade_table
def __init__(self):
self._entity_table = EquityTable()
self._stocks_table = StocksTable()
self._company_table = CompanyTable()
self._pattern_table = PatternTable()
self._trade_table = TradeTable()
self._wave_table = WaveTable()
self._asset_table = AssetTable()
self._metric_table = MetricTable()
self._trade_policy_metric_table = TradePolicyMetricTable()
self._process_table = ProcessTable()
BaseDatabase.__init__(self)
self._file_log = PatternLog()
self._dt_now_time_stamp = int(datetime.now().timestamp())
self._bitfinex_crypto_fetcher = BitfinexCryptoFetcher()
self._alphavantage_crypto_fetcher = AlphavantageCryptoFetcher()
self._alphavantage_stock_fetcher = AlphavantageStockFetcher()
self._alphavantage_forex_fetcher = AlphavantageForexFetcher()
self._quandl_fetcher = QuandlFetcher()
self._sleep_seconds = 5
def __get_value_list_for_x_variable_options__(category: str,
predictor: str):
if category == STBL.PATTERN:
if predictor == PRED.TOUCH_POINT:
return PatternTable.get_label_columns_touch_points_for_statistics(
)
elif predictor == PRED.BEFORE_BREAKOUT:
return PatternTable.get_label_columns_before_breakout_for_statistics(
)
elif predictor == PRED.AFTER_BREAKOUT:
return PatternTable.get_label_columns_after_breakout_for_statistics(
)
else:
return TradeTable.get_label_columns_for_trades_statistics()
def __get_columns_for_trading_table__(self):
if self._selected_trade_type in [TP.ONLINE, '']:
return TradeTable.get_columns_for_online_trades()
else:
return TradeTable.get_columns_for_replay()
class StockDatabase(BaseDatabase):
def __init__(self):
self._entity_table = EquityTable()
self._stocks_table = StocksTable()
self._company_table = CompanyTable()
self._pattern_table = PatternTable()
self._trade_table = TradeTable()
self._wave_table = WaveTable()
self._asset_table = AssetTable()
self._metric_table = MetricTable()
self._trade_policy_metric_table = TradePolicyMetricTable()
self._process_table = ProcessTable()
BaseDatabase.__init__(self)
self._file_log = PatternLog()
self._dt_now_time_stamp = int(datetime.now().timestamp())
self._bitfinex_crypto_fetcher = BitfinexCryptoFetcher()
self._alphavantage_crypto_fetcher = AlphavantageCryptoFetcher()
self._alphavantage_stock_fetcher = AlphavantageStockFetcher()
self._alphavantage_forex_fetcher = AlphavantageForexFetcher()
self._quandl_fetcher = QuandlFetcher()
self._sleep_seconds = 5
@property
def trade_table(self):
return self._trade_table
@property
def pattern_table(self):
return self._pattern_table
@property
def wave_table(self):
return self._wave_table
def is_symbol_loaded(self,
symbol: str,
and_clause='',
period=PRD.DAILY,
aggregation=1):
query = "SELECT * from {} WHERE Symbol = '{}' and Period = '{}' and Aggregation = {}".format(
STBL.STOCKS, symbol, period, aggregation)
query += '' if and_clause == '' else ' and {}'.format(and_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def get_name_for_symbol(self, symbol: str):
if symbol[-3:] == 'USD':
return symbol
company_dic = self.__get_company_dict__(symbol)
return '' if len(company_dic) == 0 else company_dic[symbol].Name
def __get_table_dict__(self) -> dict:
return {
STBL.STOCKS: self._stocks_table,
STBL.COMPANY: self._company_table,
STBL.PROCESS: self._process_table,
STBL.WAVE: self._wave_table,
STBL.EQUITY: self._entity_table,
STBL.TRADE_POLICY_METRIC: self._trade_policy_metric_table,
STBL.TRADE: self._trade_table,
STBL.PATTERN: self._pattern_table,
STBL.ASSET: self._asset_table,
STBL.METRIC: self._metric_table
}
def __get_db_name__(self):
return 'MyStocks.sqlite'
def __get_db_path__(self) -> str:
package_dir = os.path.abspath(os.path.dirname(__file__))
return os.path.join(package_dir, self.__get_db_name__())
def import_stock_data_by_deleting_existing_records(self,
symbol: str,
period=PRD.DAILY,
output_size=OPS.COMPACT
):
self.delete_records("DELETE from Stocks WHERE Symbol = '" +
str(symbol) + "'")
input_dic = self.get_input_values_for_stock_table(
period, symbol, output_size)
self.__insert_data_into_table__('Stocks', input_dic)
def update_stock_data_by_index(self,
index: str,
period=PRD.DAILY,
aggregation=1):
company_dict = self.__get_company_dict__()
self.__check_company_dic_against_web__(index, company_dict)
last_loaded_date_stamp_dic = self.__get_last_loaded_time_stamp_dic__(
period=period)
index_list = self.__get_index_list__(index)
for index in index_list:
print('\nUpdating {}...\n'.format(index))
ticker_dic = IndicesComponentFetcher.get_ticker_name_dic(index)
for ticker in ticker_dic:
# this condition is only for Q_FSE required since we have different lists...
if index != INDICES.Q_FSE or \
self.is_stock_data_for_single_value_available(period, aggregation, ticker, index):
name = ticker_dic[ticker]
self.__update_stock_data_for_single_value__(
period, aggregation, ticker, name, index, company_dict,
last_loaded_date_stamp_dic)
self.__handle_error_cases__()
def check_stock_data_by_index(self,
index: str,
period=PRD.DAILY,
aggregation=1):
print('\nChecking {}...\n'.format(index))
ticker_dic = IndicesComponentFetcher.get_ticker_name_dic(index)
for ticker in ticker_dic:
if self.is_stock_data_for_single_value_available(
period, aggregation, ticker, index):
print('Data available for {}/{} on {}'.format(
ticker, ticker_dic[ticker], index))
else:
print('NO data available for {}/{} on {}'.format(
ticker, ticker_dic[ticker], index))
def __check_company_dic_against_web__(self, index: str,
company_dict: dict):
company_dict_by_web = IndicesComponentFetcher.get_ticker_name_dic(
index)
for key in company_dict_by_web:
if key not in company_dict:
name = company_dict_by_web[key]
self.__insert_company_in_company_table__(key, name, True)
self.__insert_ticker_in_equity_table__(key, name, index)
new_dict = self.__get_company_dict__(key)
company_dict[key] = new_dict[key]
def update_crypto_currencies(self,
period=PRD.DAILY,
aggregation=1,
symbol_list=None):
index = INDICES.CRYPTO_CCY
company_dic = self.__get_company_dict__(like_input='USD')
last_loaded_date_stamp_dic = self.__get_last_loaded_time_stamp_dic__(
like_input='USD', period=period)
print('\nUpdating {}...\n'.format(index))
ticker_dic = IndicesComponentFetcher.get_ticker_name_dic(index)
for ticker in ticker_dic:
if symbol_list is None or ticker in symbol_list:
name = ticker_dic[ticker]
self.__update_stock_data_for_single_value__(
period, aggregation, ticker, name, index, company_dic,
last_loaded_date_stamp_dic)
self.__handle_error_cases__()
def __handle_error_cases__(self):
while len(self.error_handler.retry_dic) > 0:
retry_dic = dict(self.error_handler.retry_dic)
self.error_handler.retry_dic = {}
for ticker in retry_dic:
print('Handle error case for {}'.format(ticker))
time.sleep(self._sleep_seconds)
li = retry_dic[ticker]
self.update_stock_data_for_symbol(ticker, li[0], li[1])
def update_stock_data_for_symbol(self,
symbol: str,
name_input='',
period=PRD.DAILY,
aggregation=1):
company_dic = self.__get_company_dict__(symbol)
name = company_dic[symbol].Name if symbol in company_dic else name_input
last_loaded_dict = self.__get_last_loaded_time_stamp_dic__(symbol)
self.__update_stock_data_for_single_value__(period, aggregation,
symbol, name,
INDICES.UNDEFINED,
company_dic,
last_loaded_dict)
def insert_stocks_data(self, input_dict_list: list):
self.__insert_data_into_table__(STBL.STOCKS, input_dict_list)
def insert_pattern_data(self, input_dict_list: list):
self.__insert_data_into_table__(STBL.PATTERN, input_dict_list)
def insert_trade_data(self, input_dict_list: list):
self.__insert_data_into_table__(STBL.TRADE, input_dict_list)
def insert_equity_data(self, input_dict_list: list):
self.__insert_data_into_table__(STBL.EQUITY, input_dict_list)
def insert_wave_data(self, input_dict_list: list):
self.__insert_data_into_table__(STBL.WAVE, input_dict_list)
def insert_asset_entry(self, input_dict: dict, replace=False):
if self.is_asset_already_available(input_dict):
pass
else:
self.__insert_data_into_table__(STBL.ASSET, [input_dict])
def insert_data_into_table(self, table_name: str, input_dict_list: list):
return self.__insert_data_into_table__(table_name, input_dict_list)
def remove_existing_entries_from_data_dict_list(self, table_name: str,
input_dict_list) -> list:
return_list = []
for data_dict in input_dict_list:
is_existing = self.is_record_for_data_dict_already_available(
table_name, data_dict)
if not is_existing:
return_list.append(data_dict)
return return_list
def correct_data_types_withing_data_dict_list(self, table_name: str,
input_dict_list):
table = self.get_table_by_name(table_name)
for data_dict in input_dict_list:
for column_name, value in data_dict.items():
column = table.get_column(column_name)
if column is not None:
if column.is_integer:
data_dict[column_name] = int(
MyMath.get_float_for_string(value))
elif column.is_boolean:
if value == 'True':
data_dict[column_name] = True
elif value == 'False':
data_dict[column_name] = False
elif column.is_float:
if MyText.is_number(value):
data_dict[
column_name] = MyMath.get_float_for_string(
value)
elif column.is_date:
if MyText.is_date_time_date(value):
data_dict[column_name] = MyText.get_date_time_date(
value)
elif column.is_time:
if MyText.is_date_time_time(value):
data_dict[column_name] = MyText.get_date_time_time(
value)
def is_record_for_data_dict_already_available(self, table_name,
data_dict: dict) -> bool:
if table_name == STBL.ASSET:
return self.is_asset_already_available(data_dict)
elif table_name == STBL.STOCKS:
return self.is_stock_data_already_available(
data_dict[DC.SYMBOL], data_dict[DC.TIMESTAMP],
data_dict[DC.PERIOD], data_dict[DC.PERIOD_AGGREGATION])
elif table_name == STBL.WAVE:
return self.is_wave_already_available(data_dict)
elif table_name == STBL.TRADE:
return self.is_trade_already_available(data_dict[DC.ID])
elif table_name == STBL.EQUITY:
return self.is_equity_already_available(data_dict)
return False
def is_stock_data_for_single_value_available(self, period: str,
aggregation: int, ticker: str,
index: str):
df = self.__get_dataframe_for_single_value__(period,
aggregation,
ticker,
index,
OPS.CHECK,
is_check=True)
return df is not None
def __update_stock_data_for_single_value__(
self, period: str, aggregation: int, ticker: str, name: str,
index: str, company_dic: dict, last_loaded_date_stamp_dic: dict):
if ticker in ['CSX']:
self._file_log.log_message(
'exception - is not used {}'.format(ticker),
process='__update_stock_data_for_single_value__')
return
name = self._company_table.get_alternate_name(ticker, name)
last_loaded_time_stamp = last_loaded_date_stamp_dic[
ticker] if ticker in last_loaded_date_stamp_dic else 100000
output_size, limit = self._stocks_table.get_output_size_limit_for_data_update(
period, aggregation, self._dt_now_time_stamp,
last_loaded_time_stamp)
if output_size == OPS.NONE:
print('{} - {} is already up-to-date - no load required.'.format(
ticker, name))
return
if ticker in company_dic and not company_dic[ticker].ToBeLoaded:
return # must not be loaded
df = self.__get_dataframe_for_single_value__(period,
aggregation,
ticker,
index,
output_size,
limit=limit)
if df is None:
print(
'{} - {}: Error with some processing - nothing loaded.'.format(
ticker, name))
time.sleep(self._sleep_seconds)
return
if ticker not in company_dic:
to_be_loaded = df[CN.VOL].mean() > 10000
self.__insert_company_in_company_table__(ticker, name,
to_be_loaded)
self.__insert_ticker_in_equity_table__(ticker, name, index)
company_dic[ticker] = to_be_loaded
if not to_be_loaded:
time.sleep(self._sleep_seconds)
return
if ticker in last_loaded_date_stamp_dic:
df.sort_index(inplace=True)
df = df.loc[last_loaded_time_stamp:].iloc[1:]
if df.shape[0] > 0:
input_list = self.__get_df_data_for_insert_statement__(
df, period, ticker)
self.__insert_data_into_table__(STBL.STOCKS, input_list)
print('{} - {}: inserted {} new ticks.'.format(
ticker, name, df.shape[0]))
if index != INDICES.Q_FSE:
time.sleep(self._sleep_seconds)
def __get_dataframe_for_single_value__(self,
period: str,
aggregation: int,
ticker: str,
index: str,
output_size: str,
limit=0,
is_check=False):
try:
if index == INDICES.UNDEFINED:
index = self.get_index_for_symbol(ticker)
if index == INDICES.UNDEFINED:
return None
if ticker in [
INDICES.NASDAQ100, INDICES.Q_FSE
]: # is handled differently by calculation - not loading
return None
elif index == INDICES.CRYPTO_CCY:
# fetcher = self._alphavantage_crypto_fetcher
fetcher = self._bitfinex_crypto_fetcher
kw_args = self._bitfinex_crypto_fetcher.get_kw_args(
period, aggregation, ticker, limit=limit)
elif index == INDICES.FOREX:
fetcher = self._alphavantage_forex_fetcher
kw_args = self._alphavantage_forex_fetcher.get_kw_args(
period, aggregation, ticker, output_size, limit=limit)
elif index in [INDICES.Q_FSE]:
fetcher = self._quandl_fetcher
kw_args = self._quandl_fetcher.get_kw_args(period,
aggregation,
ticker,
output_size,
limit=limit)
else:
fetcher = self._alphavantage_stock_fetcher
kw_args = self._alphavantage_stock_fetcher.get_kw_args(
period, aggregation, ticker, output_size)
kw_args['is_check'] = is_check
fetcher.retrieve_data(**kw_args)
except KeyError:
self.error_handler.catch_known_exception(
__name__, 'Ticker={}. Continue with next...'.format(ticker))
time.sleep(self._sleep_seconds)
return None
except:
self.error_handler.catch_exception(
__name__, 'Ticker={}. Continue with next...'.format(ticker))
time.sleep(self._sleep_seconds)
return None
return fetcher.df
def get_index_for_symbol(self, symbol: str) -> str:
if symbol in ['FCEL', 'GE', 'MRAM']:
return INDICES.SHARES_GENERAL
query = "SELECT {} FROM {} WHERE {}='{}'".format(
EDC.EXCHANGE, self._entity_table.name, EDC.EQUITY_KEY, symbol)
df = self.select_data_by_query(query)
if df.shape[0] == 0:
return INDICES.UNDEFINED
exchange = df.iloc[0].values[0]
return INDICES.CRYPTO_CCY if exchange == 'Bitfinex' else exchange
def __get_kw_args_for_data_fetcher__(self, period: str, aggregation: int,
output_size: str, ticker: str):
kw_args = {
'symbol': ticker,
'period': period,
'aggregation': aggregation,
'output_size': output_size
}
return kw_args
def __get_company_dict__(self,
symbol_input: str = '',
like_input: str = ''):
company_dict = {}
query = self._company_table.get_select_query(symbol_input, like_input)
db_df = DatabaseDataFrame(self, query)
for index, rows in db_df.df.iterrows():
company_dict[rows.Symbol] = rows
return company_dict
def __get_last_loaded_time_stamp_dic__(self,
symbol_input: str = '',
like_input: str = '',
period=PRD.DAILY):
last_loaded_time_stamp_dic = {}
query = self._stocks_table.get_distinct_symbol_query(
symbol_input, like_input)
db_df = DatabaseDataFrame(self, query)
loaded_symbol_list = [
rows.Symbol for index, rows in db_df.df.iterrows()
]
query = 'SELECT Symbol, MAX(Timestamp) as Max_ts FROM {} WHERE Period = "{}" GROUP BY Symbol'.format(
STBL.STOCKS, period)
db_df = DatabaseDataFrame(self, query)
symbol_timestamp_dict_list = db_df.df.to_dict('records')
symbol_timestamp_dict = {}
for dict_entries in symbol_timestamp_dict_list:
symbol_timestamp_dict[
dict_entries['Symbol']] = dict_entries['Max_ts']
for symbol in loaded_symbol_list:
last_loaded_time_stamp_dic[symbol] = symbol_timestamp_dict.get(
symbol, 0)
return last_loaded_time_stamp_dic
def __insert_company_in_company_table__(self, ticker: str, name: str,
to_be_loaded: bool):
input_dic = self._company_table.get_insert_dict_for_company(
ticker, name, to_be_loaded)
try:
self.__insert_data_into_table__(STBL.COMPANY, [input_dic])
except Exception:
self.error_handler.catch_exception(__name__)
print('{} - {}: problem inserting into {} table.'.format(
ticker, name, STBL.COMPANY))
def __insert_ticker_in_equity_table__(self, ticker: str, name: str,
index: str):
input_dic = self._entity_table.get_insert_dict_for_equity(
ticker, name, index)
try:
self.__insert_data_into_table__(STBL.EQUITY, [input_dic])
except Exception:
self.error_handler.catch_exception(__name__)
print('{} - {}: problem inserting into {} table.'.format(
ticker, name, STBL.EQUITY))
@staticmethod
def __get_index_list__(index: str):
return [INDICES.DOW_JONES, INDICES.NASDAQ100, INDICES.SP500
] if index == INDICES.ALL else [index]
def get_input_values_for_stock_table(self, period, symbol: str,
output_size: str):
kw_args = {
'symbol': symbol,
'period': period,
'output_size': output_size
}
self._alphavantage_stock_fetcher.retrieve_data(**kw_args)
return self.__get_df_data_for_insert_statement__(
self._alphavantage_stock_fetcher.df, period, symbol)
@staticmethod
def __get_df_data_for_insert_statement__(df: pd.DataFrame,
period: str,
symbol: str,
aggregation=1):
insert_handler = StockInsertHandler(symbol, period, aggregation)
for time_stamp, row in df.iterrows():
insert_handler.add_data_frame_row(time_stamp, row)
return insert_handler.input_dict_list
def save_tick_list(self, tick_list: list, symbol: str, period: str,
aggregation: int):
insert_handler = StockInsertHandler(symbol, period, aggregation)
for wave_tick in tick_list:
if not self.is_stock_data_already_available(
symbol, wave_tick.time_stamp, period, aggregation):
insert_handler.add_wave_tick(wave_tick)
self.__insert_data_into_table__(STBL.STOCKS,
insert_handler.input_dict_list)
def create_equity_table(self):
self.__create_table__(STBL.EQUITY)
def create_stocks_table(self):
self.__create_table__(STBL.STOCKS)
def create_company_table(self):
self.__create_table__(STBL.COMPANY)
def create_pattern_table(self):
self.__create_table__(STBL.PATTERN)
def create_trade_table(self):
self.__create_table__(STBL.TRADE)
def create_wave_table(self):
self.__create_table__(STBL.WAVE)
def create_asset_table(self):
self.__create_table__(STBL.ASSET)
def create_metric_table(self):
self.__create_table__(STBL.METRIC)
def create_trade_policy_metric_table(self):
self.__create_table__(STBL.TRADE_POLICY_METRIC)
def create_process_table(self):
self.__create_table__(STBL.PROCESS)
def create_wave_view(self):
self.__create_view__(SVW.V_WAVE)
def create_pattern_view(self):
self.__create_view__(SVW.V_PATTERN)
def __insert_pattern_in_pattern_table__(self, ticker: str,
input_dic: dict):
try:
self.__insert_data_into_table__(STBL.PATTERN, [input_dic])
except Exception:
self.error_handler.catch_exception(__name__)
print('{}: problem inserting into {} table.'.format(
ticker, STBL.PATTERN))
def is_stock_data_already_available(self, symbol: str, time_stamp: int,
period: str, aggregation: int) -> bool:
query = self._stocks_table.get_query_for_unique_record(
symbol, time_stamp, period, aggregation)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_pattern_already_available(self, pattern_id: str) -> bool:
query = self._pattern_table.get_query_select_for_unique_record_by_id(
pattern_id)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def delete_existing_pattern_by_pattern_id(self, pattern_id: str):
self.delete_records("DELETE from {} WHERE Id = '{}'".format(
STBL.PATTERN, pattern_id))
def is_wave_already_available(self, wave_data_dict: dict) -> bool:
query = self._wave_table.get_query_select_for_unique_record_by_dict(
wave_data_dict)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def delete_existing_wave(self, wave_data_dict: dict):
query = self._wave_table.get_query_select_for_unique_record_by_dict(
wave_data_dict)
db_df = DatabaseDataFrame(self, query)
if db_df.df.shape[0] > 0:
rowid = db_df.df['rowid'][0]
self.delete_records("DELETE from {} WHERE rowid = {}".format(
STBL.WAVE, rowid))
def is_asset_already_available(self, input_data_dict: dict):
query = self._asset_table.get_query_select_for_unique_record_by_dict(
input_data_dict)
# print('is_asset_already_available: query={}'.format(query))
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_any_asset_already_available_for_timestamp(self, time_stamp: int):
query = self._asset_table.get_query_select_for_records(
'Validity_Timestamp={}'.format(time_stamp))
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_equity_already_available(self, input_data_dict: dict):
query = self._entity_table.get_query_select_for_unique_record_by_dict(
input_data_dict)
# print('is_equity_already_available: query={}'.format(query))
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def update_trade_type_for_pattern(self, pattern_id: str, trade_type: str):
where_clause = "Pattern_ID = '{}'".format(pattern_id)
query = self._trade_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
trade_type_not = TRT.NOT_SHORT if trade_type == TRT.SHORT else TRT.NOT_LONG
trade_type_new = trade_type_not if db_df.df.shape[
0] == 0 else trade_type
self.update_table_column(STBL.PATTERN, DC.TRADE_TYPE, trade_type_new,
"ID = '{}'".format(pattern_id))
def get_pattern_records_as_dataframe(self,
where_clause='') -> pd.DataFrame:
query = self._pattern_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_prediction_mean_values_for_nearest_neighbor_ids(
self, id_list: list):
where_clause = "id in ('{}')".format("','".join(id_list))
df = self.get_pattern_records_as_dataframe(where_clause)
df = df[[
DC.NEXT_PERIOD_HALF_POSITIVE_PCT, DC.NEXT_PERIOD_FULL_POSITIVE_PCT,
DC.NEXT_PERIOD_HALF_NEGATIVE_PCT, DC.NEXT_PERIOD_FULL_NEGATIVE_PCT,
DC.TICKS_FROM_BREAKOUT_TILL_POSITIVE_HALF,
DC.TICKS_FROM_BREAKOUT_TILL_POSITIVE_FULL,
DC.TICKS_FROM_BREAKOUT_TILL_NEGATIVE_HALF,
DC.TICKS_FROM_BREAKOUT_TILL_NEGATIVE_FULL
]]
return_dict = df.mean().to_dict()
return_dict = {
label: round(value)
for label, value in return_dict.items()
}
return return_dict
def get_wave_counter_dict(self, period: str, limit: int = 0):
query = self._wave_table.get_query_for_wave_counter(period, limit)
db_df = DatabaseDataFrame(self, query)
return {row[0]: row[1] for index, row in db_df.df.iterrows()}
def get_wave_records_for_recommender_as_dataframe(
self, limit: int) -> pd.DataFrame:
query = self._wave_table.get_query_for_recommender_records(limit)
db_df = DatabaseDataFrame(self, query)
return db_df.df
def is_trade_already_available_for_pattern_id(self, pattern_id,
buy_trigger: str,
strategy: str,
mean: int) -> bool:
where_clause = "Pattern_ID = '{}' and Trade_Mean_Aggregation = {} " \
"and Buy_Trigger = '{}' and Trade_Strategy = '{}'".format(
pattern_id, mean, buy_trigger, strategy
)
query = self._trade_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def is_trade_already_available(self, trade_id: str) -> bool:
query = self._trade_table.get_query_select_for_unique_record_by_id(
trade_id)
db_df = DatabaseDataFrame(self, query)
return db_df.df.shape[0] > 0
def get_trade_records_as_dataframe(self, where_clause='') -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(where_clause)
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_trade_records_for_trading_optimizer_dataframe(
self) -> pd.DataFrame:
query = self._trade_table.get_query_for_trading_optimizer()
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_trade_records_for_statistics_as_dataframe(self) -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0")
db_df = DatabaseDataFrame(self, query)
# print('query={}, found={}'.format(query, db_df.df.shape[0]))
return db_df.df
def get_trade_records_for_asset_statistics_as_dataframe(
self) -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0 and Trade_Process = 'Online'")
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0")
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_asset_records_for_statistics_as_dataframe(self) -> pd.DataFrame:
query = self._asset_table.get_query_select_for_records()
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_trade_records_for_replay_as_dataframe(self) -> pd.DataFrame:
query = self._trade_table.get_query_select_for_records(
"Trade_Result_ID != 0 AND Period = 'DAILY'")
db_df = DatabaseDataFrame(self, query)
return db_df.df
def get_pattern_id_for_trade_id(self, trade_id: str) -> str:
query = "SELECT id, pattern_id FROM trade WHERE id = '{}'".format(
trade_id)
db_df = DatabaseDataFrame(self, query)
return db_df.df.iloc[0][DC.PATTERN_ID]
def get_pattern_records_for_replay_as_dataframe(self) -> pd.DataFrame:
query = self._pattern_table.get_query_select_for_records(
"Period = 'DAILY'")
db_df = DatabaseDataFrame(self, query)
return db_df.df[db_df.df[DC.PATTERN_TYPE].isin(
FT.get_long_trade_able_types())]
def delete_existing_trade(self, trade_id: str):
if self.is_trade_already_available(trade_id):
self.delete_records(
self._trade_table.get_query_delete_by_id(trade_id))
def delete_duplicate_records(self, table: MyTable) -> int:
db_df_duplicate_id = DatabaseDataFrame(self, table.query_duplicate_id)
if db_df_duplicate_id.df.shape[0] == 0:
return 0
id_oid_keep_dict = {}
row_id_delete_list = []
db_df_id_oid = DatabaseDataFrame(self, table.query_id_oid)
key_columns = table.key_column_name_list
if len(key_columns) == 1:
duplicate_id_list = list(db_df_duplicate_id.df[key_columns])
else:
duplicate_id_list = []
for index, row in db_df_duplicate_id.df.iterrows():
record_id = '_'.join(
[str(row[column]) for column in key_columns])
duplicate_id_list.append(record_id)
for index, row in db_df_id_oid.df.iterrows():
if len(key_columns) == 1:
record_id = row[DC.ID]
else:
record_id = '_'.join(
[str(row[column]) for column in key_columns])
if record_id in duplicate_id_list:
row_id = row['rowid']
if record_id in id_oid_keep_dict:
row_id_delete_list.append(str(row_id))
else:
id_oid_keep_dict[record_id] = row_id
row_id_delete_concatenated = ','.join(row_id_delete_list)
query = 'DELETE FROM {} WHERE oid in ({});'.format(
table.name, row_id_delete_concatenated)
return self.delete_records(query)
def get_missing_trade_strategies_for_pattern_id(
self, pattern_id: str, check_against_strategy_dict: dict,
mean: int, sma_number: int) -> dict:
query = "SELECT ID, Pattern_id, Buy_Trigger, Trade_Strategy, Trade_Mean_Aggregation " \
"FROM trade where pattern_id = '{}'".format(pattern_id)
db_df = DatabaseDataFrame(self, query)
return_dict = {}
strategy_dict_exist = {}
for index, row in db_df.df.iterrows():
buy_trigger = row[DC.BUY_TRIGGER]
trade_strategy = row[DC.TRADE_STRATEGY]
mean_aggregation = row[DC.TRADE_MEAN_AGGREGATION]
is_combination_existing = False
if trade_strategy == TSTR.SMA and mean_aggregation == sma_number:
is_combination_existing = True
elif trade_strategy != TSTR.SMA and mean_aggregation == mean:
is_combination_existing = True
if is_combination_existing:
if buy_trigger not in strategy_dict_exist:
strategy_dict_exist[buy_trigger] = [trade_strategy]
else:
strategy_dict_exist[buy_trigger].append(trade_strategy)
for buy_trigger, trade_strategy_list in check_against_strategy_dict.items(
):
if buy_trigger not in strategy_dict_exist:
return_dict[buy_trigger] = trade_strategy_list
else:
exist_list = strategy_dict_exist[buy_trigger]
return_dict[buy_trigger] = [
strategy for strategy in trade_strategy_list
if strategy not in exist_list
]
return return_dict
def get_pattern_differences_to_saved_version(self,
pattern_dict: dict) -> dict:
query = self._pattern_table.get_query_select_for_unique_record_by_id(
pattern_dict[DC.ID])
db_df = DatabaseDataFrame(self, query)
df_first = db_df.df.iloc[0]
return {
key: [str(df_first[key]),
str(pattern_dict[key])]
for key, values in pattern_dict.items()
if str(df_first[key]) != str(pattern_dict[key])
}
def is_saved_pattern_version_without_ticks_previous_period(
self, pattern_id: str) -> bool:
query = self._pattern_table.get_query_select_for_unique_record_by_id(
pattern_id)
db_df = DatabaseDataFrame(self, query)
df_first = db_df.df.iloc[0]
ticks_previous_period_columns = [
DC.TICKS_PREVIOUS_PERIOD_HALF_TOP_OUT_TILL_PATTERN,
DC.TICKS_PREVIOUS_PERIOD_FULL_TOP_OUT_TILL_PATTERN,
DC.TICKS_PREVIOUS_PERIOD_HALF_BOTTOM_OUT_TILL_PATTERN,
DC.TICKS_PREVIOUS_PERIOD_FULL_BOTTOM_OUT_TILL_PATTERN
]
for ticks_columns in ticks_previous_period_columns:
if df_first[ticks_columns] != 0:
return False
return True
@staticmethod
def __get_view_by_name__(view_name: str):
return {
SVW.V_WAVE: WaveView(),
SVW.V_PATTERN: PatternView()
}.get(view_name, None)
def __get_table__(self):
return TradeTable()