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.
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.
You have three PayPal data sync options to QuickBooks Online. The best option for you will depend on how you are using PayPal.
I will also chat about a low-tech method to book PayPal transactions.
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.
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'.
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.
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.
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.
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.
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.
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).
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.
If you use this method, DO NOT connect the QBO PayPal bank feed.
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.
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.
I am in total agreement that apps can save time. However two big downsides of apps I see are:
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.
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.