Cameroon Budget Inquirer

This site has been built with the data from councils and regions of Cameroon, but there is still work to do to complete all local councils across the country. And you can help! Whether you are a civil servant or a citizen, you can publish data from your locality. We explain below how to.

Publish and explore council budgets data on Cameroon Budget Inquirer

This guide explains step by step how to prepare and publish budget data to be able to explore them on Cameroon Budget Inquirer.

For an introduction to OpenSpending, please refer to the OpenSpending guide

Introduction

Councils budgets in Cameroon are mainly available from municipalities in paper format. ASSOAL association and its partners have undertaken to collect and digitize these budgets, making it possible to share and reuse data online.

We explain here how to publish these data on the OpenSpending website in a format that will also allow everyone to explore the data on the Cameroon Budget Inquirer website.

These are the main steps to publish budget data on OpenSpending:

1. Data validation
2. Data cleaning
3. Data sharing on DataHub.io
4. Data publication on OpenSpending.org
5. Data visualisation and exploration on Cameroon.OpenSpending.org

To start, let's have a look to the model of the dataset we use for budget council data

OpenSpending model for Budget Council Dataset

The Cameroon Budget Inquirer uses a predefined dataset model. You must comply with this model in order to be able to publish and visualize your data on Budget Cameroon Inquirer website. This means matching fields and format of columns from the original dataset to the dataset of destination (used by OpenSpending).

For budget council data, the dataset must have the following fields:

Titre	Description	Format	Possible value	Notes
Head-account	Numerical value for the head account	Three digits number without separator		Ex: 700
Head-account description	Description of head account	Text
Sub-account	Numerical value for the sub account	Six digits number including head-account and point as separator		700.101
Sub-account description	Description of sub account	Text
Amount	Investiment amount in FCFA	Number with decimal separator (point)		Ex : 1000000.0
Year	Year of budget	Date: YYYY
Division type	Type of administrative division (Region or Council)	Text	COUNCIL REGION	Mandatory for visualization
Region	Name of the Region	Text	Extrême-Nord Nord Adamaoua Est Sud Centre Ouest Nord-Ouest Sud-Ouest Littoral	Mandatory for visualization
Council	Council name	Text		Ex: Yaounde1
Expense_type	Expense type description	Text
Revenue/Expenditure	Type or budget row (revenue or expenditure)	Text	REVENUE EXPENDITURE
Recurrent/Investment	Type of spending (recurrent or investment)	Text	RECURRENT INVESTMENT
Reporting type	Budget status (planned or executed)	Text	BUDGET ACTUAL	ACTUAL = Executed

See example with Douala 5 council budget data for fiscal year 2010

1. Validation of data collected from paper version

You need to make sure that the data entered in digital format (Excel or CSV) includes all information from the paper version.

Let's see with a paper extract from a budget council in Cameroon (see picture below) and check information by information what they correspond to in the OpenSpending model (see following table):

Paper version	Description	OpenSpending version
[Council Name]	Not available on the picture but appearing on the cover of the budget book. For this one, it is Douala 5 council.	Council
Recettes / Revenue	A budget plans expenditures and revenue. Here we have data on revenue.	Revenue/Expenditure
Exercice 2010	Adopted fical year. Here we have budget data for fiscal year 2010.	Year (except for columns refering to previous budget's year, see below)
Title	Empty	Do not report
Head, subhead, paragraph	Number that identifies the budget rows (account, subaccount) in the budget classification. For instance 7.12 identifies the account "Direct council taxe". 712.100 identifies the subaccount "Proceeds of water tax".	Head-account or Sub-account
Nature of revenue	Text description of the accoun or subaccount	Head-account description or Sub-account description
Executed revenue reported in the previous administrative account	Executed budget for the year N-2 of the written Fiscal year. Therefore, here we have executed budget for yeat 2008.	Amount (for Year = N-2 and Reporting type = ACTUAL)
Revenue estimated for the previous fiscal year.	Estimated revenue of the year N-1 of the the fiscal year. Here, we have estimated revenue for year 2009.	Amount (for Year = N-1 and Reporting type = BUDGET)
Revenue estimated voted by the municipal council.	Revenue estimated the current fiscal year (2010).	Amount (for Year N and Reporting type = BUDGET)
Revenue estimated approved by the supervisory authority.	Revenue approved by a superior authority (Department or Region).	If exist, replaceAmount (for Year = N and Reporting type = BUDGET)
Observations	Observation on the budget row	If exist Expense_type

2. Data cleaning

When you manually enter or extract data from a PDF file, your resulting Excel or CSV file often contains conversion or seizure errors such as misplaced or splitted cells, etc. It is important to correct these errors before proceeding any further.

Your data set also contains recurring terms such as the names of projects. You must ensure that these references are all written in the same way. This will facilitate subsequent data analysis.

Finally, to publish a dataset on OpenSpending, you must ensure the quality of the data, like avoiding empty cells, and convert the dates and amounts in the correct format.

All these steps are part of the data cleaning process. Here, we use two main tools, a spreadsheet application such as LibreOffice Calc or Microsoft Excel and Open Refine, a tool to clean data.

Open Refine is a powerful tool to quickly clean your dataset. We will use Refine mainly for its "Cluster" feature that helps you to identify the different cells that could represent the same reference, and then allows you to assign a unique term to all these cells.

We use Excel or LibreOffice Calc for all other operations: aggregate divided cells; convert amounts in the correct currency; fill empty cells with a default value

Here are the tasks you may have to execute:

• Assign a default value to empty cells where value is required (for example, add "0" in the column amount if the cell is empty, make sure that all cells head-account and sub-account do have a number)
• Concatenate values ​​splited into multiple cells
• Assign a unique value to the same references in the whole dataset (use standards if appropriate. For instance with names of communes use Yaounde6 but not Yaoundé 6th)
• Convert amounts in the right currency if necessary (here FCFA)
• Convert dates into Open Spending format (YYYY, ex: 2010)
• Save your file as a CSV with comma separated values

Tips and tricks for Excel

Tips and tricks for LibreOffice or OpenOffice are quite similars.

Remove line breaks in your data set:

Option Search and Replace (CTRL H) -> search "\n" replace all by " " (white space);

Avoid Excel replacing automatically comma separator with a point separator or add a separator:

Right click on the column (top), select option Format and then select Text as format (not number).

Transform 710100 into 710.100

Create a new column on the right of your columnSub-account which should be column C. Add title Sub-account to the new column.

In the first following cell of the new colum add the following function:
For Excel in english: =CONCATENATE(LEFT(C2,3),".",RIGHT(C2,3))
For Excel in french: =CONCATENER(GAUCHE(C2,3),".",DROITE(C2,3))

Then right click in the bottom-left corner of the cell to apply the function to all cells in the column.

Copy the column. Then right click on the header cell, option special paste and uncheck formula. Click on OK. Remove the unusefull column Sub-account on the left.

Finally format your column to Text format to avoid Excel to consider it as a column with numbers and add separators.

3. Data sharing on DataHub.io

In order to be able to publish you data on OpenSpending you need to share you data online (your dataset must have its own URL). We will use http://datahub.io a dataset management and publication tool.

We choose to organize our datasets first by councils and then by years on datahub.

Here is the publication process:

1. Register on datahub.io


2. Join the group OpenSpending Cameroun. You need to send a request to [email protected] and indicate your datahub user name.


3. Once added to the group, go to the group page and click on “Add Dataset”



4. Describe your dataset by setting a title (Budget_[CouncilName]), a description, the type of license (open) and keywords.


5. Modify visibility to make your data “public” et then click on “Next: Add Data”.


6. Once on the page ”Add data”, import CSV file from your computer by clicking on “Upload a file”. Indicate the name of the ressource (Budget_[CouncilName]_[Year]), add a description and specify the format (CSV).
   



7. Click on “Save and add another”.


8. Your dataset is now downloadable and viewable online. Repeat the procedure for all datasets available.
   

4. Data publication on OpenSpending.org

Once your dataset is available online, you just have to add it to the budget council dataset on openspending.org website.

To do so, send a request to [email protected] with the link of your dataset. The admin will check and add you data on OpenSpending.

If you want to learn more on how to use OpenSpending go to the OpenSpending guide.

5. Data visualization and exploration

Once you have received confirmation of the publication of your data on OpenSpending.org website then you can explore and visualize the data directly on this website.

Well done. It's now time to communicate your work! Use social media, publish an article on your Blog and contact the press.