MATLAB: How to calculate Sums in a Panel Data framework using “For Loops”

Econometrics ToolboxfinancialFinancial Instruments ToolboxFinancial Toolboxforfor loopif statementMATLAB and Simulink Student Suitenansumtable

The following table consists of Price Returns returns at different Dates:
Idea: For any given Date and Stock Ticker, if returns data of previous 12 time steps is available (cell is not NaN), sum those returns and stock them in a new table. Code:
% Take row by row
% Considering columns 2 to 279 look at each date point if returns data for previous 12 time steps (weeks) is available
for i=length(Tnew.Date)       
for j=TNew.x1COVGY:end
    if (TNew.x1COVGY:end = ~(‘NaN’)) 
% Sum those 12 entries and list the result in a new table. For example, A2AIM's value for 30-Oct will be the sum of values between 30-Oct and 14-Aug.
end
end
Thanks for your help!!

Best Answer

To get the moving sum of the previous 12 elements, and marking all windows NaN where you don't have 11 previous points, you can use the two-element window input [11 0] and the Endpoints name-value pair to movsum, like this:
 >> movsum(1:15, [11 0], 'Endpoints', 'fill')
 ans =
    NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN    78    90   102   114
This is a trailing 12-point sum.
As far as dealing with all of the variables, you can always just extract them and reassign them from the table. movsum can deal with matrix inputs, and by default will go down the columns, so you could do something like this:
 >> T = table(randn(20,1), randn(20,1), randn(20,1), randn(20,1));
 >> T(1:3, :)
 ans =
      Var1       Var2       Var3       Var4  
     _______    _______    _______    _______
     -1.2276    0.80352     1.0438    0.90102
      1.1265     0.4427    -1.0934    0.33499
     0.56487    -1.2102     1.3748    0.36737
 >> T2 = T;
 >> T2{:, 1:4} = movsum(T{:,1:4}, [11 0], 'Endpoints', 'fill')
 T2 =
       Var1       Var2       Var3        Var4  
     ________    ______    ________    ________
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
          NaN       NaN         NaN         NaN
      -1.3491     4.066      5.5826    -0.34325
      0.35979    3.5101      4.6219     -1.7896
     -0.62395    4.7247         5.7     -1.7089
     -0.90211    6.4993      1.9725     -1.3553
      -3.1805    8.3403      2.5239     -1.6156
      -2.8086    7.9674      1.6148    -0.55556
      0.84903    7.2745      2.9359     0.35856
       2.3088    6.2673    0.084432     0.36349
       3.2424    6.6688    -0.97282     0.57143
Related Question