paypal2qif.py

import csv
import sys
from collections import namedtuple

# Enter whatever is your local currency.
LOCAL_CURRENCY = 'EUR'

rows = csv.reader(sys.stdin)

# This assumes that your PayPal language is set to English.
fields = [field.strip().lower().replace(' ', '_').replace('/', '_')
            for field in rows.next() if field.strip() != '']

PaypalRecord = namedtuple('PaypalRecord', fields)
TransactionCollection = namedtuple('TransactionCollection',
                                   'transaction related_transactions')
CurrencyConvertedTransaction = namedtuple('CurrencyConvertedTransaction',
                                          'transaction splits')

transactions = [PaypalRecord(*row[0:len(fields)]) for row in rows]
transactions = dict((t.transaction_id, TransactionCollection(t, []))
                    for t in transactions)

def convert_datetime(date, time):
    day, month, year = date.split('.')
    hour, minute, second = time.split(':')
    return ''.join((year, month, day, hour, minute, second)) + '[0:GMT]'

def emit_transaction(transaction, converted, id, name):
  memo = '\\n'.join('%s=%s' % (k, getattr(transaction, k))
                    for k in sorted(fields) if getattr(transaction, k))
  transaction = converted.transaction
  if transaction.currency != LOCAL_CURRENCY:
    sys.stderr.write((
        'Cannot handle {amount} {currency}: Please manually review it:\n'
        '  {name} ({date})\n').format(
            name=name, date=transaction.date, amount=transaction.gross,
            currency=converted.transaction.currency))
    memo = 'NEEDS REVIEW\\n' + memo
    print 'LNEEDS_REVIEW'
  print 'D{date}\nT{amount}\nP{name}\nM{memo}'.format(
      name=name, memo=memo,
      date=transaction.date,
      amount=number(transaction.gross))
  for transaction in converted.splits:
    if number(transaction.fee) != 0:
      sys.stderr.write('Currency conversion with a fee. Not yet handled.\n' +
                        repr(converted))
    print 'S{currency}\nE{memo}\n${amount}'.format(
        currency=transaction.currency, memo=transaction.name,
        amount=number(transaction.gross))
  print '^'

number = lambda x: x if type(x) == float else (0. if x == '...' else float(x.replace(',','.')))

print '!Type:Cash'

# Try to match currency conversions:
for t in transactions.values():
  if t.transaction.type == 'Currency Conversion':
    transactions[t.transaction.reference_txn_id].related_transactions.append(
        t.transaction)
    del transactions[t.transaction.transaction_id]

def GetCurrencyConvertedTransaction(t):
  """
  A transaction may be in a foreign currency.
  We want to report the local currency though.
  We get that amount from the related transactions through
  the currency conversion entries.
  """
  transaction = t.transaction
  related = t.related_transactions
  if not related:
    return CurrencyConvertedTransaction(transaction, [])
  assert len(related) == 2
  a, b = related
  if a.currency != transaction.currency:
    b, a = a, b
  assert a.currency == transaction.currency
  # a == transaction == foreign currency; b == local currency
  # e.g. a (transaction=-6USD, a=6USD, b=-5EUR)
  # 5 EUR (Split: Original Price: 6 USD, To Euro: -6 USD, From USD: 5 EUR)
  new_transaction = (transaction._replace(gross=b.gross)
                                ._replace(currency=b.currency))
  return CurrencyConvertedTransaction(new_transaction, [transaction, a, b])

for t in sorted(transactions.itervalues(),
                key=lambda x: convert_datetime(x.transaction.date,
                                               x.transaction.time)):
  try:
    transaction = t.transaction
    converted = GetCurrencyConvertedTransaction(t)
    if transaction.type in ['Authorization', 'Temporary Hold',
                            'Currency Conversion', 'Order']:
      continue

    name = transaction.name
    emit_transaction(transaction, converted, transaction.transaction_id, name)
    if number(transaction.fee) != 0:
      if t.related_transactions:
        sys.stderr.write('This case is not yet handled. '
                          'Currency conversion with fee.\n' + repr(amount))
      converted = CurrencyConvertedTransaction(
          transaction._replace(gross=transaction.fee), [])
      emit_transaction(transaction, converted,
                      '"%sFEE' % transaction.transaction_id, 'Paypal Fee')
  except Exception, e:
    sys.stderr.write('Failed to process transaction:\n  %s\n' % repr(t))
    raise e