Imporing intraday data from Excel

[hendrix]

Hi,

I'm trying to open some intraday futures data into eviews. The data is 5-minute interval with an 45 minute break from 1715-1800.

Code: Select all

DATE TIME 01.01.2009 5 01.01.2009 10 01.01.2009 15 01.01.2009 20 01.01.2009 25 ... 01.01.2009 1710 01.01.2009 1715 01.01.2009 1805 ... 01.01.2009 2359

The DATE coloumn is formatted as 'date' and TIME as 'general number' in Excel. I can't get eviews to recognize the format. I have also tried formatting TIME as 'time' with hh:mm as the format. Any ideas of what kind of structure to use when importing?

I'm using Eviews 7.1 and Excel 2010.

[EViews Gareth]

Could you post the Excel file?

[hendrix]

I'm not at my work pc at the moment so i don't have access to the original file, but this is a similar type of structure. Each day contains 279 5-min intervals.

[EViews Gareth]

The problem you're going to have is with the "23:59" end point of the day. EViews will believe you have minute-by-minute data, with lots of missing observations (i.e. 01,02,03,04,06,07,08 etc...). What you really want is EViews to believe you have 5 minutely data, but it will never believe that as long as you have that "23:59".

Apart from that though, you can create a valid date/time series with the following command:

series datetime = date + @makedate(@floor(time/100), @mod(time,100),"hhmi")

[hendrix]

Okei, thank you. I will try some sort of workaround for the 23:59 interval. Maybe if I do a find and replace of all the 2359 values to just 0 in Excel before the import. Will eviews then understand that 0 refers to 00:00?

[EViews Gareth]

It won't automatically pick up the structure, but in two lines you can get EViews happy:

Code: Select all

series datetime = date + @makedate(@floor(time/100), @mod(time,100),"hhmi") pagestruct @date(datetime)

[hendrix]

Thank you! I will try this tomorrow and report back

[hendrix]

Works perfect Gareth, one more question:

"smpl 1/28/2009 3/18/2009" returns observations for 1/28/2009 thorugh 3/18/2009. Is it possible to create a sample containing only spesific dates? "smpl if @date= 1/28/2009 and @date=3/18/2009" seems logical but doesn't work. I need it to create dummies on the spesific dates.

[EViews Gareth]

Smpl 1/28/2009 1/28/2009 3/10/2009 3/10/2009

Or something similar

[hendrix]

Of course

[hendrix]

What if I want to sample all except specific dates? I have around 100 days I want to exclude from the sample.

[tchaithonov]

Just create a dummy variable indicating which obs you need, and then smpl @all if varx = 1.

[EViews Gareth]

Tchaithonov's suggestion is a good one. You can also use @date, along the lines of:

Code: Select all

smpl if @date<@dateval("2009/10/03") and @date>@dateval("2009/11/03")

which would exclude all dates between 3rd October 2009 and 3rd November 2009.

[hendrix]

Thanx, works perfect.

[hendrix]

series datetime = date + @makedate(@floor(time/100), @mod(time,100),"hhmi")

Hi again,

Do you have a similar command to get eviews to recognize 1 minute data (see att.)?