def _import_quarter(src_quarter: T, dest_quarter: T):
""":param all_update, if it is True, then importing all data from
research_quarter; Otherwise, importing latest quarter record for each stock
from research_quarter"""
all_update = get_timeslot() < 0
dest_conn = get_dest_connect()
src_conn = get_dest_connect()
percent = 0
for _, order_book_id in stockcode_map().items():
percent += 1
print("{0} {1} import data finished {2:.2f}%".format(
datetime.datetime.now(), dest_quarter,
percent / len(stockcode_map()) * 100))
with MySQLDictCursorWrapper(src_conn) as src_cursor:
if all_update:
select_sql, select_params = query.fields('*').tables(
src_quarter).where(
src_quarter.stockcode == order_book_id).select()
else:
# The normal processing method is to get current max end_date
# from dest_quarter and then query all records which are larger
# than the max end_date from src_quarter. Since it is
# quarter-level data, it is enough to get latest record from
# src_quarter.
select_sql, select_params = query.fields('*').tables(
src_quarter).where(
src_quarter.stockcode == order_book_id).order_by(
src_quarter.end_date.desc()).limit(1).select()
src_cursor.execute(select_sql, select_params)
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
for record in src_cursor:
_insert_record(dest_cursor, dest_quarter, record)
src_conn.close()
dest_conn.close()
def _update_announce_date(self):
"""
update announce date. It is necessary to update announce_to for newly
quarter report in prepare_quarter
"""
src_conn = get_dest_connect()
dest_conn = get_dest_connect()
for _, order_book_id in stockcode_map().items():
select_sql, select_params = query.fields(
prepare_quarter.stockcode, prepare_quarter.end_date,
prepare_quarter.announce_to,
prepare_quarter.comcode).tables(prepare_quarter).where(
prepare_quarter.stockcode == order_book_id).order_by(
prepare_quarter.end_date.desc()).select()
with MySQLDictCursorWrapper(src_conn) as src_cursor:
src_cursor.execute(select_sql, select_params)
update_records = [
(record.get('stockcode'), record.get('end_date'),
record.get('announce_to'), record.get('comcode'))
for record in src_cursor
]
if len(update_records) == 0:
continue
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
insert_sql, insert_params = query.fields(
self._table.stockcode, self._table.end_date,
self._table.announce_to,
self._table.comcode).tables(self._table).insert(
values=update_records,
on_duplicate_key_update=OrderedDict(
((self._table.announce_to,
func.VALUES(self._table.announce_to)), )))
dest_cursor.execute(insert_sql, insert_params)
dest_conn.close()
src_conn.close()
def _remove_late_announce_records(self):
"""
remove record which is late to announce it. If one record's
announce_date is equal to or larger than that of its next latter
quarter record, then this record is so called late announcement record.
"""
dest_conn = get_dest_connect()
src_conn = get_dest_connect()
percent = 0
for _, order_book_id in stockcode_map().items():
percent += 1
print("{0} prepare_quarter remove late announce records finished "
"{1:.2f}%".format(datetime.datetime.now(),
percent / len(stockcode_map()) * 100))
select_sql, select_params = query.fields(
self._table.stockcode, self._table.end_date,
self._table.announce_date,
self._table.comcode).tables(self._table).where(
self._table.stockcode == order_book_id).order_by(
self._table.end_date.desc()).select()
with MySQLDictCursorWrapper(src_conn) as src_cursor:
src_cursor.execute(select_sql, select_params)
latest_ann_date = 29991231
last_deleted = False
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
for record in src_cursor:
ann_date = record.get("announce_date")
enddate = record.get("end_date")
if latest_ann_date <= ann_date:
delete_sql, delete_params = query.tables(
self._table
).where(
(self._table.stockcode == order_book_id)
& (self._table.end_date == enddate)).delete()
dest_cursor.execute(delete_sql, delete_params)
last_deleted = True
print(
"deleted record: stockcode = {}, end_date = {},"
" announce_date = {}".format(
order_book_id, enddate, ann_date))
else:
if last_deleted:
update_sql, update_params = query.tables(
self._table
).where(
(self._table.stockcode == order_book_id) &
(self._table.end_date == enddate)).update({
self._table.announce_to:
latest_ann_date
})
dest_cursor.execute(update_sql, update_params)
last_deleted = False
latest_ann_date = ann_date
src_conn.close()
dest_conn.close()
def _create_day(day_name: str):
with open(resource_filename("fdhandle", "sql/day.sql"), mode="rt") as f:
create_sql = f.read() % day_name
connect = get_dest_connect(False)
with MySQLDictCursorWrapper(connect) as cursor:
cursor.execute(create_sql)
connect.close()
def verify_declare(order_book_id: str):
src_conn = get_dest_connect()
select_sql, select_param = query.fields(
strategy_quarter.announce_date, strategy_quarter.announce_to,
strategy_quarter.end_date).tables(strategy_quarter).where(
strategy_quarter.stockcode == order_book_id).order_by(
strategy_quarter.end_date.desc()).select()
with MySQLDictCursorWrapper(src_conn) as src_cursor:
src_cursor.execute(select_sql, select_param)
pre_ann_date = None
for record in src_cursor:
ann_date = record.get("announce_date")
ann_to = record.get("announce_to")
end_date = record.get("end_date")
if None in (ann_date, ann_to, end_date):
raise RuntimeError(
"missing announce_date, "
"announce_to or end_date in record {}".format(record))
if pre_ann_date is None:
pre_ann_date = ann_date
continue
if ann_date >= pre_ann_date:
raise RuntimeError("announce date in old record is larger "
"than that in new record")
if ann_to != pre_ann_date:
raise RuntimeError(
"Wrong announce to in record {}, pre_ann_date {}".format(
record, pre_ann_date))
if end_date >= ann_date:
raise RuntimeError(
"announce date < end_date in record {}".format(record))
pre_ann_date = ann_date
src_conn.close()
def _exec_update(self, update_records, duplicate_update=True):
dest_conn = get_dest_connect()
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
for record in update_records:
update_record = self._clear_record(record)
if not update_record or len(update_record) == 0:
continue
_insert_record(dest_cursor, self._table, update_record,
duplicate_update)
dest_conn.close()
def _fill_announce_date(self):
"""
handle record whose announcement date or declare date was missing.
you can refer to the case: http://jira.ricequant.com/browse/ENG-2442
to get more detail requirements of handling this kind of records.
"""
dest_conn = get_dest_connect()
src_conn = get_dest_connect()
for _, order_book_id in stockcode_map().items():
print(datetime.datetime.now(),
"adjust announce date for {}".format(order_book_id))
select_sql, select_params = query.fields(
self._table.stockcode, self._table.end_date,
self._table.comcode, self._table.announce_date,
self._table.rpt_quarter,
self._table.rpt_year).tables(self._table).where(
self._table.stockcode == order_book_id).order_by(
self._table.end_date.desc()).select()
with MySQLDictCursorWrapper(src_conn) as src_cursor:
src_cursor.execute(select_sql, select_params)
adjust_announce_date = AnnounceDateAdjustement(src_cursor)
values = adjust_announce_date.values()
if len(values) != 0:
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
insert_sql, insert_params = query.fields(
self._table.stockcode, self._table.comcode,
self._table.end_date, self._table.announce_date,
self._table.announce_to).tables(
self._table).insert(
values=values,
on_duplicate_key_update=OrderedDict(
((self._table.announce_date,
func.VALUES(
self._table.announce_date)),
(self._table.announce_to,
func.VALUES(
self._table.announce_to)))))
dest_cursor.execute(insert_sql, insert_params)
src_conn.close()
dest_conn.close()
def _remove_null_rptsrc(self):
"""
quarter research table consists of four tables (income_statement,
balance_sheet, cash_flow, financial indicator), however financial
indicator of genius has no rpt_src field. if rpt_src is null in one of
records in quarter research, it means this record only comes from
financial indicator not other three tables and it is invalid, so we
delete it
"""
dest_conn = get_dest_connect()
delete_sql, param = query.tables(
self._table).where(self._table.rpt_src == None).delete()
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
dest_cursor.execute(delete_sql, param)
dest_conn.close()
def _latest_date(self, table):
dest_conn = get_dest_connect()
with MySQLDictCursorWrapper(dest_conn) as dest_curosr:
dest_curosr.execute(
*query.fields(
table.tradedate
).tables(
table
).where(
(table.stockcode == self._order_book_id)
).order_by(
table.tradedate
).limit(1).select()
)
ret = dest_curosr.fetchone()
dest_conn.close()
return ret.get('tradedate') if ret is not None else None
def recal(self, first):
closing_prices = self.get_closing_prices()
latest_date = None if first else self._latest_date(orig_day)
day_metrics = self.get_day_metrics(latest_date)
dest_conn = get_dest_connect()
with MySQLDictCursorWrapper(dest_conn) as dest_cursor:
for record in day_metrics:
record['stockcode'] = self._order_book_id
orig_record = self._clear_record(record)
dest_cursor.execute(
*query.tables(orig_day).insert(orig_record)
)
tradedate = record.get('tradedate')
trading_date = int(to_datetime(tradedate).strftime('%Y%m%d'))
quarter_metrics = self._quarter_obj.get(trading_date)
self.pe_ratio(record, quarter_metrics)
self.pcf_ratio(record, quarter_metrics)
self.pcf_ratio_1(record, quarter_metrics)
self.ps_ratio(record, quarter_metrics)
self.pe_ratio_2(record, quarter_metrics)
self.ev(record, quarter_metrics)
self.ev2(record, quarter_metrics)
self.ev_to_ebit(record, quarter_metrics)
self.pe_ratio_1(record, quarter_metrics)
self.peg_ratio(record, quarter_metrics, trading_date)
self.pcf_ratio_3(record, quarter_metrics)
self.pcf_ratio_2(record, quarter_metrics)
self.pb_ratio(record, quarter_metrics,
closing_prices.get(tradedate))
# remove None value in non-recalculation metrics since None
# value can not store it into mongodb.
self.market_cap(record)
self.market_cap_2(record)
self.a_share_market_val(record)
self.a_share_market_val_2(record)
self.val_of_stk_right(record)
self.dividend_yield(record)
record['tradedate'] = trading_date
dest_cursor.execute(
*query.tables(recal_day).insert(record)
)
dest_conn.close()
def _quarter_metrics(order_book_id: str) -> List[Dict]:
"""
get quarter metrics from strategy_quarter since this quarter table has
filled the missing announce date and removed late announce date records.
Note: Announce date in quarter record is very important for recalculating
day-level fundamental metrics which are based on it.
:param order_book_id: string like "000001.XSHE"
:return: quarter metrics of this stock and result is in end_date descending
order
"""
select_sql, select_param = query.fields(
strategy_quarter.announce_date,
strategy_quarter.rpt_year,
strategy_quarter.rpt_quarter,
strategy_quarter.end_date,
# metrics
strategy_quarter.net_profit_parent_company,
strategy_quarter.net_profit,
strategy_quarter.operating_revenue,
strategy_quarter.cash_flow_from_operating_activities,
strategy_quarter.current_assets,
strategy_quarter.cash,
strategy_quarter.cash_equivalent,
strategy_quarter.interest_bearing_debt,
strategy_quarter.ebitda,
strategy_quarter.revenue,
strategy_quarter.cash_equivalent_inc_net,
strategy_quarter.book_value_per_share
).tables(strategy_quarter).where(
strategy_quarter.stockcode == order_book_id
).order_by(
strategy_quarter.end_date.desc()
).select()
src_conn = get_dest_connect()
with MySQLDictCursorWrapper(src_conn) as cursor:
cursor.execute(select_sql, select_param)
ret = cursor.fetchall()
src_conn.close()
return ret