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.

Answer 1

[Andrei Bobrov]

x - your array (4e6 x 6)

n = ceil((1:size(x,1))'/2);
out = [x(1:2:end,1:3), accumarray(n,x(:,4),[],@min),...
               accumarray(n,x(:,5),[],@max),x(2:2:end,6)];

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

[AA]

i get the following error:

out = [out(1:2:end,1:3), accumarray(n,out(:,4),[],@min),...
               accumarray(n,out(:,5),[],@max),out(2:2:end,6)]
Undefined function 'accumarray' for input arguments of type 'table'.

[AA]

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(1:n(jj):end,6)];
end

Answer 2

[SK]

N = 4000000;

C = reshape(Table(:, 4), [2,N/2]);
Cmax = transpose(max(C, 1));

C = reshape(Table(:, 5), [2,N/2]);
Cmin = transpose(min(C, 1));

TableNew = [ Table(1:2:N, 1:3), Cmax, Cmin, Table(2:2:N, 6) ];

Edited to correct an error.

[AA]

Thanks how can i create a loop so that i get a cell array with 2 min chart,3 min chart,4 min chart up to 100 min

[SK]

wherever there is 2, replace with 3 or 4 or 5 etc. Put Code in function like:

function TableNew = MakeNewTable(Table, m)
  %....above code with 2 replaced by m.
end

NewTable = cell(99,1);
for m = 2 : 100
  NewTable{m-1} = MakeNewTable(Table, m);
end

Watch out for excess memory consumption. You may want to clear temporary variables once you finish with them.

Also, N must be divisible by m. If not you need to remove the last few rows whenever required.

[AA]

the reshaping table command does not work. It gives me the following error:

C = reshape(out(:, 4), [2,n/2]);
Error using table/reshape (line 397)
Undefined function 'reshape' for input arguments of type 'table'.

[SK]

Oh, you are using the Matlab table object? This is something introduced by Mathworks very recently (2014a or maybe 2013b). I have never actually used it.

I thought your 'Table' was a matrix. reshape() works on matrices (and more generally on arrays).

Anyway there are two functions I see in the documentation for Table: table2array and array2table. So you could first convert it to an array using table2array, perform the required operations and then convert it back using array2table.

[AA]

i get problems with memory when i use cell array. is there a way to do the reshaping with the tables. that saves memory

[AA]

I still get an error message. it says it wants whole integers:

N =

       3755915

>> D= reshape(C(:, 4), [2,N/2])
Error using reshape
Size arguments must be real integers.

[SK]

N must be divisible by 2 (or 3 or 4 etc .. as the case may be). I mentioned that in my earlier post. For an m-minute chart you will need to delete the last rem(N,k) rows.

DO you have memory problems trying to reshape just one table, or only when you try to loop m = 2 : 100 ?

[AA]

Thanks it worked but i get a new error for this line: Cmax = transpose(max(C, 1)). It says that the command max for cell is unknown

[AA]

Cmax = transpose(max(d, 1));
Undefined function 'max' for input arguments of type 'cell'.

[SK]

But C should be a matrix not a cell.

function TableNew = MakeNewTable(Table, m)

    N = size(Table, 1);
    N = N - rem(N,m);
    Table = Table(1:N, :);

    C = reshape(Table(:, 4), [m,N/m]);
    Cmax = transpose(max(C, 1));

    C = reshape(Table(:, 5), [m,N/m]);
    Cmin = transpose(min(C, 1));

    TableNew = [ Table(1:m:end, 1:3), Cmax, Cmin, Table(m:m:end, 6) ];

end

% Insert code here to convert Table from table type object to matrix.

NewTable = cell(99,1);
for m = 2 : 100
  NewTable{m-1} = MakeNewTable(Table, m);
  % Insert code here to convert NewTable{m-1} from matrix to table type.
end

[AA]

thanks