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?
Continue reading

ComtradeTools and Stata: Automating UN Comtrade data downloads

ComtradeTools (developed by UN) is a command line program that allows you to obtain data via UN Web Services and to convert the data into CSV format (or to an SQL server). You can use Stata’s -shell- command to run ComtradeTools.

First, read the instructions here. Download the latest version (March 2010) of ComtradeTools here and the required Microsoft .NET Framework (version 1.1.4322.573) here.

The command line parameters are listed in /help which you can copy into a text file. To copy ComtradeTools /help contents to ComtradeTools_Help.txt:

cd “C:\Program Files\UNSD\ComtradeTools\”
shell ComtradeTools /help >>U:\Data\Comtrade\ComtradeTools_Help.txt

/help does not enumerate all possible entries for each parameter, but you can find them in UN Comtrade’s website. Some are listed below:

/r: Reporter Code. To get the list of countries and their corresponding codes:

copy http://comtrade.un.org/ws/refs/getCountryList.aspx U:\Data\Comtrade\Countrylist.xml //You can open this in Excel

/y: Year. 4-digit year.

/px: Classification.
SITC Rev.3=S3
SITC Rev.2=S2
SITC Rev.1=S1

/cc: Commodity Code. For example, to get commodity codes for HS1996 and SITC Rev. 2:

local classification H1 S2
foreach c of local classification{
copyhttp://comtrade.un.org/ws/refs/getCommodityList.aspx?px=c'" <em>U:\Data\Comtrade\Commoditylist_</em>c’.xml

/rg: Trade Flow.

Try to download trade data (SITC Rev. 2) for the Philippines for the year 2007 and load it to Stata.

shell ComtradeTools /r:608 /y:2007 /px:S2 /action:DownloadAndConvertToCSV /outputDirectory:U:\Data\Comtrade\
insheet using U:\Data\Comtrade\S22007608_CSV.txt, comma clear

Note that if you do not specify /outputDirectory, the files will be saved in the current working directory, that is, where the program ComtradeTools is saved.

Now, it is easy to download data for multiple countries and years by using loops. For example, to download trade data (SITC Rev. 2) for the Philippines every 5 years from 1980 to 2005 and save them as dta files:

forvalues y=1980(5)2005{
shell ComtradeTools /r:608 /y:y' <strong>/px:</strong><em>S2</em> <strong>/action:</strong>DownloadAndConvertToCSV <strong>/outputDirectory:</strong><em>U:\Data\Comtrade\</em>
<strong>insheet </strong>using <em>U:\Data\Comtrade\S2</em>
y’608_CSV.txt, comma clear
save U:\Data\Comtrade\S2y'<em>608.dta</em>, replace
<strong>erase </strong><em>U:\Data\Comtrade\S2</em>