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.
indexfundfan says
tfb, would the above scripts also download security prices?
Harry Sit says
Not directly. The brokerage statement includes the positions and the current prices. If you have positions elsewhere, those prices are not updated. However, it is conceivable to create a dummy brokerage account in Money, construct a fake OFX statement with prices from Yahoo! and then import that fake statement for the dummy accout. In your Money reports, you just exclude that dummy account. I will leave that exercise to the future or a more capable developer.
DT says
Seems like all you really wanted to do was fix the
downloads issue with MS money, and now that
you’ve solved that bugaboo, you’re gonna stick
with MS Money after all. Is that about right?
I mean, it appears you’ve just talked yourself
into staying with MS Money, due to these Python
scripts.
Are you planning a summary post for your conclusions?
J says
Not sure about MS money, but Quicken won’t import files without validating with the bank. And it won’t validate with the bank after it expires.
Simon Fell says
The scripts are a nice idea for sticking with Money, but it seens like once money is dead, the OFX download format will soon be after it, and I can see most financial institutions might stop offering it.
Harry Sit says
@DT – Yes, I’m going to stick with MS Money as long as the Python scripts work. Maybe I will write a summary post after I get the stock quotes working and after I take a look at Quicken 2010.
@Simon – That’s a possibility. We will just have to see. I hope Quicken also hit those URIs and the financial institutions will continue to support Quicken. The scripts just piggy back on Quicken.
Chrisem says
Well done! Thank you. At the least it buys time for other options to develop.
J says
To test if import works for MS Money, try pulling the network cable after downloading the files, then do the import. If MS Money needs to phone home, like Quicken does, you should get an error during the import.
Good luck!
Harry Sit says
J – Yep, tested. After downloading the transactions, I turned off WiFi on my laptop. Money 2006 imported the files successfully.
T says
TFB,
Your posts absolutely made my day. Probably my week too, which otherwise would have been spent buying Quicken and converting heaps of data into it.
I wrote a small Batch file to download and import all of my accounts, just like Money used to do, which doesn’t require a hard-coded password…posting it here in case anyone else is interested.
Thanks for the posts.
P.S… The BAT relies on your Automated Script – which accepts password as a parameter – for both Bank and CreditCard. If you have multiple accounts at the same bank, using the BAT file below requires a few small changes to the Python in order to make the filename on each OFX unique, thus to prevent overwrite. In short, change the “client.doQuery” line to include “argv[4]” as part of the filename. Something like
client.doQuery(query, argv[1]+argv[4]+dtnow+”.ofx”)
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
@echo off
REM Place this .bat file in the same directory as your Python scripts.
REM delete the old files.
del *.ofx
REM Prompt for password. If all accounts use the same password,
REM you’re done. Otherwise repeat this “set /p Password=” command
REM before each account having a different password.
echo Enter Password
set /p Password=
echo Downloading [Credit Account 1]…
python ofx.py [account] [user] %Password% [acct#]
echo Downloading [Investment Account 2]…
python ofx.py [account] [user] %Password% [acct#]
echo Downloading [Bank Account 3]…
python ofx-ba.py [account] [user] %Password% [acct#] [CHECKING or SAVINGS]
REM repeat as necessary for each account…
echo ————————————
echo Processing files.
REM this will “execute” the ofx file, which should open it in Money. If Money
REM is running, it will be imported automatically. If not, Money will process
REM the import the next time it starts.
FOR %%D in (*.ofx) DO START %%D
echo Done.
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Dave says
I went through a similar procedure to the one you have been describing in your “Replacing MS Money” series.
I am evaluating two options at the moment.
My first option is to use Yodlee to handle all the downloading. It is an easier way to do the same thing you are doing with the scrips. Yodlee downloads and aggregates all my transactions from banks and investment institutions (and some other vendors too). Then I export from Yodlee and import to GnuCash. Here is a discussion with more details on UbuntuForums:
http://ubuntuforums.org/showthread.php?t=1049798
The second option I’m considering is “My Money 2.0” by MTH. I’m not sure why it never came up on my radar previously, but I’m going to test it now. Here’s a link:
http://www.mthbuilt.com/
I decided that in addition to moving away from MS Money, I wanted to move away from all Microsoft products, so my goals are a bit different, although my requirementes are very similar to yours.
DK says
TFB: Again I appreciate your pulling this information together. I have the following suggestions to help clarify the use of the various scripts you provide in your article:
1. Using the ofx-ba.py script for downloading bank statements requires FOUR arguments in its call, not the three you mention above:
[name of the site: which is the short name given to the site parameters in the script]
[your online login/username]
[your account number]
[CHECKING/SAVINGS].
2. The modified script appears to only allow for credit card and financial institution downloads, excluding bank statements. The parameters required for this script are:
[name of the site: which is the short name given to the site parameters in the script]
[your online login/username]
[your password]
[your account number]
3. The ofx.py script is for credit card and financial institution downloads only and the parameter call is as described in your article.
Thanks for your invaluable help, DK
Harry Sit says
DK – Thank you for the notes. I didn’t do anything to the ofx-ba.py script because I don’t have a bank account. I will do the same modification and update the post later.
Dave says
I’m transitioning gradually from MS Money to something such as GnuCash. In the mean time it may interest others to know what I’m doing. I found out that MS Money will run in Ubuntu 9.04 under Wine 1.0 — and online banking works. Everything works**.
I’m using Money 2003 Deluxe and Business. All my online banking works. My investment account transactions are downloaded too. (Stock prices in my portfolio are not updated automatically, but that’s no big deal.)
I think these older versions of Money (e.g., 2003, 2004) lack the code that makes them stop downloading transactions.
One could revert to an older version of money by using this method:
http://wiki.github.com/mulicheng/csv2ofx/microsoft-money-mapping-file
Getting Money to work under Wine:
There are some tricks to it; find them here: http://ubuntuforums.org/showpost.php?p=7963718&postcount=13
Harry Sit says
DK – I found out that the ofx-ba.py script actually handles all three types of accounts: credit card, brokerage, and bank. I re-did my modified scripts based on ofx-ba.py. I updated the post and links. I hope everything is clear now.
mmblanco says
TFB – Thanks for this valuable scripts.
I want to know if there’s a way I can update the historial values for mutual funds quote prices. I have received an excel doc with two columns, one for the date and the other for th price. I wold like to import this data into MS Money, so that I can make some index comparison, etc.
Thanks in advance.
Harry Sit says
mmblanco – Sorry I don’t know how to import historical prices.
John Brinnand says
TFB: Along with others I would like to thank you for sharing your solution to updating the stock/fund prices after Microsoft pulls the plug on us. I have spent a lot of time, without success, trying to come up with a way that I could retain all my information in Money and avoid having to enter over 30 stock/mutual fund prices manually – it was a big relief to find the answer on “TheFinanceBuff”.
I have had no problem using your quotes.py script to create the “ofx” file and importing the prices into Money Plus Deluxe.
Rather than having the dummy “Investment” account showing a market value I have changed the “number of units” in the script to zero. Even though Money states “You have no unread statements” it does appear to update the “Price File” on a daily basis. I tested this with 5 consecutive daily “ofx” files with no connection to the Internet.
I am quite happy to just update the prices on a daily basis after the market closes but I did see that someone would like to update the prices though out the day. The only way I have found how to do this is: –
1. Do the first update of the day as usual.
2. One the second, and subsequent updates for the same day open Money and delete the dummy “Investment Account” (As there are no open investments in it, it will delete with one click).
3. Run a price update – Money will ask you to set up a new account – click “New” and then “Next” a few times and enter “xxx” for Bank Name. Money will then import the ofx file and update the “Price File” with the latest prices.
Not an ideal solution but it seems to work and could come in handy if you update before all the closing mutual fund prices are in and have to do a second update.
Harry Sit says
John – Thanks for the tip on making the number of unit zero. I never thought of that. I think that works better than unit of one because it won’t affect the holdings and reports. Let me try it. If it works well, I will update the script.
DK says
TFB, I have modified the quotes.py script slightly to download historical security prices for single days. It works basically like your script. I don’t maintain a website myself, but am willing to pass the script and accompanying batch file to you for posting. I like this new option as it lets me download prices for days on which I missed downloading prices with the quotes script.
DK
DK says
TFB: Has anyone tried to connect to RBC Wealth Management, formerly RBC Dain Rauscher? The information from the fi file (RBC Dain Rauscher and 8035) do not work. Thanks!
alw says
Am so happy and relieved to have found your site as I’ve been using and depending heavily on MS-Money for almost 15 years now and did not relish the idea of converting data to Quicken or another PF program. I’ve installed Active Python and your various scripts documented here and the quotes download works great for me. When attempting to execute your auto script though either at the command line or with the batch file I get the following error. I haven’t edited your script at all and am just attempting to use my own AmEx and Chase login credentials. Any thoughts as to the cause?
Traceback (most recent call last):
File “ofx-ba-tfb-auto.py”, line 212, in
client.doQuery(query, argv[1]+”_acct.ofx”)
File “ofx-ba-tfb-auto.py”, line 178, in doQuery
h = httplib.HTTPSConnection(host)
AttributeError: ‘module’ object has no attribute ‘HTTPSConnection’
alw says
I posted the above HTTPSConnection error message and am happy to report that I’ve found resolution. It turns out that Active Python for Windows 64 OS doesn’t come with the necessary SSL support to run these scripts. I am using the 32 bit version instead now and the scripts run fine. Thanks so much TFB for this fine site and solution.
Bob says
Can this be set up to just get quotes into MOney without contacting a financial institution. I don’t want updates from my bank. I just want to update stock prices
Harry Sit says
Bob – See follow-up post Download Price Quotes to Microsoft Money After Microsoft Pulls the Plug.
Julee says
I am not a programmer but I am determined to keep money downloads working.
When I attempt to run the python script at the cmd prompt I get the following error message.
python: can’t open file ‘ofx.py’: [Errno 2] No such file or directory.
I have tried saving the file in a variety of places so it can be found but to no avail.
I had to use Python 2.6.3.7 and chose the Windows(x86) version, rather than the Python 2.6.2 that you suggested. It doesn’t seem to be available anymore.
Julee says
Now that I have used the cd command to change directories to the python26 folder, I am getting a different message.
Traceback (most recent call last):
file “ofx.py”, line 167 in
client = OFXClient(sites[argv[1]], argv[2], passwd)
KeyError: ‘[discover]’
Please help.
Julee says
I got it to work now. Thanks for giving me this fun experience of feeling like I am back in college in my beginning computer science class. Clearly I learned a LOT.
Julee says
Has anyone successfully tried to download account information from an entity that uses Yodlee? I get a long string of problems relating to sending with a final soctet.error [Errno 10061]
No connection could be made because the target machine actively refused it.
I have two a bank account and a couple of credit cards athat have Yodlee as the Org and use the same provider url.
Please help.
Randy says
When I use ofx.py to get my Scottrade account (had to add “fid” field to the sites data), I do get an ofx file. When Money imports it, I have to match every symbol to one in my account (they all seem to be the same anyways) and OK it. Then it shows all my share counts as 0. It also appears to be from 10-24-2009 and not showing any activity newer than that.
Is anyone successfully using these scripts for Scottrade?
Scott says
Thanks so much for posting these scripts! These are exactly what I was looking for.
I admit that I was having problems with getting it to work with JP Morgan Retirement Services–the server kept returning 2020 error codes (something about invalid date in the request).
I tracked it down by comparing what Money was sending in its request. It turns out that JP Morgan doesn’t like to have the time included in the DTASOF field in the request–it wants only the date. In the “_invstreq” procedure, I had to add the following line:
dtnow_notime = time.strftime(“%Y%m%d”,time.localtime())
and then adjust the DTASOF parameter as follows:
_field(“DTASOF”, dtnow_notime),
I don’t know what impact this will have on other brokers, but the JP Morgan import is now working fine for me.
Thanks again!
Inko says
TFB:
Thanks so much for the great work. I have one more challenge for you though. In addition to a userid and password, my bank also (during successive logins) rotates through several other predefined qualifying questions (i.e. What is your father’s middle name) as a additional security feature. Money does support this feature. When setting up the online access for this bank, you’re prompted with a ledger that itemizes the questions and responses. Any idea on how to replicate this feature in your scripts.
Thank you…Inko
Harry Sit says
Inko – Sorry, the script can’t handle that challenge.
George Lindauer says
I’ve been unable to get any of these scripts to do anything for Chase using Active Python 2.6.4. It only calls five programs, the first two the originating python program and the last three to httplib.py lines 974, 391 and finally 355 before quitting.
2.6.4.Traceback (most recent call last)
httplib.py line 355, in _read_status raise BadStatusLine
httplib.BadStatusLine.
Please help!
Harry Sit says
George – Chase changed the OFX server address around Nov. 15. The new address is
If you replace the old address
with that new address, it will start working again.
George Lindauer says
Thanks! That worked fine.
Michael says
Love the help this site is providing. To bad Microsoft won’t help us.
Can I get some insight to fixing the following error?
File “C:\Python26\lib\urllib2.py”, line 516, in http_error_default
raise HTTPError(req.get_full_url(), code, msg, hdrs, fp)
HTTPError: HTTP Error 400: Bad Request
I’m using ofx-ba.py sample.
Michael says
I got the ofx.tfb.auto.py to function.. I’ll use it. thanks. This stuff is fun.
DK says
I am getting the same error as referenced in message 29. I am trying to access a bank which has Yodlee as designation. Does anyone know the source of this problem – I get urlopen error 1006: no connection could be made because target machine actively refused it. Any help will be appreciated. Thanks.
Harry Sit says
DK – This script will not work with Yodlee because Yodlee does not provide a true OFX interface. Yodlee charges Microsoft for its service. It will not let you just connect to it. If the bank itself does not provide a web download, the best workaround is perhaps using Yodlee MoneyCenter. I heard you can export to OFX from Yodlee MoneyCenter, although it’s manual, not automated.
Michael says
Anybody solve the OFX URL access to Dodge and Cox mutual funds?
Thanks
Harry Sit says
Michael – According to the online banking directory zip file, Dodge & Cox mutual funds uses Yodlee. I don’t think this script will work for Dodge & Cox.
George says
The ofx-ba-tfb-auto.bat works fine at Chase for a credit card but not for checking. I tried a single python script for checking
ofx-ba-tfb-auto.py chase chase_login chase_password account # CHECKING
which logs on, and produces a small OFX file but does not download banking information. The downloaded OFX information from Chase for the checking account is
0INFOSUCCESS20091226164624.986[0:GMT]ENG0INFOSUCCESS20091226181846.172[0:GMT]ENGB110898f51e5fc49b6b4494bfe3d34ab0c98aec2003ERROR4</OFXat which point it ends.
The credit card information which works for the same login without CHECKING is
0INFO4USD etcIt appears that the ERROR prevents OFX from asking for my account number. Is there something I’m missing?
Harry Sit says
George – For a bank account, you will have to put the ABA routing number in the script (modify from the “usaa” profile in the example script). If you have both a credit card and a bank account with Chase, you will need two site profiles: chase_cc with “SIGNON”, “CCSTMT” and chase_bank with “SIGNON”, “BASTMT”.
George says
I tried changing the profile for chase to,
“chase”: {
“caps”: [ “SIGNON”, “BASTMT” ],
“fid”: “10898”,
“fiorg”: “B1”,
“url”: “https://ofx.chase.com”,
“bankid”: “074000010”,
},
where 074000010 is the routing number for Chase in Indiana but once again It read in the fid and fiorg information but then gave the CODE 2003 SEVERITY ERROR STATUS CLTCOOKIE4 STMTTRNRS BANKMSGSRSV1 with no downloaded information. Is there something else I need to change?
G says
Does any body know any way to get more than 30 days of historical data? I waited about 2 months after money expired to get this working and now I’m missing a month of data. Thx
Michael says
Look in the main PY routine for:
if __name__==”__main__”:
# print “Number of args”,len(argv)
dayamt = int(argv[5])
dtstart = time.strftime(“%Y%m%d”,time.localtime(time.time()- dayamt*86400))
dtnow = time.strftime(“%Y%m%d%H%M%S”,time.localtime())
dayamt should be 31 (I changed this item to be a variable taken from the control cards. I changed default to 8 in my bat file.) Change to number of days you need.
George says
The bank id for Chase savings apparently was incorrect. It should have been 083000137. Although I live in Indiana, and my checks use 074000010, the routing number needed was that for Kentucky! Using this, the download worked.
I just purchased a new computer and installed MM 2007 (which does not require activation) on it. I imported my data from my old computer using a .qif file and the MM program update feature successfully updated the data from Chase.
Michael says
*** Here’s a heads up for MS Money users and using these routines ***
Firstly, remember to take backups before any account setting changes that deal with MSMoney’s online access setting, once your access has expired.
I discovered that after I removed the online access settings for my accounts (of which I have about 20) two things occured: First, activity previously downloaded, and then downloaded once again, from the bank showed up as new/matching existing entries and I had to ‘accept’ them end to the system. Previously, previously downloaded transactions were ignored. Second, since you have expired your MSMoney online access, you can’t restore it.
I used a backup file to get the online settings restored.
I recommend you don’t turn off your online access after MSMoney expires.
Thanks so much to all the comments and help being offered at this site. It has been a life saver for me.
Barry says
I have to say thank you, thank you, thank you for putting this information out on the net. I saw this script update info months ago but having little knowledge about scripts and batch files it seemed way too complicated for me. I was just going to buy Quicken but the Quicken 2010 reviews that I’ve seen have been lukewarm and I was afraid of how well the Quicken conversion tool would actually work. Since Fidelity and Vanguard are major holdings of mine I decided that since you had a working script that I would finally give this a try. It probably took me much longer than it should but I finally got downloads working for Fidelity, Vanguard, and security price updates. I still have other accounts that I will probably update manually but since I do my bill paying with Fidelity SmartCash that is the account that I really had to have updated automatically instead of manually. So screw you Microsoft, instead of getting cash from me every 2 yearsto renew my software like before now you get nothing and I still have a useful Money product. Don’t know if these companies will support OFX downloads once all current Money users are phased out but this is great for now. So thanks again to the original poster and for all the comments that followed.