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.
Harry Sit says
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 says
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 says
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 says
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 says
Google accepting suggestions
Already prices in the ofx file
RonS says
Hi TFB, After looking for a replacement for Money I was dispointed in what I found. I have been a MS Money user since it was first released so decided that since the only online feature I used was Quote downloads for my portfolio I would develope a routine to get the quotes into money using my Excel experience. Doing my research on how to convert .XLS files into .QFX i ran across your site, the internet is great, and decided to try your scripts. I am useing 2008 Money Deluxe Plus.
I followed all your instructions and they worked great except that some mutial funds and ETF’s would not update in Money, but the data in the QFX file was correct. The script was geting the correct info but Money wasn’t accepting the data. After some investigation I uncovered the issues, which turned out to be my problem with Money setup issues.
1) Some of my mutial funds were set up as “Investment Type” “Other”amd some of my ETF’s were setup as “Mutual Fund”.
To get there and check yours… In portfolio select a stock/fund, Right click, select “Investment Details” observe “Investment Type” box. Unfortunately you can’t change it.
After playing arround with this and doing some reserch on MS web site I ran across this artical on how to change an investment type.
http://money.mvps.org/faq/article/305.aspx
I converted 6 investment types using these instructions some had over 30 transactions, (buy, sell, dividends, LTCG, STCG, Reinvest div).
In step 2, When I added the large investment to the “OLD Investment” I used the “Add Shares” Feature.
If you name your new Investment to fall just under your old investment in the investment drop down list, it becomes very easy and quick to make the changes. It becomes all “Mouse Clicks”. In your transfer account the only change will me the name of the transfer source.
Another think I discovered, You don’t need to make any entries in the “Dummy Investment” account. My portfolio has 36 entries in 4 accounts with some duplicates. Every thing updates with the script with no entries in my Dummy Investment account.
Bigguy says
TFB – After reading all of the comments, I am still not clear as to how to perform this process. I found that the 2.6.2 downloads were not available at either Active Python or Python websites. Which version should I use? Also, where do I download quotes.py.txt?
Sorry, but I guess I’m not very savy about this.
Thanks for your help.
Harry Sit says
@Bigguy – The most recent version of Active Python or Python will do, as long as it includes SSL. The quote script is here. I renamed it to quotes.py.
whm says
I’ve tried adding ^DJI to the stock list as suggested by Ameridan but the script gives the following error:
Traceback (most recent call last):
line 232, in
sec.getQuote()
line 74, in getQuote
self.price = quote[1]
IndexError: list index out of range
Here is the script:
stocks = [“AMZN”,
“GOOG”,
“PG”,
“YHOO”,
“^DJI”]
What have I done wrong? Thank you.
ameridan says
Within the last 2 or 3 weeks, Yahoo stopped providing quote data for the Dow Jones symbol. Not sure if and when Yahoo will resolve this issue.
Chris says
Thanks so much for creating such an elegant solution to a very frustrating problem. The installation and use of Python was much easier than I was fearing. Kudos to the folks that made this possible!
Dan says
For those needing a newer version of Money, you can get a copy of Money Plus Deluxe free downloaded from Microsoft. This “Sunset” version is fuly functional and does not require registration.
Just search for “Money Plus Sunset Deluxe”
Dave says
ameridan
as you say, “^DJI” is no longer updated on Yahoo, but you can use “INDU” (at least, until they stop that too)
regards
Dave
Lou says
I am wondering if this script will work with options
David says
I’ve been downloading prices recently by changing the date of the operating system to 2007, and then downloading before it updates automatically when I go back online. (For those who haven’t heard this before, here are the steps: 1) turn off your internet connection; 2) change the OS date to 2007 by double-clicking on the date on the status bar at the bottom of the screen; 3) open Money and make sure the date reflects the date you set; 4) turn on your internet connection; 5) quickly run your “Internet Updates” as you used to do.
Unfortunately, I do not know if this will work after January 1, 2011. Does anyone know if Money will shut off the ability to go online at all? I downloaded the MS Money Sunset version, and it looks like it doesn’t have any online capabilities at all, even when I change the operating system date back to 2007.
John Hennessey says
I was just wondering, for those of us in the UK, would it be possible to take Fund quotes from the Yahoo.co.uk server rather than .com (which has now UK Mutual Funds)?
Thanks so much!
Steve says
Re: #51 Rich Smolucha on January 13, 2010
Rich,
It looks like nobody has replied to your post, but I’d be interested in the VBA route.
I have a similar Excel workbook, which I’ve set up to automatically download prices from London stock exchange. I’d much prefer to be able to import these closing prices into Money.
Any chance of posting up the file somewhere, or even the macro to reformat the data to OFX file?
Thanks for any help.
Bill M. says
Thanks for this script…worked perfectly in MS Money Plus, sunset version. Exactly what I needed, and written in my favorite language to boot. : ) I’ve fought an fought with Quicken and I think I’m just giving up and doing the old school money thing manually. Maybe I can try to automate some of my data importing using Python and OFX files if I get bored. : )
Grady J. says
TFB, thank you so much for making this available. I have been looking at AceMoney and other alternatives since I found out about the shutdown, but none of them were as nice as Money.
I installed Python 2.7. I added your quotes.py script to a /scripts subdirectory in the Money program directory, and created a shortcut on my desktop. I created a Dummy Investments account with no stocks. I substituted my stocks in the script. I don’t have any mutual funds, so I modified funds to: funds = [ ]. The prices in Money were updated as desired. I found the quotes.ofx file in the same subdirectory with quotes.py.
I have been using Money 2005(V14), which is still updating as of Dec 2010, but I guess that will end next month. I installed the Money Plus sunset version and the python script on a different computer running Win7 for testing. The sunset version updated my 2005 money file, but I noticed no real difference in the new sunset version (V17).
I was not aware that Python was such a powerful high level language, with GUI interface capability. I have been wanting to learn a programming language with that capability for tasks such as this. I will have to look into Python.
Thanks again,
Grady
ameridan says
Bill & Grady,
Seeing as to how you’d like to enhance your Python scripts, you might want to also check out PocketSense and some of the other options I cover in the Microsoft Money OFFLINE Blog @ http://microsoftmoneyoffline.wordpress.com/
PocketSense started out as enhanced versions of the Python scripts covered here.
Dan (ameridan)
Harry Sit says
Yes, Robert, creator of PocketSense, and Dan are much better with Python than I am. They took the scripts published here to the next level. I now use PocketSense myself.
G Coutts says
Thanks for this. I’ve tried the Quicken import and it proved to be a joke, I currently have multiple portfolios of over a hundred Canadian, American and UK traded shares in USD and CAD with hundreds of foreign exchange transfers between various bank accounts and cash accounts and thousands of same currency transactions going back to 1993. All Quicken did was create hundreds of duplicate transactions and even fails to recognize the Money tickers and convert these to Quicken tickers. A total waste of money and time.
Your process with some modification at least allows the continued use of Money.
I’ve created to Python scripts, one for CAD securities and one for USD securities. Manually I then need to convert the ofx file these generate to convert the Yahoo tickers to Money tickers as my Canadian Dollar based files identifies US tickers with “US:” and Canadian tickers as just the TSX ticker. This is simple enough use find/replace in Word. I can then import the two scripts into two different Watch lists by using two different fictitious brokerage numbers.
What would be nice would be some advice as to whether it is possible to automate the US Yahoo to CAD based Money ticker conversion within the Python script so that the resultant ofx file can be opened automatically by Money without the manual Word intervention.
Steve says
I’ve managed to modify the script to work for London Stock Exchange quotes, with default currency GBP, so you should be able to do the same for CAD.
Give it a try. Open the quotes.py file with PyrhonWin, and edit the line…
currency = “USD”
to
currency = “CAD”
…and save it as a separate file.
You may find it best to amend your Canadian stock tickers in Money and also in the sites.dat file, so that they’re the same as Yahoo tickers. ie with the .TO suffix
Manu says
This website is great! I have been dreading switching to another money management program since I have used MS Money since 1998. I use Money almost every day. I also have no idea why MS stopped providing online account updates or will stop providing online quotes, but I have been trying out some alternatives. I checked out Quicken (not sure which version) and I feel that it’s very different from Money. Money seems much more easier to use, but maybe it’s because I have used it for 12+ years. I also tried Mint,com which is a great aggregator, but I haven’t figured out how to enter all of my bills to better forecast my checking account balance.
I am very interested in checking out the work-around on updating quotes for my portfolio. I have never heard of Python, but will try to create the script to update quotes this weekend.
Thanks for your help!
Moe says
Anyone tried using iBank 4? I now use a Mac and have read some good reviews.
Mike R says
Great script. Not a programmer, but I did get it working for MS Money 2008. Did like Tom’s (number 30) entertainment. I found this useful because if I entered a bogus ticker symbol, the program would hault there. Was just wondering if there is a way to show which ticker symbol failed (ticker symbols have been known to change), like a pause in the program if it doesn’t exist and also display the monetary value of the ticker symbol as it says “Retriving quote for AMZN – $184.68”?
Cameron says
Well, this tool is amazing. I am loving it.
It works perfectly for stock quotes.
I am now trying to get my Canadian mutual funds in the script now. I have the right symbols, as verified on Yahoo. They are:
F0CAN05MZD.TO
F0CAN05NG1.TO
AGFCANADIANG.TO
The script isn’t successful in creating the OFX file. It seems there is a problem with the date stamp on the quote from the traceback file that quickly pops up.
I have tried putting the symbol is as either a stock or mutual fund to no success.
I am not a python expert, although have learned a lot already.
Anyone have any tips out there? A solution would be fabulous.
Hung Le says
Cameron: an alternative is to use Google’s portfolio get the quote price. I see that F0CAN05MZD.TO is available as MUTF_CA:AIM1581.
Then use a test version of hleOfxQuotes.jar to fetch your GooglePortfolio’s data and convert them into *.ofx.
Best,
Grace DiVecchio says
I need help. I am 77 years old, running Windows 7 and have used MS Money (now on 7) since it first came out. All of my checks are written thru it and I have a major portfolio which — obviously — stopped updating quotes yesterday. I don’t understand what you mean by “writing scripts”. I’m not a programmer, but pretty bright. When my new computer was installed recently (1T w/quad core processor & 2 monitors), Quicken was pre-installed. I’ve never opened it. Should I do so now? OR – Can you tell me how to get my updates so I can continue using MSN, with which I have a comfort level ?
Any and all advice will be welcomed and most appreciated.
Grace DiVecchio says
PS – I said MSN, but meant Microsoft Money.
Jim Clayton says
I’m late to the party but have been using MSN Portfolio Manager for over 10 years and have lots of data I hate to lose. Currently have been using MSN MOney Plus Deluxe. I care about nothing other than Portfolio Manager.
I’m not knowledgeable about Python, etc. and not much more than moderately computer literate. I would gladly pay for the proper software, script and instructions to make this work. I manage about 25 portfolios for family, etc. and am now at a standstill. Would you be interested in providing info for a price?
Thanks,
Jim
ameridan says
Jim,
I’m betting you already hava Java on your PC, so let me point you to a Java app that is VERY easy to install on your PC (download and run) and type (or import a list) in all of the stocks and funds you want to import the quotes into Money.
See http://microsoftmoneyoffline.wordpress.com/2010/12/25/java-app-not-python-to-update-quotes-into-money/
Gerry says
Ameridan — Thank you so much for pointing out the link to the Java solution. For those of us that have been unsuccessful with Python (I tried and tried), the Java solution is really a godsend. All I’ve wanted was to be able to easily update my investment portfolio (I never used the downloaded bank statements even when that was available on Money) and I kept striking out. Once you pointed out the Java option, I had it working (and updating) in minutes. Many, many thanks to you and Hung Le!!
Jim Clayton says
Ameridan,
Thank you for responding. And is there a tip for importing a list rather than typing in a long list of symbols?
I apologize in advance for probably contacting you again. Just trying to get this thing to work – I’m a decent investor, but not so good at computer language.
Jim
RonS says
TFB Great site. I had the same issue as Post # 15 where I needed a daily mm Fund quote for my Excel analysis sheets which import Money data using “Ultrasoft Money Link”. After some research and updating my MMF price manually I came up with the following solution which works great.
Because Yahoo doesn’t provide a quote for MM funds and the prise is always $1.00 and I needed a daily quote for my Excel analysis sheets, I made the following changes to the script.
To add Money Market Quotes each time I run the scripe, I inserted a fixed quote for my MM fund “VMMXX” as follows.
Find the following Section (line 138:
for mf in self.mfList:
posmf.append(self._pos(“mf”, mf.ticker, mf.price, mf.quoteTime))
And Modify as follows:
—————————————-
for mf in self.mfList:
posmf.append(self._pos(“mf”, mf.ticker, mf.price, mf.quoteTime))
posmf.append(self._pos(“mf”, “VMMXX”, “1.00”, mf.quoteTime))
—————————————–
Just copy/past the “posmf…” line and detent the entry back even with the for loop and replace “mf.ticker” with your MM Fund symbol “VMMXX” in my case and mf.price with “1.00”. If you have more than one MM Fund you can add multiple lines.
Then Find the following section (line 194):
——————————————
for mf in self.mfList:
mfinfo.append(self._info(“mf”, mf.ticker, mf.name, mf.price))
——————————————
And Modify as follows:
—————————————
for mf in self.mfList:
mfinfo.append(self._info(“mf”, mf.ticker, mf.name, mf.price))
mfinfo.append(self._info(“mf”,”VMMXX” , “Vanguard Money Ma”, “1.00”))
—————————————-
Just copy/past the “mfinfo…..” line and detent the entry back even with the for loop.
Replace “mf.ticker” with your MM Fund symbol “VMMXX” in my case, “mf.name” with your fund name “Vanguard Money Ma” in my case “I truncated the name to 17 chars because all the other names in the .ofx file were truncated” and mf.price with “1.00”. If you have more than one MM Fund you can add multiple lines.
These mods will add the MM fund quote to the .ofx at a fixed price of $1.00 each time you run the script.
Ron
I have been using the script with this modification for some time now with no issues.
Jim Clayton says
I have downloaded Hung Le’s latest hleOfxquotes (with the import to MNMoney button.
Before I run this the first time, a question or two for anyone who may help:
I’m running about 25 separate accounts in my Money Plus Sunset edition Portfolio Manager. Some stocks, funds, and ETFs are unique to a particular account while many equities are common to several or all accounts. If I list each symbol only once, will this update that particular holding across multiple accounts?
Also, I know how to set up a dummy acount with fake holdings in Money, but I’m not sure how to restrict this update to just the dummy account when I activate the “Import to MNMoney” button. Any help is appreciated.
Jim
Harry Sit says
@RonS – Since Money Market funds maintain $1.00 NAV all the time, why not take the Money Market fund symbols off the list? The price won’t change. You set it to $1.00 once and it will stay at $1.00.
Grace DiVecchio says
Can some one please help. I have no idea how to download the Java app mentioned in #132. When I click the link, the Blog page comes up.
All I want to do is be able to update my investment prices in MSMoney.
Jim Clayton says
Grace,
From the Java apps blog site, click on the “here” below and you can download it.
The first comment I received on my new Blog site is from HLE and he offers a Java app (hleOfxQuotes) he wrote to obtain quotes from Yahoo! and output them into an OFX file for import into Money. If you already have Java installed, download the app “here”, save it on your PC, and just click on it. HLE’s install notes are at the bottom of this post.
Jim
Gerry says
@Jim Clayton: Yes, just list all the symbols you need to update prices for…they will update in all your accounts (and in multiple accts if you have the same asset in more than one acct). I have multiple active and rollover IRAs, taxable accts, etc. One list in the Java app and they’re all updated at once.
When you hit the “import into MSMoney” button, it will ask which acct you want to associate it to. That’s when you choose the Dummy Acct.
whm says
Is there a method to get MS Money to compute and show the daily change value and total change in the Portfolio Manager View for the investments whose prices were imported via the scripts?
Thank you.
ameridan says
i don’t believe that will ever be possible.
Grace DiVecchio says
Thanks to Jim and Gerry for your help downloading. My portfolios include over 150 investments. Do I have to manually list all of them? Are they separated by commas? Pressing the Enter Key does NOT bring me to the next line. Also, how do I list Preferred Stocks? E-Trade shows (as an example) PSA.PR.A; MSMoney shows PSA-A. Tried both and neither took.
Last question: Please confirm that when I get all my Stock Symbols entered and import them into MSMoney, I am supposed to import to the dummy account, but the prices WILL update in my various portfolios where they are actually held — right?
Cannot thank you enough.
Mike Marando says
Its 2/5/11. I opened Money this morning and all my quotes had been updated to yesterday’s close. I did nothing but open the program. The update quotes command would not work. I don’t understand what is going on. It must have updated itself while the program was closed. Anyone experience this?.
Hal says
You’ve got to be kidding………………
Gerry says
@Grace: Yes, you’ll have to manually list all your investments the first time. The Java application will save them for future updates. Click the “Update Prices” button and it will populate the info at the bottom of the app. Then click “Import to MSMoney” (have your Money program open at the same time) and have it import to the Dummy Acct. YES, it will definitely update ALL the investments that you listed in the Java app even if they aren’t in your Dummy Acct (my Dummy acct has 3 investments in it, but I update 26+ investments in 8 different accounts.
Gerry says
@Grace: Oh yeah, just separate the investment symbols by a comman; you don’t need to hit Return.
Grace DiVecchio says
OK – Got it. Thanks for the super help.
Any advice on how to enter the preferreds?
What about Bonds? If I put in the Cusip Number, will it track?
Gerry says
@Grace: Sorry, no. If it doesn’t have a ticker symbol, I can’t help. All mine are stocks and mutual funds.
Jim Clayton says
Not directly related to the thread, but certainly related to the Money problem:
Does anyone have knowledge of, or experience with, Acemoney?