East Africa Regional Technical Assistance Center
UPLOADING EXCEL WORKBOOK INTO THE PRICE INDEX PROCESSOR: Field Manual #3
Compiled by Mr. Iddi H. Makame
Selected Acronyms
Ccode Country Code found in the SPD table
COICOP Classification of Individual Consumption by Purpose
CNote Product note explaining the product found in the SPD table
Ctitle Title of product based on COICOP found in the SPD table
Digit Level of COICOP detail
ID Product Identification found in the SPD table
IsActive The Variety is Active found in the SPD table
HBS Household Budget Survey
Ocode OECD Product Code found in the SPD table
Otitle OECD Classification Title found in the SPD table
Onote OECD Product note explaining the product
PIPS Price Index Processor
PPS Point of Purchase Survey
SN Serial Number assigned to each product and variety
SPD Structured Product Description
Stitle SPD title for products
tblCPSPD Microsoft Access table used to customize country’s products
tblCountryVCode A Dictionary used to create and define local products
VParent Product defined in Microsoft Access table
VCode Variety Code defined in Microsoft Access table
VDesc Variety Description defined in Microsoft Access table
FORWARD
This report is an outcome of duel effort jointly by the Office of Chief Government Statistician (OCGS), Zanzibar and East AFRITAC to have in place the user’s guide for a compilation system for the Consumer Price Index (CPI). The fundamental features and contents presented in this report aim on strengthening the compilation process of the CPI by using the standard system known as Price Index Processing System (PIPS).
The compilation system is a vital aspect of the production of the CPI aggregates. Experience shows that every country within the region has its own CPI compilation system and mainly uses Microsoft Excel. Using of the PIPS is a functioning step for the CPI harmonization process in the region.
The Office of Chief Government Statistician has worked closely with East AFRITAC for developing a simple user’s guide manual for uploading Excel workbook into the PIPS. The purposes of this user’s manual is to help East Africa regional countries, intended to use the IMF developed CPI System for Price Index Processor (PIPS) for compiling the CPI aggregate, and I am very pleased to introduce the Uploading Excel Workbook into the Price Index Processor: Field Manual, which has been drafted by the Office of Chief Government Statistician, supervised by East AFRITAC Statistics Advisor, Dr. Shelley Winston to help the countries as well as to providing a roadmap for compiling a harmonised CPI.
Indeed, the success of this publication was mainly due to concerted effort and cooperation amongst the individuals from the Price Statistics Unit under the Economic Statistics Section of the OCGS, who have piloted the PIPS for about three years.
Special thanks are extended to the East – AFRITAC Statistics Advisor for remarkable supportive role and immense contribution to the exercise in terms of technical support, advices and encouragement. This contribution is highly appreciated.
Mohammed Hafidh Rajab
Chief Government Statistician
Office of Chief Government Statistician
Zanzibar
INQUIRIES
For further information
about this publication and related statistics, contact the
Chief Government Statistician
Office of Chief Government Statistician
P.O Box 2321
Phone +255 24 22 31 869
Fax + 255 24 22 31 742
Zanzibar.
Table of Contents
Introduction 1
Section I. 2
1.1. Create New CPI Database 2
1.2. Area Description 3
1.3. Outlet Information 4
1.4. Entering Weight 7
Section II. 10
1.5 Appending worksheets 10
1.6 Adding product and variety to each outlet 14
1.7. Entering Price Information 17
Section III. 20
1.8. Reviewing Item Weight Distribution 20
1.9. Checking for Data Consistency 23
2
Introduction
This Field Manual Uploading Excel Workbook into the Price Index Processor tries to address the needs and to provide assistance to countries that have tried or will try to use the Consumer Price Index Price Index Processor System (PIPS). Therefore, this manual aims to provide easy to follow instructions for uploading already created Excel worksheets and databases that are formatted with item’s expenditure shares, CPI outlets, products, varieties, and price information for uploading into the PIPS compilation system.
This manual, hence, is to be used purely as a guide to accompany the Consumer Price Index Compilation System User Guide, (Price Index Processor Version II: Consumer Price Index, Revised Version, August 2009), which provides in-depth details, definitions, and explanations. Additionally, this manual assumes that CPI compilers are already familiar with the PIPS system.
This Field Manual was drafted with screen shots or “Snapshots” taken directly from the PIPS to facilitate ease of understanding. There are three sections with sub-headings that proceed in a logical manner. Section I starts by demonstrating how to create a new CPI database, how to enter the areas covered, how to add outlet information, and then how to enter weights. Section II describes how to append the worksheets created in Section I, how to products and varieties for each outlet, then how to enter price information. Section III concludes with reviewing item weight distribution and how to check for data consistency.
Note East AFRITAC, the IMF or UNECE Statistical Division is not responsible for any errors of omission or commission in this document or in the User Guide.
UPLOADING EXCEL WORKBOOK INTO THE PRICE INDEX PROCESSOR
Section I.
1.1. Create New CPI Database
1. Open PIPS Compiler. On main window, click System Configuration
Snapshot 1
2. Write the name of the new CPI Database in Create New CPI Database field, change the weight date. The default imputation method can be set now or at any time when needed. The elementary formulae can also be set at this time or later. When finish, click Create Access DB to create the database. Then, click Cancel to return to main window.
3. If this message appears, it means that that database being created has already being produced, click Cancel. Click the Set is as Default DB box in order for the system to always open the working database.
1.2. Area Description
4. From main window, click Add New and then Add/Edit Area
Snapshot 2
5. Define Area that will be covered in the CPI. This information will determine how many regions or stratified indexes will be produced. For example, a country may cover urban, rural, lower income, middle income, upper income, etc.
Snapshot 3
Note, comparing the CPI for an area to another area index gives an indication of difference among the areas’ rates of price change. Such comparisons indicate, whether, over time, prices of items that consumers in one area tend to buy have risen more or less rapidly than the prices of items that consumers in another area tend to buy. These comparisons DO NOT indicate whether the average level of prices in an area is higher or lower than the average level in another area.
Click Save then Cancel to return to CPI Main Window.
1.3. Outlet Information
6. Organize the CPI outlets information in a Microsoft Excel worksheet, as shown in Snapshot 4. This worksheet is formatted exactly as the PIPS processor format.
Snapshot 4
7. Once finished, on the PIPS main window; click Add then Batch Edit/Upload to upload the outlets information. An Excel spreadsheet, shown in snapshot 5 will open. The information from the Excel spreadsheet created, snapshot 4, can then be copied and pasted into the PIPS spreadsheet, snapshot 5. Note, the columns include Outlet ID, Outlet Name, Area, Description, Area Compatible, and Outlet Status. Keep in mind that Area refers to urban, rural, etc., depending on how the expenditure weights were determined. Area Compatible refers to the name of the area provided in column D; For example, Unguja is the name of the urban area shown in snapshot 4.
Snapshot 5
8. To save the outlets information into the PIPS compiler, select file menu and then save to database as shown in snapshot 6.
Snapshot 6
9. After saving to CPI Database, on file menu, click exit to return to PIPS Main Menu. A message will appear saving that the outlet information has been saved successfully.
To check the outlet data have been uploaded correctly, click Edit, then the list outlets will appear. If correct, click Cancel.
1.4. Entering Weight
10. In an Excel spreadsheet, organize the CPI weight information, if possible, at level 7 for which expenditure weights were determined. An example is shown in Snapshot 7. Note, Total is equal to Rural plus Urban.
Snapshot 7
11. While on the PIPS main window click or, go to the Utilities menu then select Item Weights Upload Template. Before clicking Generate, make sure level 7 is selected. Snapshot 8 will open.
Snapshot 8
12. Remember, to select level 7, and then click the Generate to create an Excel spreadsheet. Snapshot 9 will appear. From this spreadsheet, delete all the information except for the column headers.
Snapshot 9
13. From the Excel spreadsheet created (step 10) copy and paste the information into the Excel PIPS spreadsheet (step 12, snapshot 9). Make sure all the information entered are in the correct columns.
14. When finish, under the file menu, click Save to CPI database as shown in snapshot 9, then click Exit. On generate excel spreadsheet template for
Weight upload click Cancel to return PIPS Main Menu.
15. At this time, products per outlets have not yet been assigned. Therefore, do not distribute the item weights when prompted in the PIPSVB Box; select NO.
Section II.
1.5 Appending worksheets
16. To calculate the price index, prices are usually entered for each product at the variety level. Therefore, an Excel spreadsheet should have been created that has variety/ies assigned to each product. Varieties are usually found at level 8 of the COICOP hierarchal structure. For example, 11.01.11.1_01 is the level 7 for Rice, and 11.01.11.1_01a, level 8 is one type of variety for rice (See snapshot 10).
Snapshot 10
17. Therefore, create a spreadsheet in Excel similar to the information shown in the Access table in Snapshot 11. The columns should named: VCode (the variety code), VParent (the product), VDesc (the variety description), and IsActive (the variety is active).
Snapshot 11
18. Remember, “IsActive” means that price information for this item variety is available. Save the spreadsheet as CountryVariety.
Snapshot 12
19. Next, use Microsoft Access to import “CountryVariety” from the created Excel sheet. Therefore, open the CPI Access Database, which was the database created in Snapshot 1 (Default Database File Location, and Default CPI Database, i.e., CPI.mdb). On file menu, click Get External Data, then import.
Snapshot 13
Snapshot 14
20. From the import dialog box, in the files of type, select Microsoft Excel (*.xls). Select CountryVariety.xls that should have been created and saved, Snapshot 14, then click import.
21. Append the worksheet in the existing “tblCountryVCode.”
Snapshot 15
22. When finish, close Microsoft Access.
1.6 Adding product and variety to each outlet
23. The next step is to define in each outlet, the product/s and varieties. Note, there is no easy way to import the varieties for each the outlet, since the system is building relationships and integrity. That is, a Serial Number (SN) is being assigned to each product and variety.
Click on Main PIPS window, select the outlet of interest, right click the outlet, and then click Edit Product.
Snapshot 16
Snapshot 17
24. Click Add Product, and then click the icon to select the product. At this step, the COICOP Product Code Table will open, next select the product for which there is at least a variety to collect price.
Snapshot 18
25. Snapshot 19 shows how to select the varieties. Recall, the varieties were appended to the products, Snapshot 15. Therefore, the varieties are associated with the products.
Snapshot 19
26. Once the products and varieties have been are entered, click Cancel to select another outlet. Click Cancel to return to PIPS main menu.
1.7. Entering Price Information
27. At this point, all the outlets with their products and varieties should have been entered. Recall also, weights for each product were also entered, Snapshot 7, paragraph 10. At this stage, the weights should be distributed for each outlet, product, and then variety. More importantly, the price information (base price, previous price, and current price) for the varieties should also be entered.
28. Before entering price information into the PIPS, make sure Excel spreadsheet with price information is organized according to at least, Outlet ID, Outlet Name, Variety Code, Description, base price, previous price, and current price etc (See snapshot 20). Columns, B, E and G are very important.
Snapshot 20
29. Next, to enter the price information into the system, go to Utilities in the PIPS and select Create Excel for Update.
Snapshot 21
30. Click Select All to upload all the outlets, then click Create Excel Sheet. Click on the icon to open the Excel sheet. In the Excel menu, go to tools, then protection, then Unprotect Sheet. To unprotect the sheet, use password 123.
Snapshot 22
31. Once the sheet is unprotected, go to Data from the Excel toolbar, and filter the data. Filter column D (IsProd) False. All the varieties with their prices for each outlet should be listed. Copy from the Excel spreadsheet already created (snapshot 22) into the PIPS Excel sheet, only the base, previous and current period prices. Make certain the correct information was copied for each outlet and variety.
32. After entering the price information, click Save Back to CPI Database, then exit.
33. At this point, all the information necessary to compile the CPI should have been entered: areas, products and their weights, outlets, varieties, and prices.
Section III.
1.8. Reviewing Item Weight Distribution
34. At this time, products per outlets have been assigned and the system needs distribute the item weights. Hence, Click Utilities in the PIPS menu, then Global Distribute Item Weights.