How can I create new variables from a range in a matrix based on values?

Hello -

I am fairly new to MATLAB and struggling with a question and not sure where to begin, so I apologize for not having sample code here to start with.

There is a 7524 x 3 matrix m file attached which has columns of: 1) MATLAB date; 2) an asset value; and 3) an asset drawdown stream. Each row is a trading day.

I am trying to do the following:

Loop through each row and find ranges of values between zeros in column 3, given a certain threshold. For example, if the threshold is -.05, I would like to find and assign a variable to this range between zeros (new highs for the asset in column 3). If, between zeros (new highs), there is not a value <= -.05, disregard and move to the next. I would like to assign a unique variable name for all instances that meet this criteria (i.e. instance 1, instance 2, etc...) and then save to disk.

I am struggling thinking through how to accomplish this, so any help would be very appreciated!

Please let me know if further clarification is needed.

Answer 1

[Star Strider]

I am not certain what you want.

Try this:

D = load('spxDrwDwnData.mat');
A = D.spxDrwDwnData;
Thr = -0.05;                                                        % Threshold Value
Lv = A(:,3) <= Thr;                                                 % Logical Vecttor
TranPos = strfind(Lv.', [0 1])+1;                                   % Transition From Logical 0 To Logical 1
TranNeg = strfind(Lv.', [1 0])+1;                                   % Transition From Logical 1 To Logical 0

for k = 1:numel(TranPos)
    Vct{k} = A(TranPos(k):TranNeg(k),1);                            % Dates Corresponding To Regions Between Zeros
    Instance{k} = k*ones(TranNeg(k) - TranPos(k) + 1, 1);           % Instance Numbers
end

figure
plot(A(:,1), A(:,3))                                                % Plot Data
hold on
plot(A(TranPos,1), A(TranPos,3), '+g')
plot(A(TranNeg,1), A(TranNeg,3), '+r')
for k = 1:numel(Vct)
    plot(Vct{k}, -Instance{k}*0.01, 'LineWidth',2)                  % Plot ‘Instance’ Vectors
end
hold off
grid
axis([726790  727200    -0.1  0])                                   % Zoom For Detail (Optional)

The plot is a graphic depiction of what the code does, with the data yoou provided. See if the ‘Instance’ numbers do what you want them to do.

[BuMatlab]

Hi Star -

Thanks a million for helping me out here. The "Instance" variable is not quite what I was looking for. Ideally the Instance variable collects columns 1:3 from spxDrwDwnData for rows when spxDrwDwnData(:,3) <= -.05 (or a given threshold) between zeros (or new highs in investment terms).

Thanks again and please let me know if this makes sense!

[Star Strider]

My pleasure.

See if this does what you want:

for k = 1:numel(TranPos)
    Vct{k} = TranPos(k):TranNeg(k);                                 % Dates Corresponding To Regions Between Zeros
    Instance{k} = A(Vct{k}, :);                                     % Instance Numbers
end

The rest of my code is unchanged. The ‘Instance’ numbers are the ‘k’ indices.

[BuMatlab]

Star -

Thank you again, this is exactly what I am looking for! One more question, for the code you provided, for Instance(1,1), if I wanted to keep the -.05 thershold after the first zero for spxDrwDwnData column 3, but keep collecting data until there is another zero (vs. until -.05 again) in spxDrwDwnData column 3, what edits would be needed?

Soooo appreciative for your help!

[Star Strider]

My pleasure.

If I understand correctly, this would do what you want:

A = D.spxDrwDwnData;
Thr = -0.05;                                                        % Threshold Value
Lv = A(:,3) <= Thr;                                                 % Logical Vector
TranPos = strfind(Lv.', [0 1])+1;                                   % Transition From Logical 0 To Logical 1
TranNeg = strfind(Lv.', [1 0])+1;                                   % Transition From Logical 1 To Logical 0
NewZero = find(A(:,3) == 0);                                        % Detect Zeros In Column #3

for k = 1:numel(TranPos)
    NewRange(k) = NewZero(find(NewZero > TranPos(k), 1, 'first'));  % Check Index
    Vct{k} = TranPos(k):NewZero(find(NewZero > TranPos(k), 1, 'first'));    % Dates Corresponding To Regions Between Zeros
    Instance{k} = A(Vct{k}, :);                                     % Instance Numbers
end

Note that the next zero is the same for several values of ‘TransPos(k)’, so there will be duplicates for some data in ‘Vct’ and ‘Instance’. You may want to get unique values of ‘NewRange’ and only keep the first corresponding value of ‘Instance’, to avoid the duplication. I leave that to you.

The rest of my code is unchanged.

[Star Strider]

If my Answer helped you solve your problem, please Accept it!

[BuMatlab]

Thank you again, Star!

[Star Strider]

As always, my pleasure!

This is an interesting problem!