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!
