About Lloyd's Free PayPal/Chase/BNCR CSV to QIF Online Converter

The PayPal/Chase Bank/BNCR CSV to QIF converter requires an encrypted connection, and therefore is hosted on my company website.  Please first read the below, then convert your PayPal/Chase/BNCR CSV files. To convert a CSV file generated by Chase Bank, the file must be named 'chase.csv' (case-insensitive).

About This Tool

Amazingly, PayPal's QIF history download does not contain all transactions affecting your balance. Transactions which are refunded or partially refunded, as well as temporary holds, do not appear at all in the PayPal QIF history download! This glaring error makes the PayPal QIF history download practically useless to import transactions into business accounting software such as gnucash.

Fortunately, PayPal's CSV (comma-separated value) "balance-affecting transactions" history download DOES contain the transactions missing from PayPal's QIF history download. However, gnucash (and probably other accounting software) cannot import CSV files, at least not easily.

This tool allows you to easily convert your PayPal CSV "balance-affecting transactions" history download to QIF format, ready to import into gnucash or other accounting software. Note that gnucash QIF import wizard "remembers" the proper expense and income accounts for your frequent transactions, whereas I believe Quickbooks does not.

Chase Bank support was added because the QIF files generated by Chase do not (for me) import into Gnucash. To convert a CSV file generated by Chase Bank, the file must be named 'chase.csv'.

NEW: BNCR (Banco Nacional de Costa Rica) support added.

Security

I have gone to lengths to make this a safe, secure tool. Data is encrypted via SSL (Secure Socket Layer) as it is transmitted to and from my server in USA. The QIF files are stored using a scrambled random filename, and are automatically removed from the server after 30-35 minutes. In my opinion, if you trust SSL to transmit your data over the Internet (which you did to download your CSV transaction history file from PayPal), you can trust this tool with your financial data. If this doesn't seem secure enough for you, you can download the standalone perl script (see Standalone PC Version below) and convert the files on your own computer.

The usual disclaimer applies: neither I nor Tropical Health Foods LLC will assume any liability for losses of any kind, real or imagined, resulting from the use of this tool.

Use Instructions

To use this tool, you must first login to your PayPal account and download your "balance affecting transactions" in "Comma delimited" format for the date range you want.  Comma delimited format is the same thing as comma separated format, also referred to as CSV (Comma Separated Values).

Note that the "History Download" available in PayPal "Reports" will not work, since it contains only transactions with a status of "Completed."

What you need are the "balance-affecting transactions."  To download these, you need to get to the PayPal History Download Page, as follows:

  1. log in to your PayPal account
  2. click the "History" PayPal menu link
  3. hover your mouse over "More" in the upper right corner of the transaction list. You should see a pulldown selection. Select "Download History."

On the PayPal History Download page, choose "Custom Date Range" as download type.  Under "file types for download," choose Comma-delimited, Balance Affecting Payments."

The file you download from PayPal will be the CSV file that this tool will convert for you to QIF format.

Note that you can convert several CSV files at once.  When you have downloaded all the PayPal CVS files you need to convert, you are ready to Convert your PayPal/Chase/BNCR CSV files  The converter should correctly convert PayPal CSV files that have been opened and edited with MS Excel (Windows) and OpenOffice Calc (Linux/Mac/Windows).

Standalone PC Version

The PC version, a GNU-GPL-licensed perl script, is offered for those who prefer not to use this web-based tool, or for those who need to convert CSV files larger than 2.5 MB.  (This online converter is limited to files under 2.5 MB.)  It is able to convert multiple files via wildcard.  Perl is required and is available for both Windows and Linux. It is generally installed by default in Linux. I cannot help you install perl in Windows, but I suggest http://strawberryperl.com/.

Download directory for the standalone version.

Feedback

Please leave your questions, comments or suggestions as a comment to this post (see below).  Or, you can send me a message.

Donations are Appreciated

Donations help me pay my server expenses in order to provide this service.


 

Update History

Jan. 3, 2017 (v2.16)

Added BOM marker detection (PayPal now uses it)
Added PayPal transaction types "Direct Credit Card Payment" and "Payment Refund"

 

June 28, 2013 (v2.15)

  • Made sales tax a liability rather than an expense.  Any transaction containing sales tax gets a "Sales Tax Payable" split, and the sales tax amount is not included in the gross sales.  See http://blog.accountingcoach.com/sales-tax-liability/  See also sales tax notes on the converter page.

June 26, 2013 (v2.12)

  • Added "Express Checkout Payment Received" and "Mobile Express Checkout Payment Received" as specially-handled PayPal transaction types

Feb. 9, 2012 (v2.11)

  • Fixed broken BNCR support
  • Fixed transaction description line.

Feb. 9, 2012 (v2.10)

  • Added support for empty PayPal 'name' field
  • Fixed broken multilingual support for PayPal fieldnames

Jan. 29, 2013 (v2.09)

  • Added support for 'Item Title' field, to be appended to gnucash transaction description
  • Abbreviated PayPal special transaction types, so they don't take up so much space in the gnucash transaction description line (to allow room for 'Item Title')
  • Added "Payment Received', 'Website Payments Standard', and 'Request Sent' as specially-handled PayPal transaction types

Dec. 2, 2012 (v2.08)

  • Added support for BNCR (Banco Nacional de Costa Rica) CSV files

Sept 8, 2012 (v2.06)

  • Added support for sales tax field as a split.

July 11, 2012 (v2.03)

  • Added multiple currency support.

Jan. 18, 2012 (v2.02) (web version only)

  • Fix to allow duplicate filenames in multiple csv file uploads
  • Increased maximum csv files per batch to 4

Jan. 17, 2012 (v2.01)

  • Fix to allow parsing column headers mangled by OpenOffice Calc

Jan. 17, 2012 (v2.00)

  • Major rewrite of conversion engine to correct parsing errors caused by embedded commas and odd distribution of quote marks.  Now uses perl module Text::CSV (http://search.cpan.org/~makamaka/Text-CSV-1.21/)
  • Added support for Macintosh and Linux/Unix line endings.  Should accept CSV files edited by OpenOffice and MS Excel.
  • Removed superfluous paths in zip download.

Jan. 12, 2012 (v1.24)

  • Modifications to possibly allow processing of CSV files edited by Excel before conversion to QIF (you shouldn't do that, use a simple text editor like notepad.exe instead).
  • Increased maximum CSV file size from 1 MB to 2.5 MB, decreased total files per batch from 5 to 2.

 

Dec. 8, 2011 (v1.22 and v1.23)
Add special handling to for the following PayPal transaction types:

  • PayPal Express Checkout Payment Received
  • Subscription Payment Received
  • Withdraw Funds to a Bank Account
  • Web Accept Payment Received
  • Update to eCheck Received

Dec. 7, 2011 (v1.21)
Updated perl conversion script to handle the new JC Morgan Chase Bank csv format, which now includes a fieldname header line similar to PayPal's csv file. Backward compatibility with headerless Chase cvs files is conserved.

May 21, 2011
To download the standalone version, users now must send me a request for the username/password for the protected download directory.

May 9, 2011
Made the PC perl script GNU-GPL and offered it here for download.

April 6, 2011
Add special handling for "Update to eCheck Received" and "eBay Payment Sent" transactions.

March 18, 2011
The converter now adjusts the QIF 'type' field of several common Paypal transactions, in order to allow gnucash's QIF file importer to properly "remember" the transaction type.  These transactions are: Shopping Cart Payment Received, Website Payments Pro, Refund, Withdraw Funds, Virtual Terminal and Temporary Hold.

March 10, 2011
Initial Release

Comments

Need a sample CSV of certain transactions.

Hello, Lloyd. Came round here while surfing for information on importing PayPal transactions into GnuCash. Tried out your converter, and it worked fine for the previous 2 years' worth of transactions. Trouble is, my full transaction list, sporadic as it is, stretches all the way back to 2008, and PayPal won't let me download a full history of those transactions. What I intend to do is modify the CSV I previously downloaded from them, using Excel, to recreate the entire transaction list from scratch. I have managed to scrape together a rough file of the transactions in a non-CSV format, but it's not as comprehensive, field-wise, as the CSV PayPal supplied. Which brings me to my request for help. Do you happen to have a sample CSV (or several) containing the following kinds of transactions: 1. PayPal credit card confirmation refunds (that one was for when I linked a card to my PayPal account). 2. Refunds and fee reversals (a friend sent me some money to get a transaction done, but I refunded it in full when I couldn't conduct the transaction from my end). 3. Withdrawals to, and transfers from, a credit card (I live in Malaysia, and I made some transactions in MYR that were converted to USD, and vice-versa). 4. Subscription creations and cancellations (there was a recurring transaction, but I stopped it after a few months because I couldn't afford it at the time). 5. Currency conversions (I haven't exactly got the hang of them in Gnucash, so I figure that if I have an example to work with, I can understand things better). I apologize in advance if it seems like I'm asking for too much, but I believe that if I have a template to work with, then I can recreate the full transaction list and update my records in GnuCash. I really would appreciate your help on this one; I have contacted PayPal and asked them nicely for a full CSV with balance-affecting transactions about a week ago, but they said that they didn't have that feature or something. The fact is, it might seem like more trouble than it's worth, but if I can get it done, it might just work out right. Thanks for your help, Lloyd, and I hope you have a good day.

Hello,   The converter

Hello,
 
The converter currently only has "special handling" for the following PayPal transactions:

Website Payments Standard
PayPal Payments Standard
Website Payments Pro
PayPal Payments Pro
Express Checkout Payment Received
Web Accept Payment Received
Shopping Cart Payment Received
Virtual Terminal Transaction
Refund...
Withdraw Funds
Temporary Hold
Update to eCheck Received
eBay Payment Sent
Payment Received
Request Sent
Donation Received
 
In addition, "currency conversion" transactions should work OK.
 

By "work" and "special handling", I mean that the converter will create the QIF so that when imported into gnucash, the transaction "type" will be set to one of the above PayPal transactions, rather than to the name of the person involved.  This allows gnucash to avoid proposing to create a separate new account for each transaction at import time.

You request a sample csv file with the transaction types you listed.  Is that so you can confirm the proper names of the PayPal transactions you listed?  I don't have example csv files for all your the transactions types you listed.  I do have examples of currency conversion transactions and refund transactions.  Here are some lines from a csv file of mine of last year, with refund transactions and currency conversion transactions:

CSV header line:
Date, Time, Time Zone, Name, Type, Status, Currency, Gross, Fee, Net, From Email Address, To Email Address, Transaction ID, Reference Txn ID, Receipt ID, Balance,
...

"3/28/2013","12:13:04","PDT","Sears Holding Corporation","Refund","Completed","USD","109.87","0.00","109.87","paypal.sears@searshc.com","","72485914D6343914T","1HG67880U8819502E","","1,690.17",
"3/26/2013","11:41:37","PDT","Narcy Kwagala","Refund","Completed","USD","-8.95","0.22","-8.73","","","3KH06010LT952250J","5K2097565N626422U","","1,591.60",
"3/26/2013","11:21:55","PDT","Narcy Kwagala","Website Payments Pro API Solution","Partially Refunded","USD","112.96","-3.12","109.84","st___@gmail.com","lloyd@___foods.com","5K2097565N626422U","0YR47287KM9601409","4619-1720-1241-2153","1,600.33",
 

Currency conversion transaction, same header:
"8/22/2013","21:22:48","PDT","Alexander Willner","Web Accept Payment Sent","Completed","EUR","-9.00","0.00","-9.00","lloyd@____foods.com","donations@openpgpjs.org","27G546529F588714V","","","0.00",
"8/22/2013","21:22:48","PDT","From U.S. Dollar","Currency Conversion","Completed","EUR","9.00","0.00","9.00","","","29W98297K6809593J","27G546529F588714V","","9.00",
"8/22/2013","21:22:48","PDT","To Euro","Currency Conversion","Completed","USD","-12.35","0.00","-12.35","","","4PY902193W6378833","27G546529F588714V","","99.22",
 
If you can find out from some other source exactly what the names of the PayPal transactions are that you want to construct, just use the CSV header to put the info in the correct place, and send the exact transaction names to me so I can add them to the supported PayPal transactions.

 

Might need to ask PayPal for some of those transaction fields.

Thanks for the quick reply, Lloyd. Some transactions (on PayPal's site) lack bits of information - the credit card refund for PayPal validation, for instance, doesn't seem to have "From Email" and "Counterparty Status" entries - and it does seem a bit tough to reproduce the corresponding CSV entry for them. But I'll do my best to look them up. Maybe that might help with further updates to the tool. PayPal is currently having DNS issues at the moment, though. I'll check again later.

For use with my converter,

For use with my converter, you don't need the "From Email" and "Counterparty Status" fields.  You only need the following fields:

  • Date
  • Net
  • Type
  • Gross
  • Fee
  • Name
  • Status

Unrelated, but do you use a CSV editor?

Thanks for the earlier information. I'll put it to good use. Now that I know that only a few fields are necessary for the converter, that should make things a bit easier. Just picked up something called CSVed (http://home.hccnet.nl/s.j.francke/software/software.htm) to work with a few entries from the CSVs I've got. I'll test out the CSV data you provided to me as I go along, too. I found out (the hard way) that Excel messes up CSV files when you edit them, so I'm doing things differently this time. But out of curiosity, do you just use Notepad, or do you have a specific tool for working with CSVs yourself?

I use LibreOffice Calc, a

I use LibreOffice Calc, a free alterntive to Excel.  However, I think you should be able to use Excel as editor.  I believe I fixed the converter to work OK with CSV files edited by Excel.

Offline tool not available

Hi Lloyd, I just tried to download the zip file and got a 403 error. Could you please check that? Thanks! Rudi

Sorry about that.  The zip

Sorry about that.  The zip filename was csv2qif.pl.zip.  When I changed it to csv2qif.zip, it downloads OK.  I don't understand that.

online tool offline?

Hi, Is your online tool offline?

Yes, it was. I just moved to

Yes, it was. I just moved to a new server, and a perl dependency was missing (fixed). Thanks for letting me know.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <p> <br> <img> <alt> <h1> <h2> <h3>
  • Lines and paragraphs break automatically.

More information about formatting options

CAPTCHA
This question is to prevent automated spam submissions.
          _     _       _               _____ 
_ __ | |_ | | | |__ _ __ | ____|
| '_ \ | __| | | | '_ \ | '_ \ | _|
| |_) | | |_ | |___ | |_) | | | | | | |___
| .__/ \__| |_____| |_.__/ |_| |_| |_____|
|_|
Enter the code depicted in ASCII art style.