Connecting Stripe to Xero

Stripe is our payment provider. We love the simplicity of Stripe and the convenience that it brings. We are also big fans of Xero, our accounting software. As a company with presence in both US and UK, we needed a way to connect Stripe to Xero to allow us calculate the correct taxes and make our life easier when it comes to reconciling our revenue with our bank statements.

Taxes, taxes, taxes

Every time you charge a customer in Stripe it creates a unique payment in the system. This payment is linked to a single customer. Stripe then pays the total of all the payments (minus their fees) to your bank account under a single transaction.

Imagine the following scenraio

Customer | Country | Payment
-------------|---------|---------
ACME Inc | US | $10.00  
Blimey Ltd | UK | $9.00   
Ze Corp GmbH | Germany | $8.00

2 days after these payments are taken, you will have $27 paid in your account. This is all good unless you are either:

You are a EU company and need to charge VAT for EU customers but not the non-EU ones
You are a US company and need to charge different tax rates for different states.

If you are like us and use Xero, you can setup different accounts for different VAT or tax rates. For example you can have the following income accounts:

Account | Description  
---------|--------------
 4000 | Sales UK     
 4001 | Sales EU     
 4002 | Sales non-EU

or even more detailed accounts spliting sales and support (as they are taxed differently).

The big question here is How do I split that $27 between these accounts?

When you file your VAT return (or US sales tax) you should split that amount like this:

Account | Description | Amount 
---------|--------------|--------
 4000 | Sales UK | $9.00  
 4001 | Sales EU | $8.00  
 4002 | Sales non-EU | $10.00

You definately don't want to do this manually every 2 days!

APIs to the rescue

We built a connector that connects Stripe to Xero and splits the payments into the correct account.

Step 1 - Create a Xero developer account

Go to Xero developer resources page and get started. Xero gives you a Demo account as well so you don't mess up your real accounts.

Step 2 - The connector

require 'stripe'
require 'xero_gateway'

# methods here are called only by Stripe
class PaymentHookController < ApplicationController
    
    XERO_ACCOUNTS = { :no => 4004, :eu => 4002, :uk => 4000 }
    
    # called by transfer.paid event of stripe
    # NOTE: for now it only works with product revenue. Support revenue needs to be identified and invoiced differently
    # NOTE: This will require xero private key to be available in the home dir
    def transfer_paid
        raise ActiveRecord::RecordNotFound if !custom_authenticated

        if params[:type] != 'transfer.paid'
            # we are going to accept this as valid but not follow through on our side. this way Stripe will not retry
            render :json => { :ok => true, :message => 'not a transfer.paid event' }
            return
        end

        Stripe.api_key = Configuration.stripe_private
        
        Rails.logger.info("Received Stripe transfer.paid callback with payload #{params.to_json}")
        
        line_items = []
        # get a list transactions within this payment by calling back. For some reason the payload doesn't have all of them
        transaction = Stripe::Transfer.retrieve(params[:data][:object][:id]).transactions.all(:count => 100) # need to do this to get all transactions
        tx = transaction[:data]
        tx.each do |item|
            # find the transaction
            begin
                # we don't need to file Stripe charges
                next if item[:type] != 'charge'
                
                # get the charge
                charge = Stripe::Charge.retrieve(item[:id])
                # get the customer
                account = Account.find_by_stripe_customer_id(charge[:card][:customer])
                
                if account.nil?
                    Rails.logger.error("Account not found for #{charge[:card][:customer]}")
                    next
                else
                    Rails.logger.debug("Adding line item for charged of customer #{account.id}")
                end
                
                line_items << { 
                    :description => "Usage charge for customer #{account.id} CH:#{charge[:id]} TX:#{params[:data][:object][:id]}", 
                    :account_code => XERO_ACCOUNTS[account.vat_category], 
                    :unit_amount => item[:net].to_f / 100.00
                }
            rescue => e
                Rails.logger.error("Failed to retrieve Stripe charge #{item[:id]} due to #{e}")
            end
        end
        
        if line_items.empty?
            Rails.logger.error("No line items were created")
            render :json => { :ok => false, :message => 'No line items created' }, :status => 400
            return
        end
        
        Rails.logger.info("Creating invoice in Xero")
        begin
            gateway = XeroGateway::PrivateApp.new(Configuration.xero_key, Configuration.xero_key, File.join(Dir.home, 'xero.pem'))
            # create a xero invoice
            invoice = gateway.build_invoice({
                :invoice_type => "ACCREC",
                :due_date => Time.now.utc,
                :reference => "Stripe Invoice for Transfer #{params[:data][:object][:id]}",
                :line_amount_types => "Inclusive"
            })
            invoice.contact.name = 'Stripe'
    
            line_items.each do |item|
                invoice.line_items << XeroGateway::LineItem.new(
                :description => item[:description],
                :account_code => item[:account_code],
                :unit_amount => item[:unit_amount])
            end
    
            invoice.create
        rescue => e
            Rails.logger.error "Failed to create invoice due to #{e}"
            render :json => { :ok => false, :message => e.message}, :status => 500
            return
        end
        
        render :json => { :ok => true, :message => 'Done' }
    end
end

Notes

custom_authenticated is a method not defined here. It returns true if the call can be authentiacated. You can use your prefered way to make sure the call is made by Stripe.
You need to make sure your Xero API pem key is accessible by the code (under user home directory in this sample)

What's happening?

The connector is simple. It gets hit with a POST load (webhook) by Stripe. It then parses the payload of the webhook and looks up each of the customers behind every payment that made up the transaction in our database to detemine their VAT situation (or US sales tax one as an example). It then uses Xero API to file each payment under the right account number.

Step 3 - Push it up

This is a Rails controller example for the connector. You can put it in your app, or split it into a smaller app hosted on Heroku or your own server with Cloud 66.

Step 4 - Hook it up to Stripe

Now that the connector is live, you can add a Web Hook to your Stripe account. The webhook will be hit everytime Stripe transfers money to your account and the connector will split the amount into its constituencies and file them against the correct Xero account.

Awesome!