In this article

    PayPal import *.csv (Banana 9)

    Import
    Universal

    This import script (Banana Extension) lets you import in Banana Accounting your PayPal transactions.

    Newer version: PayPal Import *.csv (Banana+)

    This import script (Banana Extensions) lets you import in Banana accounting PayPal transactions.
    PayPal transaction files pack a lot of stuff: different transaction types; different amount types (Gross, Net, Fee) and different currencies. The download format (code page, language, international settings) also depend on the user parameters.

    Characteristics

    Banana import script for PayPal:

    • Translate a PayPal file (download history) in accounting transactions suitable to be imported into Banana Accounting
      • with Debit and credit amounts or income or expenses for Income and Expense accounting.
      • with single or multi-currency

    The PayPal script:

    • Enables users that have a PayPal account to import their data directly in their company accounting kept with Banana Accounting.
    • Allows companies or CPA's with an existing accounting solution to use Banana to keep a separated accounting for PayPal and take the summary data weekly or monthly to update the main system (See section following).

    Separate accounting for PayPal transactions

    PayPal transactions are usually composed of small amounts and have many particularities (refund, fees, etc.).
    It can be expensive and time consuming to handle all this work within the main company accounting. 

    We have prepared different template that you can immediately use:

    In many cases, it is more appropriate to manage the PayPal transactions within a separated accounting file and once a month integrate the summary data (movements for the month) into the main accounting.

    This solution also allows a third person to take responsibility for the PayPal accounting.

    If you use Banana for a PayPal Accounting solution, you can: 

    • Create an accounting file for a specific PayPal account
    • Have a Chart of accounts with
      • A different account for each currency used
      • Different accounts for Income and Expenses
    • Import the PayPal transactions and have all accounts, expenses, income and PayPal fees automatically allocated, and currency converted into basic currency.
    • Record the transactions on the appropriate accounts for
      • Bank transfers
      • Revenue with different Sale tax codes
      • Different expense types
      • Refunds and charge backs
      • Adding other information for special reporting (Cost Centers, Segments, Customers, etc.)
    • Regularly (week, month, quarter) integrate the data into the main accounting, either by
      • Printing a summary and recording manually
      • Or exporting the data for the period and import them into the main system
    • At any time, print the journal and necessary reports for auditing or control.
    • Eventually, in case you have a lot of transactions, you can customize the import filter (Javascript) so that certain transactions are registered onto specific accounts numbers.

    Within the Banana PayPal accounting you can easily keep the whole history, have instant access to all the data necessary to give a good customer service.

    Activate the import PayPal transaction

    First you need to update the import filters in Banana Accounting. See also Manage extensions.

    • Start Banana Accounting 9
    • Open your accounting file
    • Menu "Account1 → Import to accounting"
    • Select "Import transactions"
    • Button Manage filters
    • Button Update filters
    • Check the "PayPal (*.csv)" and deselect the other filters you do not need.
    • Close the dialog.

    Download PayPal transactions

    PayPal customize fields to download

    Be careful that the PayPal standard download file does not contain all the fields necessary for the import into Banana.
    You need to change the settings in PayPal so that you can have a correct download.
    • Login into PayPal
    • Go to Activity → Transaction list → Activity export
    • Before downloading the PayPal history file click on "Customize My History Download" and make sure that all required field are downloaded or select all fields available in PayPal.
    • Select the date range or the last movements
    • In "File Types for Download" select "Comma delimited - Balance Affecting Payments"
    • Save the file on your computer

     

    PayPal language encoding setting 

    If you use a local encoding, make sure that the encoding reflects your local computer encoding.

    Banana import works better if you set your file encoding in UTF-8.
    To change your language encoding settings in PayPal:

    • Login into PayPal
    • My Account → Profile → My Setting tools
    • PayPal button Language encoding (on the bottom of the page under More setting tools)
    • Eventually More Options is where you can set the UTF-8 in the list box

    Import PayPal transactions

    See also import transactions

    • Open your accounting file
    • Menu "Account1 → Import to accounting"
    • Select "Import transactions"
    • Select "PayPal"
    • In File Name, enter the path and file name of the PayPal download (or Browse)
    • Click OK.
    • Choose the date format for the PayPal date format.
      "Computer default" will use your computer setting.
      If you get the error "Date Format not valid" open the file you have downloaded from PayPal with notepad and check how the dates are written and then choose the appropriate format.
    • Enter the Account number for the PayPal Account (PayPal Account)
    • Enter the Account number where Income should be registered (PayPal In)
    • Enter the Account number where Expenses should be registered  (PayPal Out)
    • Enter the Account number where PayPal Fees should be registered (PayPal Fee)
    • Select the period you want to import and confirm

    Once you have imported the transactions, you can undo the operation or change the imported data.

    This will be the case if you want some income or some expenses to be registered on a different account..

    Adapt your accounting file for import

    Instead of specifying the PayPal account when the script starts, you can indicate in the Chart of accounts of Banana which accounts it should use. This method is also necessary if you want to have different accounts for each currency.

    • The Basic currency in File properties should be set to a valid currency.
    • Prior to import, the program will look for and use accounts with the following description:
      • "PayPal Account": the account for PayPal
      • "PayPal In": the account for income
      • "PayPal Out": the account for expenses
      • "PayPal Fee": the account for the PayPal fees.
    • Set the opening balance of the PayPal accounts.

    For each currency, you can specify an account. All transactions for this currency will be recorded on the specified account. You also will have the currency conversion transactions between the various currencies.

    • For an accounting file NOT in multi/foreign currency, insert the currency code into the account description:
      • PayPal Account EUR
      • PayPal Account USD
      • PayPal Account CHF
      • PayPal Account GBP
    • For an accounting file with multi/foreign currency, the program will look for the account where the description begins with "PayPal Account" and will then use the currency symbol of the Currency column.

    If no account has been specified for the currency, the amount will go to the basic currency account.

    PayPal currency settings and accounting settings

    It is suitable that your PayPal currency settings (My Account → Profile → My Money→ PayPal Balance → Currencies) are the same as the ones of the accounting file:

    • PayPal "Primary currency" is the same as your Basic Currency.
    • If you have set up PayPal to use more currencies (Add Currency) you should use the Double-entry accounting with Multi-currency.
      You will be able to keep a separate account for each currency in the original value.
      You can convert an existing accounting file into a multi-currency file with the Convert to new file command.
    • For multi-currency accounting, the accounts "PayPal In", "PayPal Out", "PayPal Fee" should preferably be in basic currency.
       

    Transaction split and selection

    Each PayPal transaction includes the Gross amount, the Fee amount and the Net amount. If the Net is different from the Gross, the program will generate 3 lines:

    • Line with the Gross amount registered on the PayPal Account
    • Line with the Net amount registered on the Income or Expense account
    • Line with the Fee amount registered on the Fee account.

    Transactions already imported

    The columns "ExternalReference" of the Transactions table will be filled with the unique PayPal transactionsID.

    The next time you import, transactions with the same ID will be excluded from the import. The information from the older transactions will be used for currency conversion and grouping.

    Last balance

    The import will also add a line with information regarding the last PayPal Balance for each currency used. If you do not want this information, delete the imported lines.

    Modifying the imported transactions

    Once the transactions are imported, you will be able to change the transactions, as you like.

    • Record the transfers to and from the bank account on a "Internal transfers account (Giro bank account)".
      Do the same when you import your Bank account transactions.
      The balance of the "Internal transfers account (Giro bank account)" should compensate and go to zero.
    • Income from sales can be registered on specific accounts based on the sales tax applied or based on the type of sales.
      You must adapt your accounting plan accordingly.
    • You can add segments or cost centers.

    Adapt the View of the Transaction table so that you have all columns visible and in the best sequence for modifying the data. 

    Exchange rate profit and loss

    The balances of the PayPal accounts in currency will match the balance of the PayPal account.

    The balances in basic currency may be different due to:

    • The exchange rates for the transactions vary over the time.
    • PayPal has different exchange rates for selling or buying a specific currency.

    You should from time to time adjust the basic currency with the command Recording exchange rate differences.

    Multi currency conversion

    If there are different transactions in different currencies in the file, the amounts will be converted into basic currency following this rule and order:

    • Basic currency 
    • PayPal currency conversion
      Transactions that belong to the same block will use the same conversion, so that the amount in basic currency for the same amount and transaction will always be the same.
    • Exchange rate from currency conversion of the same date
    • Exchange rates taken from the Accounting file (Exchange rates table)
    • Exchange rate collected from the PayPal currency conversion.

    The conversion will give an indication when it could not find a valid exchange rate for a currency and ask for a currency exchange rate.

    Import other PayPal fields

    You can import other information, included in the PayPal file, into Banana Accounting.

    • Add a new column to the Transactions table with the name "PayPal" + the column name of the PayPal file.
      For example "PayPalTimeZone", "PayPalSalesTax", "PayPalTip".
      • Specify the column type (Text, Number, Amount, Date, Time).
      • For Amount and Number also indicate the decimal place.
      • In the Account Card, the columns of the Amount type will also have an end total.
    • In the PayPal download history with "Customize Download Fields", you can specify the column to download.
    • The available columns (names in English, without space and in CamelCase) are:
      Time, TimeZone, Type, Name, Status, Subject, Currency, Gross, Fee, Net, Note, FromEmailAddress, ToEmailAddress, TransactionId, PaymentType, CounterpartyStatus, ShippingAddress, AddressStatus. ItemTitle, ItemId, ShippingAndHandlingAmount, InsuranceAmount, SalesTax, Tip, Discount, SellerId, Option1Name, Option1Value, Option2Name, Option2Value, AuctionSite, BuyerId, ItemUrl, ClosingDate, ReferenceTxnId, InvoiceNumber,SubscriptionNumber, CustomNumber, ReceiptId, Balance, ContactPhoneNumber, BalanceImpact, AddressLine1, AddressLine2, State, Town, Zip, Country.

    When you import the transactions, the PayPal* column cells will be filled with the contents of the PayPal file.

    Banana PayPal import script

    The conversion from the PayPal format to the accounting format is a Javascript program.

    The program is "open source software" and can be modified to suit the user's needs at best.

    You can then import the modified script, and use the one you have modified.

    To modify the script, first change the following values so that it does not conflict with the Banana PayPal filter:

    • Use a different file name
    • Use a different @id 
    • Use a different description.

    License terms

    The PayPal documentation does not cover all possible transactions. The import script has been developed based on use cases, and it is possible that not all possibilities have been covered and that the script need improvements.

    PayPal may also change the data format and the script results may not be the same.

    Take care using the import and always check that the Account Balance corresponds to the PayPal balance.

    The use of the import script is governed by the Apache 2.0 license, the file can be freely modified.

    By using the script you accept the license and the disclaimer terms.

    Troubleshooting

    If you experience any problem first update the import filter to the latest version in Manage apps.

    • Rounding problem.
      It can happen that the PayPal file use a decimal format that is different from the one of your computer. Amount are therefore truncated (12.34 become 12  or it become 1234).
      PayPal assign you a specific format for the language encoding based on you country (see PayPal language encoding explanations).
    • The error message "Impossible to translate file header: .."
      • Try using the Banana Accounting 9 version.
      • Change your download settings as specified above in the "PayPal language encoding setting"
      • Assure you are using the original CSV file downloaded from PayPal and not one opened and saved with Excel.
    • The error message "Currency non converted" indicates that there has been no available exchange rate for the conversion.
      • It is possible you have not set up your basic currency
      • You have set up the PayPal to keep more than one currency. The PayPal file does not convert currency and you should therefore use a multi-currency accounting.
    • If the last balance is different from the account
      • Check whether you have a correct opening amount (the balance prior to the first imported transaction)
      • Your account is not in the same currency.
        If your PayPal account is set up to use different currencies or in a currency different from basic currency, it would be suitable to use a multi currency account.
    • If you get a message field "BalanceImpact not found in headers" or the same message with another field name, is due to the fact that the PayPal History file does not contains a field that is needed for import.
      • Login in PayPal and go to download history
      • Before downloading the PayPal history file click on "Customize My History Download"
      • You will see a list of available field and check the one that is missing
        The "Balance Impact" is near the end of the list.
        PayPal will save the setting. Next time you download again the field will be included.
      • Download again the history file.
    • If you have an error message "AccountDebit" and "AccountCredit" not found, you have a file with old columns name.
      use the command Tools->Create New File to create a copy of the file with the correct columns.
       

    Reference

    PayPal Documentation

    Changes

    • 2014-08-27 First release
    • 2014-10-04 Version
      • Can now specify the date input format
    • 2016-01-20
      • Added support for separator ";"
      • Better language detection and error message
    • 2016-08-19
      • Adapt for new field name for  German and French 
      • German change Balance impact from "Gutschrift" to "Gutschrift/ gutschreiben"
      • Account input inform that  empty string exit
    • 2016-12-07
      • Adapt for removal of BalanceImpact field in the new "Activity download"
      • Use "General Currency Conversion" instead of "Currency conversion"
      • German "Uhrzeit" instead of "Zeit"Adapt for new field name for  German and French 
    • 2018-02-16
      • Adapt for change of the new German text "Haben / Soll"
    • 2018-06-19
      • Adapt for change of the new German texts

    Feedback

    Let us know what you think of the import script or how we could eventually improve the script.

    Help us improve the documentation

    We welcome feedback on how to improve this page.

    Tell us what theme needs a better explanation or how to clarify a topic.

    Share this article: Twitter | Facebook | LinkedIn | Email