Stock quotes import into Excel

I use Microsoft Excel to maintain my portfolio positions in a spreadsheet. One convenient feature about using Excel is that there is a free plug-in available that allows you to download the latest stock and mutual fund quotes directly from the web into the spreadsheet. So you do not have to spend the trouble to key in the latest stock or mutual fund prices when you want to check your latest asset allocation.

2006-10-19-msstockquote.png

The stock quote download plug-in is distributed free from the Microsoft website. You can use the following search keywords “excel stock quote download” from the Microsoft website main page to locate the files. For Excel 2002/2003, here is the link to the file.

Here is how the stock quote toolbar looks like in Excel after you install the plug-in:

2006-10-19-msstockquotetoolbar.png

To insert the latest price of a fund, for example VTSMX, enter the following into a cell:

=MSNStockQuote(“VTSMX”,”Last Price”,”US”)

You can also reference the name of a fund using another cell. For example if cell A1 contains VTSMX, the following will also give the latest price of the fund:

=MSNStockQuote(A1,”Last Price”,”US”)

You can have multiple instances of the above code to download the prices of as many stocks or funds as needed. Once you have modified the spreadsheet with the MSNStockQuote commands, you will be able to view your latest portfolio asset allocation with the click of a button. 🙂

5 Comments

  1. choozm

    Interesting! I am also working on such solution. Can the MSNStockQuote plug-in get quotes for stocks in other countries (Singapore, Hong Kong, etc.)?

    Reply
  2. indexfundfan (Post author)

    I haven’t tried inserting quotes from other countries. But from my experience, it seems that if the symbol is accepted on MS’s Moneycentral website, it will also work in the Excel quote.

    For example, I was able to insert the latest foreign exchange rate between USD and SGD using /USDSGD (or /SGDUSD for the inverse). So you can search for the symbol on Moneycentral first and then try it in Excel.

    Let me know if it works.

    Reply
  3. Pingback: The Mamak Stall Investor

  4. Pingback: indexfundfan @ indextown » Blog Archive » Tools for tracking my portfolio

  5. John

    you can also use Financial Link for Excel to load updating stock quotes, financial statements, estimates, price histories into Excel. Easy-to-use formulas to load the data and also wizards to help you creating the formulas.

    More detailed information here:

    http://www.xlconsulting.net/flink.php

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

*
To prove you're a person (not a spam script), type the security word shown in the picture. Click on the picture to hear an audio file of the word.
Anti-spam image