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

by L. Kenway BComm CPB

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. 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

Publisher's Note February 26, 2017 

Wow the downside to software as a service is things change that can screw up procedures you have in place. At the beginning of February when this article was written, PayPal data sync amounts were imported before PayPal service fees. Mid-February, the PayPal data sync began importing net of service fees. 

This is a problem because if you have sales which attract GST/HST, the sales tax is not booked corrected. 

For zero rated sales, your work around would be to still book the net sales amount. At month-end, reclassify the associated PayPal service fees.

I will have to see if I can come up with a work around for the Canadian sales. I am very disappointed with how the QBO-PayPal data sync works.


Article published February 2, 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.

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.


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.
  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.


PayPal Data Sync Through QBO Bank Feed

This is the BEST OPTION if you use PayPal mainly for expense transactions 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.

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 QBO. I just 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: transactions> banking> update> manage rules> new rules.



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 bookkeeping 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 don'ts have a 5% GST rate or a 13% GST 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 and another to handle your bank transfers to your PayPal account. 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. 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. However it is the BEST solution if you don't require every transaction to flow into your general ledger. 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.
  5. At this point, I usually save the csv file as xlsx so I can save my results and attach them to the QBO transaction booked.
  6. I usually prefer to book the sales summary as invoices rather sales receipt in QBO because sometimes items are in transit items or there may be refunds involved or payments on hold. I book all refunds separately and do not include them in the sales summary invoice. I also book Canadian sales in a separate transaction from foreign sales so that I can book sales tax correctly.

If you use this method, DO NOT connect the QBO PayPal 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.

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 initiate to share your data. These online banking agreements should have a list of approved third party service providers that you are allowed to give 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 now provide your bookkeeper with read only access to your business accounts BUT NOT your personal accounts. 

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.


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.
  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.
  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 the PayPal transaction 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.






6 Part QuickBooks® Primer
Click on any image below to go to the chat.

How to use QuickBooks to create customized reports.

Part 1

Accounting System Flowchart

Learn How To Customize Reports

Closing Procedures, Tracking GST/HST/PST

How to record a foreign wire transfer in QBO

Part 2

Recording Foreign Wire Transfers
in QuickBooks Online 


Part 3

Setup Meals Sales Tax Code
in QuickBooks Online Canada

Differences/Limitations

Part 4

Setup Self Assessing PST Codes
in QuickBooks Online 

Part 5

QuickBooks Desktop to Online
Conversion Problems 

Learn How To Use QuickBooks

Part 6

Beginner's Tips on A/R, A/P, Banking

Period End Closing Adjusting Entries

Shortcuts, QBO apps, Beginner's FORUM

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

Click here to subscribe to QuickBooks Online Canada. I do not receive any commissions for this referral.



Enjoy A Tea Break With
Me Today. Let's Chat!

Use the search feature to quickly find the
information you're looking for.

Like this page? Please let Google
know by clicking the +1 button.






 Keep Your  eDocuments Safe And Secure… $7 USD
Version 2 now out
Bigger and Better



SHORTEN
Your Learning Curve!

Buy Now



Warm up your tea cup and check out these chats ...

Click on image above to read the chat.






Purchasing These Products Through My Links Helps To Support This Site

Click on image above for my review.






Join Me On Facebook

Help support this site by "liking" me! Here's where I post current information.

Listed Under Websites NOT Local Business.
This website is not associated with the business operating in Bonnyville AB.






A Handy Reference

Buy Now