ExtracTED

Overview

TED (Tenders Electronic Daily) is the online version of the 'Supplement to the Official Journal' of the EU, dedicated to European public procurement.

TED provides free access to public procurement notices from contracting authorities based in the European Union and in the European Economic Area (also if they provide services in any other country).

TED website currently offers bulk downloads of XML packages dating back to 1993, which can be found on the FTP ftp://ted.europa.eu/ accessible with generic credentials (guest/guest).

This repo contains scrips to extract and parse TED Contract award notices, i.e. the results of the procurement procedure. The scripts have been fully tested to extract all Contract award notices for 2014, 2015 and 2016. Documents are extracted as Python dictionaries which can be saved in a MongoDB database for easy retrieval, or further normalised to be converted in CSVs.

Example of TED Contract award notice

The image below show an example of document as shown on the TED website.

Example of extracted dictionary

An example of extracted data (from same document above):

{
  'DOC_ID': '466898-2016',
  'CODED_DATA': {
    'NOTICE_DATA': {
      'IA_URL_GENERAL': 'www.ekz.ch',
      'ISO_COUNTRY': 'CH',
      'NO_DOC_OJS': '2016/S 253-466898',
      'ORIGINAL_CPV': ['79970000'],
      'REF_NOTICE': ['2016/S 172-310484']
      },
    'CODIF_DATA': {
      'AA_AUTHORITY_TYPE': '4',
      'AC_AWARD_CRIT': 'Z',
      'DS_DATE_DISPATCH': '20161230',
      'MA_MAIN_ACTIVITIES': ['Z'],
      'NC_CONTRACT_NATURE': '4',
      'PR_PROC': '2',
      'RP_REGULATION': '7',
      'TD_DOCUMENT_TYPE': '7',
      'TY_TYPE_BID': '9'
      }
    },
 'CONTRACT': {
   'OTH_NOT': 'NO',
   'CONTRACTING_AUTHORITY': 'Elektrizitätswerke des Kantons Zürich',
   'CONTRACT_OBJECT': {
     'CONCLUSION_FRAMEWORK_AGREEMENT': 'NO',
     'CONTRACTS_DPS': 'NO',
     'CONTRACT_COVERED_GPA': 'YES',
     'CPV_MAIN': '79970000'
     }
   },
   'AWARDS_OF_CONTRACT': [
    {
      'CONTRACTOR': {
         'ADDRESS': 'Mühlebachstraße 52',
         'COUNTRY': 'CH',
         'OFFICIALNAME': 'Linkgroup AG',
         'POSTAL_CODE': '8008',
         'TOWN': 'Zürich'
         },
      'CONTRACT_VALUE': {
        'COST': {
          'CURRENCY': 'CHF',
          'VALUE': 700000.0
          }
        }
    }
   ]
}

Fields

Fields and description of the extracted data

1. DOC_ID section

2. CODED_DATA section

The CODED_DATA section is divided in 2 groups of data.

• NOTICE_DATA contains data related to the notice
• CODIF_DATA contains the META data (codification) related to the notice.

2.1 NOTICE_DATA section

2.1.1 VALUES_LIST sub-section

VALUES_LIST can be composed of the following fields:

Value Data Type

Each value can be single or range value and can have VAT percentage

• When is single:

• When is range:

2.2 CODIF_DATA section

3. CONTRACT section

The CONTRACT section contains the notice itself, in the XML format. In the original XML file this section can be available in different translations, up to 24 when is fully translated. If available, the English translation of the contract is preferred. Otherwise, French and German becomes second and third choice respectively. When any of this translation is not present, the first available is picked.

3.1 OTH_NOT section

3.2 CONTRACTING_AUTHORITY section

3.3 CONTRACT_OBJECT section

Object of the contract.

3.3.1 CONTRACT_VALUE sub-section

Each contract value can be composed of the following fields:

Value Data Type

Fields are same as mentioned above. See Section Value Data Type

3.4 AWARDS_OF_CONTRACT section

List of awards.

Each award can contain the following sub-sections:

• CONTRACTOR: General Information about successful bidder
• CONTRACT_VALUE: Contract value associated with contractor.

3.4.1 CONTRACTOR sub-section

3.4.2 CONTRACT_VALUE sub-section

Fields are same as mentioned above. See Section 3.3.1 CONTRACT_VALUE

How do I use it?

Requirements/Pre-requisites

Python modules required:

• lxml
• voluptuous
• pandas

Optional:

• pymongo

Python version: 3.6 Database: MongoDB, local mode

How to run the project locally

Example of how to convert a document

file_path = '454322_2015.xml'  # Contract Award notice downloaded from tED website

# Extract the raw data
from extractor import extract
raw = extract(file_path)

# Validate the raw data
from validator import validate
data = validate(raw)

# Prune the data: remove empty fields
from validator import prune
prune(data)

Project Structure

Repository structure:

.
├── Lookups
│   └── AA_AUTHORITY_TYPE.csv
│   └── AC_AWARD_CRIT.csv
│   └── CPV.csv
│   └── ISO_COUNTRY.csv
│   └── MA_MAIN_ACTIVITY.csv
│   └── NC_CONTRACT_NATURE.csv
│   └── PR_PROC.csv
│   └── RP_REGULATION.csv
│   └── TD_DOCUMENT_TYPE.csv
│   └── TY_TYPE_BID.csv
├── extractor.py
└── validator.py
└── mongo_import.py

• Lookups: folder containing various lookup files
• extractor.py: script to extract raw data from the contract award notices
• validator.py: scripts to validate the raw data and prune the dictionary (i.e. remove the empty fileds)
• mongo_import.py: script to upload the data in a MongoDB database

Contributors

Alessandra Sozzi, working for the Office for National Statistics Big Data project

LICENSE

Released under the MIT License.