converting a minute chart

Hi guys, i have a table with 6 columns and 4 million rows. Column1 shows the date and column 2 the time. Column 3 the opening price, column4 the max price, column5 the min price and column6 the closing price. The whole table presents a one minute chart. I want to convert this to a two minute chart in the following manner: two rows have to be combined(row1 and row2). Column 1,2,3 should have the values of row1. Column4 should contain the max value of row1 and row2. Column 5 should have the min value of row1 and row2. Column6 should have theclosing value of row2. I got this formula but the output is a cell array. I want the calculation to be carried out within the matrix and the output should be displayed horizontally adjacent to each other in the same matrix

    n = 2:100;
    for jj = numel(n):-1:1
        n1 = ceil((1:size(x,1))'/n(jj));
        out{jj} = [x(1:n(jj):end,1:3), accumarray(n1,x(:,4),[],@min),...
                   accumarray(n1,x(:,5),[],@max),x(n(jj)-1:n(jj):end,6)];
    end

Answer 1

[Geoff Hayes]

AA - suppose I just have a smaller matrix/table of 100 rows and 6 columns, created as

 oneMinTbl = randi(255,100,6);

Presumably, the two minute table/chart will have half as many rows as you are combining rows 1 and 2, rows 3 and 4, etc. (Is this correct?)

Your code has to accomplish four things, with the first being to keep the columns 1 through 3 from each of the odd rows which will correspond to the beginning of each two minute period. We can do this as follows

 oneMinTbl(1:2:end-1,1:3)  % the first three columns from each odd row (less the last)

Note how we start at one and increment by two - so we choose rows 1, 3, 5, etc.

Next, the fourth column is the maximum value of the fourth column in rows 1 and 2 (or 3 and 4, etc.). We do this as

 max([oneMinTbl(1:2:end-1,4) oneMinTbl(2:2:end,4)],[],2)

where we create a matrix of the fourth column (shifted accordingly) and get the maximum from each row. The temporary matrix has two columns with the first being made up from the column four data of the odd rows, and the second column being made up from the column four data of the even rows.

We can do the same for the fifth column as

 min([oneMinTbl(1:2:end-1,5) oneMinTbl(2:2:end,5)],[],2)

And finally, the last column is just the closing price (sixth column) from each of the even rows

 oneMinTbl(2:2:end,6)

Combining all of this together should create your two minute table

 twoMinTable = [oneMinTbl(1:2:end-1,1:3)                                ...
                max([oneMinTbl(1:2:end-1,4) oneMinTbl(2:2:end,4)],[],2) ... 
                min([oneMinTbl(1:2:end-1,5) oneMinTbl(2:2:end,5)],[],2) ...
                oneMinTbl(2:2:end,6)];

Try the above and see what happens!