Stock time interval conversion

I have a stock data in one minute interval. I want to convert it to another interval such as 5 minutes. Is there an easy indexing method. Note: data sampling is not always continious you may have some gaps where no data is recorded (example you may have data for minute 1,2 then missing data for minutes 3-7, then data for 8,9,10, etc.) data formate is as follow: TICKER DATE OPEN HIGH LOW CLOSE VOL 1010 732678.4167 67.66000366 67.66000366 67.66000366 67.66000366 3454 1010 732678.4201 67.66000366 67.66000366 67.66000366 67.66000366 626 1010 732678.4208 67.66000366 67.66000366 67.66000366 67.66000366 778 1010 732678.4215 67.98000336 67.98000336 67.98000336 67.98000336 4396 1010 732678.4236 67.69000244 67.69000244 67.69000244 67.69000244 3767 1010 732678.4278 67.69000244 67.69000244 67.69000244 67.69000244 1255 1010 732678.4285 67.69000244 67.69000244 67.69000244 67.69000244 13565 1010 732678.4299 67.69000244 67.69000244 67.66000366 67.66000366 25124 1010 732678.4326 67.66000366 67.66000366 67.27999878 67.66000366 3139 1010 732678.4354 67.66000366 67.66000366 67.66000366 67.66000366 878 1010 732678.4361 67.69000244 67.69000244 67.69000244 67.69000244 1255 1010 732678.4389 67.69000244 67.69000244 67.66000366 67.66000366 878 1010 732678.4396 67.66000366 67.66000366 67.66000366 67.66000366 626 1010 732678.4403 67.66000366 67.66000366 67.66000366 67.66000366 626 1010 732678.4438 67.66000366 67.66000366 67.66000366 67.66000366 9130 1010 732678.4458 67.66000366 67.66000366 67.66000366 67.66000366 11706 1010 732678.4472 67.66000366 67.66000366 67.66000366 67.66000366 853 1010 732678.4493 67.66000366 67.66000366 67.66000366 67.66000366 1003 1010 732678.45 67.43000031 67.43000031 67.43000031 67.43000031 12562 1010 732678.4535 67.43000031 67.43000031 67.43000031 67.43000031 12562 1010 732678.4556 67.47000122 67.47000122 67.47000122 67.47000122 313 1010 732678.4569 67.47000122 67.47000122 67.47000122 67.47000122 313 1010 732678.4576 67.48000336 67.48000336 67.48000336 67.48000336 12587 1010 732678.4597 67.58999634 67.66000366 67.58999634 67.66000366 11053 1010 732678.4632 67.73999786 67.81999969 67.73999786 67.81999969 626 1010 732678.4653 67.66000366 67.66000366 67.66000366 67.66000366 61 1010 732678.4667 67.66000366 67.66000366 67.66000366 67.66000366 815 1010 732678.4688 67.58999634 67.58999634 67.58999634 67.58999634 188 1010 732678.4708 67.58999634 67.58999634 67.58999634 67.58999634 2512 1010 732678.4722 67.58999634 67.58999634 67.58999634 67.58999634 3265 1010 732678.4729 67.48000336 67.48000336 67.48000336 67.48000336 149

regards,

[the cyclist]

The formatting of your data makes it very hard to read. I suggest you include just a few lines of the data, write them out as a MATLAB array, and format the data as "code" using the markup tool.

Answer 1

[Matt Tearle]

Do you want to index or do you want to interpolate? Given that you don't necessarily have data for the values you want (every five minutes), it sounds like you want to interpolate in some way. So:

% read data from file
fid = fopen('ticker.txt','rt');
foo = textscan(fid,repmat('%f',1,7),'headerlines',1);
fclose(fid);
% extract some columns
date = foo{2};
closeprice = foo{6};
% *** DO YOU WANT TO DO THIS???? ***
% round off dates to nearest minute
date = round(date*1440)/1440;
% *** END DO YOU WANT TO DO THIS ***

% set regular time interval
dt = 5/1440;
% interpolate to regular time base
t0 = round(date(1)/dt)*dt;
date_reg = t0:dt:date(end);
cp_reg = interp1(date,closeprice,date_reg,'linear','extrap');

% see results
plot(date,closeprice,'o-',date_reg,cp_reg,'x-')
datetick('x')

See the doc for interp1 for different possible interpolation methods. The line of code enclosed in "DO YOU WANT TO DO THIS" rounds off the dates to the nearest minute. I did this because it looks like your dates are all close to a minute, but not quite. This appears to be due to the finite precision recorded in the data file (ie the dates are to 4 decimal places). If you do a datevec on date, you'll see that the seconds (last column) are all close to 0 (or 60).

[the cyclist]

My answer feels so lazy next to yours.

[SAEED ALGHAMDI]

Thanks for your answer.

But waht I want is to creat a candle chart, so I need all the entries of open,high,low and close to be maped to the 5 minutes.

The open will be the open value of the 1 min, the high is the maximum of the whole period, the low is the lowest of the period and the close is the close of the 5 minute.

Interpolation will provide an average value (an approximation).

Regards,

[the cyclist]

As mentioned in my answer, I think you want to use interp1, but using the "nearest" method to identify which 1-minute time interval is closest to the 5-minute time interval you want. You do NOT need to interpolate the price data themselves.

[Matt Tearle]

Yes, what the cyclist said. Otherwise I don't know how to interpret what you're trying to do. You don't have the data finely spaced enough to get start/end/min/max in each 5-min interval. But your data file already has open/close/low/high, so you can use nearest neighbor interpolation on each of the columns.

@the cyclist: I have to make sure *I* can do it first, before telling someone else to do it! So, at that point, I might as well post my code :)

[SAEED ALGHAMDI]

Hi gents,

I will explain what I want to do.

First the data come in a interval such as 1 mintues. Each mintue has five fields: open,high,low,close and volum.

I want to reconstruct this high dense data into less dense, by grouping a selected number of samples (such as 5 mintus per group) and output one records consist of the five fields(o,h,l,c and v).

The reconstructed fields are calculated values as explained before not just the nearest value to the 5 mintue mark.

Regards,

Answer 2

[the cyclist]

One way to do this is using the function interp1(), using the "nearest" (i.e. nearest neighbor) interpolation. Use the 1-minute-interval data as the known values, and request the output at the 5-minute time intervals.

You can create the 5-minute interval array by using a datenum spacing of 5/(24*60) hours.