How to make 30min intervals from 1min data taking the previous value for NaNs?

Hello,

I have a matrix of type double(below).

It consists of 1min stock prices (StockA, StockB, etc.).

   Year        Mo      Day     Hour    Min    StockA   StockB
    2012  3  22  9  58  NaN  9.98
    2012  3  22  9  59  NaN  9.40
    2012  3  22  10  00  NaN  NaN
    2012  3  22  10  01  NaN  9.59
    2012  3  22  10  02  NaN  NaN
    2012  3  22  10  03  10.03  9.91
    2012  3  22  10  04  NaN  NaN

I would like to aggregate the data in 30min intervals in such a way that if no price is available for exact 09.30,10.00, 10.30 and so on, it takes the last available value before 9.30,10.00, 10.30 and so on column by column.

Could you please advise how can I do that?

I tried to create 30mins intervals separately and use min(abs... function, but it seems not to work for the dates.

[Azzi Abdelmalek]

Is there any missing minutes?

[Ekaterina Serikova]

No, a time range created so that every minute is there.

[Azzi Abdelmalek]

What to do if all values between 10h:30 and 11h are nan

[Ekaterina Serikova]

Then just NaN, because no tradings were during these 30 mins

Answer 1

[Azzi Abdelmalek]

% -----------Example------------------------
v1=datenum('01-01-2016','dd-mm-yyyy');
pas=1/(24*60);
v=datestr(v1:pas:v1+pas*89);
n=size(v,1);
w=randi(10,n,2);
w(randperm(n*2,fix(3*n/2)))=nan;
A=[datevec(v) w];
%------------------------------------
m=size(A,2);
nn=30;
B=permute(reshape(A',m,nn,[]),[2 1 3]);
for k=1:size(B,3)
  ii1=min([nn nn-find(~isnan(flipud(B(:,7,k))),1)+1])
  ii2=min([nn nn-find(~isnan(flipud(B(:,8,k))),1)+1])
  B(nn,7,k)=B(ii1,7,k)
  B(nn,8,k)=B(ii2,8,k);
end
out=reshape(permute(B,[2 1 3]),size(A,2),size(A,1))'

[Ekaterina Serikova]

Dear Azzi, is there any reason why the last values appear in 29th and 59th minutes and not in 30th and 00th? I mean if there is a price exactly in 30th minute, it will be overwritten, no? Thanks a lot in advance.

[Ekaterina Serikova]

Also, it seems that for column 12 the value is not carried in 29th minute, please see the picture attached. How can I solve it?

[Azzi Abdelmalek]

I don't know if you have noticed, the code I posted works only for matrix nx8. To make it work for any size:

%-----------------------------------------  
v1=datenum('01-01-2016','dd-mm-yyyy');
pas=1/(24*60);
v=datestr(v1:pas:v1+pas*89);
n=size(v,1);
w=randi(10,n,5);
w(randperm(n*5,fix(3*n/2)))=nan;
A=[datevec(v) w];
%---------------------------------------
m=size(A,2);
nn=30;
B=permute(reshape(A',m,nn,[]),[2 1 3]);
for k=1:size(B,3)
  for p=7:m
      ii=min([nn nn-find(~isnan(flipud(B(:,p,k))),1)+1])
      B(nn,p,k)=B(ii,p,k)
  end
end
out=reshape(permute(B,[2 1 3]),size(A,2),size(A,1))'

[Ekaterina Serikova]

Dear Azzi, thanks so much for your help! It works perfect.

[Ekaterina Serikova]

Dear Azzi, could you please also explain what is the idea that 30mins values are in 29th and 59th minutes? In this case, if there is a price at exactly 30th minute, it will count it for the next 30 mins instead of just leaving it. How can I adjust the code so that 30th and 00th values were included? Thanks a lot.

[Ekaterina Serikova]

Dear Azzi,ok, I solved that by taking 30th and 00th value and adding a condition that if that is NA, then take the previous (which is 29th). Another question - this code works only for matrices, the length of which are multiples of 30. Thus, I have to delete some 29th and 30th lines containing the values that I also need. How can I still save these values?

Answer 2

[the cyclist]

I think this does what you want. I've commented it to show the algorithm.

% Create some pretend prices. Use your actual price matrix here.
% Needs to be multiple of 30 in length along dimension 1.
% If it isn't, trim as needed.
originalPriceMatrix = reshape(1:240,60,4);

% Set last-minute price to NaN for testing
originalPriceMatrix(end,:) = NaN;

% Reshape to get each 30-minute segment along dimension 1
price = reshape(originalPriceMatrix,30,2,4);

% Carry forward price if NaN.
lastMinutePrices = price(1,:,:);
for nt = 2:30
    thisMinutePrices = price(nt,:,:);    
    thisMinutePrices(isnan(thisMinutePrices)) = lastMinutePrices(isnan(thisMinutePrices));
    lastMinutePrices = thisMinutePrices;
end

% Permute  to get (time X stock)
lastMinutePrices = permute(lastMinutePrices,[2 3 1]);

[Ekaterina Serikova]

Dear the cyclist, the output does not seem to give "time X stock" result, as you indicated. I get lastMinutePrices=

30  90  150  210
59  119  179  239