Site icon R-bloggers

Foreign Demand for France, Germany, Italy and Spain

[This article was first published on Macroeconomic Observatory - R, and kindly contributed to R-bloggers]. (You can report issue about the content on this page here)
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.

Our purpose is to build the quarterly foreign demand for France, Germany, Italy and Spain.

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

For each country, we proceed in three steps:

France

Main commercial partners imports of goods and services (volume, quarterly, seasonally adjusted)

First of all, we need to compute the variation of the demand originating from each trading partner of France. We select 18 trading partners that channel 75 percent of French exports.

General case

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

partner_country_iso3 <- c('DEU', 'ESP', 'USA', 'ITA', 'GBR', 'BEL', 'NLD', 'CHE', 'POL', 'TUR', 'JPN', 'SWE', 'RUS', 'PRT','CHN', 'SGP', 'HKG', 'DZA')

partner_country_name <- c('Germany', 'Spain', 'United States', 'Italy', 'United Kingdom', 'Belgium', 'Netherlands', 'Switzerland', 'Poland', 'Turkey', 'Japan', 'Sweden', 'Russia', 'Portugal','China','Singapore','Hong Kong, China','Algeria')
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=LOCATION) %>% 
  filter(year(period)>=1979) %>% 
  mutate(country = plyr::mapvalues(country, from = partner_country_iso3, to = partner_country_name))

ggplot(imports ,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, 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)")

Special case: Algeria, China, Hong Kong & Singapore.

Data series of imports of goods and services from Algeria, China, Hong Kong & Singapore are not available in our dataset. We decide to use the WEO database (IMF) to retrieve this data. As it is annual, we use a spline interpolation to obtain a quarterly series.

partner_country_spec_iso3 <- c('CHN', 'SGP', 'HKG', 'DZA')

url_country_spec_iso3 <- paste0(partner_country_spec_iso3,collapse = "+")
filter <- paste0(url_country_spec_iso3,".TM_RPCH")

df <- rdb("IMF","WEO",mask=filter)

imports_spec <-
  df %>% 
  select(period,
         value, country=`weo-country`) %>% 
  na.omit() %>%
  mutate(country=
           case_when(country=="HKG" ~ "Hong Kong, China",
                     country=="CHN" ~ "China",
                     country=="DZA" ~ "Algeria",
                     country=="SGP" ~ "Singapore",
                     TRUE ~ country)) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=100*cumprod(1+value/100)) %>% 
  bind_rows(data.frame(period=as.Date("1997-01-01"),value=100, country="China"), 
            data.frame(period=as.Date("1979-01-01"),value=100, country="Algeria"),
            data.frame(period=as.Date("1979-01-01"),value=100, country="Hong Kong, China"),
            data.frame(period=as.Date("1979-01-01"),value=100, country="Singapore")) %>% 
  arrange(country, period) %>% 
  spread(country,value)

imports_spec_q <- 
  tibble(period=seq(min(imports_spec$period),
                    length.out=nrow(imports_spec)*4,
                    by = "quarter")) %>% 
  left_join(imports_spec,by="period") %>% 
  gather(country,value,-period) %>% 
  filter(!(country== "China" & year(period)<1997)) %>%
  group_by(country) %>%
  mutate(value=na.spline(value))

Growth rates

imports_growth_rate <-
  imports %>%
  bind_rows(imports_spec_q) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=value/lag(value,1)-1) %>% 
  ungroup() %>% 
  filter(year(period)>=1980)

ggplot(filter(imports_growth_rate, year(period)>=1981),aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "fixed") +
  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)")

Mintime <- 
  imports_growth_rate %>%
  group_by(country) %>%
  summarize(minTime = min(period)) %>%
  ungroup()

kable(Mintime, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
country minTime
Algeria 1980-01-01
Belgium 1980-01-01
China 1997-01-01
Germany 1980-01-01
Hong Kong, China 1980-01-01
Italy 1980-01-01
Japan 1980-01-01
Netherlands 1980-01-01
Poland 1995-01-01
Portugal 1980-01-01
Russia 1995-01-01
Singapore 1980-01-01
Spain 1980-01-01
Sweden 1980-01-01
Switzerland 1980-01-01
Turkey 1980-01-01
United Kingdom 1980-01-01
United States 1980-01-01

We have uncomplete series only for China, Poland and Russia.

French 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 France towards each country.

# Importer countries
partner_country <- c('DE', 'ES', 'US', 'IT', 'GB', 'BE', 'CN', 'NL', 'CH', 'PL','TR', 'JP', 'SG', 'HK', 'DZ', 'SE', 'RU', 'PT')
url_partner_country <- paste0(partner_country, collapse = "+")

filter <- paste0('A.FR.TXG_FOB_USD.', url_partner_country)
df <- rdb("IMF","DOT",mask = filter)

bilatx <- 
  df %>% 
  separate(series_name,into = c("tu","ti","to","importer"),sep=" – ") %>% 
  select(importer,
         value,
         period) %>%
  mutate(importer=
           case_when(importer=="Russian Federation" ~ "Russia",
                     TRUE ~ importer)) %>% 
  add_column(exporter="France") %>%
  filter(period >= '1979-01-01')

The following list shows, the date from which we have data on French exports towards each one of the trading partners selected.

start_sample <- 
  bilatx %>%
  group_by(importer) %>%
  summarize(minTime = min(year(period))) %>%
  ungroup() 

kable(start_sample, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
importer minTime
Algeria 1979
Belgium 1999
China 1979
Germany 1979
Hong Kong, China 1979
Italy 1979
Japan 1979
Netherlands 1979
Poland 1979
Portugal 1979
Russia 1992
Singapore 1979
Spain 1979
Sweden 1979
Switzerland 1979
Turkey 1979
United Kingdom 1979
United States 1979

We have uncomplete series only for Belgium and Russia.

Special case of Belgium, China, Poland and Russia

We saw in the previous section that we have uncomplete series of imports of goods and services for China, Poland and Russia, and concerning french exports, we have uncomplete series only for Belgium and Russia. We want to check the growth rates of exports with and without these partners before 1999.

export_all <- 
  bilatx %>%
  group_by(period) %>%
  summarize(value = sum(value)) %>%
  ungroup() %>% 
  mutate(var= "France - all")

export_14 <-
  bilatx %>% 
  filter(!importer %in% c("Belgium","China","Poland","Russia")) %>% 
  group_by(period) %>% 
  summarize(value=sum(value)) %>% 
  ungroup() %>% 
  mutate(var= "France - 14")

plot_export2 <-
  bind_rows(export_all,
            export_14) %>% 
  group_by(var) %>% 
  mutate(value2=value/lag(value)-1) %>% 
  filter(year(period)<=1998)

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 exports, with 14 and 18 partners")

Before 1999, both series are very similar. So we choose to compute weights of 18 commercial partners after 1999 but of only 14 partners before 1999 (without China, Belgium, Poland, and Russia).

Weights of main commercial partners in French exports

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

$$ \alpha_{i,t} = \frac{ X_{i,t} }{ \sum_i X_{i,t} } $$
#Sum of French exports by importer
bilatx %<>%
  group_by(importer,period) %>%
  summarize(value = sum(value)) %>% 
  ungroup()

#Sum of French exports to 14 importers
sumX_importer_all <-
  bilatx %>%
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'France') %>% 
  ungroup()

alphas_importer_all <-
  left_join(sumX_importer_all, bilatx, by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

#Sum of French exports to 14 importers
sumX_importer_14 <-
  bilatx %>%
  filter(! importer %in% c("Belgium", "China","Poland","Russia")) %>% 
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'France') %>% 
  ungroup()

alphas_importer_14 <-
  left_join(sumX_importer_14, 
            filter(bilatx,! importer %in% c("Belgium", "China","Poland","Russia")), 
            by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

alphas <- 
  bind_rows(
    filter(alphas_importer_14,year(period)<1999),
    filter(alphas_importer_all,year(period)>=1999)
  )

ggplot(alphas,aes(period,alpha)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Relative importance of each trading partner in French exports")

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))
alphas %<>% mutate(year=year(period)+1) %>% 
  select(-period)

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))

wd_index2010 <- 
  wd %>%
  mutate(year = year(period)) %>%
  filter(year == "2010") %>%
  group_by(year) %>%
  summarize(value = mean(value)) %>%
  ungroup()

wd_index <-
  wd %>% 
  mutate(period,
         value = 100*value/wd_index2010$value)

wd_index_growth <- 
  wd_index %>% 
  mutate(value=value/lag(value,4)-1,
         var="2- Growth rate")

plot_wd_FR <- 
  bind_rows(wd_index_growth,
            mutate(wd_index,var="1- Level"))%>%
  add_column(country="France")

Germany

Main commercial partners imports of goods and services (volume, quarterly, seasonally adjusted)

First of all, we need to compute the variation of the demand originating from each trading partner of Germany. We select 18 trading partners that channel 75 percent of German 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','FRA','GBR','NLD','CHN','ITA','AUT','POL','CHE','BEL','ESP','CZE','SWE','HUN','TUR','RUS','JPN','DNK')

partner_country_name <- c('United States','France','United Kingdom','Netherlands','China','Italy','Austria','Poland','Switzerland','Belgium','Spain','Czech Republic','Sweden','Hungary','Turkey','Russia','Japan','Denmark')

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=LOCATION) %>% 
  filter(year(period)>=1979) %>% 
  mutate(country = plyr::mapvalues(country, from = partner_country_iso3, to = partner_country_name))

ggplot(imports ,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, 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)")

Special case: China.

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

partner_country_spec_iso3 <- c('CHN')

url_country_spec_iso3 <- paste0(partner_country_spec_iso3,collapse = "+")
filter <- paste0(url_country_spec_iso3,".TM_RPCH")

df <- rdb("IMF","WEO",mask=filter)

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

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

Growth rates

imports_growth_rate <-
  imports %>%
  bind_rows(imports_spec_q) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=value/lag(value,1)-1) %>% 
  ungroup() %>% 
  filter(year(period)>=1980)

ggplot(filter(imports_growth_rate, year(period)>=1981),aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "fixed") +
  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)")

Mintime <- 
  imports_growth_rate %>%
  group_by(country) %>%
  summarize(minTime = min(period)) %>%
  ungroup()

kable(Mintime, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
country minTime
Austria 1980-01-01
Belgium 1980-01-01
China 1997-01-01
Czech Republic 1994-01-01
Denmark 1980-01-01
France 1980-01-01
Hungary 1995-01-01
Italy 1980-01-01
Japan 1980-01-01
Netherlands 1980-01-01
Poland 1995-01-01
Russia 1995-01-01
Spain 1980-01-01
Sweden 1980-01-01
Switzerland 1980-01-01
Turkey 1980-01-01
United Kingdom 1980-01-01
United States 1980-01-01

We have uncomplete series only for China, Czech Republic, Hungary, Poland and Russia.

German 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 Germany towards each country.

# Importer countries
partner_country <- c('US','FR','GB','NL','CN','IT','AT','PL','CH','BE','ES','CZ','SE','HU','TR','RU','JP','DK')
url_partner_country <- paste0(partner_country, collapse = "+")

filter <- paste0('A.DE.TXG_FOB_USD.', url_partner_country)
df <- rdb("IMF","DOT",mask = filter)

bilatx <- 
  df %>% 
  separate(series_name,into = c("tu","ti","to","importer"),sep=" – ") %>% 
  select(importer,
         value,
         period) %>%
  mutate(importer=
           case_when(importer=="Russian Federation" ~ "Russia",
                     TRUE ~ importer)) %>% 
  add_column(exporter="Germany") %>%
  filter(period >= '1979-01-01')

The following list shows, the date from which we have data on German exports towards each one of the trading partners selected.

start_sample <- 
  bilatx %>%
  group_by(importer) %>%
  summarize(minTime = min(year(period))) %>%
  ungroup() 

kable(start_sample, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
importer minTime
Austria 1979
Belgium 1999
China 1979
Czech Republic 1993
Denmark 1979
France 1979
Hungary 1979
Italy 1979
Japan 1979
Netherlands 1979
Poland 1979
Russia 1993
Spain 1979
Sweden 1979
Switzerland 1979
Turkey 1979
United Kingdom 1979
United States 1979

We have uncomplete series only for Belgium, Czech Republic and Russia.

Special case of Belgium, China, Czech Republic, Hungary, Poland and Russia

We saw in the previous section that we have uncomplete series of imports of goods and services for China, Czech Republic, Hungary, Poland and Russia, and concerning german exports, we have uncomplete series only for Belgium, Czech Republic and Russia. We want to check the growth rates of exports with and without these partners before 1999.

export_all <- 
  bilatx %>%
  group_by(period) %>%
  summarize(value = sum(value)) %>%
  ungroup() %>% 
  mutate(var= "Germany - all")

export_12 <-
  bilatx %>% 
  filter(!importer %in% c("Belgium", "China","Poland","Russia")) %>% 
  group_by(period) %>% 
  summarize(value=sum(value)) %>% 
  ungroup() %>% 
  mutate(var= "Germany - 12")

plot_export2 <-
  bind_rows(export_all,
            export_12) %>% 
  group_by(var) %>% 
  mutate(value2=value/lag(value)-1) %>% 
  filter(year(period)<=1998)

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 exports, with 12 and 18 partners")

Before 1999, both series are very similar. So we choose to compute weights of 18 commercial partners after 1999 but of only 12 partners before 1999 (without Belgium, China, Czech Republic, Hungary, Poland and Russia).

Weights of main commercial partners in German exports

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

$$ \alpha_{i,t} = \frac{ X_{i,t} }{ \sum_i X_{i,t} } $$
#Sum of German exports by importer
bilatx %<>%
  group_by(importer,period) %>%
  summarize(value = sum(value)) %>% 
  ungroup()

#Sum of German exports to 12 importers
sumX_importer_all <-
  bilatx %>%
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Germany') %>% 
  ungroup()

alphas_importer_all <-
  left_join(sumX_importer_all, bilatx, by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

#Sum of German exports to 12 importers
sumX_importer_12 <-
  bilatx %>%
  filter(! importer %in% c("Belgium", "China", "Czech Republic", "Hungary", "Poland", "Russia")) %>% 
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Germany') %>% 
  ungroup()

alphas_importer_12 <-
  left_join(sumX_importer_12, 
            filter(bilatx,! importer %in% c("Belgium", "China", "Czech Republic", "Hungary", "Poland", "Russia")), 
            by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

alphas <- 
  bind_rows(
    filter(alphas_importer_12,year(period)<1999),
    filter(alphas_importer_all,year(period)>=1999)
  )

ggplot(alphas,aes(period,alpha)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Relative importance of each trading partner in German exports")

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))
alphas %<>% mutate(year=year(period)+1) %>% 
  select(-period)

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))

wd_index2010 <- 
  wd %>%
  mutate(year = year(period)) %>%
  filter(year == "2010") %>%
  group_by(year) %>%
  summarize(value = mean(value)) %>%
  ungroup()

wd_index <-
  wd %>% 
  mutate(period,
         value = 100*value/wd_index2010$value)

wd_index_growth <- 
  wd_index %>% 
  mutate(value=value/lag(value,4)-1,
         var="2- Growth rate")

plot_wd_DE <- 
  bind_rows(wd_index_growth,
            mutate(wd_index,var="1- Level")) %>%
add_column(country="Germany")

Italy

Main commercial partners imports of goods and services (volume, quarterly, seasonally adjusted)

First of all, we need to compute the variation of the demand originating from each trading partner of Italy. We select 22 trading partners that channel 75 percent of Italian exports.

General case

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

partner_country_iso3 <- c('DEU','FRA','USA','GBR','ESP','CHE','BEL','POL','CHN','NLD','TUR','AUS','RUS','ROU','JPN','HKG','ARE','CZE','HUN','SWE','SAU','KOR')

partner_country_name <- c('Germany','France','United States','United Kingdom','Spain','Switzerland','Belgium','Poland','China','Netherlands','Turkey','Austria','Russia','Romania','Japan','Hong Kong, China','United Arab Emirates','Czech Republic','Hungary','Sweden','Saudi Arabia','South Korea')
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=LOCATION) %>% 
  filter(year(period)>=1979) %>% 
  mutate(country = plyr::mapvalues(country, from = partner_country_iso3, to = partner_country_name))

ggplot(imports ,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, 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)")

Special case: China, Hong Kong, Romania, Saudi Arabia & United Arab Emirates.

Data series of imports of goods and services from China, Hong Kong, Romania, Saudi Arabia & United Arab Emirates are not available in our dataset. We decide to use the WEO database (IMF) to retrieve this data (except for Romania whose series have extreme values). As it is annual, we use a spline interpolation to obtain a quarterly series.

partner_country_spec_iso3 <- c('CHN', 'HKG', 'ARE','SAU')

url_country_spec_iso3 <- paste0(partner_country_spec_iso3,collapse = "+")
filter <- paste0(url_country_spec_iso3,".TM_RPCH")

df <- rdb("IMF","WEO",mask=filter)

imports_spec <-
  df %>% 
  select(period,
         value, country=`weo-country`) %>% 
  na.omit() %>%
  mutate(country=
           case_when(country=="CHN" ~ "China",
                     country=="SAU" ~ "Saudi Arabia",
                     country=="HKG" ~ "Hong Kong, China",
                     #country=="ROU" ~ "Romania",
                     country=="ARE" ~ "United Arab Emirates",
                     TRUE ~ country)) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=100*cumprod(1+value/100)) %>% 
  bind_rows(data.frame(period=as.Date("1997-01-01"),value=100, country="China"), 
            data.frame(period=as.Date("1979-01-01"),value=100, country="Saudi Arabia"),
            data.frame(period=as.Date("1979-01-01"),value=100, country="Hong Kong, China"),
            #data.frame(period=as.Date("1979-01-01"),value=100, country="Romania"),
            data.frame(period=as.Date("1979-01-01"),value=100, country="United Arab Emirates")) %>% 
  arrange(country, period) %>% 
  spread(country,value)

imports_spec_q <- 
  tibble(period=seq(min(imports_spec$period),
                    length.out=nrow(imports_spec)*4,
                    by = "quarter")) %>% 
  left_join(imports_spec,by="period") %>% 
  gather(country,value,-period) %>% 
  filter(!(country== "China" & year(period)<1997)) %>%
  group_by(country) %>%
  mutate(value=na.spline(value))

Growth rates

imports_growth_rate <-
  imports %>%
  bind_rows(imports_spec_q) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=value/lag(value,1)-1) %>% 
  ungroup() %>% 
  filter(year(period)>=1980)

ggplot(filter(imports_growth_rate, year(period)>=1981),aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "fixed") +
  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)")

Mintime <- 
  imports_growth_rate %>%
  group_by(country) %>%
  summarize(minTime = min(period)) %>%
  ungroup()

kable(Mintime, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
country minTime
Austria 1980-01-01
Belgium 1980-01-01
China 1997-01-01
Czech Republic 1994-01-01
France 1980-01-01
Germany 1980-01-01
Hong Kong, China 1980-01-01
Hungary 1995-01-01
Japan 1980-01-01
Netherlands 1980-01-01
Poland 1995-01-01
Romania 1995-01-01
Russia 1995-01-01
Saudi Arabia 1980-01-01
South Korea 1980-01-01
Spain 1980-01-01
Sweden 1980-01-01
Switzerland 1980-01-01
Turkey 1980-01-01
United Arab Emirates 1980-01-01
United Kingdom 1980-01-01
United States 1980-01-01

We have uncomplete series only for China, Czech Republic, Hungary, Poland, Romania and Russia.

Italian 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 Italy towards each country.

# Importer countries
partner_country <- c('DE','FR','US','GB','ES','CH','BE','PL','CN','NL','TR','AT','RU','RO','JP','HK','AE','CZ','HU','SE','SA','KR')

url_partner_country <- paste0(partner_country, collapse = "+")

filter <- paste0('A.IT.TXG_FOB_USD.', url_partner_country)
df <- rdb("IMF","DOT",mask = filter)

bilatx <- 
  df %>% 
  separate(series_name,into = c("tu","ti","to","importer"),sep=" – ") %>% 
  select(importer,
         value,
         period) %>%
  mutate(importer=
           case_when(importer=="Russian Federation" ~ "Russia",
                     importer=="Korea, Republic of" ~ "South Korea",
                     TRUE ~ importer)) %>% 
  add_column(exporter="Italy") %>%
  filter(period >= '1979-01-01')

The following list shows, the date from which we have data on Italian exports towards each one of the trading partners selected.

start_sample <- 
  bilatx %>%
  group_by(importer) %>%
  summarize(minTime = min(year(period))) %>%
  ungroup() 

kable(start_sample, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
importer minTime
Austria 1979
Belgium 1999
China 1979
Czech Republic 1993
France 1979
Germany 1979
Hong Kong, China 1979
Hungary 1979
Japan 1979
Netherlands 1979
Poland 1979
Romania 1979
Russia 1993
Saudi Arabia 1979
South Korea 1979
Spain 1979
Sweden 1979
Switzerland 1979
Turkey 1979
United Arab Emirates 1979
United Kingdom 1979
United States 1979

We have uncomplete series only for Belgium, Czech Republic, and Russia.

Special case of Belgium, China, Czech Republic, Hungary, Poland, Romania and Russia

We saw in the previous section that we have uncomplete series of imports of goods and services for China, Czech Republic, Hungary, Poland, Romania and Russia, and concerning italian exports, we have uncomplete series only for Belgium, Czech Republic, and Russia. We want to check the growth rates of exports with and without these partners before 1999.

export_all <- 
  bilatx %>%
  group_by(period) %>%
  summarize(value = sum(value)) %>%
  ungroup() %>% 
  mutate(var= "Italy - all")

export_15 <-
  bilatx %>% 
  filter(!importer %in% c("Belgium", "China", "Czech Republic", "Hungary", "Poland", "Romania","Russia")) %>% 
  group_by(period) %>% 
  summarize(value=sum(value)) %>% 
  ungroup() %>% 
  mutate(var= "Italy - 15")

plot_export2 <-
  bind_rows(export_all,
            export_15) %>% 
  group_by(var) %>% 
  mutate(value2=value/lag(value)-1) %>% 
  filter(year(period)<=1998)

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 exports, with 15 and 22 partners")

Before 1999, both series are very similar. So we choose to compute weights of 22 commercial partners after 1999 but of only 15 partners before 1999 (without Belgium, China, Czech Republic, Hungary, Poland, Romania and Russia).

Weights of main commercial partners in Italian exports

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

$$ \alpha_{i,t} = \frac{ X_{i,t} }{ \sum_i X_{i,t} } $$
#Sum of Italian exports by importer
bilatx %<>%
  group_by(importer,period) %>%
  summarize(value = sum(value)) %>% 
  ungroup()

#Sum of Italian exports to 16 importers
sumX_importer_all <-
  bilatx %>%
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Italy') %>% 
  ungroup()

alphas_importer_all <-
  left_join(sumX_importer_all, bilatx, by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

#Sum of Italian exports to 16 importers
sumX_importer_15 <-
  bilatx %>%
  filter(! importer %in% c("Belgium", "China", "Czech Republic", "Hungary", "Poland", "Romania", "Russia")) %>% 
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Italy') %>% 
  ungroup()

alphas_importer_15 <-
  left_join(sumX_importer_15, 
            filter(bilatx,! importer %in% c("Belgium", "China", "Czech Republic", "Hungary", "Poland", "Romania","Russia")), 
            by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

alphas <- 
  bind_rows(
    filter(alphas_importer_15,year(period)<1999),
    filter(alphas_importer_all,year(period)>=1999)
  )

ggplot(alphas,aes(period,alpha)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Relative importance of each trading partner in Italian exports")

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))
alphas %<>% mutate(year=year(period)+1) %>% 
  select(-period)

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))

wd_index2010 <- 
  wd %>%
  mutate(year = year(period)) %>%
  filter(year == "2010") %>%
  group_by(year) %>%
  summarize(value = mean(value)) %>%
  ungroup()

wd_index <-
  wd %>% 
  mutate(period,
         value = 100*value/wd_index2010$value)

wd_index_growth <- 
  wd_index %>% 
  mutate(value=value/lag(value,4)-1,
         var="2- Growth rate")

plot_wd_IT <- 
  bind_rows(wd_index_growth,
            mutate(wd_index,var="1- Level")) %>%
add_column(country="Italy") 

Spain

Imports of goods and services of Spain 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 Spain. We select 18 trading partners that channel 75 percent of Spanish exports.

General case

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

partner_country_iso3 <- c('FRA','DEU','ITA','GBR','PRT','USA','NLD','BEL','MAR','TUR','CHN','POL','MEX','CHE','DZA','JPN','SAU','BRA')

partner_country_name <- c('France','Germany','Italy','United Kingdom','Portugal','United States','Netherlands','Belgium','Morocco','Turkey','China','Poland','Mexico','Switzerland','Algeria','Japan','Saudi Arabia','Brazil')

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=LOCATION) %>% 
  filter(year(period)>=1979) %>% 
  mutate(country = plyr::mapvalues(country, from = partner_country_iso3, to = partner_country_name))

ggplot(imports ,aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, 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)")

Special case: Algeria, China, Morocco & Saudi Arabia.

Data series of imports of goods and services from Algeria, China, Morocco & Saudi Arabia are not available in our dataset. We decide to use the WEO database (IMF) to retrieve this data. As it is annual, we use a spline interpolation to obtain quarterly series.

partner_country_spec_iso3 <- c('DZA', 'CHN', 'MAR', 'SAU')

url_country_spec_iso3 <- paste0(partner_country_spec_iso3,collapse = "+")
filter <- paste0(url_country_spec_iso3,".TM_RPCH")

df <- rdb("IMF","WEO",mask=filter)

imports_spec <-
  df %>% 
  select(period,
         value, country=`weo-country`) %>% 
  na.omit() %>%
  mutate(country=
           case_when(country=="SAU" ~ "Saudi Arabia",
                     country=="CHN" ~ "China",
                     country=="DZA" ~ "Algeria",
                     country=="MAR" ~ "Morocco",
                     TRUE ~ country)) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=100*cumprod(1+value/100)) %>% 
  bind_rows(data.frame(period=as.Date("1997-01-01"),value=100, country="China"), 
            data.frame(period=as.Date("1979-01-01"),value=100, country="Algeria"),
            data.frame(period=as.Date("1979-01-01"),value=100, country="Saudi Arabia"),
            data.frame(period=as.Date("1979-01-01"),value=100, country="Morocco")) %>% 
  arrange(country, period) %>% 
  spread(country,value)

imports_spec_q <- 
  tibble(period=seq(min(imports_spec$period),
                    length.out=nrow(imports_spec)*4,
                    by = "quarter")) %>% 
  left_join(imports_spec,by="period") %>% 
  gather(country,value,-period) %>% 
  filter(!(country== "China" & year(period)<1997)) %>%
  group_by(country) %>%
  mutate(value=na.spline(value))

Growth rates

imports_growth_rate <-
  imports %>%
  bind_rows(imports_spec_q) %>% 
  arrange(country,period) %>% 
  group_by(country) %>% 
  mutate(value=value/lag(value,1)-1) %>% 
  ungroup() %>% 
  filter(year(period)>=1980)

ggplot(filter(imports_growth_rate, year(period)>=1981),aes(period,value)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "fixed") +
  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)")

Mintime <- 
  imports_growth_rate %>%
  group_by(country) %>%
  summarize(minTime = min(period)) %>%
  ungroup()

kable(Mintime, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
country minTime
Algeria 1980-01-01
Belgium 1980-01-01
Brazil 1996-01-01
China 1997-01-01
France 1980-01-01
Germany 1980-01-01
Italy 1980-01-01
Japan 1980-01-01
Mexico 1980-01-01
Morocco 1980-01-01
Netherlands 1980-01-01
Poland 1995-01-01
Portugal 1980-01-01
Saudi Arabia 1980-01-01
Switzerland 1980-01-01
Turkey 1980-01-01
United Kingdom 1980-01-01
United States 1980-01-01

We have uncomplete series only for Brazil, China, and Poland.

Spanish 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 Spain towards each country.

# Importer countries
partner_country <- c('FR','DE','IT','GB','PT','US','NL','BE','MA','TR','CN','PL','MX','CH','DZ','JP','SA','BR')

url_partner_country <- paste0(partner_country, collapse = "+")

filter <- paste0('A.ES.TXG_FOB_USD.', url_partner_country)
df <- rdb("IMF","DOT",mask = filter)

bilatx <- 
  df %>% 
  separate(series_name,into = c("tu","ti","to","importer"),sep=" – ") %>% 
  select(importer,
         value,
         period) %>%
  add_column(exporter="Spain") %>%
  filter(period >= '1979-01-01')

The following list shows, the date from which we have data on spanish exports towards each one of the trading partners selected.

start_sample <- 
  bilatx %>%
  group_by(importer) %>%
  summarize(minTime = min(year(period))) %>%
  ungroup() 

kable(start_sample, "html", caption = "minTime")%>%
  kable_styling(bootstrap_options = c("striped", "hover", "condensed"), position = "center")
minTime
importer minTime
Algeria 1979
Belgium 1997
Brazil 1979
China 1979
France 1979
Germany 1979
Italy 1979
Japan 1979
Mexico 1979
Morocco 1979
Netherlands 1979
Poland 1979
Portugal 1979
Saudi Arabia 1979
Switzerland 1979
Turkey 1979
United Kingdom 1979
United States 1979

We have uncomplete series only for Belgium.

Special case of Belgium, Brazil, China and Poland

We saw in the previous section that we have uncomplete series of imports of goods and services for Brazil, China, and Poland, and concerning spanish exports, we have uncomplete series only for Belgium. We want to check the growth rates of exports with and without these partners before 1997.

export_all <- 
  bilatx %>%
  group_by(period) %>%
  summarize(value = sum(value)) %>%
  ungroup() %>% 
  mutate(var= "Spain - all")

export_14 <-
  bilatx %>% 
  filter(!importer %in% c("Belgium", "Brazil", "China", "Poland")) %>% 
  group_by(period) %>% 
  summarize(value=sum(value)) %>% 
  ungroup() %>% 
  mutate(var= "Spain - 14")

plot_export2 <-
  bind_rows(export_all,
            export_14) %>% 
  group_by(var) %>% 
  mutate(value2=value/lag(value)-1) %>% 
  filter(year(period)<=1996)

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 exports, with 14 and 18 partners")

Before 1999, both series are very similar. So we choose to compute weights of 18 commercial partners after 1997 but of only 14 partners before 1997 (without Belgium, Brazil, China and Poland).

Weights of main commercial partners in spanish exports

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

$$ \alpha_{i,t} = \frac{ X_{i,t} }{ \sum_i X_{i,t} } $$
#Sum of Spanish exports by importer
bilatx %<>%
  group_by(importer,period) %>%
  summarize(value = sum(value)) %>% 
  ungroup()

#Sum of Spanish exports to 14 importers
sumX_importer_all <-
  bilatx %>%
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Spain') %>% 
  ungroup()

alphas_importer_all <-
  left_join(sumX_importer_all, bilatx, by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

#Sum of French exports to 14 importers
sumX_importer_14 <-
  bilatx %>%
  filter(! importer %in% c("Belgium", "Brazil", "China", "Poland")) %>% 
  group_by(period) %>%
  summarise(xsum = sum(value)) %>%
  mutate(exporter = 'Spain') %>% 
  ungroup()

alphas_importer_14 <-
  left_join(sumX_importer_14, 
            filter(bilatx,! importer %in% c("Belgium", "Brazil", "China", "Poland")), 
            by = 'period') %>%
  mutate(alpha = value/xsum) %>% 
  select(period,country=importer,alpha)

alphas <- 
  bind_rows(
    filter(alphas_importer_14,year(period)<1997),
    filter(alphas_importer_all,year(period)>=1997)
  )

ggplot(alphas,aes(period,alpha)) +
  geom_line(colour = blueObsMacro) +
  facet_wrap(~country, ncol = 5, scales = "free_y") +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle("Relative importance of each trading partner in Spanish exports")

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))
alphas %<>% mutate(year=year(period)+1) %>% 
  select(-period)

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))

wd_index2010 <- 
  wd %>%
  mutate(year = year(period)) %>%
  filter(year == "2010") %>%
  group_by(year) %>%
  summarize(value = mean(value)) %>%
  ungroup()

wd_index <-
  wd %>% 
  mutate(period,
         value = 100*value/wd_index2010$value)

wd_index_growth <- 
  wd_index %>% 
  mutate(value=value/lag(value,4)-1,
         var="2- Growth rate")

plot_wd_ES <- 
  bind_rows(wd_index_growth,
            mutate(wd_index,var="1- Level")) %>%
  add_column(country="Spain")

Foreign Demand

foreign_demand<- 
  bind_rows(plot_wd_FR,
            plot_wd_DE,
            plot_wd_IT,
            plot_wd_ES)

ggplot(foreign_demand,aes(period,value,colour=country)) +
  geom_line() +
  facet_wrap(~var, scales = "free_y",ncol=1) +
  scale_x_date(expand = c(0.01,0.01)) +
  theme + xlab(NULL) + ylab(NULL) +
  ggtitle(expression(atop("Foreign demand for France, Germany, Italy and Spain", atop(italic("base 100 = 2010"),""))))

list_country <- list("FR"="France",
                     "DE"="Germany",
                     "IT"="Italy",
                     "ES"= "Spain")

foreign_demand$country <- factor(foreign_demand$country)                  
levels(foreign_demand$country)<-list_country

world_demand <- 
  foreign_demand %>%
  filter(var=="1- Level") %>%
  select(period,value,country) %>%
  add_column(var="world_demand")

write.csv(world_demand,"Foreign_demand.csv",row.names = FALSE)

You can download data here.


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/. ↩

To leave a comment for the author, please follow the link and comment on their blog: Macroeconomic Observatory - R.

R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.