Automate Stripe Payout Categorisation in Xero: Make.com Workflow for UK SMBs

Automate Stripe Payout Categorisation in Xero: A Make.com Workflow for UK SMBs

If you run a small business in the UK, you'll know that dealing with finances can be a real time sink. Especially when you're using a payment processor like Stripe and an accounting platform like Xero. The promise of seamless transactions often collides with the reality of manual reconciliation. Stripe pays out net amounts, making it a headache to categorise individual sales, fees, and refunds accurately in Xero. It’s one of those tasks that just eats away at your precious time, right?

But what if you could put that on autopilot? What if your Xero account updated itself, ready for you to review, not manually process? That's precisely what we're going to explore today: creating a robust Make.com workflow to handle your Stripe payout automation and Xero categorisation. It's a prime example of no-code finance making a real difference in automated accounting for UK small businesses.

The Problem with Manual Stripe Payout Reconciliation

You’re busy. Your business generates sales through Stripe, and at regular intervals, Stripe sends a lump sum payout to your bank account. In Xero, you see this one large "Receive Money" transaction from Stripe. Great, you think, money in! But then comes the hard part.

This single payout isn't just your gross sales. Oh no. It's your gross sales minus all the individual Stripe fees, minus any refunds you've issued, plus any adjustments. Xero, quite rightly, doesn't know what makes up that amount. So, you're faced with a choice:

• Categorise the whole payout as "Sales" (which is inaccurate because it doesn't account for fees or refunds).
• Manually dig through Stripe reports to find the gross sales, fees, and refunds for that specific payout period, then manually create multiple journal entries or "Spend Money" transactions in Xero to reflect the true picture.

Neither option is ideal. The first leads to inaccurate financial reporting, making it harder to track true profitability and prepare for tax season. The second is a tedious, error-prone task that can take hours each month, pulling you away from growing your business. For UK small business bookkeeping, getting this right isn't just good practice; it's essential for HMRC compliance and for genuinely understanding your business's performance.

Why Make.com is Your Ally in Automated Accounting

So, how do we fix this without hiring a full-time accountant just to match Stripe transactions? Enter Make.com. If you're not familiar with it, Make.com (formerly Integromat) is a powerful visual integration platform that allows you to connect apps and automate workflows without writing a single line of code. Think of it as a digital bridge builder, helping different software platforms talk to each other.

Unlike simpler automation tools, Make.com shines when you need complex logic, conditional routing, and data transformation. This is exactly what we need for Stripe payout automation. We're not just moving data; we're processing it, calculating sums, and then creating multiple, correctly categorised entries in Xero. It's a sophisticated solution for a common problem, bringing advanced automated accounting capabilities to any small business owner comfortable with a bit of logical thinking.

Understanding the Core Challenge: Stripe's Payout Structure

Before we jump into building, let's get clear on what a Stripe payout actually represents. When Stripe sends money to your bank, it's typically an aggregation of "balance transactions." These aren't just your sales; they include:

• Charges: Your customer payments.
• Refunds: Money returned to customers.
• Stripe Fees: The charges Stripe applies for processing transactions.
• Application Fees: If you use Stripe Connect or a platform that charges an additional fee.
• Disputes/Chargebacks: Funds held or deducted due to customer disputes.

Each payout has a unique ID and a set of associated balance transactions. The key to accurate Xero categorisation is to break down that lump sum into its constituent parts: the gross sales, the total fees, and the total refunds for that specific payout. Then, we post these individual components to the correct accounts in Xero, using a "clearing account" to balance everything out. This method ensures your bank reconciliation is clean and your profit and loss statement reflects your true income and expenses.

The Make.com Workflow: Step-by-Step Guide to Automated Categorisation

Right, let's get practical. This workflow will:

1. Trigger when a new Stripe payout occurs.
2. Retrieve all the individual transactions associated with that payout.
3. Aggregate the gross sales, total fees, and total refunds.
4. Create a comprehensive journal entry in Xero that correctly categorises these amounts.
5. Ensure the bank reconciliation in Xero is straightforward.

1. Setting up Your Xero Accounts (Crucial First Step!)

Before you even touch Make.com, you need the right accounts in Xero. These ensure your automated accounting workflow has the correct destinations for the data.

• Stripe Clearing Account: This is the most important one. Create a new "Bank" type account in Xero (even though it's not a real bank account) and name it something like "Stripe Clearing Account" or "Stripe Settlement Account." This account acts as a temporary holding place for all your Stripe ins and outs before the net amount hits your real bank. Set its currency to GBP (or your operating currency).
• Stripe Sales Account: Typically an "Income" account. You might already have "Sales" or "Revenue." Ensure you have one dedicated to Stripe sales, or use your general sales account.
• Stripe Fees Account: An "Expense" account, perhaps "Bank Fees" or "Stripe Merchant Fees." This is where the transaction processing costs will go.
• Stripe Refunds Account: Could be an "Expense" account or a "Sales" account with negative entries, depending on your preference. "Refunds Issued" as an expense is common.

Once these are set up, you’re ready to build the Make.com scenario.

2. Creating a New Scenario in Make.com

1. Go to Make.com and click "Create a new scenario."
2. Choose Stripe as your first module.
3. Select the "Watch Payouts" trigger. This module will listen for new payouts from Stripe.
4. Connect your Stripe account. If it’s your first time, you’ll be prompted to authorise Make.com to access your Stripe data.
5. Set the "Limit" to 1 for testing purposes; you can increase it later.

3. Retrieving Payout Balance Transactions

The "Watch Payouts" module gives you the payout ID. Now we need all the detailed transactions that make up that payout.

6. Add another Stripe module.
7. Select the "List Balance Transactions" action.
8. For the "Payout" field, map the ID output from your first "Watch Payouts" module.
9. Set a "Limit" again, perhaps 100 or 200, to ensure you capture all transactions for a larger payout.

4. Aggregating Data for Xero

This is where we process all those individual transactions and sum them up correctly. We need to differentiate between charges, refunds, and fees.

10. Add an Array Aggregator module. Connect it to the "List Balance Transactions" module.
11. Set "Source Array" to the `data[]` from the "List Balance Transactions" module.
12. Configure the fields you want to aggregate. You'll need at least:
    • Gross Sales: Use an AI assistant or a specific AI model like ChatGPT to help you with the filter formulas here if you're stuck. The logic is: sum `amount` where `type` is 'charge' AND `status` is 'available'. Remember, Stripe amounts are in cents/pence, so divide by 100. {{sum(map(filter(3.data; "type" = "charge" AND "status" = "available"); "amount")) / 100}}
    • Total Fees: Sum `fee` for all relevant transactions. Again, divide by 100. {{sum(map(3.data; "fee")) / 100}}
    • Total Refunds: Sum `amount` where `type` is 'refund'. Refunds show as negative amounts in Stripe, so we'll likely want to make this a positive number for Xero's "Refunds Issued" expense account. {{abs(sum(map(filter(3.data; "type" = "refund"); "amount"))) / 100}}
    • Net Payout: This should match the original "Watch Payouts" module's `amount_gross` (after dividing by 100). This is for reconciliation. {{1.amount_gross / 100}}
13. Give each of these aggregated values a clear target name (e.g., `totalGrossSales`, `totalStripeFees`, `totalRefunds`, `netPayoutAmount`).

5. Preparing Data for Xero (The Journal Entry Logic)

Now we have our summed totals. Time to send them to Xero. A "Journal Entry" is often the best approach here as it allows for multiple debits and credits within a single transaction, perfectly reflecting the breakdown of the Stripe payout.

14. Add a Xero module.
15. Select "Create a Journal Entry."
16. Map the following fields:
    • Date: Use the payout's `arrival_date` from the "Watch Payouts" module.
    • Narration: Something descriptive like "Stripe Payout - {{1.id}} ({{formatDate(1.arrival_date; "YYYY-MM-DD")}})".
    • Journal Lines: This is where the magic happens. You'll create multiple line items, ensuring your debits equal your credits.
17. For the Journal Lines, you'll need at least these four:
    • Line 1 (Debit Sales):
      • Account: Your "Stripe Clearing Account"
      • Description: "Stripe Gross Sales - {{formatDate(1.arrival_date; "YYYY-MM-DD")}}"
      • Debit: Map your `totalGrossSales` from the Array Aggregator.
      • Credit: Leave blank.
      • Tax Rate: Choose the appropriate rate (e.g., "NO VAT", "20% (VAT on Income)"). If your Stripe sales include various VAT rates, you might need a more complex workflow involving Stripe metadata or separate journal entries, but for simplicity, we'll assume a single rate or non-VAT sales for this example.
    • Line 2 (Credit Sales):
      • Account: Your "Stripe Sales Account"
      • Description: "Stripe Gross Sales - {{formatDate(1.arrival_date; "YYYY-MM-DD")}}"
      • Debit: Leave blank.
      • Credit: Map your `totalGrossSales` from the Array Aggregator.
      • Tax Rate: Match the debit line.
    • Line 3 (Debit Fees):
      • Account: Your "Stripe Fees Account"
      • Description: "Stripe Fees - {{formatDate(1.arrival_date; "YYYY-MM-DD")}}"
      • Debit: Map your `totalStripeFees` from the Array Aggregator.
      • Credit: Leave blank.
      • Tax Rate: Usually "NO VAT" for fees from Stripe for UK businesses, but confirm with your accountant.
    • Line 4 (Credit Fees):
      • Account: Your "Stripe Clearing Account"
      • Description: "Stripe Fees - {{formatDate(1.arrival_date; "YYYY-MM-DD")}}"
      • Debit: Leave blank.
      • Credit: Map your `totalStripeFees` from the Array Aggregator.
      • Tax Rate: Match the debit line.
    • Line 5 (Debit Refunds):
      • Account: Your "Stripe Refunds Account"
      • Description: "Stripe Refunds - {{formatDate(1.arrival_date; "YYYY-MM-DD")}}"
      • Debit: Map your `totalRefunds` from the Array Aggregator.
      • Credit: Leave blank.
      • Tax Rate: Match your sales tax rate, or "NO VAT" if refunds don't affect VAT.
    • Line 6 (Credit Refunds):
      • Account: Your "Stripe Clearing Account"
      • Description: "Stripe Refunds - {{formatDate(1.arrival_date; "YYYY-MM-DD")}}"
      • Debit: Leave blank.
      • Credit: Map your `totalRefunds` from the Array Aggregator.
      • Tax Rate: Match the debit line.

You'll notice that the Stripe Clearing Account is used as the contra account for all these entries. This means the net effect of these journal entries on the Stripe Clearing Account will be exactly the net payout amount. Brilliant!

6. Reconciling the Bank Feed in Xero

The final piece of the puzzle. Your bank account will receive the net payout from Stripe. You need to tell Xero that this incoming cash belongs to the activities we just journaled.

18. Add another Xero module.
19. Select "Create a Receive Money Transaction." (This will correspond to the bank feed transaction).
20. Map the following:
    • Contact: "Stripe"
    • Bank Account: Your actual business bank account in Xero (e.g., "NatWest Current Account").
    • Date: The payout's `arrival_date` from the "Watch Payouts" module.
    • Reference: "Stripe Payout {{1.id}}"
    • Amount: Map your `netPayoutAmount` from the Array Aggregator.
    • Line Item (Description): "Stripe Payout Settlement"
    • Line Item (Account): Your "Stripe Clearing Account." This is key!
    • Line Item (Tax Rate): "NO VAT" (as this is just moving money between internal accounts).

When the actual bank transaction from Stripe comes through your bank feed in Xero, it will now automatically match against this "Receive Money" transaction we just created. One click to reconcile!

7. Testing and Scheduling

You’ve built it! Now to test. Run the scenario once, making sure you have a recent Stripe payout that hasn't been reconciled in Xero. Check Xero to ensure the journal entry and the receive money transaction are created correctly. If all looks good, set your Make.com scenario to "On" and schedule it to run daily or weekly, depending on your payout frequency.

Refining Your Workflow: Beyond the Basics

This basic framework gets you a long way, but you can always do more:

• Detailed Sales Categorisation: If you sell different products or services through Stripe and they appear in your Stripe metadata, you can add more filters and aggregators to split your gross sales into various income accounts in Xero (e.g., "Product A Sales," "Service B Sales"). This provides much richer data for your Profit & Loss report.
• Error Handling: Add modules to send you an email or a Notion notification if a step in the workflow fails. This is good practice for any critical automated accounting process.
• AI Assistance: Don't hesitate to use AI models like ChatGPT or Claude to help craft complex Make.com expressions or troubleshoot issues. I've found that explaining what you want to achieve often yields useful formula suggestions. For more general bookkeeping prompts, you might find our article Essential AI Prompts for UK Small Business Bookkeeping helpful.
• Other Integrations: Perhaps you want to track customer data in a CRM after a sale, or automate invoice reminders. Make.com can connect these too. We cover similar automation for reminders in our post How to Automate Invoice Reminders with AI and Google Sheets.

Benefits for Your UK Small Business

Implementing this Stripe payout automation workflow isn't just about tidying up Xero. It's about giving you back control and time:

• Massive Time Savings: Imagine hours each month freed up from manual data entry and reconciliation. That’s time you can spend on sales, marketing, or simply enjoying a well-deserved break.
• Improved Accuracy: Automated processes reduce human error significantly. Your financial records will be more precise, making tax preparation (especially for VAT-registered businesses) much smoother and less stressful.
• Better Financial Insights: With real-time, accurate data in Xero, you have a clearer picture of your business's performance. You can make faster, more informed decisions.
• Peace of Mind: Knowing your Xero categorisation is handled correctly means less worry about HMRC audits or year-end accounts. It really helps with UK small business bookkeeping.

Setting up this Make.com workflow might seem a bit daunting at first, but once it's configured, it runs silently in the background, a true testament to the power of no-code finance. It’s an investment of your time upfront that will pay dividends for years to come, giving you a proper taste of automated accounting. Go on, give it a go; your future self will thank you for it.