Importing from Excel and dates

[javiersan]

Hi,

This is a pretty basic question but I still don't understand how EViews operates with Excel files. When I use wfopen to import a excel spreadsheet sometimes I receive a message saying that there is a sharing violation and sometimes the dates are imported fine. If I have multiple excel files open and I use wfopen on one of them, the dates are not loaded well (i.e. range 1-120 for example instead of the dates), if the file is closed sometiimes the dates are loaded and sometimes they are not. Is there any specific rule so that I can make sure the dates are loaded properly in all occasions?

Thanks,

Javier

[EViews Chris]

It's actually a fairly complicated question!

There are a few things going on behind the scenes that may explain what you are seeing.

The first thing you need to know is that EViews actually reads Excel files in two quite different ways. The first way is to read the Excel file directly from disk. The second way is to read the Excel file by talking to a copy of the Excel application currently executing on the machine.

We use the 'direct from disk' approach whenever possible because it's faster and less ambiguous. However, sometimes this will fail because some other process has locked us out of the file. When this happens we take a guess that the reason we can't open the file is because the file is already open inside Excel, and we try 'talking' to Excel to see if we can read the data indirectly from the copy open inside Excel.

This approach greatly reduces the number of 'sharing violation' errors that occur when working with Excel and EViews simultaneously.

There are a number of cases in which this system breaks down. One problem is that if you have multiple copies of Excel running on your machine, then we will not necessarily talk to the copy of Excel that currently has the file open. (When we request a running instance of Excel from the operating system, we don't get to choose which instance we get). In this case we will give up and report to the EViews user that we couldn't open the file. An obvious workaround is to have only one instance of Excel running at a time.

Another (more obscure) thing that can throw us off is if you open a file (typically on a network drive) through two different 'paths' (eg. once using a mapped drive letter and once using a full server name). In this case we may not recognize that the file you are trying to open is the same file that Excel already has open.

One of these two issues probably explains why you sometimes receive a 'sharing violation' error message while sometimes you don't.

To get back to the problem with dates, there are a few extra complication that arise with dates that might explain some of what you're seeing.

If we are correctly recognizing the dates when the file is open in Excel but not when the file is closed, then it is probably related to complications in the Excel file that are stopping us (but not Excel) from recognizing that the column of cells contain dates. Dates in an excel files are simply numbers with a 'date' format, and it can sometimes be hard for us to recognize which formats are date formats and which formats are number formats. (The situation is worse in Excel files written by applications other than Excel itself, which often have errors in the way the format information has been written).

If we are reading the dates OK, but are having trouble recognizing the structure of the dates (for example, we did not recognize that the data had a monthly frequency) then there is something about the column of dates that is tripping up our automatic analysis of date structure. This is a slightly different problem and it's hard to give you a simple answer for why this might fail on any particular file. There's a lot of automatic analysis going on, and sometimes things don't quite work out in the end. (On the other hand, if EViews refuses to give a date structure to a file it can also indicate that the date series is defective in some way that you haven't yet noticed).

I hope that narrows things down a little. If you still can't work out what is going on with a particular file, you should contact EViews tech support (support@eviews.com) and we may be able to give you a more specific answer.

Chris

[javiersan]

Thanks Chris for such a detailed explanation.

Regards,

Javier

[javiersan]

Hi,

I have to ask a similar question to the one I asked before. I have an Excel file that when it is opened EViews reads the data well but when it is closed it does not read the dates properly (dates are for example 39844, 39872, etc.).

I am wondering what should I do so that the dates are read properly when the Excel file is closed (i.e. Jan-09, Feb-09). One of the options is to run a command like

pagestruct(freq=m, start=1/1/2009)

but this assumes that I know that the frequency is monthly and the start date is in Jan 09.

Do you have any other suggestions? The communication between Excel and EViews is quite complex as you mentioned in the earlier post so any help provided would be greatly appreciated.

Regards,

Javier

[EViews Gareth]

Can't really say without seeing the Excel file.