OECD Data Browser

[PeterF]

Dear Tom,

I download the OECD data from within RATS via the Data Browser menu entry. I apply the leading Indicators for some countries and aggregated entities for some regular tasks. I have now noticed that most of data sets are not updated beyond December 2023. Therefore, I looked at OECD websites and found the following hint for the CLIs at https://stats.oecd.org/viewhtml.aspx?da ... LI&lang=en which provides the hint "This dataset has been migrated to our new data dissemination platform OECD Data Explorer. You can now access it from here", where the word here is a link to "https://data-explorer.oecd.org/vis?lc=e ... IOD]=false". At this site, data until April 2024 is available. There is also a link to an API. would it be possible to make the necessary adjustments for downloading the OECD data again up till the latest available data point from OECD after their migration?

The download time for the full database is quiet long. I would prefer to update the data, when i am away from the pc, For this purpose, it would be nice to have an instruction to update data via the RATS data browers, which could be executed in batch mode and run automatically at a preset time by the windows task scheduler.

Best regards
PeterF

[TomDoan]

The batch suggestion is a good one (and should be relatively easy to implement). Unfortunately, the new API is not particularly compatible with the old one---in particular, the MEI as a separate entity has been eliminated and chopped into smaller pieces.

[TomDoan]

I got this from the OECD, which shows where in the new API to find the old MEI codes. They're actually spread across a lot more "data flows" than I thought. I was hoping that the "MEI SUBJECT" and "MEI MEASURE" information would be clear in the "dimension" fields (all the "Target" fields starting with "Measure") that are used to describe the data, but that is not the case. It looks like the thing to do is to use an input file like this one, and organize it so all the data that come off a single "dataflow" can be pulled at one time. By using an Excel spreadsheet as the input, the user could customize it by eliminating any data that didn't feel like they wanted. It would still probably take longer since some of those data flows have a great deal of data (for instance, some will have the same data in four or five different transformations, such as year over year growth, period to period growth,...).

[PeterF]

Dear Tom,

the OECD Data Browser is a helpful starting point for retrieving the OECD Datei. As I am interested in the OECD Composite Leading Indicators, I set the filters to get the amplitude adjusted values for China from May 1992 until the latest available data point. Once all filters are selected and set, one can click on the button "Developer API" and the web page will then change slightly displaying a new section "Developer API query builder", which contains a text window for the query of the time series. Copying this link and inserting it in a new tab of the browser leads to display of a XML file. To obtain the data in the csv-format, a small adjustment is necessary, but this is not well explained in the documentation. For the series CHNLOLITOAASTS in the chnoecd.rat file the corresponding URL is https://sdmx.oecd.org/public/rest/data/ ... od=1992-05. Inserted in a tab of a web browser, it will download a file in the csv format, but without the extesion .csv. For the link provided the filename is "CHN.M.LI...AA.IX..H". If I rename the file and add the extension .csv, then the data can be read with the usual instructions. However, trying to retrieve the data directly with the URL leads to either an error or wrong results.

Another aspect to observe is the structure of the csv-file. It requires some more options in the Data instruction. I got the correct data with "DATA(FORMAT=CDF,NOLABELS,ORG=COLUMNS,TOP=2,left=11,RIGHT=12,dateformat="yyyy-mm") gstart gend CHNLOLITOAASTS", where gstart and gend are the start and end date for downloading the data. It is in principle possible to download more then one time series per URL, but I dont recommend this as the file is not sorted.

If it were possible to get the data provided from the OECD directly into RATS then it would be at least possible to write batch files for downloading the series needed. Is there any solution to get the Data via a URL directly into RATS?

Best regards
PeterF

[TomDoan]

Is it my imagination or are the time periods out of order in the file created by the URL in your post?

[PeterF]

No worry, they are indeed out of order. But after adding the extension .csv at the end of the filename, RATS did sort the dates properly and the data is correct, as fas as my short test had shown, also graphical inspection showed the pattern I expected. If you select two countries, then not only the dates are out of order, but also the countries. Thus, I recommended to retrieve just one time series with one request. Otherwise, it gets a mess.

[PeterF]

As the direct data download via the OECD developer API did not work with the Data instruction because the data is not in chronologial order, the READ instruction leads to the solution. The following code is an example for the three versions of the Chinese Composite Leading Indicator (CLI). I used the series names from the chnoecd.rat file for the data starting in May 1992.

Code: Select all

*
* Example for updating OECED CLI for China via OECD Developer API (new version)
* data series starts in May 1992 and last observation is May 2024
* 

Calendar(M) 1992:5

comp gstart = 1992:5
comp gend   = 2024:5

declare vector[string] date
declare vector[real] value

*
* monthly amplitude adjusted CLI
* series name CHNLOLITOAASTS in the chnoecd.rat data file
*
Open Data "https://sdmx.oecd.org/public/rest/data/OECD.SDD.STES,DSD_STES@DF_CLI,4.1/CHN.M.LI...AA.IX..H?format=csv&startPeriod=1992-05"
Read(Varying,Unit=data,FORMAT=CDF,TOP=2,left=11,RIGHT=11) date
Read(Varying,Unit=data,FORMAT=CDF,TOP=2,left=12,RIGHT=12) value 
do i=1, %rows(date)
   comp year   = fix(%value(%left(date(i),4)))
   comp period = fix(%value(%right(date(i),2)))
   comp index  = %cal(year,period)
   set CHNLOLITOAASTS index index = value(i)
end do i

*
* monthly normalized CLI
* series name CHNLOLITONOSTS in the chnoecd.rat data file
* 
Open Data "https://sdmx.oecd.org/public/rest/data/OECD.SDD.STES,DSD_STES@DF_CLI,4.1/CHN.M.LI...NOR.IX..H?format=csv&startPeriod=1992-05"
Read(Varying,Unit=data,FORMAT=CDF,TOP=2,left=11,RIGHT=11) date
Read(Varying,Unit=data,FORMAT=CDF,TOP=2,left=12,RIGHT=12) value 
do i=1, %rows(date)
   comp year   = fix(%value(%left(date(i),4)))
   comp period = fix(%value(%right(date(i),2)))
   comp index  = %cal(year,period)
   set CHNLOLITONOSTS index index = value(i)
end do i

*
* monthly trend restored CLI
* series name CHNLOLITOTRSTS in the chnoecd.rat data file
* 
Open Data "https://sdmx.oecd.org/public/rest/data/OECD.SDD.STES,DSD_STES@DF_CLI,4.1/CHN.M.LI...TR.IX..H?format=csv&startPeriod=1992-05"
Read(Varying,Unit=data,FORMAT=CDF,TOP=2,left=11,RIGHT=11) date
Read(Varying,Unit=data,FORMAT=CDF,TOP=2,left=12,RIGHT=12) value 
do i=1, %rows(date)
   comp year   = fix(%value(%left(date(i),4)))
   comp period = fix(%value(%right(date(i),2)))
   comp index  = %cal(year,period)
   set CHNLOLITOTRSTS index index = value(i)
end do i

*
* Write to RATS Data File
*
dedit "C:\Rats Files\OECD Data\chnoecd.rat"
store CHNLOLITOAASTS CHNLOLITONOSTS CHNLOLITOTRSTS
Save
Close dedit

The code could easily adopted for the CLI for other contries by just replacing the 3-digit country code (CHN) with the one of the chosen country or grouping. The code could also used for updating other time series in the OECD rats data files or for retrieving other OECD data in time series format. Furthermore, part of the code could be shortened by writing a procedure. Another possibility to improve the updating would be to set up in Excel a list with the series names, the name of the rats data file, start date of the time series and the URL. It should be emphasized again that after the question mark in the URL the part "format=csv&" has to be included in the URL which the OECD Data Browser website provides, as RATS cannot yet read XML files.

[TomDoan]

Unfortunately, it looks like the new API isn't ready for prime-time. I've attached the results from the German data from their list of the MEI series. For some bizarre reason, most of the series are there (I already pointed out to them that they were missing the PPI data), but have almost random numbers of data points. I checked the raw data (JSON) downloads, and this is an accurate depiction of what was on that. If it says it has one value only for a series for 2016:1, that appears to be the correct value for 2016:1 for that series, but the rest of the data are gone. I wouldn't be surprised if it is somehow related to the problem with the CSV having observations out of order.