Stock Tracking Dashboard and Live Stock Price Quotes Using Excel




Nearly everybody I know uses Excel for tracking the returns in the stock market. Not many however know that Excel can be used for online stock tracking and getting live price information from stock exchanges like NYSE and Nasdaq as well. Here’s how.

portfolio-tracker-in-excel

Establishing a connection to the MSN server for Stock Quotes

Click on the data tab in the Excel ribbon (in Excel 2007 and later). In Excel 2003 and earlier, the same can be accessed using the “Data” -> “Import External Data” -> “Import Data …” option from the menu.

stock-tracking-in-excel-establishing-connection

Choose MSN MoneyCentral Investor Stock Quotes.

stock-tracking-in-excel-choosing-a-source

In the dialog box that appears, enter the cell address in which you would like the data to appear.

entering-cell-address

Once this part is done, you would need to enter the stock symbols for the stocks you would like to track in the next dialog box.

entering-stock-symbols

Hey prestro, we have live stock quotes appearing on the screen. Let’s call this the “stock grid”.

Here’s a short video on how this can be done. (This being my second attempt at creating videos on youtube, bear with me.)

Here’s how the final stock grid will look like.
stock-tracking-in-excel-quotes

Stock Tracking Dashboard

Now that we have established a connection with the server, creating a complete stock tracking dashboard is just a short hop away. The first step is to right click anywhere on the output data range and select “Parameters” option. Check the third radio button and enter a cell value from which you would like to pick the values. Ensure that you provide only a single row or a single column. However there’s a bit of a catch here. In order to enable the automatic refresh when the cell values change, the application will restrict the values here to only one cell. Which means if we want to enable fresh stock quotes to be fetched when a fresh value is entered, only one cell address can be provided. You can however enter multiple stock symbols in this single cell (Ex. A1 contains “goog, msft, ba”)

parameters

Once we know how to have the stock quotes refresh on updating this cell, we move onto creating placeholders for the stock tracking dashboard. While we place the individual stock symbols in the successive rows, we combine all of them (comma separated) into a single cell which in turn will be used to pass on those parameters to the stock price query and finally to the server.

generating-the-formulas

All the other cells in the dashboard get their values from the “stock grid” that we created earlier. In order to ensure that proper formatting is preserved, you may want to place the stock grid to the bottom and right of the stock dashboard. With a few nifty IF formulas, we ensure that if a symbol has not been entered, the cells remain blank and don’t error out. Hide the columns that you don’t want to show to the users.

So that’s about it. Open the excel workbook, enter any valid symbol for a stock that you would like to track in the placeholder and press enter. Wait for a split second and let Excel do the rest for you.

You can download a sample worksheet with an example of stock tracking dashboard using excel here or click on the button below:

stock-tacking-dashboard-using-excel


Excel Formula, Excel Chart, Excel Macro, Excel VBA, Pivot Table Excel, Excel Dashboard

What Do You Think ?


XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>


Comments and Trackbacks

  1. Scott Simenauer wrote:

    Hi,

    Thanks very much. I am having a bit of trouble with the IF function in column A. When I type in the stock, the function doesn’t pick it up uness I type a comma after it. This is the case even though the function puts a comma in automatically.

    Can you please help?

    Scott

  2. David wrote:

    I used a range in the parameter (eg =Sheet1!$B$5:$B$50) and it works fine. So I have a column of stock symbols in Column B and it retrieves the quotes. No need for , or groupimg symbols

  3. Kenny Marcum wrote:

    Is it possible to have more than 65 stocks in this sheet? I keep getting an error message when I try to upload more than 65 stock symbols.

  4. stock symbols wrote:

    Hi everybody……..

    I hope that you will get the best online stock symbols of the universe from here.besides, here you will get special
    offer by select any the best online stock symbols.for more details please visit here

    Thank you

  5. Simon wrote:

    If MSN money doesn’t work for you any more, then try the spreadsheet and VBA function here. Works for me

  6. cheap car insurance wrote:

    Get as many people involved as possible, but have a relatively small committee charged with making decisions. This will ensure that action will be taken, rather than endlessly debated.

  7. http://www./ wrote:

    Kudos to you! I hadn’t thought of that!

  8. free wrote:

    I used to be suggested this web site by my cousin.
    I am not positive whether or not this put up is written by way of
    him as nobody else recognise such exact about my difficulty.
    You are wonderful! Thank you!

  1. Live Stock Market Quotes Excel | Stock Market Futures wrote:

    […] Stock Tracking Dashboard and Live Stock Price Quotes … – Nearly everybody I know uses Excel for tracking the returns in the stock market. Not many however know that Excel can be used for online stock tracking and getting … […]

  2. Excel Dashboard For Stocks | My Stock Market Futures wrote:

    […] Stock Tracking Dashboard and Live Stock … – Excel & VBA – Nearly everybody I know uses Excel for tracking the returns in the stock market. Not many however know that Excel can be used for online stock tracking and getting … […]

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES