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:


 Find us on Facebook  |   Follow us on Twitter  |   View JofA videos


Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.