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.
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.
Choose MSN MoneyCentral Investor Stock Quotes.
In the dialog box that appears, enter the cell address in which you would like the data to appear.
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.
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.)
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”)
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.
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: