Obtain stock and fund quotes after July 2013

On 1st July 2013, Microsoft finally turned off the feeds which some users had continued to use to update their stocks and funds in their portfolios. It is reasonably clear that stock downloads will not be brought back, as the product is long out of support.

For those who want to continue to get quotes, there is fortunately a workaround. It's a few more clicks than before, but will work. I recommend that if you only have a few quotes you use the manual update price option against the stock or fund and type in the price. If you have a lot, then this will save you time.

This may look scary, but it's not that bad once you have gone through the steps.

First time only steps

Ensure you follow the steps below first. You should only need to do each one once.

Required software

You will need two pieces of software:

• Java - the latest version can be obtained from java.com
• hleOfxQuotes - this can be found here

The latter program is the one which will do all of the magic. You can find more information about it at https://code.google.com/p/hle-ofx-quotes/ - these steps only cover the basics

Installation

The java software will need installing, but the hleOfxQuotes file only needs to be put in a place where you can run it regularly (you can rename it if you like). Java should install automatically when you download it. If not, double click the file and follow the steps.

The second step is to start Microsoft Money and create a NEW investment account and ensure it is marked as a "Watch Account" (see also the first two steps in Article 223).

Running hleOfxQuotes the first time

When you want to download quotes, you will run hleOfxQuotes. Double click the file you downloaded earlier (if it does not run, then you may need to check that Java is installed, and/or restart your PC). You will see the screen below.



Do the following steps to set the program up:

• Select Edit->Quotes->Currency and set it to your home currency.
• If you are using Money 2005, also select "Force INVTRANLIST" and set it to true.
• If you are using Money 2005, set the "Incrementally increased share count" setting to true.

If you have Money Sunset, you shouldn't need to set these two options.

Online account

If you are in the UK, the best online account is ft.com. Other countries can probably use Yahoo for their quotes. The use of ft.com does incorporate a couple of extra clicks unfortunately, but not using it may cause problems with pounds and pence conversion of prices.

If you need to use ft.com, go to FT.com portfolio page to set up your portfolio. You will need to register (free) for ft.com. Once you have registered, set up your entire portfolio in there. You do not need to add any purchase or sale information, you only need the correct stocks and funds to be loaded to allow you to download them later.

If you are not using ft.com and can use Yahoo, then the symbols you want to download to Money can be set up in the screen of hleOfxQuotes (for example where the BP. symbol is in the image above).

Operations you will do more than once

Getting the quotes

Once you have set up the symbols in hleOfxQuotes (Yahoo) or in ft.com (UK users), you can test the operation so far. Depending on the source, there are one or two steps:

ft.com users

Press the 'Download this Portfolio in CSV Format' button on the ft.com web page. It will be found under the portfolio, usually on the performance page. When you get the dialog prompt, save the file to your PC.



Next, go to hleOfxQuotes and click on the 'ft.com' tab, followed by the 'Import CSV File' option.

You should see the bottom half of the screen fill up with the prices. You should check that these are correct and contain all of the quotes you expect. If they do not, check that you've set everything up in ft.com properly and then follow the steps again.

Yahoo users

Click on the 'Yahoo' tab in hleOfxQuotes and then 'Update Prices'.

You should see the bottom half of the screen fill up with the prices. You should check that these are correct and contain all of the quotes you expect. If they do not, check that you've set everything up in the box above and try again.

Fund prices

Money only allows 15 characters to be in a symbol. You may find that some are longer than this, as they include an exchange or country code on the end. When importing these in the next steps, they will not work.

The person who wrote hleOfxQuotes has worked out a way around that, and that is to change the symbol after download but before importing into Money. It is called the mapper.csv (much more information can be found here) and simple use information is as follows:

• Create a file called mapper.csv and put in the following line: MSMoneySymbol,QuotesSourceSymbol,IsMutualFund,IsOptions at the top.
• Add a line for each fund as follows: ShortFundSymbol,SymbolFromOnlineProvider,TRUE,FALSE, where the 'ShortFundSymbol' is one to be used in Money which is less than or equal to 15 characters and the 'SymbolFromOnlineProvider' is the one which has been used to download the quote. Keep the True and False as specified. An example would be GB00B0C3HC70,GB00B0C3HC70:GBX,TRUE,FALSE - use of this will convert GB00B0C3HC70:GBX to GB00B0C3HC70

I recommend creating a mapper file for all fund prices, so that you can set the IsMutualFund parameter.

You can download my example mapper file with the correct settings from here.

Creating the OFX file for Money to read

Once you've tested the above, you will be ready to do to try an import into Money. Before you do this, I recommend you make a backup of your Money file - you are going to change some things in it and if you need to start again, it will be safer.

If you've backed up and are ready for the first time, still within hleOfxQuotes, click on 'Import to MSMoney'. If you have multiple files, you'll need to use the Save OFX option - contact me if you need walking through that.

After clicking the first time (you will only see this once), you will see the dialog box below. You should select the 'Watch Account' you created earlier in the drop down box. This will then know where to match your symbols.

Matching symbols

Money may require you to match the stocks and funds one time only. This will be indicated by the screen below.



If this occurs, ensure you click the UPPER option and then find your existing investment. If you do not do this, the prices you will eventually download will not update your existing investments. If you do not have the investment, then choose the LOWER option.

Processing the statement

You will be presented with a screen asking you to review your investment holdings. Note that there will be small share fractions for Money 2005 users. These can be ignored. Click Next and then Finish.

You will now find that the prices in your main investment accounts have been updated with the current prices.

Multiple investment currencies

The one main issue that I have seen with the methods described above are with investments which are not in the currency of the account you are importing into. I have put together some information on this in article 652.

Post import setup

Some pecularities may occur when using this method. As the import into Money 2005 uses fractional shares, you may find that reports show these. If this is the case, remove the watch account from the report. You will also see the fractional investment purchases - if you delete any of these, you will lose the price stored in Money for that day. This may not be a problem, but is worth noting.

Repeating the steps

Once you've got the operation going, the process for updating becomes a lot simpler. It is as follows:

1. (ft.com users) Export the CSV file from the ft.com portfolio screen
2. (ft.com users) Run hleOfxQuotes and press the 'ft.com' tab. Press Import button
3. (Yahoo users) Run hleOfxQuotes and press the 'Yahoo' tab. Press the Update Prices button
4. (all users) Press the 'Import to MS Money' button
5. In Money, process the statement by clicking on the account name and then 'next' and 'finish'

Thanks to Christopher for help in preparing this article