function [y,varargout]=backtestcomplex(x,varargin)
% Created on 2012-07-15
% latest justified on 2012-09-20
% 输入x是一个excel文件的地址字符串，如‘E:\Top50.xlsx’, excel文件的第一行为表头，包含4列：股票交易代码(SZ000001，SH600001形式)，中文简称，选股日期
%(yyyy-mm-dd形式)，权重因子
% 输出y是一个矩阵,有portfolioNum+3列，第一列对应交易日期，2:end-2列对应该交易日期组合的复权收盘价；如果第一列的值为0，那么该行对应的是组合的权重因子；
% 第end-1列为该交易日相对于上个交易日组合的收益率；第end列为策略的累计收益率
% vargin是一个数字或者日期字符串，设定回测的结束日期，默认回测到昨天。
% varargout{varout_infoRatio}是一个structure,有两个field：daily和monthly，分别是一个两列的矩阵，第一列对应日期，第二列表示从该日期起到回测结束策略的信息比率
% varargout{varout_maxDrawDown}是一个size(y,1)-1行2列的矩阵，分别对应组合和市场指数从y(1:end-1,1)交易日开始，到y(end,1)交易日结束时的最大回撤
% varargout{varout_index}是一个structure,有HS300和SZZZ两个field，对应相应的市场指数
% varargout{varout_matketCap}是一个两列的矩阵，第一列为日期，第二列为该日期组合的流通市值；
% varargout{varout_FF3},对应Fama-French三因子回归超额收益alpha
% varargout{varout_xlsResult}输出xlsResult在y中每个组合前面加上了“中文简称”，“交易代码”，“windID”三行，方便写为excel文件
% raw=x;
[~,~,raw]=xlsread(x);
tradeCost=0;
refIndexName=\'HS300\'; %计算信息比率的benchmark
risklessReturn=1+4e-2/365;
weightingMethod=1;% 0:等权重；1：流通市值加权
varout_infoRatio=3;
varout_maxDrawDown=2;
varout_index=1;
varout_matketCap=4;
varout_FF3=5;
varout_xlsResult=6;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 设定回测结束日期datenum给huicheEndDateNum
if nargin==1 || isempty(varargin)
huicheEndDateNum=today-1; % 设定回测的结束日期，结束日期设为当天之前的1天，就减去1
elseif isnumeric(varargin{1})
huicheEndDateNum=today-varargin{1};
elseif ischar(varargin{1})&&length(varargin{1})==6
huicheEndDateNum=monthEndDateNum(datenum([varargin{1},\'01\'],\'yyyymmdd\'));
elseif ischar(varargin{1})&&length(varargin{1})==7
huicheEndDateNum=monthEndDateNum(datenum([varargin{1},\'-01\'],\'yyyy-mm-dd\'));
elseif ischar(varargin{1})&&length(varargin{1})==8
huicheEndDateNum=datenum(varargin{1},\'yyyymmdd\');
elseif ischar(varargin{1})&&length(varargin{1})==10
huicheEndDateNum=datenum(varargin{1},\'yyyy-mm-dd\');
else
y=\'Not correct backtest end DATE format.\';
fprintf(\'%s\n\',y)
return
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
format longG
if isnan(raw{1,end})
raw(:,end)=[];
if size(raw,2)~=4
fprintf(\'Data format may be not correct. Verify it.\n\');
return;
end
end
raw=[raw,[{\'wind证券ID\'};cell(size(raw,1)-1,1)]];
% raw是一个5列的cell array，分别是：股票交易代码，中文简称，选股日期，权重因子，wind证券ID
tradeIDCol=1;
tradeCodeBeginBit=3;
chineseNameCol=2;
dateCol=3;
weightingFactorCol=4;
windIDCol=5;
conn=database(\'wind\',\'userName\',\'passWord\');
%将raw第3列的日期格式转变为与wind数据库相同的\'yyyymmdd\'形式，且以str类型存储
tmp=datenum(raw(2:end,dateCol),\'yyyy-mm-dd\');
raw(2:end,dateCol)=num2cell(datestr(tmp,\'yyyymmdd\'),2);
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 找出每只股票的windID，存入raw的第5列，如果一个组合为空仓，即没有购买股票，这个组合的wingID留空
raw(1,:)=[]; % 去掉raw的表头，即第一行
blankPortfolioRowNum=find(strcmp(\'AA000000\',raw(:,tradeIDCol))); % 约定空仓组合所在行的交易代码为‘AA000000’
blankPortfolio=raw(blankPortfolioRowNum,:);
raw(blankPortfolioRowNum,:)=[];
tradeID=cell2mat(raw(:,tradeIDCol));
tradeID=tradeID(:,tradeCodeBeginBit:end);
tradeID=num2cell(tradeID,2);
windIDAll=strconnec(tradeID);
tmp=fetch(conn,[\'select f2_1090 from tb_object_1090 where f4_1090=\'\'A\'\' and f16_1090 in (\',windIDAll,\') order by f16_1090\']);
[~,~,seq]=unique(tradeID);
raw(:,windIDCol)=tmp(seq,:);
raw=[blankPortfolio;raw];
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 找出各个组合在raw中对应的行范围，存储到portfolioRawRowScope，第一列为组合的开始行，第二列为组合的结束行
raw=sortrows(raw,dateCol);
[~,firstRowNum]=unique(raw(:,dateCol),\'first\');
[~,lastRowNum]=unique(raw(:,dateCol),\'last\');
portfolioRawRowScope=[firstRowNum,lastRowNum];
totalPortfolioNum=size(portfolioRawRowScope,1);
maxPortfolioCapacity=max(portfolioRawRowScope(:,2)-portfolioRawRowScope(:,1))+1;
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% y现在有maxPortfolioNum+3行，第一行对应交易日期，2:end-2行对应该交易日期组合的收盘价；
% 如果y第一行的值为0，那么该列对应的是组合的权重因子；end-1行为隔日收益率，end行为累计收益率
approxiamteBackTestMonthNum=ceil((huicheEndDateNum-datenum(raw{1,dateCol},\'yyyymmdd\'))/28);
y=zeros(maxPortfolioCapacity+3,approxiamteBackTestMonthNum*32);
marketCap=zeros(approxiamteBackTestMonthNum*32,2);
xlsResult=cell(maxPortfolioCapacity+3,approxiamteBackTestMonthNum*35);
yCol=0;
marketCapCol=0;
xlsResultCol=0;
for portfolioSN=1:totalPortfolioNum
% 确定该组合的结束日期
if datenum(raw{portfolioRawRowScope(portfolioSN,1),dateCol},\'yyyymmdd\')>=huicheEndDateNum
break
elseif portfolioSN<totalPortfolioNum && datenum(raw{portfolioRawRowScope(portfolioSN+1,1),dateCol},\'yyyymmdd\')<=huicheEndDateNum
endDateStr=raw{portfolioRawRowScope(portfolioSN+1,1),dateCol};
elseif portfolioSN<totalPortfolioNum && datenum(raw{portfolioRawRowScope(portfolioSN+1,1),dateCol},\'yyyymmdd\')>huicheEndDateNum
endDateStr=datestr(huicheEndDateNum,\'yyyymmdd\'); % 最新的组合持有到huicheEndDateNum的时间
elseif portfolioSN==totalPortfolioNum
endDateStr=datestr(huicheEndDateNum,\'yyyymmdd\');
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 一个组合是空仓
if isempty(raw{portfolioRawRowScope(portfolioSN,1),windIDCol})
tradeDay=fetch(conn,[\'select f1_1010 from tb_object_1010 where f1_1010 between \'\'\',...
raw{portfolioRawRowScope(portfolioSN,1),dateCol},\'\'\' and \'\'\',endDateStr,\'\'\' order by f1_1010\']);
yCol=yCol+1;
y(1,yCol+1:yCol+length(tradeDay))=str2double(tradeDay)\';
y(end-1,yCol:yCol+length(tradeDay))=1;
yCol=yCol+length(tradeDay);
{portfolioSN,size(portfolioRawRowScope,1),raw{portfolioRawRowScope(portfolioSN,1),dateCol},\'short\'}
continue;
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
currentPortfolioCapacity=portfolioRawRowScope(portfolioSN,2)-portfolioRawRowScope(portfolioSN,1)+1; %当前组合中的股票数目
currentPortfolioWindID=raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),windIDCol);
currentPortfolioWindID=strconnec(currentPortfolioWindID);
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 权重因子：weightingMethod==0：等权重；weightingMethod==1：流通市值加权
if weightingMethod==0
raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),weightingFactorCol)={1/currentPortfolioCapacity};
else
% 此处一个隐藏的bug是当一只股票在对应交易日的流通市值没有数据时，会提示错误而中断程序
mktCap=fetch(conn,[\'select f1_5004,f10_5004 from tb_object_5004 where f2_5004=\'\'\',...
raw{portfolioRawRowScope(portfolioSN,1),dateCol},\'\'\' and f1_5004 in (\',currentPortfolioWindID,\')\']);
if size(mktCap,1)~=currentPortfolioCapacity
no_mktCap=setdiff(raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),windIDCol),mktCap(:,1));
tmp=containers.Map(raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),windIDCol),...
raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),tradeIDCol));
no_mktCapID=values(tmp,no_mktCap);
fprintf(\'Can not fetch the Market Capacity for stocks %s on %s\n\',strconnec(no_mktCapID),raw{portfolioRawRowScope(portfolioSN,1),dateCol});
no_mktCap=fetch(conn,[\'select f1_5004,f10_5004 from tb_object_5004 where f2_5004<=\'\'\',...
raw{portfolioRawRowScope(portfolioSN,1),dateCol},\'\'\' and f1_5004 in (\',strconnec(no_mktCap),\') order by f2_5004\']);
[~,tmp]=unique(no_mktCap(:,1),\'last\');
no_mktCap=no_mktCap(tmp,:);
mktCap=[mktCap;no_mktCap];
end
cap=cell2mat(mktCap(:,2));
mktCap=containers.Map(mktCap(:,1),cap/sum(cap));
raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),weightingFactorCol)=...
values(mktCap,raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),windIDCol));
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% SQLserver的 BETWEEN……AND关键词给出的结果包含上下范围的边界
closingPrice=fetch(conn,[\'select f1_1425,f2_1425,f7_1425 from tb_object_1425 where f1_1425 IN (\',currentPortfolioWindID,...
\') AND (f2_1425 BETWEEN \'\'\',raw{portfolioRawRowScope(portfolioSN,1),dateCol},\'\'\' AND \'\'\',endDateStr,\'\'\')\']);
% closingPrice是cell array，有三列数据：wind证券ID（char类型），交易时间（char类型），复权收盘价（double类型）
% 如果wind数据库中没有一只股票某个交易日的收盘价数据，得到的tmp中也缺乏该股票这个交易日的数据。
% 修正办法：让该股票这个交易日的收盘价等于最近有数据的交易日的收盘价。
% 还有一个bug需要修正：有可能一只股票的数据在组合建立的第一天就没有；此bug已修正
uniqueWindID=sortrows(raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),windIDCol),1);
tradeSeriesMatrix=col2matrix(closingPrice);
noDataWindID=uniqueWindID(find(1-ismember(uniqueWindID,tradeSeriesMatrix(2:end,1))));
if ~isempty(noDataWindID)
fprintf(\'Caution: can not fetch closing price data for stock(s) %s from %s to %s. These datas are set to 1.\',...
cell2mat(strcat(strcat(noDataWindID,\',\')\')),raw{portfolioRawRowScope(portfolioSN,1),dateCol},endDateStr);
missed=[noDataWindID,num2cell(ones(length(noDataWindID),size(tradeSeriesMatrix,2)-1))]; %所有的交易日内都没有数据，收盘价统一设为1,该股票没有盈亏
tradeSeriesMatrix=[tradeSeriesMatrix(1,:);sortrows([tradeSeriesMatrix(2:end,:);missed],1)];
end
for tradeSeriesMatrixRow=2:size(tradeSeriesMatrix,1)
for tradeSeriesMatrixCol=3:size(tradeSeriesMatrix,2)
if isnan(tradeSeriesMatrix{tradeSeriesMatrixRow,tradeSeriesMatrixCol})
tradeSeriesMatrix{tradeSeriesMatrixRow,tradeSeriesMatrixCol}=tradeSeriesMatrix{tradeSeriesMatrixRow,tradeSeriesMatrixCol-1};
end
end
end
% 股票在组合建立当日就没有数据，收盘价设为此后最近有数据交易日的收盘价
for tradeSeriesMatrixRow=2:size(tradeSeriesMatrix,1)
lastNanCol=find(isnan(cell2mat(tradeSeriesMatrix(tradeSeriesMatrixRow,2:end))),1,\'last\')+1;
tradeSeriesMatrix(tradeSeriesMatrixRow,2:lastNanCol)=tradeSeriesMatrix(tradeSeriesMatrixRow,lastNanCol+1);
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
totalTradeDate=size(tradeSeriesMatrix,2)-1;
yCol=yCol+1;
%对raw排序，因为tradeSeriesMatrix对windid做了排序，不然权重因子对不上，add by van 20121227([email protected])
tmpArray=sortrows(raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),:),windIDCol);
y(2:currentPortfolioCapacity+1,yCol)=cell2mat(tmpArray(:,weightingFactorCol));
y(1,yCol+1:yCol+totalTradeDate)=str2double(tradeSeriesMatrix(1,2:end));
y(2:currentPortfolioCapacity+1,yCol+1:yCol+totalTradeDate)=cell2mat(tradeSeriesMatrix(2:end,2:end));
y(end,yCol:yCol+totalTradeDate)=[1,1,y(2:currentPortfolioCapacity+1,yCol)\'*...
(y(2:currentPortfolioCapacity+1,yCol+2:yCol+totalTradeDate)./repmat(y(2:currentPortfolioCapacity+1,yCol+1),1,totalTradeDate-1))];
y(end-1,yCol:yCol+totalTradeDate)=[1,1,y(end,yCol+2:yCol+totalTradeDate)./y(end,yCol+1:yCol+totalTradeDate-1)];
y(end-1,yCol+1)=y(end-1,yCol+1)*(1-tradeCost); % 增加买入交易成本
y(end-1,yCol+totalTradeDate)=y(end-1,yCol+totalTradeDate)*(1-tradeCost);% 增加卖出交易成本
yCol=yCol+totalTradeDate;
%%%%%%%%%%%%%%%%%%%%% 流通市值
if nargout>=varout_matketCap+1
currentMarketCap=marketCapFun(raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),windIDCol),...
raw{portfolioRawRowScope(portfolioSN,1),dateCol},endDateStr,conn);
if size(currentMarketCap,1)<size(tradeSeriesMatrix,2)-1
for i=1:size(currentMarketCap,1)
if ~ismember(num2str(currentMarketCap(i,1)),tradeSeriesMatrix(1,2:end))
fprintf(\'Can not fetch market capitalization data on %d for stocks in portfoio %d when caculating market Cap.\n\',...
currentMarketCap(i,1),portfolioSN);
end
end
end
%前一个组合的结束日期和后一个组合的开始日期在同一个交易日，在该日，策略的流通市值取后一个组合的流通市值，因此前一个组合的流通市值只取1:end-1
i=1-sign(str2double(tradeSeriesMatrix(1,end))-currentMarketCap(end,1));
marketCap(marketCapCol+1:marketCapCol+size(currentMarketCap,1)-i,:)=currentMarketCap(1:end-i,:);
marketCapCol=marketCapCol+size(currentMarketCap,1)-i;
end
%%%%%%%%%%%%%%%%%%%%% xlsresult
if nargout>=varout_xlsResult+1
xlsResult(1,xlsResultCol+1:xlsResultCol+4)={\'中文简称\',\'交易代码\',\'windID\',\'权重因子\'};
xlsResult(2:currentPortfolioCapacity+1,xlsResultCol+1:xlsResultCol+4)=...
sortrows(raw(portfolioRawRowScope(portfolioSN,1):portfolioRawRowScope(portfolioSN,2),[chineseNameCol,tradeIDCol,windIDCol,weightingFactorCol]),3);
xlsResult(:,xlsResultCol+5:xlsResultCol+4+totalTradeDate)=num2cell(y(:,yCol+1:yCol+totalTradeDate));
xlsResult(end-1,xlsResultCol+1:xlsResultCol+4+totalTradeDate)=[1,1,1,num2cell(y(end-1,yCol:yCol+totalTradeDate))];
xlsResultCol=xlsResultCol+4+totalTradeDate;
end
[portfolioSN,size(portfolioRawRowScope,1)]
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
y(:,yCol+1:end)=[];
if isempty(y)
fprintf(\'The backtest end-date is prior to the construction-date of the first portfolio.\n\');
return;
end
y(end,:)=cumprod(y(end-1,:));
y=y\';
spaceRow=find(y(:,1)==0);
y([1;spaceRow(2:end);spaceRow(2:end)+1],:)=[]; %将权重因子所在行全部去掉
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 信息比率information ratio
if nargout>=varout_infoRatio+1
marketIndex=idx(raw{1,dateCol},datestr(huicheEndDateNum,\'yyyymmdd\'),conn);
if strcmp(refIndexName,\'HS300\')
refIndex=marketIndex.HS300;
else
refIndex=marketIndex.SZZZ;
end
% eval([\'refIndex=marketIndex.\',refIndexName,\';\']);
if any(refIndex(:,1)~=y(:,1))
fprintf(\'Caution: Index date does not totally match with y date when caculating Information Ratio.\n\');
end
refIndex=refIndex(:,2);
indexDailyReturn=[1;refIndex(2:end)./refIndex(1:end-1)];
yMonth=floor(y(:,1)/100);
[~,firstRowNum]=unique(yMonth,\'first\');
[~,lastRowNum]=unique(yMonth,\'last\');
yMonth_YRowScope=[firstRowNum,lastRowNum];
portfolioMonthlyReturn=zeros(size(yMonth_YRowScope,1),1);
indexMonthlyReturn=zeros(size(yMonth_YRowScope,1),1);
for yMonthSN=2:size(yMonth_YRowScope,1)-1
stgMonthSN_YRow=yMonth_YRowScope(yMonthSN,1):yMonth_YRowScope(yMonthSN,2);
portfolioMonthlyReturn(yMonthSN)=prod(y(stgMonthSN_YRow,end-1));
indexMonthlyReturn(yMonthSN)=prod(indexDailyReturn(stgMonthSN_YRow));
end
portfolioMonthlyReturn([1,yMonthSN+1:end])=[];
indexMonthlyReturn([1,yMonthSN+1:end])=[];
monthlyTrackingError=portfolioMonthlyReturn-indexMonthlyReturn;
monthlyIR=zeros(size(monthlyTrackingError,1),2);
for yMonthSN=1:size(monthlyIR,1)-12
monthlyIR(yMonthSN,:)=[yMonth(yMonth_YRowScope(yMonthSN+1,1)),...
mean(monthlyTrackingError(yMonthSN:end))/std(monthlyTrackingError(yMonthSN:end))];
end
monthlyIR(yMonthSN+1:end,:)=[];
monthlyIR(:,2)=sqrt(12)*monthlyIR(:,2);
dailyTracingError=y(:,end-1)-indexDailyReturn;
dailyIR=zeros(size(y,1)-1,2);
dailyIR(1:end,1)=y(1:end-1,1);
for yRow=1:size(y,1)-1
dailyIR(yRow,2)=mean(dailyTracingError(yRow:end))/std(dailyTracingError(yRow:end));
end
dailyIR(:,2)=dailyIR(:,2)*sqrt(250);
varargout{varout_infoRatio}=struct(\'daily\',dailyIR,\'monthly\',monthlyIR);
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 最大回撤maxDrawDown
if nargout>=varout_maxDrawDown+1
yRowNum=size(y,1);
varargout{varout_maxDrawDown}=zeros(yRowNum-1,2); %varargout{varout_infoRatio}
for i=1:yRowNum-1
varargout{varout_maxDrawDown}(i,1)=maxdrawdown(y(i:end,end));
varargout{varout_maxDrawDown}(i,2)=maxdrawdown(refIndex(i:end));
end
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 市场指数
if nargout>=varout_index+1
marketIndex=idx(raw{1,dateCol},datestr(huicheEndDateNum,\'yyyymmdd\'),conn);
varargout{varout_index}=marketIndex;
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 流通市值
if nargout>=varout_matketCap+1
varargout{varout_matketCap}=marketCap(1:marketCapCol,:);
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% Famma French 三因素回归
if nargout>=varout_FF3+1
varargout{varout_FF3}=FF3(y(:,[1,end-1]),risklessReturn,conn);
end
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% xlsresult
if nargout>=varout_xlsResult+1
xlsResult(:,xlsResultCol+1:end)=[];
xlsResult(end-1:end,1)=[{\'隔日收益\'};{\'累计收益\'}];
xlsResult(end,2:end)=num2cell(cumprod(cell2mat(xlsResult(end-1,2:end))));
xlsResult=xlsResult\';
varargout{varout_xlsResult}=xlsResult;
end
close(conn);
end
function suby=strconnec(subx)
ssubx=subx(1:end-1);
ssubx=strcat(\'\'\'\',ssubx,\'\'\',\');
tmp=[\'\'\'\',subx{end},\'\'\'\'];
ssubx=[ssubx;tmp];
suby=cell2mat(ssubx\');
end
function sy=monthEndDateNum(sDateNum)
% 输入sDateNum是一个date number, 输出sy给出date number所在月最后一天的datenum
sx=datestr(sDateNum,\'yyyymm\');
endDay=eomday(str2double(sx(1:4)),str2double(sx(5:6)));
sy=datenum([sx,num2str(endDay)],\'yyyymmdd\');
end
function y=marketCapFun(windIDCell,fromDate,toDate,conn)
% windIDCell一列的cell
% fromDate, toDate是date sting，\'yyyymmdd\'形式
% y是一个两列的数值矩阵，给出从fromDate到toDate组合的流通市值之和，第一列为日期，第二列为市值
if isempty(windIDCell{1})
tradeDay=fetch(conn,[\'select f1_1010 from tb_object_1010 where f1_1010 between \'\'\',fromDate,\'\'\' and \'\'\',toDate,\'\'\' order by f1_1010\']);
fprintf(\'No stock in the portfolio. Market capitalization equals 0.\n\');
y=[str2double(tradeDay),zeros(length(tradeDay),1)];
return;
end
uniqueWindID=sortrows(windIDCell);
windID=strconnec(uniqueWindID);
marketCap=fetch(conn,[\'select f1_5004,f2_5004,f10_5004 from tb_object_5004 where f1_5004 in (\',windID,\') and (f2_5004 between \'\'\',fromDate,\'\'\' and \'\'\',toDate,\'\'\')\']);
tradeSeriesMatrix=col2matrix(marketCap);
noDataWindID=uniqueWindID(find(1-ismember(uniqueWindID,tradeSeriesMatrix(2:end,1))));
if ~isempty(noDataWindID)
fprintf(\'Caution: Can not fetch market capitalization data for stock(s) with windID %s from %s to %s. These data are set to 0.\',...
cell2mat(strcat(strcat(noDataWindID,\',\')\')),fromDate,toDate);
missed=[noDataWindID,num2cell(zeros(length(noDataWindID),size(tradeSeriesMatrix,2)-1))]; %所有的交易日内都没有数据，流通市值统一设为0
tradeSeriesMatrix=[tradeSeriesMatrix(1,:);srotrows([tradeSeriesMatrix(2:end,:);missed],1)];
end
for tradeSeriesMatrixRow=2:size(tradeSeriesMatrix,1)
for tradeSeriesMatrixCol=3:size(tradeSeriesMatrix,2)
if isnan(tradeSeriesMatrix{tradeSeriesMatrixRow,tradeSeriesMatrixCol})
tradeSeriesMatrix{tradeSeriesMatrixRow,tradeSeriesMatrixCol}=tradeSeriesMatrix{tradeSeriesMatrixRow,tradeSeriesMatrixCol-1};
end
end
end
% 股票在组合建立当日就没有数据，收盘价设为此后最近有数据交易日的收盘价
tradeSeriesMatrixData=cell2mat(tradeSeriesMatrix(2:end,2:end));
for tradeSeriesMatrixDataRow=1:size(tradeSeriesMatrixData,1)
lastNanCol=find(isnan(tradeSeriesMatrixData(tradeSeriesMatrixDataRow,:)),1,\'last\');
tradeSeriesMatrixData(tradeSeriesMatrixDataRow,1:lastNanCol)=tradeSeriesMatrixData(tradeSeriesMatrixDataRow,lastNanCol+1);
end
y=[str2double(tradeSeriesMatrix(1,2:end))\',sum(tradeSeriesMatrixData)\'];
end
function y=col2matrix(x)
% x是一个三列的cell，第一列和第二列为指标，第三列为值，如第一列为证券ID，第二列为一段时间之内的交易日，第三列为一只股票在某个交易日的收盘价
% y是一个cell，有unique(x(:,1))+1行和unique(x(:,2))+1列，y(2:end,1)等于unique(x(:,1)且按降序排列,
% y(1,2:end)等于unique(x(:,2))且按降序排列，y(i,j)的值等于y(i,1)所对应的x第一列指标和y(1,j)所对应的x第二列指标给出的x第三列中的值，若x中没有该值，y(i,j)=NaN
keyWindID=unique(x(:,1));
keyTradeDate=unique(x(:,2));
mapWindID=containers.Map(keyWindID,num2cell(1:length(keyWindID)));
mapTradeDate=containers.Map(keyTradeDate,num2cell(1:length(keyTradeDate)));
tradeDataMatrix=nan(length(keyWindID),length(keyTradeDate));
tradeDataMatrix(sub2ind(size(tradeDataMatrix),cell2mat(values(mapWindID,x(:,1))),cell2mat(values(mapTradeDate,x(:,2)))))=cell2mat(x(:,3));
y=nan(length(keyWindID)+1,length(keyTradeDate)+1);
y=num2cell(y);
y(2:end,1)=keyWindID;
y(1,2:end)=keyTradeDate\';
y(2:end,2:end)=num2cell(tradeDataMatrix);
end
function y=idx(fromDate,toDate,conn)
% y是一个是一个structure，每个field是一个数值列向量，给出从fromDate到toDate之间所有交易日的市场指数值
% HS300: S12425; 上证综指：1A0001
y.HS300=fch(\'S12425\');
y.SZZZ=fch(\'1A0001\');
function suby=fch(subx)
suby=fetch(conn,[\'select f2_1425,f7_1425 from tb_object_1425 where f1_1425=\'\'\',subx,...
\'\'\' and (f2_1425 between \'\'\',fromDate,\'\'\' and \'\'\',toDate,\'\'\') order by f2_1425\']);
suby=[str2double(suby(:,1)),cell2mat(suby(:,2))];
end
end
function yhat=FF3(x,risklessReturn,conn)
% x是一个两列的cell array，第一列为交易日期，第二列为策略隔日收益率，x等于主程序中y(:,[1,end-1])
% yhat是一个两列的矩阵，第一列是日期，第二列为FF3回归的到的累计收益。在由FF3计算隔日收益时，如果由于数据缺失而无法得到某天的收益，那么该日的收益等于x对应当日的真实收益
xorg=x;
totalDateNum=size(x,1);
SMB=zeros(totalDateNum,2);
HML=zeros(totalDateNum,2);
SMB_HML_sampleRatio=20e-2;
eachFetchDateNum=50;
fetchNum=floor(totalDateNum/eachFetchDateNum)+sign(mod(totalDateNum,eachFetchDateNum));
SMB_HML_num=0;
for fetchSN=1:fetchNum
[fetchSN,fetchNum,size(x,1)]
tic
fromDate_YRow=(fetchSN-1)*eachFetchDateNum+1
toDate_YRow=sign(fetchNum-fetchSN)*fetchSN*eachFetchDateNum+(1-sign(fetchNum-fetchSN))*totalDateNum
mktCap_PB=fetch(conn,[\'select t5.f2_5004,t5.f10_5004,t5.f15_5004,t5.f7_5004 from tb_object_5004 as t5 inner join tb_object_1425 as t1 \',...
\'on (t5.f1_5004=t1.f1_1425 and t5.f2_5004=t1.f2_1425) where (t5.f2_5004 between \'\'\',num2str(x(fromDate_YRow,1)),\'\'\' and \'\'\',...
num2str(x(toDate_YRow,1)),\'\'\') and t1.f11_1425=-1 order by t5.f2_5004\']);
if isempty(mktCap_PB)
fprintf(\'Caution: fetch no data from %d to %d when caculating FF3.\n\',x(fromDate_YRow,1),x(toDate_YRow,1));
continue;
end
mktCap_PB=[str2double(mktCap_PB(:,1)),cell2mat(mktCap_PB(:,2:end))];
% mktCap_PB_Data中会偶尔掺杂nan，去掉这些行
nanRow=prod(mktCap_PB,2);
mktCap_PB(find(isnan(nanRow)),:)=[];
mktCap_PB=sortrows(mktCap_PB,1);
[~,firstRow]=unique(mktCap_PB(:,1),\'first\');
[~,lastRow]=unique(mktCap_PB(:,1),\'last\');
[length(firstRow),length(lastRow)]
for dateSN=1:length(firstRow)
currentDateData=mktCap_PB(firstRow(dateSN):lastRow(dateSN),:);
sampleNum=floor(size(currentDateData,1)*SMB_HML_sampleRatio);
currentDateData=sortrows(currentDateData,2);
SMB(SMB_HML_num+1,:)=[currentDateData(1,1),mean(currentDateData(1:sampleNum,end)-currentDateData(end-sampleNum+1:end,end))/100];
currentDateData=sortrows(currentDateData,3);
HML(SMB_HML_num+1,:)=[currentDateData(1,1),mean(currentDateData(1:sampleNum,end)-currentDateData(end-sampleNum+1:end,end))/100];
SMB_HML_num=SMB_HML_num+1;
end
toc
end
SMB=SMB(1:SMB_HML_num,:);
HML=HML(1:SMB_HML_num,:);
indexHS300=fetch(conn,[\'select f2_1425,f7_1425 from tb_object_1425 where f1_1425=\'\'S12425\'\' and (f2_1425 between \'\'\',...
num2str(x(1,1)),\'\'\' and \'\'\',num2str(x(end,1)),\'\'\') order by f2_1425\']);
indexHS300=[str2double(indexHS300(:,1)),cell2mat(indexHS300(:,2))];
marketReturn=[1;indexHS300(2:end,2)./indexHS300(1:end-1,2)];
marketReturn=[indexHS300(:,1),marketReturn];
interTradeDay=intersect(intersect(x(:,1),marketReturn(:,1)),SMB(:,1));
SMB=SMB(find(ismember(SMB(:,1),interTradeDay)),:);
HML=HML(find(ismember(HML(:,1),interTradeDay)),:);
marketReturn=marketReturn(find(ismember(marketReturn(:,1),interTradeDay)),:);
x=x(find(ismember(x(:,1),interTradeDay)),:);
X=[ones(length(interTradeDay),1),marketReturn(:,2)-risklessReturn,SMB(:,2),HML(:,2)];
whichStats={\'beta\',\'yhat\',\'r\',\'rsquare\',\'adjrsquare\',\'tstat\',\'fstat\'};
stats=regstats(x(:,end)-risklessReturn,X(:,2:end),[],whichStats);
% regCoef=stats.beta;
% yhat=X*regCoef+risklessReturn;
yhat=stats.yhat+risklessReturn;
yhat=[interTradeDay,yhat];
xorg(find(ismember(xorg(:,1),interTradeDay)),:)=yhat; % 对于缺乏数据而不能给出FF3回归结果的交易日，当日的FF3收益等于组合的真实收益
yhat=sortrows(xorg,1);
yhat(:,2)=cumprod(yhat(:,2));
yhat=struct(\'yhat\',yhat,\'stats\',stats);
end
