How to identify an optimal subsample from a data set with missing values in MATLAB

Question

I would like to identify the largest possible contiguous subsample of a large data set. My data set consists of roughly 15,000 financial time series of up to 360 periods in length. I have imported the data into MATLAB as a 360 by 15,000 numerical matrix.

This matrix contains a lot of NaNs due to some of the financial data not being available for the entire period. In the illustration, NaN entries are shown in dark blue, and non-NaN entries appear in light blue. It is these light blue non-NaN entries which I would like to ideally combine into an optimal subsample.

I would like to find the largest possible contiguous block of data that is contained in my matrix, while ensuring that my matrix contains a sufficient number of periods.

In a first step I would like to sort my matrix from left to right in descending order by the number of non-NaN entries in each column, that is, I would like to sort by the vector obtained by entering sum(~isnan(data),1).

In a second step I would like to find the sub-array of my data matrix that is at least 72 entries along the first dimension and is otherwise as large as possible, measured by the total number of entries.

What is the best way to implement this?

Answer 1

A big warning (may or may not apply depending on context)

As Oleg mentioned, when an observation is missing from a financial time series, it's often missing for reason: eg. the entity went bankrupt, the entity was delisted, or the instrument did not trade (i.e. illiquid). Constructing a sample without NaNs is likely equivalent to constructing a sample where none of these events occur!

For example, if this were hedge fund return data, selecting a sample without NaNs would exclude funds that blew up and ceased trading. Excluding imploded funds would bias estimates of expected returns upwards and estimates of variance or covariance downwards.

Picking a sample period with the fewest time series with NaNs would also exclude periods like the 2008 financial crisis, which may or may not make sense. Excluding 2008 could lead to an underestimate of how haywire things could get (though including it could lead to overestimate the probability of certain rare events).

Some things to do:

1. Pick a sample period as long as possible but be aware of the limitations.
2. Do your best to handle survivorship bias: eg. if NaNs represent delisting events, try to get some kind of delisting return.
3. You almost certainly will have an unbalanced panel with missing observations, and your algorithm will have to be deal with that.
4. Another general finance / panel data point, selecting a sample at some time point t and then following it into the future is perfectly ok. But selecting a sample based upon what happens during or after the sample period can be incredibly misleading.

Code that does what you asked:

This should do what you asked and be quite fast. Be aware of the problems though if whether an observation is missing is not random and orthogonal to what you care about.

Inputs are a T by n sized matrix X:

T = 360;              % number of time periods (i.e. rows) in X
n = 15000;            % number of time series (i.e. columns) in X
T_subsample = 72;     % desired length of sample (i.e. rows of newX)

% number of possible starting points for series of length T_subsample
nancount_periods = T - T_subsample + 1;   

nancount = zeros(n, nancount_periods, 'int32'); % will hold a count of NaNs

X_isnan = int32(isnan(X));

nancount(:,1) = sum(X_isnan(1:T_subsample, :))';  % 'initialize

% We need to obtain a count of nans in T_subsample sized window for each
% possible time period
j = 1;
for i=T_subsample + 1:T   
    % One pass: add new period in the window and subtract period no longer in the window 
    nancount(:,j+1) = nancount(:,j) + X_isnan(i,:)' - X_isnan(j,:)';
    j = j + 1;
end

indicator = nancount==0;  % indicator of whether starting_period, series
                          % has no NaNs 

% number of nonan series of length T_subsample by starting period
max_subsample_size_by_starting_period = sum(indicator); 
max_subsample_size = max(max_subsample_size_by_starting_period);

% find the best starting period
starting_period = find(max_subsample_size_by_starting_period==max_subsample_size, 1);
ending_period   = starting_period + T_subsample - 1;

columns_mask = indicator(:,starting_period);
columns      = find(columns_mask);   %holds the column ids we are using

newX = X(starting_period:ending_period, columns_mask);