def _make_balance_sheet(self, code, engine):
balances = ts.get_balance_sheet(code)
bses = balances.T
col_name = bses.columns.tolist()
col_name.insert(0, 'code')
result = bses.reindex(columns=col_name, fill_value=code)
result.index.name = 'date'
result.to_sql('consolidated_balance_sheet',
engine,
if_exists='append',
dtype={'date': VARCHAR(50)})
def get_statement(self,down= True):
self.profit=ts.get_profit_statement(self.code)
self.cash=ts.get_cash_flow(self.code)
self.balance=ts.get_balance_sheet(self.code)
if down is True:
for __tpye in self.filetype:
__path = self.get_path()+"\\"+__tpye+"%s.csv"%self.code
if __tpye is "profit":
self.profit.to_csv(__path)
elif __tpye is "cash":
self.cash.to_csv(__path)
elif __tpye is "balance":
self.balance.to_csv(__path)
def get_balance_sheet():
i = 0
while i < len(tickers):
try:
balance_sheet = ts.get_balance_sheet(tickers[i])
balance_sheet.to_csv("your_path" + "/balance_sheet_" + tickers[i] +
'.csv',
encoding="gbk")
# remember to change it to your designated path
print(i)
i = i + 1
time.sleep(10)
except:
print(i + 'failed')
time.sleep(60)
def history_stock(engine, session, code):
tsl.log("get data for code : " + code + " start...")
path = '/home/data/'
sdate = datetime.date(2013, 1, 1)
edate = datetime.date.today()
df = ts.get_k_data(code, start=str(sdate), end=str(edate))
df.to_csv(path + 's_' + code + '.csv',
columns=['date', 'open', 'close', 'high', 'low', 'volume'])
df = ts.get_balance_sheet(code)
df.to_csv(path + 'sb_' + code + '.csv')
df = ts.get_profit_statement(code)
df.to_csv(path + 'sp_' + code + '.csv')
df = ts.get_cash_flow(code)
df.to_csv(path + 'sc_' + code + '.csv')
tsl.log("get data for code : " + code + " done")
def get_magic(code):
ProfitDF = ts.get_profit_statement(code)
ProfitDF = ProfitDF.loc[[8, 18, 19], ]
ProfitDF = ProfitDF.drop(['报表日期'], axis=1)
ProfitDF = ProfitDF.astype(np.float64)
EBIT = ProfitDF.sum()
BalanceDF = ts.get_balance_sheet(code)
BalanceDF = BalanceDF.loc[[18, 26, 41]]
BalanceDF = BalanceDF.drop(['报表日期'], axis=1)
BalanceDF = BalanceDF.astype(np.float64)
BalanceDF = BalanceDF.fillna(0)
Assets = BalanceDF.loc[18] - BalanceDF.loc[41] + BalanceDF.loc[26]
magic = EBIT / Assets
#print(magic)
return magic
def get_data(code, date):
df_profit_statement, flag_profit_statement = from_sql(code, date, 'profit_statement')
if not flag_profit_statement or len(df_profit_statement) < 1:
df_profit_statement = ts.get_profit_statement(code)
df_profit_statement = df_process(df_profit_statement, 'profit_statement', code)
df_balance_sheet, flag_balance_sheet = from_sql(code, date, 'balance_sheet')
if not flag_balance_sheet or len(df_balance_sheet) < 1:
df_balance_sheet = ts.get_balance_sheet(code)
df_balance_sheet = df_process(df_balance_sheet, 'balance_sheet', code)
df_cash_flow, flag_cash_flow = from_sql(code, date, 'cash_flow')
if not flag_cash_flow or len(df_cash_flow) < 1:
df_cash_flow = ts.get_cash_flow(code)
df_cash_flow = df_process(df_cash_flow, 'cash_flow', code)
return df_profit_statement, df_balance_sheet, df_cash_flow
def download_statement(self, _code=None, _path=None):
"""
下载财务报表
"""
if _code is not None:
self.code = _code
else:
pass
if _path is None:
self.creat_folder()
_path = self.stock_dirs()
else:
pass
print("\n start up the %s statement downloading...\n" % self.code)
self.profit = ts.get_profit_statement(self.code)
self.profit = self.profit.drop([0])
time.sleep(1)
#print (">>>",end="")
self.profit.to_csv(_path + "\\%s_profit.csv" % self.code,
encoding="gbk")
#print (">>>",end="")
self.cash = ts.get_cash_flow(self.code)
self.cash = self.cash.drop([0])
time.sleep(1)
#print (">>>",end="")
self.profit.to_csv(_path + "\\%s_cash.csv" % self.code, encoding="gbk")
#print (">>>",end="")
self.balance = ts.get_balance_sheet(self.code)
time.sleep(1)
#print (">>>",end="")
self.profit.to_csv(_path + "\\%s_balance.csv" % self.code,
encoding="gbk")
#print (">\n")
print(" completed downloading!")
def get_balance_sheet(code, data_source='tushare'):
if util.is_tushare(data_source):
return ts.get_balance_sheet(code)
def _make_balance_sheets(self, code, years):
self.balances = ts.get_balance_sheet(code)
self.balance_sheets = {
year: utils.convert_to_float(self.balances[year])
for year in years
}
def _make_balance_sheet(self, code):
balances = ts.get_balance_sheet(code)
self.balance_sheets = {
year: self._convert_to_float(balances[year])
for year in self.actual_years
}
def __init__(self,code):
self.code = code
# 取财报三表全量数据及价格数据
self.original_price = ts.get_k_data(self.code, ktype="M")
pf = ts.get_profit_statement(self.code)
pf.set_index(["报表日期"], inplace=True)
cf = ts.get_cash_flow(self.code)
cf.set_index(["报表日期"], inplace=True)
al = ts.get_balance_sheet(self.code)
al.set_index(["报表日期"], inplace=True)
# 取年报的列索引，生成全期年报报表
y_report_al = ["1231" in x for x in al.columns]
y_report_pf = ["1231" in x for x in pf.columns]
y_report_cf = ["1231" in x for x in cf.columns]
self.py_y = copy.deepcopy(pf.iloc[:, y_report_pf])
self.cf_y = copy.deepcopy(cf.iloc[:, y_report_cf])
self.al_y = copy.deepcopy(al.iloc[:, y_report_al])
# 求年收盘价
y_price_index = [pd.to_datetime(x).month == 12 for x in self.original_price["date"]]
y_price = self.original_price.loc[y_price_index, "close"]
y_price.index = pd.to_datetime(self.original_price.loc[y_price_index, "date"])
y_price.index = y_price.index.year
y_price.sort_index(ascending=False, inplace=True)
self.y_price = y_price
fd_data_index = self.al_y.columns
self.fd_data_index = pd.to_datetime(fd_data_index).year
# 具体基本面数据
self.eps = pd.to_numeric(self.py_y.loc["基本每股收益(元/股)", :])
self.net_profit = pd.to_numeric(self.py_y.loc["五、净利润", :])
self.withinterst_Debt = pd.to_numeric(self.al_y.loc["应付账款", :]) + pd.to_numeric(self.al_y.loc["短期借款", :]) + \
pd.to_numeric(self.al_y.loc["应付票据", :]) + pd.to_numeric(self.al_y.loc["其他应付款", :]) + \
pd.to_numeric(self.al_y.loc["一年内到期的非流动负债", :]) + pd.to_numeric(self.al_y.loc["长期借款", :]) + \
pd.to_numeric(self.al_y.loc["应付债券", :]) + pd.to_numeric(self.al_y.loc["长期应付款", :])
self.lqd_Asset = pd.to_numeric(self.al_y.loc["流动资产合计", :])
self.lqd_Debt = pd.to_numeric(self.al_y.loc["流动负债合计", :])
self.ivtry = pd.to_numeric(self.al_y.loc["存货", :])
self.cash = pd.to_numeric(self.al_y.loc["货币资金", :]) + pd.to_numeric(self.al_y.loc["交易性金融资产", :])
self.total_Asset = pd.to_numeric(self.al_y.loc["资产总计", :])
self.total_Debt = pd.to_numeric(self.al_y.loc["负债合计", :])
self.total_Equity = self.total_Asset - self.total_Debt
self.nwc = self.lqd_Asset - self.lqd_Debt
self.receivables = pd.to_numeric(self.al_y.loc["应收账款", :])
self.rev = pd.to_numeric(self.py_y.loc["营业收入", :])
self.gross_earnings = pd.to_numeric(self.py_y.loc["营业收入", :]) - pd.to_numeric(self.py_y.loc["营业成本", :])
self.inveset_capital = self.total_Equity + self.withinterst_Debt - self.cash
self.stock_n = pd.to_numeric(self.al_y.loc["实收资本(或股本)", :])
self.bps = self.total_Equity / self.stock_n
self.sale_per_share = self.rev / self.stock_n
# 利息与EBIT
self.intrest = pd.to_numeric(self.py_y.loc["财务费用", :]) * 0.80
self.EBIT = pd.to_numeric(self.py_y.loc["四、利润总额", :]) +self. intrest
self.all_Basic_V = [self.eps,self.net_profit,self.withinterst_Debt,self.inveset_capital,
self.lqd_Asset,self.lqd_Debt,self.ivtry,self.cash,self.total_Asset,
self.total_Equity,self.total_Debt,self.nwc,self.receivables,self.rev,
self.gross_earnings,self.EBIT,self.intrest,self.stock_n,self.bps,self.sale_per_share]
for x in self.all_Basic_V:
x.index = self.fd_data_index
# 财务比率
# 短期偿债能力：计算流动比率、速动比率（酸性比率）、现金比率
self.lqd_R = self.lqd_Asset / self.lqd_Debt
self.cash_R = self.cash / self.lqd_Debt
self.acid_test_R = (self.lqd_Asset - self.ivtry) / self.lqd_Debt
# 长期偿债能力：计算资产负债率
self.D_to_A = self.total_Debt / self.total_Asset
self.B_to_E = self.total_Debt / self.total_Equity
self.A_to_E = self.total_Asset / self.total_Equity
self.WI_D_to_A = self.withinterst_Debt / self.total_Asset
# 营运能力比率：计算各周转率、和营运资本
self.inv_turnover = self.rev / self.ivtry
self.inv_turnover_days = 365 / self.inv_turnover
self.recvbls_turnover = self.rev / self.receivables
self.recvbls_turnover_days = 365 / self.recvbls_turnover
self.A_turnover = self.rev / self.total_Asset
# 盈利性指标：ROS,ROA,ROE,ROIC,毛利率
self.ROE = self.net_profit / self.total_Equity
self.ROA = self.net_profit / self.total_Asset
self.ROS = self.net_profit / self.rev
self.ROIC = self.net_profit / self.inveset_capital
self.PM = self.net_profit / self.rev
self.gross_R = self.gross_earnings / self.rev
# 计算估值指标：PE,PB,PS
self.All_PE = self.y_price / self.eps
self.All_PS = self.y_price / self.sale_per_share
self.All_PB = self.y_price / self.bps
self.all_Ratio_V = [self.lqd_R, self.cash_R, self.acid_test_R, self.D_to_A, self.A_to_E, self.B_to_E,
self.WI_D_to_A, self.inv_turnover, self.inv_turnover_days,self.recvbls_turnover,
self.recvbls_turnover_days, self.A_turnover, self.ROE, self.ROIC, self.ROS, self.ROA,
self.gross_R, self.PM]
# 同行业数据
all_idstry_class = ts.get_industry_classified()
try:
self.indstry_str = all_idstry_class[all_idstry_class["code"] == self.code]["c_name"].values[0]
self.idstry_code_list = list(all_idstry_class[all_idstry_class["c_name"] == self.indstry_str]["code"].values)
except:
print("该股票无明显行业分类，可能是上市时间较短。")
# 数据汇总
All_Basic_data = pd.DataFrame(self.all_Basic_V, index=["每股收益", "净利润", "有息负债", "投入资本", "流动资产", "流动负债",
"存货", "现金", "总资产", "净资产", "总负债", "营运资本", "应收账款",
"营业收入", "毛利润", "EBIT", "利息净支出", "股本数", "每股净资产",
"每股销售收入"])
All_Basic_data[1:-2] = All_Basic_data[1:-2] / 10000
All_Ratio_data = pd.DataFrame(self.all_Ratio_V, index=["流动比率", "现金比率", "速动比率", "资产负债率", "权益乘数", "负债权益比",
"有息负债率", "存货周转率", "存货周转天数", "应收账款周转率", "应收账款周转天数",
"总资产周转率", "ROE", "ROIC", "ROS", "ROA", "毛利率", "销售净利率"])
All_Ratio_data.dropna(axis=1, how="all", inplace=True)
All_Valuation_data = pd.DataFrame([self.All_PS, self.All_PE, self.All_PB], index=["全时期PS", "全时期PE", "全时期PB"])
All_Valuation_data.dropna(axis=1, how="all", inplace=True)
Dupon_data = pd.DataFrame(
[self.ROE, self.ROA, self.A_to_E, self.A_turnover, self.PM, self.total_Asset, self.total_Equity,
self.rev, self.net_profit, ],
index=["ROE", "ROA", "权益乘数", "总资产周转率", "销售净利率", "总资产",
"净资产", "营业收入", "净利润"])
Dupon_data.dropna(axis=1, how="all", inplace=True)
# 计算增长率
All_Growth_data = All_Basic_data.T.pct_change(-1)
All_Growth_data = All_Growth_data.iloc[:-1, :]
All_Growth_data.dropna(how="all", inplace=True)
self.All_Basic_data = All_Basic_data
self.All_Ratio_data = All_Ratio_data
self.All_Growth_data = All_Growth_data
self.Dupon_data = Dupon_data
self.All_Valuation_data = All_Valuation_data