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

1 view (last 30 days)
Hnor on 8 Nov 2021
Edited: Chris on 15 Nov 2021
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

Chris on 9 Nov 2021
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));
end
end
end
Chris on 15 Nov 2021
@Hnor You might also save the average returns in their own variable before adding them to the table.
Preallocate before the loop starts with
then change that last line in the k loop:
and add it to the table after the loops complete.

R2021a

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!