Load Bank Feed History into the Stripe Bank Account in Xero
If you activated SubSync, then found that you didn’t have a Stripe Bank Account setup correctly in Xero you will find that you have invoices in Xero with nothing to reconcile against in the Stripe Bank Account.
The good news is it’s easy to load the Bank Account transaction history into Xero.
Firstly, make sure that your Stripe Bank Account is activated in Xero and that you have at least one transaction available - make note of this transaction as we’ll need it later (this let’s us know where we can stop loading transactions). Note: When first activated it may take up to 24hrs for the first Bank Account transaction to flow through from Stripe to Xero.
In Stripe go to the Balances screen and select the All Activity tab
Press the Export button.
Set the date range to Custom and enter the date range you want to export. Start with the first Invoice in Xero that SubSync inserted, and finish before the newest bank transaction automatically inserted by Stripe into Xero (you noted this above). In this case we’ve picked the month of May. In the Columns menu select All columns, then press Export.
You will need to make a few small changes to the exported spreadsheet to be able to import into Xero. Open the exported file in your favourite spreadsheet editor:
The Dates that Stripe uses aren’t compatible with Xero - you will need to format the Created (UTC) column as a date
As we’ll also be using this data to generate Stripe Fee items in Xero we’ll need to add two new columns to the right - call them Stripe Fee Description and Stripe Fee Amount
In the Stripe Fee Description column enter the words “Stripe Fee” in every row (as per below)
In the Stripe Fee Negative column we want to show a negative of the value in the Stripe Fee column that was exported from Stripe. To do this you can create a simple formula (e.g. =-E2 where E2 is the first row of the Stripe Fee column), then fill down for all rows. Check that the Stripe Fee and Stripe Fee Negative columns match values with the only difference being a negative value for the Stripe Fee Negative column.
Now export your work to CSV for import into Xero.
Go to Xero and in the Stripe Bank Account overflow menu (three vertical dots) (or Manage Account menu) and select Import a Statement
Use the Select file button to select the file you just exported from Stripe, then press Next.
Xero now allows you to map columns between Stripe and Xero. Map the fields as follows:
Column heading from CSV file | Field in Xero |
---|---|
Type | Transaction Type |
Source | Reference |
Amount | Transaction Amount |
Created (UTC) | Transaction Date |
Description | Description |
Make sure the Don’t import the first line because they’re column headings checkbox is ticked.
Use the preview feature to check some of the records, then press the Next button when done. Another preview will be shown, then press the Complete Import button to bring the data into Xero.
You will now need to bring the Stripe Fees into Xero - these will be offset against the invoice amounts to come to a net position that will allow the account to balance. We’re going to repeat the process with a few chnages.
Go back to the Stripe Bank Account overflow menu (three vertical dots) (or Manage Account menu) and select Import a Statement again
Select the same file again then scroll to the bottom of the screen and select the Edit import settings button and change the column mappings:
Column heading from CSV file | Field in Xero |
---|---|
Source | Reference |
Created (UTC) | Transaction Date |
Description | Description |
Stripe Fee Description | Payee |
Stripe Fee Amount | Transaction Amount |
Once you are happy you can press Next. Now conform that the data looks OK (there may be some $0 items that will be ignored as they don’t have fees). Press Complete Import when ready.
You are now ready to reconcile!
You should now see the transactions to be reconciled in the Stripe Bank Account on the Reconciliation tab - they should be auto-matched with the invoices created by SubSync for the same period. Stripe Fees will be matched against Bills created by SubSync (if you have the Stripe Fees feature turned on), or you can use a Bank Rule to allocate them to Stripe as expenses.
If you have any problems reach out to our support team.