Importing tick-by-tick data in Eviews 7

[mermet88]

Hello!

I am trying to import a file that has tick-by-tick data for 5 years of EUR/USD prices. The original file is csv, with "YYYY-MM-DD HH:MM:SS, bid, ask".

My problem is that I cannot explain to eviews the date format...
If I say "dated - specified by date series", frequency autodetect or intraday, I get "Indicies do not uniquely identify observations. 1458177 duplicate date(s) found. Create CELLID series to identify observations?"
I assume it says "duplicate dates" because there are several seconds within the same day, so I was wondering how I could overcome this problem?

Sample of the date column:
2002-12-01 19:27:18
2002-12-01 19:34:46
2002-12-01 19:38:36
2002-12-01 19:46:17
2002-12-01 19:46:27
2002-12-01 19:56:11
2002-12-01 20:18:06
2002-12-01 20:18:43
2002-12-01 20:18:59
2002-12-01 20:19:39
2002-12-01 20:31:35
2002-12-01 20:37:48

The file is almost 500MB so I cannot attach it... Any ideas? please..

Thank you very much

Eviews 7

[mermet88]

What I need in this series is just a few days here and there. Would it be possible to create a blank series of 2 days (say 2003-01-15 and 2003-01-16) with frequency 1 minute (for example) then fill the series with information I got from the original series?

I'm very sorry if it's getting complicated...

[EViews Gareth]

If you have multiple observations with exactly the same time-stamp, you cannot structure your workfile as a dated workfile.

[mermet88]

Gareth,

Thank you for your reply. This was embarrassingly obvious. I shortened the set to 500 observations and the dating happened fine...

I didn't think duplicates would occur in this set though.. May I just ask if there is a command to search and add a second to any duplicates in the dates? They are ordered in increasing order if that helps.. (I know I shouldn't change the data like this but I will not use it at second frequency but rather 5 minutes or even lower)

Thank you very much!

[EViews Gareth]

Unfortunately there is no such command (nor can I think of an easy way to do it programmatically).

[mermet88]

Ok, thank you very much!

[startz]

Maybe some variant on

Code: Select all

seconds = @recode(seconds=seconds(-1),seconds+1,seconds)

??

[mermet88]

Maybe some variant on

Code: Select all

seconds = @recode(seconds=seconds(-1),seconds+1,seconds)

??

Yes, exactly like that! I have been looking around and changed it to

Code: Select all

date2 = @recode(date=date(-1), @dateadd(date, 1, "ss"), date)

Had to run it a couple of times and finished with 110k duplicates. But then the number of duplicates increased again (probably because some dates caught up with the date of the next point...).

Hence it could have worked, but of course it does not here... I think I should simply delete the points but I have to do it for each series, i.e. if in series date entries 1000 and 1001 are the same (say 2002-12-12 12:12:00), then I should delete entry 1001 in date, series01, and series02.
Is it possible to do that?
(I still know it is butchery of data but I don't have much choice...)

[startz]

EViews dates have a resolution of a millisecond, so there may be a way to do what you want by adding a small enough fraction that you don't bump up to the next observation.

[mermet88]

That's a good idea, I have tried it and it indeed works. My last problem is that I have to repeat the step a certain amount of times until eviews tells me there are no more duplicates and I don't know how many times that is (it appears it is possible that more than two points may have the same date).

I will search the manual for how to do a loop or a different way of doing this. Thank you very much

[startz]

You may need to be careful about the following. Suppose three dates in a row are identical. The program will change the second one. Observations 1 and 3 will still be the same but that won't be caught.

[EViews Gareth]

Perhaps a more pertinent question would be - do you actually need this data to be in a dated workfile?

[startz]

Perhaps a more pertinent question would be - do you actually need this data to be in a dated workfile?

What Gareth said.

But if you do need a dated workfile you might want to compare s to s(1) rather than s(-1). I think this should make the changes in one pass.

[mermet88]

Yes I was thinking about the 3 dates in a row or even more, I'll have to think about how to handle that! If I get a straight forward script I'll post it in case someone has the same problem.

And yes I do need the data dated. Sometimes I wish I could find another way but I do need it dated. But thank you for your concern