Download Price Quotes to Microsoft Money After Microsoft Pulls the Plug

September 4, 2009 by TFB

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 2.6.2 or Python 2.6.2. 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.

Software picked, likely related posts:

Keywords: replacing Money, software
Filed under: Reviews | Leave a comment

Comments

105 Comments on Download Price Quotes to Microsoft Money After Microsoft Pulls the Plug

Wai Yip Tung on September 4, 2009 | permalink

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 on September 14, 2009 | permalink

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!
TFB on September 14, 2009 | permalink

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 on September 14, 2009 | permalink

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
TFB on September 14, 2009 | permalink

DK – The “&” character in Proctor & Gamble is throwing it off. Fixed. If you download the revised script, it should work now.
DK on September 14, 2009 | permalink

TFB: Thank you so very much for the correction to the original QUOTES.PY script. It works like a charm now! DK
Steve on September 15, 2009 | permalink

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
TFB on September 15, 2009 | permalink

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 on September 18, 2009 | permalink

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 on September 18, 2009 | permalink

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
TFB on September 18, 2009 | permalink

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.
TFB on September 19, 2009 | permalink

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 on September 19, 2009 | permalink

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
TFB on September 21, 2009 | permalink

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 on September 23, 2009 | permalink

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.
TFB on September 23, 2009 | permalink

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 on October 8, 2009 | permalink

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 on October 18, 2009 | permalink

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 on October 31, 2009 | permalink

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?
TFB on October 31, 2009 | permalink

WHB – Make sure the “Start In” folder for your shortcut is the folder where you saved the quotes.py file.
WHB on October 31, 2009 | permalink

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.
TFB on October 31, 2009 | permalink

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 on October 31, 2009 | permalink

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 on November 4, 2009 | permalink

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.
TFB on November 4, 2009 | permalink

Mat – Sorry, I don’t know how the exchange rates update inside Money.
dan on November 9, 2009 | permalink

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.
TFB on November 9, 2009 | permalink

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 on November 10, 2009 | permalink

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 on November 16, 2009 | permalink

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 on December 2, 2009 | permalink

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 on December 5, 2009 | permalink

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 on December 6, 2009 | permalink

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 on December 6, 2009 | permalink

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.

??
TFB on December 6, 2009 | permalink

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 on December 31, 2009 | permalink

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 on January 2, 2010 | permalink

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 on January 3, 2010 | permalink

John…. This won’t work after MS stops providing quotes at the backend though … which is early next year………… right ?
CD on January 3, 2010 | permalink

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 on January 3, 2010 | permalink

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!
TFB on January 3, 2010 | permalink

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 on January 3, 2010 | permalink

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.
TFB on January 3, 2010 | permalink

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 on January 6, 2010 | permalink

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 on January 6, 2010 | permalink

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 on January 7, 2010 | permalink

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.
TFB on January 9, 2010 | permalink

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 on January 10, 2010 | permalink

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 on January 10, 2010 | permalink

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
TFB on January 10, 2010 | permalink

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 on January 12, 2010 | permalink

Thank you for sharing the script. When Microsoft pulls the plug this time next year, we’ll have a means to update quotes
Rich Smolucha on January 13, 2010 | permalink

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 on January 29, 2010 | permalink

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 on January 31, 2010 | permalink

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 on February 5, 2010 | permalink

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 on February 10, 2010 | permalink

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?
TFB on February 11, 2010 | permalink

Stubby – No, not in Perl. You can try this Python-to-Perl translation tool: Perthon.
HENRY ALKEN on February 12, 2010 | permalink

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 on February 14, 2010 | permalink

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 (eg: http://www.google.com/finance?q=MUTF_CA:RBF266) 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 on February 14, 2010 | permalink

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 on February 14, 2010 | permalink

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.

Back in 2004, Yahoo tracked that fund as you can see here http://mutualfunds.yahoo.ca/p/fam/414.html
Wilf on February 16, 2010 | permalink

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 on February 20, 2010 | permalink

Vanguard fund prices are not updating in MS Money 2004. The OFX file does contain the updated prices. Any ideas?
Dan on February 20, 2010 | permalink

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 on February 20, 2010 | permalink

Thanks Dan. Unfortunately, I did it by the book. Followed the instructions to a T. Still no go.
Dan on February 20, 2010 | permalink

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 on February 20, 2010 | permalink

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 on February 20, 2010 | permalink

Just downloaded several current transactions from Vanguard. Friday’s updated prices DID NOT update in “my” Money 2004.
ameridan on February 22, 2010 | permalink

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 on February 22, 2010 | permalink

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 on February 22, 2010 | permalink

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 on February 22, 2010 | permalink

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 on February 22, 2010 | permalink

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 on March 6, 2010 | permalink

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 on March 7, 2010 | permalink

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 on March 8, 2010 | permalink

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 on March 8, 2010 | permalink

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 on March 8, 2010 | permalink

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 on March 8, 2010 | permalink

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 on March 8, 2010 | permalink

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 on March 28, 2010 | permalink

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 on March 29, 2010 | permalink

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 on April 8, 2010 | permalink

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 on April 10, 2010 | permalink

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 on April 19, 2010 | permalink

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 on April 20, 2010 | permalink

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.
TFB on April 20, 2010 | permalink

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.
TFB on April 20, 2010 | permalink

nulle – eBay.com
HENRY ALKEN on April 23, 2010 | permalink

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.
TFB on April 23, 2010 | permalink

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 on April 24, 2010 | permalink

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?
TFB on April 24, 2010 | permalink

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 on April 24, 2010 | permalink

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 on July 18, 2010 | permalink

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 on July 18, 2010 | permalink

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 on July 21, 2010 | permalink

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?
TFB on July 21, 2010 | permalink

John – CTDB.OB isn’t a valid ticker in Yahoo. It says the symbol changed to CTDBQ.OB.
John on July 21, 2010 | permalink

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 on August 5, 2010 | permalink

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 on August 7, 2010 | permalink

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 on August 9, 2010 | permalink

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??
TFB on August 9, 2010 | permalink

Rick Sparks – People tried it back in January and it didn’t work. Open the downloaded OFX file in a text editor and see if there are prices in it. There weren’t back then. Maybe things changed now.
Rick Sparks on August 9, 2010 | permalink

TFB – Thanks!
Hmmmm…. you’re right… but that doesn’t seem right.
I’ll investigate further and if I find anything I’ll post it.
Rick Sparks on August 9, 2010 | permalink

TFB — You’re right … it doesn’t seem like a true OFX files.
…. it just has the transactions… not the current prices.

I appreciate everything you’re doing….. I’m just hoping for something simpler by the time we all need it.
Gunther Schwarz on August 14, 2010 | permalink

TBF,
just ran into this discussion and thanks, you seem to have the answers I am looking for.
Right now I want to get the “quotes script” set up.
I took your samle and added all my stock symbols to it.
Is the name of this script quotes. ofx. ?
Mine is not working, Also the file that it creates, where do I find it so I can check for mistakes?
I hope you have some patience with me, I am not at all familiar with scripts etc.
Waqar Aziz on August 14, 2010 | permalink

Google accepting suggestions

Already prices in the ofx file

http://productideas.appspot.com/#25/e=23f4c4&o=10&v=24