Trouble with calculating mean of historical data

I wrote a function that calculates the mean price of the last 15 mins of the trading day. In the attached excel file I get a mean of 55.23 but my function in MATLAB returns 55.32. I've been messing with this all day, and cannot figure out the answer for the difference. Can anyone tell me why the means are different in excel and MATLAB? Thank you.

function last15MinsOfDay=last15MinsOfDay(time,price)
% last15MinsOfDay takes the average of prices between 3:45 and 4:00.

timeStr=cellstr(datestr(time));
timeDbl=datevec(timeStr);
times=and(timeDbl(:,4)==14,timeDbl(:,5)>=46)+and(timeDbl(:,4)==15,timeDbl(:,5)==0);
priceIdx=find(times);
z=find(fwdshift(1,priceIdx)~=priceIdx+1);
z=[1; z];
mu=zeros(length(z),1);
for i = 1:length(z);
    while i < length(z)
        mu(i)=mean(price(priceIdx(z(i):z(i+1))));
        i=i+1;
    end
end
last15MinsOfDay=mu;

[dpb]

Isn't 3PM 1500 hrs, pilgrim???

I'd convert the time strings to datenums and use fractional portion >=15.75/24 (computed via datenum w/ numeric integer input fields to ensure consistent internal rounding, of course) instead of the convoluted ASCII string comparisons.

As far as the comparison, I took the spreadsheet and added the formula for the average and saved it then read it in Matlab...

>> x=xlsread('cary.xls');
>> mean(x(1:end-1,2))
ans =
 55.2358
>> mean(x(1:end-1,2))==x(end,2)
ans =
   1
>>

As shown, if you use the same numbers you (unsurprisingly I suppose) get the same value (down to the last significant bit, even).

Hence, your problem is you're not selecting all the values or some such; I'd fix up the selection process as noted above and fix the times to be correct before worrying about it further; I have a feeling if you change the selection computation the problem likely will go away.

Answer 1

[Madhav Rajan]

I understand that you want to calculate the mean of the last 15 minutes of the trading day.

Assuming that you are passing the historical data to the user defined "last15MinsOfDay" function which takes in the arguments 'time' and 'price', you could call the "mean" function for the 'prices' using logical indexing. With logical indexing you are only calculating the mean of those 'prices' at those indices whose value is '1' in the 'times' variable. This would also eliminate for loops and allow MATLAB to benefit from vectorization. You can refer the following example:

function last15MinsOfDay=last15MinsOfDay(time,price)
% last15MinsOfDay takes the average of prices between 3:45 and 4:00.
timeStr=cellstr(datestr(time));
timeDbl=datevec(timeStr);
times=and(timeDbl(:,4)==14,timeDbl(:,5)>=46)+and(timeDbl(:,4)==15,timeDbl(:,5)==0); 
last15MinsOfDay=mean(price(times~=0));

Using the data that you have provided in the 'testmean.xlsx' file, the MATLAB function yielded the value of 55.2358 which is similar to the mean that you calculated in MS Excel.

You can refer the following link for more information on logical indexing:

http://www.mathworks.com/help/matlab/ref/logical.html

You can refer the following link for more information on vectorization:

http://www.mathworks.com/help/matlab/matlab_prog/vectorization.html

Hope this helps.