MATLAB: How to reshape efficiently Panel Data

panel data

I have a database where I store historical prices of ~1000 stocks for a +10yr period in a panel data format (nx4) ([Ticker] [Date] [Price] [Return]). I import the data into Matlab and then try to construct two historical matrices (one for Price and another for Returns) in the format (columns->tickers, rows->dates, values -> Price/Return). In order to do that I use the following code:
historical_returns; %panel data cell array imported from the database
historical_dates; %array that includes all historical dates
tickers; %array that includes all the tickers
Matrix_Prices = zeros(length(historical_dates),length(tickers));
Matrix_Returns = zeros(length(historical_dates),length(tickers));
for i=1:size(historical_returns,1)
temp_ticker = historical_returns{i,1};
temp_date = historical_returns{i,2};
temp_price = historical_returns{i,3};
temp_return = historical_returns{i,4};
row = find(strcmpi(historical_dates,temp_date));
column = find(strcmpi(tickers,temp_ticker));
Matrix_Prices(row,column) = temp_price;
Matrix_Returns(row,column) = temp_return;
end
The code above takes ~200sec to run assuming historical_returns has a size of 1mmx4 (which increases as the # of tickers and dates increase). I am trying to optimize the code (if possible), so I am not sure if there is a faster way to construct Matrix_Returns. I have thought of storing the data in a different format, but given the limit of column size in Access and SQL databases, I cannot create a new column for each ticker.

Best Answer

You can replace the loop with the following:
Matrix_Prices = zeros(numel(historical_dates), numel(tickers));
Matrix_Returns = zeros(numel(historical_dates), numel(tickers));
[~, rows] = ismember(lower(historical_returns(:, 1)), lower(historical_dates)); %use lower case to make case insensitive comparison
[~, cols] = ismember(lower(historical_returns(:, 2)), lower(tickers)); 
indices = sub2ind(size(Matrix_Prices), rows, cols);
Matrix_Prices(indices) = cell2mat(historical_returns(:, 3));
Matrix_Returns(indices) = cell2mat(historical_returns(:, 4));
Related Question