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.
Actigraph says
I am still using MS money deluxe 2004 version and I am able to download my credit card and bank transactions as well as stock quotes. Here is the trick
1. Set the computer time back to some time in year 2006 or any time before the online update expiration date.
2. Run MS money, and open you file.
3. Change the computer clock back to current.
4. Update your online account, new transactions and latest stock quotes will show up
It should works with those financial institutions that have direct connect services.
Bobby says
Excellent information! I found your post while looking for options to move data from Money Plus. Quicken doesn’t sound like it’s quite where I want it.
As for the scripts, they are fairly straight-forward, but being a programmer of sorts, I quickly started modifying a few things. The first was to replace the batch file with another Python script, since I wanted a “do this for real… y/n” confirmation before executing… and Python is a *much* better scripting environment than batch commands.
I now have a question regarding OFX messages. Per the OFX standard, can multiple “statements” be aggregated into one message? If so, will Money still require you press the “OK” button every time an account is updated? It would be a nice improvement to not have to “accept” the import process for every statement, even though it is trivial overall. Parsing and aggregating multiple ofx files would be easy, but is there an official OFX format for this that Money will accept?
Leo says
Like others who have posted here, I like Money and do not want to change and appreciate the work you’ve done. However, I am not a programmer, but I think I understand what you’re trying to do.
Having said that, I think my requirement is not as demanding as yours. I simply want to update my fund closing prices. I looked at Google Finance (GF) and found, not unlike Yahoo, that I could create a portfolio of my funds. So, I created a portfolio with the intention of manually updating the prices (this is before I discovered your site). After I created my portfolio I noticed that I could download to OFX from GF. I thought, Hey this is great, can Money import it? Apparently it can and does. At least when I tried it, something was imported as I got a success message. But, I don’t know what happened as none of the prices were updated. I guess my problem is that I don’t know how the import function works. Can someone help?
Thanks in advance.
Harry Sit says
Leo – If you open the OFX file from Google Finance in a text editor, you will see it doesn’t have the current prices. That’s why Money didn’t update anything. I will release a small application with a graphical user interface for updating the prices shortly. Stay tuned.
Leo says
Thanks for the quick reply.
Yes, you are correct there aren’t any prices in the OFX file. But, I don’t understand. What is the point of the download OFX option in Google Finance? What data is a person downloading?
I’ll stay tuned for the GUI application and perhaps then you might explain why there are no prices.
Thanks again for all you do.
Harry Sit says
Leo – I can’t answer for Google Finance because I only knew about its export to OFX feature after you brought it up. It looks like you can enter buys and sells on Google Finance and then download those buys and sells to OFX.
Barry says
Is there any down side to changing the system date as suggested by Actigraph?. I tried it and it works. I like this idea better than running the scrips because I have some investments that I have not yet set up to update using the script.
c230 says
Are you aware of a solution with Schwab brokerage? The ofx that I get with QWIN 1700 only contains account numbers – no transactions or positions. I tried MICROSOFT MONEY 1700, but that produces Traceback errors.
c230 says
In the previous post I meant I changed the appid and appver values from QWIN and 1800 to MONEY PLUS and 1800 respectively.
Denis says
Hi,
Thank you for all these information.
You said that in your last Python script, you managed to launch Money import Handler automatically to download your ofx file into money.
I had a look at your script, but could’nt figure out where this instruction was (the script seems to be troncated somewhere). Could you give me this part of code ?
Thanks
Harry Sit says
Dennis – In the automated transaction download script, this line starts the Money import handler:
os.startfile(“\”” + os.getcwd() + “\\” + name + “\””)
Harry Sit says
c230 – If you read the notes on GnuCash Wiki, you will see other people had the same problem with downloading from Schwab using GnuCash. Sorry I don’t know what the correct settings are for Schwab or if it’s possible to download from Schwab at all.
c230 says
Thanks for the reply. I’ve escalated my request at Schwab to hopefully provide some means of initiating the generation of an ofx file. It sounds like the Direct Connect that Schwab uses is a two-way conversation to generate the ofx, rather than a simple download.
I haven’t had luck with Discover yet either, but maybe I need to let a couple of days pass before trying again. I get no errors, but no transactions either.
Harry Sit says
From Scott via e-mail:
Schwab has been working fine for me with these scripts, for both bank and brokerage:
“schwab”: {
“caps”: [ “SIGNON”, “INVSTMT” ],
“fiorg”: “ISC”,
“fid”: “5104”,
“url”: “https://ofx.schwab.com/cgi_dev/ofx_server”,
},
“schwabbank”: {
“caps”: [ “SIGNON”, “BASTMT” ],
“fid”: “101”,
“fiorg”: “ISC”,
“url”: “https://ofx.schwab.com/bankcgi_dev/ofx_server”,
“bankid”: “121202211”
},
c230 says
After downloading my Schwab statements, Money wants to change all of my positions to a quantity of 0. I wonder what values Scott is using for appid and appver. I’ve been using QWIN and 1700.
John Brinnand says
Would appreciate it if anyone can provide the script to download prices for a specific date.
This would be handy for getting month-end prices after the fact.
Thanks
Scott says
I’m using appid/appver QWIN, 1800. All of my position information seems to be getting recorded properly as well. Are you at least getting the transactions through?
I also made some changes to the date functionality (see comment 31 for details) to make the script work with another service provider. I could have sworn that Schwab was working before I implemented those, but I suppose you could try implementing them too.
Harry Sit says
John Brinnand – Reader DK sent me histquot.py. It downloads quotes for a specific date in YYYYMMDD format. I have not had the chance to test it. I’m posting it AS-IS.
c230 says
Thanks Scott,
My Money online services haven’t expired yet – I’m trying out these scripts ahead of time, so I don’t need to try the date trick. I’ll keep trying different things…
c230 says
TFB,
Is it your understanding that online services have to be turned off in Money in order for these scripts to work? Perhaps that is why my “successful” ofx files don’t actually do anything. My quotes.py script works fine for the Dummy account (which doesn’t have any online services associated with it).
c230 says
Scott,
My browser (IE8) only shows the last digit of the comment # in the TFB Blog, so I was looking at the wrong one #51 instead of #31). Now I know which one you referred to. I am not getting an error like you did with JPMorgan, but I’ll try your suggestion anyways.
Michael says
C230, post #70
Refer to prior post #49.
Don’t remove the online update status to your accounts. You can run these OFX downloads and run MSMoney udpates (until they expire) at the same time.
If you remove online update status and MSMoney expires, you must restore your money file from a backup when the accounts had update status to reinstate update status.
Michael
Harry Sit says
c230 – The transaction download script downloads transactions. Money is smart enough to know which transactions have been already downloaded and which ones are new. For this script to show anything in Money, there have to be new transactions not yet downloaded. Don’t remove online update. Just stop using it for a few days or at least check off the box in the list of accounts to be updated for the account you are testing. Test again when there are new transactions in the account you haven’t downloaded before.
c230 says
Michael,
Thanx for the ALERT. I remembered reading #49, but was curious whether the scripts and online updates could be run simultaneously, which as you point out now – yes they can. Unless someone else chimes in, I’m going to give up on Schwab brokerage downloads for now, as I am not having any success.
Dan
c230 says
TFB,
Sorry for my high count of blog comments 🙂
After running the scripts several times this morning for both Discover and Schwab, with no transactions or positions ending up in the ofx files this morning, I did the Money online update and got 7 Discover transactions and 5 Schwab transactions. So there were transactions in the que. I’m giving up for now…
Dan
c230 says
TFB,
I ran the scripts several times this morning for both Schwab Brokerage and Discover CC and got no transactions in either ofx file. Then when I ran the Money online update I received several transactions from both, so I know they were in the que.
I give up for now…
Scott says
c230,
Earlier, you mentioned that you received only account numbers in your downloaded transaction file. Is that still the case?
This is what happens for me if I call the script using only my Schwab account credentials and not an account number–this asks Schwab to just list your accounts and nothing else.
If I provide the account number on the command line when executing the script, it should download the actual account content an transactions for the account.
I’m not sure if this is obvious or not, but I thought I’d point it out. if you are in fact including the account number and it’s still not working, I’d be interested to know what is actually contained in the resulting OFX file.
c230 says
It wasn’t obvious to me Scott. THANK YOU! That did the trick – so you get an ofx file for each account. I looked in the ofx file and Wala, the transactions were there AND the positions.
TFB – If this was pointed out, I missed it, but to help others, I think it should be pointed out that if you have multiple accounts at a brokerage, you need to run the script for each account. Now that I look in your batch file, I see you spell out [acct 1] and [acct2] but for some reason, that didn’t strike a chord until Scott mentioned it.
Michael says
C230,
I have no trouble with Discover Card. Here’s my setup structure:
“discvr”: {
“caps”: [ “SIGNON”, “CCSTMT” ],
“fiorg”: “Discover Financial Services”,
“fid”: “7101”,
“url”: “https://ofx.discovercard.com/”,
},
I don’t have Schwab. Have you edited the ofx file to see if it has any comments or error codes?
c230 says
Michael,
Well now that Schwab works, I just have Discover to work on…
my ofx file seems fine other than that there are no transactions – I see my credit card #.
0INFO20100120104329.942[-5:EST]ENGDiscover Financial Services71012f9a099888404c3c8a0d08d78979955c0INFO419990101170000 my credit card number YNNACTIVEIs this the same situation where I need to have the account # in the command line?
Bobby says
I don’t have access to my files right now, but Discover works fine. Post #78 matches my memory. As with others, you *must* include the account #. Login for Discover is usually the account# plus password, but you still have to include an account# in the account field (last parameter input to the script call I believe).
Michael says
control card:
python ofx-ba-auto.py discvr %discpwd%
I take password from the download.bat file.
is your discover userid
is your discover card account number. 16 digets.
Michael says
control card:
python ofx-ba-auto.py discvr $userid$ %discpwd% $acctnbr$
I take password from the download.bat file.
$userid$ is your discover userid
$acctnbr$ is your discover card account number. 16 digets.
c230 says
OK, thanks everyone. I’ve always heard that ofx downloads were “smart” in that you didn’t have to worry about duplication of transactions, etc., so I incorrectly assumed that since the ofx contained the account number(s) already, they didn’t need to be input as a variable. I sure was wrong!!
Hopefully if TFB adds a note like “Unless account number is part of your python statement, you will not input any positions or transactions in your Money import file.”, this will save others from going through the frustration I experienced.
c230 says
I sure feel stupid! Nowhere is it implied that account number is optional, so I don’t know why I kept omitting it. All is well now – all of the scripts work great.
TFB, you’ve got a great blog, and I’ve joined your Facebook group to insure I don’t miss your postings now that I’ve found you through the Microsoft Money Discussion Group. The python scripts will keep Money going for quite awhile.
Bobby says
FWIW, I’m having an issue with duplicate entries on Discover downloads. I’ve found that duplicate entries randomly match existing transactions. If I fix the duplicates, and then re-download the transactions soon after, all is well. If I come back and do the same a few days later, it re-enters a bunch of duplicate transactions again… not all of them, but a lot. It seems random as to which ones don’t match correctly. I’m going to watch this and see what happens, but if there’s a setting in Money, I’d be interested to hear about it. Otherwise, I’ll look into a fix on the OFX end of things.
c230 says
Bobby,
I got 49 Discover transactions today in my ofx file that were all repeats. I right clicked each one and selected delete rather than accepting them to delete later. I don’t plan on doing both methods in parallel, so I didn’t see this as a problem.
My guess is that if we only use the python script method, as TFB stated – we probably won’t get repeats.
Bobby says
As of now, I’m only using the scripts. Before starting, I deleted all online connections in Money, and then started *only* using the OFX script. It works well, except that the duplicate entries for Discover are random and Money doesn’t see them as duplicates. What is odd, is that it only happens w/ Discover and it doesn’t happen with every transaction. It’s either a date/time issue (something I saw w/ Discover and Money before), or maybe something to do with the recent deletion of the online connections within Money itself. I’m going to shorten my download window to maybe 10 days for a while, and see if it gets past the issue once I’ve gone past the 10-day window.
c230 says
I don’t like the idea of having a batch file with all of my account numbers, passwords, userids, etc. so I’m going to try QuickBFC to encrypt the file:
Quick Batch File Compiler convert your batch files into actual program (.EXE format) in one click. This program may be run on Windows 2000/2003/XP/Vista without any limitations. An .EXE file is much harder to casually reverse-engineer, so this could be a way to conceal a particular batch file’s operations from an end user. Content of your batch file will be encrypted and protected from changes.
I’ll let you know how I like it afterwards.
hokie94 says
The “Bank Balance” for my accounts do not update. Is there something I’m missing? Some other piece of code that needs to be written that will make Money read the “Bank Balance” that is listed in the ofx file and update it within money?
Thanks
c230 says
I’ll see if I can help. When you click on “View downloaded statements”, you’ll see 2 columns – Bank balance and Local balance. If you reconcile your accounts periodically, the amounts should match, or at least be pretty close, depending on whether you have future transactions recorded etc. (ie. upcoming bills paid).
If you are downloading statements, you’ll see the summary of that downloaded statement when you click on the “unread” account, but for cash accounts at a brokerage, you may be working off a delta from your last reconciliation (daily updates vs. a monthly statement as an example.
See if you are still having a problem after reconciling your accounts.
hokie94 says
c230
I’m not sure I was clear with my question.
Currently, the “Bank Balance” column indicates that Bank balance as of my last “live” download (12/15/09) which was the last time I was able to download information within MS Money, not using any of the awesome scripts here.
Now, when I use the scripts, the bank balance remains unchanged from 12/15/09. New transactions download just fine, but ht “Bank Balance” remains what it was on 12/15/09. I tend not to like a discrepancy between the “Bank Balance” and the “Local Balance” and moving forward, if that balance does not update (ever), this is going to be an issue.
Thanks for any help you can provide on the issue.
c230 says
I’ll have to defer to someone else as I only have 1 day’s experience with the script 😉
I saw that the correct bank balance was there, but it may have already been correct. If you get paper statements once per month or check the web statement and reconcile atleast that frequently, then I think I’d have confidence in the balance. My brokerage cash accounts don’t even transmit a balance, so I reconcile them every 6 months or so and have never had a problem, since all of the transactions are downloaded.
c230 says
Bobby,
It seems that you are correct regarding a slight difference in the downloaded transactions vs. the online updates. A sample of a downloaded transaction…
STMTTRN
DEBIT
DTPOSTED>20100110170000
TRNAMT>-35.79
FITID>FITID20100111-35.7900021
NAME>Sams Club – #4778 Auburn Hills M
/STMTTRN
indicates that it has got to be either the date/time or the ID#. Not really a problem except as you stated they have to be manually matched if running both updates in parallel.
hokie94,
If you look at the end of your ofx file you should see your balance info…
LEDGERBAL
BALAMT>-1258.57
DTASOF>20100121070000
/LEDGERBAL
so you can verify that it is part of the file.
By the way, a tool you might find handy is MT2OFX which can take CSV, QIF, and OFC files and reformat them into OFX for import into Money. It also makes an OFX file easier to read.
hokie94 says
C230
I see the balance in the ofx files for all statements I download. What I’m asking (humbly requesting) is that someone write some code so that that part of the ofx file is read and updated by money.
The other nice to have option would be to delete ofx files that are processed.
I don’t mean to sound ungrateful…these scripts are absolutely awesome and a lifesaver.
c230 says
The 2nd part of your request is easy if you’re using a batch file. and it was posted by T (comment 10). You can’t delete them until they’ve been processed, so it’s much easier to delete them the next time you’re creating ofx files.
————————————-
@echo off
REM Place this .bat file in the same directory as your Python scripts.
REM delete the old files.
del *.ofx
echo Downloading Discover
python….
————————————-
As to the 1st part of your request, there is no code to write for Money, it should just happen (but doesn’t always, as you know) if the data is compatible. I waiting for an expert to chime in, yet still trying to help. You could try changing appid and appver values from QWIN and 1800 to MONEY PLUS and 1700 respectively. You could also try opening Sample.mny and creating an account from scratch to try your ofx on to see if the expected results occur.
c230 says
TFB,
Using your automated script and batch file example, I’ve done the same and included your Quote script as well. Then in order to not have a batch file on my PC with sensitive information, I downloaded Quick BFC from http://www.abyssmedia.com/quickbfc/ and it creates an encoded EXE file so that you can get rid of the batch file. You simply open the bat file, click on the Build button, and name your new file (keep it in the same folder as your python scripts). I’m happy to report that it works very nicely.
If you need to make changes, simply repeat the entire process, as you can’t edit the EXE file.
——————————————
@echo off
REM Place this .bat file in the same directory as your Python scripts.
REM delete the old files.
del *.ofx
echo Downloading [name of the site]
python ofx-ba-tfb-auto.py [name of the site] [your online login] [your online password] [your account number]
— repeat for all of your other accounts using TFB’s automated script…
echo Downloading other quotes
python quotes.py
echo Done.
——————————————
Bobby says
After multiple tests, Money would always duplicate Discover transactions and rarely match them. I have no idea why, but it is obviously unacceptable. So, I began editing the script and started using the last download date+time for each account. That fixed the credit account “duplicates”. Unfortunately, this did not work for a Vanguard account that I have. Requesting too short of a time interval for that account produces a malformed OFX file, that subsequently crashes Money. Not good!
The fix I’m using now is to remember the last connect time for credit and bank accounts, but to use a 90 day window for brokerage accounts. It’s working perfectly. I placed the data get function inside an exception clause so that if the https connection fails, the “last date/time” doesn’t update. I’m going to add a check that the OFX file was created and isn’t empty as well. A better option will be to include a “time window” field in the data structure for each account, where the default window is some # of days (or zero to use the last successful time).
There’s probably more to this than I’m remembering right now (I did the coding last week), but it’s looking good. If anyone is interested, I can see if I can figure a way to post it somewhere. FWIW, I’m using a Python wrapper rather than a batch file. I had never used Python until now, but I’m hooked (I program a lot and *have* used other scripting shells, but never Python for some reason).
c230 says
Bobby,
I found an old Moneydance trouble ticket (#1193) regarding duplicate Discover transactions and sure enough I’m experiencing the same thing. If you compare my sample transaction in comment 94 to this:
STMTTRN
DEBIT
DTPOSTED>20100110170000
TRNAMT>-35.79
FITID>FITID20100111-35.7900022
NAME>Sams Club – #4778 Auburn Hills M
/STMTTRN
you’ll see that the last digit of the ID# is different, which explains why the transaction doesn’t match up with a previously downloaded duplicate. This is the same condition that others have experienced for the last 2 – 3 years with no apparent fix. I can’t find an explanation as to why Discover would do this.
So I thought I’d try downloading from the website to see if that would be less troublesome than the scripted ofx request and I get this as part of 46 duplicates:
STMTTRN
DEBIT
DTPOSTED>20100110170000
TRNAMT>-35.79
FITID>FITID20100111-35.790
NAME>Sams Club – #4778 Auburn Hills M
/STMTTRN
———————————–
My conclusion/finding/request: Perhaps TFB can modify the code to only obtain the first 3 digits after the ID# decimal, rather than all 7 digits.
c230 says
Bobby,
I also noted that the FID for Discover using the Web OFX download is 9625 vs. 7101, so I tried that in the OFX.PY thinking that it might generate the data differently, but I got an error instead. I still think the solution might be to truncate the last 4 digits of the generated FITID (transaction ID) for the Discover OFX.
To try out my theory, someone that hasn’t downloaded Discover transactions via OFX.PY could manually modify their OFX file generated from the Discover website “Download into Money” option, prior to importing the file into Money.