I have an interesting problem that involves taking the last day from a matrix and finding its last month day. Eg, if the date today is Oct-10-2011, you try to search for Sep-10-2011 or the first day < Sep-10-2011 in the matrix.
Matrix has multiple IDs and last trading dates may not be the same. Vectorized solution is desired. Thanks!
mat = [
1000 734507 11 ; 1000 734508 12 ; 1000 734509 13 ;
2001 734507 21 ; 2001 734508 22 ; 2001 734513 23 ; 2001 734516 25 ;
1000 734536 14 ; 1000 734537 15 ; 1000 734538 16 ;
2001 734536 26 ; 2001 734537 27 ; 2001 734544 28 ; 2001 734545 29;2001 734546 30
];
% datestr(mat(:,2))
[~,m,~] = unique(mat(:,1), 'rows', 'last') ;
lastDay = mat(m,;) ;
Tried using addtodate to get last-month-date here but it fails (more than 1 row)
Once I get the last-dates for each ID, I need to get the exact_day_lastmonth. After this, I need to get data on this day OR the day nearest to it (should be < exact_day_lastmonth).
Answer:
current_lastdays = [1000 734538 16 ; 2001 734546 30] ; % 4-Feb-2011, 12-Feb-2011
matching_lastmon = [1000 734507 11 ; 2001 734513 23] ; % 4-Jan-2011, 10-Jan-2011
Unless you want to risk rather large arrays with complicated indexing, I think a loop is the way to go.
mat = [ 1000 734507 11 ; 1000 734508 12 ; 1000 734509 13 ;
2001 734507 21 ; 2001 734508 22 ; 2001 734513 23 ; 2001 734516 25 ;
1000 734536 14 ; 1000 734537 15 ; 1000 734538 16 ;
2001 734536 26 ; 2001 734537 27 ; 2001 734544 28 ;2001 734545 29;2001 734546 30];
%# datestr(mat(:,2))
[~,m,~] = unique(mat(:,1), 'rows', 'last') ;
lastDay = mat(m,;) ;
matching_lastmon = lastDay; %# initialize matching_lastmon
oneMonthBefore = datenum(bsxfun(@minus,datevec(lastDay(:,2)),[0,1,0,0,0,0]));
for iDay = 1:size(lastDay,1)
%# the following assumes that the array `mat` is sorted within each ID (or globally sorted by date)
idx = find(mat(:,1)==lastDay(iDay,1) & mat(:,2) <= oneMothBefore(iDay),1,'last')
if isempty(idx)
matching_lastmon(iDay,2:3) = NaN;
else
matching_lastmon(iDay,:) = mat(idx,:);
end
end