A Refreshing Change in Your Portfolio
Q: I’ve heard that you can create a stock portfolio in Excel that can be updated automatically via the Internet. Can you please tell me if this true, and, if so, how this is accomplished?
A: You’ve heard correctly. Excel provides a built-in query tool that can link your worksheet to 20-minute delayed stock quote information. This query tool uses Open Database Connectivity (ODBC) to establish a permanent connection between the source data and your Excel workbook. Once the link is created, your portfolio can be updated by clicking the Refresh button. To build an ODBC-based stock portfolio, make sure that your computer is connected to the Internet and create a basic portfolio layout, an example of which is shown below:
Be sure to include valid ticker symbols for each of the stocks, mutual funds, ETFs and other investments you own. Next, create the query as follows:
1. Launch the Import Data dialog box:
a. In Excel 2007 and 2010, from the Data tab, select Existing Connections from the Get External Data Group to launch the Existing Connections dialog box, then select MSN MoneyCentral Investor Stock Quotes and click Open.
b. In Excel 2003, from the Data menu select Import External Data, Import Data to launch the Select Data Source dialog box, then select MSN MoneyCentral Investor Stock Quotes and click Open.
2. In the Import Data dialog box, select the option for New worksheet as shown below, and click OK to display the Enter Parameter Value dialog box (see below).
In the Enter Parameter Value dialog box, click the Cell Chooser icon and highlight the cells in your portfolio containing the stock ticker symbols as shown below. Check the box labeled Use this value/reference for future refreshes and click OK.
This action will retrieve and display an array of stock quote information from the MSN MoneyCentral Investor database in a new Excel worksheet. This information includes the name of each investment and 20-minute delayed price per share data, among other information. An example of the resulting stock quote query is shown below.
Return to your portfolio worksheet and fill in the missing name and stock price information using simple reference formulas to refer to the investment names and stock price information on the query worksheet. For example, select cell D3 in your portfolio worksheet, and enter the formula =QueryWorksheet!D4 (assuming the worksheet containing your query is named “QueryWorksheet”). Thereafter, each time you click the Refresh All button, Excel will update the query results, which in turn will update your portfolio.
Hint 1: If you plan to print your portfolio report, insert the formula =NOW() in cell A1 (or any blank cell) to ensure that the date and time your portfolio is printed appears on the report.
Hint 2: If you prefer to use the Refresh button (that is, instead of the Refresh All button), you must first select a cell (or cells) within the query results array.
Hint 3: If you later add more line items to your portfolio, you can change the query parameters by right-clicking on the query and selecting Parameters from the dropdown menu.
More from the JofA:
