Preface – please read all of it!
This article is for eBay sellers and everyone else using PayPal to receive payments for goods they sell and want to have those transactions imported into QuickBooks.
I assume some level of technical knowledge such as basic Excel and Access operations, PayPal navigation, ability to download files in different formats etc.
The information and files here are offered for free , but without any warranties other than that they were never designed to cause harm, harvest data etc. illegal activity.
Still, use everything below at your own risk and backup your files before you start experimenting!
Please realize that while I offer this at no cost I can not afford to support it at no cost!
If you have any questions or requests please use the Coppell TV Repair’s donations form to donate anywhere between $10 and $20 before submitting a question or I will simply ignore the request.
At the end of the preface , here’s a link to the files for download…but I honestly warn you it’ll be better if you read through the rest first:
Why was the PayPal-To-QuickBooks converter created?
I am an eBay seller receiving payments for goods and services via PayPal.
Sometimes I also make direct sales – again of either products or services – outside of eBay, but still receive payments through PayPal. (for the record, it’s not because I’m in love with PayPal; it’s because I prefer that everything related to one business is done in one place.)
For every payment I receive I use either eBay or PayPal to print a shipping label.
I also make purchases paying out of the PayPal account to various sellers.
It’s actually pretty straightforward – receiving money for sales, printing shipping labels, spending money on purchases and, I forgot, once a month transferring the money to a bank account so that I don’t lose too much should PayPal decide to kill me as some online rumors accuse it of doing.
And I want all those transactions to go into my QuickBooks accounting database for the company , which already holds the banking, credit card, salary etc. company accounting.
This would make my accountant happy to a point of not scalping me a few thousand dollars for having to do it by herself.
The problem
There are actually two problems with getting PayPal transactions into QuickBooks.
The first is the fact that PayPal records its transactions in a very weird, at least to me, manner. I suppose it makes great sense from their perspective and if I devote enough of my time to reverse engineer it (or , if that fails, RTFM) I could parse it and produce excellent QB file, but after looking it for 20 or so minutes I figured I better try another approach.
I know there is an app on the market that does it, but I didn’t want to both spend more money and then tie myself to yet another application. I wanted something simple, effective and below $20.
If you’re curious about PayPal’s data format just download a piece of history from your account’s transactions , then try to make sense of all those initiated, pending, open, partially refunded, temporarily held, reversed, cancelled etc. records they have.
Happily enough, they offer a download formatted “for QuickBooks” and if all you care is the amount of sales and expenses then it may actually work for you…for the most part.
On a side note I’ll also add that the transactions that PayPal returned to me in this way did not match the actual account performance, i.e. they are simplified, optimized and (I dare say) incorrect version of the complete transaction history offered by PayPal , the one that is so hard to process.
I only found that out later, of course, but I was expecting it and it was within reasonable margins. I definitely hope they improve it in the future at least in regards to accuracy.
The second problem is that the PayPal QuickBooks format, even if accurate, could not possibly fit nicely into QuickBooks unless your accounting needs are down to knowing how much was sold, how much was bought and at which dates.
If that’s all you want then you can stop reading here and just move on to downloading your history from PayPal in QuickBooks format and playing with it.
However, if you want certain frequent expenses such as subscriptions ending up in a “Subscriptions:Web Tracking” account in QuickBooks, or if you want to separate shipping label expenses from others, or if yo you want, like we wanted, to separate sales of services and products, or maybe separate certain groups of products from others, then you’re out of luck with the direct PayPal generated file for QuickBooks.
At the very best you’ll have to manually go through all transactions and reclassify them.
So, the second problem is the one of mapping of the transaction exported by PayPal.
At least I call it mapping; my account would call it “reclassifying” and you can call it whatever else you want. It is the problem of automatically assigning proper accounts, names and eventually descriptions to the transactions to be exported to QuickBooks so that you don’t have to manually change them one by one there.
For example, a simple eBay sales transaction is recorded this way in the PayPal’s QuickBooks output data file:
| TRNS | 1/31/2011 | PayPal | YUQING LING | eBay Payment Received | 31.65 | YPPD-J017C YPPD-J018C YPPD-J018E 4921QP1050B |
|
|
|
|
|
|
|
|
|
|
| SPL | 1/31/2011 | Other Income | YUQING LING | -32.9 |
|
|
|
|
|
|
|
|
|
|
|
|
| SPL | 1/31/2011 | Other Expenses | Fee | 1.25 |
|
|
|
|
|
|
|
|
|
|
|
|
In QuickBooks, this will add one record to the account called “PayPal”, one to “Other Income” and one to “Other expense”.
What this transaction actually does is reflect a sale of something (what exactly will become clear later) for a total of $32.9, automatic withdrawal of $1.25 (for postage printed through PayPal) and resulting deposit of $31.65 in the PayPal account.
Well the mapping problem here is that I’d like the postage properly accounted for in a “Postage and delivery” account in QuickBooks and I want the income to not be “Other Income”, but “Product sales” or “Services sales” depending on what was sold.
And I do not want to correct that for all transactions I have.
I want a process that maps a transaction from the generic PayPal form to accounts and names used in my QuickBooks company.
If that’s what you want this article is for you.
Getting started
As mentioned above the problem of importing PayPal sales data into QuickBooks has two components and therefore you start by addressing the two of them: download data from PayPal and then creating maps that will define the translation of the generic PayPal transactions to customized accounts and names for your own QuickBooks company.
Before I walk you through those two steps, though, I’ll take a moment and talk for a second about the data processing being used.
Application requirements
I happened to have a copy of MS Office 2007 installed on the machine I was working on so this is the tool of choice for the task.
This includes Excel 2007 and Access 2007 and the downloadable solution includes two files – an Excel spreadsheet and a relational database in the specific format.
If you have an earlier Office version and the download doesn’t already include files for it then you’ll have to either take up on the challenge and convert them yourself or refer to the sixth paragraph of this article.
Downloading the data files
To download the files you’ll want to log-in to your PayPal account and download two separate files for the same period: the QuickBooks data file and the balance-affecting transactions file as shown on the below pictures.
The reason you’re downloading two and not one file is that for some reason the Quickbooks file does not usually include information on what a purchase or sale was for, at least as of beginning of 2012.
PayPal may start including this information in the future , but at least at the time being the easiest way to get it is from the historical transaction file.
At least in my case , perhaps because I was downloading data for a whole year at once, PayPal did not directly return the file, but told me that it’s being prepared and sent me an email within 24 hours advising me it is ready for download.
I will not walk you through this part and assume you’ve already successfully downloaded AllData.txt (TAB delimited, balance affecting) and AllData.iif (Quickbooks format).
(to be continued)
