15 minutes intervals

Hi everyone!

I have some mat files that look kind of like this:

19.10.09 09:00:16 1BASF.DE 39.45 39.43 39.41

19.10.09 09:00:16 1BASF.DE 39.45 39.44 39.43

19.10.09 09:00:20 1BASF.DE 39.46 39.45 39.43

… … … … … …

19.10.09 09:14:57 1BASF.DE 39.72 39.71 39.7

19.10.09 09:14:58 1BASF.DE 39.72 39.71 39.7

19.10.09 09:15:02 1BASF.DE 39.72 39.71 39.7

19.10.09 09:15:02 1BASF.DE 39.72 39.71 39.7

so the first colum is the date, the second column is the time stamp, the third is the name of the stock and the rest are prices. I want to create 15-minutes intervals that go from 9:00-9:15 am, then 9:15-9:30 am and so on until the end of the day and for these intervals the average price of the stock is taken. Do you have any ideas of how to make this?

I have seen people using datenum and the calculating the intervals , but the problem here is that my date and time stamps are in different columns and I dont know how to do it then.

Also, the timestamp column is a cell. Do you have any ideas that could help me out?

I would really appreciate it :)

Have a nice day!

[Laura Proctor]

Could you elaborate on the data? How are you bringing it into MATLAB? A MAT-file implies that you have variables that you're importing into MATLAB. If that's the case, can you describe the relevant variables (such as their type and size)?

[Lu]

Hi Laura,

Thank you for your comment :). My data was originally a .txt file, but for some reason I never managed to load it on my workspace and work with it. (I dont really know much matlab so I am learning by doing. The .txt file looks like this:

Date Ric TRDPRC_1 SALTIM TRDVOL_1 BID1 BID2 BID3

19.10.09 08:55:02 1BASF.DE 0 : : 0 0 0 0

19.10.09 09:00:16 1BASF.DE 39.45 39.4 39.41

19.10.09 09:00:16 1BASF.DE 39.45 39.44 39.43

19.10.09 09:00:17 1BASF.DE 39.45 39.44 39.43

19.10.09 09:00:17 1BASF.DE 39.46 39.45 39.44

So I converted it into a .mat file by reading the strings and then the doubles, and then put it together in a matrix form.

So now that I have the .mat files, they look like this

19.10.09 09:00:16 1BASF.DE 39.45 39.43 39.41 ...

19.10.09 09:00:17 1BASF.DE 39.45 39.43 39.41 ...

19.10.09 09:00:18 1BASF.DE 39.45 39.43 39.41 ...

19.10.09 09:00:19 1BASF.DE 39.45 39.43 39.41 ...

first row is date, second row is time stamp, third row is stock name, and from row four to row 43 I have the prices of the stocks.

So when I type "whos ", I get the following:

EDU>> whos

Name Size Bytes Class Attributes

bid1 31591x1 6191836 cell

date 31591x1 4296376 cell

finalData 31591x43 100712108 cell

timestamp 31591x1 4296376 cell

I just typed one bid price, since the others are the same type and size.

I hope this can give you a bit more of information and I also hope you can give a hint on how to calculate the 15-minutes time intervals:)

Thank you!!

Answer 1

[Walter Roberson]

Determine the first date of the range. Find the datenum() of midnight on that day. Convert the other values in to datenum() and subtract that base of midnight. Divide the resulting difference by (15*60)/(24*60*60) = 1/(24*60/15) = 1/(24*4) = 1/96 -- so multiply by 96 to do the division. Take the floor() of the result and that vector will be the number of 15 minute bins since midnight on the base date.

Once you have those bin numbers, you can add 1 to each of them and use the result as the subscript for the purposes of accumarray:

accumarray(1+binnum(:), stockprice(:), [], @mean)

[Lu]

Dear Walter,

Thank you for your answer! It is certainly helpful! and i will also try it as soon as I can access the first row (date) and the second row(timestamp) of my dataset, because apparently they are not in a string format and datenum is not working because of that. Do you have any ideas for that?

About the nice tip about finding the datenum() of the midnight on that day, could it also be instead of midnight, the time where the exchange closes (around 17:30 pm)? and then do the substraction that you propose?

Thanks a lot for the accumarray tip! :)

[Walter Roberson]

If you use 17:30 instead, you would have to go back to the previous day's 17:30 -- otherwise the bin numbers for that first day would come out negative.

You could use the time of the _opening_ of the day as your first base, though.

What does matlab report about the class() of the variable you are passing to datenum ?

[Lu]

Dear Walter,

I think it is a cell

EDU>> class(date)

ans =

cell

I dont really know what the problem is :(

[Walter Roberson]

Try class(date{1})

I _suspect_ you have a cell array each member of which contains a cell array that contains one string.

[Lu]

Hi Walter,

This is what I get :

EDU>> class(date{1})

ans =

cell

I tried two things:

1) date=finalData(:,2);

2) da={finalData(:,2)};

and both show the same error message: EDU>> datenum(date,'dd.mm.yy');

??? Error using ==> datenum at 182

DATENUM failed.

Caused by:

Error using ==> datenum at 111

The input to DATENUM was not an array of strings.

Ill keep trying, but if you come up with a better idea of how to make that cell an array of strings let me know :)

Have a nice day :)

[Walter Roberson]

Okay, now class(date{1}{1})

If that shows char and length(date{1}) shows 1, then you have your strings wrapped in one too many layers. I think in that case

vertcat(date{:})

should be the cell array of strings ready to pass in to datenum.

[Lu]

Dear Walter,

Thank you for your answer! The problem was solved by typing this :

datenumber = datenum([date{:}],'dd.mm.yy');

Thanks a lot for your help! I will now try to do the 15 minutes intervals of the data set. Have a nice day :)

Answer 2

[Laura Proctor]

I imagine that the date and timestamps look something like the following varibles.

date = {    '19.10.09'
            '19.10.09' 
            '19.10.09' 
            '19.10.09' }

timestamp = {   '09:00:16' 
                '09:00:17' 
                '09:00:18' 
                '09:00:19' };

You can convert these into a numeric date format. The variable t below contains the date and time together so that each row has the date and time in one number.

dateN = datenum(date,'dd.mm.yy');
timeF = datenum(timestamp,'HH:MM:SS') -   datenum('00:00:00','HH:MM:SS');

t = dateN+timeF;

Here I have calculated the fraction that corresponds to 15 minutes, and then the ngroups variable tells how many groups will be contained in the data. This should hopefully get you started.

t15 = datenum('00:15','HH:MM') - datenum('00:00','HH:MM');

ngroups = ceil((max(t)-min(t))/t15)

[Lu]

Dear Laura,

Thank you so much for your answer! I´m trying it but the problem is when I try to convert date and timestamp into a numeric format, matlab keeps showing me the following message:

??? Error using ==> datenum at 182

DATENUM failed.

Caused by:

Error using ==> datenum at 111

The input to DATENUM was not an array of

strings.

So if I type whos date, this is what I get:

EDU>> whos date

Name Size Bytes Class Attributes

date 31591x1 4296376 cell

and the same for timestamp.

I have been trying different commands to try to make it a string but nothings seems to work, is it because the rows are cells?

How can I convert the date and time column into strings so that I can use datenum?

Thank you for your help! :)

[Walter Roberson]

char(date) should convert it to a char array. However, that should not be needed: if you look at the bottom of datenum()'s documentation, http://www.mathworks.com/help/techdoc/ref/datenum.html

Calling datenum with more than one date string input returns a character array of converted date strings. Pass the multiple date strings in a cell array. All input date strings must use the same format. For example, the following command passes three dates that all use the mm/dd/yyyy format:

datenum({'09/16/2007';'05/14/1996';'11/29/2010'})

ans =

733301

729159

734471

Perhaps you are using a version of MATLAB too old to implement that?

[Lu]

Dear Walter,

Thank you again for your comment! I think I have the 2010a matlab version, so it shouldnt be a problem. But, I dont know if the problem is due to the fact that my dates (dd.mm.yy format) and my timestamps (HH:MM:SS format) are contained in variables called date (<31591x1 cell>) and a variable called timestamp(<31591x1 cell>). So when I type

EDU>> ho=datenum({date},'dd.mm.yy');

??? Error using ==> datenum at 182

DATENUM failed.

Caused by:

Error using ==> datenum at 111

The input to DATENUM was not an array of strings.

and the same if I type ho=datenum({date(:)},'dd.mm.yy'); or

ho=datenum(date(:),'dd.mm.yy');

I´m really sorry to keep asking but I kind of learning by doing matlab and I dont have much knowledge about it.

I hope you can help me a bit more on this :)