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.
Chris says
Got an error on my ING Direct Bank accounts using Money 2000. After downloading a .ofx from the website and comparing, I was able to determine that Money needs a DTEND parameter for this to be successfully imported. I updated _bareq with the code _field(“DTEND”, dtnow), placed right after the DTSTART line. Now works like a champ!
Thanks for the great fix! This will be even better than before everything was sunsetted (assuming I can figure out why the Quotes part isn’t working for me).
Chris says
Anyone get this working for Sharebuilder? I set up my online access code, but i still get an error back in the .ofx file…
Matt says
I switched from a “bricks and mortar” bank to ally bank and so far like the service from them. Except, ally does’nt allow direct OFX transactions but provides for downloads of account statements into MS Money, Quicken or CSV format. The MS Money is an OFX transaction which does go into MS Money Sunset Edition except for one problem. The payee information is in the memo field as a double entry:
Beginning of OFX download:
OFXHEADER:100
DATA:OFXSGML
VERSION:102
SECURITY:NONE
ENCODING:USASCII
CHARSET:1252
COMPRESSION:NONE
OLDFILEUID:NONE
NEWFILEUID:NONE
Individual Transaction:
POS
20120904121355
-20.20
1024242578864
Withdrawal
Wal-Mart Super Wal-Mart Super TAYLORS SC US
(I edited out all of the spaces, tabs and CR’s to make it more readable)
I ran the OFX file through MS OFXAnalyzer and it comes up clean.
Any suggestions as to what to do to avoid all of the cut and paste necessary to get the information into the “Payee” field. I understand that this might require some Python code.
Thanks!
Matt says
Individual transaction got kind of messed up and time limt booted me out. Here it is again:
STMTTRN
TRNTYPE POS
DTPOSTED 20120904121355
TRNAMT -20.20
FITID 1024242578864
NAME Withdrawal
MEMO Wal-Mart Super Wal-Mart Super TAYLORS SC US
/STMTTRN
Any suggestions as to what to do to avoid all of the cut and paste necessary to get the information into the “Payee” field in MS Money? I understand that this might require some Python code.
Thanks!
Charlie says
I don’t know if this is a dumb question, but I modified the bank account script, opened a Run Command prompt, which shows up as:
C:\Users\Owner>
then when I enter
python ofx-ba.py [followed by bank Id, User Id, acct #, CHECKING], I get:
‘python’ is not recognized as an internal or external command, operable program or batch file.
The python executable is in C:\Python27. I don’t even know how to change directories. Sorry.
Suggestions appreciated, thanks.
Charlie says
I figured it out.
I had to
chdir C:\python27
then
C:\python27 python [directory path]ofx-ba.py …
Barry says
I have been running updates with these scripts since the demise of Microsoft Money. I got a new laptop and I need to transfer the existing scripts on my old computer to the new one. It looks like the setup script, getdata script, and everything related is in a folder called ofxpy_pocketsenese. Since I have existing scripts that work do I just need to copy everything in the ofxpy_pocketsense folder to the new computer? Do I need to install active_python on the new computer? I’m not very tech savvy and it took me a while to get these scripts to work so I would like to get them on the new computer in the easiest way possible. I am a little confused on what I have to do for the new computer so it would be appreciated greatly If someone can give me a step by step on what I have to do to get my already working scripts from an old computer to a new one.
Harry says
Barry – You are using PocketSense, which improved the script here in many ways. Please see PocketSense instructions.
Tom76017 says
My Edward Jones connection started erroring off this weekend after months of success with PocketSense. OFXget is still working from the OFX Home site. Any ideas or similar experiences?
STATUS>
CODE>2000
SEVERITY>ERROR
MESSAGE>[ofx.unsupportedService]
The server does not support the SIGNONMSGSRQV1 request, or a request was made against an invalid organization. Please verify the Organization name and FID and try again.
[ref: A750686F0141FAB60000013A42BD1709BB39]
/STATUS>
Tom76017 says
Sorry, the previous comment was related to PocketSense.
Himanshu says
I want to fetch Bank Transaction details for Accounts that provided by user
I am Using Asp.net and use vb.net language
I Pass Folllowing Parameter “AccountActivity” “username”, “password”, “ACCOUNTID”, “BANKID”, “ORG”, “FID”
But i got that error for Response
Please Help me to solve that issue
2000ERROR[ofx.unsupportedService] The server does not support the SIGNONMSGSRQV1 request, or a request was made against an invalid organization. Please verify the Organization name and FID and try again. [ref: 9BB4703D006397EB0000013B51353E3E69F2]20121201001441.666[-5:EST]ENGChase Bank16010af5f18d-098b-4cab-94dc-5a033135813c2000ERRORThe server does not support the BANKMSGSRQV1 request, or a request was made against an invalid organization. Please verify the Organization name and FID and try again. [ref: 0af5f18d-098b-4cab-94dc-5a033135813c]
Michael says
Been running the OFX download / import routines for a long time. Running Win XP SP3. All of a sudden I began getting the following failure with MSMoney: (taken from Application Event Viewer)
Faulting application msmoney.exe, version 17.0.0.3817, faulting module utlsrf08.dll, version 17.0.0.3817, fault address 0x00006f6b.
I download about 7 files, and only one or two of them create this failure. Once the import fails, MS Money will not run. It starts up, show “online updating…” in the upper right corner, then crashes.
I have to restore to an earlier MSMoney file, and bypass this offending OFX file. Other OFX’s from same bank
import okay.
Anyone else having this issue?
ameridan says
You’ll find the solution in my blog entry http://microsoftmoneyoffline.wordpress.com/2012/12/02/windows-8-64-bit-version-compatibility-with-sunset-money/
My blog actually started because of TFB’s creation, but I now use PocketSense instead for gathering OFX downloads.
Michael says
Patching MNYOB99.dll did the trick. Thanks so much.
joel brandt says
Microsoft money no longer automatically downloads fund and stock prices as of July 1, 2013. Has anyone else experienced that? Prices can be inserted manually, but with mutliple positions, it is quite time consuming.
Jason says
Bank of America changed their download file types this morning! I tried to download the Web Connect file type (the other 2 choices are excel or printable text formats), but it errored out in Money. Anyone have a workaround?
Jason says
Anyone get Bank of America to work? I can download them from the website, but using my standard script I now get the below error. It was working fine up until BOA made changes. They’re saying they no longer support some older Quicken versions. Anyone still download from BOA?
Traceback (most recent call last):
File “ofx-ba-tfb-auto.py”, line 252, in
client.doQuery(query, argv[1]+dtnow+”.ofx”)
File “ofx-ba-tfb-auto.py”, line 213, in doQuery
“Accept”: “*/*, application/x-ofx”
File “C:Python27libhttplib.py”, line 941, in request
self._send_request(method, url, body, headers)
File “C:Python27libhttplib.py”, line 975, in _send_request
self.endheaders(body)
File “C:Python27libhttplib.py”, line 937, in endheaders
self._send_output(message_body)
File “C:Python27libhttplib.py”, line 797, in _send_output
self.send(msg)
File “C:Python27libhttplib.py”, line 759, in send
self.connect()
File “C:Python27libhttplib.py”, line 1144, in connect
self.sock = ssl.wrap_socket(sock, self.key_file, self.cert_file)
File “C:Python27libssl.py”, line 344, in wrap_socket
ciphers=ciphers)
File “C:Python27libssl.py”, line 121, in __init__
self.do_handshake()
File “C:Python27libssl.py”, line 283, in do_handshake
self._sslobj.do_handshake()
socket.error: [Errno 10054] An existing connection was forcibly closed by the re
mote host
Ozarn says
Hi,
i had the same error and i changed the url to https://eftx.bankofamerica.com/eftxweb/access.ofx, that fixed the problem for me. I can download it now.
gun schwarz says
I am using Money Plus Sunset for a few years now.
However, after using “CCleaner” I can’t open Money anymore and when I use my backup file:
my portfolio3 Backup_2014-04-10_234802.mbf Money opens but wants to convert my file to a *.mny file.
That tells me (I think) that all of a sudden I am using Money 2006 that’s still on the hard drive.
My question: would it be ok to uninstall all Money programs and reinstall Money Plus Sunset, then the *.mbf file should open up , correct?
MW says
It appears Vanguard changed something with the OFX download, it is no longer working. Does anyone have the updated server information, or did they completely disable these downloads?
RDT2 says
I just got done fighting this!
So at the bottom of the ofx-ba-tfb-auto.py is the Main section. The line that needs tweaking is:
elif “INVSTMT” in sites[argv[1]][“caps”]:
query = client.invstQuery(sites[argv[1]][“fiorg”], sys.argv[4], dtstart)
Change “fiorg” to “brokerid”. Now at the top of the file, for all of your sites duplicate the fiorg lines and then rename the new line brokerid. Now for Vanguard you will need to have the following info:
“vanguard”: {
“caps”: [ “SIGNON”, “INVSTMT” ],
“fiorg”: “Vanguard”,
“fid”: “1358”,
“brokerid”: “vanguard.com”,
“url”: “https://vesnc.vanguard.com/us/OfxDirectConnectServlet”,
},
And now it works! So in summary, it looks like they are requiring a specific fiorg and brokerid and they are no longer the same value.
MW says
Thank you thank you thank you. I’m glad someone more knowledgeable than me still uses this script!
Can I ask how did you go about debugging this?
RDT2 says
So the error code was for account not found. Originally I went down a rat hole where I ended up buying quicken to ensure that Vanguard didn’t break something that allowed me to get the data over OFX. Quicken worked so no issue on Vanguard’s end.
So I realized that the error code was no account found, but I was logging in ok, so that means the first part of the OFX messages was working. I remembered that if you run the script without an account number you will get a list of all of your account numbers at Vanguard. I noticed that the brokerid value was different than what I was sending as the code was using the fiorg data as the brokerid. So I changed the fiorg and then it was still broken. So then I changed it so the fiorg would be Vanguard while the brokerid would be vanguard.com. Success.
Now I have to work on getting a refund through the 60 day refund policy on Quicken as I have no desire to use their software. I’ve actually written my own software and database to keep track of all of my retirement information, crunch the numbers and then upload it to Zoho Reports so I can make pretty graphs.
MW says
Nice work!
Another site you could check is PocketSense, http://pocketsense.blogspot.com/
He also figured out the correct parameters and his script must already account for the separate brokerid. But thanks for fixing this script. I wonder how many people out there are still using this one.
Barry says
My comment is not about the scripts but I do not know where else to ask this. Also it looks like the last comment here was back in September so I hope that somebody sees this.
My problem is that I used to be able to look at Portfolio Manager for a summary of my holdings. Before Money was discontinued you could get to Portfolio Manager by clicking on the “Investing” tab and then there was a “Portfolio Manager” tab that got you to the holdings page. Then with the discontinuation of Money at some point when I clicked on the “Investing” tab it went to a dead page but then in the search box if I type in “Portfolio” it got me to the page that had the “Portfolio Manager” tab to click on that got me to the holdings page. Now Microsoft made a change because when I type in “Portfolio Manager” in the search box the page that it goes to does not have the “Portfolio Manager” tab on it. So if this post is making sense to someone then how do I get to the portfolio summary page that I used to be able to get to?
Harry Sit says
I have Portfolio Manager as one of toolbar buttons above the Investing tab label. If you don’t have it, click on Shortcuts… to add it to the toolbar.
Barry says
@Harry Sit – Thank you so much for the reply. That solved my problem.
Arthur Zebra says
This script worked fine for JP Morgan Chase checking and credit cards until early this year. Chase apparently made some changes, and although their is no indication of an error, it no longer downloads any data. Is there any fix for this?
Jeff says
If anyone is still using these scripts and ran into an issue in the last month with Discover and/or Citibank rejecting the download request you can fix it by removing the lines in ofx-b-tfb.py:
h.request(‘POST’, selector, query,
{ “Content-type”: “application/x-ofx”,
“Accept”: “*/*, application/x-ofx”
})
and replacing them with:
h.putrequest(‘POST’, selector, skip_host=True, skip_accept_encoding=True)
h.putheader(‘Content-Type’,’application/x-ofx’)
h.putheader(‘Host’,host)
h.putheader(‘Content-Length’,len(query))
h.putheader(‘Connection’,’Keep-Alive’)
h.endheaders(query)
Gomel says
didn’t work for me:
Traceback (most recent call last):
File “ofx-ba-tfb.py”, line 228, in
client.doQuery(query, argv[1]+”_acct.ofx”)
File “ofx-ba-tfb.py”, line 198, in doQuery
h.endheaders(query)
TypeError: endheaders() takes exactly 1 argument (2 given)
Gomel says
working now
need python 2.7
thanks
George says
Moneyspire is a good personal finance manager that supports OFX/Direct Connect http://www.moneyspire.com
Barry says
I am trying to record a stock sale by choosing specific lots. Somehow I can’t figure out how to do it. When I record the sale and choose OK I am not getting another dialog box where I can choose the specific lots that I want to sell. I have not sold a stock in a long time and I know before Money was discontinued I could do this so am I missing something or did this functionality go away with the demise of Money?
Harry Sit says
That functionality is still there. Look in settings to make sure you don’t have it set to always use the default first in first out.
Barry says
@Harry Sit thanks for reply but I must be blind because I don’t see an option in the settings that defaults to FIFO. Can you tell me where the setting is please and do I need to change the setting at the investment account level or at the holdings level. Thank you.
Barry says
Ignore last post I found the setting. Thank you for the assist.
Richard says
It appears that Yahoo has terminated the csv API. I found an alternative API call that returns one stock at a time in JSON format. https://stackoverflow.com/questions/47064776/has-yahoo-suddenly-today-terminated-its-finance-download-api
The problem is, I don’t have sufficient Python experience to be able to integrate this change into our favorite script. My attempt fails on
import requests
with: NameError: Global name ‘requests’ is not defined
SF Giants Fan says
First of all, I would like to thank you for all this valuable information. With Quicken now going to a 1 or 2 year subscription, I’ve decided to pull the plug and go with MS Money. With your help, I was able to get everything set up and am extremely happy so far. The only issue that I’m having is that I can’t find any OFX information for my Saturna HSA. In reading your other blogs, I believe you have an HSA through Saturna, too. Are you able to download your data from Saturna to MS Money?
Thanks again!
Harry Sit says
I didn’t try. I just manually enter my small number of transactions there.
Barry says
I am recently having trouble downloading from Fidelity and T. Rowe Price. Error on both accounts is “An error occurred sending POST request. There is also an Exception Type and Excepton VAL error.
For Fidelity there is also a message Enter Hello: tlsv1 alert protocol version
For T. Rowe Price a message Enter Hello: sslv3 handshake failure
Any help is appreciated.
Harry Sit says
Bobby made many improvements to these scripts in PocketSense. Please switch to PocketSense.
http://sites.google.com/site/pocketsense/
Barry says
@Harry Sit
Please excuse my ignorance but I thought that I was using pocketsense. Or are you saying that the scripts that I am using are old and out of date and that the one you are referring me to is a different script?
I have a pocketsense directory and scripts that I set up in 2012 based on the info in this site. Should I delete everything and start fresh using the pocketsense scripts?
Thank you for your time to answer.
Harry Sit says
PocketSense made many improvements to the original scripts here back then. It made more improvements over the years. If you are using an older version of PocketSense you should update to the latest (but keep a copy of the information about your specific institutions and stock and fund symbols).
Barry says
@Harry Sit
I’m having major problems don’t know if I should be posting here or at the pocketsense site that you referred me to. I downloaded the new pocketsense info and when I run setup to add accounts account connections that were working before are not working. None of my userid or passwords have changed. The error message just flies by so is there a way to capture an error message so that I can read it?
Harry Sit says
Sorry I don’t know to how to troubleshoot it. You should post at the PocketSense site.
Robert Norsworthy says
Hi, I’m looking for a 2021 update as to the best way to download transactions from Chase and other major banks in .NET. I’ve heard OFX is the way to go, but not seeing any .NET code to do it. I have a customer for my mom-and-pop software shop that wants to automate that as part of the accounts receivable app I do for them.
Please advise,
Robert
John says
ofx.chase.com I dead for last couple days. anybody has solution?
thanks
John says
I meant is dead