Data quality and checking

1. Presentation template for adaptation and use in medicine prices and availability survey training workshop for survey personnel Review all slides carefully for applicability to your survey before use! Data quality and checking Medicine prices and availability survey Training workshop Location, dates

2. Many reasons for data problems Data collection: • Human error • Misunderstanding of the instructions/ methodology • Adaptations/improvisations of the survey methodology • Data not checked at every stage Data entry: • Human error • Poor management of workbook versions • Altering/adapting the workbook

3. How to check Workbook data • Double-entry of data into Workbook • Must be done for all sectors • Check Workbook data using “data-checker” function • Automated search for (some) data problems  not the complete solution • Manual check of Workbook data with extra attention to outlying/unusual results • All questionable results must be checked against data collection forms. If uncertainty remains notify survey manager for further follow-up.

4. The Workbook Data Checker function • Automated search for data problems • Checks for unusual or outlying data points • Four data checking actions are conducted: • Checking the consolidation pages • Checking other pages in the workbook i.e. affordability and components • The scope of analysis (number of outlets, medicines etc) • Comparison with a reference workbook (core medicines and standard treatments) • Double entry must be performed before data checker will work

5. How to use the Data Checker function • Go to Home Page and click on Check Data button • Data checker window will show checking functions to be conducted • Parameters for identifying unusual data have been pre-entered; you can use these or modify them • Press Check Data to run the data checker • Data checker will verify all the data in the Workbook according to the parameters specified • A report will identify all data that requires further checking. This data may be correct, but it requires verification.

6. Data checker will identify MPRs of less than 0.5 or greater than 125 as "outliers"

7. Sample results from data checker report The following strange findings were noted. Your data may be correct, but we would like you to go back and check each of these cases and ensure that no errors are present: • There were 2 cases where the minimum price was extremely low compared to others in the row. Could there be a price that was entered incorrectly? Worksheet Private: Cells G53,G61 • There were 5 cases where the spread between the minimum and maximum price ratios was extremely wide. Have all the prices in these rows been entered correctly? Worksheet Public: Cells H25,H75 Worksheet Private: Cells H31,H39,H65

8. Checking data from Data Checker report • In the Workbook all the cells listed in the data checker report will be coloured purple so you can easily identify them • Check all data highlighted by the data checker • If you find an error, change the data and save the Workbook. • Once you have checked all the purple cells you can turn off the colour: • Go to Home Page and click on Check Data button • In the Data checker window, click on ‘Restore formats’ • Click on ‘Close’ to exit theData checker window

9. Manual check of data • Has the identifying information been entered for each medicine outlet ? • Are the unit prices entered correctly - to at least four decimal places, and without currency symbols? • When medicines were found but were provided to patients for free or for a fixed fee, has "F" been entered? • Are there any questionable results (e.g. unit price is 0.05 at all outlets except one, where it is 0.005)?

10. Please remember: • Be polite but not shy about discussing possible problems • Data problems are normal & expected in any workbook • Every survey so far has had problems in terms of data quality or misunderstanding of the methods