Microsoft Office Tutorials and References
In Depth Information
In the Lab
In the Lab
Lab 3: Returning Real-Time Stock Quotes to the Stock Portfolio Worksheet
Problem: You belong to the Learn-N-Earn Stock Club, which has been investing in the stock market
for the past several years. As vice-president of the club, you maintain a summary of the club’s stock
market investments in an Excel workbook (Figure 6–96a on the next page). Each day you go through
the Business section of the newspaper and manually update the current prices in column G to deter-
mine the value of the club’s equities. You recently heard about the Web query capabilities of Excel and
have decided to use them to update the club’s stock portfolio automatically.
Instructions: Perform the following steps to have Web queries automatically update the current price
in column G and the major indices in the range B12:B15 of Figure 6–96a.
1. Start Excel. Open the workbook Lab 6-3 Learn-N-Earn Stock Club Portfolio Basics from the
Data Files for Students and then save the workbook using the ﬁ le name, Lab 6-3 Learn-N-Earn
Stock Club Portfolio. See the inside back cover of this topic for instructions for downloading the
Data Files for Students or see your instructor for information on accessing the ﬁ les required in this
book. After reviewing the worksheet, you should notice that it lacks current prices in column G and
the major indices in the range B12:B15.
2. Click Sheet2 and then select cell A1. Click the Data tab on the Ribbon and then click the
Existing Connections button. When Excel displays the Existing Connections dialog box, double-
click MSN MoneyCentral Investor Stock Quotes. When Excel displays the Import Data dialog
box, click the OK button. When Excel displays the Enter Parameter Value dialog box, click the
Learn-N-Earn Portfolio sheet tab at the bottom of the screen and drag through the range B3:B10.
Click the ‘Use this value/reference for future refreshes’ check box to select it. The Enter Parameter
Value dialog box should display as shown in Figure 6–96b on the next page. Click the OK button.
The Web query should return a worksheet with up-to-date stock quotes similar to the one shown
in Figure 6–96c on the next page. Rename the Sheet2 tab Stock Quotes.
3. Click the Learn-N-Earn Portfolio tab. Click cell G3. Type = (equal sign). Click the Stock Quotes
tab. Click cell D4. Press the ENTER key. Use the ﬁ ll handle to copy cell G3 on the Learn-N-Earn
Portfolio sheet to the range G4:G10. You now should have current prices for the stock portfolio that
are the same as the last prices on the Stock Quotes worksheet in column D.
4. Click Sheet3 and then select cell A1. If necessary, click the Data tab on the Ribbon and then click
Existing Connections. When Excel displays the Existing Connections dialog box, double-click
MSN Money Central Investor Major Indices. When Excel displays the Import Data dialog box,