public
Last active

Python script for importing OFX files into a ledger-cli file

  • Download Gist
ofx-to-ledger.py
Python
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80
from __future__ import print_function
from ofxparse import OfxParser
import os
import re
import sys
 
if len(sys.argv) != 1:
print ('This utility does not take command-line arguments')
exit()
 
if not 'LEDGER_FILE' in os.environ:
print ('Please set the environment variable LEDGER_FILE to point to the ledger file')
exit()
 
known_ids = set()
already_imported = set()
account_id_to_account_name = {}
 
with open(os.environ['LEDGER_FILE'],'r') as ledger_scan:
for line in ledger_scan:
id_match = re.search("@id +(\S+.*)", line)
import_match = re.search("@imported +(\S+.*)", line)
account_map_match = re.search("@account +(\S*) +(\S+.*)", line)
if id_match != None:
known_ids.add(id_match.group(1))
if import_match != None:
already_imported.add(import_match.group(1))
elif account_map_match != None:
account_id_to_account_name[account_map_match.group(1)] = account_map_match.group(2)
with open(os.environ['LEDGER_FILE'],'ab') as ledger_output:
for (dirpath,dirnames,filenames) in os.walk('.',False):
for filename in filenames:
if (filename.endswith('.ofx') or filename.endswith('.qfx')) and not filename in already_imported:
print ("Importing {0}".format(filename),end='')
with open(os.path.join(dirpath, filename),'r') as ofx_file:
ofx = OfxParser.parse(ofx_file)
account_name = account_id_to_account_name[ofx.account.number.encode('ascii')]
print (" ({0})".format(account_name))
 
ledger_output.write('\n\n\n\n\n;;;; ######################################################################\n')
ledger_output.write(';;;; @imported {0}\n'.format(filename))
ledger_output.write(';;;; ######################################################################\n\n')
 
def transaction_sort_key(t):
try:
return (t.date, t.payee)
except AttributeError:
return (t.date, "UNSPECIFIED PAYEE")
 
for t in sorted(ofx.account.statement.transactions, key=transaction_sort_key):
if len(t.id) > 10:
unique_id = t.id
else:
unique_id = ofx.account.number.encode('ascii') + "." + t.id
if unique_id in known_ids:
continue
 
print (" {0}".format(unique_id))
date = t.date.date()
ledger_output.write ('; @id {0}\n'.format(unique_id))
 
try:
payee = t.payee
except AttributeError:
payee = "UNSPECIFIED PAYEE"
 
ledger_output.write ('{0}/{1}/{2} {3}\n'.format(date.year,date.month,date.day,payee))
 
t.amount = float(t.amount)
if len(t.memo) > 0:
ledger_output.write (' {0} ${1:0.2f} ; {2}\n'.format(
account_name, t.amount, t.memo))
ledger_output.write (' Expenses:unknown\n\n')
else:
ledger_output.write (' {0} ${1:0.2f}\n'.format(
account_name, t.amount))
ledger_output.write (' Expenses:unknown\n\n')

I just used this and it was crazy useful. A couple suggestions:

  • Line 34: consider sorted(filenames)?
  • Line 60: add unique_id to known_ids after you've checked to see that it isn't already there?

Also, a docstring at the top of the file saying that this requires ofxparse and to run it by cd'ing to the directory where your ofx/qfx files are and running this script would have been useful. Something like:

"""
Add transactions from .qfx and .ofx files to your ledger file.

Usage: set LEDGER_FILE to the file you want to add transactions to. This file should have a line like

;;; @account ACCOUNT_NUMBER  Assets:Checking

to map each account number in the OFX files to the Ledger account you want it to correspond to. Then, cd to the directory where you have your OFX files and run

/path/to/ofx-to-ledger.py

"""

Oh, and you might want to change the regexp for account names. Ledger supports accounts with spaces in them, such as "Liabilities:Credit Card".

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.