Importing data from excel, large amount of NA

[martingale]

I tried first with a single excel file. Because I got an error message i tried to split the excel file. But the error message was the same both ways I tried.

[EViews Gareth]

I'm afraid without giving us more information, there is little we can do to help.

[martingale]

I see, I hope somebody else can

[EViews Glenn]

Do you still have the single excel file? Why don't you post that one and then describe what happens when you try to import.

[EViews Gareth]

I see, I hope somebody else can

Or, you could provide exact details on what it is you are trying to do, and why it isn't working.

[martingale]

Glenn, what I tried with the single Excel file is pretty much the same except that steps (i) and (vi) are redundant:

ii) choose unstructured/undated date
iii) write in the first date which is 12/29/2006
iv) import from Excel file
v) get the message that 99 observations (i.e. all in the sample) will be discarded unless 99 observations are resized, choose OK

vii) choose Proc - Structure/Resize current page, then:
- work file structure type: Dated - specified by date series;
- date series: name of the object that contains the date series
viii) then I get the message "illegal panel structure dateid is nested within the previous dimensions of the panel structure"

The files I posted have only the first 100 observations, the full data set has over 270000 observations.

OSEAX with Dates to Eviews Technical Support, First 100 Observations.xlsx

(16.6 KiB) Downloaded 760 times

Another problem is that the last column ("un.returns") is not read as numbers but as characters.

[martingale]

Glenn, I tried again and this time I got further. Numerical data was imported correctly from Excel to EViews but dates were still imported as characters. I tried to choose date format during import, but then the dates changed to "NA".

The process went as follows:
- choose import from Excel
- choose unstructured/undated date
- fill in the first date in the route "range of observations" which is 12/29/2006
- import from Excel file (the total number of observations is 271920)
- get the message that "271919 observations (i.e. all in the sample but the first one) will be discarded unless 271919 observations are resized". I choose OK
- choose "Proc - Structure/Resize current page"
- choose "Work file structure type: Dated - specified by date series"
- choose "Date series", then write the name of the object that contains the date series
- then I get the message "Indices do not uniquely identify observations, 270523 duplicate date(s) found. Create CELLID series to identify observations?": choose yes
- then I get the message "Adding CELLID as the inner index will create a 2 dimension unbalanced undated panel. Are you sure you want to continue?": choose yes
- then Eviews creates two extra series called "cellid" and "dateid"

Superficially it looks like I managed to import the data from Excel to Eviews, but I am concerned with the last message of a "2 dimension unbalanced undated panel". Does this message mean that there are errors in my data set?

[EViews Glenn]

Your sample Excel file has a single date for all of the observations. Is this true for the full workbook?

If so, you can't use date as the identifier since it doesn't identify the observations. My guess is that the column labeled IDENTIFIER is your identifier. If this is the case, I think you want an "Undated with ID series" structured workfile with IDENTIFIER used as the identifier.

If not true, then you probably want to structure as a panel with DATE as the date and IDENTIFIER as the identifier. But that's a guess since I can't see the original file.

[martingale]

Thanx for the input.

"270523 duplicate date(s) found" out of 271920

My guess is that EViews creates the series "cellid" and "dateid" to identify each observation. But why is not enough with one series?

Does the EView message "Adding CELLID as the inner index will create a 2 dimension unbalanced undated panel" relates to these two series?

The structure in "cellid" is:
2007-12-29 - 1....211 1......211
and so on

The structure in "dateid" is:
2006-12-29 - 1...211 2006-12-29
and so on

[EViews Gareth]

Again, what are you trying to do?

Are you trying to create a panel?

Are you trying to create a simple time-series workfile? If so, do you realise that you have the same date repeated many times, which means your date is an invalid observation identifier?

[martingale]

yes i realize that but it is not my question, never mind

[EViews Glenn]

You write

"270523 duplicate date(s) found" out of 271920

My guess is that EViews create the series "cellid" and "dateid" to identife each observation. But why is not enough with one series?

The first sentence is the answer to your question. The date series is not enough to identify each observation because there are duplicate dates: 270523 out of 271920 to be exact. Identifiers can't have duplicates.

[martingale]

right, but either "cellid" or "dateid" which are the series created på eviews, not the series imported from excel, identifies uniquely each observation, so why two series, that is both "cellid" and "dateid" are needed?

[EViews Gareth]

Because you're creating a panel. Panels have two identifiers.

[martingale]

thanx!

what is the message on "unbalanced undated panel" about?