基于Matlab和Wind SQL数据库的通用选股策略回测程序
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(van.sun.cn@hotmail.com)
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
