以时序数据库ymatrix记录tushare的数据
股票部分
股票和公司基础信息
股票列表
CREATE TABLE public.stock_basic (
ts_code varchar(20) NOT NULL,
symbol varchar(20) NOT NULL,
"name" varchar(100),
area varchar(50),
industry varchar(50),
fullname varchar(100),
enname varchar(100),
cnspell varchar(50),
market varchar(20),
"exchange" varchar(20),
curr_type varchar(10),
list_status varchar(10),
list_date varchar(10),
delist_date varchar(10),
is_hs varchar(5),
act_name varchar(100),
act_ent_type varchar(100)
)
上市公司基本信息
CREATE TABLE public.stock_company (
"index" bigserial,
ts_code varchar(20) NOT NULL,
"exchange" varchar(20),
chairman varchar(100),
manager varchar(100),
secretary varchar(100),
reg_capital numeric(18, 2),
setup_date date,
province varchar(50),
city varchar(50),
introduction text,
website varchar(100),
email varchar(100),
office varchar(100),
ann_date date,
business_scope text,
employees int,
main_business text
);
上市公司管理层
CREATE TABLE public.stock_manager (
"index" bigserial,
ts_code varchar(20) NOT NULL,
ann_date date,
"name" varchar(100),
gender varchar(10),
lev varchar(50),
title varchar(100),
edu varchar(50),
"national" varchar(50),
birthday date,
begin_date date,
end_date date,
resume text
);
上市公司财务信息部分
利润表
CREATE TABLE public.income (
ts_code varchar(20) NOT NULL,
ann_date varchar(10) NULL,
f_ann_date varchar(10) NULL,
end_date varchar(10) NOT NULL,
report_type varchar(10) NULL,
comp_type varchar(10) NULL,
end_type varchar(10) NULL,
basic_eps numeric(18, 4) NULL,
diluted_eps numeric(18, 4) NULL,
total_revenue numeric(18, 2) NULL,
revenue numeric(18, 2) NULL,
int_income numeric(18, 2) NULL,
prem_earned numeric(18, 2) NULL,
comm_income numeric(18, 2) NULL,
n_commis_income numeric(18, 2) NULL,
n_oth_income numeric(18, 2) NULL,
n_oth_b_income numeric(18, 2) NULL,
prem_income numeric(18, 2) NULL,
out_prem numeric(18, 2) NULL,
une_prem_reser numeric(18, 2) NULL,
reins_income numeric(18, 2) NULL,
n_sec_tb_income numeric(18, 2) NULL,
n_sec_uw_income numeric(18, 2) NULL,
n_asset_mg_income numeric(18, 2) NULL,
oth_b_income numeric(18, 2) NULL,
fv_value_chg_gain numeric(18, 2) NULL,
invest_income numeric(18, 2) NULL,
ass_invest_income numeric(18, 2) NULL,
forex_gain numeric(18, 2) NULL,
total_cogs numeric(18, 2) NULL,
oper_cost numeric(18, 2) NULL,
int_exp numeric(18, 2) NULL,
comm_exp numeric(18, 2) NULL,
biz_tax_surchg numeric(18, 2) NULL,
sell_exp numeric(18, 2) NULL,
admin_exp numeric(18, 2) NULL,
fin_exp numeric(18, 2) NULL,
assets_impair_loss numeric(18, 2) NULL,
prem_refund numeric(18, 2) NULL,
compens_payout numeric(18, 2) NULL,
reser_insur_liab numeric(18, 2) NULL,
div_payt numeric(18, 2) NULL,
reins_exp numeric(18, 2) NULL,
oper_exp numeric(18, 2) NULL,
compens_payout_refu numeric(18, 2) NULL,
insur_reser_refu numeric(18, 2) NULL,
reins_cost_refund numeric(18, 2) NULL,
other_bus_cost numeric(18, 2) NULL,
operate_profit numeric(18, 2) NULL,
non_oper_income numeric(18, 2) NULL,
non_oper_exp numeric(18, 2) NULL,
nca_disploss numeric(18, 2) NULL,
total_profit numeric(18, 2) NULL,
income_tax numeric(18, 2) NULL,
n_income numeric(18, 2) NULL,
n_income_attr_p numeric(18, 2) NULL,
minority_gain numeric(18, 2) NULL,
oth_compr_income numeric(18, 2) NULL,
t_compr_income numeric(18, 2) NULL,
compr_inc_attr_p numeric(18, 2) NULL,
compr_inc_attr_m_s numeric(18, 2) NULL,
ebit numeric(18, 2) NULL,
ebitda numeric(18, 2) NULL,
insurance_exp varchar(255) NULL,
undist_profit varchar(255) NULL,
distable_profit varchar(255) NULL,
rd_exp numeric(18, 2) NULL,
fin_exp_int_exp numeric(18, 2) NULL,
fin_exp_int_inc numeric(18, 2) NULL,
transfer_surplus_rese varchar(255) NULL,
transfer_housing_imprest varchar(255) NULL,
transfer_oth varchar(255) NULL,
adj_lossgain varchar(255) NULL,
withdra_legal_surplus varchar(255) NULL,
withdra_legal_pubfund varchar(255) NULL,
withdra_biz_devfund varchar(255) NULL,
withdra_rese_fund varchar(255) NULL,
withdra_oth_ersu varchar(255) NULL,
workers_welfare varchar(255) NULL,
distr_profit_shrhder varchar(255) NULL,
prfshare_payable_dvd varchar(255) NULL,
comshare_payable_dvd varchar(255) NULL,
capit_comstock_div varchar(255) NULL,
continued_net_profit numeric(18, 2) NULL,
update_flag varchar(20) NOT NULL,
CONSTRAINT ts_pl_key PRIMARY KEY (ts_code, end_date, update_flag)
) DISTRIBUTED BY (ts_code, end_date, update_flag);
资产负债表
CREATE TABLE balancesheet (
ts_code text NOT NULL,
ann_date text,
f_ann_date text,
end_date text NOT NULL,
report_type text,
comp_type text,
end_type text,
total_share numeric(18,2),
cap_rese numeric(18,2),
undistr_profit numeric(18,2),
surplus_rese numeric(18,2),
special_rese numeric(18,2),
money_cap numeric(18,2),
trad_asset numeric(18,2),
notes_receiv numeric(18,2),
accounts_receiv numeric(18,2),
oth_receiv numeric(18,2),
prepayment numeric(18,2),
div_receiv numeric(18,2),
int_receiv numeric(18,2),
inventories numeric(18,2),
amor_exp text,
nca_within_1y numeric(18,2),
sett_rsrv numeric(18,2),
loanto_oth_bank_fi numeric(18,2),
premium_receiv numeric(18,2),
reinsur_receiv numeric(18,2),
reinsur_res_receiv numeric(18,2),
pur_resale_fa numeric(18,2),
oth_cur_assets numeric(18,2),
total_cur_assets numeric(18,2),
fa_avail_for_sale numeric(18,2),
htm_invest text,
lt_eqt_invest numeric(18,2),
invest_real_estate numeric(18,2),
time_deposits text,
oth_assets text,
lt_rec numeric(18,2),
fix_assets numeric(18,2),
cip numeric(18,2),
const_materials numeric(18,2),
fixed_assets_disp numeric(18,2),
produc_bio_assets numeric(18,2),
oil_and_gas_assets numeric(18,2),
intan_assets numeric(18,2),
r_and_d numeric(18,2),
goodwill numeric(18,2),
lt_amor_exp numeric(18,2),
defer_tax_assets numeric(18,2),
decr_in_disbur numeric(18,2),
oth_nca numeric(18,2),
total_nca numeric(18,2),
cash_reser_cb text,
depos_in_oth_bfi text,
prec_metals text,
deriv_assets numeric(18,2),
rr_reins_une_prem text,
rr_reins_outstd_cla text,
rr_reins_lins_liab text,
rr_reins_lthins_liab text,
refund_depos text,
ph_pledge_loans text,
refund_cap_depos text,
indep_acct_assets text,
client_depos text,
client_prov text,
transac_seat_fee text,
invest_as_receiv text,
total_assets numeric(18,2),
lt_borr numeric(18,2),
st_borr numeric(18,2),
cb_borr numeric(18,2),
depos_ib_deposits numeric(18,2),
loan_oth_bank numeric(18,2),
trading_fl numeric(18,2),
notes_payable numeric(18,2),
acct_payable numeric(18,2),
adv_receipts numeric(18,2),
sold_for_repur_fa numeric(18,2),
comm_payable numeric(18,2),
payroll_payable numeric(18,2),
taxes_payable numeric(18,2),
int_payable numeric(18,2),
div_payable numeric(18,2),
oth_payable numeric(18,2),
acc_exp text,
deferred_inc text,
st_bonds_payable text,
payable_to_reinsurer numeric(18,2),
rsrv_insur_cont text,
acting_trading_sec numeric(18,2),
acting_uw_sec numeric(18,2),
non_cur_liab_due_1y numeric(18,2),
oth_cur_liab numeric(18,2),
total_cur_liab numeric(18,2),
bond_payable numeric(18,2),
lt_payable numeric(18,2),
specific_payables numeric(18,2),
estimated_liab numeric(18,2),
defer_tax_liab numeric(18,2),
defer_inc_non_cur_liab numeric(18,2),
oth_ncl numeric(18,2),
total_ncl numeric(18,2),
depos_oth_bfi text,
deriv_liab numeric(18,2),
depos text,
agency_bus_liab text,
oth_liab text,
prem_receiv_adva text,
depos_received text,
ph_invest text,
reser_une_prem text,
reser_outstd_claims text,
reser_lins_liab text,
reser_lthins_liab text,
indept_acc_liab text,
pledge_borr text,
indem_payable text,
policy_div_payable text,
total_liab numeric(18,2),
treasury_share numeric(18,2),
ordin_risk_reser numeric(18,2),
forex_differ numeric(18,2),
invest_loss_unconf text,
minority_int numeric(18,2),
total_hldr_eqy_exc_min_int numeric(18,2),
total_hldr_eqy_inc_min_int numeric(18,2),
total_liab_hldr_eqy numeric(18,2),
lt_payroll_payable numeric(18,2),
oth_comp_income numeric(18,2),
oth_eqt_tools numeric(18,2),
oth_eqt_tools_p_shr numeric(18,2),
lending_funds text,
acc_receivable text,
st_fin_payable text,
payables text,
hfs_assets numeric(18,2),
hfs_sales numeric(18,2),
cost_fin_assets text,
fair_value_fin_assets text,
contract_assets numeric(18,2),
contract_liab numeric(18,2),
accounts_receiv_bill numeric(18,2),
accounts_pay numeric(18,2),
oth_rcv_total numeric(18,2),
fix_assets_total numeric(18,2),
cip_total numeric(18,2),
oth_pay_total numeric(18,2),
long_pay_total numeric(18,2),
debt_invest numeric(18,2),
oth_debt_invest numeric(18,2),
oth_eq_invest numeric(18,2),
oth_illiq_fin_assets numeric(18,2),
oth_eq_ppbond numeric(18,2),
receiv_financing numeric(18,2),
use_right_assets numeric(18,2),
lease_liab numeric(18,2),
update_flag text NOT NULL,
CONSTRAINT ts_bs_u1 PRIMARY KEY (ts_code, end_date, update_flag)
) DISTRIBUTED BY (ts_code, end_date, update_flag);
现金流量表
CREATE TABLE cash_flow (
ts_code varchar(20) NOT NULL,
ann_date varchar(10),
f_ann_date varchar(10),
end_date varchar(10) NOT NULL,
comp_type varchar(10),
report_type varchar(10),
end_type varchar(10),
net_profit numeric(18, 2),
finan_exp numeric(18, 2),
c_fr_sale_sg numeric(18, 2),
recp_tax_rends numeric(18, 2),
n_depos_incr_fi varchar(255),
n_incr_loans_cb varchar(255),
n_inc_borr_oth_fi varchar(255),
prem_fr_orig_contr varchar(255),
n_incr_insured_dep varchar(255),
n_reinsur_prem varchar(255),
n_incr_disp_tfa varchar(255),
ifc_cash_incr varchar(255),
n_incr_disp_faas varchar(255),
n_incr_loans_oth_bank varchar(255),
n_cap_incr_repur varchar(255),
c_fr_oth_operate_a numeric(18, 2),
c_inf_fr_operate_a numeric(18, 2),
c_paid_goods_s numeric(18, 2),
c_paid_to_for_empl numeric(18, 2),
c_paid_for_taxes numeric(18, 2),
n_incr_clt_loan_adv varchar(255),
n_incr_dep_cbob varchar(255),
c_pay_claims_orig_inco varchar(255),
pay_handling_chrg varchar(255),
pay_comm_insur_plcy varchar(255),
oth_cash_pay_oper_act numeric(18, 2),
st_cash_out_act numeric(18, 2),
n_cashflow_act numeric(18, 2),
oth_recp_ral_inv_act numeric(18, 2),
c_disp_withdrwl_invest numeric(18, 2),
c_recp_return_invest numeric(18, 2),
n_recp_disp_fiolta numeric(18, 2),
n_recp_disp_sobu numeric(18, 2),
stot_inflows_inv_act numeric(18, 2),
c_pay_acq_const_fiolta numeric(18, 2),
c_paid_invest numeric(18, 2),
n_disp_subs_oth_biz numeric(18, 2),
oth_pay_ral_inv_act numeric(18, 2),
n_incr_pledge_loan numeric(18, 2),
stot_out_inv_act numeric(18, 2),
n_cashflow_inv_act numeric(18, 2),
c_recp_borrow numeric(18, 2),
proc_issue_bonds numeric(18, 2),
oth_cash_recp_ral_fnc_act numeric(18, 2),
stot_cash_in_fnc_act numeric(18, 2),
free_cashflow numeric(18, 2),
c_prepay_amt_borr numeric(18, 2),
c_pay_dist_dpcp_int_exp numeric(18, 2),
incl_dvd_profit_paid_sc_ms numeric(18, 2),
oth_cashpay_ral_fnc_act numeric(18, 2),
stot_cashout_fnc_act numeric(18, 2),
n_cash_flows_fnc_act numeric(18, 2),
eff_fx_flu_cash numeric(18, 2),
n_incr_cash_cash_equ numeric(18, 2),
c_cash_equ_beg_period numeric(18, 2),
c_cash_equ_end_period numeric(18, 2),
c_recp_cap_contrib numeric(18, 2),
incl_cash_rec_saims numeric(18, 2),
uncon_invest_loss varchar(255),
prov_depr_assets numeric(18, 2),
depr_fa_coga_dpba numeric(18, 2),
amort_intang_assets numeric(18, 2),
lt_amort_deferred_exp numeric(18, 2),
decr_deferred_exp varchar(255),
incr_acc_exp varchar(255),
loss_disp_fiolta numeric(18, 2),
loss_scr_fa numeric(18, 2),
loss_fv_chg numeric(18, 2),
invest_loss numeric(18, 2),
decr_def_inc_tax_assets numeric(18, 2),
incr_def_inc_tax_liab numeric(18, 2),
decr_inventories numeric(18, 2),
decr_oper_payable numeric(18, 2),
incr_oper_payable numeric(18, 2),
"others" numeric(18, 2),
im_net_cashflow_oper_act numeric(18, 2),
conv_debt_into_cap varchar(255),
conv_copbonds_due_within_1y varchar(255),
fa_fnc_leases varchar(255),
im_n_incr_cash_equ numeric(18, 2),
net_dism_capital_add varchar(255),
net_cash_rece_sec varchar(255),
credit_impa_loss varchar(255),
use_right_asset_dep numeric(18, 2),
oth_loss_asset varchar(255),
end_bal_cash numeric(18, 2),
beg_bal_cash numeric(18, 2),
end_bal_cash_equ varchar(255),
beg_bal_cash_equ varchar(255),
update_flag varchar(20) NOT null,
CONSTRAINT ts_cf_u1 PRIMARY KEY (ts_code, end_date, update_flag)
) DISTRIBUTED BY (ts_code, end_date, update_flag);
;
财务指标表
CREATE TABLE fina_indicator (
ts_code varchar(20) NOT NULL,
ann_date varchar(10),
end_date varchar(10) NOT NULL,
eps numeric(18, 4),
dt_eps numeric(18, 4),
total_revenue_ps numeric(18, 4),
revenue_ps numeric(18, 4),
capital_rese_ps numeric(18, 4),
surplus_rese_ps numeric(18, 4),
undist_profit_ps numeric(18, 4),
extra_item numeric(18, 4),
profit_dedt numeric(18, 4),
gross_margin numeric(18, 4),
current_ratio numeric(18, 4),
quick_ratio numeric(18, 4),
cash_ratio numeric(18, 4),
invturn_days numeric(18, 4),
arturn_days numeric(18, 4),
inv_turn numeric(18, 4),
ar_turn numeric(18, 4),
ca_turn numeric(18, 4),
fa_turn numeric(18, 4),
assets_turn numeric(18, 4),
op_income numeric(18, 4),
valuechange_income numeric(18, 4),
interst_income numeric(18, 4),
daa numeric(18, 4),
ebit numeric(18, 4),
ebitda numeric(18, 4),
fcff numeric(18, 4),
fcfe numeric(18, 4),
current_exint numeric(18, 4),
noncurrent_exint numeric(18, 4),
interestdebt numeric(18, 4),
netdebt numeric(18, 4),
tangible_asset numeric(18, 4),
working_capital numeric(18, 4),
networking_capital numeric(18, 4),
invest_capital numeric(18, 4),
retained_earnings numeric(18, 4),
diluted2_eps numeric(18, 4),
bps numeric(18, 4),
ocfps numeric(18, 4),
retainedps numeric(18, 4),
cfps numeric(18, 4),
ebit_ps numeric(18, 4),
fcff_ps numeric(18, 4),
fcfe_ps numeric(18, 4),
netprofit_margin numeric(18, 4),
grossprofit_margin numeric(18, 4),
cogs_of_sales numeric(18, 4),
expense_of_sales numeric(18, 4),
profit_to_gr numeric(18, 4),
saleexp_to_gr numeric(18, 4),
adminexp_of_gr numeric(18, 4),
finaexp_of_gr numeric(18, 4),
impai_ttm numeric(18, 4),
gc_of_gr numeric(18, 4),
op_of_gr numeric(18, 4),
ebit_of_gr numeric(18, 4),
roe numeric(18, 4),
roe_waa numeric(18, 4),
roe_dt numeric(18, 4),
roa numeric(18, 4),
npta numeric(18, 4),
roic numeric(18, 4),
roe_yearly numeric(18, 4),
roa2_yearly numeric(18, 4),
roe_avg numeric(18, 4),
opincome_of_ebt numeric(18, 4),
investincome_of_ebt numeric(18, 4),
n_op_profit_of_ebt numeric(18, 4),
tax_to_ebt numeric(18, 4),
dtprofit_to_profit numeric(18, 4),
salescash_to_or numeric(18, 4),
ocf_to_or numeric(18, 4),
ocf_to_opincome numeric(18, 4),
capitalized_to_da numeric(18, 4),
debt_to_assets numeric(18, 4),
assets_to_eqt numeric(18, 4),
dp_assets_to_eqt numeric(18, 4),
ca_to_assets numeric(18, 4),
nca_to_assets numeric(18, 4),
tbassets_to_totalassets numeric(18, 4),
int_to_talcap numeric(18, 4),
eqt_to_talcapital numeric(18, 4),
currentdebt_to_debt numeric(18, 4),
longdeb_to_debt numeric(18, 4),
ocf_to_shortdebt numeric(18, 4),
debt_to_eqt numeric(18, 4),
eqt_to_debt numeric(18, 4),
eqt_to_interestdebt numeric(18, 4),
tangibleasset_to_debt numeric(18, 4),
tangasset_to_intdebt numeric(18, 4),
tangibleasset_to_netdebt numeric(18, 4),
ocf_to_debt numeric(18, 4),
ocf_to_interestdebt numeric(18, 4),
ocf_to_netdebt numeric(18, 4),
ebit_to_interest numeric(18, 4),
longdebt_to_workingcapital numeric(18, 4),
ebitda_to_debt numeric(18, 4),
turn_days numeric(18, 4),
roa_yearly numeric(18, 4),
roa_dp numeric(18, 4),
fixed_assets numeric(18, 4),
profit_prefin_exp numeric(18, 4),
non_op_profit numeric(18, 4),
op_to_ebt numeric(18, 4),
nop_to_ebt numeric(18, 4),
ocf_to_profit numeric(18, 4),
cash_to_liqdebt numeric(18, 4),
cash_to_liqdebt_withinterest numeric(18, 4),
op_to_liqdebt numeric(18, 4),
op_to_debt numeric(18, 4),
roic_yearly numeric(18, 4),
total_fa_trun numeric(18, 4),
profit_to_op numeric(18, 4),
q_opincome numeric(18, 4),
q_investincome numeric(18, 4),
q_dtprofit numeric(18, 4),
q_eps numeric(18, 4),
q_netprofit_margin numeric(18, 4),
q_gsprofit_margin numeric(18, 4),
q_exp_to_sales numeric(18, 4),
q_profit_to_gr numeric(18, 4),
q_saleexp_to_gr numeric(18, 4),
q_adminexp_to_gr numeric(18, 4),
q_finaexp_to_gr numeric(18, 4),
q_impair_to_gr_ttm numeric(18, 4),
q_gc_to_gr numeric(18, 4),
q_op_to_gr numeric(18, 4),
q_roe numeric(18, 4),
q_dt_roe numeric(18, 4),
q_npta numeric(18, 4),
q_opincome_to_ebt numeric(18, 4),
q_investincome_to_ebt numeric(18, 4),
q_dtprofit_to_profit numeric(18, 4),
q_salescash_to_or numeric(18, 4),
q_ocf_to_sales numeric(18, 4),
q_ocf_to_or numeric(18, 4),
basic_eps_yoy numeric(18, 4),
dt_eps_yoy numeric(18, 4),
cfps_yoy numeric(18, 4),
op_yoy numeric(18, 4),
ebt_yoy numeric(18, 4),
netprofit_yoy numeric(18, 4),
dt_netprofit_yoy numeric(18, 4),
ocf_yoy numeric(18, 4),
roe_yoy numeric(18, 4),
bps_yoy numeric(18, 4),
assets_yoy numeric(18, 4),
eqt_yoy numeric(18, 4),
tr_yoy numeric(18, 4),
or_yoy numeric(18, 4),
q_gr_yoy numeric(18, 4),
q_gr_qoq numeric(18, 4),
q_sales_yoy numeric(18, 4),
q_sales_qoq numeric(18, 4),
q_op_yoy numeric(18, 4),
q_op_qoq numeric(18, 4),
q_profit_yoy numeric(18, 4),
q_profit_qoq numeric(18, 4),
q_netprofit_yoy numeric(18, 4),
q_netprofit_qoq numeric(18, 4),
equity_yoy numeric(18, 4),
rd_exp numeric(18, 4),
update_flag varchar(20) NOT null,
CONSTRAINT ts_fi_key PRIMARY KEY (ts_code, end_date, update_flag)
)
DISTRIBUTED BY (ts_code, end_date, update_flag);
主营业务构成
CREATE TABLE fina_mainbz (
ts_code varchar(20) NOT NULL,
end_date varchar(10) NOT NULL,
bz_item varchar(255),
bz_code varchar(50),
bz_sales numeric(18, 2),
bz_profit numeric(18, 2),
bz_cost numeric(18, 2),
curr_type varchar(10),
CONSTRAINT ts_fm_key PRIMARY KEY (ts_code, end_date)
)
DISTRIBUTED BY (ts_code, end_date)
;
行情数据
Tushare行情数据目前已经具备高可用高稳定性,提供了包括股票、指数、基金、期货等在内的质量比较高的日线行情和分钟行情。而且也像老版本中ts.bar接口一样,提供了一个统一的行情数据输出标准接口。
分钟行情
日线行情
周线行情
月线行情
复权行情
复权因子
停复牌信息
每日行情指标
通用行情接口