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.
Jim Clayton says
I guess I’m confused by the importance of the dummy account.
I’ve been using the OFX update created by Henry for over a year.
I created a dummy account tha has no entries in it. My numerous holdings/trackings in a quantity of MS Money accounts load and update with no problem.
So, what’s the use of entering the asset names or symbols into the dummy account?
Jim
Chris says
Henry and TFB
Thank you all for the Money OFX script.
Have got it working with my Money2000 but as per Luca’s comment above the accounts are set up in pounds not pence – which is how the yahoo quotes are delivered to OFX. Have tried to rework the script but am no Python expert as well – surely there must be a way to divide the quote by 100 prior to writing it to OFX.
Have tried to use SunsetOFX as well, but for some reason it crashes after the first quote retrieval.
Many thanks to anybody that can help out
Harry says
Chris – If you only need to divide the quote by 100, find this on line 76 in quotes.py:
self.price = quote[1]
Make it
self.price = quote[1] / 100
Bill says
Chris – If you’d like me to check out what is making SunsetOFX crash on you let me know via the blog link in #219… with your tickers.xml file I should be able to hunt down what the trouble is for the quotes you are pulling up.
Chris says
Harry and Bill – thanks for your replies.
Bill – would like to try to get Harry’s script working as it is so convenient, but will be in touch if not.
Harry – thought I had tried that solution – but maybe I got the syntax wrong or something. Home tomorrow so will see how I get on
Thanks
Chris
Gary says
Is there a way to get price updates in MS Money 2001? When I try to import, it says “Import Complete”, but the prices don’t change. The same file will import into Money Sunset without any problem, so the problem is not with the OFX files. I have tried both Pocket Sense and Hleofxquotes and both work the same way. I wanted to stay with the 2001 version if possible, because the layout is much larger (no wasted space) and when prices are updated manually, high/low target alerts will pop up. Any help will be appreciated.
Gary
Harry says
Gary – Older versions don’t like the number of units of 0. Try change the number of units to 1 or 0.001 as Charlie mentioned on Sept. 15, 2012. It’s on line 130 of quotes.py.
_field(“UNITS”, “0”),
Make it
_field(“UNITS”, “1”),
You will need to exclude the dummy investment account from your net worth and other reports because now it will have some value.
Chris says
Harry
As per #253 I’m getting
Traceback (most recent call last):
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 240, in
sec.getQuote()
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 81, in getQuote
self.price = quote[1]/100
TypeError: unsupported operand type(s) for /: ‘str’ and ‘int’
Line 81 for me due to my ticker list
Thanks
Gary says
Harry,
Thanks for your reply. I changed the units in the quotes.py file, but still got no updates on the stock prices in MM 2001. I may have to keep the MM Sunset version.
Gary
Harry says
Chris – Try
self.price = float(quote[1]) / 100
Chris says
Harry – thought you had cracked it – producing good numbers and then
Traceback (most recent call last):
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 250, in
content = writer.createContent(currency)
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 223, in createContent
self._invStmt(currency),
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 151, in _invStmt
self._invPosList()
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 115, in _invPosList
posstock.append(self._pos(“stock”, stock.ticker, stock.price, stock.quoteTime))
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 136, in _pos
_field(“UNITPRICE”, price),
File “C:\Users\Chris\Money Quotes\Money Quotes.py”, line 33, in _field
return “”+value
TypeError: cannot concatenate ‘str’ and ‘float’ objects
Cheers
Chris
Harry says
Chris – One more try.
self.price = str(float(quote[1]) / 100)
Chris says
Harry
Thats got it !!
Many thanks for your help
Chris
Chris W says
Thanks Harry, I’m a different Chris but appreciate the
self.price = str(float(quote[1]) / 100) line.
By the way, because I wanted to keep an eye on the FTSE I added an if statement:
if self.ticker == “^FTSE”:
self.price = quote[1]
else:
self.price = str(float(quote[1]) / 100)
What seems to be amazing is that for some reason it updates prices whenever you run the script not just once a day! Don’t ask me why, but it does for me. By the way, for reference, I’m using MM 2001 Personal and Business edition. I’ve also added the ^FTSE as the last stock listed after all my normal stocks.
Does this work for anyone else? Have I stumbled upon something quite useful?
Chris W
Eric Desfonds says
Like many, I am now looking for an alternative, using MSMoney 2000 since day one, in Canada.
Beyond the units == 0 issues, which I fixed by having units = 0.001.
My last residual problem is Mutual funds in Canada, the tickers are very odd in Yahoo, here is an example for
BMO Guardian Monthly High Income, where the ticker is actuall “F0CAN05N8I.TO”
Using “F0CAN05N8I.TO” in the stocks or funds array returns an error message due to the timestamp, see below. I would need some ideas on how to simply record today’s date all the time.
I believe, basically that these funds do not have timestamp, only a closing price.
Traceback (most recent call last):
File “C:\(…)\quotes.py”, line 231, in
sec.getQuote()
File “C:\(…)\quotes.py”, line 73, 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 10:-230am’ does not match format ‘%m/%d/%Y %I:%M%p’
Eric Desfonds says
Follow up, basically the csv file download is empty, for example, the URL created for the above fund downloads:
“F0CAN05N8I.TO”,0.00,”N/A”,”N/A”
So the price, date and time are invalid, this is not related to the fund name per se, but the quotes.csv format key requires a slight change
Example from another stock that works:
“LEONOVUS INC”,0.16,”9/28/2012″,”12:26pm”
Having a lot of time on my hands right now (knee surgery, so am bed ridden), I am reading up the Wiki for quotes.csv via http://code.google.com/p/yahoo-finance-managed/wiki/csvQuotesDownload, I have been unable to find another key that gets the right date format.
I will try to spend some time to integrate Google as another feed source. will share results afterwards.
Eric Desfonds says
I tried to cut and past my Google version here, but it did not work, please contact me if you are interested. A few issues
1) Google does not return the year, so I injected the gmtime() year into the sequence (if not the OFX has a 1900 year so updates were not working)
2) I removed all text before the “$” symbol, which may cause issues with multi-currency portfolios
3) I removed the “time zone” text since the %Z parsing was not working. Not a worry for me since I now update once a day late at night, but for people with portfolios where time is critical this could be a concern (anyways MSMoney only updates pricing once a day officially).
I can email my code for your review if you are interested, just contact me.
Chris says
Chris W – was interested in your FTSE script. Unfortunately with my version of MM2000 I got one update and then it froze the whole account – not quite what I was hoping for.
Gary # 259 if I can sum up what appears to be the behaviour of Harry’s script with ~MM2000….
– you will get an update of valuation and profit/loss whenever you run the script – sadly no daily change or price update. However, as mentioned above, MM does consider Friday differently and if you run the script at the weekend after the market closes you will also get an update of the prices.
Bigguy says
This is probably the wrong forum for my problem. I am attempting to install PocketSense. At the “setup Python File”, I am looking at “Main Menu” in what looks like the command prompt. I am trying to add an account. When it comes up, there is a curser at the bottom after “Selection: [0] _” I enter “1”, then up jumps “Site List” with the three sample accounts. Below that is the command “Enter Site #: [0] _ . What am I supposed to enter at this curser? I’m pretty stupid about this stuff, and have been searching for instructions for this for a couple hours! Anyone who could help a senior citizen would certainly be appreciated. Thanks in advance.
CDamico says
As of today 11/3/12 Money is up to it’s old tricks. It would start to load , then disappear. I changed the date of my computer to 11/3/11 & it works as before. Any suggestion as to how to prevent this?
CDamico says
I reinstalled Money. It installed OK. Once I restored my backup file it shut down. It still works if I change the date of my PC to 2011. Once I change it back the next command shuts it down.
This is what was going on before Sunset version was issued.
Any help would be appreciated.
Jon says
Wow, this posting went on for years, cool!
I can see there are allot of folks that hate the sunset policies of various financial programs.
I was burned by Quicken and had to upgrade to get the express web connect to work again.
All I want is a simple checking account financial program that I can automatically update my checking account with, that HAS NO 3 YEAR TERM LIMITS ON!!!!
Probably to much to ask for.
But Quicken is so feature rich and none of the other programs; GNUCash, MoneyDance, etc. come close.
So, I guess I’m stuck with upgrading Quicken every 3 years. After all, Quicken has to protect their revenue stream.
David says
It looks like I am getting the old error related to the lack of a time stamp. The script does not know how to deal with an “N/A” for a time stamp. Other tickers that have a time stamp are pulled in without a problem. Does anyone have some advice? Thanks in advance.
Traceback (most recent call last):
File “D:\Money\quotes.py”, line 329, in
sec.getQuote()
File “D:\Money\quotes.py”, line 171, 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’
Daryl Reece says
For what it is worth, I had issues in late 2012 with Money just shutting down on OFX import. I traced it back to a Windows service patch. Check out http://blogs.msdn.com/b/oldnewthing/archive/2012/11/13/10367904.aspx. I think the offending Windows update is kb2756872.
CDamico says
I am/was having the OFX import problem. I only use money for checking account but having 10 years of data is hard to give up on.
I found a program thats called “MT2OFX – V3.5.37” that will handle certain bank downloads, OFC, OFX, QIF & convert them to what seems to be an OFX file that will import without shutting things down.
I download the file but don’t let it open MONEY, then I run it through the converter then import the new file into MONEY.
I don’t have a deep enough understanding to use the fix from the 2012/11/13/12 entry above but since mine are bank imports only, I’m getting by.
Here’s the link to the converter program: http://csmale.home.xs4all.nl/mt2ofx/en/index.htm
Hope this helps.
ameridan says
The dll issue that Daryl pointed to is actually quite easy to resolve as the fixed file is available on my blog http://microsoftmoneyoffline.wordpress.com/2012/12/02/windows-8-64-bit-version-compatibility-with-sunset-money/ assuming you are using the Retail or SUnset editions of Money Plus Deluxe OR Home & Business.
Louis says
This is a wonderful simple setup for getting all the required stock quotes for MS Money. However, Yahoo Finance does not seem to have Candaian mutual fund quotes but I could get them from Google Finance. So, how could I change it to use Google Finance for retrieving all the stock and mutual fund quotes. Thank you in advance for your help.
Harry says
PocketSense and hleOfxQuotes greatly improved upon the rudimentary script here. Please consider using those.