Automating update of an international database for the Euro Area

Our purpose is to create an international quarterly database for the Euro area that could be updated automatically. We want to build the following series:

  • Foreign demand (without trade between Euro area countries)
  • Foreign interest rate
  • Oil prices
  • Real effective exchange rate
  • Import and export

To construct these series we use data from DBnomics. The DBnomics API is called using the rdbnomics package. All the code is written in R, thanks to the RCoreTeam (2016) and RStudioTeam (2016).

Foreign demand

We want to build a series that describes the evolution of the foreign demand for the Eurozone, without trade between Euro area countries. We proceed in three steps:

  • we calculate the growth of imports in volume of main trading partners;
  • we calculate the relative importance of each trading partner in Eurozone exports;
  • we sum over the growth rates of imports weighted by the relative importance of each trading partner.

Imports of goods and services of Eurozone main commercial partners (volume, quarterly, seasonally adjusted)

First of all, we need to compute the variation of the demand originating from each trading partner of the Euro area. We select 14 trading partners that channel most of Eurozone’s exports.

General case

Data comes from the OECD Economic Outlook database: we use imports of goods and services in volume.

partner_country_iso3 <- c('USA','GBR','DNK','NOR','SWE','CAN','CHE','JPN','AUS','BRA','IND','IDN','KOR','CHN')
partner_country_name <- c('United-States','United-Kingdom','Denmark','Norway','Sweden','Canada','Switzerland','Japan','Australia','Brazil','India','Indonesia','South Korea','China')
url_country_iso3 <- paste0(partner_country_iso3,collapse = "+")
filter <- paste0(url_country_iso3,".P7.VOBARSA.Q")
df <- rdb("OECD","QNA",mask=filter)

imports <- 
  df %>% 
  select(period,value,country=Country) %>% 
  filter(year(period)>=1979) %>% 
  mutate(country = plyr::mapvalues(country, from = partner_country_iso3, to = partner_country_name))
## Error: Problem with `mutate()` input `country`.
## ✖ there is no package called 'plyr'
## ℹ Input `country` is `plyr::mapvalues(country, from = partner_country_iso3, to = partner_country_name)`.
ggplot(imports ,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 3, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Imports of goods and services",subtitle="(volume, seasonally adjusted, national currency)")
## Error in ggplot(imports, aes(period, value)): object 'imports' not found

China special case

Data series of imports of goods and services from China are not available in our dataset. We decide to take Chinese imports of goods and services from the WEO database (IMF). As it is annual, we use a spline interpolation to obtain a quarterly series.

df <- rdb(ids="IMF/WEO:latest/CHN.TM_RPCH.pcent_change")

imports_cn <-
  df %>% 
  select(period,
         value) %>% 
  na.omit() %>% 
  arrange(period) %>% 
  mutate(value=100*cumprod(1+value/100)) %>% 
  bind_rows(data.frame(period=as.Date("1997-01-01"),
                       value=100)) %>% 
  arrange(period)

imports_cn_q <- 
  tibble(period=seq(min(imports_cn$period),
                    length.out=nrow(imports_cn)*4,
                    by = "quarter")) %>% 
  left_join(imports_cn,by="period") %>% 
  mutate(value=na.spline(value),
         country="China")

Growth rates

imports_growth_rate <-
  imports %>%
  filter(country != "China") %>% 
  bind_rows(imports_cn_q) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=value/lag(value,1)-1) %>% 
  ungroup() %>% 
  filter(year(period)>=1980)
## Error in filter(., country != "China"): object 'imports' not found
ggplot(imports_growth_rate,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 3, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Growth rates of imports of goods and services", subtitle="(% quarter-on-quarter, volume, seasonally adjusted)")
## Error in ggplot(imports_growth_rate, aes(period, value)): object 'imports_growth_rate' not found
Mintime <- 
  imports_growth_rate %>%
  group_by(country) %>%
  summarize(MinTime = min(period)) %>%
  ungroup()
## Error in group_by(., country): object 'imports_growth_rate' not found
kable(Mintime)
## Error in kable(Mintime): object 'Mintime' not found

We have uncomplete series only for Brazil, China, India and Indonesia.

Eurozone’s exports of goods to main commercial partners (values US dollars, annual)

To compute the relative importance of each trading partner, we use data series of values of exports of goods (Free on board, in US dollars), from DOT database (IMF), for each Eurozone country towards extra-area countries.

# Exporter countries of the Eurozone
ea_country <- c("AT","BE","R1","FR","DE","IT","LU","NL","FI","GR","IE","MT","PT","ES","CY","SK","EE","LV","LT","SI")
ea_country_name <- c('Austria','Belgium','Luxembourg-Belgium','France','Germany','Italy','Luxembourg','Netherlands','Finland','Greece','Ireland','Malta','Portugal','Spain','Cyprus','Slovak Republic','Estonia','Latvia','Lithuania','Slovenia')
url_ea_country <- paste0(ea_country, collapse = "+")

# Importer countries outside the Eurozone
partner_country <- c("US","GB","DK","NO","SE","CA","CH","JP","AU","BR","IN","ID","KR","CN")
url_partner_country <- paste0(partner_country, collapse = "+")
filter <- paste0('A.',url_ea_country,'.TXG_FOB_USD.', url_partner_country)
df <- rdb("IMF","DOT",mask = filter)

bilatx <- 
  df %>% 
  select(exporter = REF_AREA,
         importer = COUNTERPART_AREA,
         value,
         period) %>%
  mutate(exporter = plyr::mapvalues(exporter, from = ea_country, to = ea_country_name),
         importer = plyr::mapvalues(importer, from = partner_country, to = partner_country_name)) %>%
  filter(period >= '1979-01-01')
## Error: Problem with `mutate()` input `exporter`.
## ✖ there is no package called 'plyr'
## ℹ Input `exporter` is `plyr::mapvalues(exporter, from = ea_country, to = ea_country_name)`.

The following list shows, for each Eurozone country, the date from which we have data on exports towards each one of the 14 trading partners selected. We show the begining of the sample for each country.

start_sample <- 
  bilatx %>%
  group_by(exporter, importer) %>%
  summarize(MinTime = min(year(period))) %>%
  ungroup() %>%
  spread(importer,MinTime)
## Error in group_by(., exporter, importer): object 'bilatx' not found
start_sample[,1:8] %>%   
  kable()
## Error in kable(.): object 'start_sample' not found
start_sample[,c(1,9:15)] %>%   
  kable()
## Error in kable(.): object 'start_sample' not found

Special case of Belgium-Luxembourg

We have data for Belgium-Luxembourg as a single exporter until 1997. So we compute extra-area trade of Belgium and Luxembourg since 1997 to create a series for the whole period.

bilatx.Belux <- 
  filter(bilatx, exporter %in% c('Belgium','Luxembourg'))  %>%
  group_by(importer, period) %>%
  summarize(value = sum(value)) %>%
  ungroup() %>% 
  mutate(exporter = "Luxembourg-Belgium")
## Error in filter(bilatx, exporter %in% c("Belgium", "Luxembourg")): object 'bilatx' not found
bilatx %<>% 
  filter(!exporter %in% c('Belgium','Luxembourg')) %>%
  rbind(bilatx.Belux)
## Error in filter(., !exporter %in% c("Belgium", "Luxembourg")): object 'bilatx' not found

Special case of Eastern European countries

Before 1992, five countries lack some data: the Baltic states, Slovenia and Slovak Republic. On the following graph, we represent the sum of exports of the Eurozone with and without these five countries.

export_15 <- 
  bilatx %>%
  filter(!exporter %in% c("Slovenia","Slovak Republic","Latvia","Estonia","Lithuania")) %>% 
  mutate(var = 'Eurozone - 15') %>%
  group_by(var, period) %>%
  summarize(value = sum(value)) %>%
  ungroup()
## Error in filter(., !exporter %in% c("Slovenia", "Slovak Republic", "Latvia", : object 'bilatx' not found
export_all <- 
  bilatx %>%
  mutate(var = 'Eurozone - all') %>%
  group_by(var,period) %>%
  summarize(value = sum(value)) %>%
  ungroup()
## Error in mutate(., var = "Eurozone - all"): object 'bilatx' not found
plot_export <- 
  rbind(export_15, export_all)
## Error in rbind(export_15, export_all): object 'export_15' not found
ggplot(plot_export,aes(period,value, colour = var)) +
  geom_line() +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  theme(legend.title=element_blank()) +
  ggtitle("Extra-Eurozone exports, with / without Eastern countries")
## Error in ggplot(plot_export, aes(period, value, colour = var)): object 'plot_export' not found

Before 2003, both series are very similar. So we choose to keep the whole dataset as it is.

Special case of Brazil, China, India and Indonesia

We saw in the previous section that we have uncomplete series of imports of goods and services for Brazil, China, India and Indonesia, with a lack of data before 1997. As these specific countries developed their imports mainly after 1997, we want to check the growth rates of extra-area exports with and without these partners before 1997.

import_10 <-
  bilatx %>% 
  filter(!importer %in% c("Brazil","China","India","Indonesia")) %>% 
  group_by(period) %>% 
  summarize(value=sum(value)) %>% 
  ungroup() %>% 
  mutate(var= "Importers - 10")
## Error in filter(., !importer %in% c("Brazil", "China", "India", "Indonesia")): object 'bilatx' not found
plot_export2 <-
  bind_rows(mutate(export_all,var="Importers - all"),
            import_10) %>% 
  group_by(var) %>% 
  mutate(value2=value/lag(value)-1) %>% 
  filter(year(period)<=1997)
## Error in mutate(export_all, var = "Importers - all"): object 'export_all' not found
ggplot(plot_export2,aes(period,value2, colour = var)) +
  geom_line() +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  theme(legend.title=element_blank()) +
  ggtitle("Growth rate of extra-area exports, with 10 and 14 partners")
## Error in ggplot(plot_export2, aes(period, value2, colour = var)): object 'plot_export2' not found

Before 1997, both series are very similar. So we choose to compute weights of 14 commercial partners after 1997 but of only 10 partner before 1997 (without Brazil, China, India and Indonesia).

Weights of main commercial partners in Eurozone’s exports

For each commercial partner \(i\), we compute \(\alpha_i\), the share of EA exports \(X\) among all EA exports towards these partners, at time \(t\) :

$$ \alpha_{i,t} = \frac{ X_{i,t} }{ \sum_i X_{i,t} } $$
#Sum of exports of Euro area by importer
bilatx %<>%
  group_by(importer,period) %>%
  summarize(value = sum(value)) %>% 
  ungroup()
## Error in group_by(., importer, period): object 'bilatx' not found
#Sum of exports of Euro area to 14 importers
sumX_EA_importer_all <-
  bilatx %>%
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Eurozone') %>% 
  ungroup()
## Error in group_by(., period): object 'bilatx' not found
alphas_importer_all <-
  left_join(sumX_EA_importer_all, bilatx, by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)
## Error in left_join(sumX_EA_importer_all, bilatx, by = "period"): object 'sumX_EA_importer_all' not found
#Sum of exports of Euro area to 14 importers
sumX_EA_importer_10 <-
  bilatx %>%
  filter(! importer %in% c("Brazil","China","India","Indonesia")) %>% 
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Eurozone') %>% 
  ungroup()
## Error in filter(., !importer %in% c("Brazil", "China", "India", "Indonesia")): object 'bilatx' not found
alphas_importer_10 <-
  left_join(sumX_EA_importer_10, 
            filter(bilatx,! importer %in% c("Brazil","China","India","Indonesia")), 
            by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)
## Error in left_join(sumX_EA_importer_10, filter(bilatx, !importer %in% : object 'sumX_EA_importer_10' not found
alphas <- 
  bind_rows(
    filter(alphas_importer_10,year(period)<=1997),
    filter(alphas_importer_all,year(period)>1997)
  )
## Error in filter(alphas_importer_10, year(period) <= 1997): object 'alphas_importer_10' not found
ggplot(alphas,aes(period,alpha)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 3, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Share of Eurozone exports among all Eurozone exports")
## Error in ggplot(alphas, aes(period, alpha)): object 'alphas' not found

Final index

We sum over the growth rates of imports in volume weighted by the relative importance of each trading partner during the previous year. Then we create a global index.

imports_growth_rate %<>% mutate(year=year(period))
## Error in mutate(., year = year(period)): object 'imports_growth_rate' not found
alphas %<>% mutate(year=year(period)+1) %>% 
  select(-period)
## Error in mutate(., year = year(period) + 1): object 'alphas' not found
wd <-
  right_join(alphas, imports_growth_rate, by = c("year", "country")) %>%
  mutate(value = alpha * value) %>% 
  na.omit() %>% 
  select(period,value,country) %>% 
  group_by(period) %>% 
  summarise(value = sum(value)) %>%
  mutate(value = cumprod(1+value))
## Error in right_join(alphas, imports_growth_rate, by = c("year", "country")): object 'alphas' not found
wd_index2010 <- 
  wd %>%
  mutate(year = year(period)) %>%
  filter(year == "2010") %>%
  group_by(year) %>%
  summarize(value = mean(value)) %>%
  ungroup()
## Error in UseMethod("mutate_"): no applicable method for 'mutate_' applied to an object of class "function"
wd_index <-
  wd %>% 
  mutate(period,
         value = 100*value/wd_index2010$value)
## Error in UseMethod("mutate_"): no applicable method for 'mutate_' applied to an object of class "function"
wd_index_growth <- 
  wd_index %>% 
  mutate(value=value/lag(value,4)-1,
         var="2- Growth rate")
## Error in mutate(., value = value/lag(value, 4) - 1, var = "2- Growth rate"): object 'wd_index' not found
plot_wd <- 
  bind_rows(wd_index_growth,
            mutate(wd_index,var="1- Level"))
## Error in list2(...): object 'wd_index_growth' not found
ggplot(plot_wd,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~var, scales = "free_y",ncol=1) +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Foreign demand for the Eurozone, base 100 = 2010")
## Error in ggplot(plot_wd, aes(period, value)): object 'plot_wd' not found

Foreign interest rate

We use the US federal funds rate overnight as a proxy for the foreign interest rate.

df <- rdb("FED","H15",mask="129.FF.O")

shortrate <- 
  df %>% 
  mutate(period=paste(year(period),quarter(period),sep="-")) %>% 
  group_by(period) %>% 
  summarise(value=mean(value)) %>% 
  ungroup() %>% 
  mutate(period=yq(period)) %>%
  filter(period >= "1980-01-01")

ggplot(shortrate,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle('Foreign interest rate')

plot of chunk unnamed-chunk-14

Oil prices

We need to download a series that reflects oil prices to build a foreign block. We take the series from the OECD Economic Outlook database.

df <- rdb(ids = "OECD/EO/OTO.WPBRENT.Q")

oil_prices <- 
  df %>%
  select(period, value) %>%
  filter(period >= "1980-01-01")

ggplot(oil_prices,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle('Crude oil prices')

plot of chunk unnamed-chunk-15

Real effective exchange rate

df <- rdb(ids = "BIS/eer/M.R.N.XM")

reer <- 
  df %>%
  mutate(period=paste(year(period),quarter(period),sep="-")) %>%
  group_by(period) %>%
  summarize(value=mean(value)) %>%
  ungroup() %>%
  mutate(period=yq(period)) %>%
  filter(period >= "1980-01-01")

ggplot(reer,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle('Real Effective Exchange Rate')

plot of chunk unnamed-chunk-16

Extra Euro area imports and exports

df <- rdb("ECB","TRD",mask = 'M.I8.Y.M+X.TTT.J8.4.VOX')

trade <- 
  df %>%
  transmute(period=paste(year(period),quarter(period),sep="-"),
            value,
            var=ifelse(grepl("Import",series_name),"imports","exports")) %>%
  group_by(var,period) %>%
  summarize(value = mean(value)) %>%
  ungroup() %>% 
  mutate(period=yq(period))

ggplot(trade,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~var)+
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle('Extra euro area imports / exports, in volume, seasonally adjusted')

plot of chunk unnamed-chunk-17

Final international database for the Euro area

We eventually build an international database for the Euro area.

rawdata <- 
  bind_rows(
    mutate(wd_index, var = 'world_demand'),
    mutate(shortrate, var = 'foreign_rate'),
    mutate(oil_prices, var = 'oil_prices'),
    mutate(reer, var = "reer"),
    trade)
## Error in mutate(wd_index, var = "world_demand"): object 'wd_index' not found

We can check the last date available for each variable.

maxDate <- 
  rawdata %>% 
  group_by(var) %>% 
  summarize(maxdate=max(period)) %>% 
  arrange(maxdate)
## Error in group_by(., var): object 'rawdata' not found
kable(maxDate)
var maxdate
conso 2020-04-01
defconso 2020-04-01
defgdp 2020-04-01
definves 2020-04-01
hours 2020-04-01
inves 2020-04-01
pop 2020-04-01
wage 2020-04-01
employ 2020-07-01
gdp 2020-07-01
shortrate 2020-07-01
minmaxDate <- min(maxDate$maxdate)

EA_Open_rawdata <- 
  rawdata %>% 
  filter(period <= minmaxDate) %>% 
  spread(var,value)
## Error in filter(., period <= minmaxDate): object 'rawdata' not found
EA_Open_rawdata %>% 
  write.csv("EA_Open_rawdata.csv", row.names=FALSE)
## Error in is.data.frame(x): object 'EA_Open_rawdata' not found

So we filter the database until 2020 Q2. You can download all the raw series here.

sw03 <- 
  read.csv("http://shiny.cepremap.fr/data/EA_SW_rawdata.csv") %>%
  mutate(period=ymd(period))

EA_Open_data <- 
  EA_Open_rawdata %>% 
  inner_join(sw03,by="period") %>% 
  transmute(period,
            world_demand,
            foreign_rate,
            oil_prices,
            reer,
            imports,
            exports)
## Error in inner_join(., sw03, by = "period"): object 'EA_Open_rawdata' not found
EA_Open_data %>%
  mutate(period=gsub(" ","",as.yearqtr(period))) %>%
  write.csv(file = "EA_Open_data.csv",row.names = FALSE)
## Error in mutate(., period = gsub(" ", "", as.yearqtr(period))): object 'EA_Open_data' not found

You can download ready-to-use data for the estimation here.

Appendix

Chain

This function chain two series, using the growth rate of the historical one to deduce new points on the original series. It allows to go further back in time with one series while keeping the most recent points.

chain <- function(to_rebase, basis, date_chain) {

  date_chain <- as.Date(date_chain, "%Y-%m-%d")

  valref <- basis %>%
    filter(period == date_chain) %>%
    transmute(country, value_ref = value) 

  res <- to_rebase %>%
    filter(period <= date_chain) %>%
    arrange(desc(period)) %>%
    group_by(country) %>%
    mutate(growth_rate = c(1, value[-1]/lag(value)[-1])) %>%
    full_join(valref, by = "country") %>%
    group_by(country) %>%
    transmute(period, value = cumprod(growth_rate)*value_ref)%>%
    ungroup() %>%  
    bind_rows(filter(basis, period > date_chain)) %>% 
    arrange(period)

  return(res)

}

Bibliography

R Core Team. R: A Language and Environment for Statistical Computing. R Foundation for Statistical Computing, Vienna, Austria, 2016. URL: https://www.R-project.org.

RStudio Team. RStudio: Integrated Development Environment for R. RStudio, Inc., Boston, MA, 2016. URL: http://www.rstudio.com/.

Comments

comments powered by Disqus