def get_finance_indicator(code_list, date):
# https://www.joinquant.com/help/api/help#Stock:%E8%B4%A2%E5%8A%A1%E6%8C%87%E6%A0%87%E6%95%B0%E6%8D%AE
fields = [
"eps", "adjusted_profit", "operating_profit", "value_change_profit",
"roe", "inc_return", "roa", "net_profit_margin", "gross_profit_margin",
"expense_to_total_revenue", "operation_profit_to_total_revenue",
"net_profit_to_total_revenue", "operating_expense_to_total_revenue",
"ga_expense_to_total_revenue", "financing_expense_to_total_revenue",
"operating_profit_to_profit", "invesment_profit_to_profit",
"adjusted_profit_to_profit", "goods_sale_and_service_to_revenue",
"ocf_to_revenue", "ocf_to_operating_profit",
"inc_total_revenue_year_on_year", "inc_total_revenue_annual",
"inc_revenue_year_on_year", "inc_revenue_annual",
"inc_operation_profit_year_on_year", "inc_operation_profit_annual",
"inc_net_profit_year_on_year", "inc_net_profit_annual",
"inc_net_profit_to_shareholders_year_on_year",
"inc_net_profit_to_shareholders_annual"
]
table = jq.indicator
cond = jq.indicator.code.in_(code_list)
order = jq.indicator.code.asc()
query = jq.query(table).filter(cond).order_by(order)
data_df = jq.get_fundamentals(query, date=date)
if data_df.empty:
return pd.DataFrame(
{field: []
for field in ["security"] + fields + ["date"]})
# 字段重命名
result_df = data_df.rename(columns={"code": "security", "day": "date"})
# 调整字段顺序
result_df = result_df[["security"] + fields + ["date"]]
return result_df
def get_peb(index, date=None, table=False):
"""
获取指数在指定日期的 pe 和 pb。采用当时各公司的最新财报和当时的指数成分股权重加权计算。
:param index: str. 聚宽形式的指数代码。
:param date: str. %Y-%m-%d
:param table: Optioanl[bool], default False. True 时返回整个计算的 DataFrame，用于 debug。
:return: Dict[str, float]. 包含 pe 和 pb 值的字典。
"""
middle = dt.datetime.strptime(
date.replace("/", "").replace("-", ""), "%Y%m%d"
).replace(day=1)
iwdf = get_index_weight_range(
index,
start=(middle - dt.timedelta(days=5)).strftime("%Y-%m-%d"),
end=(middle + dt.timedelta(days=5)).strftime("%Y-%m-%d"),
)
q = query(valuation).filter(valuation.code.in_(list(iwdf.code)))
# df = get_fundamentals(q, date)
df = get_fundamentals(q, date=date)
df = df.merge(iwdf, on="code")
df["e"] = df["weight"] / df["pe_ratio"]
df["b"] = df["weight"] / df["pb_ratio"]
df["p"] = df["weight"]
tote = df.e.sum()
totb = df.b.sum()
if table:
return df
return {"pe": 100.0 / tote, "pb": 100.0 / totb}
def init_stock():
jq.login()
# 查询财务数据
data = sdk.get_fundamentals(sdk.query(sdk.valuation), '2020-10-30')
sql = "insert into security(code, market_cap, circulating_market_cap, pe_ratio, pb_ratio, ps_ratio, pcf_ratio, type) values (%s, %s, %s, %s, %s, %s, %s, %s)"
args = []
for i in data.index:
code = data.iloc[i]['code']
market_cap = is_nan(float(data.iloc[i]['market_cap']))
if market_cap < 100:
continue
circulating_market_cap = is_nan(
float(data.iloc[i]['circulating_market_cap']))
pe_ratio = is_nan(float(data.iloc[i]['pe_ratio']))
pb_ratio = is_nan(float(data.iloc[i]['pb_ratio']))
ps_ratio = is_nan(float(data.iloc[i]['ps_ratio']))
pcf_ratio = is_nan(float(data.iloc[i]['pcf_ratio']))
arg = (code, market_cap, circulating_market_cap, pe_ratio, pb_ratio,
ps_ratio, pcf_ratio, 'stock')
print(arg)
args.append(arg)
my.insert_many(sql, args)
del_st()
def get_stock_list(cur_date='2018-06-26',
begin_date='2018-01-01',
MARKET_MIN_CAP=100,
MARKET_MAX_CAP=500):
"""
获取从指定日期开始的，市值在指定区间的，非st，股票列表, 以及详细信息（code，circulating_cap，circulating_market_cap）
"""
# 总市值在100-500亿
q = jq.query(jq.valuation.code, jq.valuation.circulating_cap,
jq.valuation.circulating_market_cap).filter(
jq.valuation.code.notin_(['002473.XSHE',
'000407.XSHE']), # why?
jq.valuation.circulating_market_cap < MARKET_MAX_CAP,
jq.valuation.circulating_market_cap >= MARKET_MIN_CAP)
df = jq.get_fundamentals(q, date=begin_date)
df.index = list(df['code'])
# 去除st
st = jq.get_extras('is_st',
list(df['code']),
start_date=cur_date,
end_date=cur_date,
df=True)
st = st.iloc[0]
stock_list = list(st[st == False].index)
return stock_list, df
def get_indus_stk_df(stk_list, year):
return jqdatasdk.get_fundamentals(
query(valuation, income).filter(
# 这里不能使用 in 操作, 要使用in_()函数
valuation.code.in_(stk_list)),
statDate=year)
def get_annual_balancesheet(sec_code, statYYYY):
q = jq.query(jq.balance).filter(jq.balance.code == sec_code, )
ret = jq.get_fundamentals(q, statDate=statYYYY)
if ret is None or len(ret) == 0:
print "WARN: %s 于 %s 的资产表没查到 " % (sec_code, statYYYY)
return ret
def get_fundamentals_at_dt(order_book_ids, fields, dt):
query_object = create_a_query_object(order_book_ids=order_book_ids,
fields=fields)
data_df = get_fundamentals(query_object, date=dt)
# create multiindex
data_df.set_index(['code'], inplace=True)
data_df.rename_axis(index={"code": "order_book_id"}, inplace=True)
return data_df
def get_annual_indicator(sec_code, statYYYY):
q = jq.query(jq.indicator).filter(jq.indicator.code == sec_code, )
ret = jq.get_fundamentals(q, statDate=statYYYY)
if ret is None or len(ret) == 0:
print "WARN: %s 于 %s 的财务指标没查到 " % (sec_code, statYYYY)
return ret
def get_annual_cashflow(sec_code, statYYYY):
q = jq.query(jq.cash_flow).filter(jq.cash_flow.code == sec_code, )
ret = jq.get_fundamentals(q, statDate=statYYYY)
if ret is None or len(ret) == 0:
print "WARN: %s 于 %s 的现金流表没查到 " % (sec_code, statYYYY)
return ret
def update_market_cap(new_start_date, new_end_date, market_cap, close):
# share 是持股数df，换成其他df也行，用来检测股票数量是否相等
future_trade_days = get_trade_days(start_date=market_cap.index[-1],
end_date=new_end_date)[1:] # 第一天重复
old_trade_days = get_trade_days(
start_date=new_start_date, end_date=market_cap.index[0])[:-1] # 最后一天重复
new_trade_days = list(future_trade_days) + list(old_trade_days)
if len(new_trade_days) > 0:
for date in new_trade_days:
market_cap.loc[date] = np.nan
for date in tqdm(new_trade_days):
df = get_fundamentals(query(valuation.code,
valuation.market_cap).filter(
valuation.code.in_(
list(market_cap.columns))),
date=date)
market_cap.loc[date][df['code']] = df['market_cap'].values
else:
print("No need to Update")
market_cap.index = pd.to_datetime(market_cap.index)
# close 是持股数，用来检测股票数量是否相等, 新加入股票补齐
new_stocks = list(set(close.columns).difference(set(market_cap.columns)))
if len(new_stocks) > 0:
print('total number of new stocks = {}'.format(len(new_stocks)))
for s in new_stocks:
market_cap[s] = np.nan
for date in tqdm(list(market_cap.index)):
df = get_fundamentals(query(valuation.code,
valuation.market_cap).filter(
valuation.code.in_(new_stocks)),
date=datetime.date(date))
# get_fundamentals 必须是 date格式的日期
market_cap.loc[date][df['code']] = df['market_cap'].values
market_cap = market_cap.sort_index(axis=0) # 按index排序
market_cap = market_cap.sort_index(axis=1) # 按股票代码排序
market_cap = market_cap.dropna(how='all', axis=0)
market_cap.to_csv(
'/Users/caichaohong/Desktop/Zenki/financials/market_cap.csv')
def mt_save_financial_from_JQData(stk, start_date, end_date):
'''
从jqdata中获取财务数据指标indicator
可以用一个dict来保存已增加通用性
:param start_date:开始日期
:param end_date:结束日期
:return:获取的值
'''
queryDict = {'indicator': indicator, # 财务指标数据
'finance.STK_FIN_FORCAST': finance.STK_FIN_FORCAST, # 业绩预告
'finance.STK_INCOME_STATEMENT': finance.STK_INCOME_STATEMENT, # 合并利润表
'finance.STK_INCOME_STATEMENT_PARENT': finance.STK_INCOME_STATEMENT_PARENT, # 母公司利润表
'finance.STK_CASHFLOW_STATEMENT': finance.STK_CASHFLOW_STATEMENT, # 合并现金流表
'finance.STK_CASHFLOW_STATEMENT_PARENT': finance.STK_CASHFLOW_STATEMENT_PARENT, # 母公司现金流表
'finance.STK_BALANCE_SHEET': finance.STK_BALANCE_SHEET, # 合并资产表
'finance.STK_BALANCE_SHEET_PARENT': finance.STK_BALANCE_SHEET_PARENT, # 母公司资产表
}
if stk not in queryDict.keys():
return
mydb = myClient['stockFinanceDbJQData']
myCollection = mydb[stk]
try:
# 获取已有数据的datetimes
ref_ = myCollection.distinct('datetime')
except:
ref_ = []
q = query(queryDict[stk])
df = pd.DataFrame()
start_dt = datetime.datetime.strptime(start_date, '%Y-%m-%d')
end_dt = datetime.datetime.strptime(end_date, '%Y-%m-%d')
delta = relativedelta(months=3)
while start_dt < end_dt:
quarter_para = "{0}q{1}".format(start_dt.year, start_dt.month // 3 + 1)
if quarter_para in ref_:
pass
else:
df = get_fundamentals(q, statDate=quarter_para)
df["datetime"] = quarter_para
df.rename(columns={"statDate.1" : "statDate1"}, inplace=True)
df["code"] = df["code"].apply(lambda x: x.replace('.XSHE', '.SZ'))
df["code"] = df["code"].apply(lambda x: x.replace('.XSHG', '.SH'))
if df.empty is not True:
_save2mongodb(myCollection, df)
print("更新日期：{0}",quarter_para)
start_dt += delta
def get_Factor(self, date, factor_name):
try:
self.securityData = jqd.get_fundamentals(
jqd.query(jqd.valuation.code, factor_name).filter(
#jqd.valuation.code == self.securityIDCode
jqd.valuation.code.in_(self.allSecurityIndex)),
date) #2019-01-01'
except:
self._add_log(cl.msgInvalidSecurityIDCode)
return self.securityData
def record(self, entity, start, end, size, timestamps):
# different with the default timestamps handling
param = self.generate_request_param(entity, start, end, size,
timestamps)
param = sorted(list(set([i[:4] for i in param])))
q = self.generate_path_fields(entity)
df = pd.DataFrame()
for years_val in param:
rets = pd.concat([
get_fundamentals(q, statDate=f'{years_val}q' + str(i))
for i in range(1, 5)
])
df = df.append(rets)
if df.empty:
return None
# 财报时间 公告时间
df.rename(columns={
'statDate': "report_date",
'pubDate': "pub_date",
},
inplace=True)
df.set_index(['report_date', 'pub_date'], drop=True, inplace=True)
map_data = {
value[0]: key
for key, value in self.get_data_map().items()
}
df.rename(columns=map_data, inplace=True)
df.reset_index(drop=False, inplace=True)
df['report_date'] = pd.to_datetime(df['report_date'])
df['report_period'] = df['report_date'].apply(
lambda x: to_report_period_type(x))
# df['report_period'] = df['report_date'].apply(lambda x: get_recent_report_date(x))
df['pub_date'] = pd.to_datetime(df['pub_date'])
df['timestamp'] = df['report_date']
df['entity_id'] = entity.id
df['provider'] = 'joinquant'
df['code'] = entity.code
def generate_finance_id(se):
return "{}_{}".format(
se['entity_id'],
to_time_str(se['timestamp'], fmt=TIME_FORMAT_DAY))
df['id'] = df[['entity_id', 'timestamp']].apply(generate_finance_id,
axis=1)
# df = df.drop_duplicates(subset=['id'], keep='last')
df_to_db(df=df,
data_schema=self.data_schema,
provider=self.provider,
force_update=self.force_update)
return None
def maintask():
a=pd.DataFrame()
j=1
for i in indexs:
print('正在获取第%d家，股票代码%s.' % (j, i))
j+=1
q = jq.query(jq.valuation).filter(jq.valuation.code == i)
df = jq.get_fundamentals(q, '2019-05-08')
a=a.append(df)
a.to_csv("C:/Users/Administrator/Desktop/估值20190508.csv")
print(df)
def wechat():
signature = request.args.get("signature", "")
timestamp = request.args.get("timestamp", "")
nonce = request.args.get("nonce", "")
encrypt_type = request.args.get("encrypt_type", "raw")
msg_signature = request.args.get("msg_signature", "")
try:
check_signature(TOKEN, signature, timestamp, nonce)
except InvalidSignatureException:
abort(403)
if request.method == "GET":
echo_str = request.args.get("echostr", "")
return echo_str
# POST request
if encrypt_type == "raw":
# plaintext mode
msg = parse_message(request.data)
if msg.type == "text":
# reply = create_reply(msg.content, msg)
str0 = msg.content;
if str0[0] == '6':
str0 += ".XSHG"
else:
str0 += ".XSHE"
myq = query(jqdatasdk.valuation).filter(jqdatasdk.valuation.code == str0)
df = jqdatasdk.get_fundamentals(myq, '2020-9-28')
str1 = df['pe_ratio']
str2 = df['pb_ratio']
str4 = '%s ' % str1
str5 = '%s' % str2
str6 = str4 + str5
reply = create_reply(str6, msg)
else:
reply = create_reply("Sorry, can not handle this for now", msg)
return reply.render()
else:
# encryption mode
from wechatpy.crypto import WeChatCrypto
crypto = WeChatCrypto(TOKEN, AES_KEY, APPID)
try:
msg = crypto.decrypt_message(request.data, msg_signature, timestamp, nonce)
except (InvalidSignatureException, InvalidAppIdException):
abort(403)
else:
msg = parse_message(msg)
if msg.type == "text":
reply = create_reply(msg.content, msg)
else:
reply = create_reply("Sorry, can not handle this for now", msg)
return crypto.encrypt_message(reply.render(), nonce, timestamp)
def get_data_by_date(self, index_code, date):
stock_codes = jqdatasdk.get_index_stocks(index_code)
trade_day = jqdatasdk.get_trade_days(end_date=date, count=1)[0]
q = jqdatasdk.query(jqdatasdk.valuation.code, jqdatasdk.valuation.day,
jqdatasdk.valuation.pe_ratio_lyr,
jqdatasdk.valuation.pe_ratio,
jqdatasdk.valuation.pb_ratio,
jqdatasdk.valuation.market_cap,
jqdatasdk.valuation.circulating_market_cap).filter(
jqdatasdk.valuation.code.in_(stock_codes))
df = jqdatasdk.get_fundamentals(q, date=trade_day)
return df
def get_factor_data1(factor,stock, date):
if factor in val:
q = jd.query(jd.valuation).filter(jd.valuation.code.in_(stock))
df = jd.get_fundamentals(q, date)
elif factor in bal:
q = jd.query(jd.balance).filter(jd.balance.code.in_(stock))
df = jd.get_fundamentals(q, date)
elif factor in cf:
q = jd.query(jd.cash_flow).filter(jd.cash_flow.code.in_(stock))
df = jd.get_fundamentals(q, date)
elif factor in inc:
q = jd.query(jd.income).filter(jd.income.code.in_(stock))
df = jd.get_fundamentals(q, date)
elif factor in ind:
q = jd.query(jd.indicator).filter(jd.indicator.code.in_(stock))
df = jd.get_fundamentals(q, date)
df.index = df['code']
data = pd.DataFrame(index = df.index)
data[date] = df[factor] #date是函数的参数，转置索引=列名，使得date(时间)成为索引
return data.T
def fill_timestamp_with_jq(self, security_item, the_data):
# get report published date from jq
try:
q = query(indicator.pubDate).filter(
indicator.code == to_jq_entity_id(security_item), )
df = get_fundamentals(q,
statDate=to_jq_report_period(
the_data.report_date))
if not df.empty and pd.isna(df).empty:
the_data.timestamp = to_pd_timestamp(df['pubDate'][0])
self.session.commit()
except Exception as e:
self.logger.error(e)
def get_stock_list(begin_date=None, MARKET_MIN_CAP=100, MARKET_MAX_CAP=500):
"""
获取从指定日期开始的，市值在指定市值区间的股票列表, 以及详细信息（code，circulating_cap，circulating_market_cap）
"""
# 总市值在100-500亿
q = jq.query(jq.valuation.code, jq.valuation.circulating_cap,
jq.valuation.circulating_market_cap).filter(
jq.valuation.circulating_market_cap < MARKET_MAX_CAP,
jq.valuation.circulating_market_cap >= MARKET_MIN_CAP)
df = jq.get_fundamentals(q, date=begin_date)
df.index = list(df['code'])
return df
def fill_timestamp_with_jq(self, security_item, the_data):
# get report published date from jq
q = query(indicator.pubDate).filter(
indicator.code == to_jq_entity_id(security_item), )
df = get_fundamentals(q,
statDate=to_jq_report_period(
the_data.report_date))
if not 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()
def update_financials(new_start_date, new_end_date, cir_mc, pe, ps):
# share 是持股数df，换成其他df也行，用来检测股票数量是否相等
future_trade_days = get_trade_days(start_date=pe.index[-1],
end_date=new_end_date)[1:] # 第一天重复
old_trade_days = get_trade_days(start_date=new_start_date,
end_date=pe.index[0])[:-1] # 最后一天重复
new_trade_days = list(future_trade_days) + list(old_trade_days)
if len(new_trade_days) > 0:
for date in new_trade_days:
cir_mc.loc[date] = np.nan
pe.loc[date] = np.nan
ps.loc[date] = np.nan
for date in tqdm(new_trade_days):
df = get_fundamentals(
query(valuation.code, valuation.circulating_market_cap,
valuation.pe_ratio, valuation.ps_ratio).filter(
valuation.code.in_(list(pe.columns))),
date=date)
cir_mc.loc[date][df['code']] = df['circulating_market_cap'].values
pe.loc[date][df['code']] = df['pe_ratio'].values
ps.loc[date][df['code']] = df['ps_ratio'].values
else:
print("No need to Update")
cir_mc.index = pd.to_datetime(cir_mc.index)
pe.index = pd.to_datetime(pe.index)
ps.index = pd.to_datetime(ps.index)
cir_mc = cir_mc.sort_index(axis=0) # 按index排序
pe = pe.sort_index(axis=0) # 按index排序
ps = ps.sort_index(axis=0) # 按index排序
cir_mc = cir_mc.sort_index(axis=1) # 按股票代码排序
pe = pe.sort_index(axis=1) # 按股票代码排序
ps = ps.sort_index(axis=1) # 按股票代码排序
cir_mc = cir_mc.dropna(how='all', axis=0)
pe = pe.dropna(how='all', axis=0)
ps = ps.dropna(how='all', axis=0)
cir_mc.to_csv(
'/Users/caichaohong/Desktop/Zenki/financials/circulating_market_cap.csv'
)
pe.to_csv('/Users/caichaohong/Desktop/Zenki/financials/pe_ratio.csv')
ps.to_csv('/Users/caichaohong/Desktop/Zenki/financials/ps_ratio.csv')
def get_valuation(sec_code, yyyy_mm_dd):
k = (sec_code, yyyy_mm_dd)
if k in valuation_fetched:
print " skip fetching valuation %s, %s " % k
return None
valuation_fetched[k] = 1
#print " fetch valuation of %s, %s" % k
q = jq.query(jq.valuation).filter(jq.valuation.code == sec_code, )
# 传入date时, 查询指定日期date所能看到的最近(对市值表来说, 最近一天, 对其他表来说, 最近一个季度)的数据, 我们会查找上市公司在这个日期之前(包括此日期)发布的数据, 不会有未来函数.
ret = jq.get_fundamentals(q, date=yyyy_mm_dd)
if ret is None or len(ret) == 0:
print "WARN: %s 于 %s 的市值数据没查到 " % (sec_code, yyyy_mm_dd)
return None
return ret
def get_fundamental(code, Date):
myq = query(jqdatasdk.valuation).filter(jqdatasdk.valuation.code == code)
df = jqdatasdk.get_fundamentals(myq, Date)
strGetPe = '%s ' % df['pe_ratio']
strGetPb = '%s ' % df['pb_ratio']
# PE返回字符串处理
nEnd = strGetPe.index('N')
strGetPe = strGetPe[1:nEnd]
strPe = strGetPe.strip()
# PB返回字符串处理
nEnd = strGetPb.index('N')
strGetPb = strGetPb[1:nEnd]
strPb = strGetPb.strip()
strPe = "当日市盈率(PE)是：" + strPe + "; "
strPb = "当日市净率(PB)是：" + strPb + "; "
strReturn = strPe + strPb
return strReturn
def get_finance(code=None):
"""
获取指定财务条件的标的列表
:return:
"""
if not (code is None):
q = query(valuation, indicator).filter(valuation.code == code)
else:
q = query(valuation.code, valuation.market_cap,
valuation.circulating_market_cap, indicator.roe,
indicator.gross_profit_margin).filter(
valuation.market_cap > 80,
valuation.circulating_market_cap > 50,
valuation.turnover_ratio > 0.1,
indicator.roe > 0.05).order_by(
# 按市值降序排列
valuation.market_cap.desc())
# 取某行，某列的值 market_cap = df.iloc[0]['market_cap']
return jq.get_fundamentals(q)
def get_valuation(code_list, date):
# 市盈率TTM、换手率、市净率、市销率TTM、市现率TTM、总股本、总市值、流通股本、流通市值、市盈率
fields = [
"pe_ratio", "turnover_ratio", "pb_ratio", "ps_ratio", "pcf_ratio",
"capitalization", "market_cap", "circulating_cap",
"circulating_market_cap", "pe_ratio_lyr"
]
table = jq.valuation
cond = jq.valuation.code.in_(code_list)
order = jq.valuation.code.asc()
query = jq.query(table).filter(cond).order_by(order)
data_df = jq.get_fundamentals(query, date=date)
if data_df.empty:
return pd.DataFrame(
{field: []
for field in ["security"] + fields + ["date"]})
# 字段重命名
result_df = data_df.rename(columns={"code": "security", "day": "date"})
# 调整字段顺序
result_df = result_df[["security"] + fields + ["date"]]
return result_df
def verfiy_finance(security):
"""
验证基本面
:param security:
:return: bool 验证是否通过
"""
fund_df = jq.get_fundamentals(
query(valuation, indicator).filter(valuation.code == security))
fund_df = fund_df.fillna(value=100)
if fund_df is None or fund_df.empty:
flog.FinanceLoger.logger.info("标的{},获取不到财务数据".format(security))
return False
# and fund_df.iloc[0]["turnover_ratio"] > 0.01 and fund_df.iloc[0]["roe"] > 0.01 \
# and fund_df.iloc[0]["net_profit_margin"] > 5
if fund_df.iloc[0]["market_cap"] > 80 and fund_df.iloc[0][
"circulating_market_cap"] > 50:
return True
# fund_df.to_csv(security + '.csv')
return False
def get_income(code_list, date):
# 净利润、营业利润、利润总额、归属于母公司股东的净利润、营业收入、营业总收入、营业总成本、基本每股收益、稀释每股收益
fields = [
"net_profit", "operating_profit", "total_profit",
"np_parent_company_owners", "operating_revenue",
"total_operating_revenue", "total_operating_cost", "basic_eps",
"diluted_eps"
]
table = jq.income
cond = jq.income.code.in_(code_list)
order = jq.income.code.asc()
query = jq.query(table).filter(cond).order_by(order)
data_df = jq.get_fundamentals(query, date=date)
if data_df.empty:
return pd.DataFrame(
{field: []
for field in ["security"] + fields + ["date"]})
# 字段重命名
result_df = data_df.rename(columns={"code": "security", "day": "date"})
# 调整字段顺序
result_df = result_df[["security"] + fields + ["date"]]
return result_df
def get_annual_value_indicator2(statYYYY):
q = jq.query(
jq.indicator.code,
jq.indicator.statDate,
jq.balance.total_assets #总资产(元)
,
jq.balance.good_will #商誉 (元) 其实可疑的项目还有很多，比如无形资产，应收帐款，在建工程，库存 ...
,
jq.balance.total_current_assets #流动资产(元)
,
jq.balance.total_liability #总负债(元)
,
jq.balance.total_current_liability #流动负债(元)
,
jq.cash_flow.net_operate_cash_flow # 经营活动产生的现金流量净额(元)
,
jq.cash_flow.net_invest_cash_flow # 投资活动产生的现金流量净额(元)
,
jq.cash_flow.cash_equivalent_increase # 现金及现金等价物净增加额(元)
,
jq.income.net_profit #净利润(元)
,
jq.income.np_parent_company_owners # 归属于母公司股东的净利润(元)
,
jq.income.basic_eps #基本每股收益(元)
,
jq.indicator.adjusted_profit # 扣除非经常损益后的净利润(元)
,
jq.indicator.gross_profit_margin #销售毛利率(%)
).filter(jq.indicator.code == jq.balance.code,
jq.indicator.code == jq.cash_flow.code,
jq.indicator.code == jq.income.code).order_by(jq.indicator.code)
ret = jq.get_fundamentals(q, statDate=statYYYY)
if ret is None or len(ret) == 0:
print "WARN: %s年的基本面数据没查到 " % statYYYY
return ret
for s in range(len(insert_list)):
sql = "INSERT INTO %s (date,open,close,high,low,cjl) VALUES ( '%s', %.2f ,%.2f ,%.2f ,%.2f,%.2f )"
date = datetime.date(
datetime.fromtimestamp(insert_list[s][0].timestamp()))
data = ('TB' + dm_insert_list[q][0], date, insert_list[s][1],
insert_list[s][2], insert_list[s][3], insert_list[s][4],
insert_list[s][5])
cursor.execute(sql % data)
connect.commit()
print('TB' + dm_insert_sh_list[q][:6], '收盘价数据获取完成')
#获取市值
for u in range(len(date_new_list)):
df_volandincome = jq.get_fundamentals(jq.query(
jq.valuation.code, jq.valuation.circulating_market_cap,
jq.valuation.pe_ratio, jq.income.total_operating_revenue,
jq.income.np_parent_company_owners).filter(
jq.valuation.code == dm_insert_sh_list[q]),
date=date_new_list[u])
volandincome_list = df_volandincome.values.tolist()
sql = "update %s set dm='%s' , ltsz=%.2f , syl=%.2f , ys=%.2f , jlr=%.2f where date='%s'"
try:
data = ('TB' + volandincome_list[0][0][:6],
volandincome_list[0][0][:6], volandincome_list[0][1],
volandincome_list[0][2], volandincome_list[0][3],
volandincome_list[0][4], date_new_list[u])
cursor.execute(sql % data)
except Exception as e:
connect.rollback() # 事务回滚
continue
else:
connect.commit() # 事务提交
'2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018', '2019',
'2020'
]
rev = pd.DataFrame(index=stock_list, columns=years)
rev_growth = pd.DataFrame(index=stock_list, columns=years)
np = pd.DataFrame(index=stock_list, columns=years)
np_growth = pd.DataFrame(index=stock_list, columns=years)
pe = pd.DataFrame(index=stock_list, columns=years)
for i in tqdm(range(len(stock_list))):
ret = [
get_fundamentals(query(
indicator.statDate, income.np_parent_company_owners,
income.total_operating_revenue,
indicator.inc_total_revenue_year_on_year,
indicator.inc_net_profit_to_shareholders_year_on_year,
valuation.pe_ratio).filter(income.code == stock_list[i]),
statDate=y) for y in years
]
temp = pd.DataFrame()
for rr in ret:
temp = pd.concat([temp, rr], join='outer')
temp.index = [x.split('-')[0] for x in temp['statDate']]
rev.loc[stock_list[i]][
temp.index] = temp['total_operating_revenue'].values * 10**(-8)
rev_growth.loc[stock_list[i]][
temp.index] = temp['inc_total_revenue_year_on_year'].values
np.loc[stock_list[i]][
temp.index] = temp['np_parent_company_owners'].values * 10**(-8)
np_growth.loc[stock_list[i]][temp.index] = temp[