Python 宏观经济报告

Python宏观经济报告importmatplotlib.pyplotaspltimportmatplotlibimportpandasaspdimportnumpyasnpimportxlrdfrommpl_toolkits.axisartist.parasite_axesimportHostAxes,ParasiteAxesimportmatplotlib.datesasmdatesfromdatetimeimportdatetimeio="D:\【ZW】交接材料2020.10.21\宏观\k1.xls"data=pd.read_excel(io,header=0,#index_col=0,engine='xlrd')#设置显示中文plt.rcParams['font.sans-serif']=['SimHei']#显示中文标签plt.rcParams['axes.unicode_minus']=False#这两行需要手动设置#看看数据张啥样#print(data)x=data["指标名称"]data["铜/金"]=data["期货结算价(活跃合约):COMEX铜"]/data['期货收盘价(活跃合约):COMEX黄金']data["油/金"]=data["期货结算价(连续):WTI原油"]/data['期货收盘价(活跃合约):COMEX黄金']y1=data["铜/金"]y2=data["油/金"]y3=data["美国:国债收益率:10年"]#y4=data['美国:联邦目标利率']y4todo=data['美国:联邦目标利率']print(data["美国:CPI"])y5todo=data["美国:CPI:当月同比"]y4=[np.nan]top=len(y4todo)foriinrange(1,top):ify4todo[i]!=y4todo[i]:y4.append(y4[i-1])else:y4.append(y4todo[i])nexty5=[np.nan]top=len(y5todo)foriinrange(1,top):ify5todo[i]!=y5todo[i]:y5.append(y5[i-1])else:y5.append(y5todo[i])next#print(y4)#画第一个图deffigdraw1():#创建图表fig=plt.figure(figsize=(35,5),dpi=90)matplotlib.rcParams.update({'font.size':22})plt.subplots_adjust(left=0.02,right=0.99,top=0.95,bottom=0.05)#设置图形位置，[左，下，宽，高]左+宽应当小于1，下和高亦然ax_mainy1=HostAxes(fig,[0.05,0.1,0.85,0.8])#设置y轴坐标#设置子图ax，设置共享x轴ax_y2=ParasiteAxes(ax_mainy1,sharex=ax_mainy1)ax_y3=ParasiteAxes(ax_mainy1,sharex=ax_mainy1)ax_y4=ParasiteAxes(ax_mainy1,sharex=ax_mainy1)ax_y5=ParasiteAxes(ax_mainy1,sharex=ax_mainy1)#为主图添加共享y轴的子图ax_y2、ax_y3ax_mainy1.parasites.append(ax_y2)ax_mainy1.parasites.append(ax_y3)ax_mainy1.parasites.append(ax_y4)ax_mainy1.parasites.append(ax_y5)#为子图设置固定的副轴y2_axisline=ax_y2.get_grid_helper().new_fixed_axisy3_axisline=ax_y3.get_grid_helper().new_fixed_axisy4_axisline=ax_y4.get_grid_helper().new_fixed_axisy5_axisline=ax_y5.get_grid_helper().new_fixed_axis#设置副轴偏移量#ax_mainy1.axis['right']=y2_axisline(loc='right',axes=ax_mainy1,offset=(0,0))ax_y2.axis['right']=y2_axisline(loc='right',axes=ax_y2,offset=(0,0))ax_y3.axis['right']=y3_axisline(loc='right',axes=ax_y3,offset=(90,0))ax_y4.axis['right']=y3_axisline(loc='right',axes=ax_y4,offset=(150,0))ax_y5.axis['right']=y3_axisline(loc='right',axes=ax_y5,offset=(210,0))#为fig增加子图fig.add_axes(ax_mainy1)#隐藏主图上轴、右轴ax_mainy1.axis['right'].set_visible(False)ax_mainy1.axis['top'].set_visible(False)ax_y2.axis['right'].set_visible(True)ax_y2.axis['right'].major_ticklabels.set_visible(True)ax_y2.axis['right'].label.set_visible(True)#给坐标轴取名字ax_mainy1.set_ylabel("Cu/Au")ax_mainy1.set_xlabel("Time")ax_y2.set_ylabel("Oil/Au")ax_y3.set_ylabel("10YYield")ax_y4.set_ylabel("US:FedFundsRate")ax_y5.set_ylabel("US:CPI,YoY")#alpha定义透明度，越小越透明，facecolor:柱子颜色；edgecolor：轮廓颜色，lw：轮廓宽度,label图例ax_mainy1.plot(x,y1,color="blueviolet",label="Cu/Au")ax_y2.plot(x,y2,color="aquamarine",label="Oil/Au")ax_y3.plot(x,y3,color="cornflowerblue",label="10YYield")ax_y4.plot(x,y4,color="black",label="US:FedFundsRate")ax_y5.plot(x,y5,color="firebrick",label="US:CPI,YoY")#添加图例,第一个是横坐标，越小越靠左，第二个是纵坐标，越接近1越靠上ax_mainy1.legend()plt.savefig('D:\【ZW】交接材料2020.10.21\宏观\\macro0.png')y6=data["美国:国债实际收益率:5年期"]y7=data["美国:国债实际收益率:10年期"]y8todo=data['美国:所有联储银行:资产:持有证券:美国国债;短期债券']y9todo=data['美国:所有联储银行:资产:持有证券:美国国债;中长期名义债券']y10todo=data['美国:所有联储银行:资产:持有证券:美国国债;中长期通胀指数债券']y11todo=data['美国:所有联储银行:资产:持有证券:美国国债;通胀补偿债券']y12todo=data['美国:所有联储银行:资产:持有证券:联邦机构债券']y13todo=data["美国:所有联储银行:资产:持有证券:抵押贷款支持债券(MBS)"]y8=[np.nan]top2=len(y8todo)foriinrange(1,top2):ify8todo[i]!=y8todo[i]:y8.append(y8[i-1])else:y8.append(y8todo[i])nexty9=[np.nan]top2=len(y9todo)foriinrange(1,top2):ify9todo[i]!=y9todo[i]:y9.append(y9[i-1])else:y9.append(y9todo[i])nexty10=[np.nan]top2=len(y10todo)foriinrange(1,top2):ify10todo[i]!=y10todo[i]:y10.append(y10[i-1])else:y10.append(y10todo[i])nexty11=[np.nan]top2=len(y11todo)foriinrange(1,top2):ify11todo[i]!=y11todo[i]:y11.append(y11[i-1])else:y11.append(y11todo[i])nexty12=[np.nan]top2=len(y12todo)foriinrange(1,top2):ify12todo[i]!=y12todo[i]:y12.append(y12[i-1])else:y12.append(y12todo[i])nexty13=[np.nan]top2=len(y13todo)foriinrange(1,top2):ify13todo[i]!=y13todo[i]:y13.append(y13[i-1])else:y13.append(y13todo[i])nextytttodo=data['美国:外国投资者持有美国国债:合计']yjptodo=data['美国:外国投资者持有美国国债:日本']ycntodo=data['美国:外国投资者持有美国国债:中国']yottodo=ytttodo-yjptodo-ycntodoytt=[np.nan]top3=len(ytttodo)foriinrange(1,top3):ifytttodo[i]!=ytttodo[i]:ytt.append(ytt[i-1])else:ytt.append(ytttodo[i])nextyjp=[np.nan]top3=len(yjptodo)foriinrange(1,top3):ifyjptodo[i]!=yjptodo[i]:yjp.append(yjp[i-1])else:yjp.append(yjptodo[i])nextyot=[np.nan]top3=len(yottodo)foriinrange(1,top3):ifyottodo[i]!=yottodo[i]:yot.append(yot[i-1])else:yot.append(yottodo[i])nextycn=[np.nan]top3=len(ycntodo)foriinrange(1,top3):ifycntodo[i]!=ycntodo[i]:ycn.append(ycn[i-1])else:ycn.append(ycntodo[i])nextyaulongtodo=data['COMEX:黄金:报告头寸多头总持仓:持仓数量占比']yaushorttodo=data['COMEX:黄金:报告头寸空头总持仓:持仓数量占比']yculongtodo=data['COMEX:1号铜:报告头寸多头总持仓:持仓数量']/(data['COMEX:1号铜:报告头寸多头总持仓:持仓数量']+data['COMEX:1号铜:报告头寸空头总持仓:持仓数量'])ycushorttodo=data['COMEX:1号铜:报告头寸空头总持仓:持仓数量']/(data['COMEX:1号铜:报告头寸多头总持仓:持仓数量']+data['COMEX:1号铜:报告头寸空头总持仓:持仓数量'])yoillongtodo=data['IPE:WTI原油:期货(新版):生产商/贸易商/加工商/用户:多头持仓:持仓数量占比']yoilshorttodo=data['IPE:WTI原油:期货(新版):生产商/贸易商/加工商/用户:空头持仓:持仓数量占比']yaulong=[np.nan]top4=len(yaulongtodo)foriinrange(1,top4):ifyaulongtodo[i]!=yaulongtodo[i]:yaulong.append(yaulong[i-1])else:yaulong.append(yaulongtodo[i])nextyaushort=[np.nan]top3=len(yaushorttodo)foriinrange(1,top3):ifyaushorttodo[i]!=yaushorttodo[i]:yaushort.append(yaushort[i-1])else:yaushort.append(yaushorttodo[i])nextyculong=[np.nan]top3=len(yculongtodo)foriinrange(1,top3):ifyculongtodo[i]!=yculongtodo[i]:yculong.append(yculong[i-1])else:yculong.append(yculongtodo[i])nextycushort=[np.nan]top3=len(ycushorttodo)foriinrange(1,top3):ifycushorttodo[i]!=ycushorttodo[i]:ycushort.append(ycushort[i-1])else:ycushort.append(ycushorttodo[i])nextyoillong=[np.nan]top3=len(yoillongtodo)foriinrange(1,top3):ifyoillongtodo[i]!=yoillongtodo[i]:yoillong.append(yoillong[i-1])else:yoillong.append(yoillongtodo[i])nextyoilshort=[np.nan]top3=len(yoilshorttodo)foriinrange(1,top3):ifyoilshorttodo[i]!=yoilshorttodo[i]:yoilshort.append(yoilshort[i-1])else:yoilshort.append(yoilshorttodo[i])next#画第二个图deffigdraw2():matplotlib.rcParams.update({'font.size':22})fig=plt.figure(figsize=(30,20),dpi=90)plt.subplots_adjust(left=0.05,right=0.9,top=0.95,bottom=0.05)#第一个子图ax1=fig.add_subplot(511)ax1.plot(x,y6,color="blue",label="5YRealYield",linewidth=2)ax1.plot(x,y7,color="olivedrab",label="7YRealYield",linewidth=2)ax1.plot(x,y4,color="mediumblue",linewidth=4,label="FedFundsRate")ax1.set_yticks(np.linspace(-0.5,1,4))ax2=ax1.twinx()ax2.plot(x,y5,color="black",label="CPIYoY",linewidth=1)ax3=ax1.twinx()ax3.plot(x,data['期货收盘价(活跃合约):COMEX黄金'],color="darkorange",label="Au")ax1.legend(loc='centerleft',bbox_to_anchor=(0.3,1.12),ncol=3,frameon=False)ax2.legend(loc='centerleft',bbox_to_anchor=(0.2,1.12),ncol=2,frameon=False)ax3.legend(loc='centerleft',bbox_to_anchor=(0.1,1.12),ncol=1,frameon=False)box=ax1.get_position()#想将图例放上面就box.height*0.8，放右边就box.width*0.8ax1.set_position([box.x0,box.y0,box.width,box.height*0.8])country=["CN","JP","Others"]colors=["orange","plum","seagreen"]#第二个子图axn2=fig.add_subplot(523)axn2.stackplot(x,ycn,yjp,yot,labels=country,colors=colors)#axn2.plot(x,ycn,color="red",label="China")#axn2.plot(x,yjp,color="green",label="Japan")#axn2.plot(x,yot,color="lightblue",label="Others")axn2.legend(loc=2)axn1=fig.add_subplot(524)axn1.plot(x,y8,color="black",label="T-bills")axn1.plot(x,y9,color="blue",label="T-notes&Bonds")axn1.plot(x,y10,color="lime",label="inflationindexedsecurity")axn1.plot(x,y11,color="orange",label="TIPS")axn1.plot(x,y12,color="brown",linewidth=1,label="FederalAgencyBond")axn1.plot(x,y13,color="steelblue",label="MBS")axn1.legend(loc=2)#第三个子图axn2=fig.add_subplot(513)axn2.stackplot(x,data["总库存:LME铜"],labels=["LMEcopperinventory"])axn22=axn2.twinx()axn22.plot(x,data['现货结算价:LME铜'],color="black",label="LMEcopperfutures",linewidth=4)axn23=axn2.twinx()axn23.plot(x,yculong,color='maroon',label='LMECULong',linewidth=1)axn23.plot(x,ycushort,color='darkslategrey',label='LMECUShort',linewidth=1)axn2.legend(loc='centerleft',bbox_to_anchor=(1.03,0.5),ncol=1,frameon=False)axn22.legend(loc='centerleft',bbox_to_anchor=(1.03,0.7),ncol=1,frameon=False)axn23.legend(loc='centerleft',bbox_to_anchor=(1.03,0.9),ncol=1,frameon=False)box=axn2.get_position()#想将图例放上面就box.height*0.8，放右边就box.width*0.8axn2.set_position([box.x0,box.y0,box.width*0.9,box.height])axn3=fig.add_subplot(514)axn3.stackplot(x,data["SPDR:黄金ETF:持有量(吨)"],color="gold",labels=["COMEXGoldInventory"])axn32=axn3.twinx()axn32.plot(x,data['期货收盘价(活跃合约):COMEX黄金'],color="black",label="COMEXGoldFutures",linewidth=4)axn33=axn32.twinx()axn33.plot(x,yaulong,color='maroon',label='COMEXAULong',linewidth=2)axn33.plot(x,yaushort,color='darkslategrey',label='COMEXAUShort',linewidth=2)axn3.legend(loc='centerleft',bbox_to_anchor=(1.03,0.5),ncol=1,frameon=False)axn32.legend(loc='centerleft',bbox_to_anchor=(1.03,0.7),ncol=1,frameon=False)axn33.legend(loc='centerleft',bbox_to_anchor=(1.03,0.9),ncol=1,frameon=False)box=axn3.get_position()#想将图例放上面就box.height*0.8，放右边就box.width*0.8axn3.set_position([box.x0,box.y0,box.width*0.9,box.height])axn4=fig.add_subplot(515)axn4.stackplot(x,data["库存量:商业原油:全美:日(工作日)"],labels=["OilInventory"])axn42=axn4.twinx()axn42.plot(x,data['期货结算价(连续):WTI原油'],color="black",label="WTIOilFutures",linewidth=4)axn43=axn42.twinx()axn43.plot(x,yoillong,color='maroon',label='OilLong',linewidth=2)axn43.plot(x,yoilshort,color='darkslategrey',label='OilShort',linewidth=2)axn4.legend(loc='centerleft',bbox_to_anchor=(1.03,0.5),ncol=1,frameon=False)axn42.legend(loc='centerleft',bbox_to_anchor=(1.03,0.7),ncol=1,frameon=False)axn43.legend(loc='centerleft',bbox_to_anchor=(1.03,0.9),ncol=1,frameon=False)box=axn4.get_position()#想将图例放上面就box.height*0.8，放右边就box.width*0.8axn4.set_position([box.x0,box.y0,box.width*0.9,box.height])plt.savefig('D:\【ZW】交接材料2020.10.21\宏观\\macro1.png')#plt.show()deffigdraw3():io2="D:\【ZW】交接材料2020.10.21\宏观\k2.xls"data2=pd.read_excel(io2,header=0,#index_col=0,engine='xlrd')monthsFmt=matplotlib.dates.DateFormatter('%b')matplotlib.rcParams.update({'font.size':18})#风险板块x2=data2['指标名称']yf1todo=data2['美国:经济政策不确定性指数:按用途加权']yf1=[np.nan]top3=len(yf1todo)foriinrange(1,top3):ifyf1todo[i]!=yf1todo[i]:yf1.append(yf1[i-1])else:yf1.append(yf1todo[i])nextyfgold=data2['期货结算价(活跃合约):COMEX黄金']yfvix=data2['美国:标准普尔500波动率指数(VIX)']yf2todo=data2['美国:企业债券日均成交量:公开交易:高收益']/data2['美国:企业债券日均成交量:公开交易:投资级']yf2=[np.nan]top3=len(yf2todo)foriinrange(1,top3):ifyf2todo[i]!=yf2todo[i]:yf2.append(yf2[i-1])else:yf2.append(yf2todo[i])nextyjpy=1/data2['美元兑日元']ybk=data2['美国:纳斯达克银行指数:收盘价']yspx=data2['美国:标准普尔500指数']yRE=data2['美国:威尔希尔美国房地产证券市场总指数']ybk_spx=ybk/yspxyRE_spx=yRE/yspxyHY_spxtodo=data2['美国:企业债券日均成交量:公开交易:高收益']/yspxyHY_spx=[np.nan]top3=len(yHY_spxtodo)foriinrange(1,top3):ifyHY_spxtodo[i]!=yHY_spxtodo[i]:yHY_spx.append(yHY_spx[i-1])else:yHY_spx.append(yHY_spxtodo[i])next#==================================================================fign=plt.figure(figsize=(25,15),dpi=90)plt.subplots_adjust(left=0.05,right=0.95,top=0.95,bottom=0.05)axf4=fign.add_subplot(7,4,1)axf4.plot(x2,yf2,label='HYG/LOD')axf4.xaxis.set_major_formatter(monthsFmt)axf4.legend()axf1=fign.add_subplot(7,4,5)axf1.plot(x2,yf1,color='r',label="EPUIndex")axf1.xaxis.set_major_formatter(monthsFmt)axf1.legend()axf3=fign.add_subplot(749)axf3.plot(x2,yfvix,color='r',label="VIX")axf3.xaxis.set_major_formatter(monthsFmt)axf3.legend()axf7=fign.add_subplot(7,4,13)axf7.plot(x2,yHY_spx,color='r',label="HighYield/SPX")axf7.xaxis.set_major_formatter(monthsFmt)axf7.legend()axf5=fign.add_subplot(7,4,17)axf5.plot(x2,yjpy,color='r',label="JPY/USD")axf5.xaxis.set_major_formatter(monthsFmt)axf5.legend()axf2=fign.add_subplot(7,4,21)axf2.plot(x2,yfgold,color='r',label="Gold")axf2.xaxis.set_major_formatter(monthsFmt)axf2.legend()#汇率yUSD_RMB=data2['即期汇率(23:30):美元兑人民币']yUSD_JPY=data2['美元兑日元']yUSD_EUR=1/data2['欧元兑美元']yRMB_EUR=1/data2['中间价:欧元兑人民币']yBTC=data2['成交均价:比特币:Bitstamp(USD)']axc1=fign.add_subplot(742)axc1.plot(x2,yUSD_RMB,label="USD/RMB")axc1.xaxis.set_major_formatter(monthsFmt)axc1.legend()axc2=fign.add_subplot(746)axc2.plot(x2,yUSD_JPY,label="USD/JPY")axc2.xaxis.set_major_formatter(monthsFmt)axc2.legend()axc3=fign.add_subplot(7,4,10)axc3.plot(x2,yUSD_EUR,label="USD/EUR")axc3.xaxis.set_major_formatter(monthsFmt)axc3.legend()axc4=fign.add_subplot(7,4,14)axc4.plot(x2,yRMB_EUR,label='RMB/EUR')axc4.xaxis.set_major_formatter(monthsFmt)axc4.legend()axc5=fign.add_subplot(7,4,18)axc5.plot(x2,yBTC,label="BTC")axc5.xaxis.set_major_formatter(monthsFmt)axc5.legend()#指数yszzh=data2['上证综合指数']yhs300=data2['沪深300指数']ycyb=data2['创业板指数']ykcb=data2['上证科创板50成份指数']axz1=fign.add_subplot(743)axz1.plot(x2,yszzh,label="上证综指")axz1.xaxis.set_major_formatter(monthsFmt)axz1.legend()axz2=fign.add_subplot(747)axz2.plot(x2,yhs300,label="沪深300")axz2.xaxis.set_major_formatter(monthsFmt)axz2.legend()axz3=fign.add_subplot(7,4,11)axz3.plot(x2,ycyb,label="创业板")axz3.xaxis.set_major_formatter(monthsFmt)axz3.legend()axz4=fign.add_subplot(7,4,15)axz4.plot(x2,ykcb,label="科创板")axz4.xaxis.set_major_formatter(monthsFmt)axz4.legend()axz5=fign.add_subplot(7,4,19)axz5.plot(x2,yspx,label="SPX")axz5.xaxis.set_major_formatter(monthsFmt)axz5.legend()#货币和债券y10y=data2['国债到期收益率:10年']y5y=data2['国债到期收益率:5年']#y2y=data['国债到期收益率:2年']y6m=data2['国债到期收益率:6个月']y7d=data2['逆回购利率:7天']y7drepo=data2['7天回购利率:加权平均:最近1周(B1W)']ytftodo=data2['公开市场操作:货币净投放']ytf=[np.nan]ytf8=[np.nan]top3=len(ytftodo)foriinrange(1,top3):ifytftodo[i]!=ytftodo[i]:ytf.append(ytf[i-1])ytf8.append(ytf8[i-1])else:ytf.append(ytftodo[i])ytf8.append(0)nextyshibor=data2['SHIBOR:隔夜']axh1=fign.add_subplot(544)axh1.plot(x2,y10y,label="10Y&5Yyield(r)")axh1.plot(x2,y5y,color='red')axh1.xaxis.set_major_formatter(monthsFmt)axh1.legend()axh3=fign.add_subplot(548)axh3.plot(x2,y10y,label="10Y&6Myield(r)")axh3.plot(x2,y6m,color='red')axh3.xaxis.set_major_formatter(monthsFmt)#axh3.xaxis.set_major_formatter(mdates.DateFormatter('%m'))axh3.legend()axh5=fign.add_subplot(5,4,12)axh5.plot(x2,yshibor,label="Shibor&R007(r)&7DRepo(g)")axh5.plot(x2,y7d,color="green")axh5.plot(x2,y7drepo,color="r")axh5.xaxis.set_major_formatter(monthsFmt)axh5.legend()axh7=fign.add_subplot(5,4,16)axh7.plot(x2,ytf,label="货币净投放")axh7.plot(x2,ytf8,color="black")axh7.xaxis.set_major_formatter(mdates.DateFormatter('%m%d'))axh7.legend()plt.savefig('D:\【ZW】交接材料2020.10.21\宏观\\macro2.png')#figdraw1()#figdraw2()figdraw3()下面代码是处理原始数据的：importmatplotlib.pyplotaspltimportpandasaspdimportnumpyasnpimportxlrdfrommpl_toolkits.axisartist.parasite_axesimportHostAxes,ParasiteAxesdefclean():io="D:\【ZW】交接材料2020.10.21\宏观\全球宏观.xls"dataraw=pd.read_excel(io,header=0,index_col=0,engine='xlrd')datato=dataraw.tail(1095)