MATLAB Answers

JohnB
0

Re-arrange CRSP Data

Asked by JohnB
on 27 Dec 2018
Latest activity Edited by Cris LaPierre
on 28 Dec 2018
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 ?
2018-12-27_18-26-44.jpg

  2 Comments

Stephan
on 27 Dec 2018
Can you attach a sample of the data and an example how you want it to be?
JohnB
on 27 Dec 2018
I attached some preview for you 👍👍

Sign in to comment.

1 Answer

Cris LaPierre
Answer by Cris LaPierre
on 27 Dec 2018
Edited by Cris LaPierre
on 28 Dec 2018
 Accepted Answer

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);

  1 Comment

JohnB
on 28 Dec 2018
Thanks for your answer my friend !

Sign in to comment.