Accounting with PayPal downloaded data.

The data you can download from PayPal doesn't easily import into common accounting programs like QuickBooks. Although PayPal claims to support a QuickBooks format, there are significant issues with thier iif files.

• http://www.onlinemerchantnetwork.com/inform/board/message?board.id=TechnicalIssues&message.id=333

Here are some notes on how to clean up the basic data from PayPal and turn it into something that might be useful for filing your taxes:

1. Download year data as .CSV from PayPal with shopping cart info turned on
2. Insert two blank columns (day, year) between Date and Time, Convert date from Data / Text to column, delimited, "/", Rename Date column to Month, Delete Year column
3. Sort by Item Title, delete Gross amounts for lines where Item Title is "Shopping Cart"
   This allows the line items in the shopping cart to be the total in the Gross column; it prevents a "double" total where both the shopping cart total gross amount and the line item gross amounts are added in. These summary lines for the cart should only have Shipping and Handling, Insurance, Sales Tax. Fee. and Net
4. Remove the duplicated Shipping, Insurance and Sales Tax amounts from the detail lines in shopping cart transactions. Detail lines should only show the Gross amount of that line item. Watch out for the occasional "delayed" entries where these amound are included in the detail lines and NOT in the summary line for the cart.
5. Insert Category column after Item Title and tag each line with the chart of accounts item that applies.
   E.g. all lines in shopping cart or other sales are set to "Sales", USPS payments are "Shipping" or what ever your COA accounts are.
6. Sort by Category and total when Category changes.

See also:

• http://www.outright.com Outright.com directly and automatically imports PayPal transactions, and (mostly) correctly tags them as income or expense and can even categorize them further once you show it what to do. It can then estimate (very conservatively) your taxes, or prepare and income and expense report for your tax accountant.+
• http://www.simpleport.net/osc/ SimplePort simplifies balancing PayPal (Premier or Business) accounts in QuickBooks (PRO) and easily integrates PayPal data in your QuickBooks (PRO) company file by...
  - Identifying common transactions (like sales)
  - Automating manual processes
  - Generating Sales Receipts
  - Debiting the correct expense account for common expenditures (PayPal on-line shipping fees for example).
  Generates customer records (including email and address information)
  Inventory Support:
  - PayPal-to-QuickBooks item description matching
  - Sets Item Number and Income Account on sales receipt items
  - Automatic updating of inventory levels
  - Assigns revenue to correct Income Account
  Will not work with PayPal Basic account; must have Premier or Business account.
  Will not work with QuickBooks SimpleStart, etc... must have PRO or Enterprise.
  $20/mo ($10/mo up to 100 transactions)+
• http://www.bigredconsulting.com/aboutebaylink.htm Download PayPal & eBay transaction history into QuickBooks without data entry!+ Requires MS Excel.
• http://sourceforge.net/apps/mediawiki/jgnucashlib/index.php?title=PaypalPlugin There is a plugin for a DB Library for GNUCash that can import transactions via the PayPal SOAP-API. "You will need an API-account and certificate. You can get both on the paypal-website."
• http://www.drupalecommerce.org/ Drupal has an ecommerce plug in that can integrate with PayPal and generate some simple summary reports which might be useful for accounting.
• http://www.weberp.org/ an open source web based / PHP / mySQL system for orders, inventory, AR, AP, GL, and bank accounts. Some development has been done to link weberp with ecommerce shopping cart, order processing systems such as:
  • OSCommerce
  • Joomla 1 1a
  • Zen Cart
• http://www.angelleye.com/blog/2009/05/generate-microsoft-money-qif-file-from-paypal-account-transactions/ php script to translate PayPal data via API to QIF file for import to Money, Quicken, Quickbooks. (cached 2010/03/30 11:20:00 php2qif.htm)

Accounting is just killing my little business... I'm desperately trying to figure out how to do the books without spending hours on it. What I really need is a system that can do the following:

- Directly import transactions from PayPal without requiring that they be typed in again. I typically have more than 2000 little sales each year, with a typical profit of only a few dollars each. QuickBooks does NOT do this. There are programs or services that can be purchased to work WITH QuickBooks, but they require the PRO version.

- Allow me to enter parts purchases and track inventory quantity and valuations. Only the PRO version of QuickBooks tracks inventory quantity, but I'm not sure if it tracks the value of your inventory. A small but growing part of the little business is selling kits, and I understand I have to track the value of my inventory for when I file taxes. So when I change the price of the kit, does my inventory value increase, and does the software track that?

- Generate Income / Expense reports to send to my tax guy to keep the IRS and CFTB happy.

- Idealy, it should export information about my current inventory levels to a web store front so people will know if an item is backordered before they place an order.