Wednesday, July 13, 2016

All About Excel

Microsoft Excel is still used by many users and this post will quickly go over all of the different ways you can share and move data between EViews and Excel.

Native Excel File Support

EViews offers direct Excel file read and write capability. If you have data in an existing Excel spreadsheet and you wish to use it in an EViews workfile, simply drag and drop the Excel file onto an EViews workfile to start the import (see IMPORT command and Importing Data in our User's Guide) or drop it onto an empty area in the EViews frame window to create a new workfile (see WFOPEN command).

At the end of the import, you also have the option to link the data back to the source spreadsheet. This will allow you to easily refresh the data in the workfile, whenever the source Excel data has changed (see WFREFRESH).

By default, EViews will try to read in objects by column and will look for a single header row for the object names. In addition, EViews can transpose the data before import if your objects are defined in rows instead.

In the other direction, you can save EViews workfiles directly to an Excel file by going to
File –> Save As, then selecting the proper Excel type in the Save as type dropdown (see WFSAVE command and Exporting Data in our User's Guide).

Note: Reading the newer Excel .XLSX file format was added in EViews 7. Saving in .XLSX format was added in EViews 8.

Using the EViews Excel Add-In

EViews also offers an Excel Add-In that can be used within Excel to read and link to EViews data residing in EViews file formats. The Excel add-in is installed by default with each EViews installation and can be seen in Excel's ADD-INS ribbon tab.

In the EViews group box, clicking on Get Data will allow you to select an EViews source file. Once selected, you'll be presented with a list of objects from that file.

Once you've selected the objects you want, you can click Import & Link to not only read in the data to your spreadsheet but also have it linked back to the source. This will allow you to refresh the data in your Excel file whenever the EViews data has been updated (see The Excel Add-In in our User's Guide for more details).

As a side note, our Excel add-in actually performs its work by making use of Excel's built-in support for OLEDB data sources. We created a read-only EViews OLEDB provider for this purpose. If you're familiar with using OLEDB providers with Excel, you can bypass our add-in altogether and just use our OLEDB provider directly (see Microsoft's documentation on Connect OLE DB data to your workbook). For more technical details on using our OLEDB provider, please see our whitepaper (PDF).

Object Linking and Embedding (OLE) Support

EViews also supports Microsoft's Object Linking and Embedding (OLE) technology for various EViews elements such as data tables and graphs.

In the past, you could always copy an EViews graph to the clipboard and then paste it into Excel as a static picture. But if you wanted to update that image (maybe to change line colors or even the graph type), you would have to redo the entire copy and paste operation from the beginning.

Now with OLE support, you can paste the graph as an EViews object instead of a static image, which saves the actual EViews graph object (along with all relevant data) directly into your spreadsheet. This embedded object now exists separately from the workfile and represents a snapshot of the graph at that point in time, similar to a static image. But unlike a static image, you can double-click the image to open it in EViews and change its attributes, which will be reflected automatically in your spreadsheet.

You can also go one step further and paste the EViews graph as a linked OLE object instead. As a linked object, Excel will keep track of where the object originally came from and will allow you to quickly update the object from the source workfile upon request. This means any changes made to the object in the source workfile (perhaps by another EViews user) can be reflected in your spreadsheet easily and quickly.

Note: EViews OLE objects can be used in other Office applications that support it, including Microsoft Word and PowerPoint.

For more details on using EViews OLE, please see our recent OLE blog post.

For details on using OLE with Microsoft Office, see their documentation on Create, edit, and control OLE objects.

Using Excel Macros (VBA)

For the ultimate in control and flexibility, EViews also offers a COM automation interface that can be used by the Excel macro language (VBA) to talk directly to EViews. With it, you can write an Excel macro to send and retrieve data and perform almost any EViews task via EViews commands.

For instance, you can write an Excel macro that:
  1. launches a new instance of EViews
  2. sends Excel data from a cell range to series objects in a new EViews workfile
  3. run various EViews commands on the data
  4. retrieve EViews results back into an Excel cell range
all while EViews remains hidden from view.

For Excel VBA examples and more details on using our COM Automation interface, please see our whitepaper (PDF).

For a quick introduction to writing Excel VBA macros, see Microsoft's documentation on Getting Started with VBA.

No comments:

Post a Comment