Enter Missing PayPal Fees in QuickBooks
UPDATE: Since this article was published, PayPal has resolved the main issue of separating fees from payment transactions. The feed now imports fees and payments as separate line items that can be mapped to individual ledger accounts. That said, the instructions for manually importing PayPal transaction history may still be useful.
This is a step by step procedure to enter PayPal fees that are missing from the Bank Feed so you can reconcile your PayPal Activity History. If you use PayPal on your website and QuickBooks for accounting, this procedure may save you hours of frustration.
Why aren’t PayPal fees showing up in the Bank Feed?
This is a common question that neither PayPal nor QuickBooks have been able to satisfactorily answer. One theory is that the feed was designed for businesses that only use PayPal for expenses.
The PayPal Sync App, on the other hand, may update fees, but apparently does not let you review and categorize expenses. Neither option is ideal for websites that have large number of transactions on PayPal. More about the PayPal Sync App
If you run a website that receives a large number of orders, it may not be practical to maintain QuickBooks sales entries one at a time as they come in. Unfortunately, the PayPal Feed generates thousands of transactions that do not balance when you reconcile. More about Issues with PayPal and QuickBooks
How Do I Enter PayPal Fees in QuickBooks?
First, read this disclaimer.
Using this procedure, you can not only successfully sync the PayPal Bank register, but also sync PayPal with other Bank registers in QuickBooks.
Important notes are highlighted.
- Reconcile Bank Account Registers other than PayPal first
- Update PayPal Activity History
- Download “Comma Delimited – Balance Affecting Payments” version of the Activity History
- Clean Up Data Files
- Exclude (ignore) pending Bank Feed entries
- Import PayPal Activity History to QuickBooks the first time for payments (income and expenses)
- Import the PayPal Activity History to QuickBooks a second time to record PayPal fees
- Reconcile PayPal Register
Issues with PayPal and QuickBooks
PayPal Bank Feed
- Feed does not include PayPal fees
- PayPal does not reflect a “cleared” balance, so QuickBooks balances do not correspond to PayPal balances by date
- Feed does not include Backup Fund Transfers, so some transactions between other Bank feeds are not recorded
- QuickBooks Cleared Balance does not match PayPal End Balance on Reconcile because of missing transactions
PayPal Activity History
- QuickBooks only accepts 1000 rows at a time
- CSV Amounts contain commas, breaking import
- Payment and Fee in separate columns of single row, not individual rows
PayPal Sync App
- No review before transactions are imported
- Sync does not automatically categorize Transactions
- You cannot use both Sync and Feed at the same time
How Do I Download Activity History from PayPal?
- Log into PayPal.com
- Click “Reports”
- Click “Activity Downloads” (Screenshot)
- Switch to Classic Mode (Screenshot)
- Enter Date Range
- Select “Comma Delimited – Balance Affecting Payments” to preserve “hold” and backup fund transfer transactions.
Do not use “All Activity” (because it includes “authorizations” ) or “Completed Payments” (because it does not include “holds” or backup fund transfers).
- Click “Download History”
Why Does QuickBooks Show an Error When I Import the PayPal Activity History CSV File?
There are two main reasons:
- QuickBooks only accepts 1000 rows at a time
- PayPal CSV Amounts contain commas, breaking import
How Do I Cleanup the PayPal Activity History CSV?
- Open the Activity History CSV file in a plain text editor like Notepad or BBEdit (do not use Word or other “word processing” apps).
- Find any amounts greater than $1000 and remove commas (use find and replace)
- Split into multiple files if there are more than 1000 rows (Copy header row to the first line of each file)
How Do I Import PayPal Activity History into QuickBooks?
- Go to “Banking” in QuickBooks
- Select the PayPal account
- Before you do anything with the PayPal register, select all the pending “For Review” transactions, open the “Batch Action” menu and select “Exclude Selected.” The existing Bank Feed transactions will be duplicated when you upload the Activity History.
- Click the arrow next to the “Update” button (do not click “Update”!) to open the banking menu. Select “File Upload.” (Screenshot)
- Locate the CSV file that you downloaded from PayPal on your computer and click “Next.” (Screenshot)
- Select PayPal from the Bank menu and click “Next.” (Screenshot)
- Verify that the checkbox labeled “First row in .csv file is a header row” is checked
- Select “Date” from the “Date” menu and enter “MM/dd/yyyy” for “Format.” This will be the default.
- Select the “Name” column for “Description.” (Screenshot)
- Select the “Gross” column for “Amount.” (Screenshot)
- Click “Next.”
- Select the transactions to import (all are selected by default). Click “Next.” (Screenshot)
- QuickBooks will load the transactions into the “For Review” panel under the “Banking” page for your bank.
How Do I Record Bank Feed Transactions In QuickBooks?
“Recording” means one of three actions:
- Add: creates a transaction in the Bank register and a corresponding entry in a corresponding QuickBooks account. When you do this, you must select a “Payee” (recipient or sender) and an “Account” (ask an accountant the correct QuickBooks account to use) (Screenshot)
- Find Match: links a pending “For Review” transaction to an existing transaction in QuickBooks. In some cases it QuickBooks may update multiple accounts to complete the link (e.g., Payment Recieved).
- Transfer: Simultaneously adds debit and a credit transaction in two Bank registers but does not use a corresponding QuickBooks account. Use this option for any transactions where you transfered funds from one bank to another. (Screenshot)
Enter PayPal “Hold” Transactions
Record all internal PayPal “hold” transactions before you record any other transactions.
- Sort by “Bank Detail”
- Select all transactions labeled just “PayPal” and batch record them to “Clearing Account.” This will get them out of the way so you can focus on the other transactions.
Enter Bank Transfers
If you are using QuickBooks to manage multiple bank accounts, including PayPal, you may want to record funds moved between them as “transfers” in QuickBooks, not as regular Income/Expense entries.
If you are only using PayPal in QuickBooks, you can ignore this step and simply “Add” the transactions like any other Income/Expense entry.
Otherwise, follow these steps:
- Scan the rows for any “Bank Detail” that mentions another bank.
In PayPal, the description usually begins with “Bank Account.” For other banks, you may need to look carefully at the description to determine if the transaction is a transfer.
- If the transfer is to/from another bank you are managing in QuickBooks AND you have not yet reconciled the other bank’s register:
Click on the row and select “Transfer.”
The dialog will change showing a menu of Banks on the left. Select the PayPal (or the other bank) from this menu and click “Transfer.” This will add an entry into that bank’s register and record the bank as the “Account” in QuickBooks. You will use this entry to “Match” when you record transactions for the other bank later.
- If the transfer is to/from another bank you are managing in QuickBooks AND you have already reconciled the other bank’s register:
Click on the row and select “Find Match” to link it to the existing transaction entry you entered earlier.
- If the transfer is not to/from a bank you are managing in QuickBooks, just “Add” the transaction.
Enter Expense Transactions
- Categorize deductible expense transactions to their appropriate Expense Account Registers (check with an accountant). If you know you have already entered a corresponding Expense, use “Find Match” to link it. Otherwise, use “Add.”
- Categorize non-deductible expense transactions to an Equity Account Register (I use “Member Draws”). This keeps them out of the Profit/Loss report at tax time. Again, if you know you have already entered a corresponding Expense, use “Find Match” to link it. Otherwise, use “Add.”
- Some transactions may be hard to identify, especially debit card and ATM transactions. Many times these only include the address of the merchant or ATM in the memo line. I use google maps to identify the payee by the address.
Enter Income Transactions
- Record income transactions to the appropriate Income Account Register. If you know you have already entered a corresponding Payment Received or Sales Receipt, use “Find Match” to link it.
- Otherwise, use “Add.”
How do I Enter Multiple Bank Transactions at Once?
- Sort by “Bank Detail”
- Select similar rows by checking the box on the left
- Scroll up to the “Batch Actions” menu and select “Modify Selected”
- When the dialog opens, select the appropriate Payee and Account and click “Apply”
- After the page refreshes, open the “Batch Actions” menu again and select “Accept Selected.”
How Do I Add PayPal Fees in QuickBooks?
To make things even more confusing, PayPal reports fees on the same row of the Activity History as the corresponding payment. This fee does not show in the Bank Feed, causing a change in the balance that does not correspond to the register.
- After you are finished recording all the pending “For Review” transactions, follow the steps to import the Activity History CSV file a second time, only this time select “Fee” instead of “Gross” as the column to use for the “Amount.” (Screenshot)
- QuickBooks will ignore any zero values and only add entries for Activity History rows that contain fees. (Screenshot)
- Once PayPal fees are imported as pending “For Review” transactions, you can Batch record them all at the same time to the appropriate QuickBooks Account and your balance will add up correctly.
How Do I Reconcile a Bank Register in QuickBooks?
- After all of the pending “For Review” transactions for a Bank are recorded correctly, click “Go To Register” at the top right of the Banking page.
- Once the page loads, Click “Reconcile” at the top the page.
- Enter the “Statement Ending Date” and “Ending Balance” and click “Ok.”
- If the cleared balance is equal to the Ending Balance, click “Finish Now” and you are done.
- Otherwise, check the cleared items for errors.
- If you cannot make the balances agree, click “Cancel” and review the register for errors.
- If worse comes to worst, you can open “In QuickBooks,” undo all of the transactions withing the time period. You can either try to find the error or just exclude all and start over.
Why Use “Balance Affecting Payments” to import PayPal Transactions?
To understand why the PayPal Bank Feed was not adding up, I had to experiment with reconciling the PayPal register and talk with PayPal’s support. They explained that the balances were changing as a result of “holds” that are placed on the account during the transaction process. Unlike other bank feeds, PayPal does not reflect the “cleared” balance, but instead shows the current pending balance.
If you look at the raw activity (full version) you see the following things occur when a payment is made from your PayPal account.
- Transaction Authorized: The merchant issues an authorization request. This shows up as a debit in the history, but does not change the balance. Do not use the “All Transactions” history when you reconcile the PayPal register, because it would result in a duplicate debit to the account in QuickBooks.
- Hold Placed: PayPal places a matching “hold” on the account in the same amount. This appears as an debit in the history and flags the transaction as “Pending,” but does not appear in the PayPal Bank Feed.
- Backup Funds Transfered: If your PayPal balance drops below zero, PayPal transfers funds from your linked Bank Account. This appears as a credit in the history, but appears as a zero balance in the Activity History. Backup fund transfers do not appear in the PayPal Bank Feed.
- Funds Captured: Funds are sent from your account to the merchant account. This appears as another debit in the history and appears in the PayPal Bank Feed.
- Hold Released: PayPal reverses the “hold.” This appears as a credit in the history and flags the transaction as “Complete,” adn does not appear in the Bank Feed.
Other transactions can be taking place during this process, so at any given time, the PayPal balance does not necessarily correspond to the ledger as it would for a typical bank account. It can take days before PayPal releases a “hold” and a payment is flagged as “Complete.”
By including these hold transactions in your QuickBooks bank register it is much easier to follow the balance and identify any mistakes you may have made entering Expenses, Payments, Sales Receipts or other transactions.
When reconciling PayPal, you must use a statement close date and balance that occurs before any “holds” have been placed and after any “holds” have been released or the cleared balance will not agree with ending balance. If the register does not reconcile, you may need to open the CSV in Excel or a text editor to find a position different from the ending balance in the feed.
Delete Excluded Entries
If you clean up invalid “Excluded” transactions, you can delete them forever by opening the “Batch Actions” menu and selecting “Delete.”
Why not use the PayPal Sync App to record PayPal Fees?
When you request to install the App, QuickBooks online makes you fill out a survey and, in our case, responds with the following:
Since you have expenses in PayPal, we recommend using our PayPal Bank Feed integration. You’ll get the benefit of autocategorization of your expenses and the opportunity to review all your transactions before they hit your books.
If you instead wish to use Sync with PayPal, your expenses will be not be categorized and you will not have an opportunity to review the transactions before they are imported into your books.
Our new integration will combine the best of both integrations and if you have provided your contact details, we’ll reach out when it’s ready!
Based on this message, QuickBooks may be working to resolve the issues outlined in this article. We can only hope.
The steps described here should not be considered accounting advice. Consult with your accounting professional how to record transactions correctly for your business.