autorec.m

%function autorec()
clear; format long; delete auto_rec.xls;
rec={'CUSIP','abb','PositionID','Issuer','SEI','Bank','Share Difference','Net','Rate Difference','SEI Place Holder','Difference Sorter','Notes'};
[numberssei,holdings]=xlsread('sei.xls');
[numberslcp,lcp]=xlsread('bank.xls');

% generates comparison ids
for indx=1:length(holdings)
    current_name=char(holdings(indx));
    holdings{indx,3} = current_name(1,1:6);
end
for indx=1:length(lcp)
    current_name=char(lcp(indx,2));
    lcp{indx,1} = current_name(1,1:6);
end
sort(lcp);

% Replace Bank Names with ours
[nonumbers,alternate_names]=xlsread('Alternate_names.xls');
for indxS=1:length(alternate_names);
    for indxS2=1:length(lcp);
        found=regexpi(lcp{indxS2,2},alternate_names{indxS,2});
        if isempty(found)==0;
            alternateN=alternate_names{indxS,1};
            if length(alternateN)>5;
                lcp{indxS2,1}=alternateN(1:6);
            else
                lcp{indxS2,1}=alternateN(1:end);
            end
        end
    end
end

% Replace Our Names when necessary
[nonumbers2,alternate_SEI]=xlsread('Alternate_SEI.xls');
for indxS=1:length(alternate_SEI);
    for indxS2=1:length(holdings);
        found=regexpi(holdings{indxS2,1},alternate_SEI{indxS,2});
        if isempty(found)==0;
            alternateN=alternate_SEI{indxS,1};
            if length(alternateN)>5;
                holdings{indxS2,3}=alternateN(1:6);
            else
                holdings{indxS2,3}=alternateN(1:end);
            end
        end
    end
end

% Sorts the adjusted lcp list
total_lcp=lcp;
total_lcp(:,1)=lcp(:,1);
total_lcp(:,2)=lcp(:,2);
total_lcp(:,3)=lcp(:,3);
for numconv=1:length(numberslcp)
    total_lcp(numconv,4)={num2str(numberslcp(numconv,1))};
end;
for numconv=1:length(numberslcp)
    total_lcp(numconv,5)={num2str(numberslcp(numconv,2))};
end;
total_lcp=sortrows(total_lcp);
lcp(:,1)=total_lcp(:,1);
lcp(:,2)=total_lcp(:,2);
lcp(:,3)=total_lcp(:,3);
for numconv=1:length(numberslcp)
    numberslcp(numconv,1)=str2num(total_lcp{numconv,4});
end;
for numconv=1:length(numberslcp)
    numberslcp(numconv,2)=str2num(total_lcp{numconv,5});
end;

% Sorts the adjusted holdings list
total_holdings=holdings;
total_holdings(:,3)=holdings(:,1);
total_holdings(:,2)=holdings(:,2);
total_holdings(:,1)=holdings(:,3);
for numconv=1:length(numberssei)
    total_holdings(numconv,4)={num2str(numberssei(numconv,1))};
end;
total_holdings=sortrows(total_holdings);
holdings(:,3)=total_holdings(:,1);
holdings(:,2)=total_holdings(:,2);
holdings(:,1)=total_holdings(:,3);
for numconv=1:length(numberssei)
    numberssei(numconv,1)=str2num(total_holdings{numconv,4});
end;


% Turns out I didn't need, but keeping in case do. Generates combined list of comparison IDs
%total_abbr_list=sortrows([holdings(:,3);lcp(:,3)]);
%indxB=2; list_size=length(total_abbr_list);
%while indxB<list_size
%    list_size=length(total_abbr_list);
%    if all(total_abbr_list{indxB}==total_abbr_list{indxB-1});
%        total_abbr_list(indxB) = [];
%    else
%        indxB=indxB+1;
%    end;
%end;
    

%Builds rec
indx=1; indx2=1;
while indx<length(holdings)+1 & indx2<length(lcp)+1
    if strcmpi(holdings{indx,3},lcp{indx2,1}) %assigns values when there is a match
        [rows,cols]=size(rec);
        rec{rows+1,2} = holdings{indx,3}; %holdings abbr
        rec{rows+1,1} = holdings{indx,2}; %CUSIP
        rec{rows+1,3} = numberslcp(indx2,2); %PositionID
        rec{rows+1,4} = holdings{indx,1}; %holdings name
        rec{rows+1,5} = numberssei(indx,1); %holdings number
        rec{rows+1,6} = numberslcp(indx2,1); %lcp number
        indx=indx+1; indx2=indx2+1;
    elseif strcmpi(holdings{indx,3},holdings{indx-1,3})
        [rows,cols]=size(rec);
        rec{rows+1,2} = holdings{indx,3}; %holdings abbr
        rec{rows+1,1} = holdings{indx,2}; %CUSIP
        rec{rows+1,4} = holdings{indx,1}; %holdings name
        rec{rows+1,5} = numberssei(indx,1); %holdings number
        rec{rows+1,6} = 0; %lcp number
        indx=indx+1;
    elseif strcmpi(lcp{indx2,1},lcp{indx2-1,1}) | all(lcp{indx2,1}==wordcomp(lcp{indx2,1},holdings{indx,3}))
        % My wordcomp is both faster than sortrows & only runs when necessary. Sortrows would have to be run each time
        [rows,cols]=size(rec);
        rec{rows+1,2} = lcp{indx2,1}; %lcp abbr
        rec{rows+1,3} = numberslcp(indx2,2); %PositionID
        rec{rows+1,4} = lcp{indx2,2}; %lcp name
        rec{rows+1,5} = 0; %holdings number
        rec{rows+1,6} = numberslcp(indx2,1); %lcp number
        indx2=indx2+1;
    else
        [rows,cols]=size(rec);
        rec{rows+1,2} = holdings{indx,3}; %holdings abbr
        rec{rows+1,1} = holdings{indx,2}; %CUSIP
        rec{rows+1,4} = holdings{indx,1}; %holdings name
        rec{rows+1,5} = numberssei(indx,1); %holdings number
        rec{rows+1,6} = 0; %lcp number
        indx=indx+1;
    end
end

%Adds remaining transactions to bottom
if indx<length(holdings)+1
    while indx<length(holdings)+1
        [rows,cols]=size(rec);
        rec{rows+1,2} = holdings{indx,3}; %holdings abbr
        rec{rows+1,1} = holdings{indx,2}; %CUSIP
        rec{rows+1,4} = holdings{indx,1}; %holdings name
        rec{rows+1,5} = numberssei(indx,1); %holdings number
        rec{rows+1,6} = 0; %lcp number
        indx=indx+1;
    end
elseif indx2<length(lcp)+1
    while indx2<length(lcp)+1
        [rows,cols]=size(rec);
        rec{rows+1,2} = lcp{indx2,1}; %lcp abbr
        rec{rows+1,3} = numberslcp(indx2,2); %PositionID
        rec{rows+1,4} = lcp{indx2,2}; %lcp name
        rec{rows+1,5} = 0; %holdings number
        rec{rows+1,6} = numberslcp(indx2,1); %lcp number
        indx2=indx2+1;
    end
else
end

rec(2,7)={'=E2-F2'};
rec(2,8)={'=SUMIF(B$2:B$1000,B2,G$2:G$1000)'};
rec(2,9)={'=IF(AND(E2>0,F2>0),IF(OR((E2-F2)>0.05,(E2-F2)<-0.05),E2-F2,""),"")'};
rec(2,11)={'=ROUND(ABS(G2),0)'};
xlswrite('auto_rec.xls', rec);