How to Stata 11 to Read Excel

Importing Data

Earlier, nosotros described workfile creation tools that permit you to open data from foreign sources into a new workfile ( "Creating a Workfile by Reading from a Foreign Data Source"). This is nearly likely the easiest way to move data from foreign files and database sources such as ODBC into EViews and we recommend it as your showtime approach, just y'all should note that these tools are expressly designed for creating new workfiles.

Alternatively, you may wish to import data into an existing workfile, perhaps into existing serial in the workfile—y'all may, for instance, wish to read a portion of an Excel file into a subset of observations in a serial or group of series. We term the reading of information into existing workfiles and/or series importing series information to distinguish it from the cosmos of entirely new workfiles and series.

There are several methods for importing series data into EViews. In the remainder of this discussion, we outline the basics of data import from spreadsheet, text file, or printed formats, into serial and group objects. Note that nosotros omit, for the moment, discussion of importing data into EViews matrix, vector and puddle objects, and discussion of EViews and foreign databases:

Inbound Information

For pocket-sized datasets in printed form, you may wish to enter the data past typing at the keyboard.

• Your first pace is to open up a temporary spreadsheet window in which you volition enter the data. Choose from the primary menu to open an untitled group window:

• The next step is to create and name the serial. First click in one case on the up arrow in the scroll bar to display the 2nd characterization on the left-manus cavalcade. The row of cells adjacent to the 2nd label is where you will enter and edit serial names.

Click once in the cell next to the second label, and enter your first serial proper noun. Hither we take typed "income" in the edit window (the name in the cell changes as we type in the edit window). Printing RETURN. If you lot enter the proper noun of an existing series, the series data volition be brought into the group.

• EViews will prompt you to specify a series type for the column. You may select a numeric series, numeric series containing date values, or an blastoff series. When you lot click on , EViews volition create a numeric or blastoff serial and will apply formatting information that will aid you in viewing your information.

• You should repeat this procedure in subsequent columns for each additional series. If y'all decide you lot desire to rename one of your series, simply select the cell containing the series proper name, edit the proper name in the edit window, and so press RETURN. EViews volition prompt you to confirm the series rename.

• To enter the data, click on the appropriate cell and blazon the number or text. Pressing Render after entering the prison cell value will move yous to the next cell. If y'all prefer, you can use the cursor keys to navigate the spreadsheet.

• When you are finished entering information, shut the group window. If y'all wish, you can first name the untitled grouping by clicking on the button. Otherwise, if yous do non wish to keep the group, answer when EViews asks you to confirm the deletion.

Copying-and-Pasting

The Windows clipboard is a handy fashion to move pocket-sized amounts of data within EViews and between EViews and other software applications. It is a natural tool for importing these types of data from Excel and other Windows applications that support Windows copy-and-paste.

Copying from Windows Applications

The following discussion involves an example using an Excel spreadsheet, only the basic principles utilise for other Windows applications.

Suppose you have bond yield and interest rate information in an Excel spreadsheet that yous would like to bring into EViews.

Open the spreadsheet in Excel. Your start footstep is to highlight the cells to be imported into EViews. Since the column headings YIELD and Interest will be used as EViews variable names, yous should highlight them too. Since EViews understands dated information, and we are going to create a monthly workfile, you do not need to copy the engagement column. Instead, click on the column label B and drag to the column characterization C. The ii columns of the spreadsheet will be highlighted.

Select to copy the highlighted data to the clipboard.

Pasting into New Series

Outset EViews and create a new, or load an existing, monthly workfile containing the dates in the Excel spreadsheet (in our case, 1953m01 through 1994m11). Make certain that the sample is set to include the same observations that you have copied onto the clipboard.

Select . Notation that the spreadsheet opens in edit mode so there is no need to click the button.

Here, nosotros accept created a monthly workfile with a range from 1953:1 to 1999:12. The first row of the EViews spreadsheet is labeled 1953:01. Since nosotros are pasting in the serial names equally well, y'all should click on the up pointer in the scroll bar to make room for the serial names.

Identify the cursor in the upper-left jail cell, just to the right of the second label. Then select from the principal carte (not in the toolbar). The group spreadsheet volition at present contain the data from the clipboard.

EViews automatically analyzes the data on the clipboard to determine the nigh likely series blazon. If, for case, your series contains text that tin can ever exist interpreted as a number, EViews will create a numeric series. Hither, the numeric series YIELD and Involvement have been created in the workfile.

If the numbers in the series may all exist interpreted every bit date values, or if the data are all cord representations of dates, EViews will create a numeric series formatted to display dates.

If you paste a proper name corresponding to an object that already exists in the workfile, EViews will detect the side by side available name by appending an integer to the series name. For instance, if SER already exists in the workfile, pasting the name "SER" will create a series SER01.

You may now close the grouping window and delete the untitled group without losing the two serial.

Pasting into Existing Series

You can import data from the clipboard into an existing EViews series or group spreadsheet by using in the same mode. In that location are only a few additional bug to consider:

• To paste several series, you lot volition first open up a group window containing the existing series. The easiest way to practise this is to click on , and so type the series names in the order they appear on the clipboard. Alternatively, you lot tin create an untitled grouping by selecting the first series, property down the -key and click select each subsequent series (in order), and then double clicking to open up.

• Make certain that the group window is showing the sample range that corresponds to the data on the clipboard.

• Side by side, make certain that the group window is in edit mode. If not in edit mode, press the button to toggle to edit mode. Identify the cursor in the target cell, and select from the primary menu.

• Finally, click on to render to protected way.

• If y'all are pasting into a single series y'all volition need to make sure that the series window is in edit mode, and that the series is viewed in a single cavalcade. If the series is in multiple columns, push on the button. Then the data as usual, and click on to protect the data.

Linking pasted data

When pasting information into EViews, either as a new workfile, or into an existing workfile, EViews will enquire if the new series objects should be linked dorsum to the source. Click to marking the new series objects every bit externally linked. Linked objects tin be refreshed with updated data when the workfile is next opened, or on demand by right-clicking on the workfile window and selecting or by clicking on in the main menu to display the link management dialog.

When you side by side open your existing workfile containing linked data, EViews volition prompt you to refresh the data, and optionally, to display a comparing of the data earlier and after the refresh:

Importing Data

EViews provides easy-to-use tools for importing data into an existing workfile, matching observations betwixt the source and destination every bit required. The source data may be available in any of a number of data formats, from ane of the EViews supported strange file formats (i.e., Microsoft Excel, ASCII text, SPSS, SAS portable, Stata, etc.), to an EViews format workfile.

Opening the Source File

To apply the import tools, commencement, make certain that you lot accept an open workfile to receive the contents of the data import and that the workfile window is agile, then click on or click on the menu of the workfile and select In either case, EViews will brandish a standard file dialog.

Adjacent, type the name of the file you wish to read (with full path data, if appropriate) or select a file blazon, navigate to the directory containing the file, and double click on the name. Click on the button to keep.

(Alternately, y'all may only elevate-and-drop the source file onto the open workfile window.)

EViews will open the file (automatically determining the file type, if possible, or interpreting the file every bit ASCII text, if not). Side by side, EViews may display a set of dialogs prompting you lot for information most the structure of the data you are reading. These dialogs are identical to those encountered when you open up a file every bit a new workfile (see "Creating a Workfile by Reading from a Foreign Data Source"). Fill up out the dialogs accordingly, clicking on at each stride.

Once you take described the file and data, EViews will display a dialog prompting you for details on how you lot wish to perform the import of data:

There are two parts to the import specification: the choice of import method and the setting of various import options.

In the top-left portion of the dialog is the dropdown, which controls how the source data will exist read into the existing workfile. The expanse to the right of the dropdown will change to show options associated with the currently selected method.

Directly below the dropdown is the section, which offers admission to settings for renaming series and specifying frequency conversion methods. Additionally, depending on the precise form of your import, you lot may be presented with additional dialogs for resizing the destination workfile or choosing how to handle import proper name conflict.

The bottom portion of the dialog consists of an import preview display, which shows a sample of destination observations forth with corresponding data from the source file. The preview will change along with your selected import method specification.

We discuss all of these settings and choices below.

Import Method

The key question in importing data is how to relate observations in the source file to observations in the destination workfile. The dropdown at the top left of the dialog should be used to specify the desired method. Depending upon the structure of the destination workfile, yous volition be asked to select between four or five import methods: (merely if destination workfile is dated), , , , and .

Each method has a variety of associated settings, so we consider each in plow.

Dated Read

Y'all may perform a dated read if the destination workfile is a dated (or console dated) workfile. In a dated read, observations in the source file are matched past date to observations in the current workfile page, with frequency conversion performed if necessary. The concepts underlying dated reads are identical to those in creating links betwixt two dated workfile pages, as outlined in "Linking by appointment with frequency conversion".

To perform a dated read yous must first specify the date construction of the source workfile. Appropriately, when you select in the dropdown, the upper portion of the dialog volition modify to show you date settings for the data to be imported.

When the source file is first opened for dated read, EViews will endeavour to place a engagement series in the file (i.e. a column of data containing dates), and to decide the date frequency of those dates. If a source date serial is located, the dropdown volition exist gear up to and the source name will be entered in the edit field. If no date serial is identified, the import file data structure volition exist gear up to friction match the destination.

Altering the specified date structure changes the nature of the import since it affects the matching of observations betwixt the source and destination. To aid you lot in visualizing the effect of this setting, the left-hand column of the data preview display updates with changes in the structure to show yous the current date matching settings:

Hither, we see that the source observation with OBS value 1959Q1 is matched with the workfile destination observation 1959M1 and that the source observation with OBS=1959Q2 is matched with the destination observation 1959M4. Note that there are no matches for the source observation OBS=1958Q4 since the destination workfile begins in 1959.

If we were instead importing into an almanac file, the bottom portion of the brandish would change to

depicting the matching of quarterly observations in the source file with almanac observations in the destination workfile.

In both of these examples, importing of the information into the page requires frequency conversion. To control the frequency conversion method, you may click on the on the left-manus side of the page to bring up the conversion settings.

As usual, you may command both the loftier-to-low and the low-to-loftier frequency conversion method. By default, EViews volition employ the method specified in the destination series (or the global defaults, if the destination serial does not however exist). When converting from high-to-low using a specific method such as Average observations, y'all will be given the option of selecting the checkbox and so that no conversion is allowed if there are missing data for a given low frequency menses.

Click on to close the dialog and accept the conversion method.

In most cases, EViews will have correctly identified the appointment serial so that the default settings will require no modify. Click on the button complete the import specification and instruct EViews to begin the import procedure.

EViews will at present ask if the new serial objects should exist linked back to the source. Click to mark the new series objects as externally linked. Linked objects can exist refreshed with updated data when the workfile is next opened, or on demand by correct-clicking on the workfile window and selecting or by clicking on in the main menu to display the link management dialog.

You may, if desired, override the default EViews date specification settings past irresolute the dropdown menu setting to either or , and then using the dialog to specify the data frequency explicitly.

For example, if y'all select in the dropdown, the dialog changes to reflect the new settings:

Notice that EViews no longer shows the appointment serial under , instead prompting you to provide a using the dropdown menu and the edit field. EViews will assume that the source file has sequential observations of the given frequency showtime in the specified engagement.

Since nosotros take not still specified a , the "?"s in the observation preview at the bottom of the dialog point that the ascertainment matching is indeterminate. Once a start date is specified, the display volition be updated to describe matching observations.

If the destination workfile is console structured, you may instead choose in the dropdown.

When is selected, the import structure section of the dialog changes, prompting y'all to specify a and a . If you continue with the import, EViews will perform the engagement and ID matching equally specified.

Matched Read

The method performs a full general match-merge betwixt the data in the source file and the current workfile folio. The concept is outlined in detail in "Linking by general match merging".

To perform a matched read import, change the dropdown to , so specify the and corresponding that will be used equally define observation matches. In many cases, EViews will suggest likely values for the match indices determined past examining the contents of the source file and destination workfile.

Suppose for example, that your destination workfile has data on unemployment rates in each of the European union countries, you might have an identifying series called "State" containing the country names. If you lot have a respective serial in the source file, called "NATION," that as well contains country names, and then you lot would specify "NATION" as the source index, and "COUNTRY" as the destination index. Note that the observations in the source index demand not be in the same club as they are in the destination.

In the simplest case, your index serial will incorporate identical observation identifiers that tin can be used to friction match up the observations in the two files. If the matches are one-to-one or if there are multiple destination matches for every source observation (1-to-many), EViews will just perform the merge in the obvious fashion, repeating source observations for every destination observation if necessary.

If, withal, the matching is many-to-one so that there are multiple source observations for a given destination observation, EViews will contract the source data before performing the match merge. You may control the contraction method using the dropdown.

Suppose, for example, that your destination workfile contains a series Country containing the identifiers for "United mexican states," "Canada," and "Usa", your source file contains U.S. state level information. Yous should enter COUNTRY as your and bespeak that your source should exist matched with the "Usa" destination ascertainment. (In the probable outcome that the source file does not have a Land series, yous tin can create an auto-series by typing "US", with the quotes, in the box.) Note that in this case, the state level data must starting time be contracted (summarized) before it is matched to the "US" observation in the destination. To contract the source data by taking the sum of u.s.a. as a value for "US", y'all would set the to .

The last dialog setting, , should be checked if you want to care for the NA value as a distinct category for purposes of defining matches.

The observation preview in the lesser of the dialog may be used to appraise the validity of your source and destination index specifications.

Sequential Read

A sequential import uses no information on the construction of the source data, information technology just places each observation in the source file into the corresponding observation of the destination workfile. Thus the outset row of the source file will be placed in the first observation of the destination, the 2d row will be placed in the second observation, and so on.

For a sequential read, simply select in the dropdown. There is simply ane setting; yous may specify a destination sample in which to place the data, so that the first observation of the source file will be placed into the beginning observation of the specified sample, the second source ascertainment will be placed in the 2nd sample ascertainment, and and so on.

As before, the ascertainment preview in the bottom of the dialog may exist used to preview the destination for some of the source observations.

Append to Stop

The import method appends each row of the source file to the end of the destination workfile. The existing workfile construction will be removed.

For instance if your destination workfile is an almanac file running from 1990 - 2000, and the source file contains 5 rows of data, the import volition identify the 5 rows of information at the end of the workfile, leaving you lot with an unstructured 16 observation workfile.

Create New Folio

The method creates a new folio in your workfile and reads the source file into that new page. This form of data import is identical selecting in the workfile window or clicking on the tab and selecting For additional detail, see "Creating a Page past Loading a Workfile or Data Source".

Importing Attributes

Support for reading in custom serial attributes (along with the series data) specified as header rows during an import from Excel and text files was introduced in EViews x. For instance, the following Excel spreadsheet has custom attributes for Description, SKU, and State specified in header rows:

When we elevate and drib this file onto EViews, the new Import Wizard dialog appears. On the second page (Pace 2), you may alter how each header row is used:

Past default, EViews will treat every header row every bit a Name field, but you can change the row type for each header by selecting (or specifying a custom value for) the aspect name:

Y'all can now complete the import and verify that all custom attributes have been read in correctly:

Both the wfopen and import commands have been modified to support new values for the "namepos=" argument and a new "colheadnames=" argument to specify individual attribute names for each column. See wfopen for more than details.

Import Options

The sections of the dialog consists of two buttons: and .

Pressing the button brings up a dialog that allows you lot to rename some of the imported series to different names. Renaming the series on import allows you to handle illegal input series names, and to avoid proper name conflicts with existing series.

The button, which brings upwardly a dialog that controls the high-to-depression and low-to-loftier frequency conversion methods, is enabled whenever yous are performing a dated import. See "Dated Read" for discussion.

Import Workfile Resizing

When you lot import data from a source file that contains observations for which at that place are no destination workfile matches, EViews will prompt you to accommodate the data by resizing the destination.

If, for example, your original workfile has observations from 1990M01 to 1994M12 and yous import quarterly data for 1990Q1 to 1996Q4, EViews will determine that there are no destination observations corresponding to the source observations for 1995Q1 to 1996Q4. When you proceed with the import, EViews will brandish a dialog informing you of this fact.

There are two ways to continue. Commencement, you lot may click on to resize the destination workfile, adding 24 monthly observations for 1995 to 1996. The merge will then proceed using all of the information in the source.

Alternately, you may click on to retain the original destination workfile range. In this example, the 8 observations in the source file for 1995Q1 to 1996Q4 will be discarded on import, and volition not announced in the final destination workfile.

Import Proper noun Conflict

If you select an import method that brings source data into the current workfile page, and the source file contains series with the same name as an existing serial, EViews will brandish a dialog asking how you wish to handle the conflict.

Yous may choose to overwrite the existing serial object (which will delete the existing series and supercede it with the source data), to merge the two series, to rename the incoming series, or to abolish the import of the series. Clicking on the push performs the corresponding operation for all cases where there is conflict.

If you choose to the source and destination series, EViews will use the dropdown settings to make up one's mind how to bring together the data:

• will overwrite the destination series values for all observations in the source file, including observations where the source contains an NA.

• volition overwrite the destination series views with those from the source file, excluding observations where the source contains an NA.

• will just overwrite an ascertainment in the destination file if its existing value is an NA.

Note that whatever the merge settings, observations in the destination workfile that are not also in the source file will be unchanged.

Matrix and Table Object Import

The preceding discussion focused on importing data into series or group objects. Like tools are available for importing information straight into a matrix or tabular array object from external data.

baughbels1936.blogspot.com

Source: http://www.eviews.com/help/content/Basedata-Importing_Data.html

Share this post