def __execute_bulk(stmt: str, models: Any = None):
LogUtils.debug('START')
try:
LogUtils.debug(stmt)
conn = mysql.connector.connect(host=app_config.DB_HOST,
user=app_config.DB_USER_NAME,
passwd=app_config.DB_PASSWORD,
database=app_config.DB_SCHEMA)
cur = conn.cursor()
if models:
LogUtils.debug('{} records.'.format(len(models)))
cur.executemany(stmt, models)
else:
cur.execute(stmt)
conn.commit()
except Exception as e:
LogUtils.error(e)
finally:
if cur:
cur.close()
if conn:
conn.close()
LogUtils.debug('END')
def import_prices(self, limit: int = 100) -> int:
results: Dict = {
'stock_prices': [],
'etf_prices': [],
'missing_symbols': [],
'errors': []
}
try:
symbol_masters: List[SymbolMaster] = self.__stock_service.get_symbols(instrument='', exclude_status=[AppConsts.SYMBOL_STATUS_ARCHIVED])
symbols: List[str] = [s.symbol for s in symbol_masters]
price_set: BarSet = self.__alpaca_client.get_prices(symbols, limit)
if not price_set:
raise NotFoundException('BarSet', 'symbols', '')
for symbol in symbol_masters:
LogUtils.debug('start {0}'.format(symbol.symbol))
prices: Bars = price_set[symbol.symbol]
if not prices:
LogUtils.warning('{0} price not found.'.format(symbol))
results['missing_symbols'].append(symbol.symbol)
continue
for price in prices:
price_date: datetime = price.t.to_pydatetime()
data: tuple = (
symbol.id,
price_date,
NumberUtils.to_float(price.o),
NumberUtils.to_float(price.h),
NumberUtils.to_float(price.l),
NumberUtils.to_float(price.c),
NumberUtils.to_int(price.v),
)
if symbol.instrument == AppConsts.INSTRUMENT_STOCK:
org: StockPriceDaily = self.__stock_service.get_single_stock_price_daily(symbol.id, price_date)
if not org:
results['stock_prices'].append(data)
else:
org: EtfPriceDaily = self.__stock_service.get_single_etf_price_daily(symbol.id, price_date)
if not org:
results['etf_prices'].append(data)
if results['stock_prices']:
BaseService._insert_bulk(StockPriceDaily, results['stock_prices'])
if results['etf_prices']:
BaseService._insert_bulk(EtfPriceDaily, results['etf_prices'])
except NotFoundException as ex:
results['errors'].append(ex)
except Exception as ex:
results['errors'].append(ex)
finally:
self.__email_client.send_html(
subject=AppConsts.EMAIL_SUBJECT_IMPORT_PRICES,
template_path=AppConsts.TEMPLATE_PATH_IMPORT_PRICES,
model=results)
if results['errors']:
for error in results['errors']:
LogUtils.error('Import Price Error', error)
return 1
def cancel_order(self, order_id: str) -> None:
try:
LogUtils.debug('Submit request = {0}'.format(order_id))
self.__api.cancel_order(order_id)
except Exception as ex:
LogUtils.error(
'Submit Error for {0}'.format(StringUtils.to_json(order)), ex)
raise ex
def get_all_suggestions(self) -> int:
error: Exception = None
try:
accnt: Account = self.get_account()
capital: float = NumberUtils.to_floor(
NumberUtils.to_float(accnt._raw['buying_power']) /
2) # 2 to trade everyday
# Double Bottoms
req: TradeSuggestionsRequest = TradeSuggestionsRequest()
req.is_job = True
req.current_capital = capital
req.pct_risk_per_trade = 2.5
req.volume_limit = 0.01
req.test_limit_symbol = 800
req.adv_min = AppConsts.ADV_MIN_DFLT
req.adpv_min = AppConsts.ADPV_MIN_DFLT
req.strategy_type = AppConsts.STRATEGY_DOUBLE_BOTTOMS
req.strategy_request = {}
req.strategy_request['exponential_smoothing_alpha'] = 0.8
req.strategy_request['exponential_smoothing_max_min_diff'] = 0.7
req.strategy_request['double_bottoms_diff'] = 1
LogUtils.debug(StringUtils.to_json(req))
self.get_suggestions(req)
# Double Tops
req: TradeSuggestionsRequest = TradeSuggestionsRequest()
req.is_job = True
req.current_capital = capital
req.pct_risk_per_trade = 2.5
req.volume_limit = 0.01
req.test_limit_symbol = 800
req.adv_min = AppConsts.ADV_MIN_DFLT
req.adpv_min = AppConsts.ADPV_MIN_DFLT
req.strategy_type = AppConsts.STRATEGY_DOUBLE_TOPS
req.strategy_request = {}
req.strategy_request['exponential_smoothing_alpha'] = 0.8
req.strategy_request['exponential_smoothing_max_min_diff'] = 0.7
req.strategy_request['double_tops_diff'] = 1
LogUtils.debug(StringUtils.to_json(req))
self.get_suggestions(req)
except Exception as ex:
error = ex
finally:
self.__email_client.send_html(
subject=AppConsts.EMAIL_SUBJECT_GET_SUGGESTIONS,
template_path=AppConsts.TEMPLATE_PATH_GET_SUGGESTIONS,
model={'errors': [error] if error else []})
if error:
LogUtils.error('Get Suggestions Error', error)
return 1
def close_position(self, symbol: str) -> Order:
ret: Order = None
try:
LogUtils.debug('Close Position for = {0}'.format(symbol))
ret = self.__api.close_position(symbol)
LogUtils.debug('Close Position response = {0}'.format(ret))
except Exception as ex:
LogUtils.error('Close Position Error for {0}'.format(symbol), ex)
raise ex
return ret
def handle(ex: Exception) -> str:
response: ResponseModel = ResponseModel()
try:
LogUtils.error('Handle error', ex)
msg: str = str(ex)
response.http_status_code = http.HTTPStatus.INTERNAL_SERVER_ERROR
response.error_message = msg if msg else 'Error'
if isinstance(ex, BadRequestException):
response.http_status_code = http.HTTPStatus.BAD_REQUEST
if isinstance(ex, NotFoundException):
response.http_status_code = http.HTTPStatus.NOT_FOUND
if isinstance(ex, DbConnectionException):
response.http_status_code = http.HTTPStatus.INTERNAL_SERVER_ERROR
except Exception as e:
response.http_status_code = http.HTTPStatus.INTERNAL_SERVER_ERROR
response.error_message = 'Error'
return StringUtils.to_json(response)
def delete_old_prices(self) -> int:
error: Exception = None
try:
thirty_years_ago: datetime = datetime.now() - timedelta(days=365 *
30)
LogUtils.debug(
'Deleting stock price older than {0}'.format(thirty_years_ago))
db.session.query(SPD).filter(
SPD.price_date <= thirty_years_ago).delete()
db.session.commit()
except Exception as ex:
error = ex
finally:
self.__email_client.send_html(
subject=AppConsts.EMAIL_SUBJECT_DELETE_PRICES,
template_path=AppConsts.TEMPLATE_PATH_DELETE_PRICES,
model={'errors': [error] if error else []})
if error:
LogUtils.error('Delete Price Error', error)
return 1
def submit_order(
self,
symbol: str,
qty: int,
action: str,
order_type: str = AppConsts.ORDER_TYPE_MARKET,
time_in_force: str = AppConsts.TIME_IN_FORCE_DAY) -> Order:
ret: Order = None
try:
ret = self.__api.submit_order(symbol=symbol,
qty=qty,
side=action,
type=order_type,
time_in_force=time_in_force)
LogUtils.debug('Submit response = {0}'.format(ret))
except Exception as ex:
LogUtils.error('Submit Error for {0}'.format(symbol), ex)
raise ex
return ret
def send(self,
subject: str,
body: str,
content_type: str,
to: List[str] = app_config.DFLT_EMAIL_TO,
sender: str = app_config.GMAIL_USERNAME,
pw: str = app_config.GMAIL_PW,
server: str = app_config.GMAIL_SERVER,
port: int = app_config.GMAIL_PORT) -> None:
try:
msg: MIMEMultipart = MIMEMultipart()
msg['From'] = sender
msg['To'] = ','.join(to)
msg['Subject'] = '{0}{1}'.format(app_config.EMAIL_SUBJECT_PREFIX, subject)
msg.attach(MIMEText(body, content_type))
LogUtils.debug(msg)
with SMTP_SSL(server, port, context=ssl.create_default_context()) as server:
server.ehlo()
server.login(sender, pw)
server.sendmail(sender, to, msg.as_string())
except Exception as ex:
LogUtils.error('EMAIL ERROR!', ex)
def close_positions(self) -> int:
errors: List[Exception] = []
try:
is_tmrw_valid: bool = self.__alpaca_client.is_tmrw_valid()
if not is_tmrw_valid:
LogUtils.warning('Tmrw is not a valid trade date')
raise BadRequestException('Date',
DateUtils.to_string(date.today()))
req: GetTradeOrdersRequest = GetTradeOrdersRequest()
req.exact_status = AppConsts.ORDER_STATUS_IN_POSITION
orders: List[TradeOrderCustom] = self.get_trade_orders(req)
if not orders:
LogUtils.debug('No positions')
for order in orders:
try:
LogUtils.debug('Check position for = {0}'.format(
order.symbol_master.symbol))
spd: StockPriceDaily = self.__stock_service.get_last_single_stock_price_daily(
order.symbol_master.id)
if not spd:
LogUtils.warning('No Stock Price Found')
raise NotFoundException('SPD', 'symbol_id',
order.symbol_master.id)
LogUtils.debug('Last close price = {0}'.format(
spd.close_price))
# is_exit: bool = (spd.close_price > order.trade_order.target_price
# or spd.close_price < order.trade_order.stop_loss)
if True: # To-do: fix this to use strategy service. is_exit:
LogUtils.debug('Close position for = {0}'.format(
order.symbol_master.symbol))
resp: Order = self.__alpaca_client.submit_order(
symbol=order.symbol_master.symbol,
qty=order.trade_order.actual_qty,
action=AppConsts.ACTION_SELL
if order.trade_order.action == AppConsts.ACTION_BUY
else AppConsts.ACTION_BUY)
if resp:
org: TradeOrder = BaseService._get_by_id(
TradeOrder, order.trade_order.id)
if not org:
LogUtils.warning('Order not found.')
raise NotFoundException(
'TradeOrder', 'id', order.trade_order.id)
org.exit_alpaca_id = resp.id
org.status = AppConsts.ORDER_STATUS_SUBMITTED_EXIT
org.modified = datetime.now()
BaseService._update()
else:
raise Exception('Close Position Error.')
except Exception as ex:
LogUtils.error('Close Position Error', ex)
errors.append(ex)
except Exception as ex:
LogUtils.error('Close Position Error', ex)
errors.append(ex)
finally:
self.__email_client.send_html(
subject=AppConsts.EMAIL_SUBJECT_CLOSE_POSITIONS,
template_path=AppConsts.TEMPLATE_PATH_CLOSE_POSITIONS,
model={'errors': errors})
return 1
def sync_orders(self) -> int:
errors: List[Exception] = []
try:
req: GetTradeOrdersRequest = GetTradeOrdersRequest()
req.status = [
AppConsts.ORDER_STATUS_SUBMITTED_ENTRY,
AppConsts.ORDER_STATUS_SUBMITTED_EXIT
]
orders: List[TradeOrderCustom] = self.get_trade_orders(req)
if not orders:
LogUtils.debug('No orders submitted')
for order in orders:
try:
LogUtils.debug('Sync order for = {0}'.format(
order.symbol_master.symbol))
resp: Order = None
if order.trade_order.status == AppConsts.ORDER_STATUS_SUBMITTED_ENTRY:
resp = self.__alpaca_client.get_order(
order.trade_order.alpaca_id)
elif order.trade_order.status == AppConsts.ORDER_STATUS_SUBMITTED_EXIT:
resp = self.__alpaca_client.get_order(
order.trade_order.exit_alpaca_id)
if resp:
org: TradeOrder = BaseService._get_by_id(
TradeOrder, order.trade_order.id)
if not org:
raise NotFoundException('TradeOrder', 'id',
order.trade_order.id)
if order.trade_order.status == AppConsts.ORDER_STATUS_SUBMITTED_ENTRY \
and resp.status == AppConsts.ALPACA_ORDER_STATUS_FILLED:
org.status = AppConsts.ORDER_STATUS_IN_POSITION
org.actual_qty = NumberUtils.to_int(
resp.filled_qty)
org.actual_entry_price = NumberUtils.to_float(
resp.filled_avg_price)
org.modified = datetime.now()
BaseService._update()
elif order.trade_order.status == AppConsts.ORDER_STATUS_SUBMITTED_ENTRY \
and resp.status == AppConsts.ALPACA_ORDER_STATUS_CANCELLED:
org.status = AppConsts.ORDER_STATUS_CANCELLED_ENTRY
org.modified = datetime.now()
BaseService._update()
elif order.trade_order.status == AppConsts.ORDER_STATUS_SUBMITTED_EXIT \
and resp.status == AppConsts.ALPACA_ORDER_STATUS_FILLED:
exit_price: StockPriceDaily = self.__stock_service.get_single_stock_price_daily(
order.symbol_master.id,
DateUtils.get_date(
datetime.today().strftime('%Y-%m-%d'),
'%Y-%m-%d'))
if exit_price:
org.exit_stock_price_daily_id = exit_price.id
org.status = AppConsts.ORDER_STATUS_COMPLETED
org.actual_exit_price = NumberUtils.to_float(
resp.filled_avg_price)
org.modified = datetime.now()
BaseService._update()
elif order.trade_order.status == AppConsts.ORDER_STATUS_SUBMITTED_EXIT \
and resp.status == AppConsts.ALPACA_ORDER_STATUS_CANCELLED:
org.status = AppConsts.ORDER_STATUS_CANCELLED_EXIT
org.modified = datetime.now()
BaseService._update()
raise Exception('Exit Error = {0}'.format(
resp.status))
else:
raise Exception('Sync Status = {0}'.format(
resp.status))
else:
raise NotFoundException('Alpaca Order', 'id',
order.trade_order.alpaca_id)
except Exception as ex:
LogUtils.error('Sync Orders Error', ex)
errors.append(ex)
except Exception as ex:
LogUtils.error('Sync Orders Error', ex)
errors.append(ex)
finally:
self.__email_client.send_html(
subject=AppConsts.EMAIL_SUBJECT_SYNC_ORDERS,
template_path=AppConsts.TEMPLATE_PATH_SYNC_ORDERS,
model={'errors': errors})
return 1
def queue_positions(self) -> int:
errors: List[Exception] = []
try:
is_tmrw_valid: bool = self.__alpaca_client.is_tmrw_valid()
if not is_tmrw_valid:
LogUtils.warning('Tmrw is not a valid trade date')
raise BadRequestException('Date',
DateUtils.to_string(date.today()))
req: GetTradeOrdersRequest = GetTradeOrdersRequest()
# If Sunday, check Friday's price.
today: date = date.today()
if today.weekday() == AppConsts.WEEKDAY_IDX_SUN:
today = DateUtils.add_business_days(today, -1)
req.created = today.strftime('%Y-%m-%d')
req.exact_status = AppConsts.ORDER_STATUS_INIT
orders: List[TradeOrderCustom] = self.get_trade_orders(req)
req_to_ignore: GetTradeOrdersRequest = GetTradeOrdersRequest()
req_to_ignore.status = [
AppConsts.ORDER_STATUS_SUBMITTED_ENTRY,
AppConsts.ORDER_STATUS_IN_POSITION,
AppConsts.ORDER_STATUS_SUBMITTED_EXIT,
AppConsts.ORDER_STATUS_CANCELLED_EXIT
]
orders_to_ignore: List[TradeOrderCustom] = self.get_trade_orders(
req_to_ignore)
symbols_to_ignore: List[str] = [
o.symbol_master.symbol for o in orders_to_ignore
] if orders_to_ignore else []
LogUtils.debug('symbols_to_ignore = {0}'.format(symbols_to_ignore))
if not orders:
LogUtils.debug('No orders suggested')
shuffle(orders)
prioritized_orders: List[TradeOrderCustom] = []
for order in orders:
if order.trade_order.strategy == AppConsts.STRATEGY_DOUBLE_BOTTOMS:
prioritized_orders.append(order)
for order in orders:
if order.trade_order.strategy == AppConsts.STRATEGY_DOUBLE_TOPS:
prioritized_orders.append(order)
accnt: Account = self.__alpaca_client.get_account()
capital: float = NumberUtils.to_floor(
NumberUtils.to_float(accnt._raw['buying_power']) /
2) # 2 to trade everyday
for order in prioritized_orders:
try:
LogUtils.debug('Try symbol = {0}'.format(
order.symbol_master.symbol))
if order.symbol_master.symbol in symbols_to_ignore:
LogUtils.debug('Ignore for = {0}'.format(
order.symbol_master.symbol))
continue
cost: float = NumberUtils.to_float(
order.stock_price_daily.close_price *
order.trade_order.qty)
if cost > capital:
LogUtils.debug('Too expensive for = {0}'.format(
order.symbol_master.symbol))
continue
capital = capital - cost
resp: Order = self.__alpaca_client.submit_order(
symbol=order.symbol_master.symbol,
qty=order.trade_order.qty,
action=order.trade_order.action)
if resp:
org: TradeOrder = BaseService._get_by_id(
TradeOrder, order.trade_order.id)
if not org:
raise NotFoundException('TradeOrder', 'id',
order.trade_order.id)
org.alpaca_id = resp.id
org.status = AppConsts.ORDER_STATUS_SUBMITTED_ENTRY
org.order_type = AppConsts.ORDER_TYPE_MARKET
org.time_in_force = AppConsts.TIME_IN_FORCE_DAY
org.modified = datetime.now()
BaseService._update()
except Exception as ex:
LogUtils.error('Queue Position Error', ex)
errors.append(ex)
except Exception as ex:
LogUtils.error('Queue Position Error', ex)
errors.append(ex)
finally:
self.__email_client.send_html(
subject=AppConsts.EMAIL_SUBJECT_QUEUE_POSITIONS,
template_path=AppConsts.TEMPLATE_PATH_QUEUE_POSITIONS,
model={'errors': errors})
return 1
