How to import Stripe Revenue Recognition reports to QuickBooks without an accountant

Here's my step-by-step process to sync data from Stripe to QuickBooks

We’re using Stripe to accept payments and QuickBooks for accounting. Since Standuply is a SaaS tool, we chose the accrual way of accounting, and that’s where Stripe Revenue Recognition reports are very handy.

However, I was surprised it’s not a straightforward task to import those reports from Stripe to QuickBooks. If your accountant does that, you may not even know the entire process, but indeed pay for the hours wasted on that task.

Once I nailed it down with a set of tools and a complete workflow, I decided to share it with others for two reasons.

1. If I’m doing it all wrong, somebody will tell me about flaws in my process.

2. If I’m doing it right, it is actionable advice that will save you time and money.

The process consists of two logical steps. On the first, you sync the data from Stripe to QuickBooks, and on the second, you double-check it matches up.

Syncing Stripe Revenue Recognition with QuickBooks

Step #1

In Stripe, download the Itemized Report (Reports → Balance → Balance change from activity) for the corresponding month. Then upload it to Google Spreadsheets and make the following updates:

• Delete columns you don’t need: created, available_on, currency, fee, net, customer_name.

• Sort the document from A to Z using the column reporting_category and delete all entries that are not a charge.

• Set the date to DD/MM/YYYY format (created_utc column) and rename it to ‘Date’.

• Remove the time from the Date column by using this option in a Google Spreadsheet: Data → Split text into columns using space as a separator (make sure to have a blank column next);

• Rename Gross column to Line Amount.

• Insert three columns: Deposit To Account, Payee, Line Account, and fill the data for all the entries. In our setup, it’s the following: Deposit To Account = Cash from Stripe Sales; Payee = Standuply Contracts; Line Account = Accounts Receivable (A/R). In your accounting settings, it can be different, but you should get the idea.

Now if you want to add context to all the entries, you may follow the optional instructions below.

Use =Concat(A,B) formula in Google Spreadsheet to go consequentially

from

charge | Subscription update | email@email.com in separate columns

to

“charge for Subscription update - email@email.com” in the one column and name it Line Description. Then put another column next to it with just the same data and name it Line Memo.

After you’re done, download the file as a CSV, and in the next step, we’ll upload it to QuickBooks via 3-rd party service. It turns out uploading it directly in QuickBooks won’t work correctly.

Step #2

Signup for Saasant.com and connect it to your QuickBooks account.

Now and next time, do the following in Saasant:

New Import → Browse file → Continue → Bank Deposits (under Other Transactions)

Next, you will review that all columns from the uploaded file match your QuickBooks fields and click Import to QuickBooks. As the process ends, you’ll see a report.

Step #3

In Stripe download the following report: Reports → Revenue Recognition -> Debits and Credits. Make sure to choose a corresponding month and the period of one month only.

Then upload it to Google Spreadsheets, and make minor changes:

• Delete all Cash to Account Receivables A/R inputs.

• Replace Cash with your QuickBooks field for the Stripe Revenue (in our setup, it’s “Cash from Stripe Sales”).

• Replace Revenue with your QuickBooks field for income (in our setup, it’s Sales of Product Income);

Then make a journal entry dated the last date of the corresponding month with this data.

Step #4

In Stripe, go to Reports -> Balance -> Balance Summary and find the “Less fees” number for the corresponding month.

Make another journal entry dated the last of that month with the following entry:

Account: Merchant Service Fees // Debit: Less fees sum.
Account: Fees from Stripe Sales // Credit: Less fees sum.

That’s how we set it up in QuickBooks so that Fees from Stripe Sales is the sub-account of Stripe Account (which is cash on hands account). Maybe you’ll do that differently.

Verification

To verify that everything is correct, do the following.

Step #1

In QuickBooks, run Profit & Loss report for the corresponding month and check the field for sales in your setup (in our setup, it’s Sales of Product Income).

In Stripe find this number: Revenue recognition -> Month's summary -> Net revenue.

They should match up.

Step #2

In Stripe, download the following report (Reports -> Revenue Recognition -> Balance Sheet report) for the corresponding month. Open it in a Google Spreadsheet and look at the ending_balance column.

In QuickBooks run the report: Reports → Balance Sheet and compare the inputs below. All of them should match up.

• UnbilledAccountsReceivable

• ExternalAsset

• DeferredRevenue

• CustomerBalance

• Cash (or how it’s named in your setup)

Step #3

In Stripe, find this number: Reports->Financial Reports->Balance Summary → Ending balance.

In QuickBooks in the Balance Sheet report, there should be a number for your Stripe Account (in our setup, it’s Stripe Account).

They should match up.

As a conclusion

After going through this process for months, it takes about half an hour which is awesome, but I wish there would be a tool that does it all automatically in a single click.

Is it a good startup idea? :)