4. MSN MoneyCentral Investor Stock Quotes Excel

CHAPTER 6 Excel

More Skills 14 Use External Data Links to Update Data

You can use web queries to insert data from web pages into your Excel worksheet and then manipulate it.

When you use an external link to update data, you can configure the link so that the data updates automatically or manually, allowing you to keep the data in your workbook as current as needed.

To complete this document, you will need the following file: e06_Stocks You will save your document as: Lastname_Firstname_e06_Stocks 1. Start Excel, and then open the student data file e06_Stocks. Save the file in your Excel

Chapter 6 folder with the name Lastname_Firstname_e06_Stocks Add the file name in both worksheets' left footers. Select cell A1, and then return to Normal view. 2. Display the Web Query worksheet, and then select cell A1. 3. Display the Data tab, and in the Get External Data group, click Existing Connections. 4. In the Existing Connections dialog box, verify that the Show box displays All Connections, and then click MSN MoneyCentral Investor Stock Quotes. Compare your screen with Figure 1, and then click Open.

Existing Connections dialog box

All Connections selected

MSN MoneyCentral Investor Stock

Quotes selected

Work with Advanced Functions and Formulas | Microsoft Excel Chapter 6 From Skills for Success with Microsoft? Excel 2010 Comprehensive

Figure 1

More Skills: SKILL 14 | Page 1 of 6 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

5. In the Import Data dialog box, verify that the Existing worksheet option button is selected and that the Existing worksheet box displays the text =$A$1. Compare your screen with Figure 2, and then click OK.

Existing worksheet option button

Location set to $A$1

Figure 2

Work with Advanced Functions and Formulas | Microsoft Excel Chapter 6 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: SKILL 14 | Page 2 of 6 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

6. In the displayed Enter Parameter Value dialog box, type CCL, HD, JCP, BBY, MAT, GOOG, MSFT

You are adding stock symbols telling the application which stocks to look for when MSN MoneyCentral Investor is queried.

7. Select the Use this value/reference for future refreshes check box. Compare your screen with Figure 3.

The Use this value/reference for future refreshes check box is selected to store the values that have been entered so that they do not have to be typed the next time the same query is run.

Check box selected

Figure 3

Work with Advanced Functions and Formulas | Microsoft Excel Chapter 6 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: SKILL 14 | Page 3 of 6 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

8. Click OK. Wait a moment, and then compare your screen with Figure 4. The values that display on your screen will likely be different than the ones displayed in the images because the stock market is dynamic and the values change often. If you are not connected to the Internet, you cannot complete the rest of this skill. The stock quotes from MSN Money are inserted into the worksheet.

Current stock data

Figure 4

Work with Advanced Functions and Formulas | Microsoft Excel Chapter 6 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: SKILL 14 | Page 4 of 6 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

9. Copy the range D4:D10, and then display the Current Value worksheet.

10. Select cell D5. On the Home tab, in the Clipboard group, click the Paste button arrow, and then under Other Paste Options, click Paste Link . Compare your screen with Figure 5.

Values pasted as links

Figure 5

Work with Advanced Functions and Formulas | Microsoft Excel Chapter 6 From Skills for Success with Microsoft? Excel 2010 Comprehensive

More Skills: SKILL 14 | Page 5 of 6 Copyright ? 2011 by Pearson Education Inc. publishing as Prentice Hall. All rights reserved.

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download