MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• The MSN Money Central Web site provides analyst ratings (buy, sell, and hold) for stocks. How can I import this information into Excel?

• Is there a way I can download current stock prices into Excel?

We all know that the World Wide Web contains useful data on just about everything. However, we can’t really do any sort of analysis of this data while it’s on the Web. We need to import the data into Microsoft Office Excel. Excel 2007 makes importing data from the Web very easy. After you find the URL where the data you want is located, copy the URL to the Windows Clipboard. Then open an Excel worksheet and on the Data tab, in the Get External Data group, click From Web. When the New Web Query dialog box appears, paste the URL into it, and then click Go. The Web page appears, and you can select the data you want to import into Excel. Our first example will show you how to implement this easy procedure.

• The MSN Money Central Web site provides analyst ratings (buy, sell, and hold) for stocks. How can I import this information into Excel?

• The URL http://moneycentral.msn.com/investor/invsub/analyst/recomnd.asp?Symbol=MSFT contains information about analysts’ forecasts for the future price of Microsoft stock. For example, in Figure 34-1, we find that on March 27, 2007, 9 of 18 analysts rated Microsoft a strong buy, whereas a month before, 10 of 19 analysts rated Microsoft a strong buy. It would be useful to import this data into Excel so that we could better understand the data.

  Figure 34-1: Analyst forecast for Microsoft in March 2007. The Web contains a lot of useful data, but it isn’t easy to analyze on a Web site.

• To import the data into Excel, copy the URL and then open a blank worksheet. Now display the Data tab of the Ribbon, and in the Get External Data group, click From Web. When the New Web Query dialog box appears, press Ctrl+V to paste the URL into the Address box, and then click Go. The New Web Query dialog box displays the data shown in Figure 34-2.

  Figure 34-2: New Web Query dialog box after choosing a URL

• You now click the arrow that points to the data you want to download and import. In our case, we would click the arrow directly to the left of Analyst Ratings. The arrow changes to a check mark. After clicking Import, you’ll see the Import Data dialog box, in which you indicate to Excel where you want to place the data. I chose cell C2 of the current worksheet for this example. After clicking OK, the analysts’ ratings are imported into the worksheet. (See Figure 34-3.) Note that the numbers are beautifully separated into different columns!

  Figure 34-3: Analyst forecasts in March 2007 imported into Excel

• You can easily set your query to refresh, or update in any desired fashion. Just right-click anywhere in the query results, select Data Range Properties, and change the Refresh Control settings. As shown in Figure 34-4, we selected our query to refresh every minute and automatically refresh when the file is reopened.

  Figure 34-4: Refresh settings for Web query

• To edit this query, simply right-click anywhere within the query output and select Edit Query. By clicking the options button, you can control the fomatting of your query results. Off course, when you run this Web query again, you’ll probably see different information because analysts are constantly changing their views of future stock price for Microsoft.

• Is there a way I can download current stock prices into Excel?

• Excel comes with a built-in Web query that can be used to download current information about stocks. This Web query is a dynamic Web query, which means that you can set the query to update stock information as it changes in real time. All you have to do is select the data you want to update and on the Data tab, in the Connections group, click Refresh All. Let’s see how to download real-time stock information about Microsoft (ticker symbol MSFT) and General Motors (ticker symbol GM) into Excel.

• Open or create a worksheet in Excel. Display the Data tab of the Ribbon, and in the Get External Data group, double-click Existing Connections. Now select the Web query named MSN MoneyCentral Investor Stock Quotes. Click Open, and then use the Import Data dialog box to indicate where you want to place the data in the worksheet. Click OK, and then fill in the Enter Parameter Value dialog box as shown in Figure 34-5. The query downloads information about Microsoft and GM into Excel. Figure 34-6 (and the file Msftgmquotes.xlsx) shows a sample of the downloaded information. Checking the options shown in Figure 34-5 on the next page ensures that the worksheet changes to reflect the most current information.

  Figure 34-5: Use the Enter Parameter Value dialog box to designate the stocks for which you want the dynamic Web query to download information.

  Figure 34-6: Downloaded information about Microsoft and General Motors stocks

• Excel also ships with Web queries designed to download currency exchange rates and information about major stock indexes. These Web queries are static, however, and will not update information in real time unless you rerun the query.

• Of course, you can create your own Web queries. Creating Web queries is beyond the scope of this book. See Chapter 4 of Data Analysis for Managers with Microsoft Excel by S. Christian Albright, Wayne L. Winston, and Christopher Zappe (Duxbury Press, 2004) for an introduction to creating Web queries.