Extract Maltese ARMS Utility Bills (Water+Electricity) to Excel

A Maltese ARMS bill is a bilingual, bimonthly PDF that combines electricity and water on one document. To make the Excel defensible, capture account number, service number, meter number, billing period, per-band kWh and m3 consumption, electricity net and VAT, and water net and exempt VAT as separate columns. If those splits survive extraction, the file can feed Xero, QuickBooks, Sage, or portfolio reconciliation without reopening the source bill.

ARMS - Automated Revenue Management Services Limited - is jointly owned by Enemalta plc and the Water Services Corporation, and it issues one bill for both utilities. Bills run on a bimonthly cycle, can be based on actual or estimated readings, and print a service classification (Residential, Domestic, or Non-Residential Premises Service) that determines the tariff schedule; the classification detail is covered in ARMS Malta tariff classification for rental property. The workflow below treats each ARMS surface - bilingual fields, pro-rata bands, multiple meters, and the electricity/water VAT split - as a schema problem first, then shows how to batch the PDFs, validate the output, and import the resulting file.

Mapping bilingual ARMS labels to Excel columns

The fastest way to disambiguate an ARMS bill is to walk it field by field with both labels in front of you. Most fields print the Maltese label, the English label, or both, and the position on the bill rarely shifts. The list below names each field as it appears, what it contains, and the column to put it in. Capture the column names verbatim — every later section assumes these spellings.

Numru tal-kont / Account Number. The primary account identifier, one per ARMS account. Column: account_number. The same field also appears in headers as the abbreviated form Nru. tal-kont, sometimes shortened further on remittance slips; the value is identical, so a clean extraction maps both surface forms to the same column. The account number is the join key for everything downstream — without it, no two bills for the same account can be reconciled.

Numru tas-servizz / Service Number. A per-property identifier under the account. One ARMS account can carry multiple service numbers when a single landlord or company has several properties on the same ledger. Column: service_number.

Numru tal-mitru / Meter Number. The physical meter the reading comes from. One service can carry several meters (a flat with house and garage meters, a block with a communal stairwell meter, sub-metered units off a master supply). Column: meter_number.

Indirizz tal-proprjetà / Property Address. The billing location for the service. Column: property_address. Useful as a human-readable identifier in reporting where account and service numbers are not memorable.

Konsum tad-dawl (kWh) / Electricity Consumption. The electricity used during the billing period, expressed in kWh. On the ARMS bill this prints as multiple band lines rather than a single figure — typically four or five rows depending on consumption — and each row is a separate slice of the annual tariff structure. The takeaway for the field map is that capturing this as a single number loses the data that matters; the band model and the column layout that preserves it come later in the article.

Konsum tal-ilma (m³) / Water Consumption. Water used during the billing period, in m³. Same multi-line band-split structure as electricity, with bands measured in cubic metres rather than kWh.

Ħlas fiss / Service Charge. A fixed component of the bill, applied independently of consumption. Column: service_charge. This is the standing charge for being connected to the supply rather than for what flowed through it.

Tnaqqis ekoloġiku / Eco-Reduction. A Residential discount that prints as a negative figure on the bill. Column: eco_reduction. Keep the sign so the extracted columns reproduce the bill total.

Kontribuzzjoni ekoloġika / Eco-Contribution. A levy applied across all service classes (Residential, Domestic, Non-Residential). Column: eco_contribution. Always positive.

Tariffa soċjali / Social Tariff. A means-tested adjustment that appears only where the account holder qualifies. Column: social_tariff; keep it on every row even when the value is zero.

VAT 5% (dawl). The VAT line for electricity, applied at the 5% reduced rate. Water is shown alongside as exempt, with a note rather than a VAT figure. Two separate columns capture the split — vat_electricity_5pc and vat_water_exempt — and the on-bill split is the input the column choice has to honour.

Total dovut / Amount Due. The full payable amount for the bill. Column: total_payable.

Two field-level cases warrant a column choice rather than a one-to-one map. Multi-name billing should land as an array of names, not one concatenated string. The Estimat flag belongs in reading_type_actual_or_estimated, because estimated and actual reads behave differently in reconciliation.

Flattening pro-rata band lines into kWh and m³ columns

The Maltese residential electricity tariff is structured as cumulative annual bands. Each consumer gets the lowest rate up to a threshold, then steps up, then up again. According to the regulated residential tariff bands published by REWS, the cumulative bands are the first 2,000 kWh at €0.1047/kWh, the next 4,000 at €0.1298/kWh, the next 4,000 at €0.1607/kWh, the next 10,000 at €0.3420/kWh, and consumption above 20,000 kWh at €0.6076/kWh, all VAT-inclusive.

What ARMS does on each bimonthly bill is slice each annual band into a sixth — one slice per bill across the six bills issued per account per year. The lowest band, 2,000 kWh annually, becomes roughly 333 to 345 kWh per bill (with rounding adjustments across the cycle). The next two 4,000 kWh annual bands become roughly 666 to 690 kWh per bill each. The 10,000 kWh band becomes roughly 1,667 kWh per bill. Each slice prints as its own line on the bill, with the slice's kWh figure, the rate, and the value. A residential consumer running near the average national draw will see four band lines on most bills; a heavy summer or winter draw can push consumption into the higher bands and produce a fifth line.

Water bands work the same way, in m³ rather than kWh, at the rate appropriate to the service classification. The exact thresholds are published by REWS for each tariff class; the structural shape — annual bands sliced bimonthly, each slice on its own line — is identical.

The Excel target that survives this is a flat per-bill row with named columns for every slice. For electricity, capture kWh_band1 through kWh_band5, the matching rate_band1 through rate_band5, and kWh_total as the sum. For water, capture m3_band1 through m3_band4, the matching rates, and m3_total. The band rows from the source PDF map one-to-one onto column groups in the sheet, not onto rows; one bill stays one row, with the per-band detail spread across columns. The rate fields earn their place because REWS revises tariffs periodically, and without the rate columns the same kWh figure becomes ambiguous as soon as the schedule changes — keeping rates inline lets a reconciler recompute each band's value from cold storage years later.

The Estimat flag earns its own column for a different reason. Roughly one bill in five is issued against an estimated reading rather than a meter reading, and ARMS trues up the estimate against the next actual read. From the consumer's side, this means the bill following an estimated bill includes an adjustment that nets the estimate against reality. A reconciler comparing year-on-year consumption who has not flagged the estimated periods will double-count consumption when the trued-up bill arrives — the estimate sat in one period, the correction sits in the next, and the year totals include both. Capturing reading_type_actual_or_estimated per bill keeps the audit trail clean and lets downstream consumption reporting filter or reconcile estimated periods deliberately rather than discovering the distortion months later.

Handling bills with multiple meters and sub-meters

A single ARMS bill can carry readings from more than one physical meter. The cases that show up routinely in a property-management portfolio are a residential property with separate house and garage meters on the same account, a block of flats with a communal stairwell or lift meter that is invoiced to the landlord rather than apportioned to tenants, and sub-metered units where the landlord meters individual flats off a master supply and reconciles tenant charges internally. Each meter prints its own consumption block on the bill — own meter number, own band lines, own consumption total — and the per-meter blocks aggregate into a service-level total, which then aggregates into the bill total. This is the case generic single-meter extractors fail on, because they read the first block and treat the others as duplicates or footnotes.

The layout choice is the decision worth making upfront. There are two layouts, and either is correct depending on what the data is for.

One row per meter-reading (analytical detail layout). Each meter is its own row. meter_number is the natural row key, the per-band kWh or m³ columns sit on the meter row, and the bill-level financial totals (net amounts, VAT, service charge, eco lines, total payable) either replicate on every meter row of the same bill or sit on a parent header row with the meter rows attached as detail. This is the layout for portfolio cost analysis, per-property allocation, consumption trending, and any work where the question is "how did this specific meter behave over time."

One row per bill (header AP-import layout). The bill is one row, carrying the bill-level totals against the account and service identifiers, and meter-level detail attaches as either replicated columns (meter_number_1, meter_number_2, with their own kWh totals) or as a separate detail sheet linked by account and billing period. This is the layout for supplier-invoice import into Xero, QuickBooks, and Sage, where the accounting system expects one supplier invoice per ARMS bill and the meter detail is an internal allocation rather than a downstream record.

The row-per-meter rule is service-agnostic: it applies whether the bill carries two electricity meters, the standard electricity-and-water pairing, or sub-metered units under a master supply. In the Maltese file, meter_number is the per-point key; account_number, service_number, and billing dates tie each meter row back to the ARMS bill.

Preserving the 5% electricity / exempt water VAT split

Electricity supplied to domestic customers in Malta is taxed at 5%, the reduced rate set under the VAT Act for that supply. Water is exempt — not zero-rated, not standard-rated, not reverse-charged. ARMS prints these distinctly on the bill: the electricity VAT line sits beside the electricity net total at the 5% rate, and water carries a note rather than a VAT figure, indicating that no VAT applies to the water supply. The structural split is on the source document; the column choice is whether to carry it through to Excel or collapse it on the way.

The recommended columns are the ones that preserve the split:

• net_electricity — electricity net of VAT for the bill.
• vat_electricity_5pc — VAT on the electricity portion at 5%.
• net_water — water net amount.
• vat_water_exempt — typically zero, but kept as a column so the structural split is explicit on every row.
• service_charge — the fixed Ħlas fiss component.
• eco_contribution — the levy applied across all classes.
• eco_reduction — the Residential discount, captured as a negative value.
• social_tariff — the means-tested adjustment where it applies, zero otherwise.
• total_payable — the Total dovut figure from the bill.

Three downstream failures follow from collapsing the electricity-and-water VAT into a single line. The first is VAT return preparation: the 5% box and the exempt-supplies disclosure on the Maltese VAT return draw on different inputs, and source data that has already merged the two cannot be cleanly mapped back to either. A bookkeeper preparing the return either re-derives the split from the source PDFs (which is what extraction was supposed to avoid) or accepts a return that misclassifies one of the two supplies. The second is the landlord-recharge calculation: when a Maltese landlord re-bills utility costs to a tenant, the VAT treatment of utility recharges to tenants depends on which underlying supply the cost relates to — 5% on electricity, exempt on water, 18% on any admin mark-up, or out-of-scope as a disbursement — and the underlying split has to be intact for the recharge to be defensible. The third is audit traceability: the source bill shows a 5% line and an exempt note; output that shows a single VAT figure no longer reconciles to the source without manual re-derivation, and an auditor sampling the bill will surface the gap.

How the split lands in the accounting system depends on how the landlord is registered for VAT — Article 10 registration, the simplified Article 11 small-undertaking regime, and the Article 12 framework each handle utility recharges and input recovery differently. The split has to be preserved before that question can even be answered. For the regime detail, see Malta's Article 10 vs 11 vs 12 VAT registration regime; the column-level discipline this section names is upstream of the regime choice and applies regardless of which one the landlord sits under.

There is one practical check that catches a collapsed split before it propagates. The captured columns should reproduce the bill total: net_electricity + vat_electricity_5pc + net_water + 0 + service_charge + eco_contribution + eco_reduction (negative) + social_tariff = total_payable. Run the arithmetic across a sample of bills after extraction; if the captured columns do not reconcile to total_payable, a column is missing or has been merged, and the schema needs the addition or the split before the data goes anywhere downstream.

Working around the absence of bulk export and API

The constraint to design around is structural rather than technical. arms.com.mt offers per-bill PDF downloads, but no zip export, batch endpoint, or public API. Avoid portal scraping; build the workflow around getting PDFs into one folder or mailbox, then extracting the stack.

The practical intake routes are simple: download per bill for small portfolios, route e-bill notifications into a shared inbox for scale, scan posted bills to searchable PDFs, and keep all landed files in a month folder before extraction. For scanned paper, test one representative bill first so Maltese labels such as ekoloġiku and Ħlas survive OCR.

AI-powered utility bill data extraction can process the accumulated PDFs as one batch, apply a saved prompt that names the ARMS columns and VAT split, export XLSX/CSV/JSON, and preserve source-file/page references for reconciliation.

A pragmatic Excel schema for ARMS extraction

The columns named across the article consolidate into a single flat-schema reference for AP-style supplier-invoice import — one row per ARMS bill, in this order:

• account_number
• service_number
• property_address
• billing_period_start
• billing_period_end
• reading_type_actual_or_estimated
• kWh_total
• m3_total
• net_electricity
• vat_electricity_5pc
• net_water
• vat_water_exempt
• service_charge
• eco_reduction
• eco_contribution
• social_tariff
• balance_brought_forward
• total_payable
• due_date

The detail schema for analytical work — laid out in the multi-meter section — is an expansion of this rather than a parallel design: the same identifiers, plus meter_number as the natural row key, plus the per-band detail (kWh_band1 through kWh_band5 and rate_band1 through rate_band5 for electricity meters; m3_band1 through m3_band4 and matching rates for water meters), plus a per-meter consumption_total. The flat schema is an aggregation of the detail schema joined on (account_number, billing_period_start, billing_period_end); kWh_total is the sum across electricity meter rows, m3_total is the sum across water meter rows. For portfolios where several owner entities or properties share the same monthly close, that same schema also supports a portfolio-level ARMS bill reconciliation workflow. Running that aggregation and confirming it matches the flat row's bill-level totals is the integrity check that catches a missing column or a merged meter while the source PDFs are still on hand, rather than six months later when an auditor asks why one quarter's electricity costs do not match the underlying bills.

Where ARMS extraction commonly breaks

Validate the extraction before import:

• account_number is populated and does not contain a label such as Nru. tal-kont.
• Band columns are populated, not collapsed into only kWh_total or m3_total.
• vat_electricity_5pc and vat_water_exempt remain separate.
• The meter-row count matches the source bill.
• Extracted columns reproduce total_payable.

Importing the extracted data into Xero, QuickBooks, and Maltese ERPs

The schema is the import contract. In Xero and QuickBooks Online, net_electricity becomes a 5% line, net_water becomes an exempt or zero-rated line according to the chart setup, service and eco lines stay separate, and eco_reduction imports as a negative line. Use service_number or property_address as the tracking dimension for per-property reporting.

Sage and Maltese ERPs usually take the same data by CSV or proprietary mapping. The loader can change, but the reconciliation rule does not: imported lines should reproduce the ARMS total before the full stack is posted.