There is a small piece of unfinished business in my series for replacing Microsoft Money. After giving my requirements and looking at Quicken, GnuCash, and Moneydance, I came upon two OFX scripts for downloading transactions directly from the financial institutions, outside of Microsoft Money.
Those scripts will take care of the transactions. They will also update the prices for the securities held in the investment accounts that provide transaction download. However, if you have holdings in accounts that do not provide transaction download, the prices for those holdings are still not updated.
With the help of a book from the library, Learning Python, and a lot of Googling, I came up with a new script that gets the quotes from Yahoo! and writes a dummy OFX file for importing into Microsoft Money. Being a Python newbie, I’m sure the script can be made much more elegant, but what I have now works.
I tested it with Microsoft Money 2006. The online service in Money 2006 already expired. I created a new Investment account in Money called Dummy Investment. I ran the script, which imported a dummy statement with the current prices into the Dummy Investment account. Because the number of shares is set to zero (thanks to suggestion from John Brinnand), the import will not add any shares to the Dummy Investment account. It only updates the prices for your other accounts.
The script is very easy to use. You put the ticker symbols in two lists, one for stocks and one for mutual funds. Depending on how you set them up in Money, ETFs can be either stocks or mutual funds.
stocks = [“AMZN”,
“GOOG”,
“YHOO”]funds = [“VTSMX”,
“VBMFX”]
You still need either ActivePython or Python. Then you just create a shortcut with
python quotes.py
Double click on the shortcut. Follow the prompts with a few more clicks and you are done.
Because it simulates importing a statement from a broker, Microsoft Money will only update the prices once per day. It’s best to run the script after the market closes. If you run the script more than once when the market is open, only the first price update will get into Money; you will not get the closing price.
With the Python scripts that download transactions and price quotes, I intend to use Money past its official expiration date.
Update: Since this was first published, other software developers created PocketSense and hleOfxQuotes, which greatly improved upon the rudimentary script here. Please consider using those. I use PocketSense now.
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.
Rich Smolucha says
First off thanks for the pioneering scripts… FYI I’ve ported some of these (the price to OFX file ones) to VBA as a set of user-defined functions in an Excel Workbook. I had my own version of Yahoo-price-getter functions (retrieves the last quote including real-time (presuming the market is open)).
In combination these scripts (a) get the latest prices, (b) create the OFX file for stock price updates, and (c) imports the OFX file into Money Plus 2007. All of this is driven by a worksheet in the workbook that lists the securities of interest. The workbook runs all of this on activation. So to use it one (a) manages the list of ticker symbols in a worksheet (which is rpretty trivial) and (b) opens the workbook whenever the prices are to be updated (this can be added to scheduled tasks to make it totally automatic).
There are some anomalies… for one the prices though specificed with a DTASOF of toay are recorded in Money Plus 2007 with tomorrows date (that is if the DATEASOF is 20100115 (jan 15 2010) the price history records these as being for Jan 16 2010). Any clues as to why this would be would be apprciated. I am actively working to remedy this and hope to have success at some point. Also, as you noted the updates only happen “periodically” (youy say once a day but I can get the updates in more than once a day as long as I first delete all of the prices for the current date).
I’d be glad to share the code if you’re interested in vetting it and then hosting it to the community. I also have some work in progress to download statements from firms (credit card, banking, investment firms) and have these working for a small set of companies (Discover, American Express, and Fidelity Investments). I’d also be glad to share these as they become more stable. THese also are driven by tables in a worksheet so a non-programmer can use these out-of-the-box.
Thanks again and feedback appreciated.
ameridan says
I utilize a Watch Account in Money (that I’ve renamed Market Indexes) and I wondered – “can I use this script to update these as well, when Money goes offline?”
Well it turns out you can by simply including Yahoo’s symbols for the indexes in the STOCKS section of TFB’s script.
^DJI for Dow Jones Industrial Avg.
^GSPC for S&P 500 and
^IXIC for the NASDAQ Composite
ameridan says
I remember reading that someone was looking for a way to download exchange rates, and I think I’ve figured that out too. See http://microsoftmoneyoffline.wordpress.com/2010/01/28/16/
I’m documenting all of my findings in my Micosoft Money Offline blog and making sure TFB gets credit for his great scripts!
Wilf says
This looks like a fantastic solution; I’m looking forward to trying it!
Thought I’d first check that Yahoo listed my mutual funds but I can’t find them. All are Royal Bank, for example RBF266(Cdn dividend), RBF462(Global energy), etc. I’ve tried the symbol, Symbol.TO, and searching on the name but no results.
What am I doing wrong???
Stubby says
TFB – I run Perl on my PC; is there an equivalent script that will run on it instead of the one mentioned in this blog that runs on Python?
Harry Sit says
Stubby – No, not in Perl. You can try this Python-to-Perl translation tool: Perthon.
HENRY ALKEN says
TFB you are a genius!
Your Python script updates the stocks in my 2005 MSN Money Merrill Lynch account.
I notice that the Funds do not get updated. I added the 2 funds
[“VTSMX”, VBMFX”] that you have in your example to my Merrill Lynch account with just 1 share of each but I noticed they were not updated. Is there a reason for this?
This script has taken a real load off my mind in view of Microsoft’s irresponsible behavior in terminating support. Little Willy should stop screwing around in Africa and come back and run the business.
Great work!
Wilf says
Follow-up to post 54: I’ve been searching for the past week and still can’t figure out how to get Yahoo to return Canadian mutual funds.
Google reports Cdn funds but their OFX download seems to be for statement data vs. financial quotes.
I don’t know enough about scripting to figure out how to rewrite this for Google.
Can someone help me figure out how to have Yahoo show CDN funds or how to get price updates into Money from Google?
Thanks!
ameridan says
Wilf,
I just added the answer (with instructions and links) to your question on my blog http://microsoftmoneyoffline.wordpress.com/2010/02/14/adapting-quotes-py-for-worldwide-stocks-funds/ using Yahoo!
You won’t have to change any of the scripts 🙂 ENJOT!
ameridan says
Wilf,
I see what you mean now; some mutual funds like rbf266 don’t seem to be tracked at Yahoo! and Google doesn’t seem to provide a CSV / OFX means of obtaining quotes.
Wilf says
Thanks AmeriDan!
It looks like Yahoo stopped tracking most of the Royal Bank funds – strange: the Royal is Canada’s largest bank.
Google’s Excel download has the quotes but I have no clue how to move that to an OFX.
I’m hoping someone can help find a way to make this work with Google or another service.
Thanks,
Wilf.
Jim says
Vanguard fund prices are not updating in MS Money 2004. The OFX file does contain the updated prices. Any ideas?
Dan says
The first time you import the OFX file, you have to match the investments (with Yahoo! symbols) up with the equivalent investment already in Money (with MSN symbols). If you blow by that step, then they are considered to be independent investments with a quantity of zero. That is why the instructions state that you need to create a Dummy Account (that you’ll never look at).
Having said all that, I could be way off base 🙂 but my intentions are to hopefully trigger your recollection of how you handled the first OFX download.
Jim says
Thanks Dan. Unfortunately, I did it by the book. Followed the instructions to a T. Still no go.
Dan says
In that case, try deleting the Dummy Account and reimport the OFX (it will prompt you to create another Dummy account). Also a few more details… do you still do the online updates? do you use the other OFX script to get data from Vanguard?
jim says
I have used Bank of America, Vanguard, and FIA Card Services manual download OFX files. I have used Money 2004 online update. I have tried adding “1” unit to your script.
I have tried a new file using your script first and allowing Money to create a new account. Then downloading a single Vanguard account with six investments to track. This new file is using Yahoo symbols and descriptions.
If it is the script, it is not forcing Money to use the updated price. If it is Money, then it will only accept MSN updates or manual updates.
Jim says
Just downloaded several current transactions from Vanguard. Friday’s updated prices DID NOT update in “my” Money 2004.
ameridan says
Jim,
By chance, the mutual funds that aren’t updating – are they closed end funds?
I’ve stumbled on some information that only open ended funds should be requested in the [funds] section of quotes.py as closed end funds are really like stocks.
By the way, check out the Java quotes app in my Blog (click on my name) and see if that works any differently. I’m trying to learn all I can on these OFX importing methods…
Dan
Jim says
Dan,
The funds are all open. This is turning into a pretty good puzzle. I’m testing your script on Money’s latest version, Plus. The first pass updated perfectly. I will test again today and through out the week. Money 2004, same script, no go.
My installation of Money 2004 is from original disk with no known updates from MS servers. Running on Windows XP sp3. Only clues are brokerage account OFX files do not update current prices even tho they are included in the OFX files. Online update does work.
ameridan says
Well, TFB has proven functionality with Money 2006 and I have with Money Plus (it’s probably safe to assume Money 2007 works fine as well), so perhaps Money 2004 just isn’t compatible with the scripts. Everyone needs to realize that the OFX Python Quote scripts are taking real quote data and wrapping that data with created OFX parameters so that Money recognises it as an OFX file to import. It could just be one of those parameters throwing the older Money off?…
“OFXHEADER:100”,
“DATA:OFXSGML”,
“VERSION:102”,
“SECURITY:NONE”,
“ENCODING:USASCII”,
“CHARSET:1252”,
Jim says
I’m not a programmer so who knows what is in Money 2004 preventing the updates. It does work on Money Plus, so this is really just a puzzle for me. Money 2004 was my favorite version and it is independent of MS activation. What will happen to Money Plus? MS in control not me. With 2004 I own the software.
Bobby, over at PocketSense, has written some scripts I going to try. I think he likes the same puzzle I do. Plus his IQ is 10x higher than mine.
Dan, you’ve written excellent pioneering scripts. Hopefully, as more Money users are deactivated scripts will be written building on yours. Quicken desktop will be EOL within a couple of years. This is going to be a fascinating area for scripts.
ameridan says
Just to set the record straight, I haven’t written any scripts and I am not TFB 🙂
I just appreciate what he has started and am trying to help him (her) out. I started my Blog ( http://microsoftmoneyoffline.wordpress.com/ ) after trying out TFB’s Quote script with the intent of posting anything that might help other Money users after their program goes OFFLINE.
And I agree with your remark about Bobby – he has really mastered Python!
James says
Nice scripts, I will have to rewrite into either VBScript, PowerShell, or JScript in order to get the functionality working on my system. Thanks for the insight.
HENRY ALKEN says
TFB,
I do not see the connection between the Python scripts and MSN Money.
How does Python get Money to read the .OFX file and update the portfolio prices?
Dan says
Henry,
The scripts grab the quotes from Yahoo! Finance (not MSN Money) site and “create” an OFX file that you link to a dummy account in Money. Once any OFX file is submitted to Money, it is automatically imported, and any other accounts that have those same investments are therefore updated as well.
HENRY ALKEN says
Dan,
Thank you for your response.
How is an OFX file ‘submitted’ to Money?
Where is the OFX file placed – in what directory?
How does Money know which OFX file to read?
When I ran the Python script, my MSN Money 2005 updated all the stocks in the portfolio. I did not have a Dummy account.
Dan says
I was under the impression that since the quantities are set to zero (0), and that if you linked to a real account, the price would be correct but you would mess up your quantity. TFB has a very clear set of instructions – kinda sounds like you didn’t follow them ???
The files end up in a temp folder – as soon as your PC sees the OFX file, the Money import handler program takes care of it.
Dan says
Henry,
Sorry if I came across kind of strong. Now that I reviewed the posting again, I guess there really weren’t a clear set of instructions – just a sentence as to what TFB did. If linking the quotes up to a real account doesn’t mess up your investment quantities, that would be good to know.
Dan says
Henry,
In answer to your other question, the following code is what submits the OFX file to your PC.
os.startfile(fileName)
When you install Money, the OFX file extension is registered with the Money Import Handler program which keeps track of the OFX files submitted, imports them into Money, and then deletes them from the temp folder afterwards.
Michael says
I am a 12 year heavy user of MS Money – currently use Money 2007. I agree with all the sentiments re Money being very powerful and seems superior to Quicken and the few emerging newbies.
However I have an additional problem that I am unclear about a solution.
I have a very large MNY file with multiple Investment ,credit card and Bank accounts – all are denominated in AUSTRALIAN Dollars. Everything works fine and all stock prices are automatically updated. It is unclear how the Python scripts handle a mixture of Australian,Canadian and US stock prices. In MS Money the stocks ticker symbols are, for example BHP (for Australian stock) CD:PLS (canada TSE) and US:IBM (for NYSE stocks) . These same stocks in Yahoo are designated as BHP.AX PLS.TO and IBM.
Can the Python script handle the accessing the prices from Yahoo with one set of Stock symbols and then assigning the prices to the correct stocks in Money?
Alternatively do you have to somehow force Money at accept new stock symbols that are identical to the Yahoo ones?
This problem does not arise if all the stocks are US Based – as neither Money or Yahoo have exchange identifiers attached to the stocks. The problem does arise if the US based account has foreign stocks ,including Canada.
Appreciate some help
Dan says
The symbols are all relative to MSN, so when gathering the quotes from Yahoo!, they’ll need to be changed. When matching the investments up after the first download using quotes.py, I think Money will store the new symbol internally, even if you don’t see it changed in the Portfolio screen.
You can download quotes for foreign stocks using the OFX scripts, but I think the quotes will always be in US dollars.
Even though the exchange rate can also be downloaded with the same script, it won’t work in the same manner as the MSN update.
Hal says
First, I want to thank you much for your quote download script for Money.
It works great and I hope will continue to do well.
However, I would like to suggest an addition to the script that stops it to report a symbol that no longer exists (de-listing, etc). This recently happened to me when ALD merged. The script will close before I could see any error message to find out why. I then realized the delisting and removed symbol.. thus back to working AOK.
Tracy says
I missed the step about creating a dummy account and selected a real account. Now I need to undo that and have the import use a dummy account. I turned off online banking for all accounts but I can’t get Money to ask me which account to use for the import. I’m using Money Plus. Can someone please help?
nulle says
anyone knows I can get my hands on a copy of MSM 2006 Deluxe or higher?
I am running MSM 2004 STD OEM version on Win2K machine. I have a copy of MSM 2006 Std standing by…however the MSM 2006 Std version sucks in terms of functionality. the MSM 2006 Deluxe is the replacement for it.
I am also trying to find a way to import quotes via the QIF (still researching) just recently lost the ability to update stock prices from online.
Henry Alken says
TFB
Does your Python program also update the dividends received into one’s brokerage account?
I currently have a Merrill Lynch account and each dividend received from a stock is automatically entered into the account by Money as well as the stock price.
Harry Sit says
Henry – There are two scripts. The script in this post only downloads prices. The other script downloads account activities, including dividends. You will have to find out the settings for Merrill Lynch. Please read the other post.
Harry Sit says
nulle – eBay.com
HENRY ALKEN says
TFB
I am having no luck getting a response from Merrill Lynch using ofx.py.
I am getting HTTP Error 400.
Any help finding the “sites” data for Merrill Lynch would be appreciated.
Thank you.
Harry Sit says
Henry – What settings did you use? According to a post by Dan, it’s
“caps”: [ “SIGNON”, “INVSTMT” ],
“fiorg”: “www.mldirect.ml.com”,
“fid”: “5550″,
“url”: “https://taxcert.mlol.ml.com/eftxweb/access.ofx”,
HENRY ALKEN says
TFB
Thank you. I am now getting a Money window that pops up with a Heading of Import Summary and a content of Import Complete after I run ofx-ba-tfb-auto.py.txt using import-ofx.bat.txt. Merrill Lynch is accepting the request for updates without complaining but is not sending the new prices.
In your “quotes.py” you created a class OfxWriter to list the stock names.
Is that list of stocks to be updated now created by Money and is no longer necessary?
Harry Sit says
Henry – If you are able to run the account update script and Merrill Lynch is your only investment account, you probably don’t need the quote script. The prices only update once a day. Run it again in a few days and see if the prices change. If for some reason Merrill Lynch does not include prices in its download, you will need the quote script but you will have to manually edit the list of stocks and funds in that script. It doesn’t know about your accounts or holdings.
HENRY ALKEN says
TFB
I have attempted to obtain the log files which describe the traffic between Money and Merrill Lynch but have not been successful:
http://support.microsoft.com/kb/911941/en-us
I have checked the Registry and it has been correctly changed to permit log file generation.
The two log files should be:
merrillrs.txt for the request to Merrill
merrillrq.txt for the response from Merrill
Maybe you will have better luck.
whm says
MS Money Plus Deluxe (ver 17) alerts you to dividend payouts for your holdings without downloading the information from your broker. Would you consider adding this feature to your quote script? If not, is there a web site that provides and tracks dividend payout dates & amounts for investments. Thank you.
HENRY ALKEN says
I thought I would check in to see how everyone is doing with the MSN Money replacement business.
I got a free copy of MSN Money Plus and started using TFB’s price updating scripts. But then I found that my broker, Merrill Lynch, had an OFX download for MSN Money users. I started using that & it worked extremely well, downloading prices, dividends, stock splits, etc.
But this week Merrill Lynch notified me that Microsoft is no longer supplying them with data & so they have disabled the MSN Money OFX file download from their website. From now on they will supply only QIF files for Quicken.
So I thought I would switch to Quicken. However, when I read the user reviews on 2010 Quicken they were extremely hostile to the software and to the Intuit company. In fact I have never read such bad reviews.
So now I am sticking with MSN Money Plus and back to using TFB’s Life Saver script for updating prices from Yahoo.
One reader, #47, asked how to disable the import – file connection. This is not so easy. Here is one Microsoft recommended procedure:
http://support.microsoft.com/kb/910214
As you can see even the Microsofties have trouble doing it.
For those who wish to study OFX, there is a PDF spec that can be downloaded:
http://www.ofx.net/
So,in summary, I am using TFB’s great script and a dummy account for updating prices. I manually enter the broker’s cash total which includes all dividends. Individual dividends are not entered. I enter, by hand, stock splits and stock purchases & sales.
How is everyone else doing?
John says
Hello! I am getting this error:
Retrieving quote for XOM
Retrieving quote for CTDB.OB
Traceback (most recent call last):
File “C:\Users\J\Desktop\Money Files\quotes.py”, line 245, in
sec.getQuote()
File “C:\Users\J\Desktop\Money Files\quotes.py”, line 86, in getQuote
timeStruct = time.strptime(quote[2] + ” ” + quote[3], “%m/%d/%Y %I:%M%p”)
File “C:\Python26\lib\_strptime.py”, line 454, in _strptime_time
return _strptime(data_string, format)[0]
File “C:\Python26\lib\_strptime.py”, line 325, in _strptime
(data_string, format))
ValueError: time data ‘N/A N/A’ does not match format ‘%m/%d/%Y %I:%M%p’
I tried it with Python 2.6.2 and 2.7. Any ideas?
Harry Sit says
John – CTDB.OB isn’t a valid ticker in Yahoo. It says the symbol changed to CTDBQ.OB.
John says
TFB,
Thanks! If I knew Python well enough, I would throw in an Onerror messagebox “The following stocks/fund name is not recognized and cannot be retrieved. Ending the script. Press OK to acknowledge.” It seems alot of the questions you deal with above are from stock name issues and not actual script issues, so I am not the only one who had a problem there, but you shouldn’t have to research my stock names for me. Thanks for doing my homework for me and for the help! 😉
Waqar says
Is it possible to amend the script to pick the prices from a different website. I am in the UK and have a portfolio up at Moneyextra which offers a facility to download prices etc as a csv file. Can this be modded to create an ofx file ?
Alternatively there is the MS Money excel addin which pulls prices into excel from MSN Moneycentral, could that be modded to create a ofx file ?
Gerry says
Newbie here…REALLY appreciate all the experts on here helping. I’m stuck, though. I am using MS Money Plus and am not having success with the script. I think I followed all the steps…when on double click on my “python quotes.py” script, a dialogue box BRIEFLY appears (too quick for me to read anything in it) and then closes. Nothing more. No “further prompts” or anything and nothing that appears to have been created within Money or external to it. What am I doing wrong? I downloaded Python, the TFB script, created “Dummy Investment” acct and populated it w/ the stocks/funds used in the script. What am I missing??
Thanks in advance!
Rick Sparks says
I haven’t tried it yet because my Money Deluxe 2006 still updates share prices each day…. but I’m preparing for after Jan 2011 (I think that’s the date (?))
Why wouldn’t this work as an alternative:
1) Set up accounts in Google Finance Portfolios
2) Download as OFX (upper right of Portfolio)
3) Import into Money
Wouldn’t that be equally effective??