国际金融习题2：数据处理与应用

樊潇彦 复旦大学经济学院

1. 准备工作

# 用 install.packages("**") 安装命令包后调用：
library(readxl)               # 读取excel数据
library(stringr)              # 字符串处理
library(corrplot)
library(igraph)
library(forecast) 
library(stats)                # 基础包，不用安装直接调用
library(dplyr)
library(tidyr)
library(data.table)  
library(foreign)
library(readstata13)
library(haven)   
library(ggplot2)
library(ggrepel)
library(dygraphs)     
library(plotrix)      
library(lubridate)    
library(zoo)          
library(mFilter) 
library(RCurl)
library(httr)
set_config( config( ssl_verifypeer = 0L ))
library(Quandl)
Quandl.api_key("...")         # 在Quandl（https://www.quandl.com）免费申请 api_key
setwd("D:\\...")              # 设置工作目录
rm(list=ls())                 # 清内存

2. 国际清算银行（BIS）有效汇率

从国际清算银行网页（http://www.bis.org/statistics/full_data_sets.htm）下载有效汇率数据 "Effective exchange rate indices (monthly).csv"，整理后取61个国家和地区加权的名义有效汇率做图：

3. 从Quandl下载BRICS数据

在 Quandl 注册一个免费帐号，从 https://www.quandl.com/collections/economics 下载各类经济数据。

3.1 货币兑美元汇率

currency=c("BRL","ZAR","CNY","RUB","INR") 
currency_name=data.frame(currency=currency,
                         name=c("巴西-雷亚尔","南非-兰特","中国-元","俄罗斯-卢布","印度-卢比"),
                         stringsAsFactors =F)
list=paste("CURRFX/USD",currency,sep="")       # Currency Exchange Rates - USD 
E_daily=data.frame()
for (i in 1:length(currency)){
  E=Quandl(list[i], start_date="1970-01-01")
  E=E%>% select(Date,Rate)%>%mutate(currency=currency[i])
  E_daily=rbind(E_daily,E)
}
E_daily=E_daily%>%
  left_join(currency_name,by="currency")%>%
  mutate(label=ifelse(Date==as.Date("2013-06-04"),name,""))
ggplot(E_daily[E_daily$Rate<1000,],aes(Date,Rate,color=name))+geom_line()+
  labs(title="金砖五国货币兑美元汇率",x="",y="")+
  geom_text(aes(label=label,vjust =-0.5))+
  guides(color=guide_legend(title=NULL))+theme_bw()+theme(legend.position="non")

3.2 宏观经济数据

country=c("USA","BRA","CHN","IND","RUS","ZAF") 
list_pi=paste("WORLDBANK/",country,"_NY_GDP_DEFL_KD_ZG", sep="")    # Inflation, GDP deflator (annual %)
list_g=paste("ODA/",country,"_NGDP_RPCH",sep="")                    # GDP at Constant Prices, % change
list_M2NGDP=paste("WORLDBANK/",country,"_FM_LBL_MQMY_GD_ZS",sep="") # Money and quasi money (M2) as % of GDP
list_NGDP=paste("ODA/",country,"_NGDP", sep="")                     # GDP at Current Prices, LCU Billions
list_i=paste("WORLDBANK/",country,"_FR_INR_LEND",sep="")  # Lending interest rate (%) 存款利率部分国家没有 
list_ca=paste("ODA/",country,"_BCA_NGDPD",sep="")                   # Current Account Balance, % of GDP
list_var=c("pi","g","M2NGDP","NGDP","lendi","ca")
Data=data.frame() 
for (i in 1:length(country)){ 
  pi=Quandl(list_pi[i])
  g=Quandl(list_g[i])
  M2NGDP=Quandl(list_M2NGDP[i])
  NGDP=Quandl(list_NGDP[i])
  lendi=Quandl(list_i[i])
  ca=Quandl(list_ca[i])
  var=rep(list_var,time=c(nrow(pi),nrow(g),nrow(M2NGDP),nrow(NGDP),nrow(lendi),nrow(ca)))
  D=rbind(pi,g,M2NGDP,NGDP,lendi,ca)
  Data=rbind(Data,data.frame(D,var=var,country=rep(country[i],nrow(D))))
  rm(list=list_var)
}

3.3 数据合并

# save(E_daily,Data,file="BRICS.RData")       # 上述数据下载较慢，已另存
load("BRICS.Rdata")                           # 直接加载
cc=data.frame(currency=c("USD","BRL","CNY","INR","RUB","ZAR"),
              country=c("USA","BRA","CHN","IND","RUS","ZAF"),stringsAsFactors = F)
E=E_daily%>%
  mutate(year=as.numeric(year(Date)))%>%
  group_by(currency,year)%>%
  mutate(rk=n()+1-min_rank(Date))%>%
  filter(rk==n())%>%
  select(year,currency,Rate,name)%>%
  mutate(id=paste(year,currency,sep=""))
Data=Data%>%
  mutate(year=as.numeric(year(Date)))%>%
  select(-Date)%>%
  spread(var,Value)%>%
  left_join(cc,by="country")%>%
  mutate(id=paste(year,currency,sep=""))%>%
  left_join(E,by="id")%>%
  select(-year.y,-currency.y,-id)%>%
  rename(year=year.x)%>%
  rename(currency=currency.x)%>%
  mutate(M2=M2NGDP*NGDP)%>%
  arrange(country,year)%>%
  group_by(country)%>%
  mutate(gE=lead(Rate)/Rate-1)%>%
  mutate(gM2=M2/lag(M2)-1)%>%
  mutate(i_us=ifelse(country=="USA",lendi,NA))%>%
  mutate(ca_us=ifelse(country=="USA",ca,NA))%>%
  mutate(pi_us=ifelse(country=="USA",pi,NA))%>%
  group_by(year)%>%
  mutate(diffi=lendi-max(i_us,na.rm=T))%>%
  mutate(diffca=ca-max(ca_us,na.rm=T))%>%
  mutate(diffpi=pi-max(pi_us,na.rm=T))

4. 用BRICS数据检验汇率决定理论

regPPP=lm(gE~gM2+g-1,data=Data)
summary(regPPP)
regpi=lm(gE~diffpi-1,data=Data)
summary(regpi)
regi=lm(gE~diffi-1,data=Data)
summary(regi)
regca=lm(gE~diffca-1,data=Data)
summary(regca)