paypal2ofx.py

import csv
import sys
from collections import namedtuple
from datetime import datetime
import re

#
# OFX Specification: http://www.ofx.net/
#
# Instructions (as of 02/15/2015):
#  Login to Paypal site (https://www.paypal.com) and
#  go to "Activity"
#  under "Statements", select "Activity Export"
#  select your data / date range
#  select a comma delimited (CSV) file type for download
#  click "Download History"
#  convert file using this script: "paypal2ofx.py < pp.csv > pp.ofx"
#  import ofx file


LocalCurrency = "USD"
NowTime = '{0:%Y%m%d%H%M%S}'.format(datetime.now())

rows = csv.reader(sys.stdin)

fields = [field.strip().lower().replace(' ', '_').replace('/', '_').replace('"', '').replace('\xef\xbb\xbf', '').replace('\ufeff','')
            for field in next(rows) if field.strip() != '']

PaypalRecord = namedtuple('PaypalRecord', fields)

transactions = [PaypalRecord(*row[0:len(fields)]) for row in rows]


def convert_datetime(record):
    d = datetime.strptime(record.date+' '+record.time, '%m/%d/%Y %H:%M:%S')
    # need to put TZ offset in
    return '{0:%Y%m%d%H%M%S}[:{1}]'.format(d, record.timezone)

# Statement Transaction (STMTTRN)


def emit_transaction(date, amount, fit_id, name, memo, trntype):
    name = re.sub(r'[&]+', '', name)
    memo = re.sub(r'[&]+', '', memo)
    print ("""
\t  <STMTTRN>
\t    <TRNTYPE>{5}
\t    <DTPOSTED>{0}
\t    <TRNAMT>{1}
\t    <FITID>{2:.32}
\t    <NAME>{3:.32}
\t    <MEMO>{4:.255}
\t  </STMTTRN>""".format(date, amount, fit_id, name, memo, trntype))


print ("""OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE

<OFX>
  <SIGNONMSGSRSV1>
    <SONRS>
      <STATUS>
        <CODE>0
	<SEVERITY>INFO
      </STATUS>
      <DTSERVER>{0}
      <LANGUAGE>ENG
      <FI>
        <ORG>PayPal
        <FID>12345
      </FI>
    </SONRS>
  </SIGNONMSGSRSV1>
  <BANKMSGSRSV1>
    <STMTTRNRS>
      <TRNUID>1
      <STATUS>
        <CODE>0
	<SEVERITY>INFO
      </STATUS>
      <STMTRS>
        <CURDEF>{1}
        <BANKACCTFROM>
	  <BANKID>12345
	  <ACCTID>12345
	  <ACCTTYPE>CHECKING
	</BANKACCTFROM>
        <BANKTRANLIST>
""".format(NowTime, LocalCurrency))

print("\t  <DTSTART>{0}".format(convert_datetime(transactions[-1])))
print("\t  <DTEND>{0}".format(convert_datetime(transactions[0])))

for transaction in transactions:
  #
  memo = "%s: %s %s" % (transaction.name, transaction.type, transaction.receipt_id)
  amount = transaction.amount
  trntype = "OTHER"
  
  # payment sent
  # print(amount)
  if len(amount) > 0 and float(amount.replace(',', '')) < 0:
    trntype = "DEBIT"
    name = transaction.name
    memo = "(TX) %s" % (transaction.type)
  # payment received
  elif len(amount) > 0 and float(amount.replace(',','')) > 0:
    trntype = "CREDIT"
    name = transaction.name
    memo = "(RX) %s" % (transaction.type)
  else:
    name = transaction.type
  # check for local currency
  if transaction.currency != LocalCurrency:
    memo = "({0} ${1}) {2}".format(transaction.currency, amount, memo)
    if len(amount) > 0 and float(amount) < 0:
      amount = -1
    else:
      amount = 1

  #        sys.stderr.write("Warning: Non-%s transaction, input manually\n" % (LocalCurrency) )
  # 
  if trntype != "OTHER":
    emit_transaction(convert_datetime(transaction), amount.replace(',', ''), ("%s%s%s%s" % (transaction.date, transaction.time, transaction.name, trntype)).replace('/', '').replace(':',''), name, memo, trntype)

print ("""
        </BANKTRANLIST>
        <LEDGERBAL>
          <BALAMT>{0}
          <DTASOF>{1}
        </LEDGERBAL>
      </STMTRS>
    </STMTTRNRS>
  </BANKMSGSRSV1>
</OFX>
""".format(transactions[0].balance, convert_datetime(transactions[0])))