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.
Leo says
All of a sudden, my MS Money update of mutual funds has stopped working.
Here’s the error message:
C:\Users\Leo>d:\python26\quotes.py
Traceback (most recent call last):
File “D:\python26\quotes.py”, line 253, in
sec.getQuote()
File “D:\python26\quotes.py”, line 90, in getQuote
timeStruct = time.strptime(quote[2] + ” ” + quote[3], “%m/%d/%Y %I:%M%p”)
File “D:\Python26\lib\_strptime.py”, line 454, in _strptime_time
return _strptime(data_string, format)[0]
File “D:\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’
Any ideas as to what’s wrong?
RonS says
Responce to Leo (201) I am having the same problem. Every thing worked fine on Friday, but my Monday update did not occure.
After reading Leo’s comment I ran quotes.py this morning and I get the same error
message.
Win XP SP3, with Python 27
Ron
Harry Sit says
@Leo, @RonS – It depends on what Yahoo returns for your ticker. Try this in your browser:
http://finance.yahoo.com/d/quotes.csv?f=nl1d1t1&s=VTSMX
Replace the ticker VTSMX in the end with your own fund’s ticker. For VTSMX, Yahoo returned
“VANGUARD INDEX TR”,32.91,”7/18/2011″,”6:25pm”
That’s correct, with a date and time. It looks like Yahoo returned “N/A” and “N/A” for a fund in your list. Which fund is it?
Leo says
When I tried your suggestion, a .csv file was downloaded & opened with Excel. I assume this is correct, so far. Several of them did not download the proper information to the csv file. However, if I tried the failed one a second time, it would work. This might explain what I saw a few weeks back. It failed when I tried quotes.py asit is doing now. But, when I tried it again it worked OK. Now, it’s permanently broken.
I hope you understand what I’ve written.
Leo says
Let me add more. Try VFIIX. It works, but then doesn’t work.
R Tandon says
Response to Chip:
I’m having the same problem. For sometime the quote updates worked well. But as of the last few days, one or more quotes displays a very large price during the download, something like 50554460.xx.80 . Looks like an error resonse from the website perhaps.
Harry Sit says
@Leo – Try changing “finance.yahoo” to “download.finance.yahoo” in this line in quotes.py:
url = “http://finance.yahoo.com/d/quotes.csv?s=%s&f=nl1d1t1” % self.ticker
See if it helps.
Leo says
I’ll try it, but will it work? Your suggestion in #203 doesn’t always work all the time.
Tried it. Doesn’t work.
RonS says
I tried to down a Yahoo portfilio to Excel, which I do alot, and all I get is garbage. There is definitly something wrong at Yahoo.
Ron
Harry Sit says
@Leo – If Yahoo doesn’t consistently return the quotes to you, I don’t think there’s anything you can do. The quotes.py script is simply acting like the browser pulling quotes from Yahoo. If it doesn’t work for you in the browser, the script won’t work either. Both addresses are working for me, all the time, with VFIIX as well.
Leo says
Your analysis is correct and makes sense to me. There is at least one other person with this trouble. How can I/we bring this to the attention of Yahoo?
Hal says
From 203–” @Leo, @RonS – It depends on what Yahoo returns for your ticker. Try this in your browser:
http://finance.yahoo.com/d/quotes.csv?f=nl1d1t1&s=VTSMX
Replace the ticker VTSMX in the end with your own fund’s ticker. For VTSMX, Yahoo returned”
=============
Try DELL for ticker***
I get the following return:
Inc. 505545600 1/1/1970 8:01pm
Notice the stock price!!!
Harry Sit says
@Hal – I tried this just now
http://download.finance.yahoo.com/d/quotes.csv?f=nl1d1t1&s=DELL
It returns
“Dell Inc.”,17.42,”7/19/2011″,”4:00pm”
Looks normal to me. It seems the problem is either intermittent or location specific. I don’t know what’s going on. Usually these problems go away after a few days.
bill says
It looks like every time the script is run the price is returning the weird number on different tickers each time, with the “NA NA” issue happening on different ones too. For example I ran it once and Apple had the odd price, and on the second time it didn’t and something else did. Hopefully Yahoo will get fixed soon and this is a temporary hiccup.
Hal says
It appears what Bill describes in #214 is happening..
Hal says
YAHOO DOWNLOAD STILL HAVING ISSUES AT MY END VIA PY SCRIP..
bill says
Like other Yahoo problems, this one will probably take a few days to sort out. I am developing a .NET Windows program that will download quotes and create an OFX file and am seeing the exact same behavior from Yahoo, it isn’t the fault of TFB’s script at all.
Leo says
A “few days to sort out” has been almost two weeks! I’ve switched over to the Java version. It still doesn’t update the prices for ETFs, but I only have 3 of those, so I do them manually.
http://microsoftmoneyoffline.wordpress.com/2010/02/12/java-app-to-update-quotes/
bill says
If anyone would like to try a solution I am developing in case Yahoo never gets fixed 🙂 you are welcome to try a tool I have developed. It is a Windows program that will let you specify a ticker list graphically, then let you modify what Yahoo is sending down before you create the OFX file.
I don’t want to hijack TFB’s thread with this, I’m just trying to develop a solution to give back a little after what TFB has done in making MS Money usable post-sunset. TFB if you don’t want this thread to be a discussion area for this effort I totally understand and discussions can happen at my place, just say the word.
http://syntap.com/blog/?p=82
Just to manage expectations, I’m only looking to to what TFB’s script does while Yahoo isn’t working. Please don’t ask for options/currency/whatever has already been discussed in this thread.
tom says
There is definately something going on with the Yahoo quotes server, I have been having the N/A N/A problem for the past week. The quotes returned have the name truncated the price is not right and the date/time is always 1/1/1970 8:01pm. Failures are random, I have multiple portfolios with a combination of funds and stocks.
Running various quotes direct from the url returns garbage as well. This is one result for RCL (Royal Caribbean Cruise Lines)
l Caribbean Cruis 505545632 1/1/1970 8:01pm
Tom says
Okay I just downloaded the java app, and used the Yahoo option to download two quotes: RCL and IBM
The results were not stellar:
IBM rnational Busines 505545504.0000 1/1/1970 8:01pm
RCL I Caribbean Cruise 505545504.0000 1/1/1970 8:01pm
This confirms to me that Yahoo Quote server is having issues.
Sterling says
I’m running money 2007 plus….in recent weeks mutual fund prices stopped
downloading for me too….then some days mutual fund prices would sometimes
download, but the change amount for that day would not be posted. Now today
(Friday 7/22) the mutual fund prices downloaded perfectly including the
amount of change from the previous price…I can’t understand why this
randomly works. It seems that when I’ve had this problem with automatically
downloading mutual fund prices, the prices seem to always work at the end of
the week on Fridays. Does that make any sense ?? What makes Fridays so
special to the MS Money program. Oh, I also have a few ETF’s and those
prices seem to always download each day just like individual stock prices.
Go figure !!
hleofxquotes says
FYI, hleofxquotes also support an alternate quote source: Google. For folks who are having issues with Yahoo might wan to give that a try.
As a data point, I am using Yahoo quote source on daily basis and have not seen the corrupted data recently. For those who are having problems, I wonder if you can provide additional information such as: where you are geographically and time/date when problem occurs. Just curious more than anything else.
Harry Sit says
Please feel free to use alternative solutions to this simplistic script. They have have many improvements.
hle-ofx-quotes by Hung Le: a Java application with graphical user interface
PocketSense by Robert: a more user-friendly package of Python scripts with menus and config options
bill says
Yahoo seems to be returning correct info at the moment, I am downloading quotes to SunsetOFX and they aren’t causing problems. The python script may be back in business.
For any interested, SunsetOFX will now optionally download quotes from Google.
If you are set up in Money for TFB’s python method, SunsetOFX should get you back up and quoting if Yahoo decides to to bad again or if you’d like to use quotes from Google.
http://syntap.com/blog/?p=82
Leo says
Hooray! I agree Yahoo seems to be back to normal.
RonS says
I finally recieved good data today with no glitches. All the quotes seem to be correct.
Ron
RonS says
A Message from Yahoo
Hello Ron,
Thank you for writing to Yahoo! Finance.
I understand you’re seeing incorrect or inconsistent data when
downloading quotes to a CSV file from Yahoo! Finance. I appreciate you
taking the time to report this issue and I apologize for any
inconvenience caused.
Our engineering team is currently working to correct this, and our goal
is to resolve the issue as quickly as possible.
Again, I apologize for any inconvenience this issue has caused. If you
have any questions or concerns, please feel free to contact us back.
We’re always more than happy to help and we appreciate all feedback that
helps us identify ways to constantly maintain and improve our services.
Thank you again for contacting Yahoo! Finance.
Regards,
Charles
Finance E2Y
Harry Sit says
If Yahoo has problems again, please report it to Yahoo with this form:
Yahoo! Finance Help Form
Tell them this link is returning bad data:
http://download.finance.yahoo.com/d/quotes.csv?f=nl1d1t1&s=YHOO
Only Yahoo can fix it.
Joan Reece says
I run a Python script to retrieve daily mutual fund prices from Yahoo to update same in Money (Money Plus Deluxe) and some of the prices are wrong. These wrong prices are in the Python output file (quotes.ofx). Is anyone else getting wrong prices for mutual funds when retrieving them from Yahoo through a Python script?
RonS says
Respone to 230, Joan,
Because the MF Co’s need to run their portfilio update processes after the market closes. Mutual Fund prices do not update on Yahoo (or anywhere else) until sometime after the market closes, in some cases after 5PM ET.
Build a Yahoo portifilo of your MFs and observe the time when the prices get updated. Then run your update program after that time. I run mine at 6:15 CT
Ron
RonS says
Second responce to 230. 8/10/2011
I checked my last 3 downloads and I see the wrong date for some of the quotes. The quotes are right for the date shown in the ofx file.
I tried running this url
http://download.finance.yahoo.com/d/quotes.csv?s=YHOO,vgtsx,ge&f=nl1d1t1
on Yahoo and sometimes I get the right date and sometimes I get a different date. For stocks the wrong date is always 8/2/2011, and for MF the wrong date always seems to be 8/1/2011.
Wrong Quotes: (Run on 8/10/2011)
“Yahoo! Inc.”,12.76,”8/2/2011″,”4:00pm”
“VANGUARD TOTAL IN”,15.99,”8/1/2011″,”5:52pm”
“General Electric “,17.21,”8/2/2011″,”4:00pm”
Right Quotes: (Run on 8/10/2011)
“Yahoo! Inc.”,11.77,”8/10/2011″,”4:00pm”
“VANGUARD TOTAL IN”,13.74,”8/10/2011″,”5:54pm”
“General Electric “,15.09,”8/10/2011″,”4:01pm”
I reported this information to Yahoo finance in and error report.
Ron
JoanR says
RonS,
Thank you for your replies including letting me know that you submitted an error report to Yahoo. In regard to the timing of pulling the daily mutual fund prices from Yahoo I run the Python script at 6:30 PM through a Microsoft Scheduled Task.
One other problem that I have had is updating the daily price for the Tweedy Browne Global Value Fund in Money. Although the Python script output file (quotes.ofx) contains this fund’s daily price, the price does not consistently get updated in Money. At this time it is not working. It mysteriously started working sometime before the Yahoo problem that got fixed on July 25. After that fix, the daily price for that Tweedy fund in Money stopped updating.
I am going to look back through the various users’ comments on this page to see if anyone else has been able to use a different source for daily prices as there have been a number of problems with using Yahoo and therefore it puts into question the confidence of relying on the mutual fund data in Money.
Bill says
Yeah, the quotes still seem to be funny at times. I have a few mutual funds with prices a few days old even now, and those prices are what would download so it appears to have wrong prices when in fact they might just be old. That’s part of the purpose of SunsetOFX, you can see the quotes and change dates and make corrections graphically before creating the OFX file.
If the Yahoo quotes look bad, you can download the Google ones to see if you have any better luck.
http://syntap.com/blog/?p=82
The Dude says
Well, I can’t live without Microsoft money. So here what I did: Created an account with yodlee.com. Setup all my accounts there. From their transactions screen that has all account’s transactions. I download an excel file with all accounts transactions. Then from that I create OFX file for each account and import them. Since I’m a software developer, I developed the whole thing to be done with a single button click and it does it all 3 minutes latter I go check money for all my new transactions.
ARMLJ5 says
I know this is off topic, but before I can try the suggestions above I need to migrate my data from MS Money 2003 Standard into Sunset version. 🙁
Anyone able to advise – I’ve searched high and low without finding a solution that works. At this point the best I can do is to create Account Transactions and Investment Transactions reports that I can export to Excel as .csv files (the “Send To” options do not work as MS Money crashes / produces emtpy xml data).
Anyone able to help with the next step?
David says
After not having any problems with the Python script for many months, I’m just now getting an error. Can anyone help with this?
****************************************************
Traceback (most recent call last):
File “C:\Users\David\Documents\Money\quotes.py”, line 311, in
sec.getQuote()
File “C:\Users\David\Documents\Money\quotes.py”, line 153, in getQuote
timeStruct = time.strptime(quote[2] + ” ” + quote[3], “%m/%d/%Y %I:%M%p”)
File “C:\Python27\lib\_strptime.py”, line 454, in _strptime_time
return _strptime(data_string, format)[0]
File “C:\Python27\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’
Harry Sit says
@David – Yahoo! had this problem back in the summer. Read from comment 201 on down. You either tell Yahoo! to fix it or just wait until they fix it on their own. Or you use one of the alternatives mentioned in the comments.
Luca says
Hi. I need help on bond prices.
when i import bond price (ie ticker X09142.TI) the price is uploades as 100 times the real price (ie the market price is 100.55 and appears as 10,055.00 in Money). below what appears in .OFX file.
X09142.TI
TICKER
4.875 A2A 13
X09142.TI
100.55
someone can help me?
thanks
Luca
Bill says
MS Money does not seem to track bonds the same way as stocks. Price updates as I understand them are updated based on percentage of par value, as opposed to par value itself. Check out the two links below, I don’t know if there is an easy answer other than sticking to manual updates for bonds. I only track a few I and EE bonds and I handle those manually. But once you figure out what MS Money is doing with the quote, it may be possible to download the quote into SunsetOFX along with your other quotes and manually adjust the price to be a percentage or whatever it is MS Money is looking for. A pain but still less so that the steps needed within MS Money for a manual price update.
http://support.microsoft.com/kb/283846
http://www.cdnbusinessdirectory.com/microsoft-money/2956-bond-price-updates
Luca says
Bill, thanks for your prompt answer!
Given the fact that all bond have a ticker (I checked for Italian bonds but I guess is the same for the oders), should be simple create a subrutine that write the favue divided by 100 in the .OFX file, just for bonds.
this will import the right prices into Money.
I can’t make it by myself because I’m not used to Phyton, sorry 🙁
Thanks
Luca
Jim Clayton says
Thanks to you and others, I’ve been able to continue using Money by downloading quotes via Hung Le’s applet.
BUT now, my new Windows 7 compute has a Java/Windows problem I can’t seem to lick. I can’t download/upgrade Java and current Java apparently won’t fully uninstall. So, I can’t access Java. When going through the process, I encounter a 1723 error code stating that a .dll is missing. The Java site is no help and Control Panel shows Java installed but it isn’t. Some way, I think I have to get all traces of Java off my compute before I can re-install Java. From web browsing, this seems to be a fairly common problem. However, I can’t find a common solution. Any help or advice from you or your readers would be very much appreciated.
Bill says
Luca, I am not a Python expert either, but if you want to try SunsetOFX (a Windows program, not a Python script) you can adjust the bond ticker price before it gets sent to MS Money.
Jim, if no one has a Java-based solution then this page’s Python solution or SunsetOFX (see post 19) should be workable alternatives for a Windows 7 user.
hleofxquotes says
Jim, I have not run across that problem before.
John says
Is anyone having trouble dowloading their prices into sunset tonight 6-26-2012. I have tried it on three computers and get a corrupted file and cannot import. Is it just me tonight. I have been using this script for years.
Fer says
TFB: I appreciate very much this script. It has saved me a lot of data entry time. I have a quick q re mutual funds… I have some canadian mutual funds. I included the codes Yahoo has for them, but it fails when it attempts to get the canadian m fund information. It seems Google has the actual fund codes. How could I treat those? Would it be a quick change to have the script pull fund info from Google? thnx
Alan says
We downloaded the script. Since it was in HTML, had to reformat the entire file so it would run, i.e. separate the lines, add indetation, etc. Now it runs and gets all the prices, and creates a OFX file, but money has trouble reading it, even though I have created a dummy investment file. All the comments above, and non talks about having to go thru all the pain we went thru to fix the format and get it to run w/o syntex error. Can you guys help?
Thanks
Alan
Alan says
Basically, we either need to have the script that runs w/o having to make any changes to it, or a sampe, OFX file to see how the one we generated differs and why money is having problem with it.
Thanks
Alan
Matt says
Thanks for the script. I have been using MS Money 2000 for 12 years and the online quotes worked for me just fine until September 6 of this year. This script is now working for me. Thanks also to Jeff’s clarification on setting the units from 0 to 1, once I realized this was a modification to the Python script I was in business.
Charlie says
To follow up to Matt and Jeff, I changed the units to 0.001, to keep the amount in the Dummy investment to the minimum. Works great. I was worried after Money stopped automatic updates last week, but I’m back in business. Thanks!