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