Re-arrange CRSP Data

How can I rearrange CRSP Data into matrix since both stocks and dates are ranged into columns.

How can I place dates in a single row and only Stocks tickers in a column ?

[Stephan]

Can you attach a sample of the data and an example how you want it to be?

[JohnB]

I attached some preview for you ??

Answer 1

[Cris LaPierre]

I would elect to do this using a table. However, column headers in a table are variable names, and MATLAB variables cannot be numbers. That means a little modification will be needed. You can set the codes as the rownames and the modified dates as the variableNames (column headers).

This is a bit of a hack, and can likely be improved. Still, this should get you started

file = 'US_500_Book_SRT.xlsx';
opts = detectImportOptions(file);
% Your dates are in a numeric 'yyyymmdd' format that can't be recognized by readtable.
% I import them as numbers here and convert them later.
opts = setvartype(opts,1,'categorical');

% load data
data = readtable(file,opts);
% convert dates from numeric 'yyyymmdd' to datetime 'yyyyMM'
datetimeYY = @(D)datetime(D,'ConvertFrom','yyyymmdd','Format','yyyyMM');
data = convertvars(data,2,datetimeYY);
% I want to use dates as a grouping mechanism, so convert to categorical as well.
data = convertvars(data,2,'categorical')

% extract the unique stock codes and yyyyMM dates
stocks = categories(data.Var1);
dates = categories(data.Var2);

% Find groups, using indeces to assign to row, column of matrix
Gs = findgroups(data.Var1);
Gd = findgroups(data.Var2);
mat2 = zeros(length(stocks),length(dates));

% Didn't find a more elegant way to do this. Loop through each row of data
% Assign to table using stocks group for the row number and dates group for the column
for loop = 1:length(Gs)
    mat2(Gs(loop),Gd(loop)) = data.Var3(loop);
end

% Convert mat2 to a table
tbl2 = table(mat2);
tbl2 = splitvars(tbl2);
% assign stock codes as rownames
tbl2.Properties.RowNames = stocks;
% make column names dates (prepend with 'd_' so it is a valid MATLAB variable name)
tbl2.Properties.VariableNames = 'd_' + string(dates);

[JohnB]

Thanks for your answer my friend !