MSMoneyQuotes - FT.com Quotes and CSV download


General Usage

MSMoneyQuotes can be used to import quotes from FT.com. This is useful if you have securities in your Money file that MSN Money doesn't recognize/support.
You can setup a Portfolio on FT.com and then manually download quotes for those securites into a comma-separated values (CSV) file. You can then use MSMoneyQuotes's -i option to import quotes from this CSV file and update the quotes in Money.
Example Usage: 

MSMoneyQuotes.exe -v -i c:\MoneyFiles\Test_2014-12-05_0640PM.csv

Note: You could use the -i option to import quotes from any CSV file provided it conforms to the expected format.

Details

Details on the -i import option:

  1. When using the -i option, MSMoneyQuotes imports quotes from the specified CSV file. It does NOT fetch online quotes. Think of -i as an alternative mode to get quotes. In other words, for a single run of MSMoneyQuotes, it can either get quotes from the CSV file (-i option is used) or get quotes online (-i option is not used).
  2. The -a options is ignored when the -i option is used. This is because -a is intended for online quotes, and as stated above the -i "mode" doesn't get online quotes
  3. It parses out and uses the "pure symbol" from both the CSV file and the Money file when trying to match securities from the CSV to securities in the Money file. The "pure symbol" is the "GB0003874798" part of this symbol "GB:GB0003874798:GBX". It strips off the country code prefix and the stock exchange suffix.
  4. It matches securities based on the "pure symbol" for a variety of reasons including:
    1. Because the country code prefix (e.g. "GB:", "US:") is a MSN Money specific concept and not something FT.com and other quote providers follow.
    2. Because FT.com symbols include a stock exchange suffix (e.g. ":LSE", ":GBX", ":GBP") that Money doesn't follow or understand. And also, having the stock exchange prefix may cause the symbol to be too long for Money to handle. (e.g. GB0003874798:GBX)
    3. To normalize and simplify symbol comparison. Turn on the "-v" verbose option during the import to see the "pure symbol" for each security in the CSV file
  5. It uses the CSV file's "last update time" as the timestamp to use for the "last update" time for each security it updates in Money.
  6. The CSV file may contain the following 13 columns in this order: Name, Symbol, Last, Currency, Today's Price Change, Open, High, Low, 52 Week High, 52 Week Low, Volume, Market Cap, Shares Outstanding
  7. MSMoneyQuotes expects the column order in the CSV file to match above, but the CSV file doesn’t have to contain all the columns. So if you only have the first 5 columns (e.g. Name, Symbol, Last, Currency, Today’s Price Change), my program will still be able to import the data.
  8. Columns 14 and higher are simply ignored. For example, the FT.com CSV file automatically includes some Currency-related columns here that are ignored by the import
  9. The first row of the CSV file should contain headings and is automatically skipped
  10. Example CSV file downloaded from FT.com: Example.csv

Details on the -t import option:

Example Usage: 
MSMoneyQuotes.exe -v -i c:\MoneyFiles\MyImportFile.csv -t
  1. The -t option was added in version 2.3 to support importing historical quotes
  2. If -t is specified, then MSMoneyQuotes will import an additional timestamp column from the csv file and use it for the time of last sale (instead of using the CSV file's "last update time" as the timestamp)
  3. The csv file should contain an additional column immediately following the "Shares Outstanding" column.
  4. The dates listed in this column should use the following format: year-month-day. For example, January 16th, 2018 should be formatted like: 2018-01-16.
  5. Example CSV file that can be used with the -t option: HistoricalExample.csv

Tips

Tips on how to best use the -i import option:

  1. Use the -i option to suplement online quotes -- Use it to update quotes for those symbols that don't work on MSN Money. Remember you can turn on/off online quotes for a given security using Portfolio Manager’s "Update Prices…" –> "Pick quotes to download…" selections.
  2. Download and save the FT.com CSV file using the same path and filename everytime. Then you can setup a shortcut to have MSMoneyQuotes execute the import by pointing to the same CSV file location.
  3. You may need to change symbols in Money to get them to match the FT.com symbols. MSN Money and FT.com have different "symbology", meaning they may use different symbols for the same security. If you have to update a symbol in Money, it may be worth cutting & pasting the previous symbol into the "Comment" box in case you need to revert later.
  4. I do not control quotes on MSN Money or FT.com, so don't blame me if a security isn't supported or has inconsistent/wrong quotes between the two!


How to Download quotes as a CSV file from FT.com

Here are the basic steps to download a CSV file from FT.com that MSMoneyQuotes will be able to import.

  1. Setup a "Portfolio" or "Watchlist" on FT.com with the securities you need quotes for.
  2. If you'd like to get quotes for indexes (for example, the FTSE index), then I recommend you use a Watchlist since FT.com won't allow you to add indexes to the Portfolio.
  3. Go to your FT.com Portfolio/Watchlist and click "Export data" - See Figure #1
  4. Click "Create new view" (or Edit current view" if you already have a view setup) and setup the columns for the Equities, ETFs/ETCs, and Funds tabs to match Figures #2, Figures #3 Figure #4.
  5. Click "Export holding data" to download the CSV file with quotes for the securities in your Portfolio/Watchlist
  6. Use MSMoneyQuotes -i option to import the quotes from the CSV file you just downloaded

Figure #1 - Export Data link



Figure #2 - Equities Columns



Figure #3 - ETFs/ETCs Columns



Figure #4 - Funds Columns