Comtrade API in Stata

[Amjad Masood]

Hello
I aim to get trade data from Comtrade site using the API from within the dofile. I got this page http://statadaily.com/2015/09/08/un-...-api-in-stata/ which tells two possible ways. Through the link, for the example given there:
import delimited http://comtrade.un.org/api/get?r=608...=TOTAL&fmt=csv
Alternatively, by a program. However, I got an error for both of these.

file http://comtrade.un.org/api/get? not found
server says file temporarily redirected to https://comtrade.un.org/api/get?
could not open url
r(603);

I have Stata/MP13.0.
Would be great if someone please look into this.

[Nick Cox]

Just by clicking on your second link I got a .csv file. All you need to do is save that to your own system and then use import delimited

[Amjad Masood]

Thanks Nick for your response.
Yes, that link works but that is the Comtrade API code, just an example, to extract TOTAL EXPORTS of the PHILIPPINES to ALL its trading PARTNERS in 2014 in CSV format.

In fact I frequently need to download data with different parameters in terms of EXPORTERS, IMPORTERS, PRODUCTS, and
YEARS etc, so an ado program may help.

Therefore, the point is to make it work from within the dofile. So that using a loop to automate downloads for many countries for many years and for many goods. The Stata Daily page http://statadaily.com/2015/09/08/un-...-api-in-stata/ tells the method but it is not working for me.

[Nick Cox]

Thanks for the detail; I see more of what you mean. I have not tried Mitch Abdon's program. It may or may not be true here, but such programs are often broken completely by changes in the interface.

[Amjad Masood]

the API call, the part of the command after import delimited, works well. That means all parameters are okay, or at least I feel, and the problem is Stata failing to be directed to the file which is actually downloadable.

[wbuchanan]

Amjad Masood from the API documentation it seems you can get the data in JSON format. In that case you can use jsonio to query the incoming JSON payloads to select only the specific elements that are of interest to you.

[Amjad Masood]

I find that jsonio needs at least Stata 14.0, and I unfortunately have 13.0 version.

[Boris Knapp]

Amjad Masood I am also trying to extract data through the API directly via Stata and I found the same instructions online as you.
I, however, get a different error.
Namely,
=exp not allowed
r(101);

Have you overcome the Problem and got it to work?

@all Does anybody have tips why it might not be working?
When I type in
import delimited http://comtrade.un.org/api/get?r=608...=TOTAL&fmt=csv

I get the above error message.

[JanDitzen]

Boris, Amjad: I receive the same error. Apparently the comtrade server redirect to a secure SSL connection. So if you type

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

then the error is:

sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderE xception: unable to find valid certification path to requested target

It seems to me that Stata does not support SSL connections (please correct me if not true!). There is a workaround if you can use wget (has to be downloaded separately) and shell or winexec:

winexec C:\**path to wget**\wget.exe ///
wget -r --tries=10 -O test.csv --no-check-certificate ///
http://comtrade.un.org/api/get?max=5...=TOTAL&fmt=csv

I am calling wget with winexec (it might be possible to do with shell, but I cannot install wget, so I have to call the exe by hand). Then I call wget the options to replace the file, try 10 times, save it as test.csv and do not check for certificates.

This downloads all commodities for Fiji in the years 2012 - 2016 and saves it as test.csv in your documents. How to build the string behind "get?" is explained here: https://comtrade.un.org/data/doc/api/

[Ricardo Cantu]

Does anyone found the answer for this error? I'm experiencing the same issue, and for me too, it is easier to download my datasets automatically through the web because they are updated very frequently. Thanks a lot!

[JanDitzen]

Ricardo Cantu I coded a downloader for Comtrade, see

ssc install comtrade

and I put a few more information here.

If I recall correctly, the problem above is that Stata versions <14 (if I am right, I dont have Stata 13 at hand) do not support https connections. Therefore as comtrade (the website) uses https for data downloads and the user written comtrade program use https connections, you need Stata 14 or later to download data directly from comtrade (website).

As an alternative you can alter the code from above, in which you call an external program which downloads the data. There are many possibilities around, I suggested to use wget here.

[Ricardo Cantu]

JanDitzen Thanks a lot for your answer and for your time to reply to my question. I understand that Stata 13 might not support https connections; however, it is funny that before I could automatically download these same datasets. Maybe something changed from the website I was downloading. Nonetheless, I appreciate your help!

[JanDitzen]

Ricardo Cantu, Comtrade indeed changed their downloads from http to https. That's why it is not working anymore.
Jan