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('未来除权')