How-To’s

How To Make Database of Governments Officials Limos

Bosnian institutions and public companies spend around 10 million euros on cars a year! While most of that spending is for cars needed for public service, great amount and most expensive are government officials cars. 

In 2018 alone over 10.6 million KM – 5 million euros – was spent on purchasing 329 official limousines whose price averaged 32,000 KM, about 16,000 euros. In total, tenders to procure 1,666 official vehicles, worth about 46 million euros, were recorded in 2018 by Balkan Investigative Reporting Network in Bosnia and Herzegovina.

BIRN BiH database proved for the first time a few talking points of Bosnian public: politicians enjoy overpriced luxury limos like no others in Europe; most of the tenders have only one bidder indicating corruption; and also there is serious lack of control of budget spending on cars.

While this was speculated in media, experts discussions and in public, evidence was partial for complete analysis and complete conclusions. BIRN Bosnia and Herzegovina itself published dozens of articles of specific cases realizing in that process two important things: there are far too many examples of overspending and stories soon begin to repeat and look the same for audience lowering their impact. By late 2017 we were already collecting al tenders related to cars from public procurement website, centralized government portal where institutions and public companies are obliged to publish their tenders. 

BIRN published an analysis in December showing that around five million euros was spent on vehicles in 2017.

After BIRN’s reports about violations of public procurement practices, several institutions amended their tender specifications.

We then decided it would be more effective to make database with every tender related to cars.

It took us around six months to work with IT company to develop database structure and to manually input hundreds of tenders for car purchases and to input data on more than 3,000 cars into our car registry – where we publish data on existing cars owned by institutions.

In mid 2018 we published database and data for first half of same year with press conference. When we wrapped a database for whole year, final figure of more than 93 million Bosnian marks or more than 46 million euros of total tenders for car purchases in 2018 was a surprise even we saw that figure will be huge while we were working on the database.

It earned us a republication in almost every significant media in the country. We had enough data to be able to say there is no competition in tenders to buy cars for officials as vast majority of tenders had only one bidder and we could identify who bought most expensive cars and how they did it but also we knew preferences of models and brands for politicians.

We had data for tenders for more than 1,600 cars that year and data on how almost 1,000 cars were bought as not all tenders were finished by the end of 2018.

The database also contains a register for vehicles already owned by institutions and public companies, which shows that the average cost of a vehicle is around 25,000 euros.

Other important part of database was car registry as we now have more than 3.500 cars from numerous government institutions. Its unique database in Bosnia as no official data is available on which institutions owns how many cars and which are they and how much is their worth.

How we collect and input data

We are monitoring every out of tens of thousands tenders in Bosnia and Herzegovina and we do it every day, downloading hundreds of documents. Our source is official public procurement portal and we only use data available on this portal. Each tender contains two main parts, one document containing basic data on who is buying what and document containing detailed information on car details. While first document has template and could be used for automated input, second one is different almost every time making it hard to scrape data from portal directly to the database. In some cases, institutions use scanned document making it harder to copy data. In these cases we have to retype data containing details on car dimensions, type of engine required or what kind of display it needs to have.

As we use only official data which is in most part publicly available, we only fact check our input after we input each tender but also at the end of the year before we publish final report.

As we collect data for car purchase we found a way to make first officials car registry in Bosnia and Herzegovina. It would take years to use Freedom of information request to get data for all cars owned by every institution in the country as there are tens of thousand of cars owned by the different level of government. What we found is that all of them are obliged to use tenders to buy insurance or to repair cars. We are using tender documents as they contain lists of cars.

Tenders for insurance are more useful than ones for maintenance as they contain more data, in most of cases they have to contain data on year of purchase and price of the car.

Sometimes they also list license plates which we used when we tracked who is using budget car for traveling to election rally. 

We use custom made back end of database website for input. There are few main input entities – institutions including couple of sub-entities such as level or type of government (containing basic information on them and allowing different kind of search results); companies (basic data on companies that are bidding for tenders); car registry part where we insert each car as different entity in SQL database; expanses (each expanse tender in instead separately and linked by database to different modules which allows complex search results); cities; and most important part, tenders, which has input module containing several parts with different ways of input thru text boxes and drop down menu’s.

Database structure

Public procurement portal is far from user friendly for general audience and it does not have an option to list only tenders related to cars. We had to replicate parts of portal’s database.

Database on cars is custom made SQL database separated in three main parts – 1) data on purchasing cars; 2) car registry containing data on cars owned by institutions; 3) part where we register expenses on cars such as gas and maintenance costs.

Database is live and interactive as visitors without IT knowledge can surf thru the database and search by numbers of criteria. Its structure is designed to be user friendly and to show data as simple as possible but also to connect the data by different criteria. 

1) Data on car purchases contain several most important information on every car purchase for government institution or public company from 2018 to the date. We are manually inserting data on institutions, type of tender, its current status, number of cars purchased and their information such as which type of cars; what gas they are using and even if buyer instated on heated seats or other luxury item requested.

What is significant for BIRN Bosnia and Herzegovina database is that its showing data as they are happening so we can publish if there is something wrong with the tender. It resulted in canceling couple of tenders or changing requirements as they indicated which brand is request, criteria not allowed by Bosnian law on tenders. This live database is allowing visitors, journalists and researchers to track tenders as they are happening. 

We are also red flagging tenders, around 100 of them in 2018 by criteria made by years of state audits related to cars or Law on public procurement. 

2) Car registry contains thousands of cars and their details. It is possible to how many cars Presidency has and what is their worth. As database front end is interconnected and interactive it is possible to list all Mercedeses for example or who owns most expensive car.

Visitors can check which car their local major drives or to see how often he changes his car.

3) Third segment of database are expanses. As this is by far the biggest database set available on public procurement portal we only insert data on institutions which are already inserted in our database. We plan to automate this part by scraping data in the future.

Visualization

We tried to make front end as simple as possible and to highlight most important parts of database with modules showing latest tenders or most expensive ones, using graphs to show expanses or which company won most tenders. As much effort we put into building database structure that would allow to have complex search results we also put into visualizing hard work we put into maintaining this database.

Each tender has document linked to it making it available to others to use, a feature which made this kind of documents transparent to the general audience.

Challenges

As BIRN’s database improved transparency on car tenders procedures it faced major backlog by procurement agency. Different to other countries in the region, in Bosnia and Herzegovina, tender documentation, a document or more of them containing most important information, is not available to the public as the rest of procurement portal, but only to registered users, companies interested in tenders. 

When we started to monitor public procurement it was possible to register as user as an NGO but after we published our most important stories, our account was disabled in broader effort to ban NGO’s to bid for tenders. Even if BIRN’s account was only used to monitor tenders and even if we never big for any tender or ever wanted to, our account was also suspended with the rest. 

It made impossible to continue with the database update but also disabled notification about changes to tenders we were already monitoring so we had to manually go back tender to tender to see if they were finished and who won the tender. It took us a few week to find a solution and register as private company in order to continue database update.

News stories and impact

BIRN’s car database was quoted in every major news outlet in the country. What it enabled us is to be quoted even if we don’t do story ourselves. Journalists find different story in database by themselves and publish it. 

Other times it is enough to tweet about interesting fact or a tender from the database for other news outlets to write the story. 

As database is searchable by keywords, we once used this search to see how many tenders requested official limo to have ISOFIX, part of the seat which enables mounting child seat. It is feature that should not be listed as requirements when buying official car but it turns out six of tenders had that requirement in 2018. After we tweeted that on database separate twitter account, media made a story out of it.

Database is regularly quoted in most of news stories related to government cars.

It also made an impact on number of tenders as our live monitoring made tender to be changed or canceled after we revealed information. 

Story on public spending on cars in 2018 was one of the story in series nominated for EU Journalism Awards which won BIRN’s journalist Second Prize for best investigative stories for 2018. 

“The significance of (…) investigative series is that it documents the misuse of public procurement through publicly available information,” the jury noted.