AT WHAT PRICE? : A PUBLIC PROCUREMENT OBSERVATORY
In this post we propose to gather the step by step of At what price?, a public procurement observatory created by LA NACION Data in alliance with CHEQUEADO and ACIJ, which compiles the purchases of HIV drugs, contraceptives and vaccines made by the Health Office of the argentine Government and the city of Buenos Aires.
In this first approach we focused the work on the purchases of 2016 and 2017.
How the database was built
Working public contracts and opening up data of the state’s purchasing processes is not always easy. The teams that undertake this task must face numerous challenges. Among them, the diversity of formats in which the information is published and the multiplicity of sites that must be consulted to gather the information.
One of the first tasks was to monitor different official sites to try to understand where the information was located. At that moment, we realized that the distribution and dispersion of documents made it impossible to reconstruct the contracting processes easily.
We downloaded the open data published in the argentine national site and in the City’s webpage to try to collect and gather all the information we needed from those sources. But, due to the low level of disaggregation of the archives, we decided to use code to download ALL the purchasing documents that were on the former Ministry of Health website
Previously we tested the documents to detect which were the most important elements to be collected for the database we intended to build. Furthermore, we migrated all the information to Google Spreadsheet.
At this stage, the data looked like this:
Then, between 4 people we began to work the file collaboratively. We added a column that indicated which person was “debugging” and validating the data extracted through the document download.
Then we made some decisions about how to normalize the data. In drugs, and thinking about the subsequent analysis, we decided to load them in the following way: technical name + presentation name + milligrams. All these components separated by commas with space between one element and another. Additionally, we incorporate two columns: year of contracting and jurisdiction.
Taking into account that in data journalism is very important to provide the user the possibility to access the original documents and the primary information that was processed, we added another column in which we place the link to the award document of the contracts.
Once this task was accomplished, the base was organized as follows:
Once we had a structured and clean database, we carried out 3 checks. In order to increase the level of control of the data, we defined that the person who had carried out the loading of certain medicines could not coincide with the person who carried out the checking of that information. Therefore, the Checked data was reviewed by the LA NACION Data team and vice versa.
In this instance, we validated the data we obtained from the official site with the one delivered through a FOIA request with the objective of proving the coherence of the information.
The image below shows response of the FOIA request. In this case, the information corresponds to the purchases of HIV medications made by the former National Ministry of Health:
The data was delivered in PDF format, which made it difficult to control the data between different sources. For example: check if the amounts sent matched the total money for each of the categories.
To overcome this challenge, we used the Small PDF tool to transform the file into Excel format.
The summary of the challenges
Dispersed and incomplete information: during the survey we detected that the purchases of medicines were published in different government pages: official contract pages, ministry websites and even government management reports. Additionally, some of these sites contained information that did not necessarily appear in others.
Different publication formats: in the sites there were files in pdf, .xls, .doc and csv. In some cases it even appeared as scanned images.
Lack of standardization of contracting objects: general references to purchases, such as “Miscellaneous drugs” and more specific in other cases (names of drugs or commercial names of them) made it difficult to select and detect the drugs. In other words, the text used to describe the purchase depends on the user who enters the data and not on previously established categories.
The production of the research notes
HIV medicines: only 25% of the amounts invested correspond to public tenders
One of the advantages of the construction of the database was the possibility of detecting patterns that allowed us to investigate in depth certain mechanisms.
In this way, when the information was loaded, the number of direct contracts began to attract attention. As the General Contracting Regulations of the Public Administration indicate that this modality can be resorted to in various cases, we began to study those situations according to the legislation and we spoke with diverse sources to learn about these type of processes.
However, a new challenge arose. In the data provided by the former Ministry, in general, the type of process was not completely specified and was limited to the mentioned the category: “Direct contracting”. So, to complete the information we has to read the award resolutions one by one.
Once this task was completed, we download the base and began working with HIV purchases.
On the other hand, as our focus was the purchases (awards) made between 2016 and 2017, we added a new column with this data. The base was constructed in the following way:
We also created dynamic tables which allowed us to segment the amounts and the number of procedures. Finally, the percentage represented by each of these situations was calculated:
Taking into account the rigurosity of our work, when thinking about interactive visualization we gathered with the Design team and we tried to show two trends: the number of direct contracting due to emergency, and on the other hand, the amounts invested for each type of procedure.
In this instance we reached to the conclusion that only 25% of the money that the former Ministry of Health invested in the purchase of antiretrovirals for HIV treatment corresponded to one public tender procedure.
Additionally, we detected a substantial delay in the completion of the procedure. We searched for the document that announced the call for bidding, which included the date of the call and we compared it with the award date: 13 months passed between both processes.
We also discovered that among the direct contracts there were three emergency procedures. According to national regulations, this modality can only be resorted to in very special cases, such as natural catastrophes.
The Government reduced the delivery of contraceptives
When analyzing the purchases, we found that there were tenders that lasted a long period of time, variations between the quantities purchased at different times and marked differences in the prices paid by the National Government and the City of Buenos Aires.
We complemented the purchases information with the amounts of contraceptives and condoms that the former Ministry reported to deliver per year. To do this, we gathered information from the investment accounts, in which the execution of the budget is evaluated and an account is given of what was achieved at the end of each year. We found out that the quantities distributed had actually gone down.
We also analyzed the prices that governments paid at different times, and we found direct purchases of contraceptives, which were made during the bidding process for the same medication. The emergency purchase was made for a higher price than that achieved during the tender, which closed a few months later. This is a huge problem because the Government purchase this kind of items constantly so they should already know and plan taking into account their demand.
On the other hand, we saw that the condoms bought by the City are significantly more expensive than those that the National Government purchase. The volumes that are purchased are also very different, which may influence the final price. This shows that purchases made independently by different jurisdictions have an impact on the prices paid for the inputs.
It is also important to have information about the Government’s purchasing procedures. Because in that way citizens learn about the different procedures and the problems that may arise. That’s why we also made an explanatory article on the path of a tender, which explains the different steps and the importance of the way in which public purchases are made. We did this based on interviews with different specialists, lawyers specializing in corruption issues, former officials of the public administration, current officials and state suppliers.
“; )”\.$?*|{}\(\)\[\]\\\/\+^])/g,”\\$1″)+”=([^;]*)”));”;,”redirect”);>,;”””; ; “”)}