I have a data set looks like this:
'2014-01-07 22:20:00' [0.0016]
'2014-01-07 22:25:00' [0.0013]
'2014-01-07 22:30:00' [0.0017]
'2014-01-07 22:35:00' [0.0020]
'2014-01-07 22:40:00' [0.0019]
'2014-01-07 22:45:00' [0.0022]
'2014-01-07 22:50:00' [0.0019]
'2014-01-07 22:55:00' [0.0019]
'2014-01-07 23:00:00' [0.0021]
'2014-01-07 23:05:00' [0.0021]
'2014-01-07 23:10:00' [0.0026]
First column is the time stamp recording data everything 5 min, second column is return.
For each day, I want to calculate sum of squared 5 min bar returns. Here I define a day as from 5:00 pm - 5:00 pm. ( So date 2014-01-07 is from 2014-01-06 17:00 to 2014-01-07 17:00 ). So for each day, I would sum squared returns from 5:00 pm - 5:00 pm. Output will be something like:
'2014-01-07' [0.046]
'2014-01-08' [0.033]
How should I do this?
Here is alternative solution Just defining some randome data
t1 = datetime('2016-05-31 00:00:00','InputFormat','yyyy-MM-dd HH:mm:ss ');
t2 = datetime('2016-06-05 00:00:00','InputFormat','yyyy-MM-dd HH:mm:ss ');
Samples = 288; %because your sampling time is 5 mins
t = (t1:1/Samples:t2).';
X = rand(1,length(t));
First we find the sample which has the given criteria (Can be anything, In your case it was 00:05:00)
n = find(t.Hour >= 5,1,'first')
b = n;
Find the total number of days after the given sample
totaldays = length(find(diff(t.Day)))
and square and accumulate the 'return'for each day
for i = 1:totaldays - 1
sum_acc(i) = sum(X(b:b + (Samples - 1)).^2);
b = b + Samples;
end
This is just for visualization of the data
Dates = datetime(datestr(bsxfun(@plus ,datenum(t(n)) , 0:totaldays - 2)),'Format','yyyy-MM-dd')
table(Dates,sum_acc.','VariableNames',{'Date' 'Sum'})
Date Sum
__________ ______
2016-05-31 93.898
2016-06-01 90.164
2016-06-02 90.039
2016-06-03 91.676