Simon Benninga. Financial Modelling 3-rd edition

Подождите немного. Документ загружается.

Information from the Web

41.1 Overview

This chapter discusses the acquisition and updating of data from the Web

to Excel. Our purpose is to illustrate a variety of techniques that can be

used to capture data from a Web page and include them into an Excel

spreadsheet.

Topics include the following:

•

Copy-paste techniques for static data acquisition

•

Dynamic data acquisition—that is, data acquisition that updates

automatically

•

VBA programming of dynamic data acquisition

41.2 Copy and Paste as a Simple Data-Acquisition Technique

By far the most common data Web acquisition task faced by most Excel

users is to copy and paste data from a Web page into Excel. In many

cases simple copy-paste will sufﬁ ce to acquire data from the Web and

put it into Excel. Suppose, for example, that we want to put the marked

data of the following Yahoo price data into Excel (see appendix 2 of

Chapter 2 for how to get to similar data):

1030 Chapter 41

The various possibilities that we will examine tend to depend on a

combination of browser and your computer’s setup. In this chapter

Microsoft Internet Explorer 7 (MSIE) and Mozilla Firefox 2 (MF), which

currently dominate the browser market, will be used to demonstrate the

differences between Internet browsers.

1031 Information from the Web

41.2.1 Using Edit|Paste in Excel

In Excel, using Edit|Paste is the simplest possibility, and it may well work.

Here’s what happened to us, using Microsoft’s Windows Internet

Explorer:

The table from the Web was imported into our Excel, though the format-

ting does not exactly match our default fonts (no big deal!). Note the

smart tag to the side of the import. Clicking on this smart tag gives the

following:

1032 Chapter 41

Clicking on Match Destination Formatting reformats the import to the

original formatting of the sheet. We will have more to say about this

smart tag later on.

The preceding was in Internet Explorer. In contrast, when we used Firefox,

Copy|Paste gave us all the data as one long line of text in one cell:

One workaround for Yahoo data involves selecting the table including

the header; notice that “prices” is selected.

Here is the result of a straightforward Copy|Paste into Excel when using

Firefox:

1033 Information from the Web

41.2.2 Using Edit|Paste Special

In most cases Edit|Paste Special gives you more control and options. We

paste using Edit|Paste Special and then use Text as an import option. If

the data are pasted into Firefox, the result looks like this:

Excel imported each value into a separate cell and differentiated between

text, dates, and numbers. If, however, the data are pasted into Internet

Explorer, the result looks like this:

1034 Chapter 41

Excel has imported the text as lines, and these need to be separated in

order to be useful. All is not lost, however: Click on the smart tag (which

was indicated with an arrow) and choose Use Text Import Wizard:

This option will take you to the Excel Text Import Wizard, which will

walk you through a process that in the end will give you the columnar

format of the numbers. The next illustration presents the ﬁ rst window

from the text wizard—we trust you’ll ﬁ gure out how to proceed:

1035 Information from the Web

Here is the result (notice that the word “Close*” that was part of the

header for column G got separated into its own column):

41.3 Dynamic Web Queries

The copy-paste operations illustrated in the previous section are static:

once copied the data do not change to reﬂ ect changes in the data stored

on the Web page from which they came. In this section we illustrate the

beginnings of dynamic Web queries—links between Excel and the data

source that can be updated automatically.

If the data on the Web are updated frequently and we want the updates

to be reﬂ ected in our worksheet, we can use a Web query. Web queries

work on some but not all the data we can see on the Web.

Suppose we

need information about the Dow Jones Industrial Average index; one

place to get the information is the Web address (or URL) linking to

Yahoo, http://ﬁ nance.yahoo.com/q?s=DJI. Here is what the relevant part

of the Yahoo Web site looks like:

1. In technical terms, the data generation should be done on the Web server and not on

the client computer.

1036 Chapter 41

The way Microsoft envisaged the creation of Web queries involves the

use of a wizard. From the Data menu select Import External Data and

then New Web Query.

Once you have clicked on New Web Query, a window onto your Web

home page opens.

1037 Information from the Web

We now change the address in the address bar to the location of the

document we want to load. In this instance the document is at http://

ﬁ nance.yahoo.com/q?s=^DJI. Once we click Go or press Enter, we

should be presented with a view of the data and some yellow arrows

pointing to parts of it. If the window is not sufﬁ ciently large, we can drag

its corner to change its size.