# Trouble with calculating mean of historical data

3 visualizzazioni (ultimi 30 giorni)
Cary il 22 Lug 2015
Modificato: dpb il 25 Lug 2015
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 CommentoMostra -1 commenti meno recentiNascondi -1 commenti meno recenti
dpb il 23 Lug 2015
Modificato: dpb il 25 Lug 2015
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...
>> 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.

Accedi per commentare.

### Risposte (1)

Madhav Rajan il 24 Lug 2015
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.
Hope this helps.
##### 0 CommentiMostra -2 commenti meno recentiNascondi -2 commenti meno recenti

Accedi per commentare.

### Categorie

Scopri di più su Get Started with MATLAB in Help Center e File Exchange

### Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by