Paypal & Quickbooks Quirks

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.

 

Bugs

  1. 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.
    1. 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).
  2. The same issue occurs when exporting the CSV from Paypal directly.
  3. 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
  4. 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”.
  5. 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.

 

  1. 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.
    • Download
  2. 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.
  3. 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
    • Authorization
    • 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
  4. Remove all columns except for the following:
    • date
    • name
    • gross
    • fee
    • item title (it’s okay if this is blank for all of the transactions)
  5. Make the Gross and Fee columns “Plain Text”
  6. Duplicate the sheet.
  7. 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.
  8. Delete the “Gross” column.
  9. Select the first row beneath Name Column and enter “Paypal Fee”
  10. Copy that.
  11. Then select the Name Column, which should select all of the rows beneath it.
  12. 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.
  13. Now select all of the rows (excluding your column headings row) and copy.
  14. Now switch back to the first Sheet.
  15. Add a row at the bottom of the Sheet.
  16. Now paste the rows you previously copied.
  17. Now delete the other Sheet.
  18. And delete the “Fee” column in the first Sheet.
  19. Now you can import into Quickbooks.
  20. And drink some alcohol.

 

Final Notes

General Notes About Bugs with Paypal CSV’s

  • Vendor/Payee is listed under Description when it should be under Payee.

 

Questions

  • 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?

 

Screen Shot 2015-03-19 at 3.15.33 PM




By Spencer Hill
Categorized in: Paypal
This post is related to:

  • Generic selectors
    Exact matches only
    Search in title
    Search in content
    Search in posts
    Search in pages
    Filter by Categories
    10
    2D Vector Design
    8.1
    Adobe
    AdWords
    Analytics
    Apple
    Apps
    Bash / Shell
    Bedrock
    Blade
    Business Development
    Careers
    ChromeOS
    Company News
    Content Marketing
    Digital Design
    Digital Marketing
    Freelancing
    Gaming
    Google
    Google Hangouts
    Illustrator
    Linux
    Mac
    New Products or Services
    Operating Systems
    OS X
    Pay Per Click Management
    Paypal
    PHP
    Press Releases
    Programming
    Razer
    Reviews
    Roots
    Sage Theme
    Search Engine Marketing
    Search Engine Optimization
    Search Marketing
    Tips and Tricks
    Trellis
    Tutorials
    Ubuntu
    Uncategorized
    Video Production
    Web Hosting
    Website Development
    Website Development
    Windows
    WordPress
  • Recent Posts

  • Categories

  • About

    Since 2005 we've been offering digital and content marketing strategy and implementation. Including website development, search engine optimization and marketing, search marketing and more.

    Continue Reading »

    Contact

    Email

    us@theportlandcompany.com

    Phone

    503-567-9561

    Follow

  • Logo for The Portland Company with a Coyote
    Thank you for using our site. x