Skip to content

Introduction

Recently, I studied Switzerland's international trade based on data from the UN Commodities Trade Database (https://comtradeplus.un.org/), which gathers information about countries, trade flows, modes of transport and types of commodities during a certain period of time. At some point, I was curious about the fact that there are not many countries with which Switzerland have a substantial deficit in trade. By substantial, I mean more or equal to 20% of the overall trade per year. The 20% threshold was adopted based on the lower limit of the new customs tariffs introduced in 2025 by the USA.

Besides Germany (with which Swiss have average deficit of 10% and 12.5 bln USD), most of the "deficit-makers" do not make more than 4.1 bln USD of deficit per year, which is around 1% of the total imported goods to Switzerland. What is more interesting is that very quickly a substantial deficit becomes almost entirely the value of imports from several countries (where imports constitute 95-100% of bilateral trade). So, a vast part of the so-called deficit-makers only sell something to Switzerland, without buying anything in return.

What is it that these countries have and Switzerland does not? What kind of advantage do they have over Switzerland? And is it really their or actually Switzerland's advantage? What brings together countries like Uzbekistan, South Africa, Ghana, Burkina Faso, the Ivory Coast, Peru or Kazakhstan with Switzerland?

Preview

Combined trading fact table of Switzerland and dim tables

Preview of trading fact table for Switzerland (ca. 200k rows) :

Spinner
DataFrameas
df
variable
SELECT *
FROM ch_2020_24.csv
LIMIT 5;

Preview of dim table with names of countries:

Spinner
DataFrameas
df1
variable
SELECT *
FROM countries.csv
LIMIT 5

Preview of dim table with names of commodities

Spinner
DataFrameas
df3
variable
SELECT *
FROM commodities.csv
LIMIT 5

Exploratory Data Analysis

Table showing all the fields:

Spinner
DataFrameas
df11
variable
SELECT *
FROM ch_2020_24.csv AS a
LEFT JOIN countries.csv AS b
		USING (partner_code)
LEFT JOIN commodities.csv AS c
		ON a.commodities_code = c.commodities_code
LIMIT 5

Focus on trading partners

To grasp the scale of the topic:

- all traded value per year (bln $)

- how big is 1% of that yearly trades (bln $)

- how much % the traded value grew compared to the previous year?

- how big is the top 10 countries compared to the overall trade per year?

Spinner
DataFrameas
traded_value_per_year
variable
SELECT
	*,
	trade_value_bln_usd/100 AS one_percent_yearly_trade,
	ROUND((trade_value_bln_usd * 100) / (LAG(trade_value_bln_usd,1) OVER (ORDER BY trade_year)),2) - 100 AS yearly_growth_percent
FROM (
	SELECT
		trade_year,
		ROUND((SUM(trade_value)/1000000000),2) as trade_value_bln_usd,
	FROM ch_2020_24.csv AS a
	GROUP BY trade_year
)
ORDER BY trade_year

Swiss trade balance of each year:

Spinner
DataFrameas
df13
variable
WITH import AS (
	SELECT
		trade_year,
		trade_flow,
		ROUND((SUM(trade_value)/1000000000),2) as import_value_bln_usd,
	FROM ch_2020_24.csv AS a
	WHERE trade_flow = 'Import'
	GROUP BY trade_year, trade_flow
),
export AS(
	SELECT
		trade_year,
		trade_flow,
		ROUND((SUM(trade_value)/1000000000),2) as export_value_bln_usd,
	FROM ch_2020_24.csv AS a
	WHERE trade_flow = 'Export'
	GROUP BY trade_year, trade_flow
),
total_year AS (
	SELECT
		trade_year,
		ROUND((SUM(trade_value)/1000000000),2) as total_trade_value_bln_usd,
	FROM ch_2020_24.csv AS a
	GROUP BY trade_year
)

SELECT
	b.trade_year,
	export_value_bln_usd,
	import_value_bln_usd,
	total_trade_value_bln_usd,
	export_value_bln_usd - import_value_bln_usd AS surplus_deficit_bln,
	ROUND(((export_value_bln_usd - import_value_bln_usd) *100/total_trade_value_bln_usd),2) AS percent_surplus
FROM import AS a
INNER JOIN export AS b
	USING(trade_year)
INNER JOIN total_year AS c
	USING(trade_year)
ORDER BY trade_year
‌
‌
‌