def do_get_valuations(self, code):
valuations = finance.run_query(
query(finance.SW1_DAILY_VALUATION.date,
finance.SW1_DAILY_VALUATION.code,
finance.SW1_DAILY_VALUATION.pe,
finance.SW1_DAILY_VALUATION.pb,
finance.SW1_DAILY_VALUATION.average_price,
finance.SW1_DAILY_VALUATION.dividend_ratio).filter(
and_(
finance.SW1_DAILY_VALUATION.code ==
self.format_code_jq(code),
finance.SW1_DAILY_VALUATION.date > time_since)))
valuations.to_sql(Valuation.__tablename__,
ORMManager.getConnection(),
if_exists='append',
index=False)
def load_top10_data(fun=(lambda x: x), ext="all", force=False):
path = "top10_data_%s_%s.csv" % (
ext, time.strftime("%Y-%m-%d", time.localtime()))
if not force and os.path.exists(path):
return pd.read_csv(path)
else:
jqdatasdk.auth("******", "*******")
stocks = list(jqdatasdk.get_all_securities(['stock']).index)
df_list = []
for code in stocks:
query = jqdatasdk.query(finance.STK_SHAREHOLDER_TOP10).filter(
finance.STK_SHAREHOLDER_TOP10.code == code,
finance.STK_SHAREHOLDER_TOP10.pub_date > '2019-01-01')
df_list.append(fun(finance.run_query(query)))
top10_data = pd.concat(df_list, ignore_index=True)
top10_data.to_csv(path, index=False)
return top10_data
def fund_nav_daily(code: str, start=None) -> pd.DataFrame:
fields = (finance.FUND_NET_VALUE.day, finance.FUND_NET_VALUE.net_value,
finance.FUND_NET_VALUE.sum_value, finance.FUND_NET_VALUE.refactor_net_value)
# q = query(*fields).filter(finance.FUND_NET_VALUE.code == code).\
# order_by(finance.FUND_NET_VALUE.day.desc()).limit(5)
q = query(*fields).filter(finance.FUND_NET_VALUE.code == code)
if start is not None:
q = q.filter(finance.FUND_NET_VALUE.day > start)
df = finance.run_query(q)
df = df.rename({'day': '_id', 'net_value': 'nav', 'sum_value': 'cum_nav',
'refactor_net_value': 'refactor_nav'}, axis=1)
df['_id'] = pd.to_datetime(df['_id'])
if df.dtypes['nav'] != np.dtype('float64'):
df['nav'] = df['nav'].apply(lambda x: None if x is None else float(x))
if df.dtypes['cum_nav'] != np.dtype('float64'):
df['cum_nav'] = df['cum_nav'].apply(lambda x: None if x is None else float(x))
if df.dtypes['refactor_nav'] != np.dtype('float64'):
df['refactor_nav'] = df['refactor_nav'].apply(lambda x: None if x is None else float(x))
# print(df)
return df
def record(self, entity, start, end, size, timestamps):
jq_code = code_map_jq.get(entity.code)
q = 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)
print(df)
json_results = []
for item in df.to_dict(orient='records'):
result = {
'provider':
self.provider,
'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, 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 init_stock_info():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
stock_infos = []
for stock_code in stock_codes:
code = stock_code['code']
company_info = finance.run_query(
query(finance.STK_COMPANY_INFO).filter(
finance.STK_COMPANY_INFO.code == code).limit(1))
company_id = int(company_info.iloc[0]['company_id'])
full_name = company_info.iloc[0]['full_name']
short_name = company_info.iloc[0]['short_name']
register_location = company_info.iloc[0]['register_location']
office_address = company_info.iloc[0]['office_address']
register_capital = is_nan(
float(company_info.iloc[0]['register_capital']))
main_business = company_info.iloc[0]['main_business']
business_scope = company_info.iloc[0]['business_scope']
description = company_info.iloc[0]['description']
province = company_info.iloc[0]['province']
city = company_info.iloc[0]['city']
comments = company_info.iloc[0]['comments']
stock_info = (company_id, full_name, short_name, register_location,
office_address, register_capital, main_business,
business_scope, description, province, city, comments,
code)
print(stock_info)
stock_infos.append(stock_info)
update_stock_info_sql = "update security set company_id = %s, full_name = %s, short_name = %s, register_location = %s, office_address = %s," \
" register_capital = %s, main_business = %s, business_scope = %s, description = %s, province = %s, city = %s, comments = %s" \
" where code = %s"
my.update_many(update_stock_info_sql, stock_infos)
def record(self, entity, start, end, size, timestamps):
q = 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,
'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
def get_fund_data(start_date, end_date, w):
ld = get_last_date(start_date)
codes = list(w.index)
data = finance.run_query(query(finance.FUND_NET_VALUE).filter(finance.FUND_NET_VALUE.code.in_(codes),
finance.FUND_NET_VALUE.day >= ld,
finance.FUND_NET_VALUE.day <= end_date))
value = data.reset_index().pivot("day", "code", "sum_value")
# print(value)
nanlist = value.columns[value.isna().any()].tolist()
w = w.drop(nanlist)
w = w * (1 / np.sum(w))
# print(w)
codes = list(w.index)
fund_value = np.zeros(len(value))
# value['fund_value'] = [0] * len(value)
# print(value)
for code in codes:
# value[code] = value[code] / value[code][0]
fund_value += w.loc[code, 'weights'] * value[code]
fund_value = pd.DataFrame(fund_value)
fund_value.columns = ['p']
return fund_value
def record(self, entity, start, end, size, timestamps):
df = finance.run_query(query(finance.STK_SHAREHOLDERS_SHARE_CHANGE).filter(
finance.STK_SHAREHOLDERS_SHARE_CHANGE.code == to_jq_entity_id(entity)).filter(
finance.STK_SHAREHOLDERS_SHARE_CHANGE.pub_date >= to_time_str(start)))
if pd_is_not_null(df):
df.reset_index(inplace=True, drop=True)
df['name'] = entity.name
df['index_columns'] = df.index
df.rename(columns={
'pub_date': 'timestamp', # 公告日期
'end_date': 'holder_end_date', # 变动截至日期
'shareholder_name': 'holder_name', # 股东名称
'change_number': 'volume', # 变动数量
'change_ratio': 'change_pct', # 变动比例 变动数量占总股本比例(%)
'after_change_ratio': 'holding_pct', # 变动后_占总股本比例(%)
'price_ceiling': 'price', # 交易均价(元)
}, inplace=True)
df['entity_id'] = entity.id
df['timestamp'] = pd.to_datetime(df.timestamp)
df['provider'] = 'joinquant'
df['code'] = entity.code
df['holder_direction'] = df.type.replace(1, '减持').replace(0, '增持')
def generate_id(se):
return "{}_{}_{}".format(se['entity_id'], to_time_str(se['timestamp'], fmt=TIME_FORMAT_DAY), se.name)
df = pd.concat([i.reset_index(drop=True) for i in dict(list(df.groupby('timestamp'))).values()])
df.index += 1
df['id'] = df[['entity_id', 'timestamp']].apply(generate_id, axis=1)
df['holder_name'] = df['holder_name'].apply(lambda x: str(x).replace('(有限合伙)', ''))
df['holder_name'] = df['holder_name'].apply(lambda x: str(x).replace('（有限合伙）', ''))
df['holder_name'] = df['holder_name'].apply(lambda x: str(x).split('-')[0])
df_to_db(df=df, data_schema=self.data_schema, provider=self.provider, force_update=self.force_update)
return None
def init_index_price(limit=100):
industry_sql = "select code from industry where type = 'sw_l1'"
industry_codes = my.select_all(industry_sql, ())
jq.login()
index_price_list = []
for industry_code in industry_codes:
code = industry_code['code']
jq1_price_data = finance.run_query(
sdk.query(finance.SW1_DAILY_PRICE).filter(
finance.SW1_DAILY_PRICE.code == code).order_by(
finance.SW1_DAILY_PRICE.date.desc()).limit(limit))
for index in jq1_price_data.index:
index_jq1_price = jq1_price_data.iloc[index]
name = index_jq1_price['name']
code = index_jq1_price['code']
date = index_jq1_price['date'].strftime('%Y-%m-%d')
open = float(index_jq1_price['open'])
high = float(index_jq1_price['high'])
low = float(index_jq1_price['low'])
close = float(index_jq1_price['close'])
volume = float(index_jq1_price['volume'])
money = float(index_jq1_price['money'])
change_pct = float(index_jq1_price['change_pct'])
jq1_price = (name, code, date, open, high, low, close, volume,
money, change_pct)
print(jq1_price)
index_price_list.append(jq1_price)
insert_sql = "insert into index_price(name, code, date, open, high, low, close, volume, money, change_pct) values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, index_price_list)
def record(self, entity, start, end, size, timestamps):
if "swl1" not in entity.id:
return None
start = to_time_str(start)
df = finance.run_query(
query(finance.SW1_DAILY_PRICE).filter(
finance.SW1_DAILY_PRICE.code == entity.code).filter(
finance.SW1_DAILY_PRICE.date >= start).limit(size))
if pd_is_not_null(df):
df['name'] = entity.name
df.rename(columns={
'money': 'turnover',
'date': 'timestamp'
},
inplace=True)
df['entity_id'] = entity.id
df['timestamp'] = pd.to_datetime(df['timestamp'])
df['provider'] = 'joinquant'
df['level'] = '1d'
df['code'] = entity.code
def generate_kdata_id(se):
return "{}_{}".format(
se['entity_id'],
to_time_str(se['timestamp'], fmt=TIME_FORMAT_DAY))
df['id'] = df[['entity_id', 'timestamp']].apply(generate_kdata_id,
axis=1)
df_to_db(df=df,
data_schema=self.data_schema,
provider=self.provider,
force_update=self.force_update)
return None
def run(self):
for security_item in self.entities:
assert isinstance(security_item, StockDetail)
security = to_jq_entity_id(security_item)
# 基本资料
df = finance.run_query(
query(finance.STK_COMPANY_INFO).filter(
finance.STK_COMPANY_INFO.code == security))
if df.empty:
continue
concept_dict = get_concept(security, date=security_item.timestamp)
security_df = pd.DataFrame(index=[0])
security_df['profile'] = df.description.values[0]
security_df['main_business'] = df.main_business.values[0]
security_df['date_of_establishment'] = to_pd_timestamp(
df.establish_date.values[0])
security_df['register_capital'] = df.register_capital.values[0]
security_df['industries'] = ''
security_df['industries'] = security_df['industries'].apply(
lambda x: '、'.join(df[['industry_1', 'industry_2']].values.
tolist()[0]))
security_df['concept_indices'] = ''
security_df['concept_indices'] = security_df['industries'].apply(
lambda x: '、'.join([
i['concept_name']
for i in concept_dict[security]['jq_concept']
]))
security_df['area_indices'] = df.province.values[0]
df_stk = finance.run_query(
query(finance.STK_LIST).filter(
finance.STK_LIST.code == security))
if not df_stk.empty:
security_df['price'] = df_stk.book_price.values[0]
security_df['issues'] = df_stk.ipo_shares.values[0]
try:
security_df['raising_fund'] = df_stk.ipo_shares.values[
0] * df_stk.book_price.values[0]
except TypeError:
pass
security_df['timestamp'] = security_item.timestamp
security_df['id'] = security_item.id
security_df['entity_id'] = security_item.entity_id
security_df['code'] = security_item.code
security_df['entity_type'] = security_item.entity_type
security_df['exchange'] = security_item.exchange
security_df['name'] = security_item.name
security_df['list_date'] = security_item.list_date
security_df['end_date'] = security_item.end_date
df_to_db(df=security_df,
data_schema=self.data_schema,
provider=self.provider,
force_update=True)
# security_item.profile = df.description[0]
# security_item.main_business = df.main_business.values[0]
# security_item.date_of_establishment = to_pd_timestamp(df.establish_date.values[0])
# security_item.register_capital = df.register_capital.values[0]
# security_item.date_of_establishment = security_item.date_of_establishment.to_pydatetime()
# security_item.date_of_establishment = security_item.date_of_establishment.strftime('%Y-%m-%d %H:%M:%S')
# 关联行业to_pd_timestamp
# security_item.industries = df[['industry_1', 'industry_2']].values.tolist()[0]
# 关联概念
# security_item.concept_indices = [i['concept_name'] for i in concept_dict[security]['jq_concept']]
# 关联地区
# security_item.area_indices = df.province.values[0]
self.sleep()
# 发行相关
# df_stk = finance.run_query(query(finance.STK_LIST).filter(finance.STK_LIST.code == security))
# security_item.issue_pe = to_float("--")
# security_item.price = to_float(str(df_stk.book_price.values[0]))
# security_item.issues = to_float(str(df_stk.ipo_shares.values[0]))
# security_item.raising_fund = to_float(str(df_stk.ipo_shares.values[0] * df_stk.book_price.values[0]))
# security_item.net_winning_rate = pct_to_float("--")
# self.session.commit()
self.logger.info('finish recording stock meta for:{}'.format(
security_item.code))
self.sleep()
logout()
sql = "SELECT dm FROM dmb order by id "
cursor.execute(sql)
for row in cursor.fetchall():
dm_list.append(row)
for s in range(len(dm_list)):
if dm_list[s][0].startswith('6'):
t = dm_list[s][0] + '.XSHG'
else:
t = dm_list[s][0] + '.XSHE'
dm_sh_list.append(t)
for x in range(0, len(dm_sh_list)):
df = finance.run_query(
jq.query(finance.STK_XR_XD.code, finance.STK_XR_XD.a_xr_date,
finance.STK_XR_XD.dividend_ratio,
finance.STK_XR_XD.transfer_ratio).filter(
finance.STK_XR_XD.code == dm_sh_list[x]).order_by(
finance.STK_XR_XD.report_date.desc()).limit(1))
print(df)
if (df.empty):
print('empty')
pass
else:
for i in range(0, 1):
if (str(df.iloc[i]['a_xr_date']) == 'None'):
print('NONE')
pass
else:
if (int(str(df.iloc[i]['a_xr_date']).replace('-', '')) > int(
d.replace('-', ''))
or int(str(df.iloc[i]['a_xr_date']).replace(
'''
#获取十大流通股东
dm_list = []
rq_list = [
'2015-03-31', '2015-06-30', '2015-09-30', '2015-12-31', '2016-03-31',
'2016-06-30', '2016-09-30', '2016-12-31', '2017-03-31', '2017-06-30',
'2017-09-30', '2017-12-31', '2018-03-31', '2018-06-30', '2018-09-30',
'2018-12-31', '2019-03-31'
]
sql = "SELECT dm FROM dmb order by id "
cursor.execute(sql)
for row in cursor.fetchall():
dm_list.append(row)
from jqdatasdk import finance
for x in range(0, len(dm_list)):
df3 = finance.run_query(
jq.query(
finance.STK_SHAREHOLDER_FLOATING_TOP10.code,
finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_rank,
finance.STK_SHAREHOLDER_FLOATING_TOP10.share_ratio).filter(
finance.STK_SHAREHOLDER_FLOATING_TOP10.code == dm_list[x][0] +
'.XSHE',
finance.STK_SHAREHOLDER_FLOATING_TOP10.end_date ==
'2015-03-31' #03-31 #06-30 #09-30 #12-31
))
print(df3)
# 关闭连接
cursor.close()
connect.close()
def write_docx(filename, sd, risk, duration, fig1, p_per1, p_money1, fig2,
p_per2, p_money2, md2, piename, w):
document = Document()
document.styles['Normal'].font.name = u'宋体'
document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'宋体')
document.styles['Normal'].font.size = Pt(10.5)
document.styles['Normal'].font.color.rgb = RGBColor(0, 0, 0)
H = document.add_heading("", level=0)
run = H.add_run(filename + '理财与基金对比报告' + '(' + risk + ')')
run.font.name = u'Cambria'
run.font.size = Pt(18)
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'Cambria')
run.font.color.rgb = RGBColor(0, 0, 0)
today = datetime.today()
H = document.add_heading("", level=1)
run = H.add_run(today.date().__str__())
run.font.name = u'Cambria'
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'Cambria')
run.font.color.rgb = RGBColor(0, 0, 0)
H = document.add_heading("", level=1)
run = H.add_run('一、真实历史交易复现：')
run.font.name = u'Cambria'
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'Cambria')
run.font.color.rgb = RGBColor(0, 0, 0)
p = document.add_paragraph('复现周期：')
p.add_run(sd.date().__str__() + '——' + today.date().__str__())
document.add_picture(fig1, width=Inches(6.0))
p = document.add_paragraph('') #zym
p = document.add_paragraph('期间收益：' + str(round(p_per1 * 100, 2)) + '%(' +
str(round(p_money1 / 10000)) + '万元)')
p = document.add_paragraph('')
H = document.add_heading("", level=1)
run = H.add_run('二、' + risk + '组合公募基金投资对比复现')
run.font.name = u'Cambria'
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'Cambria')
run.font.color.rgb = RGBColor(0, 0, 0)
p = document.add_paragraph('持仓组合类别：' + risk + '组合')
document.add_paragraph('投资方法：每次申购均买入对应季度的' + risk + '组合，买入后每持有' +
duration + '调整一次')
p = document.add_paragraph('复现周期：')
p.add_run(sd.date().__str__() + '——' + today.date().__str__())
document.add_picture(fig2, width=Inches(6.0))
document.add_paragraph('') #zym
p = document.add_paragraph('期间收益：' + str(round(p_per2 * 100, 2)) + '%(' +
str(round(p_money2 / 10000)) + '万元)')
p.add_run(change(p_money1, p_money2))
document.add_paragraph('组合最大回撤（可能面临的最大风险）：' + str(round(md2 * 100, 2)) +
'%')
document.add_paragraph('')
H = document.add_heading("", level=1)
run = H.add_run('三、推荐基金组合')
run.font.name = u'Cambria'
run._element.rPr.rFonts.set(qn('w:eastAsia'), u'Cambria')
run.font.color.rgb = RGBColor(0, 0, 0)
document.add_paragraph('组合类别：' + risk + '公募基金组合')
year = today.year
quarter = (today.month - 1) // 3 + 1
document.add_paragraph('组合更新时间：' + str(year) + '年' + str(quarter) +
'季度（组合明细如下图所示）')
document.add_picture(piename, width=Inches(6.0))
document.add_paragraph('')
document.add_paragraph('组合明细（以100万元计算）：')
length = len(w['weights'])
# mw=w.values
for i in range(0, length):
#print(mw[i][0])
#print(mw[i][1])
name = finance.run_query(
query(finance.FUND_MAIN_INFO).filter(
finance.FUND_MAIN_INFO.main_code == w.index[i]))['name']
stri = w.index[i] + name[0] + '——' + str(
round(float(w.iloc[i][0]) * 100)) + '万'
document.add_paragraph(stri)
document.save(
os.path.join(os.path.dirname(__file__), 'result',
filename + '理财与基金对比报告' + '(' + risk + ').docx'))
def get_nav_rates(fund_nav_update=False,
fund_pool_update=False,
begin_date="2019-10-17",
end_date=date.today(),
fund_pool_fun='return'):
"""
:param fund_pool_fun:
:param fund_nav_update: 基金收益率序列是否更新
:param fund_pool_update: 基金池是否更新
:param begin_date: 序列起始日期
:param end_date: 序列截止日期
:return: 基金收益率序列
"""
global fund_nav_rates
# 提前一天的净值才可以
_end_date = end_date - timedelta(1)
# 更新标志为假，无需更新
if not fund_nav_update:
print("----------------")
print("基金净值无需更新")
print("----------------")
# 读取候选基金池 #zym
fund_nav_rates = pd.read_csv(os.path.join(
os.path.dirname(__file__), "vill", "snapshots", "fund_nav_rate_" +
fund_pool_fun + "_" + end_date.__str__() + ".csv"),
index_col=0,
header=None,
dtype=object) # 读取基金收益率数据
return fund_nav_rates
print("----------------")
print("开始更新基金净值")
print("----------------")
# 读取基金池信息
res = get_fund_pool(fund_pool_update=fund_pool_update,
update_date=end_date,
return_detail=False,
fund_pool_fun=fund_pool_fun)
data = pd.DataFrame()
#print(res, begin_date)
bd = datetime.strptime(begin_date, "%Y-%m-%d").date()
while True:
ed = bd + timedelta(10)
tmp = finance.run_query(
query(finance.FUND_NET_VALUE).filter(
finance.FUND_NET_VALUE.code.in_(res),
finance.FUND_NET_VALUE.day > bd,
finance.FUND_NET_VALUE.day <= ed))
data = pd.concat([data, tmp])
if ed >= end_date:
break
bd = ed
#print(data)
#print(ed)
# 读取候选基金净值信息
#data = c.csd(res, "ADJUSTEDNAVRATE", begin_date, _end_date.__str__(), "period=1,adjustflag=1,curtype=1,"
# "pricetype=1,order=1,market=CNSESH,"
# "Ispandas=1,RowIndex=2")
# 格式变换
#fund_nav_rates = data.reset_index().pivot("DATES", "CODES", "ADJUSTEDNAVRATE") / 100.
fund_value = data.reset_index().pivot("day", "code", "sum_value")
#print(fund_value)
fund_nav_rates = fund_value.pct_change()
#print(fund_nav_rates)
#print(fund_nav_rates)
'''
重要操作，这里将收益率变换成和沪深300相比的超额收益率，与择时标准匹配
'''
#hs300 = c.csd("000300.SH", "PCTCHANGE", begin_date, _end_date.__str__(),
# "period=1, adjustflag=1, curtype=1, pricetype=1, order=1, market=CNSESH, Ispandas=1, RowIndex=2")
#hs300 = hs300.reset_index().pivot("DATES", "CODES", "PCTCHANGE") / 100.
hs300 = get_price('000300.XSHG',
start_date=begin_date,
end_date=end_date.__str__(),
frequency='daily')
hs300_ret = hs300.close / hs300.open - 1
hs300 = pd.DataFrame(hs300_ret, index=hs300.index)
# 每一列都减去hs300当天增长率
fund_nav_rates = fund_nav_rates.sub(hs300.iloc[:, 0], axis=0)
fund_nav_rates = pd.DataFrame(fund_nav_rates)
# 净值信息进行存储 #zym
pd.DataFrame.to_csv(
pd.DataFrame(fund_nav_rates),
os.path.join(os.path.dirname(__file__), "vill", "results",
"fund_nav_rate_" + fund_pool_fun + ".csv"),
index=True)
pd.DataFrame.to_csv(pd.DataFrame(fund_nav_rates),
os.path.join(
os.path.dirname(__file__), "vill", "snapshots",
"fund_nav_rate_" + fund_pool_fun + "_" +
end_date.__str__() + ".csv"),
index=True)
# 读取基金收益率数据
# fund_nav_rates = pd.read_csv(os.getcwd() + "\\vill\\snapshots\\fund_nav_rate_" + fund_pool_fun + "_" + end_date.__str__() + ".csv", index_col=0)
print("----------------")
print("基金净值更新完毕")
print("----------------")
return fund_nav_rates
print('TB'+dm_insert_sh_list[q][:6],'资金流数据获取完成')
#获取流通股东
date_int_list=[]
sql2 = "SELECT date FROM rqb order by id "
cursor.execute(sql2)
for row in cursor.fetchall():
p=int(row[0].replace('-','').replace('\'', ''))
date_int_list.append(p)
for x in range(len(rq_list)):
ltgd_df=finance.run_query(jq.query(
finance.STK_SHAREHOLDER_FLOATING_TOP10.code,
finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_rank,
finance.STK_SHAREHOLDER_FLOATING_TOP10.share_ratio
).filter(
finance.STK_SHAREHOLDER_FLOATING_TOP10.code==dm_insert_sh_list[q],
finance.STK_SHAREHOLDER_FLOATING_TOP10.end_date==rq_list[x]
))
for z in range(len(date_new_list)):
if date_int_list[z]>rq_int_list[x] and date_int_list[z]<=rq_int_list[x+1]:
sql = " update %s set lt_1=%.2f , lt_2=%.2f ,lt_3=%.2f ,lt_4=%.2f ,lt_5=%.2f ,lt_6=%.2f ,lt_7=%.2f ,lt_8=%.2f ,lt_9=%.2f ,lt_10=%.2f where date='%s'"
try:
data=('TB'+dm_insert_list[q][0],ltgd_df.iloc[0]['share_ratio'],ltgd_df.iloc[1]['share_ratio'],ltgd_df.iloc[2]['share_ratio'],ltgd_df.iloc[3]['share_ratio'],ltgd_df.iloc[4]['share_ratio'],ltgd_df.iloc[5]['share_ratio'],ltgd_df.iloc[6]['share_ratio'],ltgd_df.iloc[7]['share_ratio'],ltgd_df.iloc[8]['share_ratio'],ltgd_df.iloc[9]['share_ratio'],date_new_list[z])
cursor.execute(sql % data)
except Exception as e:
connect.rollback() # 事务回滚
else:
connect.commit() # 事务提交
print('TB'+dm_insert_sh_list[q][:6],'流通股东数据获取完成')
def excute(self, code):
return finance.run_query(
query(finance.STK_BALANCE_SHEET_PARENT).filter(
finance.STK_BALANCE_SHEET_PARENT.code == self.format_code_jq(
code)))
def get_finace(self, code):
rtn = finance.run_query(
query(finance.STK_BALANCE_SHEET).filter(
finance.STK_BALANCE_SHEET.code == code).limit(100))
return rtn
# qianyuandianli=pandas.DataFrame(columns=['A', 'B', 'C', 'D','A', 'B', 'C', 'D','A'])
from jqdatasdk import finance
codeshuidian = [
'sh600900', 'sh600025', 'sh600886', 'sh600236', '002039', 'sh600674',
'sz000722', 'sz000883', '000791.SZ', '000993', '000601', '600868'
]
for code in codeshuidian:
codeqianyuandianli = jqdatasdk.normalize_code(code)
# print("\n*********",code,"***********************")
qianyuandianli1 = finance.run_query(
jqdatasdk.query(
finance.STK_BALANCE_SHEET.shortterm_loan,
finance.STK_BALANCE_SHEET.longterm_loan,
finance.STK_BALANCE_SHEET.non_current_liability_in_one_year,
finance.STK_BALANCE_SHEET.bonds_payable,
finance.STK_BALANCE_SHEET.total_assets,
finance.STK_BALANCE_SHEET.code, finance.STK_BALANCE_SHEET.pub_date,
finance.STK_BALANCE_SHEET.start_date,
finance.STK_BALANCE_SHEET.end_date,
finance.STK_BALANCE_SHEET.company_name).filter(
finance.STK_BALANCE_SHEET.code == codeqianyuandianli,
finance.STK_BALANCE_SHEET.pub_date >= '2010-01-01',
finance.STK_BALANCE_SHEET.report_type == 0).limit(50))
qianyuandianli1 = qianyuandianli1.fillna(0)
# qianyuandianli1.append(qianyuandianli1)
print(
(qianyuandianli1['shortterm_loan'] + qianyuandianli1['longterm_loan'] +
qianyuandianli1['non_current_liability_in_one_year'] +
qianyuandianli1['bonds_payable']) / qianyuandianli1['total_assets'],
qianyuandianli1['company_name'])
# qianyuandianli1.to_excel('C:/TEMP/Excel/loan_2010-2020_hydro_power_20200601.xlsx')
def get_International_Index(self, code, count=100):
q = jq.query(finance.GLOBAL_IDX_DAILY).filter(
finance.GLOBAL_IDX_DAILY.code == code).order_by(
finance.GLOBAL_IDX_DAILY.day.desc()).limit(count)
return finance.run_query(q)
update_market_cap(new_start_date='2014-01-01',
new_end_date='2021-06-17',
market_cap=market_cap,
close=close)
# financials pe
circulating_market_cap = pd.read_csv(
'~/Desktop/dataset/circulating_market_cap.csv',
index_col='Unnamed: 0',
date_parser=dateparse)
pe_ratio = pd.read_csv('~/Desktop/dataset/pe_ratio.csv',
index_col='Unnamed: 0',
date_parser=dateparse)
ps_ratio = pd.read_csv('~/Desktop/dataset/ps_ratio.csv',
index_col='Unnamed: 0',
date_parser=dateparse)
update_financials(new_end_date='2021-06-17',
new_start_date='2014-01-01',
cir_mc=circulating_market_cap,
pe=pe_ratio,
ps=ps_ratio)
# 所有公司名称
all_stock = finance.run_query(
query(finance.STK_COMPANY_INFO.code,
finance.STK_COMPANY_INFO.short_name).filter(
finance.STK_COMPANY_INFO.code.in_(close.columns)))
all_stock.to_excel('~/Desktop/dataset/all_stock_names.xlsx')
def get_fund_pool(fund_pool_update=False,
update_date=date.today(),
return_detail=False,
fund_pool_fun='return',
fund_filename='2015_1.txt'):
"""
1、基金代码处理，默认获取当前所有基金；或者直接给定初始基金池
2、过滤掉不公布净值的基金和规模过小的基金
3、对池中基金进行排序、过滤和评分
:param start_dates:
:param fund_pool_fun: "return": 根据区间内收益率取基金池
"drawdown1": 根据区间内最大涨幅/最大回撤取基金池
"drawdown2": 根据区间内最大涨幅和最大回撤交集取基金池
:param fund_pool_update: 是否进行基金池更新
:param update_date: 更新日期
:param return_detail: 是否返回基金池详细信息
:return: 返回基金池列表，简单信息只含代码，详细信息包含多维信息
"""
global fund_pool
# 更新标志为假，无需更新，返回默认池data\snapshots\fundpool.csv
if not fund_pool_update:
print("--------------")
print("不更新基金池")
print("--------------")
# 读取候选基金池 #zym os.getcwd()
res = pd.read_csv(os.path.join(
os.path.dirname(__file__), "vill", "snapshots", "fundpool_" +
fund_pool_fun + "_" + update_date.__str__() + ".csv"),
index_col=0,
header=None,
dtype=object)
fund_pool = list(res.index)
if return_detail:
return res
else:
return fund_pool
with open(fund_filename, 'r') as f:
codes = f.readlines()
#codes = codes.strip().split('')
codes = [code.strip() for code in codes]
if codes == ['']:
# 更新标志为真，更新基金池，保存到默认池以及快照池
print("--------------")
print("现在更新基金池")
print("--------------")
# -----------------------------------------#
# 基金代码处理，若是给定基金池，则不需处理 #
# -----------------------------------------#
# 股票型基金代码
#data = c.sector("507030", update_date.__str__())
#stock = data.Data[::2]
# 混合型基金代码
#data = c.sector("507029", update_date.__str__())
#hybrid = data.Data[::2]
# 股票型基金
s = finance.run_query(
query(finance.FUND_MAIN_INFO).filter(
finance.FUND_MAIN_INFO.underlying_asset_type_id ==
402001).limit(5000))
# 混合型基金
h = finance.run_query(
query(finance.FUND_MAIN_INFO).filter(
finance.FUND_MAIN_INFO.underlying_asset_type_id ==
402004).limit(5000))
# 黑名单，由于各种原因被排除在外的基金，如申赎费率、停止申赎、限大额等
#blacklist = "470021.OF,003362.OF,001309.OF,002166.OF,005434.OF,960000.OF,160421.OF"
#blacklist = "470021,003362,001309,002166,005434,960000,160421"
# 将字符串列表化
#blacklist = blacklist.split(',')
with open('blacklist.txt', 'r') as f:
tmp = f.readline()
blacklist = tmp.strip().split(' ')
# 所有可选基金
# fund_pool_code = stock.__add__(hybrid)
data = pd.merge(s, h, how="outer")
# 去除黑名单基金
#fund_pool_code = list(set(fund_pool_code) - set(blacklist))
data = data[~data.main_code.isin(blacklist)]
# 测试时用，减少数据流量
# fund_pool_code = fund_pool_code[0:400]
fund_pool_code = list(data.main_code)
print("候选基金个数：", fund_pool_code.__len__())
# --------------------------------------------#
# ---过滤掉不公布净值的基金和规模过小的基金---#
# --------------------------------------------#
# 读取基金池中各个基金的最新净值更新日期
# data = c.css(fund_pool_code, "NAVLATESTDATE", "EndDate=" + update_date.__str__() + ",RowIndex=1,Ispandas=1")
# print(data)
# 去掉停更基金
# tmp = data[data.iloc[:, 1] >= data.iloc[:, 1].max()] #这个语句可能有问题 201938会大于2019319，更新的日期还有可能有“”值或者None
# 1、去掉更新日期为“”的基金
#dat = data[data.iloc[:, 1] != ""]
# 2、对于更新日期为None的基金，一律设置为很早，剔除掉
#dat.iloc[:, 1].fillna('2000/01/01', inplace=True)
# 3、将字符串格式的日期转化为规范化的日期类型，这个是东方财富不专业的地方
#dat.iloc[:, 1] = dat.iloc[:, 1].apply(lambda x: datetime.strptime(x, '%Y/%m/%d'))
# 选择出最近两天有更新的基金列表
#tmp = dat[dat.iloc[:, 1] >= dat.iloc[:, 1].max()-timedelta(2)]
tmp = finance.run_query(
query(finance.FUND_NET_VALUE).filter(
finance.FUND_NET_VALUE.code.in_(fund_pool_code),
finance.FUND_NET_VALUE.day == update_date.__str__()))
# 更新基金池
# fund_pool_code = list(tmp.index)
fund_pool_code = list(tmp.code)
print("去掉停止更新基金后的候选基金个数： ", fund_pool_code.__len__())
# 过滤掉规模小于一亿的基金，流动性会受到影响
#data = c.css(fund_pool_code, "FUNDSCALE", "EndDate=" + update_date.__str__() + ",RowIndex=1,Ispandas=1")
#tmp = data[data.iloc[:, 1] >= 1e8]
data = finance.run_query(
query(finance.FUND_FIN_INDICATOR).filter(
finance.FUND_FIN_INDICATOR.code.in_(fund_pool_code)).order_by(
finance.FUND_FIN_INDICATOR.id.desc()).group_by(
finance.FUND_FIN_INDICATOR.code))
tmp = data[data.total_tna >= 1e8]
# 更新基金池
fund_pool_code = list(tmp.code)
else:
#tmp = finance.run_query(query(finance.FUND_NET_VALUE).filter(finance.FUND_NET_VALUE.code.in_(codes), finance.FUND_NET_VALUE.day == update_date.__str__()))
# 更新基金池
# fund_pool_code = list(tmp.index)
#fund_pool_code = list(tmp.code)
fund_pool_code = list(set(codes))
print("去掉停止更新基金后的候选基金个数： ", fund_pool_code.__len__())
#fund_pool_code = ['519712', '519772', '001938', '110011', '000527', '001714', '519069', '519736',
# '710002', '040008', '163402', '270002', '000194', '000191', '217003', '002865',
# '006077']
print("去掉小规模基金后的候选基金个数： ", fund_pool_code.__len__())
# ---------------------------------------- #
# -----对池中基金进行排序、过滤和评分----- #
# ---------------------------------------- #
# 选取前top_n个基金入池，至少是前半区
top_n = min(int(fund_pool_code.__len__() / 2), 800)
# 考察基金的时间区间：1,3,6,12,24个月
#interval = [30, 90, 180, 365, 730]
#interval = [30, 60, 90, 120, 180]
interval = [14, 30, 60]
previous_dates = get_previous_date(interval, update_date)
# 存放每个时间区间的基金排序结果
results = []
# store the showing times and scores for each fund
rank_times = pd.concat([
pd.Series(fund_pool_code),
pd.Series(np.zeros(len(fund_pool_code))),
pd.Series(np.zeros(len(fund_pool_code)))
],
axis=1)
# 分别给三个序列命名
rank_times.columns = ["codes", 'times', 'scores']
# 设置索引，记住复制，否则不保存！！！
rank = rank_times.set_index("codes")
# 对每个时间区间的基金进行排序
for previous_date in previous_dates:
#previous_date = previous_date.__str__()
#previous_date = update_date - timedelta(ind)
#根据区间内收益率进行排序
if fund_pool_fun == 'return':
# 区间内净值表现
#result_ori = c.css(fund_pool_code, "NAVADJRETURNP",
# "StartDate=" + previous_date.__str__() + ", EndDate=" + update_date.__str__() + ",RowIndex=1,Ispandas=1")
# 将其按照净值排序
#result_sort = result_ori.sort_values("NAVADJRETURNP", ascending=False)
# 选择前topN基金,只取收益率,注意必须去掉NaN值，因为不是所有被选出来的都有净值收益
#temp = result_sort.iloc[0:top_n, 1].dropna()
# 区间内净值表现
previous_value = finance.run_query(
query(finance.FUND_NET_VALUE).filter(
finance.FUND_NET_VALUE.code.in_(fund_pool_code),
finance.FUND_NET_VALUE.day == previous_date.__str__()))
previous_value = previous_value.set_index('code')
now_value = finance.run_query(
query(finance.FUND_NET_VALUE).filter(
finance.FUND_NET_VALUE.code.in_(fund_pool_code),
finance.FUND_NET_VALUE.day == update_date.__str__()))
now_value = now_value.set_index('code')
previous_value.fillna(1, inplace=True)
result_ori = now_value.sum_value / previous_value.sum_value
result_ori.columns = ['scores']
# 将其按照净值排序
result_sort = result_ori.sort_values(ascending=False)
# 选择前topN基金,只取收益率,注意必须去掉NaN值，因为不是所有被选出来的都有净值收益
temp = result_sort.iloc[0:top_n].dropna()
#print(result_ori, temp)
# 该时间区间的基金代码列表
result = list(temp.index)
# 将收益率值变换成序号，即大值小序号
temp = temp.rank(ascending=False)
# 列表中的每个基金出现次数加1
rank._set_value(
result, "times",
np.ones(result.__len__()) + rank.loc[result, :].times)
# 列表中每个基金累计上新的得分
#print(rank, temp)
rank._set_value(result, "scores",
temp + rank.loc[result, :].scores)
results.append(result)
elif fund_pool_fun == 'md':
data = finance.run_query(
query(finance.FUND_NET_VALUE).filter(
finance.FUND_NET_VALUE.code.in_(fund_pool_code),
finance.FUND_NET_VALUE.day >= previous_date.__str__(),
finance.FUND_NET_VALUE.day <= update_date.__str__()))
# data = pd.DataFrame()
# bd = previous_date
# ed = bd
# while ed <= update_date:
# ed = bd + timedelta(10)
# if ed >= update_date:
# ed = update_date + timedelta(1)
# tmp = finance.run_query(query(finance.FUND_NET_VALUE).filter(finance.FUND_NET_VALUE.code.in_(fund_pool_code),
# finance.FUND_NET_VALUE.day >= bd,
# finance.FUND_NET_VALUE.day < ed))
# data = pd.concat([data, tmp])
# bd = ed
fund_value = data.reset_index().pivot("day", "code", "sum_value")
#fund_nav_rates = fund_value.pct_change()
fund_value.fillna(1, inplace=True)
result_ori = pd.DataFrame(columns=('scores', ))
#result_ori.index.name = "code"
#fund_nav_rates.fillna(0, inplace=True)
#fund_nav_rates = fund_nav_rates.drop(list(fund_nav_rates.index)[0])
for code in list(fund_value.columns):
md = get_maxdrawdown(fund_value[code])
result_ori.loc[code] = md
#print(result_ori)
result_sort = result_ori.sort_values(by='scores', ascending=False)
temp = result_sort.iloc[0:top_n].dropna()
#print(result_ori, temp)
# 该时间区间的基金代码列表
result = list(temp.index)
# 将收益率值变换成序号，即大值小序号
temp = temp.rank(ascending=False)
# 列表中的每个基金出现次数加1
rank._set_value(
result, "times",
np.ones(result.__len__()) + rank.loc[result, :].times)
# 列表中每个基金累计上新的得分
#print(rank, temp)
rank._set_value(result, "scores",
temp.scores + rank.loc[result, :].scores)
#print(rank)
results.append(result)
# 按收益回撤比排序
elif fund_pool_fun == 'rmd':
data = finance.run_query(
query(finance.FUND_NET_VALUE).filter(
finance.FUND_NET_VALUE.code.in_(fund_pool_code),
finance.FUND_NET_VALUE.day >= previous_date.__str__(),
finance.FUND_NET_VALUE.day <= update_date.__str__()))
# data = pd.DataFrame()
# bd = previous_date
# ed = bd
# while ed <= update_date:
# ed = bd + timedelta(10)
# if ed >= update_date:
# ed = update_date + timedelta(1)
# tmp = finance.run_query(query(finance.FUND_NET_VALUE).filter(finance.FUND_NET_VALUE.code.in_(fund_pool_code),
# finance.FUND_NET_VALUE.day >= bd,
# finance.FUND_NET_VALUE.day < ed))
# data = pd.concat([data, tmp])
# bd = ed
fund_value = data.reset_index().pivot("day", "code", "sum_value")
#fund_nav_rates = fund_value.pct_change()
fund_value.fillna(1, inplace=True)
result_ori = pd.DataFrame(columns=('scores', ))
#result_ori.index.name = "code"
#fund_nav_rates.fillna(0, inplace=True)
#fund_nav_rates = fund_nav_rates.drop(list(fund_nav_rates.index)[0])
for code in list(fund_value.columns):
md = get_maxdrawdown(fund_value[code])
r = fund_value[code][-1] / fund_value[code][0] - 1
result_ori.loc[code] = r / md
#print(result_ori)
result_sort = result_ori.sort_values(by='scores', ascending=False)
temp = result_sort.iloc[0:top_n].dropna()
#print(result_ori, temp)
# 该时间区间的基金代码列表
result = list(temp.index)
# 将收益率值变换成序号，即大值小序号
temp = temp.rank(ascending=False)
# 列表中的每个基金出现次数加1
rank._set_value(
result, "times",
np.ones(result.__len__()) + rank.loc[result, :].times)
# 列表中每个基金累计上新的得分
#print(rank, temp)
rank._set_value(result, "scores",
temp.scores + rank.loc[result, :].scores)
#print(rank)
results.append(result)
'''
# 按照最大涨幅/最大回撤排序
result_fall = c.css(fund_pool_code, "MAXFALL",
"StartDate=" + previous_date.__str__() + ", EndDate=" + update_date.__str__() + ",RowIndex=1,Ispandas=1")
result_rise = c.css(fund_pool_code, "MAXRISE",
"StartDate=" + previous_date.__str__() + ", EndDate=" + update_date.__str__() + ",RowIndex=1,Ispandas=1")
result_fall['MAXFALL'] = -result_fall['MAXFALL']
result_fall.fillna(999)
result_fall['MAXFALL'][result_fall['MAXFALL'] == 0] = 0.01
result_ori = result_rise['MAXRISE'] / result_fall['MAXFALL']
#print(result_ori)
result_sort = result_ori.sort_values(ascending=False)
temp = result_sort.iloc[0:top_n].dropna()
'''
elif fund_pool_fun == 'drawdown2':
pass
'''
# 该时间区间的基金代码列表
result = list(temp.index)
# 将收益率值变换成序号，即大值小序号
temp = temp.rank(ascending=False)
# 列表中的每个基金出现次数加1
rank._set_value(result, "times", np.ones(result.__len__()) + rank.loc[result, :].times)
# 列表中每个基金累计上新的得分
#print(rank, temp)
rank._set_value(result, "scores", temp.scores + rank.loc[result, :].scores)
s
# 对没有排名的基金特别照顾,但必须大于半年的业绩才可特别入池
if ind > 180:
#is_na = result_sort.iloc[:, 1].isna()
is_na = result_sort.isna()
tt = list(result_sort[is_na].index)
result = result + tt
results.append(result)
#print(rank)
'''
# 基金进行过滤，每个区间列表相交
ss = list(results[0])
for ind in results:
ss = list(set(ss).intersection(set(ind)))
#print(ss)
# 计算每个基金的评分信息
res = rank.loc[ss, :]
res.scores = res.scores / res.times
res = res.sort_values("scores").iloc[:, 1]
# 存储候选池基金全部信息 #zym
res.to_csv(os.path.join(os.path.dirname(__file__), "vill", "results",
"fundpool_" + fund_pool_fun + ".csv"),
index=True,
encoding="utf_8_sig")
res.to_csv(os.path.join(
os.path.dirname(__file__), "vill", "snapshots",
"fundpool_" + fund_pool_fun + "_" + update_date.__str__() + ".csv"),
index=True,
encoding="utf_8_sig")
return res
'''
def record(self, entity, start, end, size, timestamps):
if not end:
end = to_time_str(now_pd_timestamp())
start = to_time_str(start)
em_code = to_jq_entity_id(entity)
div_columns_dict = {
"report_date": "report_date", #报告时间
"board_plan_pub_date": "announce_date", #公告日
"a_registration_date": "record_date", #股权登记日
"a_bonus_date": "dividend_date", # 除权除息日
"shareholders_plan_bonusnote": "dividend", # 方案
"announce_date_general_meeting":
"shareholders_plan_pub_date", # 股东大会公告日
"implementation_pub_date":
"announce_date_dividend_implementation", # 分红实施公告日
"b_registration_date":
"last_trading_day_b_shares", # B股最后交易日 股权登记日
"at_bonus_ratio_rmb":
"dividend_per_share_after_tax", # 每股股利(税后) 原始数据/10
"bonus_ratio_rmb":
"dividend_per_share_before_tax", # 每股股利(税前) 原始数据/10
"plan_progress": "dividend_plan_progress", # 分红方案进度
"dividend_arrival_date": "dividend_pay_date", # 派息日,红利到账日
"dividend_ratio": "share_bonus_per_share", # 每股送股比例 原始数据/10
"transfer_ratio":
"per_share_conversion_ratio", # 每股转增比例 应该 原始数据/10
}
df = finance.run_query(
query(finance.STK_XR_XD).filter(
finance.STK_XR_XD.code == em_code,
finance.STK_XR_XD.board_plan_pub_date >= start).order_by(
finance.STK_XR_XD.report_date).limit(100))
df.rename(columns=div_columns_dict, inplace=True)
df.dropna(subset=['dividend_date'], inplace=True)
if pd_is_not_null(df):
df.reset_index(drop=True, inplace=True)
df['dividend_per_share_after_tax'] = df[
'dividend_per_share_after_tax'] / 10
df['dividend_per_share_before_tax'] = df[
'dividend_per_share_before_tax'] / 10
df['share_bonus_per_share'] = df['share_bonus_per_share'] / 10
df['per_share_conversion_ratio'] = df[
'per_share_conversion_ratio'] / 10
# df['dividend'] = df['dividend'].apply(lambda x: str(x).split('（')[0])
df['entity_id'] = entity.id
df['timestamp'] = pd.to_datetime(df.report_date)
df['provider'] = 'joinquant'
df['code'] = entity.code
def generate_id(se):
return "{}_{}".format(
se['entity_id'],
to_time_str(se['timestamp'], fmt=TIME_FORMAT_DAY))
df['id'] = df[['entity_id', 'timestamp']].apply(generate_id,
axis=1)
# df.replace('None',pd.NaT,inplace=True)
df_to_db(df=df,
data_schema=self.data_schema,
provider=self.provider,
force_update=self.force_update)
return None
connect.commit() # 事务提交
print('TB' + dm_insert_sh_list[q][:6], '资金流数据获取完成')
#获取流通股东
date_int_list = []
sql2 = "SELECT date FROM rqb order by id "
cursor.execute(sql2)
for row in cursor.fetchall():
p = int(row[0].replace('-', '').replace('\'', ''))
date_int_list.append(p)
for x in range(len(rq_list)):
ltgd_df = finance.run_query(
jq.query(
finance.STK_SHAREHOLDER_FLOATING_TOP10.code,
finance.STK_SHAREHOLDER_FLOATING_TOP10.shareholder_rank,
finance.STK_SHAREHOLDER_FLOATING_TOP10.share_ratio).filter(
finance.STK_SHAREHOLDER_FLOATING_TOP10.code ==
dm_insert_sh_list[q],
finance.STK_SHAREHOLDER_FLOATING_TOP10.end_date ==
rq_list[x]))
for z in range(len(date_new_list)):
if date_int_list[z] > rq_int_list[x] and date_int_list[
z] <= rq_int_list[x + 1]:
sql = " update %s set lt_1=%.2f , lt_2=%.2f ,lt_3=%.2f ,lt_4=%.2f ,lt_5=%.2f ,lt_6=%.2f ,lt_7=%.2f ,lt_8=%.2f ,lt_9=%.2f ,lt_10=%.2f where date='%s'"
try:
data = ('TB' + dm_insert_list[q][0],
ltgd_df.iloc[0]['share_ratio'],
ltgd_df.iloc[1]['share_ratio'],
ltgd_df.iloc[2]['share_ratio'],
ltgd_df.iloc[3]['share_ratio'],
ltgd_df.iloc[4]['share_ratio'],
update_margin_buy(new_start_date='2017-03-17', new_end_date='2021-05-10', margin_df=margin_buy_value)
# 前10活跃
top_10_net_buy = pd.read_excel('/Users/caichaohong/Desktop/Zenki/南北向资金/TOP_10_net_buy.xlsx', index_col='Unnamed: 0')
top_10_net_buy = top_10_net_buy.dropna(axis=0, how='all')
raw_df = pd.read_excel('/Users/caichaohong/Desktop/Zenki/南北向资金/TOP_10_raw_data.xlsx', index_col='Unnamed: 0')
margin_buy_value = pd.read_excel('/Users/caichaohong/Desktop/Zenki/融资融券/margin_buy_value.xlsx')
margin_sell_value = pd.read_excel('/Users/caichaohong/Desktop/Zenki/融资融券/margin_sell_value.xlsx')
# 南北总资金
north_amount = finance.run_query(query(finance.STK_ML_QUOTA).filter(finance.STK_ML_QUOTA.day>='2017-03-17',
finance.STK_ML_QUOTA.link_id.in_([310001,310002])))
north_df = north_amount.groupby('day').sum()
north_df = north_df.drop(columns=['id','link_id', 'currency_id', 'quota_daily', 'quota_daily_balance'])
north_df.index = pd.to_datetime(north_df.index)
north_df = north_df[north_df.index<= '2021-05-10'] #比交易日少很多
# 所有公司名称
all_stock = finance.run_query(query(finance.STK_COMPANY_INFO.code,
finance.STK_COMPANY_INFO.short_name).filter(
finance.STK_COMPANY_INFO.code.in_(close.columns)))
all_stock.to_excel('/Users/caichaohong/Desktop/Zenki/all_stock_names.xlsx')
date_parser=dateparse)
roe_yeayly = pd.read_csv(
'/Users/caichaohong/Desktop/Zenki/financials/roe_yearly.csv',
index_col='statDate') # 2924个
pe = pd.read_csv('/Users/caichaohong/Desktop/Zenki/financials/pe_ratio.csv',
index_col='Unnamed: 0',
date_parser=dateparse) # 2924个
net_profit = pd.read_csv(
'/Users/caichaohong/Desktop/Zenki/financials/net_profit_yearly.csv',
index_col='statDate') # 2924个
market_cap = market_cap[close.columns]
pe = pe[close.columns]
#股息率
df_bank = finance.run_query(
query(finance.SW1_DAILY_VALUATION).filter(
finance.SW1_DAILY_VALUATION.code == '801780'))
# 回购
df_bond = bond.run_query(
query(bond.REPO_DAILY_PRICE).filter(
bond.REPO_DAILY_PRICE.name == 'GC182').limit(2000))
df_t1 = pd.merge(df_bond, df_bank, on='date')
df_t1 = df_t1[['date', 'close', 'dividend_ratio']]
df_t1.index = pd.to_datetime(df_t1['date'])
# 当风险偏好<0不持股
df_t1['licha'] = (df_t1['close'].rolling(60).mean() -
df_t1['dividend_ratio'].rolling(60).mean()).diff(1)
df_t1['licha'] = df_t1['licha'].fillna(method='ffill')
df_t1['hs300'] = hs300['net_value']
# 回购和价格行情日期不同
def get_balance_sheet():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
balance_sheet_list = []
for stock_code in stock_codes:
code = stock_code['code']
balance_sheet_data = finance.run_query(
sdk.query(finance.STK_BALANCE_SHEET).filter(
finance.STK_BALANCE_SHEET.code == code).filter(
finance.STK_BALANCE_SHEET.report_type == 0).order_by(
finance.STK_BALANCE_SHEET.pub_date.desc()).limit(1))
if balance_sheet_data.empty:
continue
index_balance_sheet = balance_sheet_data.iloc[0]
pub_date = index_balance_sheet['pub_date'].strftime('%Y-%m-%d')
# exist_sql = "select count(1) count from balance_sheet where code = %s and pub_date = %s"
# exist = my.select_one(exist_sql, (code, pub_date))
# if exist['count'] > 0:
# print('%s had init', code)
# continue
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', None)
pd.set_option('max_colwidth', 120)
company_name = index_balance_sheet['company_name']
start_date = index_balance_sheet['start_date'].strftime('%Y-%m-%d')
end_date = index_balance_sheet['end_date'].strftime('%Y-%m-%d')
cash_equivalents = float(
is_none(index_balance_sheet['cash_equivalents']))
trading_assets = float(is_none(index_balance_sheet['trading_assets']))
bill_receivable = float(is_none(
index_balance_sheet['bill_receivable']))
account_receivable = float(
is_none(index_balance_sheet['account_receivable']))
advance_payment = float(is_none(
index_balance_sheet['advance_payment']))
other_receivable = float(
is_none(index_balance_sheet['other_receivable']))
affiliated_company_receivable = float(
is_none(index_balance_sheet['affiliated_company_receivable']))
interest_receivable = float(
is_none(index_balance_sheet['interest_receivable']))
dividend_receivable = float(
is_none(index_balance_sheet['dividend_receivable']))
inventories = float(is_none(index_balance_sheet['inventories']))
expendable_biological_asset = float(
is_none(index_balance_sheet['expendable_biological_asset']))
non_current_asset_in_one_year = float(
is_none(index_balance_sheet['non_current_asset_in_one_year']))
total_current_assets = float(
is_none(index_balance_sheet['total_current_assets']))
hold_for_sale_assets = float(
is_none(index_balance_sheet['hold_for_sale_assets']))
hold_to_maturity_investments = float(
is_none(index_balance_sheet['hold_to_maturity_investments']))
longterm_receivable_account = float(
is_none(index_balance_sheet['longterm_receivable_account']))
longterm_equity_invest = float(
is_none(index_balance_sheet['longterm_equity_invest']))
investment_property = float(
is_none(index_balance_sheet['investment_property']))
fixed_assets = float(is_none(index_balance_sheet['fixed_assets']))
constru_in_process = float(
is_none(index_balance_sheet['constru_in_process']))
construction_materials = float(
is_none(index_balance_sheet['construction_materials']))
fixed_assets_liquidation = float(
is_none(index_balance_sheet['fixed_assets_liquidation']))
biological_assets = float(
is_none(index_balance_sheet['biological_assets']))
oil_gas_assets = float(is_none(index_balance_sheet['oil_gas_assets']))
intangible_assets = float(
is_none(index_balance_sheet['intangible_assets']))
development_expenditure = float(
is_none(index_balance_sheet['development_expenditure']))
good_will = float(is_none(index_balance_sheet['good_will']))
long_deferred_expense = float(
is_none(index_balance_sheet['long_deferred_expense']))
deferred_tax_assets = float(
is_none(index_balance_sheet['deferred_tax_assets']))
total_non_current_assets = float(
is_none(index_balance_sheet['total_non_current_assets']))
total_assets = float(is_none(index_balance_sheet['total_assets']))
shortterm_loan = float(is_none(index_balance_sheet['shortterm_loan']))
trading_liability = float(
is_none(index_balance_sheet['trading_liability']))
notes_payable = float(is_none(index_balance_sheet['notes_payable']))
accounts_payable = float(
is_none(index_balance_sheet['accounts_payable']))
advance_peceipts = float(
is_none(index_balance_sheet['advance_peceipts']))
salaries_payable = float(
is_none(index_balance_sheet['salaries_payable']))
taxs_payable = float(is_none(index_balance_sheet['taxs_payable']))
interest_payable = float(
is_none(index_balance_sheet['interest_payable']))
dividend_payable = float(
is_none(index_balance_sheet['dividend_payable']))
other_payable = float(is_none(index_balance_sheet['other_payable']))
affiliated_company_payable = float(
is_none(index_balance_sheet['affiliated_company_payable']))
non_current_liability_in_one_year = float(
is_none(index_balance_sheet['non_current_liability_in_one_year']))
total_current_liability = float(
is_none(index_balance_sheet['total_current_liability']))
longterm_loan = float(is_none(index_balance_sheet['longterm_loan']))
bonds_payable = float(is_none(index_balance_sheet['bonds_payable']))
longterm_account_payable = float(
is_none(index_balance_sheet['longterm_account_payable']))
specific_account_payable = float(
is_none(index_balance_sheet['specific_account_payable']))
estimate_liability = float(
is_none(index_balance_sheet['estimate_liability']))
deferred_tax_liability = float(
is_none(index_balance_sheet['deferred_tax_liability']))
total_non_current_liability = float(
is_none(index_balance_sheet['total_non_current_liability']))
total_liability = float(is_none(
index_balance_sheet['total_liability']))
paidin_capital = float(is_none(index_balance_sheet['paidin_capital']))
capital_reserve_fund = float(
is_none(index_balance_sheet['capital_reserve_fund']))
specific_reserves = float(
is_none(index_balance_sheet['specific_reserves']))
surplus_reserve_fund = float(
is_none(index_balance_sheet['surplus_reserve_fund']))
treasury_stock = float(is_none(index_balance_sheet['treasury_stock']))
retained_profit = float(is_none(
index_balance_sheet['retained_profit']))
equities_parent_company_owners = float(
is_none(index_balance_sheet['equities_parent_company_owners']))
minority_interests = float(
is_none(index_balance_sheet['minority_interests']))
foreign_currency_report_conv_diff = float(
is_none(index_balance_sheet['foreign_currency_report_conv_diff']))
irregular_item_adjustment = float(
is_none(index_balance_sheet['irregular_item_adjustment']))
total_owner_equities = float(
is_none(index_balance_sheet['total_owner_equities']))
total_sheet_owner_equities = float(
is_none(index_balance_sheet['total_sheet_owner_equities']))
other_comprehensive_income = float(
is_none(index_balance_sheet['other_comprehensive_income']))
deferred_earning = float(
is_none(index_balance_sheet['deferred_earning']))
settlement_provi = float(
is_none(index_balance_sheet['settlement_provi']))
lend_capital = float(is_none(index_balance_sheet['lend_capital']))
loan_and_advance_current_assets = float(
is_none(index_balance_sheet['loan_and_advance_current_assets']))
derivative_financial_asset = float(
is_none(index_balance_sheet['derivative_financial_asset']))
insurance_receivables = float(
is_none(index_balance_sheet['insurance_receivables']))
reinsurance_receivables = float(
is_none(index_balance_sheet['reinsurance_receivables']))
reinsurance_contract_reserves_receivable = float(
is_none(
index_balance_sheet['reinsurance_contract_reserves_receivable']
))
bought_sellback_assets = float(
is_none(index_balance_sheet['bought_sellback_assets']))
hold_sale_asset = float(is_none(
index_balance_sheet['hold_sale_asset']))
loan_and_advance_noncurrent_assets = float(
is_none(index_balance_sheet['loan_and_advance_noncurrent_assets']))
borrowing_from_centralbank = float(
is_none(index_balance_sheet['borrowing_from_centralbank']))
deposit_in_interbank = float(
is_none(index_balance_sheet['deposit_in_interbank']))
borrowing_capital = float(
is_none(index_balance_sheet['borrowing_capital']))
derivative_financial_liability = float(
is_none(index_balance_sheet['derivative_financial_liability']))
sold_buyback_secu_proceeds = float(
is_none(index_balance_sheet['sold_buyback_secu_proceeds']))
commission_payable = float(
is_none(index_balance_sheet['commission_payable']))
reinsurance_payables = float(
is_none(index_balance_sheet['reinsurance_payables']))
insurance_contract_reserves = float(
is_none(index_balance_sheet['insurance_contract_reserves']))
proxy_secu_proceeds = float(
is_none(index_balance_sheet['proxy_secu_proceeds']))
receivings_from_vicariously_sold_securities = float(
is_none(index_balance_sheet[
'receivings_from_vicariously_sold_securities']))
hold_sale_liability = float(
is_none(index_balance_sheet['hold_sale_liability']))
estimate_liability_current = float(
is_none(index_balance_sheet['estimate_liability_current']))
deferred_earning_current = float(
is_none(index_balance_sheet['deferred_earning_current']))
preferred_shares_noncurrent = float(
is_none(index_balance_sheet['preferred_shares_noncurrent']))
pepertual_liability_noncurrent = float(
is_none(index_balance_sheet['pepertual_liability_noncurrent']))
longterm_salaries_payable = float(
is_none(index_balance_sheet['longterm_salaries_payable']))
other_equity_tools = float(
is_none(index_balance_sheet['other_equity_tools']))
preferred_shares_equity = float(
is_none(index_balance_sheet['preferred_shares_equity']))
pepertual_liability_equity = float(
is_none(index_balance_sheet['pepertual_liability_equity']))
balance_sheet = (
code, company_name, pub_date, start_date, end_date,
cash_equivalents, trading_assets, bill_receivable,
account_receivable, advance_payment, other_receivable,
affiliated_company_receivable, interest_receivable,
dividend_receivable, inventories, expendable_biological_asset,
non_current_asset_in_one_year, total_current_assets,
hold_for_sale_assets, hold_to_maturity_investments,
longterm_receivable_account, longterm_equity_invest,
investment_property, fixed_assets, constru_in_process,
construction_materials, fixed_assets_liquidation,
biological_assets, oil_gas_assets, intangible_assets,
development_expenditure, good_will, long_deferred_expense,
deferred_tax_assets, total_non_current_assets, total_assets,
shortterm_loan, trading_liability, notes_payable, accounts_payable,
advance_peceipts, salaries_payable, taxs_payable, interest_payable,
dividend_payable, other_payable, affiliated_company_payable,
non_current_liability_in_one_year, total_current_liability,
longterm_loan, bonds_payable, longterm_account_payable,
specific_account_payable, estimate_liability,
deferred_tax_liability, total_non_current_liability,
total_liability, paidin_capital, capital_reserve_fund,
specific_reserves, surplus_reserve_fund, treasury_stock,
retained_profit, equities_parent_company_owners,
minority_interests, foreign_currency_report_conv_diff,
irregular_item_adjustment, total_owner_equities,
total_sheet_owner_equities, other_comprehensive_income,
deferred_earning, settlement_provi, lend_capital,
loan_and_advance_current_assets, derivative_financial_asset,
insurance_receivables, reinsurance_receivables,
reinsurance_contract_reserves_receivable, bought_sellback_assets,
hold_sale_asset, loan_and_advance_noncurrent_assets,
borrowing_from_centralbank, deposit_in_interbank,
borrowing_capital, derivative_financial_liability,
sold_buyback_secu_proceeds, commission_payable,
reinsurance_payables, insurance_contract_reserves,
proxy_secu_proceeds, receivings_from_vicariously_sold_securities,
hold_sale_liability, estimate_liability_current,
deferred_earning_current, preferred_shares_noncurrent,
pepertual_liability_noncurrent, longterm_salaries_payable,
other_equity_tools, preferred_shares_equity,
pepertual_liability_equity)
print(balance_sheet)
balance_sheet_list.append(balance_sheet)
insert_sql = "insert into balance_sheet(code, company_name, pub_date, start_date, end_date, cash_equivalents, trading_assets, bill_receivable," \
" account_receivable, advance_payment, other_receivable," \
" affiliated_company_receivable, interest_receivable, dividend_receivable, inventories," \
" expendable_biological_asset, non_current_asset_in_one_year, total_current_assets, hold_for_sale_assets," \
" hold_to_maturity_investments, longterm_receivable_account, longterm_equity_invest, investment_property," \
" fixed_assets, constru_in_process, construction_materials," \
" fixed_assets_liquidation, biological_assets, oil_gas_assets, intangible_assets, development_expenditure," \
" good_will, long_deferred_expense, deferred_tax_assets, total_non_current_assets," \
" total_assets, shortterm_loan, trading_liability, notes_payable, accounts_payable, advance_peceipts," \
" salaries_payable, taxs_payable, interest_payable, dividend_payable," \
" other_payable, affiliated_company_payable, non_current_liability_in_one_year, total_current_liability," \
" longterm_loan, bonds_payable, longterm_account_payable, specific_account_payable," \
" estimate_liability, deferred_tax_liability, total_non_current_liability, total_liability, paidin_capital," \
" capital_reserve_fund, specific_reserves, surplus_reserve_fund, treasury_stock," \
" retained_profit, equities_parent_company_owners, minority_interests, foreign_currency_report_conv_diff," \
" irregular_item_adjustment, total_owner_equities, total_sheet_owner_equities," \
" other_comprehensive_income, deferred_earning, settlement_provi, lend_capital," \
" loan_and_advance_current_assets," \
" derivative_financial_asset, insurance_receivables, reinsurance_receivables," \
" reinsurance_contract_reserves_receivable, bought_sellback_assets, hold_sale_asset," \
" loan_and_advance_noncurrent_assets, borrowing_from_centralbank, deposit_in_interbank, borrowing_capital," \
" derivative_financial_liability, sold_buyback_secu_proceeds, commission_payable, reinsurance_payables," \
" insurance_contract_reserves, proxy_secu_proceeds, receivings_from_vicariously_sold_securities," \
" hold_sale_liability, estimate_liability_current, deferred_earning_current, preferred_shares_noncurrent," \
" pepertual_liability_noncurrent, longterm_salaries_payable, other_equity_tools," \
" preferred_shares_equity, pepertual_liability_equity)" \
" values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
" %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, balance_sheet_list)
def get_cash_flow():
stocks_sql = "select code from security"
stock_codes = my.select_all(stocks_sql, ())
jq.login()
cash_flow_list = []
for stock_code in stock_codes:
code = stock_code['code']
cash_flow_data = finance.run_query(
sdk.query(finance.STK_CASHFLOW_STATEMENT).
filter(finance.STK_CASHFLOW_STATEMENT.code == code).filter(
finance.STK_CASHFLOW_STATEMENT.report_type == 0).order_by(
finance.STK_CASHFLOW_STATEMENT.pub_date.desc()).limit(1))
if cash_flow_data.empty:
continue
index_cash_flow = cash_flow_data.iloc[0]
pub_date = index_cash_flow['pub_date'].strftime('%Y-%m-%d')
exist_sql = "select count(1) count from cash_flow where code = %s and pub_date = %s"
exist = my.select_one(exist_sql, (code, pub_date))
if exist['count'] > 0:
print('%s had init', code)
continue
company_name = index_cash_flow['company_name']
start_date = index_cash_flow['start_date'].strftime('%Y-%m-%d')
end_date = index_cash_flow['end_date'].strftime('%Y-%m-%d')
goods_sale_and_service_render_cash = float(
is_none(index_cash_flow['goods_sale_and_service_render_cash']))
tax_levy_refund = float(is_none(index_cash_flow['tax_levy_refund']))
subtotal_operate_cash_inflow = float(
is_none(index_cash_flow['subtotal_operate_cash_inflow']))
goods_and_services_cash_paid = float(
is_none(index_cash_flow['goods_and_services_cash_paid']))
staff_behalf_paid = float(is_none(
index_cash_flow['staff_behalf_paid']))
tax_payments = float(is_none(index_cash_flow['tax_payments']))
subtotal_operate_cash_outflow = float(
is_none(index_cash_flow['subtotal_operate_cash_outflow']))
net_operate_cash_flow = float(
is_none(index_cash_flow['net_operate_cash_flow']))
invest_withdrawal_cash = float(
is_none(index_cash_flow['invest_withdrawal_cash']))
invest_proceeds = float(is_none(index_cash_flow['invest_proceeds']))
fix_intan_other_asset_dispo_cash = float(
is_none(index_cash_flow['fix_intan_other_asset_dispo_cash']))
net_cash_deal_subcompany = float(
is_none(index_cash_flow['net_cash_deal_subcompany']))
subtotal_invest_cash_inflow = float(
is_none(index_cash_flow['subtotal_invest_cash_inflow']))
fix_intan_other_asset_acqui_cash = float(
is_none(index_cash_flow['fix_intan_other_asset_acqui_cash']))
invest_cash_paid = float(is_none(index_cash_flow['invest_cash_paid']))
impawned_loan_net_increase = float(
is_none(index_cash_flow['impawned_loan_net_increase']))
net_cash_from_sub_company = float(
is_none(index_cash_flow['net_cash_from_sub_company']))
subtotal_invest_cash_outflow = float(
is_none(index_cash_flow['subtotal_invest_cash_outflow']))
net_invest_cash_flow = float(
is_none(index_cash_flow['net_invest_cash_flow']))
cash_from_invest = float(is_none(index_cash_flow['cash_from_invest']))
cash_from_borrowing = float(
is_none(index_cash_flow['cash_from_borrowing']))
cash_from_bonds_issue = float(
is_none(index_cash_flow['cash_from_bonds_issue']))
subtotal_finance_cash_inflow = float(
is_none(index_cash_flow['subtotal_finance_cash_inflow']))
borrowing_repayment = float(
is_none(index_cash_flow['borrowing_repayment']))
dividend_interest_payment = float(
is_none(index_cash_flow['dividend_interest_payment']))
subtotal_finance_cash_outflow = float(
is_none(index_cash_flow['subtotal_finance_cash_outflow']))
net_finance_cash_flow = float(
is_none(index_cash_flow['net_finance_cash_flow']))
exchange_rate_change_effect = float(
is_none(index_cash_flow['exchange_rate_change_effect']))
other_reason_effect_cash = float(
is_none(index_cash_flow['other_reason_effect_cash']))
cash_equivalent_increase = float(
is_none(index_cash_flow['cash_equivalent_increase']))
cash_equivalents_at_beginning = float(
is_none(index_cash_flow['cash_equivalents_at_beginning']))
cash_and_equivalents_at_end = float(
is_none(index_cash_flow['cash_and_equivalents_at_end']))
net_profit = float(is_none(index_cash_flow['net_profit']))
assets_depreciation_reserves = float(
is_none(index_cash_flow['assets_depreciation_reserves']))
fixed_assets_depreciation = float(
is_none(index_cash_flow['fixed_assets_depreciation']))
intangible_assets_amortization = float(
is_none(index_cash_flow['intangible_assets_amortization']))
defferred_expense_amortization = float(
is_none(index_cash_flow['defferred_expense_amortization']))
fix_intan_other_asset_dispo_loss = float(
is_none(index_cash_flow['fix_intan_other_asset_dispo_loss']))
fixed_asset_scrap_loss = float(
is_none(index_cash_flow['fixed_asset_scrap_loss']))
fair_value_change_loss = float(
is_none(index_cash_flow['fair_value_change_loss']))
financial_cost = float(is_none(index_cash_flow['financial_cost']))
invest_loss = float(is_none(index_cash_flow['invest_loss']))
deffered_tax_asset_decrease = float(
is_none(index_cash_flow['deffered_tax_asset_decrease']))
deffered_tax_liability_increase = float(
is_none(index_cash_flow['deffered_tax_liability_increase']))
inventory_decrease = float(
is_none(index_cash_flow['inventory_decrease']))
operate_receivables_decrease = float(
is_none(index_cash_flow['operate_receivables_decrease']))
operate_payable_increase = float(
is_none(index_cash_flow['operate_payable_increase']))
others = float(is_none(index_cash_flow['others']))
net_operate_cash_flow_indirect = float(
is_none(index_cash_flow['net_operate_cash_flow_indirect']))
debt_to_capital = float(is_none(index_cash_flow['debt_to_capital']))
cbs_expiring_in_one_year = float(
is_none(index_cash_flow['cbs_expiring_in_one_year']))
financial_lease_fixed_assets = float(
is_none(index_cash_flow['financial_lease_fixed_assets']))
cash_at_end = float(is_none(index_cash_flow['cash_at_end']))
cash_at_beginning = float(is_none(
index_cash_flow['cash_at_beginning']))
equivalents_at_end = float(
is_none(index_cash_flow['equivalents_at_end']))
equivalents_at_beginning = float(
is_none(index_cash_flow['equivalents_at_beginning']))
other_reason_effect_cash_indirect = float(
is_none(index_cash_flow['other_reason_effect_cash_indirect']))
cash_equivalent_increase_indirect = float(
is_none(index_cash_flow['cash_equivalent_increase_indirect']))
net_deposit_increase = float(
is_none(index_cash_flow['net_deposit_increase']))
net_borrowing_from_central_bank = float(
is_none(index_cash_flow['net_borrowing_from_central_bank']))
net_borrowing_from_finance_co = float(
is_none(index_cash_flow['net_borrowing_from_finance_co']))
net_original_insurance_cash = float(
is_none(index_cash_flow['net_original_insurance_cash']))
net_cash_received_from_reinsurance_business = float(
is_none(
index_cash_flow['net_cash_received_from_reinsurance_business'])
)
net_insurer_deposit_investment = float(
is_none(index_cash_flow['net_insurer_deposit_investment']))
net_deal_trading_assets = float(
is_none(index_cash_flow['net_deal_trading_assets']))
interest_and_commission_cashin = float(
is_none(index_cash_flow['interest_and_commission_cashin']))
net_increase_in_placements = float(
is_none(index_cash_flow['net_increase_in_placements']))
net_buyback = float(is_none(index_cash_flow['net_buyback']))
net_loan_and_advance_increase = float(
is_none(index_cash_flow['net_loan_and_advance_increase']))
net_deposit_in_cb_and_ib = float(
is_none(index_cash_flow['net_deposit_in_cb_and_ib']))
original_compensation_paid = float(
is_none(index_cash_flow['original_compensation_paid']))
handling_charges_and_commission = float(
is_none(index_cash_flow['handling_charges_and_commission']))
policy_dividend_cash_paid = float(
is_none(index_cash_flow['policy_dividend_cash_paid']))
cash_from_mino_s_invest_sub = float(
is_none(index_cash_flow['cash_from_mino_s_invest_sub']))
proceeds_from_sub_to_mino_s = float(
is_none(index_cash_flow['proceeds_from_sub_to_mino_s']))
investment_property_depreciation = float(
is_none(index_cash_flow['investment_property_depreciation']))
# pd.set_option('display.max_columns', None)
# pd.set_option('display.max_rows', None)
# pd.set_option('max_colwidth', 100)
cash_flow = (
code, company_name, pub_date, start_date, end_date,
goods_sale_and_service_render_cash, tax_levy_refund,
subtotal_operate_cash_inflow, goods_and_services_cash_paid,
staff_behalf_paid, tax_payments, subtotal_operate_cash_outflow,
net_operate_cash_flow, invest_withdrawal_cash, invest_proceeds,
fix_intan_other_asset_dispo_cash, net_cash_deal_subcompany,
subtotal_invest_cash_inflow, fix_intan_other_asset_acqui_cash,
invest_cash_paid, impawned_loan_net_increase,
net_cash_from_sub_company, subtotal_invest_cash_outflow,
net_invest_cash_flow, cash_from_invest, cash_from_borrowing,
cash_from_bonds_issue, subtotal_finance_cash_inflow,
borrowing_repayment, dividend_interest_payment,
subtotal_finance_cash_outflow, net_finance_cash_flow,
exchange_rate_change_effect, other_reason_effect_cash,
cash_equivalent_increase, cash_equivalents_at_beginning,
cash_and_equivalents_at_end, net_profit,
assets_depreciation_reserves, fixed_assets_depreciation,
intangible_assets_amortization, defferred_expense_amortization,
fix_intan_other_asset_dispo_loss, fixed_asset_scrap_loss,
fair_value_change_loss, financial_cost, invest_loss,
deffered_tax_asset_decrease, deffered_tax_liability_increase,
inventory_decrease, operate_receivables_decrease,
operate_payable_increase, others, net_operate_cash_flow_indirect,
debt_to_capital, cbs_expiring_in_one_year,
financial_lease_fixed_assets, cash_at_end, cash_at_beginning,
equivalents_at_end, equivalents_at_beginning,
other_reason_effect_cash_indirect,
cash_equivalent_increase_indirect, net_deposit_increase,
net_borrowing_from_central_bank, net_borrowing_from_finance_co,
net_original_insurance_cash,
net_cash_received_from_reinsurance_business,
net_insurer_deposit_investment, net_deal_trading_assets,
interest_and_commission_cashin, net_increase_in_placements,
net_buyback, net_loan_and_advance_increase,
net_deposit_in_cb_and_ib, original_compensation_paid,
handling_charges_and_commission, policy_dividend_cash_paid,
cash_from_mino_s_invest_sub, proceeds_from_sub_to_mino_s,
investment_property_depreciation)
print(cash_flow)
cash_flow_list.append(cash_flow)
insert_sql = "insert into cash_flow(code, company_name, pub_date, start_date, end_date," \
" goods_sale_and_service_render_cash, tax_levy_refund," \
" subtotal_operate_cash_inflow, goods_and_services_cash_paid," \
" staff_behalf_paid, tax_payments, subtotal_operate_cash_outflow," \
" net_operate_cash_flow, invest_withdrawal_cash, invest_proceeds," \
" fix_intan_other_asset_dispo_cash, net_cash_deal_subcompany," \
" subtotal_invest_cash_inflow, fix_intan_other_asset_acqui_cash," \
" invest_cash_paid, impawned_loan_net_increase," \
" net_cash_from_sub_company, subtotal_invest_cash_outflow," \
" net_invest_cash_flow, cash_from_invest, cash_from_borrowing," \
" cash_from_bonds_issue, subtotal_finance_cash_inflow," \
" borrowing_repayment, dividend_interest_payment," \
" subtotal_finance_cash_outflow, net_finance_cash_flow," \
" exchange_rate_change_effect, other_reason_effect_cash," \
" cash_equivalent_increase, cash_equivalents_at_beginning," \
" cash_and_equivalents_at_end, net_profit," \
" assets_depreciation_reserves, fixed_assets_depreciation," \
" intangible_assets_amortization, defferred_expense_amortization," \
" fix_intan_other_asset_dispo_loss, fixed_asset_scrap_loss," \
" fair_value_change_loss, financial_cost, invest_loss," \
" deffered_tax_asset_decrease, deffered_tax_liability_increase," \
" inventory_decrease, operate_receivables_decrease," \
" operate_payable_increase, others, net_operate_cash_flow_indirect," \
" debt_to_capital, cbs_expiring_in_one_year," \
" financial_lease_fixed_assets, cash_at_end, cash_at_beginning," \
" equivalents_at_end, equivalents_at_beginning," \
" other_reason_effect_cash_indirect," \
" cash_equivalent_increase_indirect, net_deposit_increase," \
" net_borrowing_from_central_bank, net_borrowing_from_finance_co," \
" net_original_insurance_cash," \
" net_cash_received_from_reinsurance_business," \
" net_insurer_deposit_investment, net_deal_trading_assets," \
" interest_and_commission_cashin, net_increase_in_placements," \
" net_buyback, net_loan_and_advance_increase," \
" net_deposit_in_cb_and_ib, original_compensation_paid," \
" handling_charges_and_commission, policy_dividend_cash_paid," \
" cash_from_mino_s_invest_sub, proceeds_from_sub_to_mino_s," \
" investment_property_depreciation) " \
"values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, " \
"%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
my.insert_many(insert_sql, cash_flow_list)
def do_get_capital(self, code):
return finance.run_query(
query(finance.STK_CAPITAL_CHANGE.pub_date,
finance.STK_CAPITAL_CHANGE.share_total).filter(
finance.STK_CAPITAL_CHANGE.code == self.format_code_jq(
code)))