We rely on Paypal as our credit and debit card processor, as well as our payment gateway for our website. We’ve found the benefit of receiving payments immediately paired with the Paypal Debit Card irreplaceable when standing up against higher processing fee’s, more complicated setups and inexplicable delays of 3-5+ business days to wait for funds to be deposited.
However, there are a number of problems with Paypal’s Integration method with QuickBooks and it’s CSV export functionality.
This article not only highlights exactly what those issues are and how to repeat them, but it shows you how to resolve them.
The best part is, I’m a chronic fixer and I own a internet marketing company so I will be employing my staff to develop an automated script that does all this for you as soon as possible. 🙂
But before you continue reading this, you might be able to follow this article by Xero (a competitor to QuickBooks) easier.
- When using Quickbooks’ automatic import / synchronization feature with Paypal it’s only using the gross, which means it does not account for the fee charged by Paypal.
- To solve this you’ll need to create a manual entry (one for each month is best, but one for the year is fine too) that is the total of all your PayPal fee’s).
- The same issue occurs when exporting the CSV from Paypal directly.
- In order to reconcile; Quickbooks requires you to enter your balance at the start of the month, and your balance at the end of the month. But in PayPal the balances aren’t accurate because they have a transaction on the date it’s requested of Paypal, and a second transaction on the date Paypal actually executes that transaction (usually several days later). So it debits, credits and debits the account which can cause that to appear as income, as well as report a false balance. It should be separating Available Funds from Actual Funds but it doesn’t appear to do this. (I’m working on a solution for this right now and should have a solution).
- Note; Starting balance should be the balance after the last transaction on the day before the first day of the month. https://www.paypal.com/webapps/business/transactions
- To locate your Monthly Statement Ending Balance:
- In “Classic” Paypal
- My Account -> Profile -> My Money -> Statements -> “Choose another report” -> Monthly Financial Summary
- In New Paypal
- When adding a sub-category, sometimes you’ll get an error message saying “For sub-accounts, you must select the same account type as their parent.” but the problem is that it doesn’t tell you what the “account type” is, and there is no area labelled “account type” anyway – so I’m assuming it’s referring to “Detail Type”.
- Upon adding a Rule in QuickBooks it reloads the page fucking up all the shit you just did by erasing the values you entered for your transactions such as Payee and Category. Quickbooks should be fucking sued for this shit.
What Paypal Has to Say
Here is what Paypal has to say on these topics. I actually didn’t find these until after I wrote this article – which is unfortunate because it seems to propose a possible solution: https://business.paypal.com/webapps/merchantreportingweb/popup.form?popupType=faq#question5
Before You Begin
Ultimately, the issue is that Paypal doesn’t account for fee’s. But when you use Quickbooks method of adding your Paypal and syncing automatically, opposed to manually adding it into QuickBooks through the Transactions > Chart of Accounts area, Paypal is providing QuickBooks with the gross balance. So, while Paypal only goes back three months, it’s much simpler to connect it that way, then import the CSV of just the Fee’s each month forward until Paypal resolves this issue.
Export & Filter CSV to be Imported
Before you can import your historical transactions from Paypal you will need to export and filter the CSV they provide you, as well as take into account the Fee’s column. Below are instructions on how to do so.
- After logging into Paypal: to export the correct CSV. Important Note: There are a number of places that you can export a CSV from Paypal. It’s obnoxious and confusing because they all export different data so exporting from the specified location is very important. Do not get the Balance for the Quickbooks Register from here: https://www.paypal.com/webapps/business/transactions?tab=bookkeeping
- Classic Paypal
- My Account > History > Download History
- New Paypal
- More > Reports > Download History
- Transactions > Download (on the middle right of the screen)
- Select your Date Range.
- Open the CSV in your CSV editor of choice. I recommend uploading it to Google Drive and opening it with Spreadsheets (Google’s version of Microsoft Excel). These instructions may not be exactly right if you use something else but it should still be close enough to get the job done.
- Before you remove any columns you need to Filter out the following from the Type using the Type column:
- Temporary Hold
- Reserve Hold
- Reserve Release
- Invoice Sent
- Request Sent
- Add Funds from Bank Account – We don’t want this to appear as income, but Filtering this from the Transactions will probably make the balance incorrect. So I’m not sure what the right choice is here but I’m going to leave it in for now because, theoretically, if it shows as an expense in the bank account that it’s coming from then it should zero out.
- Cancelled Fee
- Remove all columns except for the following:
- item title (it’s okay if this is blank for all of the transactions)
- Make the Gross and Fee columns “Plain Text”
- Duplicate the sheet.
- In the duplicated sheet, Filter the Fee’s column by deselecting 0, “(Blanks)”, “…” and any positive numbers. Now you should only see a list of negative numbers.
- Delete the “Gross” column.
- Select the first row beneath Name Column and enter “Paypal Fee”
- Copy that.
- Then select the Name Column, which should select all of the rows beneath it.
- Deselect the first row and then paste the “Paypal Fee” text that you previously copied and it should paste it into every row beneath Name Column.
- Now select all of the rows (excluding your column headings row) and copy.
- Now switch back to the first Sheet.
- Add a row at the bottom of the Sheet.
- Now paste the rows you previously copied.
- Now delete the other Sheet.
- And delete the “Fee” column in the first Sheet.
- Now you can import into Quickbooks.
- And drink some alcohol.
General Notes About Bugs with Paypal CSV’s
- Vendor/Payee is listed under Description when it should be under Payee.
- Should Paypal Fee’s be Categorized as a Cost of Goods Sold?
- quickbooks online how to edit categories
- How to remove Categories (AKA Accounts) in QuickBooks?
- How do I bulk Accept/Add Transactions.
- How do you edit “Rules” once they’ve been created?
- What is the difference between the “Add” button and the “Accept” option in Bulk Edit?
- How do I create sub-categories when categorizing a Transaction? The only “sub” option I see is for “Sub-Accounts” – which doesn’t make sense if that’s what I’m suppose to use because Categories aren’t Accounts.
- When categorizing an expense that is required in order to run the company (electricity for example) what should it’s “Detail Type” be?
This post is related to: