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