Skip to content

Instantly share code, notes, and snippets.

@dansloane

dansloane/caxton2xero.py

Last active August 29, 2015 14:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save dansloane/8bbdedb033cddf1aedb7 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save dansloane/8bbdedb033cddf1aedb7 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. dansloane revised this gist Sep 29, 2014. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions caxton2xero.py
    View file
    Original file line number Diff line number Diff line change
    @@ -14,7 +14,6 @@

    from bs4 import BeautifulSoup
    import sys
    import re
    import csv
    import string

    @@ -42,4 +41,4 @@

    # write the output to stdout
    op = [transdate,transvalue,payee,description]
    output.writerow(op)
    output.writerow(op)
  2. dansloane revised this gist Sep 29, 2014. 1 changed file with 18 additions and 11 deletions.
    29 changes: 18 additions & 11 deletions caxton2xero.py
    View file
    Original file line number Diff line number Diff line change
    @@ -3,23 +3,22 @@
    # 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 re
    import csv
    import string

    def cell_text(cell):
    return " ".join(cell.stripped_strings)

    # 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']
    @@ -28,11 +27,19 @@ def cell_text(cell):

    for table in soup.find_all('tbody'):
    for row in table.find_all('tr'):
    transdate = row.find_all('td')[0].string
    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
    op = [transdate,transvalue,payee,description]
    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)
  3. dansloane revised this gist Sep 29, 2014. 1 changed file with 12 additions and 0 deletions.
    12 changes: 12 additions & 0 deletions caxton2xero.py
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,16 @@
    #!/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 re
  4. dansloane renamed this gist Sep 29, 2014. 1 changed file with 0 additions and 0 deletions.
    0 gistfile1.py → caxton2xero.py
    View file
    File renamed without changes.
  5. dansloane created this gist Sep 29, 2014.
    26 changes: 26 additions & 0 deletions gistfile1.py
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,26 @@
    #!/usr/bin/python
    from bs4 import BeautifulSoup
    import sys
    import re
    import csv
    import string

    def cell_text(cell):
    return " ".join(cell.stripped_strings)

    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
    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
    op = [transdate,transvalue,payee,description]
    output.writerow(op)