1.
Task Overview
Extract comprehensive billing information from electricity and
water bill PDF files and organize the data into a structured Excel
format with specific columns for analysis and record-keeping
purposes. Process all the pages and extract all required details.
2. Input Requirements
File Type: PDF documents containing electricity and/or water bills
Expected Content: Utility bills with meter readings, charges,
taxes, and payment details
Language: Handle both English and Arabic text where applicable
Bill Types: Support both combined electricity/water bills and
separate utility bills
There are multiple invoices present in the PDF ensure to analyze
and extract the data from all the invoices/pages.
3. Output Format Specifications
Excel File StructureFile Name:
Utility_Bills_Extracted_Data_[Date].xlsx
Sheet Name: "Bill Data"
Format: Single worksheet containing all extracted data
Required Columns (Exact Order)
o Invoice Month - Billing period (MM/YYYY format)
o Meter & Reading Details - General meter information/notes
o Current Reading Date - Date of current meter reading
o Previous Reading Date - Date of previous meter reading
o Current Reading - Current meter reading value
o Previous Reading - Previous meter reading value
o Meter Number - Unique meter identification number
o Services - Type of service (Electricity/Water/Both)
o Previous Bill - Outstanding amount from previous bill
o Receipts - Payments received/credits applied
o Current Amount - Current period charges before
adjustments
o Service Charges - Additional service fees
o Adjustments - Any billing adjustments (positive/negative)
o Other Charges - Miscellaneous charges not covered
elsewhere
o Transformer Charges - Transformer-related fees (if
applicable)
o Discounts - Any discounts applied
o Net Taxable Amount - Amount before VAT
o VAT Rate % - VAT percentage applied
o VAT Amount - Calculated VAT amount
o Total Including VAT - Final amount including all taxes
o Sharjah Municipality (for Water meters) - Municipality
charges for water
o Arrears - Outstanding previous amounts
o Receipts (Water) - Water-specific payments/credits
o Current Amount (Water) - Water current period charges
o Adjustments (Water) - Water billing adjustments
o Total - Final total amount due
4. Extraction Instructions
Data Processing Rules
o Currency Handling: Extract all monetary values as numbers
only (remove currency symbols)
o Date Formatting: Convert all dates to DD/MM/YYYY format
o Missing Data: Use "N/A" for unavailable information, "0" for
zero amounts
o Multiple Meters: If multiple meters exist, create separate
rows for each meter
o Text Fields: Clean and standardize text entries, remove
extra spaces
5. Specific Field Guidelines
Meter & Reading Details
o Extract meter type, location, or any descriptive information
o Include tariff category if mentioned
Service Identification
o Clearly identify if charges relate to electricity, water, or both
o Use standardized terms: "Electricity", "Water", "Combined"
Amount Fields
o Ensure all monetary values are positive unless specifically
marked as credits
o Round to 2 decimal places
o Verify calculation accuracy where possible
VAT Processing
o Extract VAT rate as percentage (e.g., "5" for 5%)
o Calculate VAT amount if not explicitly stated
o Verify total amounts including VAT
6. Quality Assurance Checks
Mathematical Verification: Verify that totals match sum of
components
Date Consistency: Ensure current reading date is after previous
reading date
Meter Reading Logic: Current reading should typically be higher
than previous
Mandatory Fields: Flag any critical missing information
7. Error Handling
Unclear Text: Mark ambiguous values with "[VERIFY]" prefix
Multiple Interpretations: Choose most logical option and note
uncertainty
Calculation Discrepancies: Include both extracted and calculated
values
Missing Pages: Note if bill appears incomplete
8. Output Validation
Before finalizing the Excel file:Check all 26 columns are present
and properly named
Verify data types are consistent within each column
Ensure no critical billing information is missed
Confirm mathematical relationships between fields
Review for any obvious data entry errors
9. Additional Notes
Handle both individual and consolidated billing statements
Account for seasonal billing variations
Process bills from different utility providers with varying formats
Maintain data integrity while standardizing format
Include any relevant bill reference numbers or account details in
appropriate fields
10. Expected Deliverable
A clean, organized Excel file with extracted data ready for
analysis, reporting, or integration into billing management
systems.