Whilst there is a PayPal feed available on QBO, we recommend you do not use it. If you already have it, please can you deactivate it. The problem with the feed is that one PayPal can transact in a few different currencies. However, when a feed is set up, £100 will go into QBO as £100, but $100 will also go into QBO as £100, which causes serious problems. Therefore, please can you upload the statements using the PayPal upload process below.
Note: you will need a separate PayPal account on QBO for each currency that you use on PayPal, e.g. PayPal USD, PayPal GBP etc. Create these with the type of Cash at Bank and In Hand and the detail type of Cash on Hand. You can have a look at the video (2min) here illustrating the process.
Uploading PayPal statements
See this video (10min) for details of this process
A PayPal statement will likely have more than one currency showing in the same sheet. You of course need to split these into the different currencies and upload to each separate PayPal currency account that you have set up on QBO.
This process is shown below:
- Copy the PayPal statement file and rename ‘PayPal manipulated YYYY.MM’
- Open the ‘PayPal manipulated YYYY.MM’ file
- Save file as XLSX file type.
- Delete all ‘Memo’ transactions from the ‘Balance Impact’ column, if this exists, as these transactions don’t impact the balance and therefore should be removed. Note that this column is often not on the statement, so you can skip this step.
- Copy the date column and paste directly under date data
- Copy the currency column and paste directly under currency data
- Copy the fee column and paste directly under gross data
- Add ‘Paypal fees’ to the description of the new rows
- Add a filter on all data, then select zeroes in ‘Gross’ column, and delete all zeroes.
- Add in a column in column B with the heading ‘Full Description’. Add the formula to combine ‘Description’ and ‘Name’ columns with a hyphen in between, using the formula: =Description&”-“&Name (see video for details). Copy this down to the end of the data.
- Filter by each currency at a time, copying all data and column headings to a new worksheet in the same workbook. Name the worksheet the currency code and month, e.g. GBP 2020.04, USD 2020.04 etc.
- Save the document.
- Go to first currency worksheet and save as file type of CSV Comma Delimited, and file name of worksheet (e.g. GBP 2020.04). Accept warnings
- Repeat above step for all worksheets.
- Upload to QBO. Ensure the following:
- You upload to correct account
- You use the ‘Full Description’ column to upload
- You use ‘Gross’ for the amount
- Reconcile each PayPal account, simply by using the end balance for each currency. If it doesn’t reconcile, resolve before moving on. Note, if the balance is incorrect, check that you deleted the ‘Memo’ transactions in step 5 above. Note: this step must not be missed. See this video (4min) for details of this process
- Repeat the QBO upload process for each currency.
Note: If the PayPal CSV file’s format of information is not as required (i.e. it’s showing all in one column), then:
- Open a new workbook, under the ‘Data’ tab select ‘From text/CSV’ then select the PayPal statement required.
- Select the relevant delimiter (i.e. comma if it’s a comma between the date and description)
- Save as ‘PayPal manipulated YYYY.MM’ (Continue at point 3)
Transfers between PayPal currencies
In QBO, matching transfers in different currencies must be done manually. This isn’t therefore very easy to do on PayPal, as there may be a large number of these ‘General Currency Conversions’.
As such, we recommend you create bank rules to add all ‘General Currency Conversion’ transactions to the ‘Exchange Gain / Loss’ account. If both sides of the conversion are added here, they will mostly net off against one another, leaving only the actual exchange gain / loss.
This is done automatically once the bank rules for ‘General Currency Conversion’ is set up as below.
Transfers to / from PayPal
PayPal accounts are generally linked to one payment method, such as a debit or credit card, or a bank account. It is important for you to know which account your PayPal is linked to:
- If it is linked to a business debit / credit card or bank account, then you’re going to be able to see both the money into PayPal, and the money out of the business account.
A bank rule would be required on the PayPal account to create a transfer in the relevant corresponding account.
- If it is linked to a personal debit / credit card or bank account, all transactions in and out of the PayPal to that account should be shown as DLA.
A bank rule would be required to add these transactions in / out as DLA.
Whilst we do not want you to have a bank feed for your PayPal account, it may be that you use the QBO integration with PayPal. This allows you to raise invoices in QBO, selecting ‘Pay by Debit/Credit card’ on that invoice, which then gives your clients a link to enter their card details online and pay.
When this integration is set up, there will be a ‘PayPal Bank’ account set up, as well as a ‘PayPal fees’. The PayPal bank will be in GBP. You should then ‘adopt’ this PayPal Bank as your PayPal GBP, rather than using a separate account.
When amounts are received in this way, a transaction will be entered automatically into this account for the income and fees. Therefore, income of this kind should be excluded before uploading the PayPal Statement, to avoid duplication.
To do this, find transactions with the description of ‘Express Checkout Payment’ and delete these from the statement before manipulating and uploading.
We recommend that you use Bank Rules to make the bookkeeping process more automated.
All bank rules should be set up with the following criteria:
- Bank accounts: select all PayPal accounts
- Any / All: Any
- Automatically add to my books selected