library("readr")
library("readxl")
library("dplyr")
library(stringr)
library(PerformanceAnalytics)
library(plm)
setwd("e:/R/tail risk/day/finance")
rf1<-read.csv("rf1.csv")
listf<-read_excel("listf.xlsx",sheet = 1)%>%select(-2)
rf1<-left_join(rf1,listf,by="Stkcd")
varp<-0.05
cs1<-read_excel("cs.xlsx",sheet = 1)%>%arrange(Stkcd)
findex<-read_excel("findex.xlsx",sheet = 1)%>%select(2,3,12)%>%rename(Stkcd="简称",Clsdt="日期",Dretwd="涨跌幅(%)")%>%filter(Dretwd!="--")
findex1<-data.frame(findex[,c(1,2)],Dretwd=as.numeric(findex$Dretwd))
findex1<-(data.frame(findex1,Trdmnt=as.numeric(str_replace_all(findex1$Clsdt,"-","")))%>%select(-2))[,c(1,3,2)]
cs<-filter(findex1,Stkcd=="金融(风格.中信)")%>%mutate(Cdretwdos=Dretwd/100)%>%select(-1,-3)
wf<-read_excel("wf.xlsx",sheet = 1)%>%slice(-(1L:2L))%>%select(-1,-4)
wf1<-data.frame(Trdmnt=as.numeric(str_replace_all(wf$Clsdt,"-","")),Nrrdaydt=as.numeric(wf$Nrrdaydt))
rb<-semi_join(rf1,cs1,by="Stkcd")%>%inner_join(cs,by="Trdmnt")%>%left_join(wf1,by="Trdmnt")
d=list();d1=list();d2=list();d11=list();d12=list()
for(i in 0:9){
for(h in 0:3){
if(i==9&h==1) break
r1<-filter(rb,Trdmnt>=(20050101+300*h+10000*i),Trdmnt<(20100101+300*h+10000*i))
r1<-filter(r1,List<=min(r1$Trdmnt))
k<-round(nrow(distinct(r1,Trdmnt))*varp)
r2<-distinct(r1,Trdmnt,.keep_all = TRUE)%>%transmute(rme=Cdretwdos,rme=-rme)%>%arrange(rme)
r3<-slice(r2,(nrow(distinct(r1,Trdmnt))-k+1):n())
r4<-transmute(r3,lrme=log(rme));a<-sum(r4)
α1<-1/k*a-log(as.numeric(slice(r2,nrow(distinct(r1,Trdmnt))-k)))
r5<-mutate(r1,rje=-(Dretwd),rme=-(Cdretwdos))
b<-group_by(r5,Stkcd)%>%arrange(rme)%>%slice(n()-round(n()*varp))%>%select(Stkcd,rme)%>%rename(frme=rme)
f<-group_by(r5,Stkcd)%>%arrange(rje)%>%slice(n()-round(n()*varp))%>%select(Stkcd,rje)%>%rename(frje=rje)
c<-left_join(r5,f,by="Stkcd")%>%left_join(b,by="Stkcd")
c1<-mutate(c,t=ifelse(rje>frje&rme>frme,1,0),tt=ifelse(rme>frme,1,0))
c2<-group_by(c1,Stkcd)%>%summarise(τ=sum(t)/sum(tt))
varm<-b
varj<-f
d[[i*4+(h+1)]]<-data.frame(left_join(c2,varj,by="Stkcd")%>%left_join(varm,by="Stkcd")%>%mutate(tβ=(τ^α1)*frje/frme)%>%select(-(2:4))%>%filter(tβ!="NA",tβ!="NaN"),Trdmnt=200501+3*h+100*i)[,c(1,3,2)]
d1[[i*4+(h+1)]]<-data.frame(left_join(c2,varj,by="Stkcd")%>%left_join(varm,by="Stkcd")%>%mutate(crisk=τ^α1)%>%select(-(2:4))%>%filter(crisk!="NA",crisk!="NaN"),Trdmnt=200501+3*h+100*i)[,c(1,3,2)]
d2[[i*4+(h+1)]]<-data.frame(left_join(c2,varj,by="Stkcd")%>%left_join(varm,by="Stkcd")%>%mutate(brisk=frje/frme)%>%select(-(2:4))%>%filter(brisk!="NA",brisk!="NaN"),Trdmnt=200501+3*h+100*i)[,c(1,3,2)]
d11[[i*4+(h+1)]]<-data.frame(group_by(r1,Stkcd)%>%summarise(p=cov(Dretwd,Cdretwdos)/(sd(Dretwd)*sd(Cdretwdos)))%>%filter(p!="NA",p!="NaN"),Trdmnt=200501+3*h+100*i)[,c(1,3,2)]
d12[[i*4+(h+1)]]<-data.frame(group_by(r1,Stkcd)%>%summarise(d=sd(Dretwd)/sd(Cdretwdos))%>%filter(d!="NA",d!="NaN"),Trdmnt=200501+3*h+100*i)[,c(1,3,2)]
}}
badj<-read_excel("badj.xlsx",sheet = 1);roaj<-read_excel("roaj.xlsx",sheet = 1);capj<-read_excel("capj.xlsx",sheet = 1)
netj<-read_excel("netj.xlsx",sheet = 1);costj<-read_excel("costj.xlsx",sheet = 1);otherj<-read_excel("otherj.xlsx",sheet = 1)
hdj<-read_excel("hdj.xlsx",sheet = 1);changej<-read_excel("changej.xlsx",sheet = 1);investj<-read_excel("investj.xlsx",sheet = 1)
servicej<-read_excel("servicej.xlsx",sheet = 1);netimj<-read_excel("netimj.xlsx",sheet = 1);incomej<-read_excel("incomej.xlsx",sheet = 1)
equityj<-read_excel("equityj.xlsx",sheet = 1);assetj<-read_excel("assetj.xlsx",sheet = 1);depositj<-read_excel("depositj.xlsx",sheet = 1)
loanj<-read_excel("loanj.xlsx",sheet = 1);roej<-read_excel("roej.xlsx",sheet = 1)
badj1<-as.matrix(badj[,3:78])%>%t();dim(badj1)<-c(76*33,1)
roaj1<-as.matrix(roaj[,3:78])%>%t();dim(roaj1)<-c(76*33,1)
capj1<-as.matrix(capj[,3:78])%>%t();dim(capj1)<-c(76*33,1)
roej1<-as.matrix(roej[,3:78])%>%t();dim(roej1)<-c(76*33,1)
netj1<-as.matrix(netj[,3:78])%>%t();dim(netj1)<-c(76*33,1)
costj1<-as.matrix(costj[,3:78])%>%t();dim(costj1)<-c(76*33,1)
otherj1<-as.matrix(otherj[,3:78])%>%t();dim(otherj1)<-c(76*33,1)
hdj1<-as.matrix(hdj[,3:78])%>%t();dim(hdj1)<-c(76*33,1)
changej1<-as.matrix(changej[,3:78])%>%t();dim(changej1)<-c(76*33,1)
investj1<-as.matrix(investj[,3:78])%>%t();dim(investj1)<-c(76*33,1)
servicej1<-as.matrix(servicej[,3:78])%>%t();dim(servicej1)<-c(76*33,1)
netimj1<-as.matrix(netimj[,3:78])%>%t();dim(netimj1)<-c(76*33,1)
incomej1<-as.matrix(incomej[,3:78])%>%t();dim(incomej1)<-c(76*33,1)
equityj1<-as.matrix(equityj[,3:78])%>%t();dim(equityj1)<-c(76*33,1)
assetj1<-as.matrix(assetj[,3:78])%>%t();dim(assetj1)<-c(76*33,1)
depositj1<-as.matrix(depositj[,3:78])%>%t();dim(depositj1)<-c(76*33,1)
loanj1<-as.matrix(loanj[,3:78])%>%t();dim(loanj1)<-c(76*33,1)
a<-cs1[rep(1:nrow(cs1),each=76),];a1<-data.frame(Trdmnt=rep(seq(as.Date("2000/3/1"),as.Date("2018/12/1"),"quarter"),33))
a2<-data.frame(a,Trdmnt=as.numeric(str_sub(str_replace_all(a1$Trdmnt,"-",""),start=1L,end=6L)))
a3<-data.frame(a2,badj=as.numeric(badj1),roaj=as.numeric(roaj1),capj=as.numeric(capj1),roej=as.numeric(roej1),
assetj=as.numeric(assetj1),equityj=as.numeric(equityj1),incomej=as.numeric(incomej1),
netimj=as.numeric(netimj1),servicej=as.numeric(servicej1),investj=as.numeric(investj1),
changej=as.numeric(changej1),hdj=as.numeric(hdj1),otherj=as.numeric(otherj1),
costj=as.numeric(costj1),netj=as.numeric(netj1),depositj=as.numeric(depositj1),loanj=as.numeric(loanj1))
cappj<-select(a3,1:3,equityj,assetj)%>%mutate(cappj=equityj/assetj*100)%>%select(-2,-4,-5)
a31<-mutate(a3,loa=loanj/assetj*100,gap=(loanj-depositj)/assetj*100,noint=(incomej-netimj)/incomej*100,
ser=servicej/incomej*100,exc=hdj/incomej*100,inv=investj/incomej*100,oth=(changej+otherj)/incomej*100,
cos=costj/incomej*100)%>%select(-(9:18))
a32<-arrange(a31,Trdmnt)%>%group_by(Stkcd)%>%slice(-1)%>%tbl_df()
a33<-arrange(a31,Trdmnt)%>%group_by(Stkcd)%>%slice(-n())%>%tbl_df()
a34<-data.frame(a32,asset1j=a33$assetj,deposit1j=a33$depositj,loan1j=a33$loanj)
a35<-mutate(a34,assetgrow=(assetj-asset1j)/asset1j*100,depgro=(depositj-deposit1j)/deposit1j*100,
lngro=(loanj-loan1j)/loan1j*100)%>%select(-(9:10),-(19:21))%>%arrange(Stkcd,Trdmnt)%>%left_join(cappj,by=c("Stkcd","Trdmnt"))
a4<-rep(seq(as.Date("2000/7/1"),as.Date("2019/1/1"),"quarter"),33)
a5<-data.frame(a35,t=as.numeric(str_sub(str_replace_all(a4,"-",""),start=1L,end=6L)))%>%select(-Trdmnt)
tr<-do.call("rbind",d);cr<-do.call("rbind",d1);br<-do.call("rbind",d2);pr<-do.call("rbind",d11);dr<-do.call("rbind",d12)
tr1<-filter(tr,tβ>0);cr1<-filter(cr,crisk>0);br1<-filter(br,brisk>0);pr1<-filter(pr,p>0);dr1<-filter(dr,d>0)
ris1<-full_join(tr1,cr1,by=c("Stkcd","Trdmnt"))%>%full_join(br1,by=c("Stkcd","Trdmnt"))%>%full_join(pr1,by=c("Stkcd","Trdmnt"))%>%full_join(dr1,by=c("Stkcd","Trdmnt"))
ris2<-left_join(ris1,a5,by=c("Stkcd","Trdmnt"="t"))%>%arrange(Stkcd)%>%select(-name)%>%select(-roej,-depgro,-lngro)
ris3<-filter(ris2,cappj!="NA",roaj!="NA",loa!="NA",gap!="NA",noint!="NA",cos!="NA",badj!="NA",
assetgrow!="NA",ser!="NA",exc!="NA",inv!="NA",oth!="NA")
res<-lm(log(assetj)~cappj+roaj+loa+gap+noint+cos+badj+assetgrow,ris3)$residuals
ris4<-data.frame(ris3,rsize=res)
result<-plm(log(tβ) ~ rsize+cappj+roaj+loa+gap+noint+cos+badj+assetgrow,model = "within",effect = "time",
data = ris4, index = c("Stkcd","Trdmnt"))
summary(result)
m<-as.matrix(summary(result)$coefficients)[,c(1,3,4)]
write.csv(m,"m.csv")
res<-lm(log(assetj)~cappj+roaj+loa+gap+ser+exc+inv+oth+cos+badj+assetgrow,ris3)$residuals
ris5<-data.frame(ris3,rsize=res)
result<-plm(log(brisk) ~ rsize+cappj+roaj+loa+gap+ser+exc+inv+oth+cos+badj+assetgrow,model = "within",effect = "time",
data = ris5, index = c("Stkcd","Trdmnt"))
summary(result)
m<-as.matrix(summary(result)$coefficients)[,c(1,3,4)]
write.csv(m,"m.csv")
result<-plm(log(tβ) ~ cap+roa+loa+gap+noint+cos+badln+assetgrow+equitytodebt+retainedearn+assetturn+lngro+depgro+sub+dou+los,model = "within",effect = "time",
data = ris3, index = c("Stkcd","Trdmnt"))
summary(result)
ms1<-select(ris3,-1,-2)%>%mutate(ln=log(assetj));summary(ms1);apply(ms1,2,sd)
稳健性检验
a4<-rep(seq(as.Date("2000/10/1"),as.Date("2019/4/1"),"quarter"),33)
a4<-rep(seq(as.Date("2001/1/1"),as.Date("2019/7/1"),"quarter"),33)
result<-plm(log(tβ) ~ rsize+cappj+roaj+loa+gap+noint+cos+badj+assetgrow,model = "within",effect = "individual",
data = ris4, index = c("Stkcd","Trdmnt"))
summary(result)
m<-as.matrix(summary(result)$coefficients)[,c(1,3,4)]
write.csv(m,"m.csv")
result<-plm(log(tβ) ~ rsize+cappj+roaj+loa+gap+noint+cos+badj+assetgrow,model = "within",effect = "twoway",
data = ris4, index = c("Stkcd","Trdmnt"))
summary(result)
m<-as.matrix(summary(result)$coefficients)[,c(1,3,4)]
write.csv(m,"m.csv")
ris3<-arrange(ris3,assetj)%>%slice(1:(n()/2))
ris3<-arrange(ris3,assetj)%>%slice((n()/2+1):n())
findex<-read_excel("index.xlsx",sheet = 1)%>%select(2,3,12)%>%rename(Stkcd="简称",Clsdt="日期",Dretwd="涨跌幅(%)")%>%filter(Dretwd!="--")
findex1<-data.frame(findex[,c(1,2)],Dretwd=as.numeric(findex$Dretwd))
findex1<-(data.frame(findex1,Trdmnt=as.numeric(str_replace_all(findex1$Clsdt,"-","")))%>%select(-2))[,c(1,3,2)]
cs<-filter(findex1,Stkcd=="沪深300")%>%mutate(Cdretwdos=Dretwd/100)%>%select(-1,-3)
res<-lm(log(assetj)~log(p)+log(d)+cappj+roaj+loa+gap+noint+cos+badj+assetgrow,ris3)$residuals
ris4<-data.frame(ris3,rsize=res)
result<-plm(log(brisk) ~ log(p)+log(d)+rsize+cappj+roaj+loa+gap+noint+cos+badj+assetgrow,model = "within",effect = "time",
data = ris4, index = c("Stkcd","Trdmnt"))
summary(result)
m<-as.matrix(summary(result)$coefficients)[,c(1,3,4)]
write.csv(m,"m.csv")
求出各个银行的系统性尾部风险
tt<-group_by(tr,Stkcd)%>%summarise(t=mean(tβ))
cc<-group_by(cr,Stkcd)%>%summarise(c=mean(crisk))
bb<-group_by(br,Stkcd)%>%summarise(b=mean(brisk))
write.csv(tt,"tt.csv",row.names = FALSE);write.csv(cc,"cc.csv",row.names = FALSE);write.csv(bb,"bb.csv",row.names = FALSE)
上市时间 | 银行名称 | 系统性尾部风险 | 传染性风险 | 银行尾部风险 |
---|
1991-04-03 | 平安银行 | 1.034 | 0.866 | 1.195 |
2007-07-19 | 宁波银行 | 1.005 | 0.834 | 1.204 |
1999-11-10 | 浦发银行 | 1.003 | 0.872 | 1.149 |
2003-09-12 | 华夏银行 | 1.046 | 0.859 | 1.215 |
2000-12-19 | 民生银行 | 0.896 | 0.846 | 1.061 |
2002-04-09 | 招商银行 | 0.900 | 0.871 | 1.032 |
2007-07-19 | 南京银行 | 0.945 | 0.840 | 1.124 |
2007-02-05 | 兴业银行 | 0.994 | 0.849 | 1.170 |
2007-09-19 | 北京银行 | 0.923 | 0.838 | 1.101 |
2010-07-15 | 农业银行 | 0.601 | 0.784 | 0.767 |
2007-05-15 | 交通银行 | 0.796 | 0.836 | 0.951 |
2006-10-27 | 工商银行 | 0.604 | 0.800 | 0.755 |
2010-08-18 | 光大银行 | 0.908 | 0.831 | 1.093 |
2007-09-25 | 建设银行 | 0.668 | 0.804 | 0.832 |
2006-07-05 | 中国银行 | 0.619 | 0.803 | 0.774 |
2007-04-27 | 中信银行 | 0.955 | 0.820 | 1.167 |