How can I average same-calendar-month returns over the prior 20-year period?

I have a timtable with monthly stock returns from Jan 1943 - Dec 2011organized as in the picture below:

PERMNO is an identification number for each unique stock. What i want to do is calculate the average same-calendar-month excess returns over the prior 20 year period for each unique PERMNO, and save these averages in column 5 of the timetable.

For example, the stock with PERMNO 10001 have observations from Feb 1986 - 30 Dec 2011. Hence, the same-calendar month average for Dec 2011 would be the average EXCESS_RET of Dec 2011, Dec 2010, Dec 2009, ... , Dec 1991. And for Dec 2010 it would be the average EXCESS_RET from Dec 2010, Dec 2009, ... , Dec 1990, and so on. After doing this for all the months from Dec 2011 until Feb 2006, I would not be able to compute the Jan 2006 prior 20 year same-calendar-month average because there is only 19 prior Jan months of observations left. Hence, for Jan 2006, Jan 2005, ... , Jan 1987, I would like to insert the average same-calendar-month excess return computed for Jan 2007, and likewise for the other months with less than 20 years of observations left.

My timetable is named data_excess, and with the code below I am able to compute the 71 first average excess returns of the prior 20 year same-calendar-months for PERMNO 10001.

I am struggling with the last part, where I don't have 20 years of same-calendar-months to compute the average from.

1. How can I insert my last monthly computed average for the remaining 240 months (20 years)?
2. Also, how do I go about doing this procedure for all the PERMNO's, and not just 10001?
3. And how do I insert these results for all the PERMNO's in column 5 of my original table, so that each average matches the date?

Thanks!

dates = unique(data_excess.date)';
dates = flip(dates, 2);

for i = 1:588
    dates1(:, i) = dates(i:12:240+i)
end 

for i = 1 : 311-240
    Permno10001 = data_excess((data_excess.PERMNO == 10001), :);
    D = dates1(:, i);  
    same_cal(i, :) = mean(Permno10001.EXCESS_RET(D, :));  
end

Answer 1

[Chris]

This is a complex question.

I would answer your questions with a cell array of tables, and a whole lot of untested (because I don't have your timetable) code for you to ponder.

stonks = unique(data_excess.PERMNO); % easy enough
for idx = 1:12 %months
    for jdx = 1:numel(stonks) % PERMNO
        % Find all that apply
        tempidxs = ((data_excess.PERMNO == stonks(jdx)) & (month(data_excess.date) == idx));
        tempdata = data_excess(tempidxs)
        
        % store this table just in case?
        stonkmonth{jdx,idx} = tempdata;
        
        counter = 0;
        minyear = min(year(tempdata.date));
        
        % Go through each stonkmonth individually
        for kdx = 1:size(tempdata,1)
            %assuming dates are sorted
            thisyear = year(tempdata.date(kdx));
            yspan = max(thisyear-20,minyear):thisyear;
            runningT = mean(tempdata.EXCESS_RET(ismember(year(tempdata.date)),yspan));
            data_excess.finalAnswer(tempidxs(kdx)) = runningT;        
        end
    end
end

[Hnor]

Is there any way to speed up your code? Matlab shuts down after running the loop for 8h.

[Chris]

Huh. Are you sure it's not stuck in an infinite loop? As I mentioned, I couldn't test this code because I don't have a similar timetable to work with.

You could add some lines to indicate where the code is. For instance:

for idx = 1:12
    for jdx = 1:numel(stonks)
        if ~mod(jdx,100) % Or some other interval depending on how many PERMNOs there are
             fprintf(['\nMONTH: ' num2str(idx) '\tStock # ' num2str(jdx)])
        end
        
        % Find all that apply
        tempidxs = ...
        
    end
end

If it makes it to February, it's probably not in a loop. If you don't need the stonkmonth array, commenting out that line would probably help.

[Chris]

@Hnor You might also save the average returns in their own variable before adding them to the table.

Preallocate before the loop starts with

finalAnswer = zeros(size(data_excess,1),1);

then change that last line in the k loop:

finalAnswer(tempidxs(kdx)) = runningT; 

and add it to the table after the loops complete.