There seem to be other people looking at this stuff, so I thought I'd
post what I have so far.
Brief version: Attached is a python script that downloads OFX
transactions from ameritrade, discover, and AT&T universal card.
> I've read a couple places that you aren't sure where to get the
> information necessary to support statement downloads into GnuCash -
> most notably the actual bank URLs. It turns out it's actually pretty
> easy to obtain a complete list of banks, capabilities and their URLs
> from Microsoft Money.
Yup, I've done the same thing. I found that fipartner.ini in the
mode0.cab distributed with the money2004 demo contains, for each
institution, a url to a file on Microsoft's web site. This file
contains a unicode XML config file for that institution, including the
OFX URL, the FID, and the name of the institution. (Also data on the
capabilities of the server.)
The ofx spec tells the format of the messages. They are SGML messages
posted to the server with a header at the front. (XML is specified in
the newer spec, but none of the servers I've worked with have support
for it.) The mime type of the post MUST be application/x-ofx. (Some
servers, e.g. ameritrade, will fail if you don't set this.)
After trying and failing with a hand-crafted XML request based on the
latest standard, I decided that working out the details would require
too much trial and error with the actual servers. So I took the short
cut approach and ran the MS Money demo against a debug version of
wininet.dll. (So I could capture the contents of the https
transactions.)
The net result of all of this is a little python script that can
download the OFX for the last month's transactions or a list of
accounts. It doesn't try to parse the responses, it only writes them to
a file. I currently use it with ameritrade, ATT Universal Card, and
Discover card.
For Discover, this is the only way I can find to get my transactions in
ofx format, because there is no download option on the web site. I use
this script fairly regularly in conjunction with gnucash.
The script is a quick hack, and I'm still learning python (the reason I
used python instead of perl), but hopefully it's a useful example to
someone. Aside from the structure on the top, you probably want to read
it backwards, as it builds from little functions to big ones. Also,
change the "if 1" to "if 0" to print the queries instead of sending
them.
Steve
[EMAIL PROTECTED]
#!/usr/bin/python
import time, os, httplib, urllib2
import sys
join = str.join
sites = {
"ucard": {
"caps": [ "SIGNON", "CCSTMT" ],
"fid": "24909",
"fiorg": "Citigroup",
"url": "https://secureofx2.bankhost.com/citi/cgi-forte/ofx_rt?servicename=ofx_rt&pagename=ofx";,
},
"discover": {
"caps": [ "SIGNON", "CCSTMT" ],
"fiorg": "Discover Financial Services",
"fid": "7101",
"url": "https://ofx.discovercard.com/";,
},
"ameritrade": {
"caps": [ "SIGNON", "INVSTMT" ],
"fiorg": "ameritrade.com",
"url": "https://ofx.ameritrade.com/ofxproxy/ofx_proxy.dll";,
}
}
def _field(tag,value):
return "<"+tag+">"+value
def _tag(tag,*contents):
return join("\r\n",["<"+tag+">"]+list(contents)+["</"+tag+">"])
def _date():
return time.strftime("%Y%m%d%H%M%S",time.localtime())
def _genuuid():
return os.popen("uuidgen").read().rstrip().upper()
class OFXClient:
"""Encapsulate an ofx client, config is a dict containg configuration"""
def __init__(self, config, user, password):
self.password = password
self.user = user
self.config = config
self.cookie = 3
config["user"] = user
config["password"] = password
if not config.has_key("appid"):
config["appid"] = "PyOFX"
config["appver"] = "0100"
def _cookie(self):
self.cookie += 1
return str(self.cookie)
"""Generate signon message"""
def _signOn(self):
config = self.config
fidata = [ _field("ORG",config["fiorg"]) ]
if config.has_key("fid"):
fidata += [ _field("FID",config["fid"]) ]
return _tag("SIGNONMSGSRQV1",
_tag("SONRQ",
_field("DTCLIENT",_date()),
_field("USERID",config["user"]),
_field("USERPASS",config["password"]),
_field("LANGUAGE","ENG"),
_tag("FI", *fidata),
_field("APPID",config["appid"]),
_field("APPVER",config["appver"]),
))
def _acctreq(self, dtstart):
req = _tag("ACCTINFORQ",_field("DTACCTUP",dtstart))
return self._message("SIGNUP","ACCTINFO",req)
def _ccreq(self, acctid, dtstart):
config=self.config
req = _tag("CCSTMTRQ",
_tag("CCACCTFROM",_field("ACCTID",acctid)),
_tag("INCTRAN",
_field("DTSTART",dtstart),
_field("INCLUDE","Y")))
return self._message("CREDITCARD","CCSTMT",req)
def _invstreq(self, brokerid, acctid, dtstart):
dtnow = time.strftime("%Y%m%d%H%M%S",time.localtime())
req = _tag("INVSTMTRQ",
_tag("INVACCTFROM",
_field("BROKERID", brokerid),
_field("ACCTID",acctid)),
_tag("INCTRAN",
_field("DTSTART",dtstart),
_field("INCLUDE","Y")),
_field("INCOO","Y"),
_tag("INCPOS",
_field("DTASOF", dtnow),
_field("INCLUDE","Y")),
_field("INCBAL","Y"))
return self._message("INVSTMT","INVSTMT",req)
def _message(self,msgType,trnType,request):
config = self.config
return _tag(msgType+"MSGSRQV1",
_tag(trnType+"TRNRQ",
_field("TRNUID",_genuuid()),
_field("CLTCOOKIE",self._cookie()),
request))
def _header(self):
return join("\r\n",[ "OFXHEADER:100",
"DATA:OFXSGML",
"VERSION:102",
"SECURITY:NONE",
"ENCODING:USASCII",
"CHARSET:1252",
"COMPRESSION:NONE",
"OLDFILEUID:NONE",
"NEWFILEUID:"+_genuuid(),
""])
def ccQuery(self, acctid, dtstart):
"""CC Statement request"""
return join("\r\n",[self._header(),
_tag("OFX",
self._signOn(),
self._ccreq(acctid, dtstart))])
def acctQuery(self,dtstart):
return join("\r\n",[self._header(),
_tag("OFX",
self._signOn(),
self._acctreq(dtstart))])
def invstQuery(self, brokerid, acctid, dtstart):
return join("\r\n",[self._header(),
_tag("OFX",
self._signOn(),
self._invstreq(brokerid, acctid,dtstart))])
def doQuery(self,query,name):
# N.B. urllib doesn't honor user Content-type, use urllib2
request = urllib2.Request(self.config["url"],
query,
{ "Content-type": "application/x-ofx",
"Accept": "*/*, application/x-ofx"
})
if 1:
f = urllib2.urlopen(request)
response = f.read()
f.close()
f = file(name,"w")
f.write(response)
f.close()
else:
print request
print self.config["url"], query
# ...
import getpass
argv = sys.argv
if __name__=="__main__":
dtstart = time.strftime("%Y%m%d",time.localtime(time.time()-31*86400))
dtnow = time.strftime("%Y%m%d",time.localtime())
if len(argv) < 3:
print "Usage:",sys.argv[0], "site user [account]"
print "available sites:",join(", ",sites.keys())
sys.exit()
passwd = getpass.getpass()
client = OFXClient(sites[argv[1]], argv[2], passwd)
if len(argv) < 4:
query = client.acctQuery("19700101000000")
client.doQuery(query, argv[1]+"_acct.ofx")
else:
if "CCSTMT" in sites[argv[1]]["caps"]:
query = client.ccQuery(sys.argv[3], dtstart)
elif "INVSTMT" in sites[argv[1]]["caps"]:
query = client.invstQuery(sites[argv[1]]["fiorg"], sys.argv[3], dtstart)
client.doQuery(query, argv[1]+dtnow+".ofx")
_______________________________________________
gnucash-devel mailing list
[EMAIL PROTECTED]
http://www.gnucash.org/cgi-bin/mailman/listinfo/gnucash-devel
