Trouble with calculating mean of historical data

Question

Cary 2015-7-22

0
链接

此问题的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/230832-trouble-with-calculating-mean-of-historical-data

编辑： dpb 2015-7-25

testmean.xlsx

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;

1 个评论
显示 -1更早的评论隐藏 -1更早的评论

dpb 2015-7-23

编辑：dpb 2015-7-25

在 MATLAB Online 中打开

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 2015-7-24

0
链接

此回答的直接链接

https://ww2.mathworks.cn/matlabcentral/answers/230832-trouble-with-calculating-mean-of-historical-data#answer_187071

在 MATLAB Online 中打开

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.