Do you host your website on Squarespace and collect donations or sell products? Are you using a donor management system or a CRM that uses Stripe as the payment gateway? If you answered yes to any of these questions, this tutorial is meant for you.
I have seen many users get confused about recording revenue transactions from Stripe in QuickBooks Online. If you only record the net sales amount that shows up on the bank feeds, you are understating your revenue and expenses.
In this tutorial, I will walk through my process to record and reconcile Stripe transactions in QuickBooks Online.
Note that the same method can be applied to any other merchant account/payment gateway systems you use such as PayPal, Vanco Give+, etc.
Skip to Step #5 if you are one of the lucky ones using a CRM or donor management system like Breeze that makes it easier to run the reports that show the Stripe gross sales and fees taken out of each transaction in their platforms.
For the rest of us using CRM systems that don’t make it easier and require you to do extra steps in recording your data in QuickBooks Online, start from Step #1.
To get started, you will access the following:
Access to your Stripe account with an admin or analyst permission;
Your website orders report if the transaction details don’t transfer to Stripe;
Microsoft Excel and a cloud storage system;
And, admin or standard user permission access to your QuickBooks Online account
Step #1 - Create your folder structure
Create a folder on your computer or in your cloud storage platform that you will store all the stripe reports and QuickBooks Online report throughout this tutorial. Don’t skip this step unless you want to end up duplicating your transactions in QuickBooks Online and wasting time trying to figure out what you recorded last week.
The folder structures I use are:
Stripe (Master folder)
Stripe Transaction Details (sub-folder)
Stripe Payouts (sub-folder)
Stripe Month-End Reports (sub-folder)
Stripe-QBO Reconciliation Reports (sub-folder)
Step #2 - Download Stripe Reports (Balance Change from Activity, Payouts, & Balance Summary)
Log into your Stripe Account.
Click on Reports.
Change the date range to what you want. I tend to set this as semi-monthly or monthly depending on the volume of transactions I need to record in QuickBooks Online.
Stripe Reports Dashboard
Under the Balance Summary, click the download button.
You can leave the report format as the default option.
Click download and save the CSV file in your Stripe Month-End Report folder.
Your report should look similar to the below screenshot. This is the report you will need to reconcile the Stripe clearing account in QuickBooks Online.
Stripe Balance Summary Excel Report
Under the Balance Change from activity, click the download button. In the download screen, choose the following:
Report format = Itemized
Reporting category = All
Columns = custom and select all the appropriate fields
Your report options should look similar to the below screenshot.
Click download to download the CSV file and save it to your Stripe Transactions Details folder.
Stripe Itemized Report Options
Under the Payouts section, click download and save your file to the Stripe Payouts folder.
You can leave the report format as itemized and the columns as the default. Your report should look similar to the below screenshot.
Stripe Payout Report
Step #3 - Create Stripe Account & Service Item in QuickBooks Online
Log into QuickBooks Online.
Click the Accounting Tab and select Charts of Account.
Click the New button to create a bank type clearing account called “Stripe”.
Click the Sales tab and select Products/Services.
Click the New green button to add a new service item.
Service name = Stripe Fees
Description = Stripe processing fee
Income account = Bank charges and merchant fees
Press play to start the video - Create Stripe Account and Service Item in QBO
Step #4 - Record Stripe Transaction in QuickBooks Online
Open the Stripe balance change from the activity file in a separate monitor.
Click the +New button and select Sales Receipt to create a sales receipt or select Receipt to apply the invoice payment.
In the sales receipt window, complete the following:
Customer name = Select from an existing one or create a new one
Payment method = Stripe or card type
Reference No = Copy the transaction ID number or the charge ID number or invoice ID from the CSV
Payment date = Created date from the CSV file
Location tracking = with or without donor restriction for nonprofits or retail locations for small businesses
Deposit to = Stripe bank type clearing account
Line 1: Record the gross sales
Select a product/service item that matches the description field from the CSV file
Enter a description if needed
Amount = gross sales from the CSV file
Enter the appropriate class if you are using class tracking
Line 2: Record the fees
Product/service item = Stripe
Description = payment processing fee
Amount = fee from CSV file as a negative number
Enter the same class from above if you are using class tracking
The total net amount should match the net amount column from the CSV file
Add a copy of the Stripe report to the attachment section if you are recording a single transaction
Click save and close
Press play to start the video - Record Stripe Transactions in QBO
Step #5 - Record Stripe Payouts in QuickBooks Online
If you look at your Stripe general ledger register you will see the sales receipt, we recorded listed there.
To move the funds to the real bank account, open the Stripe payout CSV report.
Back in QuickBooks Online, you can use the “Transfer” feature to move the funds from the Stripe clearing account to your real bank account if you are not using class and location tracking.
Check out my guide on how to transfer funds in QuickBooks Online to complete this step.
If you are using class tracking and location tracking, follow my guide on how to record bank transfers using a check.
Using the check method, record your transaction doing the following:
Payee = Stripe Transfers or Fund Transfers (these are vendor names)
Bank Account = Stripe clearing account
Payment Date = Payout date from CSV file
Check No. = payout ID (you can shorten the ID if needed)
Location tracking = With or without donor restrictions for nonprofits or physical location for small businesses
Category = Select your real bank account
Description = Stripe payout id number from the CSV file
Enter the same class from above if you are using class tracking
Add a copy of the Stripe payout report to the attachment section if you are recording a single transaction
Click save and close
Press play to start the video - Record Stripe Payouts in QuickBooks Online
Step #6 - Reconcile Stripe Clearing Account
Open the Stripe Balance Summary report you downloaded earlier. The ending balance amount from this report is what you will use in reconciling the Stripe account each month in QuickBooks Online.
Follow my guide on how to reconcile a bank account in QuickBooks Online to complete the rest of this process.
Press play to start the video - Reconcile Stripe Account in QuickBooks Online
You will notice that the transactions we recorded are clearing the Stripe account. Your register balance for the same closing period should always match the ending balance in the Stripe Balance Summary report unless you recorded more transactions past the closing date.
Don’t forget to save your Stripe account reconciliation report in the Stripe Reconciliation sub-folder you created earlier.
If you have a negative balance or none of the amounts are matching your Stripe reports, you need to go back to step #3 through #5 to find the duplicate transaction. The Stripe transaction number and payout ID are unique numbers you can use to search in QuickBooks Online to locate the duplicate record.
Overall, if you follow the guidelines above, your reconciliation process should be a piece of cake and your Stripe account should balance at all times.
Are You On Pinterest?
Pin this post and share it with others.
If you found this tutorial helpful, leave a comment and/or share it with others. Contact me - I am happy to help with QuickBooks Online set up, training or troubleshooting issues.
