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.
Wai Yip Tung says
Very cool. I don’t use Microsoft Money myself. In case anyone uses Excel to analyze their stock portfolio I also have a piece of script that updates the quote in Excel. Anyone are welcome to use it or adapt it for their need.
http://tungwaiyip.info/blog/2008/06/22/python_stock_quote
DK says
TFB: I appreciate very much the work you put into making Money work for us poor souls left in the dink by Microsoft. I followed your instructions for downloading stock quotes and am getting the QUOTES.OFX . However my Money Plus refuses to process it: “the file you attempted to import appears to be invalid or contains corrupt data. Please download the file again. If this problem continues, contact your financial institution.” I have created a “DUMMY INVESTMENT” account which contains the stocks in the stocks and funds lists used in quotes.py. Any suggestions on how to get to the details of the Money error statement or how to proceed? Thanks a lot!
Harry Sit says
DK – Do you get the same error if you use the script as-is with the sample stocks and funds? Or do you only get this error with your own lists of stocks and funds? If it’s the latter, please send me your lists via the contact form. Thanks.
DK says
TFB: The error did not occur with your original script QUOTES.PY. The error occurs with my list of stocks and funds: ADP, IBM, PG and VTSAX. I tried to import QUOTES.OFX with Dummy Investment empty and also with Dummy Investment containing the four stocks/fund. In both cases Money refused to import the file. My list of stocks/fund exist already in other of my Money accounts.
Thank you for your response. DK
Harry Sit says
DK – The “&” character in Proctor & Gamble is throwing it off. Fixed. If you download the revised script, it should work now.
DK says
TFB: Thank you so very much for the correction to the original QUOTES.PY script. It works like a charm now! DK
Steve says
Hi TFB,
Nice article. Was headed down the same path myself (Money user for 5 years, “old school” record keeper) and looking into GnuCash and OFX synchronization alternatives when I came across your site. Very cool.
I had a couple of questions. I downloaded your script to play around with it, and used it as-is. The resulting OFX imports without problems. I then tried tweaking the price of AMZN manually to see if a re-import would update the prices. The import result indicated the correct price, but when I then went to view Portfolio Mgr the first price was still displayed. Thinking that the problem was probably that the quote price still had the same date/timestamp, I tried changing this in the OFX file and then re-importing. Same result.
I am mainly interested in keeping quotes alive — except for one bank account, I manually enter all other financial transactions — so I am interested in confirming that this works. I had an old copy of MSM 2006 around so I installed it on another computer and repeated the above experiment. Same result. I also tried running the script, then disconnecting my network cable before running the import.
Another odd piece of behavior with this second attempt was that although Money correctly indicated that I had services that had expired 387 days ago, it still updates the equity quotes! Any idea why that would occur? I thought online quotes expired with the rest. Somewhat perplexed, but looking forward to solving the problem.
Thanks again for a great site.
Steve
Harry Sit says
Steve – MS Money 2006 is still updating quotes. I also saw that with my test copy. I don’t expect it to last forever though. As long as Microsoft continues to provide quotes, we don’t have to run this script. When they stop, we will have something ready.
I suspect the online update from Money is overwriting the imported prices. If you have a software firewall like ZoneAlarm, block Money from accessing the Internet and then test the imports. If not, I propose this test:
1) Don’t open Money. Put a “#” in front of the last line in the script “os.startfile(fileName)”. Run the script. Rename the quote.ofx file to quote1.ofx.
2) Wait a day and run the script again. Rename the quote.ofx file to quote2.ofx.
3) Disconnect computer from the Internet. Run Money. Import quote1.ofx, then import quote2.ofx.
4) Go to the Investment in question. Click on Update prices -> Update manually. Look at the price history. If there are two prices, it proves the imports are updating the prices correctly.
Repeat this test. Instead of waiting a day between two downloads, wait an hour. I expect the price history to show the price in quote2.ofx.
Steve says
Hi TFB,
Don’t have ZoneAlarm but I tried Windows Firewall. Didn’t seem to block so I proceeded with approach #2:
I created a dummy account with the stocks and funds from the default script. With Money closed I generated 4 OFXs files, one about every 10 minutes. I then disconnected the network cable and opened Money.
I initialize the account using default script. Amazon has a price of 90.07. The first time I open the OFX file it asks which account I wish to import into. Subsequently it does not and just displays the “Import Complete” dialog. I then see “you have xxx statements to read” where one of them is the dummy account I created. However, in the “Last downloaded” column it does not indicate the current date. If I select the dummy account I go to the the “Review investment holdings” screen, where I see the updated prices for the sample stocks and funds (e.g., AMZN is 90.49); the date at the top (in the label “These positions were reported by your broker on your statement, dated …”) is still not the current date (I am running these tests on 9/18 and both dates I mentioned above are 9/17).
If I then click Finish to end reconciliation, then Done to exit the “you have xxx statements to read” screen, it takes me out to the main screen. If I go to Portfolio Mgr I still see the same price as before.
Anytime that I perform the import with a different OFX I see the updated price in the “Review investment holdings” screen, but the updated price never makes it to Port Mgr.
I also performed your check:
4) Go to the Investment in question. Click on Update prices -> Update manually. Look at the price history. If there are two prices, it proves the imports are updating the prices correctly.
Here it is not displaying the updated price either.
Any thoughts?
Thanks,
Steve
Steve says
Also I should say that I performed these tests with MSM Plus Deluxe — I should probably repeat with MSM 2006, as that is what you are using?
Cheers,
Steve
Harry Sit says
Steve – Thank you for helping me test and refine the script. Your tests showed that only the first price update on a day went in. I’m less concerned about that. What about price updates on different days? If I can get one price update per day, that’s good enough for me. Another issue is the as-of date seems to be off by one day. Let me look into that.
Harry Sit says
Steve – I fixed the as-of date. Please get the new script. 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.
Steve says
Hi TFB,
Thanks for the update. It’s great to have someone interested in keeping this app functional who also has a great resource for discussing it and other financial matters. I will try out the update and let you know.
I am also looking into what alternatives there are to MS’s Stock Screener tool on the Money website. I am talking about the ActiveX version available when using IE; the functionality is significantly reduced when using other browsers. The SS is also going away — even sooner I’m afraid (Nov 09). Any thoughts on alternatives?
Cheers,
Steve
Harry Sit says
Steve – I don’t know the answer to your question about the stock screener. I heard you get some of the Morningstar premium features for free if you register at T. Rowe Price’s web site (you don’t have to open an account, just register with user name and password).
John says
The script seems to generate something strange when the mutual fund is a money market mutual fund.
I added the Hewitt Money Market Fund (HEWXX) to the list of mutual funds, and the OFX file generate caused Money to bark a problem with the data received.
The problem looks to be this:
0.05%
0.05%
Instead of returning the proper share price of $1.00 for money market mutual funds, Yahoo! returns the 7-day yield. There doesn’t appear to be any way around this problem. The Yahoo! CSV download URL format has two options to return the price: l and l1. Both of these return the 7-day yield and not the share price.
I suppose one option would be to recognize that any mutual fund ending in “XX” is a money market mutual fund (e.g. HEWXX, TPCXX, FDRXX, SPAXX, FSLXX, etc.) and always populate the UNITPRICE and MKTVAL fields with “1.00”.
Microsoft Money must have some way to make this determination or maybe the Money quote server just returns “1.00” for the price of money market mutual funds.
For now, it doesn’t seem like a problem to just omit HEWXX from my list of money market mutual funds in the script.
Thanks for the excellent work. I’m using Microsoft Money 2001 and really have no desire to move to an alternative.
Harry Sit says
John – Thank you for your note. I’d say just leave the money market funds out. You know the price is going to be 1.00. Keep updating the price with 1.00 doesn’t really do much.
Jeff says
Great information! I was searching for a Money replacement, specifically just for the online update ability (and also the debt reduction planner too, I guess) and like you say, why use another product if you can get the online update to work! Thanks!!
Tom says
Thanks for the quotes script, you save me having to re-enter 5+ years of transaction data. A real life saver, note to Bill Gates … thanks for nothing !
WHB says
Thanks for setting up this script. I downloaded PYthon and your script and then set up a shortcut with “C:\Python26\python.exe quotes.py” and double clicked on the shortcut. A command screen came up briefly, but no prompts. I could not find that it had written any .ofx file. Is there something else I need to do?
Harry Sit says
WHB – Make sure the “Start In” folder for your shortcut is the folder where you saved the quotes.py file.
WHB says
Yes my “Start in” for the shortcut is C:\Python26 with a Target of C:\Python26\python.exe quotes.py and the script is in c:\python26.
Harry Sit says
WHB – Open a command prompt. Navigate to that directory. Execute the command manually and see what happens. Did you remove the .txt extension from the downloaded script?
WHB says
Yep, forgot to remove the .txt extension. So I had quotes.py.txt. It works now. BTW, Yahoo picks up Berkshire B as BRKB, Money used to be BRK.B. That was another issue. Works great. Thanks much.
Mat says
On top of your excellent work TFB, do you know how exchange rates could be used to update Microsoft Money’s currency table? I added “CADUSD=X” to my Stocks list, which successfully updated a dummy “CAD to USD” investment. Unfortunately, it fails to update the value of my accounts that use CAD currency (bank, credit cards).. Any ideas on how to automate this?
Otherwise, manually updating currencies can be done by selecting Tools > Settings and then choosing Program Settings > Update Currencies.. but it’d be great to have it done automatically, once again.
Harry Sit says
Mat – Sorry, I don’t know how the exchange rates update inside Money.
dan says
I’ve been a Quicken user for over a decade. Have more than 30 years of investment data in a Quicken 2005 DB. Don’t use my computer for much more than investment tracking, email and internet…Hence older machine and OS.. I’m once again faced with a Quicken (forced) update to get online stock quotes. This one would push me into a new machine and OS…do you have any idea of how to get the quotes updated in Quicken like your solution for Money. I’m tired of being extorted by Quicken.
Harry Sit says
dan – Sorry, you can’t do the same with Quicken. Intuit closed off this opportunity. As I showed with the screenshot in the previous post, if you try to import an OFX file, Quicken phones home and refuses to cooperate.
Julee says
Thanks for all of this information. I was wondering if there is a way to modify the quotes.py script so that I can get the prices from a previous day. I like to have updates for the last day of the monthand am sometimes out of town. Older versious of money could not get historical prices but the one I have been using has the capability to do so.
Bob says
This could be a useful workaround.
It may not work for foreign stocks while downloads are still enabled, e.g. Money (and MSN Money) uses AU:BHP and Yahoo Finance uses BHP.AX as ticker symbols for the same stock.
Do you know if Money 2005 Deluxe can update prices from a QIF file? Quicken can do this if the file is similar to
!Type:Prices
AMZN,132.97,”11/13/2009″
YHOO,15.93,”11/13/2009″
[etc]
^
Also the very useful MSN Investment Toolbox on moneycentral.msn.com was discontinued recently. Do you know of a replacement?
tom says
I’ve updated my script with some user entertainment …. added the print statements as indicated in the following locations.
if __name__==”__main__”:
print ‘Start Program’
.
.
.
for ticker in stocks:
print ‘Retriving quote for {0}’.format(ticker)
.
.
.
for ticker in funds:
print ‘Retriving quote for {0}’.format(ticker)
.
.
.
.
os.startfile(fileName)
print ‘End Program’
Daniel says
Hi TFB,
I’ve tried the script and importing the dummy broker statement works. I was able to view the imported statement but the quotes could not be updated into the other accounts. Once I get into the portfolio view, the prices are not updated. I’m using Money 2002. I’ve also tried clicking” Finish” to end reconciliation after viewing the statement but the price was not updated. Is there any changes that I can do in the script to update the price in Money 2002?
John says
Super job!
Saved me from paying $20 for the 2007 version just to get this lost stock-quote functionalty!
Some suggestions for future versions:
a) An msi installer, inf, bat, etc.
b) “You need to be connected to the Internet for this installation.”
c) “Unable to find Python on your system. Directing you to their download webpage.”
d) “Unable to find MS Money on your system. This product is only used wth MS Money.”
e) Why have all users change the filename from “quotes.py.txt” to “quotes.py”? Just put “quotes.py” on your server vice something that we all have to rename.
f) I didn’t need to create a shortcut for anything. Just download the quotes.py.txt file, start a Dos prompt and rename it (Only way I could find to do it), download and install Python, and then double-click the quotes.py file (which associates with Python).
g) Need some kind of error-handler. If the script stops because it can’t find a particular stock, the script should stop, wait for user acknowledgement, then shutdown, not just shutdown by itself.
h) It couldn’t find the stock CTDB and the mutual fund FRIFX, so it shut down when it got to those. If anyone knows how to update those, please let me know.
i) Incorporating Tom’s user messages is a must, otherwise alot is happening behind the scenes wth no statuses. Thanks, Tom!
Other than that, once the quotes.py file was fnally set up, it created the quotes.ofx file and then brought it into MS Money. That step was just plain cool.
Thanks TFB for taking the time to share this with us!
John says
Hello.
Any ideas as to why even after you run ths script, the “Last updated” column in PortFolio Manager doesn’t change? Mine still says “8/29/2008” and “8/31/2008” for my stocks/MFs.
??
Harry Sit says
John – Please feel free to add any enhancement as you’d like. It uses Yahoo! for quotes. Yahoo! has CTDB as CTDB.OB. It’ll probably work if you use the symbol CTDB.OB. Money does not consider this as updating quotes. It’s importing a statement from a broker, which has the desirable side effect of updating the quotes for the securities in the brokerage statement.
G says
I got it all working except BRK.B. I see the note that it is called something different by yahoo and tried both BRKB and BRK-B, but to no avail. Do I have to change it on the Money side? thx
John says
All,
I found this easier way to get the stocks updated without the need for scripts:
1) In Windows, change your clock’s date/time back to (for me) Dec 2007 (sometime before your updating functionality ended).
2) Start up MS Money. Go to Tools>Internet Updates>Update now.
3) All your stuff will update to the current (Jan 2010) date.
4) Then change your system clock back to the current date/time.
5) Your “Last Updated” in Portfolio Manager also gets the correct info this way.
HTH!
Sunny says
John…. This won’t work after MS stops providing quotes at the backend though … which is early next year………… right ?
CD says
Rolling back the clock works great (for now) but it did mess w/my monthly reports even after resetting to today. Needed to close Money & reboot, that seemed to do the trick.
Thanks all the work & suggestions.
CD
NYC
David says
TFB,
When I ran this the first time, I associated the quotes file with a real account by mistake, and now I need to associate it with the Dummy Account. Do you know how I can change the association?
Thanks for the script!
Harry Sit says
David – Go to that real account and remove/disable online banking from the account. Do the import again and associate it with the dummy account this time.
Jeff says
How does Money know what quotes have been downloaded? Does the ticker symbol need to match only, or both the name and the symbol, or??
I get a “received investment positions for DUMMY”, but the quotes don’t appear when I review the price history. I’m using MS Money 2000.
Thanks.
Harry Sit says
Jeff – The ticker symbols need to match. The names don’t matter. You put in the script a list of ticker symbols you have in your real accounts. When the prices for the securities in the dummy account are updated, so are those for your real accounts.
Jason J says
Thank you very much for this script. This will save me so much time and allow me to continue using MS Monday Plus.
If you know of a way to add a proxy, please let me know.
Thanks
-Jason
Jeff says
Thanks TFB. It turns out Money 2000 doesn’t like the UNITS to be 0 in the OFX file. Otherwise it just ignores the updates. I labelled my DUMMY account as a WATCH account so it doesn’t affect my investments and put the UNITS to 1 and now it works like a charm.
Thanks again,
Jeff
Lee Simmons says
Got here by googling the problem–no more live quotes in my Money Plus portfolios. Looks like you have come up with a fix, but it also looks like you have to be something of a programmer to use it. Any chance you could write a program for us poor souls who would like to get downloading capability back? It would be worth more to me than the price of a new Quicken (I hate it!) program.
There really have to be a few thousand folks like me–enough of a market perhaps to make it worth your while.
Harry Sit says
Lee – The transaction download script requires more work but this price quote download script is very easy. You just follow these simple steps:
1) Install Python (either link in the post will work)
2) Download quotes.py.txt. Rename to quotes.py.
3) Edit quotes.py with your list of ticker symbols.
4) Create a shortcut to quotes.py on your desktop. Double click on it. Done.
David says
TFB,
Regarding your response #40 above, how can I disable/remove online banking from the account? I don’t see any checkbox for that? I poked around quite a bit, but was unable to find it. Thanks again for your help.
David
CD says
TFB,
I’ve been following this thread for about a month & feel I need to voice my appreciation for all the work & attention you put in here.
As someone who has used MS ( ;^P ) Money for about 10 years under the pretext that I owned it once I paid for it, I was flabbergasted when the DB’s at MS decided to leave us all high & dry. Could they not have at least modified the code so we could keep going to our own banks.brokers, etc. to get price & transaction updates? One would think they were going out of business as a brand.
Again, thank you for all your efforts.
CD
NYC
Harry Sit says
David – It probably depends on your specific version of Money. In Money Plus, it’s under Banking -> Account List -> (left pane) Manage online services -> click on the bank/brokerage name -> Stop using online services.
Dan says
Thank you for sharing the script. When Microsoft pulls the plug this time next year, we’ll have a means to update quotes 🙂