How to update a high frequency dataset using a timestamp var

[neophytosk]

Title: How to update an unstructured high frequency dataset using a timestamp variable

Dear all,

I have the following question.

Background

I have a timeseries dataset regarding fx trades with different counterparties as below, location of exchange etc (which are not included in the example). Key variables are the categorical variable 'counterparty type' indicating which is the originating side of a trade (who is trading), and some numerical variables as the exchange rate(price) by which the transaction is settled. The 'time' variable is of the format 23/05/2013 15:30:31 , with respectively date, and day time to the second.

My exaple dataset set looks as below:

time Currency counterparty type x-rate
23.05.2013 9:00:01 eur/usd bank 1.36
... 10:03:25 eur/usd corporate 1.35
... 11:05:10m eur/usd non bank financial 1.34
...12:30:45 eur/usd government 1.35
...13:35:10 eur/usd corporate 1.35


Question

I imported the dataset as an unstructed dataset. Could you tell me how to update it using the time variable and convert it into a dated-frequency dataset which takes into account the level of detail of the date/time variable?
Many thanks for your feedback.
Neo

[EViews Gareth]

I think you'll need to provide the file.

[neophytosk]

Hi,

I provided an example file. The date variable is 'last modification date'

Best

Neo

[EViews Gareth]

You have observations with the same value of last_modified_date. Therefore you cannot use it as a date series to structure the workfile by.

[neophytosk]

These are trades which occur at the same exact time......i.e two customers trade at the same time with the bank

What do you suggest as an alternative to structure the dataset? Is there a way to overcome this?

Let's first go with the easiest way, say i delete those trades which occur exactly at the same time(loss of informatíon will be limited), how do I then convert the dataset to dated structure?

[EViews Gareth]

Probably best off just leaving it as unstructured.

[neophytosk]

I need to structure the dataset - this post was created for this reason as I want to convert it from high to low frequency based on the time variable.

e.g sum up observations at 1 min or 10 minute intervals

Is this possible without structuring the dataset?

[EViews Gareth]

Yes, you can perform a general match merge, using your date/time variables as the IDs.

[neophytosk]

this means that I have to make the assumption that each observation based on ID equals to some predefined time interval, e.g 1min right? from ID1 to ID5 the d(time)=4mins if we make the assumption that all observations are equally distributed in time?
let me know if this is the case and potentially share if possible a link on how to do the mergirg
how can I structure the dataset as discussed before? i will try the above merge proposal but i think i will not able to run my analysis in such a way.....

thnx

[EViews Gareth]

No, you don't have to assume the observations are equally distributed. You just have to decide how you want to handle contractions of the observations with the same time.

[neophytosk]

Could you give me a code to execute this? I need test first your suggestion and see if this will give me the results I would like to get.

Thanks

[EViews Gareth]

Just copy from the first page, and then paste special into the second. User Guide I has quite detailed instructions on performing general match merge copying.

[neophytosk]

yup I 've tried that but I get the reply 'variables you are trying to merge are of the wrong type'

I have befoehand added a cell ID and date ID series and used those as 'source IDs'

[EViews Gareth]

Post your workfile.

[neophytosk]

I attached an example workfile

I would like to convert from high to lower frequency using the date as ID. For example to sum up the group variables for a specific time frequency e.g 5min intervals. Rgd those trades which appear at the same time I propose to:
-use deal ID as an additional source variable since some deals are dublicated in the dataset
As mentioned before I tried the general merge approach and I get the error message:
“some of the specified series do not yet exist or are of the wrong type”

thanks for your support