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.
alw says
Thank you so much for the heads-up about the URL change TFB. I was not aware of the URL change and since the direct downloads continued to work for me in MS-Money until just recently I hadn’t been relying on the scripts.
Thanks again for a terrific site too. I came because of the MS-Money issue and have stayed because of the excellent general content.
Cheers.
ED says
My banks don’t have an FID or an URL in the xml files, it seems most banks in Canada don’t have the direct OFX connection. Instead I built Python scripts with Mechanize to emulate web browser clicks to get the ofx files from the bank website. It works really well.
SD says
Has anyone else had issues with American Express credit card downloads not working? They were working for me for quite a while, but it broken within the last three months (with no changes on my part).
I’m now getting this message:
2000ERROR
[ofx.unsupportedClientApplication]
Client application or version is not supported by this server.
In my script, I have the app version and ID configured with these values. Perhaps someone else has an idea of which client application/version is liked better by AMEX?
config[“appid”] = “QWIN”
config[“appver”] = “1800”
motif1 says
Sorry to interrupt the very useful programming discussion. Just to say this is a great post, thanks for the work. I was looking for a Money replacement also. I first found that Quicken won’t accept my bank (in France) OFX files. I downloaded the OFX locally and tried to import it into Quicken, but no dice. The googled reason for that is apparently that banks have to not only provide OFX data, but also they have to PAY INTUIT (!!) to authorize data from that bank, even by download/import as I tried. As evil as it sounds, it nevertheless seems plausible, because I could not get the OFX files to import into Quicken.
Then tried Moneydance, yes it did import the OFX files but the program seems not quite there yet in terms of the functionalities that I am used to from Money.
Finally, I downloaded the Sunset version of Money to try, I wanted to make sure it was OK with at least local importing of OFX files. Yes, the same OFX files from the bank “banned” by Quicken are imported with no problem by Money.
So now I figure, between Money Sunset version and download/importing OFX, at least until I can get your py scripts set up, at least one problem in my life is solved. This is great, using Money like this should now be good for another 5 years, until a new bank data format or several more win OS generations finally break everything…
SD says
OK—I fixed my American Express download problem. Google tells me, from a different forum, that AMEX just changed their download URL. I was able to get it working again by configuring the script to point at:
csingley says
Thought you guys might be interested in this:
http://github.com/csingley/pyofx/tree/master/ofx/
Updated python OFX download script, including a basic QT GUI.
keg says
Has anyone gotten these scripts to work with Edward Jones? Mine fail with an OFX 2003 error.
Tom says
My Edward Jones is also failing. Without an account number, the login is successful and the account numbers are returned. But when I use one of the account numbers, the return has the 2003.
Ouroboros says
This is great info. I’ve tried both these and the hleOfxQuotes Java method.
My main problem is I have loads of brokerage accounts with different securities. I want one OFX for all of them, but Money only seems to like to import per account, which is quite time consuming – even if you are reloading from one large OFX.
Anyone tinkered with a direct Money DB update method yet?
Ouroboros says
Also,
If anyone fancies hosting a replacement to the MS online services, serving up the same data (we could change our hosts files to point to the new place)
I would very happily subscribe!
Christian says
Thankyou for these scripts. I’ve been using them for a year. Last week my Scottrade accounts stopped downloading their positions. I still get the account balance and the cash balance, just no stock positions. This happened to all 5 accounts at the same time last week.
Is anyone else seeing or has anyone solved this problem?
Thank you,
Christian
Christian says
After reaching out to Scottrade tech support, the downloads mysteriously started working again.
Thanks,
Christian
Andrew says
Well, like Dave above, I’ve been successfully using Money D&B 2003, happily downloading prices from Microsoft – until Dec. 30, 2010. Fortunately, I found these helpful scripts (which run under Python 2.6.x, but not Python 3.1.x, as it took me awhile to figure out).
Mostly, I am just trying to update my equity prices from yahoo, using the automated script. Everything works and it likes up to my Investment Watch account with the Broker “broker.com” and account number “123456789”, as listed in the OFX file that the script produces.
I still cannot get the OFX file to actually update the prices as of the last trade date (or any other date). It just doesn’t update the holdings in my Investment Watch account in Money, which lists all my equities dated 1/1/2010 with zero shares added. I tried changing the added shares to 1, instead of zero, but that didn’t work either. I tried manually updating one of the positions and reimporting the OFX file – didn’t work. No matter what I have done, the OFX file imports and associates with the Investment Watch account, but does not update the prices as of 12/31/10.
Any suggestions?
Andrew says
PS All update entries in the OFX file show 20101231160200.000[-5:EST]
or something very close to the time of the last trading day end, which should if interpreted properly by Money be updated as of 12/13/10.
Andrew says
PPS – Oh, and Money is matching up the securities with the imported data, just not posting the updated prices, which cannot be seen in portfolio view or on the price history. Still trying to figure it out.
Harry Sit says
@Andrew – Have you tried the Money sunset edition? There is one for home and business as well. People reported the quote script works out of the box with newer versions including the Plus and the Sunset editions.
Andrew says
@TFB – No, I only have Money 2003 Deluxe and Business edition. It worked fine – until now.
The quote script is working fine so far, but Money is so far not recognizing the date and updating the prices, even thought the OFX file is being imported.
ourobrs says
Re Last Updated and Last Trade.
‘Last Trade’ will be populated with the last time you did something like buy or sell. ‘Last Updated’ cannot be activated by the OFX scripts – it could only be previously activated by the Microsoft online download.
I missed this at first (as I wanted it to work to see that the quotes were always updated). However now I just use a ‘TR Week’ field in portfolio view. So long as that is moving, I know the stocks and funds are getting updated. a 0% there means something is wrong.
Matt says
I have 8 accounts in my python script, 2 with Wachovia and 6 with Vanguard. If I use the test function and test individually they all work correctly and import into MS Money Sunset. However, If I try to initiate the script it runs but nothing gets imported. Everything looks correct.
Any suggestions?
Andrew says
That sounds related to the problem I am having, “ourobrs”, but I don’t understand what using a ‘TR Week’ filed in portfolio view means. There is no such view or field in Portfolio from what I can tell, and I don’t understand how that helps if the quotes are not actually updated and seen in the returns view.
Matt, did you select File, Import, and choose the OFX files that the script creates?
Matt says
No, I didn’t. My problem was a misunderstanding in how to use the Sites.dat file. I set up a separate entry for every account at an institution, instead of just the one that is needed. I rebuilt my system onto a new pc and discovered this error when I went through the procedure to install the python scrips and pocketsense. It now works correctly with two accounts at my bank and 6 accounts at my mutual fund.
Sorry I can’t be of help to your problem.
Jason says
This is great. I’m preparing for Money to go offline this month and this will save me a lot of time every day. I’ve got it to work with every account I have in Money, but I can’t get ING Direct to work. I get a successful log on and it downloads an OFX file which appears to be good (I see the latest transactions, etc), but when Money runs to import the file it says that it’s not a valid file. Below is what I have in the script and I’m calling it with:
python ofx-ba-tfb-auto.py ING ‘loginname’ ‘SpecialWebPassword’ ‘Acct#’ SAVINGS
“ING”: {
“caps”: [ “SIGNON”, “BASTMT” ],
“fid”: “35489”,
“fiorg”: “ING DIRECT”,
“url”: “https://ofx.ingdirect.com/OFX/ofx.html”,
“bankid”: “031176110”,
},
Matt says
Suggestion:
Have you set the appid and app version?
“appid” = “QWIN”
“appver”= “1800″
for the specific ING account. You could also contact ING to see what Quicken versions they do support.
Jason says
Yes, I have it set to:
“appid” = “QWIN”
“appver”= “1800″
I’ll try to find out what works for ING.
Hung Le says
Regarding the malformed ING download. The response from ING has a missing required field DTEND in BANKTRANLIST
ING has:
BANKTRANLIST
DTSTART …
STMTTRN
Should be
BANKTRANLIST
DTSTART …
DTEND …
STMTTRN
Jason says
Is that caused by something I’m doing or is it an ING issue? If I download the data directly from their website it works fine.
Hung Le says
ING issue. I’ve send customer support a note on that. Suggest you do the same. Best,
Richard says
In response to earlier comment: Dodge and Cox seems to work OK (as of today, anyway) using data from the online banking directory given in the article. D&C put a bunch of linefeeds in there that others generally don’t, but otherwise it is OK.
The account uses a peculiar xxx,xxxxxxxx format. When I missed off the leading xxx, part, no data came back other than the signon acknowledgement.
Richard says
Dodge and Cox correction: The address it used came from one of the other sources above, as follows…
“https://ofx3.financialtrans.com/tf/OFXServer?tx=OFXController&cz=702110804131918&cl=50314030604”,
and an fid of 8935,
joel brandt says
How complicated are the fixes posted to convert Yahoo quotes into Money? I have Yahoo set up so that its captures all of my position daily, but I have over 50 positions at the moment and the thought that I will need to manually impute prices into Money is depressing (Today is the end!). I’m think this will add an extra hour of work for my weekly Saturday morning updates.
Reviewing these earlier posts makes me think the process described is complicated (I’m not a geek).
Given that today is the absolute end, can the moderator take the time to SLOWLY describe the solution?
tyler says
tfb, this type of script is exactly what i’ve been looking for! however, i’m having some trouble getting connected to my texans credit union checking account.
i’ve followed the steps above and modified the raw ofx-ba.py file with:
“texans”: {
“caps”: [ “SIGNON”, “BASTMT” ],
“fid”: “008PU”,
“fiorg”: “TexansCU”,
“url”: “https://www.netit.financial-net.com/ofx”,
“bankid”: “311987786”, # bank routing #
}
all of this information was retrieved from the fidata xml zip file above.
when i go to run the script using:
ofx-ba.py texans username account# CHECKING
I type in my password at the prompt, and then after 1 second I get an output file which contains the following:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:35e3679544e04c4c9cc1f06f31c0c972
15500ERROR
20110202194546
ENG
TexansCU
008PU
fe99bae2bfd94191906d700c4d6f8fe2
15500ERROR
4
does anyone have any ideas of what may be going wrong? i read the OFX spec from microsoft which says the 15500 error is a “signon invalid” error.
thanks for your help!
Peter says
First of all, a huge Thank You to TFB for putting together this website and the OFX scripts. I have been using MS Money 2002 since, well, 2002, and I cannot face the prospect of changing to another personal finance program. None of the ones available today seems to have the combination of familiarity, ease of use, and feature set that Money has.
I’ve been following this thread for months and did get as far as installing Python, downloading the scripts, running them and getting (it seems) price quotes into a “Dummy” account in Money which contains all the stock and mutual fund positions that I need daily price quotes on.
The one aspect where it looks like it will not work is for options quotes, because the new option quote symbols are longer than what Money allows for the symbol of an investment. I’ve been updating my option quotes manually for a while now, ever since the new symbols came into use, so that does not bother me too much.
But the point of my post is that, 3 days into February, I am still able to download price quotes for stocks and mutual funds, and even foreign currencies (/EURUS) in Money 2002! Does anyone else have a similar experience, i.e., quotes still working? Could it be thatMicrosoft has reconsidered pulling the plug on the price quotes service, or more likely, perhaps it is an oversight and they will disappear soon enough!
Great thread and discussion, thanks everybody!
Matt says
Pete,
Try MS Money Sunset Edition. It’s Free!
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=60302e1e-207e-4710-ac80-d19c22e47488
joel brandt says
I’ve noticed that quotes continue to be downloaded as well. Lets hope it continues.
Amol says
Thanks for posting all the scripts.
Having used several disappointing personal finance software over years, and considering all the time (and money) it needs to maintain them ….
I’m considering writing similar scripts to download transactions in spread sheet formats: excel and CSV. Most financial institutions which offer quicken support, also offer download of transactions in spreadsheet formats. A feature to auto assign categories can be implemented as well.
The best part is, you’re not locked into using any of these finance softwares which want you to upgrade every few years. You can use any Spreadsheet app like Excel, OpenOffice etc, which you use anyway.
Ann says
I thought I was doing fine using the OFX scripts, when suddenly my Wells Fargo accounts stopped working (3 other banks I use still seem to be OK). Did WF completely stop all non-Quicken OFX access? Are there any workarounds?
Harry Sit says
@Ann – Wells Fargo sent a letter to customers some time ago saying they would stop supporting Money downloads. Chase did too.
Robert L. Winter says
First, I’m pretty sure Intuit’s QFX (quicken) and QBO (quickbooks) download files are in fact OFX format files. I feed them to the script below successfully.
Second, Wesabe open sourced a python script that ‘fixes’ OFX files for various brokenness – it updates OFX 1.x version files to 2.X for example.
Here’s the link: https://github.com/wesabe/fixofx
Barry says
I hope someone can help me. In preparation for losing online services I set up Python scripts and tested them several months ago and I was able to import all of my accounts. Now when I try to import into Money I get an import dialog box for each account as expected, After the last account is imported my Money program just stops working and a windows dialog box pops up saying that there was a problem and the program stopped working. Then I am dead in the water. When I close out of Money and then reopen it the program stops working with the same message that the program has stopped working. If I uninstall and reinstall Money there are no problems. But every time I import after running the Python scripts after the last import the same problem – Money stops working. This has to be a problem with my computer or else other people would have this problem. So does anyone have any idea why after importing my last account Money stops working? I am using the Pocket Sense scripts but I do not see a recent discussion area there.
Harry Sit says
@Barry – I had this happen to me once. It was caused by bad data downloaded from Vanguard during a weekend. The solution was deleting temp files, as described in Method 5 in this Microsoft knowledge base article.
http://support.microsoft.com/kb/182608
After I deleted the temp files. Money launched OK. I then removed Vanguard accounts from the list of accounts I download.
You will have to find out which financial institution is giving you bad data and remove it. Otherwise you will have this happen again when it gives you bad data again.
Barry says
@TFB Thanks for the quick response. I will look into it. Vanguard is one of my accounts so I will start with that. If it is bad data from an account then do I have to permanently remove that account from the script and update manually of is this just a temporary problem that will resolve itself?
Also I have been back dating the system date and updating that way since my online services expired in 2009, still works but I’d like to start using the Python method., When I download with Money then I don’t have a problem so if it is bad data why is it happening with the Python scripts but not happening when I use Money online services?
Barry says
@TFB – I deleted the temp files and now Money starts so thank you for your help. I have not verified it yet but Vanguard is the last account on my import list so I suspect that is the problem account.
Still looking for answers on whether I need to permanently remove Vanguard or if it is just a one time issue for the bad data that may have been sent. Also if there was bad data why does it affect Money with the Python script but not when I use Money itself to download.
Harry Sit says
@Barry – I don’t know the answers. Because I don’t have many transactions in my Vanguard accounts anyway, I didn’t put them back in after I removed them. Now that you know how to fix it, you can experiment and see if it’s only temporary. Maybe they only have this problem during some weekends when they do maintenance. I don’t know how Money does it. Maybe Microsoft has additional validation checks and massages the data if it sees problems.
It’ll be interesting to see what’s wrong in the data though. If you look at the Vanguard ofx file and compare it with others, maybe you can see which part isn’t right.
If I get some time, I’ll see if I can link the script to the Wesabe “fix ofx” script Robert posted in comment #188. Hope that will help with some of these bad data problems.
Barry says
@TFB thanks for taking the time to reply. Now that I know I can just delete those temp files if it happens again I will wait a few days before trying to download any Vanguard data. If Vanguard continues to be a problem then I’ll have to permanently remove it and download from the Vanguard web site instead because that does not cause a problem either, it is only the python download that is causing a problem.
Jason says
Has anyone started having problems with the quotes.py piece? It worked fine for me for several weeks, but then suddenly this week Money starts telling me that the data is bad. I don’t mind uploading the ofx file if it will help since it has nothing but stock quotes in it if anyone wants to take a look.
Harry Sit says
@Jason – Yahoo! broke it. I fixed it. See comments in the quotes.py post.
Joanne Lennstrom says
First off – thank-you so much for the python script to update closing stock and mutual fund prices into MS Money.
The script works great!
One part of the MS Money portfolio that I miss is the daily change in stock prices from the previous day, and the calculation of the change in value for my investments. The “Change” column just has “unch”.
Can that ability be written into the script? Or is that a msft calc function that won’t work when the price source is “Online” instead of “Update” ?
Jo L.
Joanne Lennstrom says
OOps Previous post should have read:
Or is that a msft calc function that works only when the price source is “Online” instead of “Update” , as appears in the manual ” update price” window?
Daryl Reece says
First of all, thanks for putting all this together. It saved me lots of time and frustration, but I still have a little an issue that is stumping me. I can’t get Vanguard to download. Can someone who has it working tell me what FIORG, FID and URL you are using? I’ve tried all the permutations of
fiorg: vanguard.com
fid: 1358 and 5641
url:
and
I either get a blank OFX file or one that tells me “The page you’re trying to reach is currently unavailable.”
ameridan says
On my Blog http://microsoftmoneyoffline.wordpress.com/2010/10/06/cal-learners-review-fidelity-401k-citi-card-and-vanguard-account-info/ , someone has reported working settings for Vanguard for PocketSense as follows:
SiteName : VANGUARD AcctType : INVSTMT #investment fiorg : Vanguard fid : url : https://vesnc.vanguard.com/us/OfxDirectConnectServlet bankid : brokerid : vanguard.com appid : appver : mininterval: 30 #Vanguard doesn't like short intervals? Use 30 day window.If you are able to change the interval in TFBs script, that may resolve your issue.