UN Comtrade: Trade Data, Formulas and Extraction

This guide provides researchers and economists with the methodology, mathematical formulas, and programmatic code required to extract and analyze international trade statistics from the United Nations Commodity Trade Statistics Database (UN Comtrade). UN Comtrade is the official multilateral database maintained by the United Nations Statistics Division (UNSD), compiling detailed merchandise trade records reported by approximately 200 countries and territories. Aligned with World Bank Data platforms and national customs frameworks, Comtrade standardizes international trade flows under the Harmonized Commodity Description and Coding System (HS), Standard International Trade Classification (SITC), and Classification by Broad Economic Categories (BEC). Economists frequently combine Comtrade trade records with IMPLAN regional input-output models to trace inter-industry supply chains and evaluate regional macroeconomic trade shocks.

Definition in One Paragraph

UN Comtrade (United Nations Commodity Trade Statistics Database) is the official multilateral repository administered by the United Nations Statistics Division (UNSD) that compiles and standardizes international merchandise trade statistics reported by national customs authorities worldwide. Governed by the United Nations International Merchandise Trade Statistics: Concepts and Definitions 2010 (IMTS 2010) framework, merchandise trade is defined as goods that add to or subtract from the stock of material resources of a country by entering (imports) or leaving (exports) its economic territory. The database records cross-border merchandise transactions categorized into imports, exports, re-imports, and re-exports, covering over 99 percent of global goods trade from 1962 to the present. Values are compiled in current United States dollars alongside supplementary physical quantity metrics and net weight in kilograms, providing the empirical foundation for trade policy, balance of payments accounting, and bilateral mirror flow reconciliation.

Formula and Trade Valuation Metrics

Analyzing merchandise trade data from UN Comtrade requires standardized arithmetic formulations to compute trade balances, valuation adjustments, and bilateral asymmetries. The core metrics and their mathematical definitions include:

1. Merchandise Trade Balance (TB)
The net merchandise trade balance measures the difference between outgoing and incoming goods across economic borders:

TB = X – M

Where X represents total merchandise exports and M represents total merchandise imports. In UN Comtrade reporting, exports are recorded on a Free on Board (FOB) basis, whereas imports are customarily recorded on a Cost, Insurance, and Freight (CIF) basis:

TB_CIF = X_FOB – M_CIF

For national accounts and balance of payments reconciliations, imports are adjusted to an FOB valuation basis (M_FOB):

TB_FOB = X_FOB – M_FOB

2. CIF-to-FOB Valuation Margin (Δ_CIF/FOB and r_CIF/FOB)
The difference between CIF and FOB import valuations isolates the cost of international transport, freight, and insurance between exporting and importing borders:

Δ_CIF/FOB = M_CIF – M_FOB

r_CIF/FOB = ((M_CIF – M_FOB) / M_FOB) × 100%

3. Trade Coverage Ratio (TCR)
The trade coverage ratio evaluates the extent to which merchandise export earnings cover import expenditures:

TCR = (X / M) × 100%

4. Bilateral Mirror Discrepancy (Δ_mirror)
In bilateral trade research, country A reported exports to partner B (X_AB^FOB) rarely equal partner B reported imports from country A (M_BA^CIF). The mirror discrepancy isolates this divergence:

Δ_mirror = M_BA^CIF – X_AB^FOB

The mathematical symbols used in UN Comtrade accounting are summarized below:

Symbol	Variable Name	Economic Definition	Unit
X_FOB	Merchandise Exports (FOB)	Outgoing goods value at exporter customs border, excluding international shipping freight and insurance	Current USD
M_CIF	Merchandise Imports (CIF)	Incoming goods value delivered at importer customs frontier, including international carriage and insurance	Current USD
M_FOB	Merchandise Imports (FOB)	Supplementary value of incoming goods excluding international transport and transit insurance	Current USD
TB	Trade Balance	Net merchandise trade flow (X – M); positive values indicate surplus, negative indicate deficit	Current USD
r_CIF/FOB	CIF/FOB Margin Ratio	Percentage markup attributable to international transport, carriage, and maritime freight insurance	Percentage (%)
TCR	Trade Coverage Ratio	Proportion of merchandise import spending covered by export revenue	Percentage (%)
Δ_mirror	Mirror Trade Discrepancy	Numerical divergence between partner import records and reporter export records (M_BA^CIF – X_AB^FOB)	Current USD

Economists estimating empirical trade flow determinants frequently use linear and multiple regression models or examine inter-market correlations through a covariance matrix across multilateral trading partners.

Worked Example with Real Numbers and Computed Arithmetic

To demonstrate the exact arithmetic, we apply these formulas to official annual trade statistics extracted directly from the UN Comtrade database for the United States (reporter code 842) for calendar year 2023 across all merchandise commodities (commodity code TOTAL, partner code 0 for World):

Reporter: United States (UN M49 code 842)
Partner: World (UN M49 code 0)
Reference Year: 2023 (Annual, refYear = 2023)
Total Merchandise Exports (X_FOB): $2,018,542,583,771.00 USD
Total Merchandise Imports at CIF (M_CIF): $3,168,471,121,076.00 USD
Total Merchandise Imports at FOB (M_FOB): $3,080,052,528,515.00 USD

Step 1: Compute CIF-to-FOB Valuation Margin on Imports
We isolate the international shipping and insurance surcharge on United States imports in 2023:

Δ_CIF/FOB = $3,168,471,121,076 – $3,080,052,528,515 = $88,418,592,561.00 USD

We calculate the percentage margin over the FOB baseline:

r_CIF/FOB = ($88,418,592,561 / $3,080,052,528,515) × 100% = 0.0287068 × 100% = 2.87068%

The insurance and freight wedge added 2.87% to the FOB value of imported merchandise.

Step 2: Compute Headline Merchandise Trade Balance (CIF Imports)
Under standard UN IMTS 2010 guidelines, headline merchandise trade balances compare FOB exports against CIF imports:

TB_CIF = $2,018,542,583,771 – $3,168,471,121,076 = -$1,149,928,537,305.00 USD

The United States recorded a headline merchandise trade deficit of $1,149,928,537,305.00 USD (approximately $1.150 trillion) in 2023.

Step 3: Compute Balance of Payments Trade Balance (FOB Imports)
Under IMF Balance of Payments standards, imports are measured FOB to avoid double-counting transport services in the goods balance:

TB_FOB = $2,018,542,583,771 – $3,080,052,528,515 = -$1,061,509,944,744.00 USD

On a pure FOB basis, the merchandise trade deficit is $1,061,509,944,744.00 USD (approximately $1.062 trillion). The difference between the two balance figures is exactly $88,418,592,561.00 USD, representing international freight and insurance services.

Step 4: Compute the Trade Coverage Ratio
We calculate the trade coverage ratio under both valuation approaches:

TCR_CIF = ($2,018,542,583,771 / $3,168,471,121,076) × 100% = 63.7071%

TCR_FOB = ($2,018,542,583,771 / $3,080,052,528,515) × 100% = 65.5359%

In 2023, United States merchandise exports funded 63.71% of its CIF merchandise imports and 65.54% of its FOB imports.

Interpretation of Trade Flows and Mirror Discrepancies

Interpreting UN Comtrade data requires evaluating both macroeconomic accounting and bilateral partner relationships. The table below outlines reading guidance for trade indicators and mirror statistics:

Indicator Value	Economic Interpretation	Structural Meaning	Research Action
TB > 0	Merchandise Trade Surplus	Domestic export production exceeds domestic absorption of foreign goods	Assess whether surplus reflects expanding export competitiveness or depressed domestic import demand
TB < 0	Merchandise Trade Deficit	Domestic consumption and investment demand exceed domestic physical output	Verify valuation basis (CIF vs. FOB); adjust for shipping costs when integrating into national accounts
TCR < 100%	Deficit Coverage Position	Export receipts cover only a fraction of foreign merchandise expenditure	Examine foreign reserve stability and external financing requirements
Δ_mirror > 0	Positive Mirror Asymmetry (M_BA^CIF > X_AB^FOB)	Partner country records higher import value than exporter recorded outgoing shipments	Standard divergence from CIF transport margins (3% to 10%); larger gaps indicate entrepôt transit hubs
Δ_mirror < 0	Negative Mirror Asymmetry (M_BA^CIF < X_AB^FOB)	Partner imports fall below exporter recorded shipments	Investigate customs clearance time lags across fiscal years, tariff evasion, or shipment diversion

When modeling international trade regressions, omitting structural factors such as bilateral freight costs or border contiguity can induce omitted variable bias, distorting estimated trade elasticities.

Assumptions and Common Mistakes

Working with UN Comtrade records requires awareness of administrative customs conventions. Researchers must avoid the following common pitfalls:

General Trade System vs. Special Trade System: The general trade system encompasses the entire national territory, including commercial free zones and bonded warehouses. The special trade system excludes goods entered into bonded facilities until cleared for domestic consumption. Comparing a general-system country with a special-system country creates artificial structural gaps.
Partner Attribution (Country of Origin vs. Country of Consignment): Under IMTS recommendations, imports are attributed to the country of origin, whereas exports reflect the country of last known destination. When goods transit third-party trade hubs (such as Rotterdam or Singapore), the transit country may record exports or re-exports, while the final destination attributes imports to the primary producer, creating bilateral asymmetries.
Valuation Standard Inconsistencies: Subtracting CIF imports from FOB exports understates the goods balance by shipping and insurance costs. While most nations report imports CIF, certain countries (such as the United States and Australia) traditionally compile imports on an FOB basis.
Harmonized System Nomenclature Vintages: The World Customs Organization revises the Harmonized System every 5 years (HS 1992, HS 1996, HS 2002, HS 2007, HS 2012, HS 2017, HS 2022). Multi-year longitudinal studies must apply official concordance tables to align legacy HS subheadings with modern codes.
Data Suppression and Chapter 99 Aggregation: Customs rules mandate confidentiality for sensitive or monopolized commodities (such as specialized chemicals or aerospace parts). National authorities suppress these detailed 6-digit lines and roll values into chapter 99, leading researchers to misinterpret zero-values as absent trade.

How to Report It (APA Style)

When reporting UN Comtrade extractions and calculations in academic publications, the methodology must specify the database source, classification system, revision vintage, valuation basis, and access date. Below is a standard reporting template adhering to APA 7th guidelines:

“Bilateral merchandise trade flows and trade balances were retrieved from the United Nations Commodity Trade Statistics Database (UN Comtrade, UNSD, 2024). Trade values were compiled in current United States dollars under the Harmonized Commodity Description and Coding System 2022 edition (HS 2022) at the 6-digit subheading level. Merchandise exports were measured on a Free on Board (FOB) valuation basis, while imports were evaluated on both Cost, Insurance, and Freight (CIF) and FOB bases to reconcile transport and insurance margins. Re-exports and re-imports were identified to isolate domestic production from entrepôt transshipments.”

The corresponding APA 7th reference list entry should be formatted as follows:

United Nations Statistics Division. (2024). UN Comtrade Database [Data set]. United Nations. https://comtradeplus.un.org

How to Calculate It in Excel, R, and Python

Programmatic workflows allow researchers to automate data retrieval from the UN Comtrade API and compute trade indicators directly. The following code snippets provide verified implementations in Python, R, and Microsoft Excel.

Python: Querying the UN Comtrade API and Calculating Trade Metrics

This script uses Python standard library modules to retrieve official trade records from the UN Comtrade v1 preview API and compute the merchandise trade balance, CIF/FOB margin, and trade coverage ratio:

import urllib.request
import json

base_url = "https://comtradeapi.un.org/public/v1/preview/C/A/HS"
headers = {"User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"}

def fetch_trade_flow(reporter_code, year, flow_code):
    url = f"{base_url}?reporterCode={reporter_code}&period={year}&partnerCode=0&flowCode={flow_code}&cmdCode=TOTAL"
    req = urllib.request.Request(url, headers=headers)
    with urllib.request.urlopen(req, timeout=15) as response:
        payload = json.loads(response.read().decode('utf-8'))
        records = payload.get("data", [])
        return records[0] if records else None

exp_data = fetch_trade_flow(reporter_code=842, year=2023, flow_code="X")
imp_data = fetch_trade_flow(reporter_code=842, year=2023, flow_code="M")

x_fob = float(exp_data["fobvalue"])
m_cif = float(imp_data["cifvalue"])
m_fob = float(imp_data["fobvalue"])

margin_usd = m_cif - m_fob
margin_pct = (margin_usd / m_fob) * 100.0
tb_cif = x_fob - m_cif
tb_fob = x_fob - m_fob
tcr_cif = (x_fob / m_cif) * 100.0

print(f"Total Exports (FOB):       ${x_fob:,.2f}")
print(f"Total Imports (CIF):       ${m_cif:,.2f}")
print(f"Total Imports (FOB):       ${m_fob:,.2f}")
print(f"CIF/FOB Margin:            ${margin_usd:,.2f} ({margin_pct:.2f}%)")
print(f"Trade Balance (CIF basis):  ${tb_cif:,.2f}")
print(f"Trade Balance (FOB basis):  ${tb_fob:,.2f}")
print(f"Trade Coverage Ratio:      {tcr_cif:.2f}%")

R: Extracting and Analyzing Comtrade Flows

This R script queries the UN Comtrade API using base R and jsonlite, calculating trade balances and valuation differences:

library(jsonlite)

base_url <- "https://comtradeapi.un.org/public/v1/preview/C/A/HS"
user_agent <- "Mozilla/5.0 (Windows NT 10.0; Win64; x64)"

fetch_comtrade <- function(reporter, period, flow) {
  endpoint <- sprintf("%s?reporterCode=%s&period=%s&partnerCode=0&flowCode=%s&cmdCode=TOTAL",
                      base_url, reporter, period, flow)
  con <- url(endpoint, headers = c("User-Agent" = user_agent))
  raw_data <- fromJSON(con)
  close(con)
  return(raw_data$data)
}

exp_record <- fetch_comtrade(842, "2023", "X")
imp_record <- fetch_comtrade(842, "2023", "M")

x_fob <- as.numeric(exp_record$fobvalue[1])
m_cif <- as.numeric(imp_record$cifvalue[1])
m_fob <- as.numeric(imp_record$fobvalue[1])

cif_fob_margin <- m_cif - m_fob
margin_rate    <- (cif_fob_margin / m_fob) * 100
tb_cif         <- x_fob - m_cif
tb_fob         <- x_fob - m_fob
coverage_ratio <- (x_fob / m_cif) * 100

cat(sprintf("Exports (FOB):        $%.2fn", x_fob))
cat(sprintf("Imports (CIF):        $%.2fn", m_cif))
cat(sprintf("Trade Balance (CIF):  $%.2fn", tb_cif))
cat(sprintf("CIF-FOB Margin Rate:  %.2f%%n", margin_rate))
cat(sprintf("Trade Coverage Ratio: %.2f%%n", coverage_ratio))

Excel: Direct Cell Formulas and Power Query Integration

In Microsoft Excel, trade calculations can be performed on downloaded UN Comtrade worksheets using structured formulas:

Assuming Cell B2 contains Exports FOB (2018542583771), Cell B3 contains Imports CIF (3168471121076), and Cell B4 contains Imports FOB (3080052528515):
Trade Balance (CIF): =B2-B3 (Yields -1149928537305)
Trade Balance (FOB): =B2-B4 (Yields -1061509944744)
CIF-to-FOB Margin ($): =B3-B4 (Yields 88418592561)
CIF-to-FOB Margin (%): =(B3-B4)/B4 (Format as percentage to yield 2.87%)
Trade Coverage Ratio (%): =B2/B3 (Format as percentage to yield 63.71%)

Frequently Asked Questions (FAQ)

What is the difference between UN Comtrade and World Bank WITS?

UN Comtrade is the underlying primary database maintained by the United Nations Statistics Division that collects and stores official customs trade records directly from national governments. The World Bank World Integrated Trade Solution (WITS) is an analytical software platform that integrates UN Comtrade with other international databases, such as UNCTAD TRAINS for non-tariff measures and WTO IDB/CTS for tariff schedules, providing pre-calculated trade indicators and tariff simulations.

How often is UN Comtrade updated?

UN Comtrade receives updates on a rolling, continuous basis as national statistical institutes and customs authorities submit their official annual and monthly trade files. Most member states submit annual merchandise trade files within six to nine months following the close of the calendar year, while monthly trade files are uploaded as national processing permits.

Why do bilateral import and export values between two partner countries not match?

Bilateral mirror discrepancies arise primarily because exports are recorded on a Free on Board (FOB) basis while imports are recorded on a Cost, Insurance, and Freight (CIF) basis, creating an inherent valuation wedge reflecting maritime transit, shipping insurance, and port handling. Additional discrepancies stem from differences in partner attribution (country of origin versus country of consignment), intermediate trade through logistics entrepôt hubs, customs processing time lags across reporting periods, and currency conversion timing.

What is the difference between HS codes and SITC codes in UN Comtrade?

The Harmonized Commodity Description and Coding System (HS) is an internationally standardized nomenclature managed by the World Customs Organization that classifies goods according to their material composition and physical characteristics, serving as the basis for national customs tariffs. The Standard International Trade Classification (SITC), developed by the United Nations, classifies goods according to their economic function, stage of processing, and industrial use, making it ideal for long-term historical economic analysis.

Is the UN Comtrade API free to use?

Yes. The United Nations offers a free public preview API tier that allows users to test endpoints and retrieve up to 500 records per request without registration. Registered users on the ComtradePlus platform receive a free subscription key enabling larger automated data queries (up to 100,000 records per request) subject to monthly request quotas, with premium subscriptions available for enterprise-scale bulk extractions.

Sources

United Nations Statistics Division. (2011). International Merchandise Trade Statistics: Concepts and Definitions 2010 (IMTS 2010) (Statistical Papers, Series M, No. 52/Rev.3). United Nations Publications. https://unstats.un.org/unsd/trade/eg-imts/imts2010.pdf (Accessed 2026-11-08)
United Nations Statistics Division. (2024). UN Comtrade v1 RESTful API Technical Specification and Data Platform. United Nations. https://comtradeapi.un.org (Accessed 2026-11-08)
World Bank. (2024). World Integrated Trade Solution (WITS): Trade Indicators and Database Concepts. World Bank Group. https://wits.worldbank.org/wits/wits/witshelp/content/data_retrieval/t/intro/t2.htm (Accessed 2026-11-08)
World Customs Organization. (2022). The Harmonized Commodity Description and Coding System (HS Nomenclature 2022 Edition). World Customs Organization. (Accessed 2026-11-08)