Track Invoice Payments in Excel Using ClickPesa Control Numbers

Managing invoice payments manually can be overwhelming, especially when working from spreadsheets. If you’re sending out multiple invoices and waiting on mobile money or bank transfers, matching payments to customers becomes time-consuming and error-prone.

ClickPesa’s Order BillPay Control Number system makes it possible to generate unique payment references per invoice and automatically track payments, even from a simple Excel sheet.

ClickPesa’s Order BillPay Control Number system makes it possible to generate unique payment references per invoice and automatically track payments, even from a simple Excel sheet.

This guide walks through how to:

• Create Order BillPay Control Numbers in Excel
• Share them with customers
• Automatically update the Excel sheet when payments are received

---

The Problem

Imagine you have a list of 50 unpaid invoices in an Excel file. For each invoice, you want to:

• Assign a unique, trackable payment reference
• Let customers pay via mobile money or bank
• Automatically see which invoice has been paid (or partially paid)
• Avoid manually checking statements or guessing who paid what

This is exactly what Order BillPay Control Numbers solve.

---

What Is an Order BillPay Control Number?

An Order BillPay Control Number is a dynamic number used by customers when making a payment. It is generated by combining:

1. Your Merchant BillPay-Namba (a 4-digit number assigned to you by ClickPesa)
2. Your Order Reference (e.g., the invoice number from your Excel sheet)

Formula:

Order BillPay Control Number = [BillPay-Namba] + [Order Reference]

Example:

• BillPay-Namba: 1122
• Invoice Number: INV0045
• Control Number: 1122INV0045

This control number is shared with the customer, who uses it when paying via mobile money or bank.

---

Step-by-Step: Tracking Invoice Payments in Excel

Step 1: Prepare Your Excel Sheet

Start with a basic sheet containing your invoice data. Include the following columns:

Invoice Number	Customer Name	Amount Due	Control Number	Payment Status	Amount Paid
INV001	Jane Doe	150,000 TZS	1122INV001	Unpaid	0
INV002	John Smith	200,000 TZS	1122INV002	Unpaid	0

• Column A: Invoice number
• Column D: Concatenate your BillPay-Namba with the invoice number
  • Formula: ="1122"&A2
• Columns E and F will be automatically updated when payments are received

---

Step 2: Share the Control Numbers with Customers

You can now send payment instructions to each customer using their unique control number. This can be done via:

• Email
• PDF invoice
• SMS or WhatsApp message

Make sure to clearly display the Order BillPay Control Number and inform customers that they can pay using:

• M-Pesa
• Airtel Money
• Tigopesa
• Halopesa
• CRDB Bank (App, USSD, Wakalas, Internet Banking)

---

Step 3: Set Up Webhook Integration with Power Automate

ClickPesa sends a webhook whenever a payment is made using a BillPay Control Number. To automatically update your Excel sheet, you’ll use Power Automate, Microsoft’s no-code automation tool.

Why Power Automate?

• Excel (VBA or Office Scripts) can’t receive incoming webhooks
• Power Automate can create an HTTP endpoint and update Excel Online

How It Works:

1. Create a new Power Automate flow with the trigger “When an HTTP request is received”
2. Paste the following JSON schema for the request body:

{
  "type": "object",
  "properties": {
    "orderReference": { "type": "string" },
    "controlNumber": { "type": "string" },
    "amountPaid": { "type": "number" },
    "status": { "type": "string" }
  }
}

3. Add Excel Online (Business) action: “List rows present in a table”
   • File: Choose your invoice tracker Excel file on OneDrive or SharePoint
   • Table: Ensure your Excel file has a table defined
4. Add a “Filter array” step to match the row where Invoice Number equals orderReference
5. Add “Update a row” action to:
   • Set Payment Status to “Paid” or “Partially Paid” based on amountPaid
   • Set Amount Paid to the received value
6. Save the flow. Power Automate gives you a URL. Paste this URL into ClickPesa as your webhook endpoint.

---

Bonus: Add Conditional Formatting

In Excel, use conditional formatting to visually mark:

• Green: Paid
• Yellow: Partially Paid
• Red: Unpaid

This gives you a fast visual overview of the invoice status.

---

Summary Workflow

1. List invoices in Excel
2. Generate control numbers using your BillPay-Namba + invoice number
3. Share the numbers with customers
4. Set up a Power Automate webhook to update Excel
5. Automatically update payment status when payment is received

---

Final Thoughts

You don’t need expensive tools to track mobile money and bank payments. With ClickPesa’s Order BillPay Control Numbers and a basic Excel setup, you can fully automate invoice tracking with just a bit of Power Automate integration.

If you like any other integration, reach out to ClickPesa, anything is possible with the APIs