This is part 5 in my series for replacing Microsoft Money. I gave my requirements in part 1. Then I looked at Quicken, GnuCash, and Moneydance.
I still haven’t found a perfect replacement for Microsoft Money. Money isn’t perfect either, but it’s working. Well, sort of. I already know the bugs and limitations and I know how to work around them.
Having suffered from being at the mercy of one software vendor, I really don’t look forward to jumping into the arms of another vendor with the same policy of automatically disabling features. Meanwhile, the contenders GnuCash and Moneydance still don’t match all the functionalities I’d like to have.
If only the downloading features won’t work in Money, why junk the whole thing? Why not just fix the downloads?
Having seen how GnuCash and Moneydance can download transactions directly from the financial institutions, I wondered if there is a way to use an alternative application for downloading. I can then import the downloaded files into Microsoft Money. If it’s only a one-time setup and a few more mouse clicks, I can live with that.
With the help of Google and GnuCash Wiki, I found two great Python scripts (ofx.py and ofx-ba.py). With some trial and error, I figured out how to use them on Windows.
[The rest of this post contains detailed technical instructions. I provide enough details to make it work even if you are not that technical. Skip if you are not interested.]
1. Download and install Python. I used the latest ActivePython 2.x for Windows. I think the 2.x version from python.org will work too.
2. Download the Python scripts: ofx-ba-tfb.py. Just save them in a folder. Open it in a text editor. You will see something like this:
sites = {
"amex": {
"caps": [ "SIGNON", "CCSTMT" ],
"fid": "3101",
"fiorg": "AMEX",
"url": "https://www99.americanexpress.com/myca/ofxdl/us/download?request_type=nl_desktopdownload",
},
These are the address and IDs used in downloading from the sample financial institutions. You will need the address and IDs for the financial institutions you use.
3. Look for OFX settings. The settings for some financial institutions are listed in OFX Direct Connect Bank Settings on GnuCash Wiki. There is another searchable list at a web site OFX Home. If the companies you use are not listed there, you will have to find them yourself in some other way.
Find and gather all the values for each of your financial institutions.
4. Update Python script. Open the Python script again in a text editor. Replace the sample settings in the scripts with the settings for the companies you use. Give each entry a short name. Fill in fiorg, fid, and url. Add or remove more entries in the “sites” area if necessary.
For credit cards, make them look like amex in the script with a CCSTMT. For brokerage accounts, make them look like fidelity in the script with a INVSTMT. Add bank settings to the script. You will also need the routing number for your bank.
5. Run Python scripts. Open a command prompt (Start -> Run … -> cmd). For credit cards and brokerage accounts, type
python ofx-ba-tfb.py [name of the site] [your online login] [your account number]
For bank accounts:
python ofx-ba.py [name of the site] [your online login] [your account number] [CHECKING or SAVINGS]
When you are prompted for the password, type your online password and press Enter.
The script will download a [site][yyyymmdd].ofx file. Open Microsoft Money. Double click on the downloaded .ofx file. The Microsoft Money Import Handler mnyimprt.exe will take it from there.
So far, this is still not much different from going to each financial institution’s web site and downloading the transactions manually. With more modifications, I made the script:
- take the password as a parameter instead of prompting the user for the password
- trigger the Microsoft Money Import Handler automatically after each download
You can download my automated script. To use the automated script, insert your online password between your login and your account number. For credit card or brokerage account:
python ofx-ba-tfb-auto.py [name of the site] [your online login] [your online password] [your account number]
For bank account:
python ofx-ba-tfb-auto.py [name of the site] [your online login] [your online password] [your account number] [CHECKING or SAVINGS]
To download one account after another, a batch file calls the automated script for each account. Because you have to put your logins, passwords, and account numbers into the batch file, I suggest you put the scripts and the batch file on a portable USB drive. Plug the USB drive into your computer only when you import. Unplug when you are done.
I placed a shortcut to the batch file on my desktop. Now I have my own one-step update. In a round-about way, after looking at the alternatives, I realize I don’t have to replace Microsoft Money after all.
The files generated by these scripts only work with Microsoft Money. Attempting to import the same file into an older version of Quicken gave me this error:
These transaction download scripts only update the prices of securities held at the institutions that provide direct OFX downloads. If you have stocks or mutual funds at institutions that don’t offer OFX transaction downloads, you need a different script for updating the prices. See follow-up post Download Price Quotes to Microsoft Money After Microsoft Pulls the Plug.
Once again, the downloads are:
- ActivePython 2.x or Python 2.x
- my modified script for downloading interactively. Test your settings with this one first.
- my automated script and batch file.
- my script for downloading price quotes.
Special thanks to:
- GnuCash community for the inspiration
- OFX Direct Connect Bank Settings on GnuCash Wiki
- Costin Manolache for the fix for Vanguard
- Google, for helping me find the resources above
Say No To Management Fees
If you are paying an advisor a percentage of your assets, you are paying 5-10x too much. Learn how to find an independent advisor, pay for advice, and only the advice.
c230 says
Too bad we can’t edit our postings…
What I meant to say was – To try out my theory, someone that hasn’t downloaded Discover transactions via OFX.PY could manually modify their OFX file generated from the script prior to importing the file into Money.
I’m pretty sure that I’ve triggered the Discover website “Download into Money” option before trying these scripts and didn’t get the duplicates after the file imported into Money.
,
Bobby says
I plan to develop a fix for the Discover transactions soon. I have been testing a method that doesn’t require truncating the FITID value, but it has a glitch itself. I’ll probably write something up elsewhere and provide a link, since a comment post doesn’t work very well.
c230 says
Bobby,
Just a thought (that perhaps coincides with your plan). Before deleting all of the ofx files, the name of the existing Discover file (discover20100125104535.ofx as an example) could be used to determine the start date of requested transactions, rather than the default 30 days.
Dan
hokie94 says
Folks…I discovered something that maybe useful to some.
I changed my system date to a day prior to when my software “expired” (just changed the year to 2009). I opened my money file. I changed the date back to the current date. I was able to update all of my accounts. No scripts needed. I have bank and credit card accounts. Not sure if anyone has tried this before, but it works for me.
Anybody willing to figure out and post what the registry entry is for the expiration date?
c230 says
The fact still remains that come next January, Microsoft is pulling the plug on their Money servers, so this activity for many of us is proactive – we’re preparing for going offline, but not there yet.
Michael says
Here’s something I found on the web: Save computer date; Reset computer date; start up program; restore computer date. It might help speed up the process:
set saved=%DATE%
set date=01-03-09
start msmoney
set date=%saved%
Maybe at this point, you can launch the OFX file extracts for money.
Bobby says
Changing the system date before start-up does work, but it’s not a good idea to use Money with the date set back. By my current understanding, Money relies on OFX data files that reside a Microsoft server. When they pull those (and they will), Money will no longer have what it needs, regardless of the system date.
Ken says
Thanks for your efforts !
I have downloaded the scripts and have run them. As I do a lot of programming at work I have made some modifications to output file names and locations. I have also written a script to decipher JHancock price quotes from a download of their daily “Investment Options” page where they list their prices.
I have two questions:
1. Regarding the activity script … the import file from Fidelity returned my monthly statement activity. Have you, or anyone for that matter, found how to download daily activity for just the previous day(s), as MsMoney Plus DeLuxe 2008 used to download ? This would be like downloading the history for the last 2-3 days or something like that.
2. The updates appear in the price listings (Select update prices\update prices manually…”) by date with a Source = “update” rather than “online.” “Update” prices are not used to calculate the “Change” in price value or the “TotalChange” value columns I include on some of my listings, whereas “Online” price values do result in these calculations. Any idea how to modify the scripts to make these “Online” price updates rather than “Update” price updates?
Thanks again,
Ken
Harry Sit says
Ken – The script has hard coded to download 30 days of activities. It’s controlled by this line:
dtstart = time.strftime(“%Y%m%d”,time.localtime(time.time()-31*86400))
If you want to make it download for fewer days, or remember the last download date and time and resume from that point, just play with that dtstart variable.
What Money does with the imported prices is controlled by Money. I don’t think there’s way to force it to treat the prices as “Online” as opposed to “Update.”
schecker says
I am trying to download brokerage transactions from E*Trade. The download works, shows success and my account no. etc. but does not bring down the detailed transactions. What could I be missing? I am using the ofx-ba-tfb-auto.py script.
CH says
Thank you TFB for all your hard work. I was able to get Scottrade to work. The key for me was that the brokerid is not the same as the org. I changed the line
_field(“BROKERID”, brokerid),
to be
_field(“BROKERID”, “www.scottrade.com”),
and it started working. I know this breaks it for other brokerages and I was hoping you might amend the script in a better way.
Before this modification, the OFX file would say that it logged in successfully, but then have a 2003 ERROR.
Thank you again, this has been a real life saver!
CH
c230 says
CH,
Per the Online Banking Directory, 5468.xml – Scottrade in your py script should read something like:
“scottrade”: {
“caps”: [ “SIGNON”, “INVSTMT” ],
“fiorg”: “scottsave.com”,
“url”: “https://ofxstl.scottsave.com”,
but if “www.scottrade.com” works better, change the fiorg to that instead and return the brokerid code as it was.
schecker,
You’re not making the same error I made in omitting the account number, are you?
MDB says
Maybe you might consider AceMoney as an alternative to MS Money; just a thought.
schecker says
I was able to get e*trade brokerage to work by using etrade.com in the fiorg section instead of what the downloaded xml contained. etrade.com was in the brokerid section of the xml.
Has anyone been able to make Wells Fargo Credit Card work?
Bobby says
OK… after messing with this for a while to fix the Discover issue, I then ended up modifying/adding code to the extent that I decided to do a little write-up on it. I wrote too much for my taste, but what the heck… it’s done now.
If interested, you can read about it at:
http://sites.google.com/site/pocketsense/
Note: I’m not a web-guy… and created the site and entry for the sole purpose of sharing/discussing the OFX import to Money. I didn’t realize until well into doing it how much time I would spend describing what’s going on… live and learn.
If you have comments, I *think* you can leave them on the site.
Dan says
Bobby,
Great Job!! I will be one of your first testers. I couldn’t find a way to leave a comment on your site, by the way. That scubber code for Discover is exactly what I was looking for.
Dan says
Bobby,
We really shouldn’t bring up issues on your scripts on this forum, but you don’t have contact info or a comments method. Your setup.py bombs out on me when testing Discover ….
Traceback:
File “setup.py”, line 253, in
test_acct(AcctArray[acctnum])
File “setup.py”, line 111, in test_acct
if ofx.getOFX(acct) == 0:
File “C:\Documents and Settings\…\ofx.py
“, line 191, in getOFX
minInterval = float(Sites[sitename][‘minInterval’]) #minimum interval (da
ys) defined for this site
KeyError: ‘minInterval’
Dan says
Never mind Bobby,
I had copied the institution data from TFB’s script and omitted to add the minInterval variable in the control.py
Bobby says
Dan: I added a comments page, but apparently it only works if a person has been explicitly “invited”? Rather than try to figure that out tonight, I just added a link to a blogspot.com entry for this topic. Hopefully, that works better.
Sorry about the minInterval issue, I forgot to mention that on the setup page. It was the way I fixed the issue w/ Vanguard requiring longer intervals than the others. I’ll update the instructions for anyone trying to copy their prior stuff over.
steve says
Please help. After working my way through this fix, I can’t seem to get past an error on line 163 in my ofx.py file that says: tuple indices must be integers, not str. This happens after it prompts me to enter my password.
Troy says
Firstly, thank you very much for this. I’ve been downloading quotes for a couple of months and now I’m trying to set up credit card and investment account downloads.
I’ve gotten all other accounts to work except for New York Life and Principal investment accounts. Has anybody gotten these to download using the scripts. Here is the code I have entered:
"nyl": { "caps": [ "SIGNON", "INVSTMT" ], "fiorg": "Yodlee", "fid": "9111", "url": "https://216.34.60.221/DatafeedAPI", }, "principal": { "caps": [ "SIGNON", "INVSTMT" ], "fiorg": "Yodlee", "fid": "3776", "url": "https://216.34.60.221/DatafeedAPI", },c230 says
Troy,
The scripts do not work with Yodlee unfortunately.
Steve,
Were you inquiring about scripts by TFB or Bobby? (We should respectfully address questions to Bobby on his Blog comments page)
Steve says
Sorry about the confusion, my question was to TFB and the suggested ofx.py script.
Harry Sit says
Steve – You will have to show us what you had in the sites section. The script can throw an error if you missed a comma, a quote, a square bracket, or a curly brace, or if you had one too many of those. I would also suggest you use ofx-ba-tfb.py with the changes I made for Windows.
Steve says
Okay, I’ve pasted below the section from my sites:
sites = {
“fidelity”: {
“caps”: [ “SIGNON”, “INVSTMT” ],
“fid”: “7776”,
“fiorg”: “fidelity.com”,
“url”: “https://ofx.fidelity.com/ftgw/OFX/clients/download”,
},
},
I tried removing the extra }, but then I received an error saying invalid syntax on line 18 which has def _field (tag,value):
I don’t believe I have the file ofx-ba-tfb.py? I only have ofx-ba.py which I tried but get the same error but on line 183 which says: client = OFXClient(sites[argv[1]], argv[2], passwd)
Harry Sit says
Steve – Remove that very last comma. Make it
sites = {
“fidelity”: {
“caps”: [ “SIGNON”, “INVSTMT” ],
“fid”: “7776”,
“fiorg”: “fidelity.com”,
“url”: “https://ofx.fidelity.com/ftgw/OFX/clients/download”,
},
}
matt says
Hi, trying to get this to work, followed the steps correctly, i think, but get an error trying to execute the b-a py:
File “ofx-ba.py”, line 69
req = _tag(“STMTRQ”,
^
whats the correct syntax for executing the script from cmd?
Harry Sit says
Matt – If you are using the original ofx-ba.py, without my modifications,
python ofx-ba.py [name of the site] [your online login] [your account number] [CHECKING or SAVINGS]
The script will prompt you for the password.
matt says
Hi again,
Yeah, i followed the steps listed above, but I still get that error. Are the parameters encapsulated in brackets [ ]?
matt says
So, I made a bit of progress. I installed an earlier version of Python (2.6) and followed the steps, again. I got further, it prompted me for a password, but then returns:
Traceback (most recent call last):
File “ofx-ba.py”, line 182, in
client = OFXClient(sites[argv[1]], argv[2], passwd)
KeyError: ‘[test]’
Im throwing it:
python ofx-ba.py [test] [account] [CHECKING]
if i just do python ofx-ba.py, it tells me that test is an available site. Any ideas?
matt says
Ah, got it. Missed a param. Now I get an error 15500
Matt says
Hi all,
I finally figured out the error – Wells Fargo is one of the banks that charges for OFX connectivity.
Would anyone be willing to send me some sample checking/debit card transactions that are returned from their banks service? I am trying to build a DTD that will normalize data. Yeah, they should be following the ofx spec, but you know how that goes.
If so, you can axe any confidential info – just make sure you give me the bank name. I’ll create a public wiki just in case anyone else wants this info.
[email protected]
circuit_breaker says
I’m the guy that extended ofx.py to include checking/savings and released it as ofx-ba.py. I guess I could have documented it better, and it does contain an ugly hack that shows my ignorance of python.. but it was my first time ever working with python, and it was thoroughly enjoyable. I’m really glad to see it scattered around the web now helping other people. I can’t really claim to be able to support well it as I haven’t used it in YEARS myself.. but I may soon use it again as I recently wrote myself a fancy check balancing spreadsheet. This would integrate well with that… so you might see some more OFX related code from me. But this time it will be in perl with a Gtk front-end 🙂
circuit_breaker says
By the way, guys, you do NOT need to enclose the arguments to ofx-ba.py with [ ]’s. I’m not sure why I wrote it that way, too long ago!
Michael says
I’ve been using this for some time, and it works pretty good.
Just curious, is there any encription going on for these requests to the financial groups?
What’s the chance that some web skimmer won’t find these things and get access to our passwords, etc?
Any ideas out there?
Bobby says
Michael: Yes, the connection occurs via a secure, encrypted (HTTPS) connection. You may be interested in the modified scripts I put together too. I started with the stuff posted here, but modified quite a bit and wrapped the whole thing with a “user configuration” shell. The primary reason I headed down that path was to allow encryption of locally stored account info, including user-names and passwords, but I extended the effort a bit. You can read more @ http://sites.google.com/site/pocketsense/home/msmoneyfixp1 .
Michael says
Bobby, thanks for the update. I feel safer.
I did try Quicken 2010 Deluxe, and boy is it ‘clunky’! Not a smooth GUI like Money.
Anyway, while trying Quicken, I noticed that I could get Oppenhiemer accounts.
Does anyboy out there access Oppenheimer with the OFX downloads? If so, what is their URL and FID codes, etc.
Thanks, Michael
ameridan says
Try these:
“fiorg” = “Oppenheimer”
“fid” = “125”
“url” = “https://ofx.opco.com/eftxweb/access.ofx”
ameridan says
I’ve found (and published) a very populated file that Moneydance keeps up-to-date with FI settings that don’t seem to be available on the GnuCash site, for those trying to locate their settings for the OFX scripts.
http://microsoftmoneyoffline.wordpress.com/moneydance-ofx-settings/
ENJOY!
ameridan says
For those that might be interested, I’ve come up with a method to run the OFX scripts (Bobby’s PocketSense in particular) from with the Money program to replace the “Update Online Information” function that we currently use. Although a shortcut in the Toolbar would have been my preferred method, this webpage approach doesn’t require any hacking into the program.
Read more here:
http://microsoftmoneyoffline.wordpress.com/2010/02/25/webpage-add-in-run-pocketsense-from-within-the-money-program/
Ryan P. says
This is such a great find! Thanks to TFB and everyone else for the good information!
I have one problem…I bought MS Money in 2008, and it had a key good for I think 2 years. So now it’s expired, and it won’t let me enter new transactions. I still have the exe file I downloaded when I bought it, and that will let me run it in trial mode on a new computer for 60 days, so I’ve just been hopping through old computers in my house every 60 days to keep Money going. Does anybody have any suggestings for getting a new license to keep Money running (or some other way around this)?
Thanks!
Brian R. says
Hello. Thanks for publishing these instructions and these scripts. I have been fretting about losing the transaction downloads and cash flow forecast for several months now.
One note regarding USAA. I have gotten this to work. But USAA users should be aware that you must use your USAA member number as the User ID and the 4-digit PIN as the password (not the UserID and password you use to logon to their web-site which are completely different).
Thanks again. I’m very happy that I’ll be able to run MS Money (hopefully) for years to come. Quicken just doesn’t cut it for me.
Brian
Brian R. says
Also, I forgot to note that I has to put two leading zeroes in front of my account number in the script to get it to work. For instance, if my account number was 123456789, I had to put 00123456789 in the script.
Brian
Brian R. says
I can’t figure out how to edit so I’m posting a correction. In my previous post, I indicated that you needed to preface your checking and/or savings account number with two leading zeroes for USAA. This is correct. However, my example was incorrect. A correct example would be:
Account Number: 12345678
Needs to be formatted as: 0012345678
Effectively, they require 10-digit account numbers. Sorry for the mistake.
Brian
Brian R. says
After a lot of trial and error, I have determined that you can also download your USAA home equity line by using the following settings:
ACCTTYPE in your bat file must be CREDITLINE (rather than CHECKING or SAVINGS)
Account number must be preceded by leading zeroes to create a 14-digit account number. For example, account number 12345678 would be formatted as 00000012345678.
Brian
JM says
Thanks for the scripts. I have implemented your scripts for three brokers: Fidelity, Optionsxpress & Schwab. Fidelity and Schwab work fine with MS Money Plus. Optionsxpress is proving to be a problem. I get MS Money error stating that the file to import contains invalid data or is corrupt when trying to download an OFX file using your scripts. (I also cannot get OX to download via the automatic process within MS Money)….any suggestions about OFX problems with OX? Thanks.
Harry Sit says
JM – If Money proper couldn’t download automatically from OptionsExpress, I doubt you can make the script do it. Download manually after logging in to OptionsExpress (if it offers downloads), or just enter them by hand.
alw says
Is anybody else having a problem with Chase? My batch file calls several institutions and has been working find since last October. Now it works for all institutions except for Chase.
Now get this error on all my Chase accounts (both credit and deposit).
Traceback (most recent call last):
File “ofx.py”, line 220, in
client.doQuery(query, argv[1]+dtnow+”.ofx”)
File “ofx.py”, line 181, in doQuery
“Accept”: “*/*, application/x-ofx”
File “C:\Python26\lib\httplib.py”, line 898, in request
self._send_request(method, url, body, headers)
File “C:\Python26\lib\httplib.py”, line 935, in _send_request
self.endheaders()
File “C:\Python26\lib\httplib.py”, line 892, in endheaders
self._send_output()
File “C:\Python26\lib\httplib.py”, line 764, in _send_output
self.send(msg)
File “C:\Python26\lib\httplib.py”, line 723, in send
self.connect()
File “C:\Python26\lib\httplib.py”, line 1100, in connect
self.sock = ssl.wrap_socket(sock, self.key_file, self.cert_file)
File “C:\Python26\lib\ssl.py”, line 350, in wrap_socket
suppress_ragged_eofs=suppress_ragged_eofs)
File “C:\Python26\lib\ssl.py”, line 118, in __init__
self.do_handshake()
File “C:\Python26\lib\ssl.py”, line 293, in do_handshake
self._sslobj.do_handshake()
ssl.SSLError: [Errno 8] _ssl.c:480: EOF occurred in violation of protocol
alw says
Sorry – hit enter too soon, but am also having trouble downloading directly into MS-Money 2007 from Chase site. Basically there is no error, it just doesn’t do anything. If I’m not signed into MS-Money I get a prompt upon download that Money will import the file on it’s next run, but that doesn’t happen.
Thanks in advance.
Harry Sit says
alw – Which URL are you using for Chase? Did you see they changed it some time last year?