How to sum a specific range of values in a column?

Hello,

I'm trying to build a function that would read all the elements in a column matrix "a" then find the same value in column matrix "b" read the exact row and lastly sum all rows from exact row to exact row-30, and make it save all the answers in a new matrix.

This is what I have so far, but something is wrong.

 function [psum, nsum] = datac (a, b, c)
 lngth=length(a);

 for i=1:lngth
    eyear=a(i, 1);
    row=find(b(:,1)==eyear);

    row2=row-30;

    psum(1,i)=sum(c(row2:row, 1) >0);
    nsum(1,i)=sum(c(row2:row, 1) <0);
end

[Edmundas Povilavicius]

With you suggestions, this is what I finally wrote:

 Superset = datenum(datedif);
 Subset = datenum(electiondates);

 [isfound, daterows] = ismember(electiondates, datedif);
 for i=1:numel(daterows)
    if isfound(i)==0
        Subset(i)=Subset(i)-1;
        electiondates(i)=datestr(Subset(i));
        [isfound(i),~] = ismember(electiondates(i), datedif);
        if isfound(i)==0
            Subset(i)=Subset(i)-1;
            electiondates(i)=datestr(Subset(i));
        end
    end
 end
 [~, daterows] = ismember(electiondates, datedif);

 for j = 1:numel(daterows)
    sumdif = difrence(daterows(j) : daterows(j)+29);
    psum(j) = sum(sumdif(sumdif> 0));
    nsum(j) = sum(sumdif(sumdif< 0));
 end

Any suggestions for improvement?

Answer 1

[Guillaume]

A few notes first. 1) Avoid length, it's a dangerous function. Use numel or size with an explicit dimension. 2) There's no point indexing the singleton dimension with vectors, a(i) is simpler and clearer than a(i, 1) for vectors. 3) Use meaningful variable names.

Assuming that your election dates are stored as datenum or datetime (preferable), then:

%variables:
%electiondates: datenum or datetime vector
%datedif: datenum or datetime vector (or better column of a table)
%difrence: numeric vector (or better column of a table)
[~, daterows] = ismember(electiondates, datedif); %find location of each election date in datedif. Assumes all dates are found
positivesum = zeros(size(electiondates));
negativesum = zeros(size(electiondates));
for row = 1:numel(daterows))
    summationdifrence = difrence(max(1, daterows(row)-29) : daterows(row));  %assume you want 30 elements (hence -29). makes sure you don't go before the first element with max
    positivesum(row) = sum(summationdifrence(summationdifrence> 0));
    negativesum(row) = sum(summationdifrence(summationdifrence< 0));
end

[Edmundas Povilavicius]

This is perfect! Thank you!

Tho I found a problem, it seems some elections happened over the weekend and therefore there are no market data (aka the diference100) because it was closed. How would it be possible to amend the code that if electiondates and datedif would have no match (be 0) the row number would be the closest one below the election date happening over a weekend. (it will be either -1 or -2 lower depending if it's Sunday or Saturday as markets close on Friday).

Thank you

[Guillaume]

Well, the simplest thing might be to adjust the electiondates vector to shift the date to friday if it's saturday or sunday.

Another approachis to change the creation of daterows to:

[isexact, daterows] = ismember(electiondates, datedif);
missingdates = electiondates(~isexact);
[isprevious, prevrows] = ismember(missingdates - 1, datedif);
[~, beforerows] = ismember(missingdates(~isprevious) - 1, datedif);
prevrows(~isprevious) = beforerows(~isprevious);
daterows(~isexact) = prevrows(~isexact);

Answer 2

[Image Analyst]

It's not clear what you want. Can you supply some data and expected output. If you want to just sum in a sliding 30 row window, why don't you simply use conv()? And don't use data for your variable name since that is a built-in functions. And it's not clear why you're using cell arrays instead of simpler double arrays, and why you're putting all the results into the first element of psum instead of the i'th element.

[Edmundas Povilavicius]

I suppose I can answer to most of your questions by saying that I don't know what am I doing. I need to do data analysis and I chose matlab to do it.

Instead of me trying to explain what I'm doing, I will better describe the task.

I have specific dates of every election. And then there are values for every day since 1927.

I need to sum all the positive and all the negative values of "Diference100" 30 days before the election date. These sums should be made for every election.

P.S. It seems I can't upload the excel datasheet.

[Image Analyst]

To sum the values of Diference100:

windowSize = 30;
summedValues = movsum(Diference100, windowSize);

[Edmundas Povilavicius]

Thank you, however, I don't need to sum up every value because most of the data is useless and I need to skip it. I only need to sum up data 1 to 30 days before a specific date, i.e. election date.