Table calculations, annual return

Im trying to calculate annual return of several stocks in a table by dividing the 12th observation on the first one, and get an error message on this code:

table = table();

table.AAPL = mean(AAPL,2);

table.AMZN = mean(AMZN,2);

table.F = mean(F,2);

table.INTC = mean(INTC,2);

table.MSFT = mean(MSFT,2);

table.TXN = mean(TXN,2);

table.UIS = mean(UIS,2);

table.XOM = mean(XOM,2);

ann_ret = table(12,:)./table(1,:);

When i write table(12,:) in the command window i get no error message, but when i try to run the code i get this error.

Error:

Subscripting into a table using one subscript (as in t(i)) or three or more subscripts (as in t(i,j,k)) is not

supported. Always specify a row subscript and a variable subscript, as in t(rows,vars).

If anyone could make a loop of some sort that would be nice as i need to calculate the annual returns for three years, meaning observation 24 and 36 from the table as well.

Answer 1

[Star Strider]

Assuming that you created the table called ‘T1’ correctly and that it contains variables (columns) named ‘AAPL’, ‘AMZN’ and the rest, do something like this to create a second table ‘T2’ with the results:

T2.AAPL   = mean(T1.AAPL);
T2.AMZN   = mean(T1.AMZN);

and so for the rest.

Also, naming it ‘table’ overshadows the actual table function, so it will not be possible to create more table arrays later. Name it something else, as I did in this example.

Answer 2

[Peter Perkins]

Almost certainly the best way to do this is to use a timetable, and a grouped varfun (or grouptransform).

>> AAPL = rand(36,1);
>> MSFT = rand(36,1);
>> TMW = rand(36,1);
>> tt = timetable(AAPL,MSFT,TMW,'RowTimes',datetime(2020,1:36,1))
tt =
  36×3 timetable
       Time          AAPL         MSFT        TMW   
    ___________    _________    ________    ________
    01-Jan-2020      0.70207     0.53679     0.97899
    01-Feb-2020      0.37746     0.76211     0.28327
    01-Mar-2020      0.73496     0.34757     0.13378
 [snip]
    01-Oct-2022       0.4889      0.8569     0.40773
    01-Nov-2022      0.22031     0.04339    0.036382
    01-Dec-2022      0.22621     0.69163     0.74615
>> tt.Year = year(tt.Time);
>> returns = varfun(@(x)x(12)/x(1),tt,"GroupingVariable","Year");

Now clean things up a bit

>> returns.Properties.VariableNames(3:5) = tt.Properties.VariableNames(1:3);
>> returns.Time = datetime(returns.Year,1,1,"Format","uuuu");
>> returns.Year = [] % no longer needed
returns =
  3×4 timetable
    Time    GroupCount     AAPL        MSFT        TMW  
    ____    __________    _______    ________    _______
    2020        12        0.16401    0.025071    0.23396
    2021        12         3.0933       1.139    0.94322
    2022        12        0.12213     0.24493     304.91