Page 1 of 2
Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 2:39 am
by 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.
Re: Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 7:57 am
by EViews Gareth
Could you post the Excel file?
Re: Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 8:36 am
by 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.
Re: Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 10:50 am
by 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")
Re: Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 11:02 am
by 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?
Re: Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 11:05 am
by 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)
Re: Imporing intraday data from Excel
Posted: Mon Apr 12, 2010 11:09 am
by hendrix
Thank you! I will try this tomorrow and report back
Re: Imporing intraday data from Excel
Posted: Wed Apr 14, 2010 4:53 am
by 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.
Re: Imporing intraday data from Excel
Posted: Wed Apr 14, 2010 7:19 am
by EViews Gareth
Smpl 1/28/2009 1/28/2009 3/10/2009 3/10/2009
Or something similar
Re: Imporing intraday data from Excel
Posted: Wed Apr 14, 2010 10:54 am
by hendrix
Of course
Re: Imporing intraday data from Excel
Posted: Fri Apr 30, 2010 5:39 am
by hendrix
What if I want to sample all except specific dates? I have around 100 days I want to exclude from the sample.
Re: Imporing intraday data from Excel
Posted: Fri Apr 30, 2010 7:25 am
by tchaithonov
Just create a dummy variable indicating which obs you need, and then smpl @all if varx = 1.
Re: Imporing intraday data from Excel
Posted: Fri Apr 30, 2010 8:09 am
by 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.
Re: Imporing intraday data from Excel
Posted: Fri Apr 30, 2010 8:16 am
by hendrix
Thanx, works perfect.
Re: Imporing intraday data from Excel
Posted: Tue May 11, 2010 4:37 am
by 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.)?
