def record(self, entity, start, end, size, timestamps, http_session): jq_code = code_map_jq.get(entity.code) q = jq_query(finance.STK_MT_TOTAL).filter( finance.STK_MT_TOTAL.exchange_code == jq_code, finance.STK_MT_TOTAL.date >= to_time_str(start)).limit(2000) df = finance.run_query(q) json_results = [] for item in df.to_dict(orient='records'): result = { 'provider': self.provider.value, 'timestamp': item['date'], 'name': entity.name, 'margin_value': item['fin_value'], 'margin_buy': item['fin_buy_value'], 'short_value': item['sec_value'], 'short_volume': item['sec_sell_volume'], 'total_value': item['fin_sec_value'] } json_results.append(result) if len(json_results) < 100: self.one_shot = True return json_results
def record(self, entity, start, end, size, timestamps, http_session): jq_code = code_map_jq.get(entity.code) q = jq_query(finance.STK_EXCHANGE_TRADE_INFO).filter( finance.STK_EXCHANGE_TRADE_INFO.exchange_code == jq_code, finance.STK_EXCHANGE_TRADE_INFO.date >= to_time_str(start)).limit(2000) df = finance.run_query(q) json_results = [] for item in df.to_dict(orient='records'): result = { 'provider': self.provider.value, 'timestamp': item['date'], 'name': entity.name, 'pe': item['pe_average'], 'total_value': multiple_number(item['total_market_cap'], 100000000), 'total_tradable_vaule': multiple_number(item['circulating_market_cap'], 100000000), 'volume': multiple_number(item['volume'], 10000), 'turnover': multiple_number(item['money'], 100000000), 'turnover_rate': item['turnover_ratio'] } json_results.append(result) if len(json_results) < 100: self.one_shot = True return json_results
def record(self, entity, start, end, size, timestamps): for timestamp in timestamps: q = jq_query(finance.STK_HK_HOLD_INFO).filter( finance.STK_HK_HOLD_INFO.link_id == entity.code, finance.STK_HK_HOLD_INFO.day == to_time_str(timestamp)) df = finance.run_query(q) # print(df) if pd_is_not_null(df): df.rename(columns={ 'day': 'timestamp', 'link_id': 'holder_code', 'link_name': 'holder_name' }, inplace=True) df['timestamp'] = pd.to_datetime(df['timestamp']) df['entity_id'] = df['code'].apply(lambda x: to_entity_id( entity_type=EntityType.Stock, jq_code=x)) df['code'] = df['code'].apply(lambda x: x.split('.')[0]) # id格式为:{holder_name}_{entity_id}_{timestamp} df['id'] = df[['holder_name', 'entity_id', 'timestamp']].apply( lambda se: "{}_{}_{}".format( se['holder_name'], se['entity_id'], to_time_str(se['timestamp'], fmt=TIME_FORMAT_DAY)), axis=1) df_to_db(df=df, region=Region.CHN, data_schema=self.data_schema, provider=self.provider, force_update=self.force_update)
def record(self, entity, start, end, size, timestamps, http_session): q = jq_query(finance.FUND_PORTFOLIO_STOCK).filter(finance.FUND_PORTFOLIO_STOCK.pub_date >= start).filter( finance.FUND_PORTFOLIO_STOCK.code == entity.code) df = finance.run_query(q) if pd_is_not_null(df): # id code period_start period_end pub_date report_type_id report_type rank symbol name shares market_cap proportion # 0 8640569 159919 2018-07-01 2018-09-30 2018-10-26 403003 第三季度 1 601318 中国平安 19869239.0 1.361043e+09 7.09 # 1 8640570 159919 2018-07-01 2018-09-30 2018-10-26 403003 第三季度 2 600519 贵州茅台 921670.0 6.728191e+08 3.50 # 2 8640571 159919 2018-07-01 2018-09-30 2018-10-26 403003 第三季度 3 600036 招商银行 18918815.0 5.806184e+08 3.02 # 3 8640572 159919 2018-07-01 2018-09-30 2018-10-26 403003 第三季度 4 601166 兴业银行 22862332.0 3.646542e+08 1.90 df['timestamp'] = pd.to_datetime(df['pub_date']) df.rename(columns={'symbol': 'stock_code', 'name': 'stock_name'}, inplace=True) df['proportion'] = df['proportion'] * 0.01 df = portfolio_relate_stock(df, entity) df['stock_id'] = df['stock_code'].apply(lambda x: china_stock_code_to_id(x)) df['id'] = df[['entity_id', 'stock_id', 'pub_date', 'id']].apply(lambda x: '_'.join(x.astype(str)), axis=1) df['report_date'] = pd.to_datetime(df['period_end']) df['report_period'] = df['report_type'].apply(lambda x: jq_to_report_period(x)) df_to_db(df=df, region=Region.CHN, data_schema=self.data_schema, provider=self.provider, force_update=self.force_update) # self.logger.info(df.tail()) self.logger.info(f"persist etf {entity.code} portfolio success") return None
def record(self, entity, start, end, size, timestamps, http_session): q = jq_query( valuation ).filter( valuation.code == to_jq_entity_id(entity) ) count: pd.Timedelta = now_pd_timestamp(Region.CHN) - start df = jq_get_fundamentals_continuously(q, end_date=now_time_str(Region.CHN), count=count.days + 1, panel=False) df['entity_id'] = entity.id df['timestamp'] = pd.to_datetime(df['day']) df['code'] = entity.code df['name'] = entity.name df['id'] = df['timestamp'].apply(lambda x: "{}_{}".format(entity.id, to_time_str(x))) df = df.rename({'pe_ratio_lyr': 'pe', 'pe_ratio': 'pe_ttm', 'pb_ratio': 'pb', 'ps_ratio': 'ps', 'pcf_ratio': 'pcf'}, axis='columns') df['market_cap'] = df['market_cap'] * 100000000 df['circulating_market_cap'] = df['circulating_market_cap'] * 100000000 df['capitalization'] = df['capitalization'] * 10000 df['circulating_cap'] = df['circulating_cap'] * 10000 df['turnover_ratio'] = df['turnover_ratio'] * 0.01 df_to_db(df=df, region=Region.CHN, data_schema=self.data_schema, provider=self.provider, force_update=self.force_update) return None
def fill_timestamp_with_jq(self, security_item, the_data): # get report published date from jq try: q = jq_query(indicator.pubDate).filter( indicator.code == to_jq_entity_id(security_item), ) df = jq_get_fundamentals(q, statDate=to_jq_report_period( the_data.report_date)) if pd_is_not_null(df) and pd.isna(df).empty: the_data.timestamp = to_pd_timestamp(df['pubDate'][0]) self.logger.info( 'jq fill {} {} timestamp:{} for report_date:{}'.format( self.data_schema, security_item.id, the_data.timestamp, the_data.report_date)) self.session.commit() except Exception as e: self.logger.error(e)
def record(self, entity, start, end, size, timestamps, http_session): q = jq_query(finance.STK_ML_QUOTA).filter( finance.STK_ML_QUOTA.link_id == entity.code, finance.STK_ML_QUOTA.day >= to_time_str(start)).limit(2000) df = finance.run_query(q) # print(df) json_results = [] for item in df.to_dict(orient='records'): result = { 'provider': self.provider.value, 'timestamp': item['day'], 'name': entity.name, 'buy_amount': multiple_number(item['buy_amount'], 100000000), 'buy_volume': item['buy_volume'], 'sell_amount': multiple_number(item['sell_amount'], 100000000), 'sell_volume': item['sell_volume'], 'quota_daily': multiple_number(item['quota_daily'], 100000000), 'quota_daily_balance': multiple_number(item['quota_daily_balance'], 100000000) } json_results.append(result) if len(json_results) < 100: self.one_shot = True return json_results