function [BalanceSum,BalanceDetail]=readData(file,sheet)
%读取结算单数据
%----------------------------------
%BalanceSum输出交易汇总数据
%InitialValue:上月结存
%FinalValue:当月结存
%InoutDate:出入金发生日期
%InMoney:入金
%OutMoney:出金
%DayTradeDate:交易日期
%DayTradePrice:成交价
%DayTradeNumber:手数
%DayTradeAmount:成交额
%DayTradefee:手续费
%DayProfit:平仓盈亏
%Positions:上月末持仓情况，第一列买持仓，第二列卖持仓
%AveragePrice：上月末持仓均价，第一列买均价，第二列卖均价
%-----------------------------------
%BalanceDetail输出交易明细数据
%DateTime:交易时间
%Contract:合约名称
%OpenClear:1表示开仓，-1表示平仓
%TradePrice:成交价
%BuySell:1表示开仓买入，0表示开仓卖出
%TradeNumber:手数
%TradeAmount:成交额
%TradeFee:手续费
%TradeProfit:平仓盈亏
%-----------------------------------
%file表示文件地址
%sheet表示明细数据所在sheet
%-----------------------------------
%2014/8/21/HuangZhaohua code
[~,~,raw] = xlsread(file);
[~,txt1,raw1] = xlsread(file,sheet);
[r0,~] = find((strcmp(raw,'出入金明细'))~=0);
[r1,~] = find((strcmp(raw,'成交汇总'))~=0);
[r2,~] = find((strcmp(raw,'持仓汇总'))~=0);
initialvalue = raw{(r0-7),3}; %上月结存
finalvalue = raw{(r0-6),3}; %本月结存
if (r1-3) < (r0+2)
inmoney = [];
outmoney = [];
inoutdate =[];
elseif (r1-3) >= (r0+2)
inmoney = raw((r0+2):(r1-3),3);
a = length(inmoney);
for i = 1:a
if isempty(inmoney{i}) == 1
inmoney{i} = 0;
end
end
inmoney = cell2mat(inmoney); %入金
outmoney = raw((r0+2):(r1-3),5);
c = length(outmoney);
for i=1:c
if isempty(outmoney{i}) == 1
outmoney{i} = 0;
end
end
outmoney = cell2mat(outmoney); %出金
inoutdate = raw((r0+2):(r1-3),1);
inoutdate = datenum(inoutdate); %出入金发生日期
end
tradedate = raw((r1+2):(r2-3),1);
tradedate = datenum(tradedate); %交易日期
tradeprice = [raw{(r1+2):(r2-3),5}]; %成交价
tradenumber = [raw{(r1+2):(r2-3),6}]; %交易手数
tradefee = [raw{(r1+2):(r2-3),9}]; %交易手续费
tradeamount = [raw{(r1+2):(r2-3),7}]; %成交额
index1 = strcmp(raw((r1+2):(r2-3),10),'--');
profit = zeros((r2-r1-4),1);
for i = 1:(r2-r1-4)
if index1(i) == 1
profit(i) = 0;
else profit(i) = raw{i+r1+1,10};
end
end
[r3,~] = find((strcmp(raw1,'交易日期'))~=0);
[r4,~] = find((strcmp(raw1,'合计'))~=0);
rdate = raw1((r3+1):(r4-1),1);
time = raw1((r3+1):(r4-1),4);
kg = cell(r4-r3-1,1);
kg(:) = {' '};
date_time = strcat(rdate,kg,time);
test = datestr(date_time);
date = datenum(rdate); %交易日期
datetime = datenum(test); %交易时间
trade_price = [raw1{(r3+1):(r4-1),7}]; %成交价
trade_number = [raw1{(r3+1):(r4-1),8}]; %成交手数
trade_amount = [raw1{(r3+1):(r4-1),9}]; %成交额
trade_fee = [raw1{(r3+1):(r4-1),11}]; %手续费
index2 = strcmp(raw1((r3+1):(r4-1),12),'--');
trade_profit = zeros((r4-r3-1),1);
for i = 1:(r4-r3-1)
if index2(i) == 1
trade_profit(i) = 0;
else trade_profit(i) = raw1{i+r3,12};
end
end
buy_sell = raw1((r3+1):(r4-1),5);
buysell = zeros((r4-r3-1),1); %开仓买入用1表示，开仓卖出用0表示
for i = 1:(r4-r3-1)
if strcmp(buy_sell{i},'买')
buysell(i) = 1;
else buysell(i) = 0;
end
end
open_clear = raw1((r3+1):(r4-1),10);
openclear = zeros((r4-r3-1),1); %开仓用1表示，平仓用-1表示
for i = 1:(r4-r3-1)
if strcmp(open_clear{i},'开')
openclear(i) = 1;
else openclear(i) = -1;
end
end
contract = txt1((r3+1):(r4-1),2);
contractcell{1,1} = contract;
r5 = length(raw(:,1));
%buykeep上月末多头持仓量，buyprice多头持仓均价；sellkeep上月末空头持仓量，sellprice空头持仓均价
%keepcontract上月末持仓合约
%settle上月末持仓合约结算价
if (r2+2) > (r5-3)
buykeep = [];
sellkeep = [];
buyprice = [];
sellprice = [];
settleprice = [];
keepcontract='';
else
keepcontract = raw((r2+2):(r5-3),1);
settleprice = cell2mat(raw((r2+2):(r5-3),7));
index3 = strcmp(raw((r2+2):(r5-3),2),'');
buykeep = zeros((r5-r2-4),1);
buyprice = zeros((r5-r2-4),1);
for i = 1:(r5-r2-4)
if index3(i) == 1
buykeep(i) = 0;
buyprice(i) = 0;
else buykeep(i) = raw{i+r2+1,2};
buyprice(i) = raw{i+r2+1,3};
end
end
index4 = strcmp(raw((r2+2):(r5-3),4),'');
sellkeep = zeros((r5-r2-4),1);
sellprice = zeros((r5-r2-4),1);
for i = 1:(r5-r2-4)
if index4(i) == 1
sellkeep(i) = 0;
sellprice(i) = 0;
else sellkeep(i) = raw{i+r2+1,4};
sellprice(i) = raw{i+r2+1,5};
end
end
end
keepcontractcell{1,1} = keepcontract;
position = [buykeep,sellkeep];
averageprice = [buyprice,sellprice];
BalanceSum = struct('InitialValue',initialvalue,'FinalValue',finalvalue,...
'InoutDate',inoutdate,'InMoney',inmoney,...
'OutMoney',outmoney,'DayTradeDate',tradedate,...
'DayTradePrice',tradeprice,'DayTradeNumber',tradenumber,...
'DayTradeAmount',tradeamount,'DayTradefee',tradefee,...
'DayProfit',profit,'Positions',position,'SettlePrice',settleprice,...
'AveragePrice',averageprice,'KeepContract',keepcontractcell);
BalanceDetail = struct('
评论0