def get_scale(code: str) -> tuple:
fields = (finance.FUND_FIN_INDICATOR.period_end, finance.FUND_FIN_INDICATOR.total_tna)
df = finance.run_query(query(*fields).filter(finance.FUND_FIN_INDICATOR.code == code).
order_by(finance.FUND_FIN_INDICATOR.pub_date.desc()).limit(1))
return df.loc[0, 'period_end'], df.loc[0, 'total_tna']
def query(*args, **kwargs):
return jqdatasdk.query(*args, **kwargs)
'inc_operation_profit_year_on_year', 'inc_net_profit_year_on_year',
'inc_net_profit_to_shareholders_year_on_year'
],
'profit': [
'pubDate', 'statDate', 'total_operating_revenue', 'operating_revenue',
'operating_profit', 'total_profit', 'net_profit',
'np_parent_company_owners', 'basic_eps', 'diluted_eps'
]
}
data_path = root_path + 'data/fund/{}/'.format(statement)
os.makedirs(data_path, 0o777, exist_ok=True)
if not utils.is_bday(d, country):
print('{} is {} holiday'.format(d, country))
quit()
import jqdatasdk
import utils_jq
stocks = utils_jq.get_universe(utils.date_str(d))
f = jqdatasdk.get_fundamentals(jqdatasdk.query(
jqdatasdk.valuation, jqdatasdk.income,
jqdatasdk.indicator).filter(jqdatasdk.valuation.code.in_(stocks)),
date=utils.date_str(d))
f['date'] = d
f.fillna(0)
f = f.rename(columns={'code': 'ric'})
f = f.drop_duplicates()
f = f[['date', 'ric'] + fund_map[statement]]
data_file = data_path + '/{}.txt'.format(d)
print(data_file)
f.to_csv(data_file, sep='\t', index=False)
print("当日可调用数据总条数为：",count['total'])
print("当日剩余可调用条数为：",count['spare'])
# In[3]:
# 获取平安银行股票数据，XSHE 深圳交易所 XSHG 上海交易所
jq.get_price("000001.XSHE", start_date="2021-01-01", end_date="2021-03-05")
# In[4]:
# valuation是内置市值数据对象(这里表示查询valuation下所有的属性)
q = jq.query(jq.valuation).filter(jq.valuation.code=='000001.XSHE')
df = jq.get_fundamentals(q, '2021-01-01')
df
# ## 获取金融板块的股票
# In[17]:
import pandas as pd
pd.set_option('max_rows', None)
# 汽车制造 C36，零售业 F52，其他金融业：J69, 货币金融服务：J66
# 计算机、通信和其他电子设备制造业 C39
temp = jq.get_industries()
# 筛选出和金融相关的产业
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']
# 回购和价格行情日期不同
df_repo = pd.DataFrame(columns=['licha'], index=close.index)
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
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')
import jqdatasdk
# 获取全市场股票PE
all_stock = jqdatasdk.get_all_securities(['stock'], date=query_date)
stocks = list(all_stock.index)
print(" 全市场", len(stocks), "只股票信息")
stock1 = stocks[:2000]
stock2 = stocks[2000:]
q1 = jqdatasdk.query(jqdatasdk.valuation.code, jqdatasdk.valuation.day,
jqdatasdk.valuation.pe_ratio,
jqdatasdk.valuation.pb_ratio).filter(
jqdatasdk.valuation.code.in_(stock1))
df1 = jqdatasdk.get_fundamentals(q1, date=query_date)
df1.to_sql(name='stock_valuations', if_exists='append', con=conn, index=False)
q2 = jqdatasdk.query(jqdatasdk.valuation.code, jqdatasdk.valuation.day,
jqdatasdk.valuation.pe_ratio,
jqdatasdk.valuation.pb_ratio).filter(
jqdatasdk.valuation.code.in_(stock2))
df2 = jqdatasdk.get_fundamentals(q2, date=query_date)
df2.to_sql(name='stock_valuations', if_exists='append', con=conn, index=False)
stock_fund = pd.concat([df1, df2]).set_index('code')
# 计算全市场等权PE
def get_fundamentals_continuously(code, Date, Count):
myq = query(jqdatasdk.valuation).filter(jqdatasdk.valuation.code == code)
GL_df = jqdatasdk.get_fundamentals_continuously(myq, end_date=Date, count=Count, panel=False)
return GL_df
close_rts_1['hs300'] = hs300['rts_1']
close_rts_5 = close.pct_change(5)
close_rts_5['hs300'] = hs300['rts_5']
close_rts_10 = close.pct_change(10)
close_rts_10['hs300'] = hs300['rts_10']
close_rts_20 = close.pct_change(20)
close_rts_20['hs300'] = hs300['rts_20']
close_rts_30 = close.pct_change(30)
close_rts_30['hs300'] = hs300['rts_30']
close_rts_60 = close.pct_change(60)
close_rts_60['hs300'] = hs300['rts_60']
df = list(
get_fundamentals(
query(valuation.code).filter(
valuation.market_cap > 100,
valuation.pe_ratio > 25))['code']) # 市值大于100的股票池
roe_yeayly = pd.read_csv(
'/Users/caichaohong/Desktop/Zenki/financials/roe_yearly.csv',
index_col='statDate')
# N日波动率
rts_std_20 = close_rts_1.std()
rts_std_90 = close_rts_1.std()
# 年度：
annual_index = [x for x in roe_yeayly.index if x.endswith('12-31')]
# 选股：
rev_growth_value = 10
np_growth_value = 10
for row in cursor.fetchall():
if row[0].startswith('6'):
r = row[0] + '.XSHG'
else:
r = row[0] + '.XSHE'
dm_list.append(r)
#主体部分
for y in range(0, len(dm_list)):
total = 0
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_list[y],
finance.STK_SHAREHOLDER_FLOATING_TOP10.end_date ==
rq_list[x]))
for z in range(len(date_int_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_list[y][:6],
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'],
def get_stocks_fundamentals(stocks, trade_date):
q = jqdatasdk.query(jqdatasdk.valuation).filter(
jqdatasdk.valuation.code.in_(stocks))
df = jqdatasdk.get_fundamentals(q, trade_date)
return df
def jq_query(*args, **kwargs):
logger.info("HTTP QUERY, with args={}, kwargs={}".format(args, kwargs))
return query(*args, **kwargs)
def get_distribute_info(sec_code, start_day):
q = jq.query(
jq.finance.STK_XR_XD.company_name,
jq.finance.STK_XR_XD.code,
jq.finance.STK_XR_XD.report_date,
jq.finance.STK_XR_XD.bonus_type #年度分红 中期分红 季度分红 特别分红 向公众股东赠送 股改分红
,
jq.finance.STK_XR_XD.board_plan_pub_date #董事会预案公告日期
,
jq.finance.STK_XR_XD.board_plan_bonusnote #董事会预案分红说明 每10股送XX转增XX派XX元
,
jq.finance.STK_XR_XD.shareholders_plan_pub_date # 股东大会预案公告日期
,
jq.finance.STK_XR_XD.shareholders_plan_bonusnote #股东大会预案分红说明
,
jq.finance.STK_XR_XD.implementation_pub_date #实施方案公告日期
,
jq.finance.STK_XR_XD.implementation_bonusnote #实施方案分红说明
,
jq.finance.STK_XR_XD.dividend_ratio # 送股比例 每10股送XX股
,
jq.finance.STK_XR_XD.transfer_ratio # 转增比例 每10股转增 XX股
,
jq.finance.STK_XR_XD.
bonus_ratio_rmb # 每10股派 XX。说明：这里的比例为最新的分配比例，预案公布的时候，预案的分配基数在此维护，如果股东大会或实施方案发生变化，再次进行修改，保证此处为最新的分配基数
,
jq.finance.STK_XR_XD.dividend_number # 送股数量 单位：万股
,
jq.finance.STK_XR_XD.transfer_number # 转增数量 单位：万股
,
jq.finance.STK_XR_XD.bonus_amount_rmb # 派息金额(人民币) 单位：万元
,
jq.finance.STK_XR_XD.a_registration_date # A股股权登记日
,
jq.finance.STK_XR_XD.a_xr_date # A股除权日
,
jq.finance.STK_XR_XD.a_bonus_date # 派息日(A)
,
jq.finance.STK_XR_XD.dividend_arrival_date #红股到帐日
,
jq.finance.STK_XR_XD.a_increment_listing_date #A股新增股份上市日
,
jq.finance.STK_XR_XD.total_capital_before_transfer #送转前总股本 单位：万股
,
jq.finance.STK_XR_XD.total_capital_after_transfer #送转后总股本
,
jq.finance.STK_XR_XD.float_capital_before_transfer #送转前流通股本
,
jq.finance.STK_XR_XD.float_capital_after_transfer #送转后流通股本
#,jq.finance.STK_XR_XD.plan_progress_code # 方案进度编码
#,jq.finance.STK_XR_XD.plan_progress # 方案进度说明
).filter(
jq.finance.STK_XR_XD.code == sec_code,
jq.finance.STK_XR_XD.report_date >= start_day,
jq.finance.STK_XR_XD.plan_progress_code == '313002' # 方案进度编码
).order_by(jq.finance.STK_XR_XD.report_date)
# 返回一个 dataframe， 每一行对应数据表中的一条数据， 列索引是你所查询的字段名称
df = jq.finance.run_query(q)
return df
#!/usr/bin/python3
#/usr/local/bin/python3
import pandas as pd
import datetime
import sys
import os
root_path = os.path.dirname(os.path.realpath(__file__)) + '/../'
sys.path.append(root_path + 'scripts/')
import utils
import utils_jq
import jqdatasdk
data_path = root_path + 'data/holder/cn/'
dt = sys.argv[1]
os.makedirs(data_path, 0o777, exist_ok = True)
#stocks = utils_jq.get_universe(utils.date_str(dt))
stocks = utils_jq.get_universe(utils.date_str(datetime.datetime.now().strftime('%Y%m%d')))
df_list = []
df = jqdatasdk.finance.run_query(jqdatasdk.query(jqdatasdk.finance.STK_HOLDER_NUM).filter(jqdatasdk.finance.STK_HOLDER_NUM.pub_date == utils.date_str(dt)))
df['date'] = utils.date_str(dt)
df.fillna(0)
df = df.rename(columns = {'code': 'ric'})
df = df[df['ric'].isin(stocks)]
df = df.drop_duplicates()
df = df[['date', 'ric', 'end_date', 'pub_date', 'share_holders']]
data_file = data_path + '{}.txt'.format(dt)
print(data_file)
df.to_csv(data_file, sep='\t', index = False)
date_str = datetime.datetime.now().strftime("%Y-%m-%d")
trade_days = jq.get_trade_days(start_date=date_str, end_date=None, count=None)
if trade_days and trade_days[0].strftime("%Y-%m-%d") == date_str:
print("开盘日:", date_str)
else:
sys.exit()
# stocks_hourly 股票小时行情
# securities 所有标的物
# stocks_hourly = stock_a_db['stocks_hourly']
all_stocks_name = jq.get_all_securities()
all_stocks_name_list = list(all_stocks_name.index)
# 取得股本情况
basic_stocks_plus_1 = jq.get_fundamentals(jq.query(jq.valuation).filter(
jq.valuation.code.in_(all_stocks_name_list)),
date=date_str)
basic_stocks_plus_1 = basic_stocks_plus_1.set_index("code")[[
'day', 'capitalization', 'circulating_cap', 'market_cap',
'circulating_market_cap', 'turnover_ratio'
]]
basic_stocks_plus_1['day'] = parser.parse(date_str + 'T00:00:00.000Z')
# 取股票日candle
basic_stocks_plus_2 = jq.get_price(all_stocks_name_list,
start_date=date_str,
end_date=date_str,
frequency='1d').major_xs(date_str)
# 合并
stock_basic = pd.merge(all_stocks_name,
basic_stocks_plus_1,
left_index=True,
def get_factor_data(stockPool, factor,date_start, date_end):
#获取股票池函数
def get_stock(stockPool, begin_date):
if stockPool == 'HS300':#用于获取沪深300股票池
stockList = jd.get_index_stocks('000300.XSHG', begin_date)
elif stockPool == 'ZZ500':#用于获取中证500股票池
stockList = jd.get_index_stocks('399905.XSHE', begin_date)
elif stockPool == 'ZZ800':#用于获取中证800股票池
stockList = jd.get_index_stocks('399906.XSHE', begin_date)
elif stockPool == 'A':#用于获取全部A股股票池
stockList = jd.get_index_stocks('000002.XSHG', begin_date) + jd.get_index_stocks('399107.XSHE', begin_date)
else:#自定义输入股票池
stockList = stockPool
return stockList
#从财务库获取数据
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
#获取日期列表
date_list = jd.get_trade_days(start_date = date_start, end_date = date_end)
#空df预备存储数据
data = pd.DataFrame(columns = get_stock(stockPool,begin_date=date_list[0]))
#获取五张财务基础所有指标名称
val = jd.get_fundamentals(jd.query(jd.valuation).limit(1)).columns.tolist()
bal = jd.get_fundamentals(jd.query(jd.balance).limit(1)).columns.tolist()
cf = jd.get_fundamentals(jd.query(jd.cash_flow).limit(1)).columns.tolist()
inc = jd.get_fundamentals(jd.query(jd.income).limit(1)).columns.tolist()
ind = jd.get_fundamentals(jd.query(jd.indicator).limit(1)).columns.tolist()
all_columns = val+bal+cf+inc+ind
all_stocks = get_stock(stockPool, date_list[0])
#循环时间列表获取指标数据
for date in date_list:
#获取股票池
all_stocks = get_stock(stockPool, date)
#获取因子数据
if factor in all_columns: #可以从财务库直接取到因子值的因子
data_temp = get_factor_data1(factor,all_stocks, date)
else: #可以从因子库直接取到因子值的因子
try:
data_temp = jd.get_factor_values(all_stocks, [factor], end_date = date, count = 1)[factor]
except:
print('系统暂不能获取该因子，请获取其他因子')
break
data = pd.concat([data, data_temp], axis = 0)
return data
def http_get_fundamentals(table, code, count=None, date=None, columns=None):
body = {
"method": "get_fundamentals",
"token": token,
"table": table,
"columns": columns,
"code": code,
"date": date,
"count": count,
"pe_ratio": None,
}
return get_data(requests.post(url, data=json.dumps(body)))
http_get_fundamentals('income', code="000001.XSHE", count=5, date="2016-12-04",
columns="code,pubDate,day,statDate,commission_income")
from jqdatasdk import cash_flow, query
import jqdatasdk
jqdatasdk.auth('18806662076', 'WANGfei123')
myq = query(jqdatasdk.valuation).filter(jqdatasdk.valuation.code == '000001.XSHE')
df = jqdatasdk.get_fundamentals(myq, '2018-4-12')
print("当日动态市盈率是：", df['pe_ratio'])
print('当日市净率是：', df['pb_ratio'])
print('当日换手率是：', df['turnover_ratio'])
print('当日市销率是：', df['ps_ratio'])
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_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')
import jqdatasdk
from datetime import datetime
import pandas as pd
import numpy as np
jqdatasdk.auth("******", "******")
if __name__ == '__main__':
'''get threshold on certain date and stock index'''
benchmark_date = '2015-12-30'
stock_set = '000018.XSHG'
q = jqdatasdk.query(jqdatasdk.valuation.code, jqdatasdk.valuation.pe_ratio, jqdatasdk.valuation.market_cap).\
filter(jqdatasdk.valuation.pe_ratio>0, jqdatasdk.valuation.code.in_(jqdatasdk.get_index_stocks(stock_set))).\
order_by(jqdatasdk.valuation.pe_ratio.asc())
df = jqdatasdk.get_fundamentals(q, benchmark_date)
pe_mean = float(df['pe_ratio'].mean())
mc_mean = float(df['market_cap'].mean())
print df.head()
print
print 'pe_mean', pe_mean, '; mc_mean', mc_mean
print
'''get stock index we want'''
q = jqdatasdk.query(jqdatasdk.valuation.code, jqdatasdk.valuation.pe_ratio).\
filter(jqdatasdk.valuation.pe_ratio<pe_mean, jqdatasdk.valuation.market_cap<mc_mean,\
jqdatasdk.valuation.pe_ratio>0, jqdatasdk.valuation.code.in_(jqdatasdk.get_index_stocks(stock_set))).\
order_by(jqdatasdk.valuation.pe_ratio.asc())
df = jqdatasdk.get_fundamentals(q, benchmark_date)
newlist = df['code'].tolist()
print newlist
print
'''get benchmark_date data'''
index_col='Unnamed: 0',
date_parser=dateparse)
close = clean_close(close, low, high_limit) # 新股一字板
close = clean_st_exit(close) # 退市和ST
years = [
'2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018',
'2019', '2020'
]
years_q = [x + 'q' + str(i) for x in years for i in range(1, 5)]
years_q.append('2021q1')
all_stock = list(close.columns)
# 季度：get_date_index :
datelist = [
get_fundamentals(query(
indicator.statDate).filter(income.code == all_stock[0]),
statDate=y) for y in years_q
]
date_df = pd.DataFrame()
for rr in datelist:
date_df = pd.concat([date_df, rr], join='outer')
#
roe = pd.DataFrame(columns=all_stock, index=date_df['statDate'])
inc_roe = pd.DataFrame(columns=all_stock, index=date_df['statDate'])
roa = pd.DataFrame(columns=all_stock, index=date_df['statDate'])
total_revenue = pd.DataFrame(columns=all_stock, index=date_df['statDate'])
total_revenue_growth = pd.DataFrame(columns=all_stock,
index=date_df['statDate'])
net_profit = pd.DataFrame(columns=all_stock, index=date_df['statDate'])
net_profit_growth = pd.DataFrame(columns=all_stock, index=date_df['statDate'])
gross_profit_margin = pd.DataFrame(columns=all_stock,
port=3306,
user='******',
passwd='123456',
db='xg',
charset='utf8')
cursor = connect.cursor()
#获取流通市值和净利润
date_list = []
sql = "SELECT date FROM rqb order by id "
cursor.execute(sql)
for row in cursor.fetchall():
date_list.append(row)
for k in range(0, len(date_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),
date=date_list[k][0])
volandincome_list = df_volandincome.values.tolist()
total = 0
for l in range(0, len(volandincome_list)):
sql = "update %s set dm='%s' , ltsz=%.2f , syl=%.2f , ys=%.2f , jlr=%.2f where date='%s'"
try:
dataall = ('TB' + volandincome_list[l][0][:6],
volandincome_list[l][0][:6], volandincome_list[l][1],
volandincome_list[l][2], volandincome_list[l][3],
volandincome_list[l][4], date_list[k][0])
cursor.execute(sql % dataall)
except Exception as e:
connect.rollback() # 事务回滚
continue
'''
#获取十大流通股东
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 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
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')
fq_ref_date=datetime.date.today())
market_df.index.names = ['code', 'idx']
market_df_copy = market_df.copy()
market_df_copy['year'] = market_df_copy['date'].apply(lambda x: x.year)
market_df_copy['month'] = market_df_copy['date'].apply(lambda x: x.month)
market_df_copy.index = market_df_copy.index.droplevel(1)
market_df_copy['pd_datetime'] = pd.to_datetime(market_df_copy.date)
market_df_copy.reset_index(inplace=True)
#---------------------------------get data--------------------------
date_list = market_df_copy.date.unique()
char_df = pd.DataFrame()
for date in date_list:
q = jq.query(jq.valuation.day, jq.valuation.code,
jq.valuation.circulating_market_cap,
jq.valuation.pb_ratio, jq.valuation.pe_ratio_lyr).filter(
jq.valuation.code.in_(avai_stock_list))
result = jq.get_fundamentals(q, date)
char_df = char_df.append(result)
char_df.day = pd.to_datetime(char_df.day)
char_df.circulating_market_cap = char_df.circulating_market_cap.apply(
np.log)
char_df.pb_ratio = char_df.pb_ratio.apply(lambda x: np.log(1 / x))
char_df.pe_ratio_lyr = 1 / char_df.pe_ratio_lyr
char_df.sort_values(['day', 'code'], inplace=True)
#---------------------------------merge_df--------------------------
year = datetime.date.today().year
month = datetime.date.today().month
# encoding=utf-8
"""
本脚本对可转债数据进行研究
"""
from DataSource.auth_info import jq_login
from jqdatasdk import bond, query
if __name__ == '__main__':
jq_login()
df = bond.run_query(
query(bond.CONBOND_BASIC_INFO).filter(
bond.CONBOND_BASIC_INFO.bond_form_id == '703011'))
end = 0
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)
dm_cq_list = []
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('-', ''))):
print('未来除权')
