Skip to content

Instantly share code, notes, and snippets.

@dansloane

dansloane/caxton2xero.py

Last active August 29, 2015 14:07
Show Gist options
  • Save dansloane/8bbdedb033cddf1aedb7 to your computer and use it in GitHub Desktop.
Save dansloane/8bbdedb033cddf1aedb7 to your computer and use it in GitHub Desktop.
Download ZIP
Caxton Pre-paid card XLS (actually HTML) to Xero import
Raw
caxton2xero.py
#!/usr/bin/python
# caxton2xero.py
# converts a Caxton Pre-paid card statement to CSV for Xero upload
# dms/ssq/29.09.2014
# Caxton pre-paid Visa card only have 'XLS' output, which is actually HTML
# Xero can import statements using QIF or CSV in a particular format
# http://help.xero.com/help/bankaccounts_details_importtranscsv.htm
#
# This script uses BeautifulSoup to parse the HTML and CSV to output the
# relevant fields as required by the Xero spec above.
from bs4 import BeautifulSoup
import sys
import csv
import string
# gets the stdin to process
soup = BeautifulSoup(sys.stdin.read())
output = csv.writer(sys.stdout,quoting=csv.QUOTE_NONNUMERIC,delimiter=',')
xeroheader = ['Date','Amount','Payee','Description']
output.writerow(xeroheader)
for table in soup.find_all('tbody'):
for row in table.find_all('tr'):
transdate = row.find_all('td')[0].string
# we just want the date
transdate = transdate.split()[0]
transvalue = row.find_all('td')[6].string
transvalue = float(transvalue) * -1
payee = row.find_all('td')[1].string
description = "Local currency = " + row.find_all('td')[2].string
# special case for card load (credit)
if payee == 'Card Load':
transvalue = row.find_all('td')[7].string
description = ""
# write the output to stdout
op = [transdate,transvalue,payee,description]
output.writerow(op)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment