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

2 次查看（过去 30 天）
Hnor
Hnor 2021-11-8
编辑： Chris 2021-11-15
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
Chris 2021-11-9
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
  3 个评论
显示 1更早的评论
Chris
Chris 2021-11-15
编辑：Chris 2021-11-15
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
Chris 2021-11-15
编辑：Chris 2021-11-15
@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.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Holidays / Seasons 的更多信息

标签

产品


版本

R2021a

Community Treasure Hunt

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

Start Hunting!

Translated by