UN Comtrade API in Stata

The UN Comtrade is the largest repository of disaggregated trade statistics. It offers free access to detailed annual trade data starting from 1962 and monthly trade data from 2010. Free access is limited to 50,000 records per query. This limitation is relaxed in some cases but the API (as of date) only allows a maximum of 50,000 records per query for all users.

The UN Comtrade data extraction API (currently beta version) to access the database is publicly available. How can we exploit this to download Comtrade data directly from Stata?

Data requests takes the form:


where parameters are detailed in in the API documentation site. Selected API parameters are listed below:
Output format           fmt  
Reporting area          area
Frequency               freq 
Time period             ps
Classification          px
Partner area            p 
Trade flow              rg
Classification code     cc

For example, the API call to extract total export of the Philippines to all its trading partners in 2014 in CSV format is:


If you execute this link from your internet browser, the data specified is download into your downloads folder, wherever that may be. But how can we download this directly from Stata? import delimited (Stata 13) will do the trick (I tried insheet in Stata 12 and Stata 13 but I cannot make it work).

To download the specified data above within Stata, type:

import delimited http://comtrade.un.org/api/get?r=608&freq=A&ps=2014&p=all&rg=2&cc=TOTAL&fmt=csv

You can then use loops to extract more data. If you need to access UN Comtrade frequently, however, it might be helpful to create your own ado file that is much more readable than the API call. For example, we can write comtradedl.ado with the syntax:

comtradedl, parameters

Using comtrade.ado, the specified data above can be downloaded by typing:

comtradedl, r(608) freq(A) px(H2) ps(2014) rg(2) cc(TOTAL)

This will then return:
Extracting data from UN Comtrade:
Reporters:            608 (All or up to 5 may be selected)
                      -all may only be used if a partner is selected
Partners:             all (All or up to 5 may be selected)
                      -all may only be used if a reporter is selected
Frequency:            A
Time period:          2014 (Up to 5 may be selected)
Classification:       H2
Trade flow:           2
Classification code:  TOTAL

API call: http://comtrade.un.org/api/get?r=608&freq=A&ps=2014&px=H2&p=all&rg=2&cc=TOTAL&fmt=csv
(22 vars, 209 obs)

Total records returned:      209

Note that the limits indicated above are set by the API (as of date). Here is my version 1 of comtradedl.ado:
* comtradedl
*! 08sep2015 v1 Mitch Abdon

program comtradedl

version 13
syntax [anything] [,            ///
    r(numlist >=0 integer sort)  ///
    freq(string)            ///
    ps(numlist >=0 integer sort) ///
    px(string)          ///
    p(numlist >=0 integer sort)  ///
    rg(numlist >=0 integer sort) ///
    cc(string)          ///

// reporter country 
if "`r'" == "" | "`r'" == "999" {
    loc rnew = "all"
    else {
        loc rnew = subinstr("`r'", " ", "%2C", .)

// frequency
if "`freq'" == "" | "`freq'" == "A" {
    loc freqnew = "A"
    else if "`freq'" == "M" {
        loc freqnew = "M"
    else {
        di as err "Error in freq(). Must be A or M."

// time period
if "`ps'" == "" | "`ps'" == "997" {
    loc psnew = "now"
    else if "`ps'" == "`998'" {
        loc psnew = "recent"
    else {
        loc psnew = subinstr("`ps'", " ", "%2C", .)

//  classification
if "`px'" == "" {
    loc pxnew = "HS"
    else {
        loc pxnew = "`px'"

// partner country  
* http://comtrade.un.org/data/cache/partnerAreas.json
if "`p'" == "" | "`p'" == "999" {
    loc pnew = "all"
    else {
        loc pnew = subinstr("`p'", " ", "%2C", .)

// trade flow
if "`rg'" == "" {
    loc rgnew = "all"
    else if `rg'>4 & !mi(`rg') {
        di as err "Error in rg(). Must be within [1,4]."
        di as err  "1 - Import"
        di as err  "2 - Export"
        di as err  "3 - re-Export"
        di as err  "4 - re-Import"
    else {
        loc rgnew = `rg'    

// classification code
if "`cc'" == "" {
    loc ccnew = "AG2"
    else {
        loc ccnew = "`cc'"
global parms = "r=`rnew'" + "&freq=`freqnew'"   ///
    + "&ps=`psnew'" + "&px=`pxnew'" + "&p=`pnew'" ///
    + "&rg=`rgnew'" + "&cc=`ccnew'" + "&fmt=csv"
global apiadd = "http://comtrade.un.org/api/get"
// output display
loc rnew = subinstr("`rnew'", "%2C", ", ", .)
loc pnew = subinstr("`pnew'", "%2C", ", ", .)
loc psnew = subinstr("`psnew'", "%2C", ", ", .)

di as text "----------------------------------------------------------------------------"   
di as text "Extracting data from UN Comtrade:"
di as text "----------------------------------------------------------------------------"   
di as text "Reporters:"         _col(23) "`rnew' (All or up to 5 may be selected)"  
di as text              _col(23) "-all may only be used if a partner is selected"
di as text "Partners:"          _col(23) "`pnew' (All or up to 5 may be selected)"
di as text          _col(23) "-all may only be used if a reporter is selected"
di as text "Frequency:"         _col(23) "`freqnew'" 
di as text "Time period:"       _col(23) "`psnew' (Up to 5 may be selected)"
di as text "Classification:"    _col(23) "`pxnew'"
di as text "Trade flow:"        _col(23) "`rgnew'" 
di as text "Classification code:"  _col(23) "`ccnew'"
di as text "----------------------------------------------------------------------------"   

di as text _n(1) "API call: $apiadd?$parms"
import delimited  "$apiadd?$parms"
di as text _n(1) "Total records returned: "  _col(30) _N

These settings are as of date. Check the The UN Comtrade data extraction API documentation site to know about changes.

In an older post we used ComtradeTool to automate the downloading of data from UN Comtrade database, but it looks like the development of that tool ended in 2010.

Leave a Reply