Sorting return by dates

I would like to sort the average return of each year. I have a data base of stock returns from 1992-2021. I would like to know the average stock return of each year.

How should I code this? Should i set a date range? I know there is a sort function to place the years in ascending orders. However, it is unable to help me to calculate the return of a specific year.

Also, the dates are in matlab form, like ,727594, which requires datevec function to look at the dates. Does it affect the coding part?

or this task can be think as adding up the return and find the average return of stocks in the same year.

I have totally no idea how to do this, please help me with this. Thank you very much

Answer 1

[Walter Roberson]

[Y, ~] = datevec(TheDateNumbers) ;
G = findgroups(Y) ;
results = grpstats(YourData, G, "mean");

If you want the return per stock then stock identification should be also be passed to findgroups.

[King To Leung]

Hi, Thank you for your answer.

I'm sorry that I don't really understand how the codes work. How can I get the mean return of each year from 1992-2021.

What should I enter in "TheDateNumbers"?As there are a lot of dates.

Answer 2

[Dyuman Joshi]

%to get the year of a date
[year,~,~] = datevec(727594)

year = 1992

%storing the years in y (months and dates are not necessary)
%dates are stored in the 2nd column

[y,~,~] = datevec(data_crsp(:,2));

%calculating mean stock return for each year, return is in the 7th column
for k=1992:2021
    stockreturn(k-1991) = mean(data_crsp(y==k,7));
    %1st element will correspond to 1992, 2nd - 1993 and so on
end

[King To Leung]

Hi, Thank you for your answer.

I tried the code, matlab says that mean(stock(y==k)) has error. There is no stock function.

Should I put my data column to replace the word stock?

[Dyuman Joshi]

"Should I put my data column to replace the word stock?"

Yes

So, names like dates and stock are used to denote your input. Since we don't know what the variable name is, we make an assumption that can be easily related/understood.

[King To Leung]

Thank you for the reply. My data base is called data_crsp, and there is 16 columns in it. The date is at the 2nd column and the return is at the 7th column. I am sorting the return according to the dates. I'm a bit confused what to put into "stock".

Also, should I be inputting the earliest date to "dates". So that it can find the yearly average return for each year from 1992-2021.

Thank you very much for your time!!

[Dyuman Joshi]

Alright, I have adjusted my code according to your input. Let me know if it works or not.

[King To Leung]

Thanks a lot!!

The store of value was successful. However, it resulted in 1*30 array with all Nan.

[Walter Roberson]

What shows up for

unique(y)

mask = y >= 1992 & y <= 2021;
nnz(mask)
nnz(isnan(data_crsp(mask,7)))

[King To Leung]

it shows 1992-2021.

The second ans is

ans =

1703110

, which equals to the no. of rows of data_crsp

the third ans is

ans =

13148

, so this means that there is Nan in the return column, causing the problem?

[King To Leung]

I turned the Nans into 0. However this would affect the accuracy of the result.

I tried to add ~isnan to the following code

stockreturn(k-1992) = mean(~isnan(data_crsp(y==k,7)));

but the answer is super strange, the average return each year is like 99%, which is not reasonable.

I then tried this

for k=1993:2021

stockreturn(k-1992) = mean(intersect(find(~isnan(data_crsp(:,c.date))),find(~isnan(data_crsp(:,c.ret)))),(y==k,7));

%1st element will correspond to 1993, 2nd - 1994 and so on

end

, but it shows error

[Dyuman Joshi]

Can you post your data here? That way we can directly look at it and work accordingly.

[King To Leung]

Thanks for your help! but the data is too big, how can i post it here?

[Dyuman Joshi]

Use the attach symbol in the dashboard menu.

[King To Leung]

I tried to attach the matlab data, but the system says it's over 5MB