class CashFlow(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'y': INT(), 'm': INT(),
'end_date': DATE(),
'comp_type': VARCHAR(length=1), 'report_type': VARCHAR(length=1), 'net_profit': Float(precision=53),
'finan_exp': Float(precision=53), 'c_fr_sale_sg': Float(precision=53),
'recp_tax_rends': Float(precision=53),
'n_depos_incr_fi': Float(precision=53), 'n_incr_loans_cb': Float(precision=53),
'n_inc_borr_oth_fi': Float(precision=53),
'prem_fr_orig_contr': Float(precision=53), 'n_incr_insured_dep': Float(precision=53),
'n_reinsur_prem': Float(precision=53),
'n_incr_disp_tfa': Float(precision=53), 'ifc_cash_incr': Float(precision=53),
'n_incr_disp_faas': Float(precision=53),
'n_incr_loans_oth_bank': Float(precision=53), 'n_cap_incr_repur': Float(precision=53),
'c_fr_oth_operate_a': Float(precision=53), 'c_inf_fr_operate_a': Float(precision=53),
'c_paid_goods_s': Float(precision=53),
'c_paid_to_for_empl': Float(precision=53), 'c_paid_for_taxes': Float(precision=53),
'n_incr_clt_loan_adv': Float(precision=53),
'n_incr_dep_cbob': Float(precision=53), 'c_pay_claims_orig_inco': Float(precision=53),
'pay_handling_chrg': Float(precision=53), 'pay_comm_insur_plcy': Float(precision=53),
'oth_cash_pay_oper_act': Float(precision=53), 'st_cash_out_act': Float(precision=53),
'n_cashflow_act': Float(precision=53),
'oth_recp_ral_inv_act': Float(precision=53), 'c_disp_withdrwl_invest': Float(precision=53),
'c_recp_return_invest': Float(precision=53), 'n_recp_disp_fiolta': Float(precision=53),
'n_recp_disp_sobu': Float(precision=53), 'stot_inflows_inv_act': Float(precision=53),
'c_pay_acq_const_fiolta': Float(precision=53), 'c_paid_invest': Float(precision=53),
'n_disp_subs_oth_biz': Float(precision=53), 'oth_pay_ral_inv_act': Float(precision=53),
'n_incr_pledge_loan': Float(precision=53), 'stot_out_inv_act': Float(precision=53),
'n_cashflow_inv_act': Float(precision=53),
'c_recp_borrow': Float(precision=53), 'proc_issue_bonds': Float(precision=53),
'oth_cash_recp_ral_fnc_act': Float(precision=53), 'stot_cash_in_fnc_act': Float(precision=53),
'free_cashflow': Float(precision=53), 'c_prepay_amt_borr': Float(precision=53),
'c_pay_dist_dpcp_int_exp': Float(precision=53),
'incl_dvd_profit_paid_sc_ms': Float(precision=53), 'oth_cashpay_ral_fnc_act': Float(precision=53),
'stot_cashout_fnc_act': Float(precision=53), 'n_cash_flows_fnc_act': Float(precision=53),
'eff_fx_flu_cash': Float(precision=53), 'n_incr_cash_cash_equ': Float(precision=53),
'c_cash_equ_beg_period': Float(precision=53), 'c_cash_equ_end_period': Float(precision=53),
'c_recp_cap_contrib': Float(precision=53), 'incl_cash_rec_saims': Float(precision=53),
'uncon_invest_loss': Float(precision=53), 'prov_depr_assets': Float(precision=53),
'depr_fa_coga_dpba': Float(precision=53),
'amort_intang_assets': Float(precision=53), 'lt_amort_deferred_exp': Float(precision=53),
'decr_deferred_exp': Float(precision=53), 'incr_acc_exp': Float(precision=53),
'loss_disp_fiolta': Float(precision=53),
'loss_scr_fa': Float(precision=53), 'loss_fv_chg': Float(precision=53), 'invest_loss': Float(precision=53),
'decr_def_inc_tax_assets': Float(precision=53), 'incr_def_inc_tax_liab': Float(precision=53),
'decr_inventories': Float(precision=53), 'decr_oper_payable': Float(precision=53),
'incr_oper_payable': Float(precision=53),
'others': Float(precision=53), 'im_net_cashflow_oper_act': Float(precision=53),
'conv_debt_into_cap': Float(precision=53),
'conv_copbonds_due_within_1y': Float(precision=53), 'fa_fnc_leases': Float(precision=53),
'end_bal_cash': Float(precision=53),
'beg_bal_cash': Float(precision=53), 'end_bal_cash_equ': Float(precision=53),
'beg_bal_cash_equ': Float(precision=53),
'im_n_incr_cash_equ': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'cashflow'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
def _update_ts(self):
cal_date = self._get_nearest_cal_date()
if cal_date is None:
return
trade_date = cal_date.strftime('%Y%m%d')
df = get_pro().daily_basic(ts_code='', trade_date=trade_date)
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'close': FLOAT(),
'y': INT(),
'm': INT(),
'turnover_rate': FLOAT(),
'turnover_rate_f': FLOAT(),
'volume_ratio': FLOAT(),
'pe': FLOAT(),
'pe_ttm': FLOAT(),
'pb': FLOAT(),
'ps': FLOAT(),
'ps_ttm': FLOAT(),
'dv_ratio': FLOAT(),
'dv_ttm': FLOAT(),
'total_share': FLOAT(),
'float_share': FLOAT(),
'free_share': FLOAT(),
'total_mv': FLOAT(),
'circ_mv': FLOAT()
}
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_stock_price_monthly(ts_code, force=None):
table_name = 'stock_price_monthly'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
df = get_pro().monthly(
ts_code=ts_code,
fields='ts_code,trade_date,open,high,low,close,vol,amount')
if not len(df):
return
df_add_y_m(df, 'trade_date')
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'y': INT,
'm': INT,
'open': DECIMAL(precision=8, scale=2),
'high': DECIMAL(precision=8, scale=2),
'low': DECIMAL(precision=8, scale=2),
'close': DECIMAL(precision=8, scale=2),
'vol': BIGINT(),
'amount': BIGINT()
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
class PatientTable(BaseModel):
__tablename__ = 'patient'
pid = Column(VARCHAR(24), primary_key=True, nullable=False, unique=True)
fname = Column(VARCHAR(45))
lname = Column(VARCHAR(45))
birthday = Column(DATE())
sex = Column(VARCHAR(45))
location = Column(VARCHAR(45))
def __init__(self,pid,\
fname=None,\
lname=None,\
birthday=None,\
sex=None,\
location=None):
self.pid = pid
self.fname = fname
self.lname = lname
self.birthday = birthday
self.sex = sex
self.location = location
def __repr__(self):
return 'patient information: \n'+\
'pid:'+HORIZONTAL_TABLE+self.pid+\
'fname:'+HORIZONTAL_TABLE+self.fname+\
'lname:'+HORIZONTAL_TABLE+self.lname+\
'birthday:'+HORIZONTAL_TABLE+str(self.birthday)+\
'sex:'+HORIZONTAL_TABLE+self.sex+\
'location:'+HORIZONTAL_TABLE+self.location
def _init_ts(self):
y_start = 1990
__pool = ThreadPoolExecutor(max_workers=MULTIPLE,
thread_name_prefix="test_")
fs = []
i = 0
for y_i in range(31)[::-1]:
y = y_start + y_i
first, last = dao.get_trade_date(y, 0)
if not first:
continue
print("{}-{}".format(y, 0))
first = first.strftime('%Y%m%d')
last = last.strftime('%Y%m%d')
f1 = __pool.submit(get_pro().index_weight,
index_code=self.biz_code,
start_date=first,
end_date=first)
f2 = __pool.submit(get_pro().index_weight,
index_code=self.biz_code,
start_date=last,
end_date=last)
fs.append(f1)
fs.append(f2)
i += 2
if i > 197:
print('198次后休息60秒')
time.sleep(60)
i = 0
df = None
for f2 in fs:
temp_df = f2.result()
if len(temp_df):
if df is None:
df = temp_df
else:
df = df.append(temp_df, ignore_index=True)
df_add_y_m(df, 'trade_date')
dtype = {
'index_code': VARCHAR(length=10),
'con_code': VARCHAR(length=10),
'y': INT,
'm': INT,
'trade_date': DATE(),
'weight': DECIMAL(precision=10, scale=6)
}
df = df.reindex(
columns='index_code,con_code,y,m,trade_date,weight'.split(','))
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
class Education(DeclarativeBase):
__tablename__ = 'education'
##{B:Columns}
id = Column(Integer, primary_key=True)
order = Column(Integer)
education = Column(Unicode(255))
date_completed = Column(DATE())
userid = Column(Integer, ForeignKey('tg_user.user_id'))
class ProjectHistory(DeclarativeBase):
__tablename__ = 'project_history'
##{B:Columns}
id = Column(Integer, primary_key=True)
order = Column(Integer)
name = Column(Unicode(64))
summary = Column(Unicode(4096))
start = Column(DATE())
end = Column(DATE())
tools_used = Column(Unicode(128))
tags = Column(Unicode(1024))
hidden = Column(Boolean())
userid = Column(Integer, ForeignKey('tg_user.user_id'))
companyid = Column(Integer, ForeignKey('job_history.id'))
project_bullets = relationship("ProjectPoints", backref="project", order_by="ProjectPoints.order")
class Award(DeclarativeBase):
__tablename__ = 'awards'
##{B:Columns}
id = Column(Integer, primary_key=True)
order = Column(Integer)
award = Column(Unicode(255))
date_awarded = Column(DATE())
userid = Column(Integer, ForeignKey('tg_user.user_id'))
def _init_ts(self):
print('start init list...')
fileds = 'ts_code,symbol,name,area,industry,fullname,market,exchange,curr_type,list_status,list_date,delist_date,is_hs'
d_l = get_pro().query(self.interface,
exchange='',
list_status='L',
fields=fileds)
print('L', len(d_l))
d_d = get_pro().query(self.interface,
exchange='',
list_status='D',
fields=fileds)
print('D', len(d_d))
d_p = get_pro().query(self.interface,
exchange='',
list_status='P',
fields=fileds)
print('P', len(d_p))
df = d_l.append(d_d).append(d_p)
print('all size:', len(df))
dtype = {
'ts_code': VARCHAR(length=10),
'symbol': VARCHAR(length=8),
'name': VARCHAR(length=20),
'area': VARCHAR(length=10),
'industry': VARCHAR(length=32),
'fullname': VARCHAR(length=32),
'market': VARCHAR(length=10),
'exchange': VARCHAR(length=10),
'curr_type': VARCHAR(length=5),
'list_status': VARCHAR(length=1),
'list_date': DATE(),
'delist_date': DATE(),
'is_hs': VARCHAR(length=1)
}
df.to_sql(self.get_table_name(),
get_engine(),
dtype=dtype,
index=False,
if_exists='replace')
class Income(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'y': INT(), 'm': INT(),
'end_date': DATE(),
'report_type': VARCHAR(length=1), 'comp_type': VARCHAR(length=1), 'basic_eps': Float(precision=53),
'diluted_eps': Float(precision=53), 'total_revenue': Float(precision=53), 'revenue': Float(precision=53),
'int_income': Float(precision=53), 'prem_earned': Float(precision=53), 'comm_income': Float(precision=53),
'n_commis_income': Float(precision=53), 'n_oth_income': Float(precision=53),
'n_oth_b_income': Float(precision=53),
'prem_income': Float(precision=53), 'out_prem': Float(precision=53), 'une_prem_reser': Float(precision=53),
'reins_income': Float(precision=53), 'n_sec_tb_income': Float(precision=53),
'n_sec_uw_income': Float(precision=53),
'n_asset_mg_income': Float(precision=53), 'oth_b_income': Float(precision=53),
'fv_value_chg_gain': Float(precision=53),
'invest_income': Float(precision=53), 'ass_invest_income': Float(precision=53),
'forex_gain': Float(precision=53),
'total_cogs': Float(precision=53), 'oper_cost': Float(precision=53), 'int_exp': Float(precision=53),
'comm_exp': Float(precision=53), 'biz_tax_surchg': Float(precision=53), 'sell_exp': Float(precision=53),
'admin_exp': Float(precision=53), 'fin_exp': Float(precision=53),
'assets_impair_loss': Float(precision=53),
'prem_refund': Float(precision=53), 'compens_payout': Float(precision=53),
'reser_insur_liab': Float(precision=53),
'div_payt': Float(precision=53), 'reins_exp': Float(precision=53), 'oper_exp': Float(precision=53),
'compens_payout_refu': Float(precision=53), 'insur_reser_refu': Float(precision=53),
'reins_cost_refund': Float(precision=53),
'other_bus_cost': Float(precision=53), 'operate_profit': Float(precision=53),
'non_oper_income': Float(precision=53),
'non_oper_exp': Float(precision=53), 'nca_disploss': Float(precision=53),
'total_profit': Float(precision=53),
'income_tax': Float(precision=53), 'n_income': Float(precision=53), 'n_income_attr_p': Float(precision=53),
'minority_gain': Float(precision=53), 'oth_compr_income': Float(precision=53),
't_compr_income': Float(precision=53),
'compr_inc_attr_p': Float(precision=53), 'compr_inc_attr_m_s': Float(precision=53),
'ebit': Float(precision=53),
'ebitda': Float(precision=53), 'insurance_exp': Float(precision=53), 'undist_profit': Float(precision=53),
'distable_profit': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'income'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
class Dividend(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(), 'm': INT(), 'ann_date': DATE(),
'div_proc': VARCHAR(length=10),
'stk_div': Float(precision=53), 'stk_bo_rate': Float(precision=53), 'stk_co_rate': Float(precision=53),
'cash_div': Float(precision=53),
'cash_div_tax': Float(precision=53), 'record_date': DATE(), 'ex_date': DATE(), 'pay_date': DATE(),
'div_listdate': VARCHAR(length=10),
'imp_ann_date': DATE(), 'base_date': DATE(), 'base_share': Float(precision=53)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'dividend'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
def _clean(self):
self._df = self._df[self._df['div_proc'].str.contains('实施')]
def _add_y_m(self):
self._add_y()
def _second_process(self):
table_name = self._interface + '_stat'
df = self._df
grouped = df.groupby('y')
r = grouped[['stk_div', 'cash_div']].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'})
r = r.sort_values(by=['y'], ascending=False)
data = {'ts_code': np.full((len(r)), self._key), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]}
df = pd.DataFrame(data)
dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)}
df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
class JobHistory(DeclarativeBase):
__tablename__ = 'job_history'
##{B:Columns}
id = Column(Integer, primary_key=True)
order = Column(Integer)
company_name = Column(Unicode(64))
job_title = Column(Unicode(64))
job_city = Column(Unicode(64))
job_state = Column(Unicode(48))
start_date = Column(DATE())
end_date = Column(DATE())
job_summary = Column(Unicode(4096))
tags = Column(Unicode(1024))
hidden = Column(Boolean())
userid = Column(Integer, ForeignKey('tg_user.user_id'))
job_bullets = relationship("JobPoints",
backref="job",
order_by="JobPoints.order")
projects = relationship("ProjectHistory", backref="job")
def init_stock_list_all():
print('start init list...')
fileds = 'ts_code,symbol,name,area,industry,fullname,market,exchange,curr_type,list_status,list_date,delist_date,is_hs'
d_l = __pro.stock_basic(exchange='', list_status='L', fields=fileds)
print('L', len(d_l))
d_d = __pro.stock_basic(exchange='', list_status='D', fields=fileds)
print('D', len(d_d))
d_p = __pro.stock_basic(exchange='', list_status='P', fields=fileds)
print('P', len(d_p))
df = d_l.append(d_d).append(d_p)
print('all size:', len(df))
dtype = {
'ts_code': VARCHAR(length=10),
'symbol': VARCHAR(length=8),
'name': VARCHAR(length=20),
'area': VARCHAR(length=10),
'industry': VARCHAR(length=32),
'fullname': VARCHAR(length=32),
'market': VARCHAR(length=10),
'exchange': VARCHAR(length=10),
'curr_type': VARCHAR(length=5),
'list_status': VARCHAR(length=1),
'list_date': DATE(),
'delist_date': DATE(),
'is_hs': VARCHAR(length=1)
}
# df.reset_index(drop=True)
# df = df.reindex(columns='ts_code,end_date,ex_date,div_proc,stk_div,cash_div'.split(','))
df.to_sql('stock_list',
get_engine(),
dtype=dtype,
index=False,
if_exists='replace')
print('finished init list.')
class SpecificSkills(DeclarativeBase):
__tablename__ = 'specific_skills'
##{B:Columns}
id = Column(Integer, primary_key=True)
order = Column(Integer)
name = Column(Unicode(64))
time_used = Column(Unicode(16))
last_used = Column(DATE())
proficiency = Column(Unicode(10))
tags = Column(Unicode(1024))
hidden = Column(Boolean())
skillgroupid = Column(Integer, ForeignKey('skill_groups.id'))
def _get_dtype(self, df=None):
if self._interface in __class__.type_mapping:
return __class__.type_mapping[self._interface]
df = df if df is not None else self._df
mapping = {}
for name, dt in df.dtypes.incomesems():
if dt is dtype('object'):
if '_date' in name:
mapping[name] = DATE()
continue
else:
max_length = df[name].apply(lambda x: 0 if x is None else len(x)).max()
mapping[name] = VARCHAR(length=max_length)
__class__.type_mapping[self._interface] = mapping
return mapping
def init_month_matrix_basic():
table_name = 'stock_month_matrix_basic'
sql = 'select * from trade_date where m != 0 ;'
yms = pd.read_sql_query(sql, get_engine())
df = None
for i, row in yms.iterrows():
first_trade_date_str = row['first'].strftime('%Y%m%d')
last_last_date_str = row['last'].strftime('%Y%m%d')
data = __pro.daily_basic(ts_code='', trade_date=last_last_date_str)
print(last_last_date_str)
if df is None:
df = data
else:
df = df.append(data)
df_add_y_m(df, 'trade_date')
df.reset_index(drop=True)
df = df.iloc[::-1]
dtype = {
'ts_code': VARCHAR(length=10),
'trade_date': DATE(),
'close': FLOAT(),
'y': INT(),
'm': INT(),
'turnover_rate': FLOAT(),
'turnover_rate_f': FLOAT(),
'volume_ratio': FLOAT(),
'pe': FLOAT(),
'pe_ttm': FLOAT(),
'pb': FLOAT(),
'ps': FLOAT(),
'ps_ttm': FLOAT(),
'dv_ratio': FLOAT(),
'dv_ttm': FLOAT(),
'total_share': FLOAT(),
'float_share': FLOAT(),
'free_share': FLOAT(),
'total_mv': FLOAT(),
'circ_mv': FLOAT()
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def _second_process(self):
table_name = self._interface + '_stat'
df = self._df
grouped = df.groupby('y')
r = grouped[['stk_div', 'cash_div']].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'})
r = r.sort_values(by=['y'], ascending=False)
data = {'ts_code': np.full((len(r)), self._key), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]}
df = pd.DataFrame(data)
dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)}
df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
def test_defaults(self):
assert self._comp(DATE()) == 'DATE'
assert self._comp(DATETIME()) == 'TIMESTAMP(6)'
assert self._comp(TIMESTAMP()) == 'TIMESTAMP(6)'
assert self._comp(TIME()) == 'TIME(6)'
assert self._comp(CHAR()) == 'CHAR(1)'
assert self._comp(VARCHAR()) == 'LONG VARCHAR'
assert self._comp(NCHAR()) == 'CHAR CHAR SET UNICODE'
assert self._comp(NVARCHAR()) == 'LONG VARCHAR CHAR SET UNICODE'
assert self._comp(CLOB()) == 'CLOB'
assert self._comp(TEXT()) == 'CLOB'
assert self._comp(DECIMAL()) == 'DECIMAL(5, 0)'
assert self._comp(NUMERIC()) == 'NUMERIC(5, 0)'
assert self._comp(INTEGER()) == 'INTEGER'
assert self._comp(FLOAT()) == 'FLOAT'
assert self._comp(REAL()) == 'REAL'
assert self._comp(SMALLINT()) == 'SMALLINT'
assert self._comp(BIGINT()) == 'BIGINT'
assert self._comp(BOOLEAN()) == 'BYTEINT'
r = grouped[['stk_div', 'cash_div']].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={('stk_div', 'sum'): 'stk_div', ('cash_div', 'sum'): 'cash_div', ('y'): 'y'})
r = r.sort_values(by=['y'], ascending=False)
data = {'ts_code': np.full((len(r)), self._biz_code), 'y': r['y'], 'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]}
df = pd.DataFrame(data)
dtype = {'ts_code': VARCHAR(length=10), 'end_date': DATE(), 'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8), 'cash_div': DECIMAL(precision=12, scale=8)}
df.to_sql(table_name, get_engine(), dtype=dtype, index=False, if_exists='append')
_type_mapping = {
'balancesheet': {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'end_date': DATE(),
'y': INT(),
'm': INT(),
'report_type': VARCHAR(length=1), 'comp_type': VARCHAR(length=1),
'total_share': Float(precision=53),
'cap_rese': Float(precision=53), 'undistr_porfit': Float(precision=53),
'surplus_rese': Float(precision=53),
'special_rese': Float(precision=53), 'money_cap': Float(precision=53),
'trad_asset': Float(precision=53),
'notes_receiv': Float(precision=53), 'accounts_receiv': Float(precision=53),
'oth_receiv': Float(precision=53),
'prepayment': Float(precision=53), 'div_receiv': Float(precision=53),
'int_receiv': Float(precision=53),
'inventories': Float(precision=53), 'amor_exp': Float(precision=53),
'nca_within_1y': Float(precision=53),
'sett_rsrv': Float(precision=53), 'loanto_oth_bank_fi': Float(precision=53),
def init_fina_indicator(ts_code, force=None):
table_name = 'stock_fina_indicator'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'end_date': DATE(),
'y': INT(),
'm': INT(),
'eps': FLOAT(),
'dt_eps': FLOAT(),
'total_revenue_ps': FLOAT(),
'revenue_ps': FLOAT(),
'capital_rese_ps': FLOAT(),
'surplus_rese_ps': FLOAT(),
'undist_profit_ps': FLOAT(),
'extra_item': FLOAT(),
'profit_dedt': FLOAT(),
'gross_margin': FLOAT(),
'current_ratio': FLOAT(),
'quick_ratio': FLOAT(),
'cash_ratio': FLOAT(),
'invturn_days': FLOAT(),
'arturn_days': FLOAT(),
'inv_turn': FLOAT(),
'ar_turn': FLOAT(),
'ca_turn': FLOAT(),
'fa_turn': FLOAT(),
'assets_turn': FLOAT(),
'op_income': FLOAT(),
'valuechange_income': FLOAT(),
'interst_income': FLOAT(),
'daa': FLOAT(),
'ebit': FLOAT(),
'ebitda': FLOAT(),
'fcff': FLOAT(),
'fcfe': FLOAT(),
'current_exint': FLOAT(),
'noncurrent_exint': FLOAT(),
'interestdebt': FLOAT(),
'netdebt': FLOAT(),
'tangible_asset': FLOAT(),
'working_capital': FLOAT(),
'networking_capital': FLOAT(),
'invest_capital': FLOAT(),
'retained_earnings': FLOAT(),
'diluted2_eps': FLOAT(),
'bps': FLOAT(),
'ocfps': FLOAT(),
'retainedps': FLOAT(),
'cfps': FLOAT(),
'ebit_ps': FLOAT(),
'fcff_ps': FLOAT(),
'fcfe_ps': FLOAT(),
'netprofit_margin': FLOAT(),
'grossprofit_margin': FLOAT(),
'cogs_of_sales': FLOAT(),
'expense_of_sales': FLOAT(),
'profit_to_gr': FLOAT(),
'saleexp_to_gr': FLOAT(),
'adminexp_of_gr': FLOAT(),
'finaexp_of_gr': FLOAT(),
'impai_ttm': FLOAT(),
'gc_of_gr': FLOAT(),
'op_of_gr': FLOAT(),
'ebit_of_gr': FLOAT(),
'roe': FLOAT(),
'roe_waa': FLOAT(),
'roe_dt': FLOAT(),
'roa': FLOAT(),
'npta': FLOAT(),
'roic': FLOAT(),
'roe_yearly': FLOAT(),
'roa2_yearly': FLOAT(),
'roe_avg': FLOAT(),
'opincome_of_ebt': FLOAT(),
'investincome_of_ebt': FLOAT(),
'n_op_profit_of_ebt': FLOAT(),
'tax_to_ebt': FLOAT(),
'dtprofit_to_profit': FLOAT(),
'salescash_to_or': FLOAT(),
'ocf_to_or': FLOAT(),
'ocf_to_opincome': FLOAT(),
'capitalized_to_da': FLOAT(),
'debt_to_assets': FLOAT(),
'assets_to_eqt': FLOAT(),
'dp_assets_to_eqt': FLOAT(),
'ca_to_assets': FLOAT(),
'nca_to_assets': FLOAT(),
'tbassets_to_totalassets': FLOAT(),
'int_to_talcap': FLOAT(),
'eqt_to_talcapital': FLOAT(),
'currentdebt_to_debt': FLOAT(),
'longdeb_to_debt': FLOAT(),
'ocf_to_shortdebt': FLOAT(),
'debt_to_eqt': FLOAT(),
'eqt_to_debt': FLOAT(),
'eqt_to_interestdebt': FLOAT(),
'tangibleasset_to_debt': FLOAT(),
'tangasset_to_intdebt': FLOAT(),
'tangibleasset_to_netdebt': FLOAT(),
'ocf_to_debt': FLOAT(),
'ocf_to_interestdebt': FLOAT(),
'ocf_to_netdebt': FLOAT(),
'ebit_to_interest': FLOAT(),
'longdebt_to_workingcapital': FLOAT(),
'ebitda_to_debt': FLOAT(),
'turn_days': FLOAT(),
'roa_yearly': FLOAT(),
'roa_dp': FLOAT(),
'fixed_assets': FLOAT(),
'profit_prefin_exp': FLOAT(),
'non_op_profit': FLOAT(),
'op_to_ebt': FLOAT(),
'nop_to_ebt': FLOAT(),
'ocf_to_profit': FLOAT(),
'cash_to_liqdebt': FLOAT(),
'cash_to_liqdebt_withinterest': FLOAT(),
'op_to_liqdebt': FLOAT(),
'op_to_debt': FLOAT(),
'roic_yearly': FLOAT(),
'total_fa_trun': FLOAT(),
'profit_to_op': FLOAT(),
'q_opincome': FLOAT(),
'q_investincome': FLOAT(),
'q_dtprofit': FLOAT(),
'q_eps': FLOAT(),
'q_netprofit_margin': FLOAT(),
'q_gsprofit_margin': FLOAT(),
'q_exp_to_sales': FLOAT(),
'q_profit_to_gr': FLOAT(),
'q_saleexp_to_gr': FLOAT(),
'q_adminexp_to_gr': FLOAT(),
'q_finaexp_to_gr': FLOAT(),
'q_impair_to_gr_ttm': FLOAT(),
'q_gc_to_gr': FLOAT(),
'q_op_to_gr': FLOAT(),
'q_roe': FLOAT(),
'q_dt_roe': FLOAT(),
'q_npta': FLOAT(),
'q_opincome_to_ebt': FLOAT(),
'q_investincome_to_ebt': FLOAT(),
'q_dtprofit_to_profit': FLOAT(),
'q_salescash_to_or': FLOAT(),
'q_ocf_to_sales': FLOAT(),
'q_ocf_to_or': FLOAT(),
'basic_eps_yoy': FLOAT(),
'dt_eps_yoy': FLOAT(),
'cfps_yoy': FLOAT(),
'op_yoy': FLOAT(),
'ebt_yoy': FLOAT(),
'netprofit_yoy': FLOAT(),
'dt_netprofit_yoy': FLOAT(),
'ocf_yoy': FLOAT(),
'roe_yoy': FLOAT(),
'bps_yoy': FLOAT(),
'assets_yoy': FLOAT(),
'eqt_yoy': FLOAT(),
'tr_yoy': FLOAT(),
'or_yoy': FLOAT(),
'q_gr_yoy': FLOAT(),
'q_gr_qoq': FLOAT(),
'q_sales_yoy': FLOAT(),
'q_sales_qoq': FLOAT(),
'q_op_yoy': FLOAT(),
'q_op_qoq': FLOAT(),
'q_profit_yoy': FLOAT(),
'q_profit_qoq': FLOAT(),
'q_netprofit_yoy': FLOAT(),
'q_netprofit_qoq': FLOAT(),
'equity_yoy': FLOAT(),
'rd_exp': FLOAT(),
'update_flag': VARCHAR(length=1)
}
columns = 'ts_code,ann_date,end_date,eps,dt_eps,total_revenue_ps,revenue_ps,capital_rese_ps,surplus_rese_ps,undist_profit_ps,extra_item,profit_dedt,gross_margin,current_ratio,quick_ratio,cash_ratio,invturn_days,arturn_days,inv_turn,ar_turn,ca_turn,fa_turn,assets_turn,op_income,valuechange_income,interst_income,daa,ebit,ebitda,fcff,fcfe,current_exint,noncurrent_exint,interestdebt,netdebt,tangible_asset,working_capital,networking_capital,invest_capital,retained_earnings,diluted2_eps,bps,ocfps,retainedps,cfps,ebit_ps,fcff_ps,fcfe_ps,netprofit_margin,grossprofit_margin,cogs_of_sales,expense_of_sales,profit_to_gr,saleexp_to_gr,adminexp_of_gr,finaexp_of_gr,impai_ttm,gc_of_gr,op_of_gr,ebit_of_gr,roe,roe_waa,roe_dt,roa,npta,roic,roe_yearly,roa2_yearly,roe_avg,opincome_of_ebt,investincome_of_ebt,n_op_profit_of_ebt,tax_to_ebt,dtprofit_to_profit,salescash_to_or,ocf_to_or,ocf_to_opincome,capitalized_to_da,debt_to_assets,assets_to_eqt,dp_assets_to_eqt,ca_to_assets,nca_to_assets,tbassets_to_totalassets,int_to_talcap,eqt_to_talcapital,currentdebt_to_debt,longdeb_to_debt,ocf_to_shortdebt,debt_to_eqt,eqt_to_debt,eqt_to_interestdebt,tangibleasset_to_debt,tangasset_to_intdebt,tangibleasset_to_netdebt,ocf_to_debt,ocf_to_interestdebt,ocf_to_netdebt,ebit_to_interest,longdebt_to_workingcapital,ebitda_to_debt,turn_days,roa_yearly,roa_dp,fixed_assets,profit_prefin_exp,non_op_profit,op_to_ebt,nop_to_ebt,ocf_to_profit,cash_to_liqdebt,cash_to_liqdebt_withinterest,op_to_liqdebt,op_to_debt,roic_yearly,total_fa_trun,profit_to_op,q_opincome,q_investincome,q_dtprofit,q_eps,q_netprofit_margin,q_gsprofit_margin,q_exp_to_sales,q_profit_to_gr,q_saleexp_to_gr,q_adminexp_to_gr,q_finaexp_to_gr,q_impair_to_gr_ttm,q_gc_to_gr,q_op_to_gr,q_roe,q_dt_roe,q_npta,q_opincome_to_ebt,q_investincome_to_ebt,q_dtprofit_to_profit,q_salescash_to_or,q_ocf_to_sales,q_ocf_to_or,basic_eps_yoy,dt_eps_yoy,cfps_yoy,op_yoy,ebt_yoy,netprofit_yoy,dt_netprofit_yoy,ocf_yoy,roe_yoy,bps_yoy,assets_yoy,eqt_yoy,tr_yoy,or_yoy,q_gr_yoy,q_gr_qoq,q_sales_yoy,q_sales_qoq,q_op_yoy,q_op_qoq,q_profit_yoy,q_profit_qoq,q_netprofit_yoy,q_netprofit_qoq,equity_yoy,rd_exp,update_flag'
df = __pro.fina_indicator(ts_code=ts_code,
start_date='19901201',
end_date='20210101',
columns=columns)
if len(df) == 0:
print('=' * 32, 'code:{}'.format(ts_code))
print('error exit middle')
exit(4)
# clean
# df = df.drop_duplicates(["end_date"], keep="first")
df = drop_more_nan_row(df, 'end_date')
df_add_y_m(df, 'end_date')
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
class BalanceSheet(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'f_ann_date': DATE(), 'end_date': DATE(), 'y': INT(),
'm': INT(),
'report_type': VARCHAR(length=1), 'comp_type': VARCHAR(length=1), 'total_share': Float(precision=53),
'cap_rese': Float(precision=53), 'undistr_porfit': Float(precision=53),
'surplus_rese': Float(precision=53),
'special_rese': Float(precision=53), 'money_cap': Float(precision=53), 'trad_asset': Float(precision=53),
'notes_receiv': Float(precision=53), 'accounts_receiv': Float(precision=53),
'oth_receiv': Float(precision=53),
'prepayment': Float(precision=53), 'div_receiv': Float(precision=53), 'int_receiv': Float(precision=53),
'inventories': Float(precision=53), 'amor_exp': Float(precision=53), 'nca_within_1y': Float(precision=53),
'sett_rsrv': Float(precision=53), 'loanto_oth_bank_fi': Float(precision=53),
'premium_receiv': Float(precision=53),
'reinsur_receiv': Float(precision=53), 'reinsur_res_receiv': Float(precision=53),
'pur_resale_fa': Float(precision=53),
'oth_cur_assets': Float(precision=53), 'total_cur_assets': Float(precision=53),
'fa_avail_for_sale': Float(precision=53),
'htm_invest': Float(precision=53), 'lt_eqt_invest': Float(precision=53),
'invest_real_estate': Float(precision=53),
'time_deposits': Float(precision=53), 'oth_assets': Float(precision=53), 'lt_rec': Float(precision=53),
'fix_assets': Float(precision=53), 'cip': Float(precision=53), 'const_materials': Float(precision=53),
'fixed_assets_disp': Float(precision=53), 'produc_bio_assets': Float(precision=53),
'oil_and_gas_assets': Float(precision=53),
'intan_assets': Float(precision=53), 'r_and_d': Float(precision=53), 'goodwill': Float(precision=53),
'lt_amor_exp': Float(precision=53), 'defer_tax_assets': Float(precision=53),
'decr_in_disbur': Float(precision=53),
'oth_nca': Float(precision=53), 'total_nca': Float(precision=53), 'cash_reser_cb': Float(precision=53),
'depos_in_oth_bfi': Float(precision=53), 'prec_metals': Float(precision=53),
'deriv_assets': Float(precision=53),
'rr_reins_une_prem': Float(precision=53), 'rr_reins_outstd_cla': Float(precision=53),
'rr_reins_lins_liab': Float(precision=53), 'rr_reins_lthins_liab': Float(precision=53),
'refund_depos': Float(precision=53),
'ph_pledge_loans': Float(precision=53), 'refund_cap_depos': Float(precision=53),
'indep_acct_assets': Float(precision=53),
'client_depos': Float(precision=53), 'client_prov': Float(precision=53),
'transac_seat_fee': Float(precision=53),
'invest_as_receiv': Float(precision=53), 'total_assets': Float(precision=53),
'lt_borr': Float(precision=53),
'st_borr': Float(precision=53), 'cb_borr': Float(precision=53), 'depos_ib_deposits': Float(precision=53),
'loan_oth_bank': Float(precision=53), 'trading_fl': Float(precision=53),
'notes_payable': Float(precision=53),
'acct_payable': Float(precision=53), 'adv_receipts': Float(precision=53),
'sold_for_repur_fa': Float(precision=53),
'comm_payable': Float(precision=53), 'payroll_payable': Float(precision=53),
'taxes_payable': Float(precision=53),
'int_payable': Float(precision=53), 'div_payable': Float(precision=53), 'oth_payable': Float(precision=53),
'acc_exp': Float(precision=53), 'deferred_inc': Float(precision=53),
'st_bonds_payable': Float(precision=53),
'payable_to_reinsurer': Float(precision=53), 'rsrv_insur_cont': Float(precision=53),
'acting_trading_sec': Float(precision=53),
'acting_uw_sec': Float(precision=53), 'non_cur_liab_due_1y': Float(precision=53),
'oth_cur_liab': Float(precision=53),
'total_cur_liab': Float(precision=53), 'bond_payable': Float(precision=53),
'lt_payable': Float(precision=53),
'specific_payables': Float(precision=53), 'estimated_liab': Float(precision=53),
'defer_tax_liab': Float(precision=53),
'defer_inc_non_cur_liab': Float(precision=53), 'oth_ncl': Float(precision=53),
'total_ncl': Float(precision=53),
'depos_oth_bfi': Float(precision=53), 'deriv_liab': Float(precision=53), 'depos': Float(precision=53),
'agency_bus_liab': Float(precision=53), 'oth_liab': Float(precision=53),
'prem_receiv_adva': Float(precision=53),
'depos_received': Float(precision=53), 'ph_invest': Float(precision=53),
'reser_une_prem': Float(precision=53),
'reser_outstd_claims': Float(precision=53), 'reser_lins_liab': Float(precision=53),
'reser_lthins_liab': Float(precision=53),
'indept_acc_liab': Float(precision=53), 'pledge_borr': Float(precision=53),
'indem_payable': Float(precision=53),
'policy_div_payable': Float(precision=53), 'total_liab': Float(precision=53),
'treasury_share': Float(precision=53),
'ordin_risk_reser': Float(precision=53), 'forex_differ': Float(precision=53),
'invest_loss_unconf': Float(precision=53),
'minority_int': Float(precision=53), 'total_hldr_eqy_exc_min_int': Float(precision=53),
'total_hldr_eqy_inc_min_int': Float(precision=53), 'total_liab_hldr_eqy': Float(precision=53),
'lt_payroll_payable': Float(precision=53), 'oth_comp_income': Float(precision=53),
'oth_eqt_tools': Float(precision=53),
'oth_eqt_tools_p_shr': Float(precision=53), 'lending_funds': Float(precision=53),
'acc_receivable': Float(precision=53),
'st_fin_payable': Float(precision=53), 'payables': Float(precision=53), 'hfs_assets': Float(precision=53),
'hfs_sales': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'balancesheet'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
def init_income(ts_code, force=None):
table_name = 'stock_income'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} '.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'f_ann_date': DATE(),
'y': INT(),
'm': INT(),
'end_date': DATE(),
'report_type': VARCHAR(length=1),
'comp_type': VARCHAR(length=1),
'basic_eps': FLOAT(),
'diluted_eps': FLOAT(),
'total_revenue': FLOAT(),
'revenue': FLOAT(),
'int_income': FLOAT(),
'prem_earned': FLOAT(),
'comm_income': FLOAT(),
'n_commis_income': FLOAT(),
'n_oth_income': FLOAT(),
'n_oth_b_income': FLOAT(),
'prem_income': FLOAT(),
'out_prem': FLOAT(),
'une_prem_reser': FLOAT(),
'reins_income': FLOAT(),
'n_sec_tb_income': FLOAT(),
'n_sec_uw_income': FLOAT(),
'n_asset_mg_income': FLOAT(),
'oth_b_income': FLOAT(),
'fv_value_chg_gain': FLOAT(),
'invest_income': FLOAT(),
'ass_invest_income': FLOAT(),
'forex_gain': FLOAT(),
'total_cogs': FLOAT(),
'oper_cost': FLOAT(),
'int_exp': FLOAT(),
'comm_exp': FLOAT(),
'biz_tax_surchg': FLOAT(),
'sell_exp': FLOAT(),
'admin_exp': FLOAT(),
'fin_exp': FLOAT(),
'assets_impair_loss': FLOAT(),
'prem_refund': FLOAT(),
'compens_payout': FLOAT(),
'reser_insur_liab': FLOAT(),
'div_payt': FLOAT(),
'reins_exp': FLOAT(),
'oper_exp': FLOAT(),
'compens_payout_refu': FLOAT(),
'insur_reser_refu': FLOAT(),
'reins_cost_refund': FLOAT(),
'other_bus_cost': FLOAT(),
'operate_profit': FLOAT(),
'non_oper_income': FLOAT(),
'non_oper_exp': FLOAT(),
'nca_disploss': FLOAT(),
'total_profit': FLOAT(),
'income_tax': FLOAT(),
'n_income': FLOAT(),
'n_income_attr_p': FLOAT(),
'minority_gain': FLOAT(),
'oth_compr_income': FLOAT(),
't_compr_income': FLOAT(),
'compr_inc_attr_p': FLOAT(),
'compr_inc_attr_m_s': FLOAT(),
'ebit': FLOAT(),
'ebitda': FLOAT(),
'insurance_exp': FLOAT(),
'undist_profit': FLOAT(),
'distable_profit': FLOAT(),
'update_flag': VARCHAR(length=1)
}
df = __pro.income(ts_code=ts_code,
start_date='19901201',
end_date='20210101')
# clean
df = drop_more_nan_row(df, 'end_date')
# format
df_add_y_m(df, 'end_date')
#
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_cashflow(ts_code, force=None):
table_name = 'stock_cashflow'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'f_ann_date': DATE(),
'y': INT(),
'm': INT(),
'end_date': DATE(),
'comp_type': VARCHAR(length=1),
'report_type': VARCHAR(length=1),
'net_profit': BIGINT(),
'finan_exp': BIGINT(),
'c_fr_sale_sg': BIGINT(),
'recp_tax_rends': BIGINT(),
'n_depos_incr_fi': BIGINT(),
'n_incr_loans_cb': BIGINT(),
'n_inc_borr_oth_fi': BIGINT(),
'prem_fr_orig_contr': BIGINT(),
'n_incr_insured_dep': BIGINT(),
'n_reinsur_prem': BIGINT(),
'n_incr_disp_tfa': BIGINT(),
'ifc_cash_incr': BIGINT(),
'n_incr_disp_faas': BIGINT(),
'n_incr_loans_oth_bank': BIGINT(),
'n_cap_incr_repur': BIGINT(),
'c_fr_oth_operate_a': BIGINT(),
'c_inf_fr_operate_a': BIGINT(),
'c_paid_goods_s': BIGINT(),
'c_paid_to_for_empl': BIGINT(),
'c_paid_for_taxes': BIGINT(),
'n_incr_clt_loan_adv': BIGINT(),
'n_incr_dep_cbob': BIGINT(),
'c_pay_claims_orig_inco': BIGINT(),
'pay_handling_chrg': BIGINT(),
'pay_comm_insur_plcy': BIGINT(),
'oth_cash_pay_oper_act': BIGINT(),
'st_cash_out_act': BIGINT(),
'n_cashflow_act': BIGINT(),
'oth_recp_ral_inv_act': BIGINT(),
'c_disp_withdrwl_invest': BIGINT(),
'c_recp_return_invest': BIGINT(),
'n_recp_disp_fiolta': BIGINT(),
'n_recp_disp_sobu': BIGINT(),
'stot_inflows_inv_act': BIGINT(),
'c_pay_acq_const_fiolta': BIGINT(),
'c_paid_invest': BIGINT(),
'n_disp_subs_oth_biz': BIGINT(),
'oth_pay_ral_inv_act': BIGINT(),
'n_incr_pledge_loan': BIGINT(),
'stot_out_inv_act': BIGINT(),
'n_cashflow_inv_act': BIGINT(),
'c_recp_borrow': BIGINT(),
'proc_issue_bonds': BIGINT(),
'oth_cash_recp_ral_fnc_act': BIGINT(),
'stot_cash_in_fnc_act': BIGINT(),
'free_cashflow': BIGINT(),
'c_prepay_amt_borr': BIGINT(),
'c_pay_dist_dpcp_int_exp': BIGINT(),
'incl_dvd_profit_paid_sc_ms': BIGINT(),
'oth_cashpay_ral_fnc_act': BIGINT(),
'stot_cashout_fnc_act': BIGINT(),
'n_cash_flows_fnc_act': BIGINT(),
'eff_fx_flu_cash': BIGINT(),
'n_incr_cash_cash_equ': BIGINT(),
'c_cash_equ_beg_period': BIGINT(),
'c_cash_equ_end_period': BIGINT(),
'c_recp_cap_contrib': BIGINT(),
'incl_cash_rec_saims': BIGINT(),
'uncon_invest_loss': BIGINT(),
'prov_depr_assets': BIGINT(),
'depr_fa_coga_dpba': BIGINT(),
'amort_intang_assets': BIGINT(),
'lt_amort_deferred_exp': BIGINT(),
'decr_deferred_exp': BIGINT(),
'incr_acc_exp': BIGINT(),
'loss_disp_fiolta': BIGINT(),
'loss_scr_fa': BIGINT(),
'loss_fv_chg': BIGINT(),
'invest_loss': BIGINT(),
'decr_def_inc_tax_assets': BIGINT(),
'incr_def_inc_tax_liab': BIGINT(),
'decr_inventories': BIGINT(),
'decr_oper_payable': BIGINT(),
'incr_oper_payable': BIGINT(),
'others': BIGINT(),
'im_net_cashflow_oper_act': BIGINT(),
'conv_debt_into_cap': BIGINT(),
'conv_copbonds_due_within_1y': BIGINT(),
'fa_fnc_leases': BIGINT(),
'end_bal_cash': BIGINT(),
'beg_bal_cash': BIGINT(),
'end_bal_cash_equ': BIGINT(),
'beg_bal_cash_equ': BIGINT(),
'im_n_incr_cash_equ': BIGINT()
}
df = __pro.cashflow(ts_code=ts_code,
start_date='19901201',
end_date='20210101')
# clean
# df = df.drop_duplicates(["end_date"], keep="first")
df = drop_more_nan_row(df, 'end_date')
df_add_y_m(df, 'end_date')
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_balancesheet(ts_code, force=None):
table_name = 'stock_balancesheet'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
dtype = {
'ts_code': VARCHAR(length=10),
'ann_date': DATE(),
'f_ann_date': DATE(),
'y': INT(),
'm': INT(),
'end_date': DATE(),
'report_type': VARCHAR(length=1),
'comp_type': VARCHAR(length=1),
'total_share': BIGINT(),
'cap_rese': BIGINT(),
'undistr_porfit': BIGINT(),
'surplus_rese': BIGINT(),
'special_rese': BIGINT(),
'money_cap': BIGINT(),
'trad_asset': BIGINT(),
'notes_receiv': BIGINT(),
'accounts_receiv': BIGINT(),
'oth_receiv': BIGINT(),
'prepayment': BIGINT(),
'div_receiv': BIGINT(),
'int_receiv': BIGINT(),
'inventories': BIGINT(),
'amor_exp': BIGINT(),
'nca_within_1y': BIGINT(),
'sett_rsrv': BIGINT(),
'loanto_oth_bank_fi': BIGINT(),
'premium_receiv': BIGINT(),
'reinsur_receiv': BIGINT(),
'reinsur_res_receiv': BIGINT(),
'pur_resale_fa': BIGINT(),
'oth_cur_assets': BIGINT(),
'total_cur_assets': BIGINT(),
'fa_avail_for_sale': BIGINT(),
'htm_invest': BIGINT(),
'lt_eqt_invest': BIGINT(),
'invest_real_estate': BIGINT(),
'time_deposits': BIGINT(),
'oth_assets': BIGINT(),
'lt_rec': BIGINT(),
'fix_assets': BIGINT(),
'cip': BIGINT(),
'const_materials': BIGINT(),
'fixed_assets_disp': BIGINT(),
'produc_bio_assets': BIGINT(),
'oil_and_gas_assets': BIGINT(),
'intan_assets': BIGINT(),
'r_and_d': BIGINT(),
'goodwill': BIGINT(),
'lt_amor_exp': BIGINT(),
'defer_tax_assets': BIGINT(),
'decr_in_disbur': BIGINT(),
'oth_nca': BIGINT(),
'total_nca': BIGINT(),
'cash_reser_cb': BIGINT(),
'depos_in_oth_bfi': BIGINT(),
'prec_metals': BIGINT(),
'deriv_assets': BIGINT(),
'rr_reins_une_prem': BIGINT(),
'rr_reins_outstd_cla': BIGINT(),
'rr_reins_lins_liab': BIGINT(),
'rr_reins_lthins_liab': BIGINT(),
'refund_depos': BIGINT(),
'ph_pledge_loans': BIGINT(),
'refund_cap_depos': BIGINT(),
'indep_acct_assets': BIGINT(),
'client_depos': BIGINT(),
'client_prov': BIGINT(),
'transac_seat_fee': BIGINT(),
'invest_as_receiv': BIGINT(),
'total_assets': BIGINT(),
'lt_borr': BIGINT(),
'st_borr': BIGINT(),
'cb_borr': BIGINT(),
'depos_ib_deposits': BIGINT(),
'loan_oth_bank': BIGINT(),
'trading_fl': BIGINT(),
'notes_payable': BIGINT(),
'acct_payable': BIGINT(),
'adv_receipts': BIGINT(),
'sold_for_repur_fa': BIGINT(),
'comm_payable': BIGINT(),
'payroll_payable': BIGINT(),
'taxes_payable': BIGINT(),
'int_payable': BIGINT(),
'div_payable': BIGINT(),
'oth_payable': BIGINT(),
'acc_exp': BIGINT(),
'deferred_inc': BIGINT(),
'st_bonds_payable': BIGINT(),
'payable_to_reinsurer': BIGINT(),
'rsrv_insur_cont': BIGINT(),
'acting_trading_sec': BIGINT(),
'acting_uw_sec': BIGINT(),
'non_cur_liab_due_1y': BIGINT(),
'oth_cur_liab': BIGINT(),
'total_cur_liab': BIGINT(),
'bond_payable': BIGINT(),
'lt_payable': BIGINT(),
'specific_payables': BIGINT(),
'estimated_liab': BIGINT(),
'defer_tax_liab': BIGINT(),
'defer_inc_non_cur_liab': BIGINT(),
'oth_ncl': BIGINT(),
'total_ncl': BIGINT(),
'depos_oth_bfi': BIGINT(),
'deriv_liab': BIGINT(),
'depos': BIGINT(),
'agency_bus_liab': BIGINT(),
'oth_liab': BIGINT(),
'prem_receiv_adva': BIGINT(),
'depos_received': BIGINT(),
'ph_invest': BIGINT(),
'reser_une_prem': BIGINT(),
'reser_outstd_claims': BIGINT(),
'reser_lins_liab': BIGINT(),
'reser_lthins_liab': BIGINT(),
'indept_acc_liab': BIGINT(),
'pledge_borr': BIGINT(),
'indem_payable': BIGINT(),
'policy_div_payable': BIGINT(),
'total_liab': BIGINT(),
'treasury_share': BIGINT(),
'ordin_risk_reser': BIGINT(),
'forex_differ': BIGINT(),
'invest_loss_unconf': BIGINT(),
'minority_int': BIGINT(),
'total_hldr_eqy_exc_min_int': BIGINT(),
'total_hldr_eqy_inc_min_int': BIGINT(),
'total_liab_hldr_eqy': BIGINT(),
'lt_payroll_payable': BIGINT(),
'oth_comp_income': BIGINT(),
'oth_eqt_tools': BIGINT(),
'oth_eqt_tools_p_shr': BIGINT(),
'lending_funds': BIGINT(),
'acc_receivable': BIGINT(),
'st_fin_payable': BIGINT(),
'payables': BIGINT(),
'hfs_assets': BIGINT(),
'hfs_sales': BIGINT(),
'update_flag': VARCHAR(length=1)
}
# call
df = __pro.balancesheet(ts_code=ts_code,
start_date='19901201',
end_date='20210101')
# clean
# df = df.drop_duplicates(["end_date"], keep="first")
df = drop_more_nan_row(df, 'end_date')
# format
df_add_y_m(df, 'end_date')
df.reset_index(drop=True)
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
def init_dividend(ts_code, force=None):
table_name = 'stock_dividend_detail'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
fields = 'ts_code,end_date,ann_date,div_proc,stk_div,stk_bo_rate,stk_co_rate,cash_div,cash_div_tax,record_date,ex_date,pay_date,div_listdate,imp_ann_date,base_date,base_share'
df = __pro.dividend(ts_code=ts_code, fields=fields)
df = df[df['div_proc'].str.contains('实施')]
df_add_y(df, 'end_date')
df.reset_index(drop=True)
dtype = {
'ts_code': VARCHAR(length=10),
'end_date': DATE(),
'div_proc': VARCHAR(length=10),
'stk_div': DECIMAL(precision=10, scale=8),
'cash_div': DECIMAL(precision=12, scale=8),
'ex_date': DATE(),
'y': INT()
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
'''
statistical
'''
table_name = 'stock_dividend'
if not need_pull_check(ts_code, table_name, force):
print('need not 2 pull {} -> {}'.format(table_name, ts_code))
return
else:
print('start 2 pull {} -> {} .'.format(table_name, ts_code))
grouped = df.groupby('y')
r = grouped['stk_div', 'cash_div'].agg([np.sum])
r = r.reset_index()
r = r.rename(columns={
('stk_div', 'sum'): 'stk_div',
('cash_div', 'sum'): 'cash_div',
('y'): 'y'
})
r = r.sort_values(by=['y'], ascending=False)
data = {
'ts_code': np.full((len(r)), ts_code),
'y': r['y'],
'stk_div': r[('stk_div', 'sum')],
'cash_div': r[('cash_div', 'sum')]
}
df = pd.DataFrame(data)
dtype = {
'ts_code': VARCHAR(length=10),
'end_date': DATE(),
'y': INT(),
'stk_div': DECIMAL(precision=10, scale=8),
'cash_div': DECIMAL(precision=12, scale=8)
}
df.to_sql(table_name,
get_engine(),
dtype=dtype,
index=False,
if_exists='append')
class FinaIndicator(CodeDao):
DTYPE = {'ts_code': VARCHAR(length=10), 'ann_date': DATE(), 'end_date': DATE(), 'y': INT(), 'm': INT(),
'eps': Float(precision=53),
'dt_eps': Float(precision=53), 'total_revenue_ps': Float(precision=53), 'revenue_ps': Float(precision=53),
'capital_rese_ps': Float(precision=53), 'surplus_rese_ps': Float(precision=53),
'undist_profit_ps': Float(precision=53),
'extra_item': Float(precision=53), 'profit_dedt': Float(precision=53), 'gross_margin': Float(precision=53),
'current_ratio': Float(precision=53), 'quick_ratio': Float(precision=53),
'cash_ratio': Float(precision=53),
'invturn_days': Float(precision=53), 'arturn_days': Float(precision=53), 'inv_turn': Float(precision=53),
'ar_turn': Float(precision=53), 'ca_turn': Float(precision=53), 'fa_turn': Float(precision=53),
'assets_turn': Float(precision=53), 'op_income': Float(precision=53),
'valuechange_income': Float(precision=53),
'interst_income': Float(precision=53), 'daa': Float(precision=53), 'ebit': Float(precision=53),
'ebitda': Float(precision=53),
'fcff': Float(precision=53), 'fcfe': Float(precision=53), 'current_exint': Float(precision=53),
'noncurrent_exint': Float(precision=53), 'interestdebt': Float(precision=53),
'netdebt': Float(precision=53),
'tangible_asset': Float(precision=53), 'working_capital': Float(precision=53),
'networking_capital': Float(precision=53),
'invest_capital': Float(precision=53), 'retained_earnings': Float(precision=53),
'diluted2_eps': Float(precision=53),
'bps': Float(precision=53), 'ocfps': Float(precision=53), 'retainedps': Float(precision=53),
'cfps': Float(precision=53),
'ebit_ps': Float(precision=53), 'fcff_ps': Float(precision=53), 'fcfe_ps': Float(precision=53),
'netprofit_margin': Float(precision=53), 'grossprofit_margin': Float(precision=53),
'cogs_of_sales': Float(precision=53),
'expense_of_sales': Float(precision=53), 'profit_to_gr': Float(precision=53),
'saleexp_to_gr': Float(precision=53),
'adminexp_of_gr': Float(precision=53), 'finaexp_of_gr': Float(precision=53),
'impai_ttm': Float(precision=53),
'gc_of_gr': Float(precision=53), 'op_of_gr': Float(precision=53), 'ebit_of_gr': Float(precision=53),
'roe': Float(precision=53), 'roe_waa': Float(precision=53), 'roe_dt': Float(precision=53),
'roa': Float(precision=53),
'npta': Float(precision=53), 'roic': Float(precision=53), 'roe_yearly': Float(precision=53),
'roa2_yearly': Float(precision=53), 'roe_avg': Float(precision=53), 'opincome_of_ebt': Float(precision=53),
'investincome_of_ebt': Float(precision=53), 'n_op_profit_of_ebt': Float(precision=53),
'tax_to_ebt': Float(precision=53),
'dtprofit_to_profit': Float(precision=53), 'salescash_to_or': Float(precision=53),
'ocf_to_or': Float(precision=53),
'ocf_to_opincome': Float(precision=53), 'capitalized_to_da': Float(precision=53),
'debt_to_assets': Float(precision=53),
'assets_to_eqt': Float(precision=53), 'dp_assets_to_eqt': Float(precision=53),
'ca_to_assets': Float(precision=53),
'nca_to_assets': Float(precision=53), 'tbassets_to_totalassets': Float(precision=53),
'int_to_talcap': Float(precision=53),
'eqt_to_talcapital': Float(precision=53), 'currentdebt_to_debt': Float(precision=53),
'longdeb_to_debt': Float(precision=53),
'ocf_to_shortdebt': Float(precision=53), 'debt_to_eqt': Float(precision=53),
'eqt_to_debt': Float(precision=53),
'eqt_to_interestdebt': Float(precision=53), 'tangibleasset_to_debt': Float(precision=53),
'tangasset_to_intdebt': Float(precision=53), 'tangibleasset_to_netdebt': Float(precision=53),
'ocf_to_debt': Float(precision=53), 'ocf_to_interestdebt': Float(precision=53),
'ocf_to_netdebt': Float(precision=53),
'ebit_to_interest': Float(precision=53), 'longdebt_to_workingcapital': Float(precision=53),
'ebitda_to_debt': Float(precision=53), 'turn_days': Float(precision=53), 'roa_yearly': Float(precision=53),
'roa_dp': Float(precision=53), 'fixed_assets': Float(precision=53),
'profit_prefin_exp': Float(precision=53),
'non_op_profit': Float(precision=53), 'op_to_ebt': Float(precision=53), 'nop_to_ebt': Float(precision=53),
'ocf_to_profit': Float(precision=53), 'cash_to_liqdebt': Float(precision=53),
'cash_to_liqdebt_withinterest': Float(precision=53), 'op_to_liqdebt': Float(precision=53),
'op_to_debt': Float(precision=53),
'roic_yearly': Float(precision=53), 'total_fa_trun': Float(precision=53),
'profit_to_op': Float(precision=53),
'q_opincome': Float(precision=53), 'q_investincome': Float(precision=53),
'q_dtprofit': Float(precision=53),
'q_eps': Float(precision=53), 'q_netprofit_margin': Float(precision=53),
'q_gsprofit_margin': Float(precision=53),
'q_exp_to_sales': Float(precision=53), 'q_profit_to_gr': Float(precision=53),
'q_saleexp_to_gr': Float(precision=53),
'q_adminexp_to_gr': Float(precision=53), 'q_finaexp_to_gr': Float(precision=53),
'q_impair_to_gr_ttm': Float(precision=53),
'q_gc_to_gr': Float(precision=53), 'q_op_to_gr': Float(precision=53), 'q_roe': Float(precision=53),
'q_dt_roe': Float(precision=53), 'q_npta': Float(precision=53), 'q_opincome_to_ebt': Float(precision=53),
'q_investincome_to_ebt': Float(precision=53), 'q_dtprofit_to_profit': Float(precision=53),
'q_salescash_to_or': Float(precision=53), 'q_ocf_to_sales': Float(precision=53),
'q_ocf_to_or': Float(precision=53),
'basic_eps_yoy': Float(precision=53), 'dt_eps_yoy': Float(precision=53), 'cfps_yoy': Float(precision=53),
'op_yoy': Float(precision=53), 'ebt_yoy': Float(precision=53), 'netprofit_yoy': Float(precision=53),
'dt_netprofit_yoy': Float(precision=53), 'ocf_yoy': Float(precision=53), 'roe_yoy': Float(precision=53),
'bps_yoy': Float(precision=53), 'assets_yoy': Float(precision=53), 'eqt_yoy': Float(precision=53),
'tr_yoy': Float(precision=53), 'or_yoy': Float(precision=53), 'q_gr_yoy': Float(precision=53),
'q_gr_qoq': Float(precision=53),
'q_sales_yoy': Float(precision=53), 'q_sales_qoq': Float(precision=53), 'q_op_yoy': Float(precision=53),
'q_op_qoq': Float(precision=53), 'q_profit_yoy': Float(precision=53), 'q_profit_qoq': Float(precision=53),
'q_netprofit_yoy': Float(precision=53), 'q_netprofit_qoq': Float(precision=53),
'equity_yoy': Float(precision=53),
'rd_exp': Float(precision=53), 'update_flag': VARCHAR(length=1)}
def __init__(self, ts_code):
super().__init__(ts_code)
self._interface = 'fina_indicator'
self._dtype = __class__.DTYPE
self._fields = ','.join(__class__.DTYPE).replace('y,m,', '')
conf.MYSQL_PASSWORD + '@' + conf.MYSQL_HOST_PORT + '/' +
conf.MYSQL_DATABASE)
instrument = conf.TICKER + '_extended_100000' # # Write here the name of the MySQL table you want to fill
df_raw = pd.read_csv('datasets/raw_dataset/' + instrument + '.csv', sep=';')
df = df_raw.dropna()
df.to_sql(con=engine,
name=instrument,
if_exists='replace',
index_label='ID',
chunksize=10000,
dtype={
'DATE': DATE(),
'TIME': TIME(),
'OPEN': DECIMAL(6, 5),
'HIGH': DECIMAL(6, 5),
'LOW': DECIMAL(6, 5),
'CLOSE': DECIMAL(6, 5),
'TICKVOL': INT(),
'RETURN1': DECIMAL(6, 5),
'RETURN5': DECIMAL(6, 5),
'RETURN15': DECIMAL(6, 5),
'RETURN60': DECIMAL(6, 5),
'RETURN240': DECIMAL(6, 5),
'RETURN1440': DECIMAL(6, 5),
'RETURN4320': DECIMAL(6, 5),
'PCTL15': DECIMAL(6, 5),
'PCTL60': DECIMAL(6, 5),
def init_trade_date():
template_start = '{}00101'
template_end = '{}91231'
data = None
for i in range(4):
print(i)
t = 199 + i
start, end = template_start.format(t), template_end.format(t)
df = __pro.query('trade_cal', start_date=start, end_date=end)
if data is not None:
data = data.append(df, ignore_index=True)
else:
data = df
print('start:{},date:{}'.format(start, len(data)))
# data.to_sql('trade_date_o', get_engine(), if_exists='replace', schema=db_name)
df = data
df_add_y_m(df, 'cal_date')
# df['y'] = df['cal_date'].apply(lambda s: int(s[:4]))
# df['m'] = df['cal_date'].apply(lambda s: int(s[4:6]))
df.set_index(['y', 'm', 'cal_date'])
df = df[df['is_open'] == 1]
df = df.reindex(columns=['y', 'm', 'cal_date', 'is_open', 'exchange'])
df.to_sql(
'trade_date_detail',
get_engine(),
index=False,
dtype={
'cal_date': DATE(),
'y': Integer(),
'm': INT(),
'is_open': INT(),
'exchange': VARCHAR(8)
},
# dtype={'cal_date': 'M8[d]'},
if_exists='replace')
'''
分组插入扩展表
'''
grouped_m = df.groupby(['y', 'm'])
# for a, g in grouped_m:
# print(a)
# print(g)
r1 = grouped_m['cal_date'].agg([np.min, np.max])
r1 = r1.rename(columns={'amin': 'first', 'amax': 'last'})
r1['y'] = pd.Series(r1.index.get_level_values('y'), index=r1.index)
r1['m'] = pd.Series(r1.index.get_level_values('m'), index=r1.index)
grouped_m = df.groupby(['y'])
r2 = grouped_m['cal_date'].agg([np.min, np.max])
r2 = r2.rename(columns={'amin': 'first', 'amax': 'last'})
r2['y'] = pd.Series(r2.index.get_level_values('y'), index=r2.index)
r2['m'] = pd.Series(np.zeros(len(r2)), index=r2.index)
r = r1.append(r2, ignore_index=True)
r = r.reindex(columns=['y', 'm', 'first', 'last'])
r.to_sql('trade_date',
get_engine(),
index=False,
dtype={
'first': DATE(),
'last': DATE(),
'y': Integer(),
'm': INT()
},
if_exists='replace')
