Rank stock returns by month
2 views (last 30 days)
Show older comments
I have a table of stock returns for each company in the US, in each month from 2000-2019. Each month has roughly 3000 companies (and forecasted returns). I want to sort these forecasted returns into ten deciles, with decile 1 having the highest 10% of forecasted returns in each month (90th percentile), and so on so decile 10 has the lowest 10% forecasted returns of the month (10th percentile). I tried discretize but this function ranks the whole table together, not by month. Finally, I want to be able to combine all decile 1 ACTUAL returns into one table, decile 2 returns into another table and so on, and then find the average returns for each month, in each decile. I know I can do this using groupsummary, but I need to get the deciles assigned first. Any tips? Thanks.
I attached a sample data that I believe represents the structure of my table. I just took random parts of my table and put it together. The original table is 700k+ rows long. I need to rank by "TPER" (forecasted returns), and in the end I will want to find average "RET" (actual return) for each month in each decile. Thanks!
2 Comments
William Rose
on 12 May 2023
@Syabil, To help others help you, please provide the table, or a subset of the table, or simulated data with the same table structure, that illustrates how the data is arranged. Someone could waste time devising a solution that doesn;t work for your table. For example, your table could have 3000 rows and 241 columns (col.1=company name, columns 2-241=monthly returns, for the months from Jan 2000 to Dec 2019). Or the table could have 240 x 2 columns, because the company names could change from month to month, as some companies fail and others start. Or the columns could be companies and the rows could be months. Or something else.
Answers (2)
Santosh Fatale
on 18 May 2023
Hi Syabil,
I understand that you want to process the data available in tabular format in MATLAB.
Kindly refer to the following code snippet, which could help you to understand about extracting rows of interests from the table variable.
load("sampledata.mat");
originalTable = sampledata;
% Sort table as per Year+Month and assign it to new table variable.
[sortedValues, sortingIndices] = sort(originalTable.dates,'ascend');
updatedTable = originalTable(sortingIndices,:);
% Find unique dates from the updated table.
uniqueDates = unique(updatedTable.dates);
% Loop over each unique date and process the data.
for idx = 1 : length(uniqueDates)
% Select the rows as per
tempTable = updatedTable(updatedTable.dates == uniqueDates(idx),:);
% Write your logic to process data from tempTable
end
In “for” loop, you could add logic for processing data extracted from the original table.
1 Comment
Eric Sofen
on 5 Jun 2023
First off, get your dates into datetime rather than YYYYMM doubles!
Then, you can use groupsummary to group the data by month and do the discretize you have above. The tricky part is that each month appears to have a different number of companies, so the size of the output varies from month to month. To deal with this, we wrap the output in a cell. Then, for any given month, if you want the company numbers corresponding to the quantiles, you'll have to index back into that list...
load sampledata.mat
sampledata.dates = datetime(string(sampledata.dates),InputFormat="yyyymm");
head(sampledata)
pctlFun = @(x) {discretize(x,quantile(x,[0:10]/10))};
coNumList = @(y) {horzcat(y)};
monthlyPercentiles = groupsummary(sampledata,"dates","month",pctlFun,"TPER")
Depending on the format you ultimately want, you could also use rowfun to find which companies are in each quantile, then put each quantile's companies into a cell that goes into the table. Similar to this example.
0 Comments
See Also
Categories
Find more on Dates and Time 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!