代码思路
所有用到的公式都来自于财报说,数据最后对比也参考此网站。对于每一只股票,都有各自的负债表、流量表、利润表。加起来参数有两百来个,我将所有参数都写入一个txt文件中,便于随时查询。将数据批量导入后,后来的难点就是对公式的理解以及保存了。
资产负债比率
# -*- coding: utf-8 -*-
"""
Created on Mon Feb 26 14:57:38 2018
@author: root
"""
import pandas as pd
import os
import numpy as np
years=[2017,2016,2015,2014,2013]
#----------------------------------------打开文件夹中对应的三张Excel表-----------------------------------
path_root = r'C:\Users\烫烫\Desktop\A股报表大全'
dirs = os.listdir(path_root)
for dir in dirs:
df1=pd.DataFrame(index=['现金与约当现金(占总资产%)','应收款项(占总资产%)','存货(占总资产%)','流动资产(占总资产%)','非流动资产(占总资产%)','总资产(占总资产%)','应付款项(占总资产%)','流动负债(占总资产%)','非流动负债(占总资产%)','股东权益(占总资产%)','负债和所有者权益(占总资产%)'])
path_stock=os.path.join(path_root,dir)
path_fzb=os.path.join(path_stock,'fzb')
ls=dir.split('_')
file_count = 0
for fn in os.listdir(path_fzb): #部分公司上市少于5年,目录下的文件数少于5个,获得具体数量,为以下循环提供数据
file_count = file_count + 1
if file_count<5:
years_cnt = file_count
else:
years_cnt=len(years)
yearsdata=[]
contents=[]
for i in range(years_cnt):
if ls[0].startswith('6'): #沪市A股代码以数字6打头
path_fzb_year=os.path.join(path_fzb,'sh_fzb_'+ls[0]+'_'+str(years[i])+'.csv')
else:
path_fzb_year=os.path.join(path_fzb,'sz_fzb_'+ls[0]+'_'+str(years[i])+'.csv')
f1 = open(path_fzb_year)
fzb = pd.read_csv(f1)
fzb=fzb.fillna(0) #将nan缺失值填充为0
fzb=fzb.iloc[-1]
#----------------------------------------取出想分析的数据列进行分析---------------------------------------------------- \n",
# print(str(ls[0])+'-'+str(ls[1])+'-'+str(years[i])+'年:')
xjyydxj=fzb['货币资金(元)']/fzb['资产总计']
yskx=(fzb['应收票据']+fzb['应收账款'])/fzb['资产总计']
ch=fzb['存货']/fzb['资产总计']
ldzc=fzb['流动资产合计']/fzb['资产总计']
fldzc=fzb['非流动资产合计']/fzb['资产总计']
zzc=1.00
yfkx=(fzb['短期借款']+fzb['应付票据']+fzb['应付账款'])/fzb['资产总计']
ldfz=fzb['流动负债合计']/fzb['资产总计']
fldfz=fzb['非流动负债合计']/fzb['资产总计']
gdqy=fzb['所有者权益(或股东权益)合计']/fzb['资产总计']
fzhsyzqy=1.00
df1.at['现金与约当现金(占总资产%)',years[i]]=xjyydxj*100
df1.at['应收款项(占总资产%)',years[i]]=yskx*100
df1.at['存货(占总资产%)',years[i]]=ch*100
df1.at['流动资产(占总资产%)',years[i]]=ldzc*100
df1.at['非流动资产(占总资产%)',years[i]]=fldzc*100
df1.at['总资产(占总资产%)',years[i]]=zzc*100
df1.at['应付款项(占总资产%)',years[i]]=yfkx*100
df1.at['流动负债(占总资产%)',years[i]]=ldfz*100
df1.at['非流动负债(占总资产%)',years[i]]=fldfz*100
df1.at['股东权益(占总资产%)',years[i]]=gdqy*100
df1.at['负债和所有者权益(占总资产%)',years[i]]=fzhsyzqy*100
df1.to_csv(r'C:\Users\烫烫\Desktop\资产负债比率\%s.csv'%str(ls[0]+'-'+ls[1]), sep=',', encoding='ANSI')
获得的所有股票以及具体格式如下:
五大财务比率
# -*- coding: utf-8 -*-
"""
Created on Mon Feb 26 14:57:38 2018
@author: root
"""
import numpy as np
import pandas as pd
import os,re,csv,math
from pandas.core.frame import DataFrame
#f = open(r'C:\Users\烫烫\Desktop\A股报表大全\000004_国农科技\fzb\sz_fzb_000004_2016.csv')
#res = pd.read_csv(f)
#a=res.columns
#print(res['机构名称'])
#df2=DataFrame(np.arange(110).reshape((22,5)),index=['负债占资产比率(%)','长期资金占不动产及设备比率(%)','流动比率(%)','速动比率(%)','应收款项周转率(次/年)','应收款项周转天数(天)','存货周转率(次/年)','存货周转天数(天)','固定资产周转率(次/年)','总资产周转率(次/年)','ROA=资产收益率(%)','ROE=净资产收益率(%)','税前纯益占实收资本比率(%)','毛利率(%)','营业利润率(%)','净利率(%)','营业费用率(%)','经营安全边际率(%)','EPS=基本每股收益(元)','现金流量比率','现金流量允当比率','现金再投资比率'],columns=['2013','2014','2015','2016','2017'])
#print(zcfzbl)
years=[2017,2016,2015,2014,2013]
#----------------------------------------打开文件夹中对应的三张Excel表-----------------------------------
path_root = r'C:\Users\烫烫\Desktop\A股报表大全'
dirs = os.listdir(path_root)
for dir in dirs:
df2=DataFrame(index=['负债占资产比率(%)','长期资金占不动产及设备比率(%)','流动比率(%)','速动比率(%)','应收款项周转率(次/年)','应收款项周转天数(天)','存货周转率(次/年)','存货周转天数(天)','固定资产周转率(次/年)','总资产周转率(次/年)','ROA=资产收益率(%)','ROE=净资产收益率(%)','税前纯益占实收资本比率(%)','毛利率(%)','营业利润率(%)','净利率(%)','营业费用率(%)','经营安全边际率(%)','EPS=基本每股收益(元)','现金流量比率','现金流量允当比率','现金再投资比率'])
path_stock=os.path.join(path_root,dir)
path_fzb=os.path.join(path_stock,'fzb')
path_lrb=os.path.join(path_stock,'lrb')
path_llb=os.path.join(path_stock,'llb')
ls=dir.split('_')
file_count = 0
for fn in os.listdir(path_fzb): #部分公司上市少于5年,目录下的文件数少于5个,获得具体数量,为以下循环提供数据
file_count = file_count + 1
if file_count<5:
years_cnt = file_count
else:
years_cnt=len(years)
for i in range(years_cnt):
if ls[0].startswith('6'): #沪市A股代码以数字6打头
path_fzb_year=os.path.join(path_fzb,'sh_fzb_'+ls[0]+'_'+str(years[i])+'.csv')
path_lrb_year=os.path.join(path_lrb,'sh_lrb_'+ls[0]+'_'+str(years[i])+'.csv')
path_llb_year=os.path.join(path_llb,'sh_llb_'+ls[0]+'_'+str(years[i])+'.csv')
else:
path_fzb_year=os.path.join(path_fzb,'sz_fzb_'+ls[0]+'_'+str(years[i])+'.csv')
path_lrb_year=os.path.join(path_lrb,'sz_lrb_'+ls[0]+'_'+str(years[i])+'.csv')
path_llb_year=os.path.join(path_llb,'sz_llb_'+ls[0]+'_'+str(years[i])+'.csv')
f1 = open(path_fzb_year)
fzb = pd.read_csv(f1)
fzb=fzb.fillna(0) #将nan缺失值填充为0
fzb=fzb.iloc[-1]
f2 = open(path_lrb_year)
lrb = pd.read_csv(f2)
lrb=lrb.fillna(0) #将nan缺失值填充为0
lrb=lrb.iloc[-1]
f3 = open(path_llb_year)
llb = pd.read_csv(f3)
llb=llb.fillna(0) #将nan缺失值填充为0
llb=llb.iloc[-1]
#导入上一年负债表,在下面计算平均值需要用到。判断是否有上一年数据,没有的话设置为0.
if os.path.exists(os.path.join(path_fzb,'sh_fzb_'+ls[0]+'_'+str(years[i]-1)+'.csv'))==True or os.path.exists(os.path.join(path_fzb,'sz_fzb_'+ls[0]+'_'+str(years[i]-1)+'.csv'))==True:
if ls[0].startswith('6'):
last=open(os.path.join(path_fzb,'sh_fzb_'+ls[0]+'_'+str(years[i]-1)+'.csv'))
else:
last=open(os.path.join(path_fzb,'sz_fzb_'+ls[0]+'_'+str(years[i]-1)+'.csv'))
lastyear=pd.read_csv(last)
lastyear=lastyear.fillna(0)
lastyear=lastyear.iloc[-1]
exists1=True
else:
exists1=False
##----------------------------------------取出想分析的数据列进行分析---------------------------------------------------- \n",
print(str(ls[1])+'-'+str(years[i])+'年')
zcfzbl=fzb['负债合计']/fzb['负债和所有者(或股东权益)合计']
cqzj=(fzb['所有者权益(或股东权益)合计']+fzb['非流动负债合计'])/(fzb['固定资产']+fzb['在建工程']+fzb['工程物资'])
ldbl=fzb['流动资产合计']/fzb['流动负债合计']
sdbl=(fzb['流动资产合计']-fzb['存货']-fzb['预付款项'])/fzb['流动负债合计']
gdzczzl=lrb['其中:营业收入(元)']/fzb['固定资产']
# 平均应收款项余额 = (应收账款余额年初数+应收票据余额年初数+应收帐款余额年末数+应收票据余额年末数)/2
sqcyzsszbbl=lrb['四、利润总额']/fzb['实收资本(或股本)']
mll=(lrb['其中:营业收入(元)']-lrb['其中:营业成本'])/lrb['其中:营业收入(元)']
yylrl=lrb['三、营业利润']/lrb['其中:营业收入(元)']
jlrl=lrb['五、净利润']/lrb['其中:营业收入(元)']
yyfyl=mll-yylrl
jyaqbjl=yylrl/mll
EPS=lrb['(一)基本每股收益']
xjllbl=llb['经营活动产生的现金流量净额']/fzb['流动负债合计']
xjztzbl=(llb['经营活动产生的现金流量净额']-llb['分配股利、利润或偿付利息支付的现金'])/(fzb['资产总计']-fzb['流动负债合计'])
if exists1==True:
pjyskxye=(lastyear['应收账款']+fzb['应收账款']+lastyear['应收票据']+fzb['应收票据'])/2
yskxzzl=lrb['其中:营业收入(元)']/pjyskxye
yskxzzdays=pjyskxye*360/lrb['其中:营业收入(元)']
pjch=(fzb['存货']+lastyear['存货'])/2
chzzl=lrb['其中:营业成本']/pjch
chzzdays=pjch*360/lrb['其中:营业成本']
bdc=(lastyear['固定资产']+fzb['固定资产'])/2
#营业收入净额=营业收入+其它业务收入-销售折扣或折让 不太理解!
yysrje=lrb['其中:营业收入(元)']
#平均资产总额 = (期初资产资产总额+期末资产总额)/2
pjzcze=(lastyear['资产总计']+fzb['资产总计'])/2
zzczzl=yysrje/pjzcze
ROA=lrb['五、净利润']/pjzcze
#平均股东权益总额=(期初股东权益+期末股东权益)/2
pjgdqyze=(fzb['所有者权益(或股东权益)合计']+lastyear['所有者权益(或股东权益)合计'])/2
ROE=lrb['五、净利润']/pjgdqyze
df2.at['应收款项周转率(次/年)',years[i]]=yskxzzl
df2.at['应收款项周转天数(天)',years[i]]=yskxzzdays
df2.at['存货周转率(次/年)',years[i]]=chzzl
df2.at['存货周转天数(天)',years[i]]=chzzdays
df2.at['总资产周转率(次/年)',years[i]]=zzczzl
df2.at['ROA=资产收益率(%)',years[i]]=ROA*100
df2.at['ROE=净资产收益率(%)',years[i]]=ROE*100
#营业活动现金流量 = 经营活动产生的现金流量净额
#投资活动现金流量 = 投资活动产生的现金流量净额
#筹资活动现金流量 = 筹资活动产生的现金流量净额
#获得当年前五年的llb
if os.path.exists(os.path.join(path_fzb,'sh_fzb_'+ls[0]+'_'+str(years[i]-5)+'.csv'))==True or os.path.exists(os.path.join(path_fzb,'sz_fzb_'+ls[0]+'_'+str(years[i]-5)+'.csv'))==True:
if ls[0].startswith('6'):
last1=open(os.path.join(path_llb,'sh_llb_'+ls[0]+'_'+str(years[i]-1)+'.csv'))
last2=open(os.path.join(path_llb,'sh_llb_'+ls[0]+'_'+str(years[i]-2)+'.csv'))
last3=open(os.path.join(path_llb,'sh_llb_'+ls[0]+'_'+str(years[i]-3)+'.csv'))
last4=open(os.path.join(path_llb,'sh_llb_'+ls[0]+'_'+str(years[i]-4)+'.csv'))
last5=open(os.path.join(path_llb,'sh_llb_'+ls[0]+'_'+str(years[i]-5)+'.csv'))
else:
last1=open(os.path.join(path_llb,'sz_llb_'+ls[0]+'_'+str(years[i]-1)+'.csv'))
last2=open(os.path.join(path_llb,'sz_llb_'+ls[0]+'_'+str(years[i]-2)+'.csv'))
last3=open(os.path.join(path_llb,'sz_llb_'+ls[0]+'_'+str(years[i]-3)+'.csv'))
last4=open(os.path.join(path_llb,'sz_llb_'+ls[0]+'_'+str(years[i]-4)+'.csv'))
last5=open(os.path.join(path_llb,'sz_llb_'+ls[0]+'_'+str(years[i]-5)+'.csv'))
lastyear1=pd.read_csv(last1)
lastyear2=pd.read_csv(last2)
lastyear3=pd.read_csv(last3)
lastyear4=pd.read_csv(last4)
lastyear5=pd.read_csv(last5)
lastyear1=lastyear1.iloc[-1]
lastyear2=lastyear2.iloc[-1]
lastyear3=lastyear3.iloc[-1]
lastyear4=lastyear4.iloc[-1]
lastyear5=lastyear5.iloc[-1]
lastyear1=lastyear1.fillna(0)
lastyear2=lastyear2.fillna(0)
lastyear3=lastyear3.fillna(0)
lastyear4=lastyear4.fillna(0)
lastyear5=lastyear5.fillna(0)
#获得五年前的fzb
if ls[0].startswith('6'):
last=open(os.path.join(path_fzb,'sh_fzb_'+ls[0]+'_'+str(years[i]-4)+'.csv'))
else:
last=open(os.path.join(path_fzb,'sz_fzb_'+ls[0]+'_'+str(years[i]-4)+'.csv'))
fzb_5year=pd.read_csv(last)
fzb_5year=fzb_5year.fillna(0)
fzb_5year=fzb_5year.iloc[-1]
wnyyhdxjll=lastyear1['经营活动产生的现金流量净额']+lastyear2['经营活动产生的现金流量净额']+lastyear3['经营活动产生的现金流量净额']+lastyear4['经营活动产生的现金流量净额']+llb['经营活动产生的现金流量净额']
wngjgdzcwxzchqtcqzc=lastyear1['购建固定资产、无形资产和其他长期资产支付的现金']+lastyear2['购建固定资产、无形资产和其他长期资产支付的现金']+lastyear3['购建固定资产、无形资产和其他长期资产支付的现金']+lastyear4['购建固定资产、无形资产和其他长期资产支付的现金']+llb['购建固定资产、无形资产和其他长期资产支付的现金']
wnczgdzcwxzchqtcqzc=lastyear1['处置固定资产、无形资产和其他长期资产收回的现金净额']+lastyear2['处置固定资产、无形资产和其他长期资产收回的现金净额']+lastyear3['处置固定资产、无形资产和其他长期资产收回的现金净额']+lastyear4['处置固定资产、无形资产和其他长期资产收回的现金净额']+llb['处置固定资产、无形资产和其他长期资产收回的现金净额']
fpgllrhcflxzf=lastyear1['分配股利、利润或偿付利息支付的现金']+lastyear2['分配股利、利润或偿付利息支付的现金']+lastyear3['分配股利、利润或偿付利息支付的现金']+lastyear4['分配股利、利润或偿付利息支付的现金']+llb['分配股利、利润或偿付利息支付的现金']
xjllydbl=wnyyhdxjll/(wngjgdzcwxzchqtcqzc-wnczgdzcwxzchqtcqzc+fzb['存货']-fzb_5year['存货']+fpgllrhcflxzf)
df2.at['现金流量允当比率',years[i]]=xjllydbl*100
df2.at['负债占资产比率(%)',years[i]]=zcfzbl*100
df2.at['长期资金占不动产及设备比率(%)',years[i]]=cqzj*100
df2.at['流动比率(%)',years[i]]=ldbl*100
df2.at['速动比率(%)',years[i]]=sdbl*100
df2.at['固定资产周转率(次/年)',years[i]]=gdzczzl
df2.at['税前纯益占实收资本比率(%)',years[i]]=sqcyzsszbbl*100
df2.at['毛利率(%)',years[i]]=mll*100
df2.at['营业利润率(%)',years[i]]=yylrl*100
df2.at['净利率(%)',years[i]]=jlrl*100
df2.at['营业费用率(%)',years[i]]=yyfyl*100
df2.at['经营安全边际率(%)',years[i]]=jyaqbjl*100
df2.at['EPS=基本每股收益(元)',years[i]]=EPS
df2.at['现金流量比率',years[i]]=xjllbl*100
df2.at['现金再投资比率',years[i]]=xjztzbl*100
df2.to_csv(r'C:\Users\烫烫\Desktop\五大资产比率\%s.csv'%str(ls[0]+'-'+ls[1]), sep=',', encoding='ANSI')