Learning QuickBooks Online | Focus on Bookkeeping
QBO - PayPal Data Sync

by L. Kenway BComm CPB Retired

PayPal data syncs can be tricky. Importing PayPal transactions into QuickBooks Online (QBO) can be frustrating.

How you choose to integrate your PayPal data with QBO (or not) depends on what you are using PayPal for. 

If you have a large volume of PayPal sales transactions, I do NOT recommend integrating at all. It just clutters up the data base and may make it more difficult to have accurate financial statements. Check my "another way" method below on how to book PayPal sales without app integration. 

Get your beverage of choice (not everyone likes tea apparently!) and let's have a chat about your options and which one is best for you.

Chat Continues Below Advertisement

Article published February 2, 2017, updated August 31, 2017

PayPal is a popular choice among small business owners who work online. It allows you to quickly sell or purchase digital products and virtual services throughout the world. This trusted payment method is simple, affordable and easy for small business owners to use.

If you sell with PayPal, the cost is 2.9% + $0.30 a transaction for domestic (Canadian) transactions. International sales are 3.9% plus a fixed fee and U.S. sales are 3.7% plus a fixed fee. As PayPal advertises - no surprises and no hidden fees. There is no vetting and you receive your money instantly ... no waiting.

Buying something using PayPal as your payment method is free unless it is transacted in a foreign currency. This will be changing in October 2017 in Canada. There will a 2.9% + fixed fee based on currency if payment is funded by a credit card or Visa debit card. There will be a cross-border fee of $2.99 for payments to the U.S. or Europe and $4.99 fee to any other country. If you fund your payment through your bank or your PayPal balance there will no charge.

Because sellers receive their funds instantly, small business owners don't have the hassle of reconciling the timing differences associated with using merchant card service gateways. It keeps thing simple for small business owners.

Three Ways to Import PayPal Transactions to QBO

You have three PayPal data sync options to QuickBooks Online. The best option for you will depend on how you are using PayPal.

1. Connect QBO's bank feed to your PayPal account. This is the QBO-PayPal data sync I choose to use if the sales volume is low.
2. Pull data into QBO using PayPal API. This app is not available in Canada. If you are from the U.S., then check out Charlie Russell's article at Accountex. It was written a few years ago so not all the information is correct as QBO updates the software frequently.
3. Fetch your data through Hubdoc. I don't use Hubdoc because of its price point so you'll have to figure this out on your own if you go this route.

I will also chat about a low-tech method to book PayPal transactions which is my preferred method if there is a large volumes of sales transactions. 

PayPal Data Sync Through QBO Bank Feed

This is the BEST OPTION if you use PayPal mainly for expense transactions purchased in your home currency (i.e. CAD not USD or EURO or GBP or AUD or ...) as the PayPal data sync categorizes the expenses   ... or my pet peeve ... those of you who don't keep a PayPal balance but transfer funds for EVERY transaction.

By not keeping a balance in PayPal, ONE transaction now requires recording THREE bookkeeping transactions if a foreign currency is involved and you want to be able to reconcile your PayPal accounts easily. 

The BIGGEST PROBLEM with the QBO - PayPal data sync through the bank feed is it doesn't handle multi-currency transactions well.

In fact, it's a HUGE NEGATIVE because it imports ALL transactions to one account in your home currency. This means any foreign transactions in the bank feed are NOT VALID because you can't get them to post the total in the home currency. Think ... mixing apples and oranges ... where a $35 USD posts as $35 CAD ... and there is no way you can edit the price to book the true CAD value. You will need to "EXCLUDE" foreign currency amounts from your bank feed and enter these transactions manually.

Excluded bank feed transactions will show up under the Excluded tab. The transaction in this example was a USD purchase. The $35 shows up in the PayPal bank feed as CAD and QBO will not let you change the CAD value. It will let you change the USD value or the currency exchange rate but not the CAD value. Setting the currency rate to 1 is not correct and you shouldn't do it as the two currencies are not at or near par. 

This QBO-PayPal data sync shortcoming is a big negative for anyone living outside the United States as a lot of transactions are not conducted in your home currency ... which is probably why you opted to use PayPal in the first place. PayPal handles international payments with no fuss.

I don't understand why the feed can't bring in the transactions by currency - one bank account for each currency. Do the accounting software developers not use PayPal to understand how important this is? WAVE accounting handles it differently than QBO but their method leaves the PayPal account out of balance.

Chat Continues Below Advertisement

One solution would be if QBO let me edit the account it posts to. Wouldn't it be great if the software let the bank feed dump it into one PayPal account but allowed me to edit the transaction so I can select a different payment account. Just sayin'.

 Data Integration Through PayPal API 

This is the BEST OPTION if you use PayPal mainly for sales transactions AND use the U.S. version of QBO. BooHoo for those of us in Canada. The app pulls PayPal data into QBO providing the gross sale amount along with any discounts or PayPal sales fees by customer. It automatically creates a sales receipt in QBO and syncs the transaction details.

As mentioned at the start, read Charlie Russell's article if you want and can go this route.

In July 2017, Intuit changed the bank feed in QBO Canada to bring two transactions for each sale -  the sale excluding the transaction fee as the transaction fee is now booked separately. I haven't tested how it is handling discounts. This is great if you have a lot of recurring sales but ask yourself if you want to pull in data from individual sales of one time customers.

How to Book PayPal Sales Receipts in QBO

If you are in Canada, you are pretty much stuck with the QBO-PayPal data sync bank feed with all its shortcomings if you are not using an app like Hubdoc to fetch and sync the data.

A quick and dirty way to book your PayPal digital sales using the QBO bank feed is to setup a few bank rules in QBO. I personally don't need the customer's name in the QBO customer list but I do need it on the sales receipt. I also need the place of residence for sales tax purposes so for this example I have setup two customers - PayPal Canadian Sales and PayPal Foreign Sales. This allows me to book my GST/HST properly. For this method to work, you have to sell in your home currency and not USD or another foreign currency.

QBO - PayPal Data Sync Example

I sell two eBooks. One is my Bookkeeping Checklists for $5 and the other is my Monthend Procedures for $20. If you buy them at the same time, I offer a discount and you pay $22.50.

As I only have two digital products, I signed into this test QBO company file and went to bank - manage rules and created a few bank rules based on price. The path to get there is: banking> update> manage rules> new rule ... or ....choose Banking on the left hand navigation bar, then choose Bank Rules on the tab on the top navigation bar then select New Rule.

For this example, I chose to setup three rules - one for foreign transactions, another for payments than attracted GST and the last rule for Ontario HST sales. I chose to NOT "automatically add to books" so I can review the transactions before posting. I also left the memo blank so the customer name from the bank feed description will be added to the memo area of the QBO sales receipt.

Once I'm confident the bank rules are working properly, I will probably go in and revise the bank rules to post the transactions automatically. This will reduce the time I spend bookkeeping and reduce data entry errors. But initially, I want a human review prior to booking the transactions.

I decided for these transactions, the best way to setup the rules was on price as there are only two digital products. If there is a sale from another province that doesn't have a 5% GST rate or a 13% HST rate, it will just sit in the feed until I deal with the transaction manually or create a new rule in QBO to deal with it.

PayPal Data Sync Bank Transfers

While you are here setting up bank rules, you should create one to handle your PayPal withdrawals to your bank account (shown below) and another to handle your bank transfers to your PayPal account (not shown below). For this rule, I chose to turn on "automatically add to my books" because it is a straight forward transaction with no alternatives.

If you have your business bank account connected to the QBO bank feed, these transactions should show up as a "match" in that feed. This makes the workflow for the QBO - PayPal data sync seamless.

Bank Rule Applied

Once you have connected your PayPal account to the QBO feed, you should see all your PayPal transactions populate your screen.

For this example, I purchased my Bookkeeping Checklists eBook. The purchase showed up on the QBO Banking PayPal bank feed screen.

You can see below the entry is ready to post. It has created a sales receipt for the customer named PayPal Canadian Sales as well as assigned the GST tax code. The description includes the name of the purchaser. It even tells me what bank rule it applied to create the transaction in the feed. When I click "add" it posts the sales receipt.

The one problem with creating the Sales Receipt from the bank feed, is the accounting software does not create an automatic receipt number. It left the Sales Receipt No. field blank. This is not ideal from an internal control procedure point of view.

As my workaround, I go in and manually assign receipt numbers after the fact. I created a customized report to sort all sales by invoice number so I would know what the "next" invoice should be. Again ... not ideal from an internal control perspective.

The other thing I'm still trying to figure out is how to change the default "product/service" item. Initially it defaulted to posting to a Service Type item it created called Services posting it to the Services income account.

I was able to go in and change the name of the account as well as the income account and assign a default tax code.The applied bank rule will override the default tax code.

It would not let me change the type from Service to Non-Inventory. I can live with that.

Another small drawback from posting entries through the bank feed is that some fields on the Sales Receipt are left blank such as the payment method and the reference no. This means that later if you want to search for or filter a report by payment method ... which sometimes comes in handy if a reconciliation isn't balancing ... these transactions will not show up. It would also be nice to search on the PayPal transaction number in QBO. You'll have to decide if this is acceptable.

If not, then you will need to manually enter these fields ... perhaps at the same time as you are manually entering in the sales receipt number.

Chat Continues Below Advertisement

Note in the example below that the tax amount is set to "Inclusive of Tax". QBO takes the total and calculates the sales tax. If it had been set to "Exclusive of Tax", I would not have been able to post this transaction from the bank feed.

Overall, I'm fairly happy that I have a quick way to post my PayPal sales to QBO with very little data entry on my part.

If the QBO - PayPal data sync method using the bank feed doesn't appeal to you, then read on for another alternative that doesn't involve using an app. This is how I booked PayPal sales when I was using QuickBooks Desktop (QBD).

Another Way to Book PayPal Sales

This method is very low tech and will not appeal to everyone as it does not use a PayPal data sync.

However it is the BEST solution if you don't require every sales transaction to flow into your general ledger. Sometimes more is not better ... it is just more .... more reconciling, more cluttering up your books with one time sales customers ... sometimes it is just more .... and sometimes less is better.

If you have a high volume of PayPal sales transactions, this is a BETTER method for you than app integration IMHO.

What we are going to do is book your transactions in sales summary format.

1. Sign into PayPal and go to More> Sitemap> Transactions> Download transactions.
2. Request a download history file for the period you are wanting to book. Make sure you select "Comma delimited - All Activity". I used to do it once a month but you could do it once a week or even once a day. Your decision should be based on volume of transactions and the associated dollar value. The more money flowing through PayPal, the more frequently you should be posting to QBO if you want access to real time data.
3. Download the PayPal history cvs file and open it in Excel. 
4. Do some sorting or filtering in the file so you can book a monthly (in this example) sales summary. You may have to sort by country so you can pull out Canadian sales that attract sales tax or by individual product sales. I discuss in more detail how I sort the file in my chat on How to Reconcile Your PayPal Accounts. 
5. At this point, I save the csv file as xlsx so I can save my results and attach them to the QBO transaction booked.
6. My preference is to book the sales summary as invoices rather than sales receipt in QBO because sometimes there are items in transit or there may be refunds involved or payments on hold.
7. I book all refunds separately and do NOT include them in the sales summary invoice.
8. I book Canadian sales in a separate transaction from foreign sales so that I can book and balance sales tax correctly.
9. Don't forget to attach the Excel file to your QBO invoice to support the numbers.

If you use this method, DO NOT connect the QBO PayPal data sync (bank feed).

Adjusting Entries Required at Month-End

My QBO - PayPal data sync procedures mean I have to book two adjusting each monthend.

I post a reclassifying journal entry to move my foreign sales to a foreign sales income account because the bank feed only defaults to one service item as mentioned above. I can't do this though the Accountant's Reclassify Transactions Tool because the entries are item-based and the Accountant's tool will only permit a Class reclassification.

I also have to book an entry to expense the PayPal fees because each sales entry is gross and excludes the service fee. This is a good thing as the books reflect sales accurately. It used to book net of fees. I much prefer booking the gross sale amount. 

I get my monthly PayPal fees from my monthly PayPal statement I download. In QBD, I could do this through the bank reconciliation screen. However once you turn on the QBO bank feed, you no longer are able to manually enter the bank fees through the reconciliation window. You have to book the service fees manually.

CAUTION - Are There Risks With Banks Feeds?

This is not talked about much these days but there is a huge risk factor with automatic importing of data. You are most likely violating your online Canadian banking agreement if you do not have a commercial bank account.

It is a pet peeve of mine. All banks are aware that a vast majority of banking customers are allowing third parties read access. Just know, that if you go this route, you are no longer covered for fraud or anything else as you have "shared" your account information with a third party aggregator and it is considered contrary to your agreement to keep your password absolutely confidential.

I think it is shameful in this technological age that the Banks write in the agreement that they can share YOUR data with third parties to provide you with services but they do not vet and approve third parties to allow you to initiate sharing your data. These online banking agreements should have a list of approved third party aggregate/service providers that you are allowed to share read only access to. 

I have noticed ads popping up by RBC. They have created an app to share your data with QBO. All banks should follow their lead and help us protect our access sign-ons while still allowing us to use OUR data without violating their agreement.

Many of the banks will provide your bookkeeper with read only access to your business accounts BUT NOT your personal accounts ... and they won't provide you with a separate read only ID.

I'm not suggesting you don't connect your bank feeds or other document organizing software. I am suggesting however that you assess your risks BEFORE you do and whether you are okay with accepting those risks. PayPal data syncs are okay because you can setup a separate user with limited access through Manage Users.

The Pros and Cons of Apps

I am in total agreement that apps can save time. However two big downsides of apps I see are:

1. The price - each app usually requires a subscription which overtime is eroding your bottom line profit if it doesn't make your workflow more efficient by reducing overall operating expenses or translate to increased sales income ... and we all know companies rarely lower their fees instead opting for increasing their fees annually. Does anyone else see this as becoming cost prohibitive? Is it cheaper to scan and enter manually? Track your time in the new automated process vs. the old process. In some cases, the answer may be yes.
2. Real time-savings - you need to continually evaluate if you are really saving time using technology or does sticking with a manual process make more sense. Monitor the amount of time you spend with tech issues. Is it faster to scan and enter manually? In some cases, the answer may be yes. When automating, remember to factor in the time now required for data review. Instead of entering data, you need a new procedure to review the data.
3. Remember to evaluate the security risks to your business as discussed. Make an informed decision.

I'm a big fan of embracing technology and improving your virtual workflow ... just don't get carried away ... be selective and always check back to ensure it really did improve your workflow.

You will have to decide if using the QBO - PayPal data sync is a good fit for your business ... or if it is better to stay low tech and book your PayPal transactions through a manual sales summary.

Do you know of other ways to get your PayPal data into QBO? If so, please drop me a line and let me know.

QBO Workflow App Integration Chats
Click on any image below to go to the chat.

QuickBooks® is a registered trademark of Intuit Inc. Screen shots © Intuit Inc. All rights reserved.

© 2015 Google Inc, used with permission. Chrome™ browser and the Chrome™ browser logo are registered trademarks of Google Inc. Screen shots © 2015 Google Inc. All rights reserved.

Hubdoc is a trademark of Hubdoc Inc. Screen shots © Hubdoc Inc. All rights reserved.

LedgerDocs is a trademark of LedgersOnline Inc. Screen shots © LedgersOnline Inc. All rights reserved.

Ablii is a trademark of nanopay Corporation © All rights reserved.

Plooto is a trademark of Plooto Inc. Screen shots © Plooto Inc. All rights reserved.

LedgerSync is a trademark of LedgerSync, LLC. Screen shots © LedgerSync, LLC. All rights reserved.

Bookkeeping Essentials › Virtual Accounting Workflow › QBO - PayPal Data Sync