Table calculations, annual return

4 views (last 30 days)
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.

Accepted Answer

Star Strider
Star Strider on 9 Apr 2021
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.

More Answers (1)

Peter Perkins
Peter Perkins on 3 Mar 2022
Edited: Peter Perkins on 3 Mar 2022
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

Categories

Find more on Tables in Help Center and File Exchange

Community Treasure Hunt

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

Start Hunting!