We present the OpenTED browser, a Web application allowing to interactively browse public spending data related to public procurements in the European Union. The application relies on Open Data recently published by the European Commission and the Publications O ce of the European Union, from which we imported a curated dataset of 4.2 million contract award notices spanning the period 2006-2015. The application is designed to easily lter notices and visualise relationships between public contracting authorities and private contractors. The simple design allows for example to quickly nd information about who the biggest suppliers of local governments are, and the nature of the contracted goods and services. We believe the tool, which we make Open Source, is a valuable source of information for journalists, NGOs, analysts and citizens for getting information on public procurement data, from large scale trends to local municipal developments.
Public procurement is the process whereby governments buy goods and services,
such as o ce supplies, equipment, buildings, roads, and so forth. It represents
about one third of total government expenditures in OECD countries [
The development of Tenders Electronic Daily (TED) [
Along with TED, the Publications O ce provides bulk downloads of its data. The raw dataset contains all contract notices for tender data since 1995 in XML format, and its size is around 100 GB (GigaBytes). The browsing and analysis of this dataset raises a number of complex challenges, due to varying quality of data between countries and years, missing values, variability in the naming of contracting authorities and entities, multilingual documents, and so forth.
The Directorate-General for Internal Market, Industry, Entrepreneurship and
SMEs (DG-GROW) of the European Commission has recently initiated great
e orts in the curation of the Publications O ce data, and released in August
2015 a summary of all contract award notices (CANs) for European public
procurements for the period spanning 2006-2015 [
The relative large size of the dataset however still prevents its analysis from users without an analytics background: Data is provided as CSV les for each year of CAN (from 2006 to 2015, ten les in total), whose size ranges from around 100 MB (MegaBytes) to 300 MB, totalising about 2 GB (GigaBytes). While the size of the dataset can be stored without trouble on current laptop or desktop con gurations, the opening of such les remains a challenge for standard spreadsheet applications such as Excel, and makes analyses spanning multiple years (i.e. ltering data from multiple les) very tedious. It must be stressed that most of the people interested in CAN data (journalists, entrepreneurs, citizen, ...) do not have the necessary analytics background to explore datasets of such size.
The OpenTED browser aims at bridging this gap, and our contributions are the following. First, data is stored on our OpenTED server, and does not need to be fully downloaded, making it easier for users with slow Internet connection to access the data. Users can furthermore lter the data they need (according to countries, years, type of goods, and so forth), and download only what they are interested in. Second, we provide a visualisation tool, based on Sankey diagrams, that represents as a graph the contract awards between public authorities and private contractors. The visualisation makes clear how much money the contracts are worth, and provides hyperlinks to the o cial TED award notices for further details. Finally, we make the code for both the data preprocessing (Python) and the Web application (R Shiny) open source. A docker container can be used to run the Web application on a local machine, making the interaction with the application even faster.
The paper is organised as follows. Section 2 details the o cial TED CAN data, and how they were preprocessed for the OpenTED browser. Section 3 presents the OpenTED browser Web interface. Section 4 presents the lottery game `Public spending is fun! who wants to be a supplier?', a `gami cation' of the search for contract award notices. Section 5 concludes the paper with open issues and perspectives. 2
Raw data was retrieved from the Open Data portal of the European Union [
The data comes from the European Economic Area, Switzerland, and the former
Yugoslav Republic of Macedonia and covers the time period between 2006/01/01
and 2015/12/31. It is in comma separated value (CSV) format and is encoded as
UTF-8. Generally, the data consists of notices above the procurement thresholds.
However, publishing below threshold notes in TED is considered good practice,
and thus a non-negligible number of below threshold notices is present as well
[
2006 2007 2008 2009 2010 2011 2012 2013 2014 2015
Year
The number of countries covered has increased throughout the years, generally in line with their accession to the single European market. This is illustrated in Fig. 1, which reports the number of CANs per country and per year, since s e c i to4e+05 n d r a w a t c a tr n o fc2e+05 o r e b m u N 0e+00 Contracting authority country
France Other Poland Germany United Kingdom Spain Italy Romania Lithuania 2006. Only countries with more than 15,000 CANs per year are reported for clarity reasons (number of CANs for other countries are summarized in `Other').
The number of CANs increased from about 250,000 in 2006 to more than 500,000 in 2015, and a total of 4,283,986 CANs are available in the dataset. It is worth noting that the number of CANs available per country is quite imbalanced, and are the highest for France, Poland, Germany and the United Kingdom (UK).
The data includes 48 selected elds from CANs (Table 3 in the Annex),
divided in six categories: Notice metadata, Contracting authority or entity identi
cation, Winning bidder identi cation, Various CAN level variables, and Various
CA level variables [
We remained as faithful as possible to the original data, and included all 4,283,986 records and 48 elds in the OpenTED browser. For making the ltering of the dataset more user-friendly, we however renamed nine elds, which are highlighted in the OpenTED browser (see Section 3). These elds consist in the date of the CAN, the identi er (ID), the name and country of the contracting authority, the name and country of the contractor, the value of the CAN, the number of o ers and the CPV code. The renaming is detailed in Table 1.
Besides the renaming, we also reformatted elds involving dates, countries,
and values. In the original data, date formats are Day-Months (3 rst
letters)Year (2 last digits), e.g., `31-DEC-13'. We reformatted it as Year (4
digits)Month (2 digits)-Day, e.g., `2013-12-31'. Such conversion makes it more suitable
to de ne ltering intervals on dates, using operator such as greater or less than
(e.g. date higher than `2013-06-01' and less than `2013-12-31' to get CANs from
the last six months of 2013). Country related elds were converted from the
ISO code (2 letters, e.g., `FR') to their full names (e.g., `France'). All award
value elds were converted from oats to integers. Finally, award notice IDs
were converted to hyperlinks linking to the CAN page on the o cial TED Web
site [
Finally, we converted the CSV data to the Apache Parquet format [
We make available the code for data preparation and Parquet conversion as
an IPython notebook [
The OpenTED browser is a Web application that provides a user-friendly
access to the CANs. Its main features are a ltering tool for extracting subsets of
CANs, and a visualisation tool that displays the relationships between
contracting authorities and contractors by means of a Sankey diagram. The OpenTED
browser is made available online at [
The ltering tool allows to lter CANs by setting conditions on the content of any of the CAN elds listed in Tables 1 and 3. A snapshot of the tool is given in Fig. 2. All 48 elds can be ltered. The ltering operators provided for a eld depends on the data type of the eld. A summary of available ltering operators for a given data type is provided in Table 2.
Conditions can be combined by logical conjunction and disjunction operators, and may also be nested thanks to the grouping option. An example of ltering is given in Fig. 2. The lter retrieves CANs for which: (i) the contracting authority country is `Belgium', (ii) the CPV code either begins with `301' (O ce machinery, equipment and supplies except computers, printers and furniture) or `302' (Computer equipment and supplies) and (iii) the contract value in euros is more than one million.
Applying the lter returns the set of CANs matching the conditions. In the
example above, 128 CANs are returned, and displayed as a table below the
ltering widget. The table is interactive, and the user can reorder columns by
increasing or decreasing order of values. Award notice IDs are hyperlinks that
connect to the page of the CAN on the o cial TED Web site [
Sankey diagrams visualise the magnitude of ow between nodes in a network. They provide a useful visualisation tool for contract award notices, where contracting authorities and contractors can be seen as nodes of a network, and the contract values as ` ows'.
The set of contracting authorities are represented on the left side of the
network, and the set of contractors on the right side. The thickness of the ow is
proportional to the sum of contract values between contracting authorities and
contractors.
The subjects of procurement contracts are encoded using Common Procurement
Vocabulary (CPV) [
The meaning of CPV codes can be found from the EU Publications o ce [
The Web application was developed using R Shiny [
The lottery game is a third-party Web site aimed at promoting the OpenTED
browser [
The OpenTED browser provides an intuitive online gateway for ltering contract award notices (CANs) of the European procurement system, and for getting insights into the business relationships existing between contracting authorities and entities. We believe that the tool, thanks to its simplicity and ease of use, can be of signi cant interest for a number of users.
Ongoing improvements concern the correction of inconsistencies in the data, related to CPV codes and to the naming of contracting authorities and entities. The nomenclature for CPV codes changed in 2008, which requires to adapt queries to two nomenclatures when searching for CANs before and after 2008. We plan to address this issue shortly. The second type of inconsistency relates to variations in the naming of bidders and buyers. These may be caused by typos, but are also due to di erent ways of naming an entity (e.g., Siemens, Siemens A.G., Siemmens A.G., and so forth). While e orts to provide unique identi ers instead of names are being promoted, name inconsistencies currently remain an important challenge to properly group CANs according to their contracting authorities and entities.
In a larger perspective, a wide range of avenues exist for improving the browsing of TED notices, and for providing better insights into tender data. To name a few, possible extensions concern the integration of contract notices, also recently made available by the Publications O ce as curated Open Data, and make use of advanced analysis techniques such as clustering, graph analysis, or outlier detection, to investigate questions such as what makes a bidder successful, what are the tendering patterns among EU countries, or to identify indicators of fraudulent behaviours.
Acknowledgments. The authors acknowledge the support of \BruFence: Scalable machine learning for automating defense system" (RBC/14 PFS-ICT 5), a project funded by the Institute for the Encouragement of Scienti c Research and Innovation of Brussels (INNOVIRIS, Brussels Region, Belgium), Journalismfund.eu for organising the DataHarvest/European Investigative Journalism Conference, the OpenTED working group (http://ted.openspending.org), the European Union Publications O ce, http://ted.europa.eu, 1998{2016, and the European Commision, Directorate-General for Internal Market, Industry, Entrepreneurship and SMEs (DG-GROW) for providing the data, and Jachym Hercher, Policy O cer at DG-GROW, for providing feedback and improvements on this article.
\National ID" e.g. VAT number for utilities