# Trading - Summarise 1 min data into 5 min data bars

18 views (last 30 days)
Steven on 29 Mar 2011
Hi,
I am trying to summarise Forex/Stock 1 minute data bars into 5 minute data bars. It is best described with an example.
GBP/USD 1 min data
DATE Time Open High Low Close
03/15/2011 659 1.61137 1.61137 1.61115 1.61115
03/15/2011 700 1.61109 1.61117 1.61105 1.61117
03/15/2011 701 1.6112 1.61128 1.61104 1.61116
03/15/2011 702 1.61116 1.61116 1.61083 1.61089
03/15/2011 703 1.61088 1.61093 1.61068 1.61084
03/15/2011 704 1.61083 1.61089 1.61074 1.61088
03/15/2011 705 1.61089 1.61102 1.61045 1.61055
03/15/2011 706 1.61055 1.61105 1.61053 1.61105
This is the data I am trying to create:-
GBP/USD 5 min data
DATE Time Open High Low Close
03/15/2011 705 1.6112 1.61128 1.61045 1.61055
To create the 7:05 5 min bar
OPEN = OPEN 7:01
CLOSE = CLOSE 7:05
HIGH = HIGHEST 7:01 - 7:05
LOW = LOWEST 7:01 - 7:05
I can easily get them as follows assuming the data is in M and I somehow start at 7:01
OPEN = M(1:5:end, 3); CLOSE = M(5:5:end, 6);
I have the HIGH and LOW in a for loop using max and min(I don't have the code I worked out right now)
The BIG problem I have is that there are often missing data so I cannot assume there are always 5 x 1 min data entries. Sometimes there can be 1 missing and other times 5-6 missing. I need to assume that the missing entries have the same values of the previous bars.
I suppose I either need to somehow fill in the missing values or come up with another way to solve the problem.
Sorry for the long question. Can anyone help?

Michael Teo on 31 Mar 2011
All roads lead to Rome and this will what i will do in form of vectorization instead of for-loop.
1. Compute the check point of the data:
aTimeVec = datevec(aTimes);
% compute the minutes of the day.
aMinDay = aTimeVec(:,4)*60 + aTimeVec(:,5) - 1;
% compute the factor by dividing the minutes of day by timeframe.
aMap = (aMinDay / eTimeframe);
% extract only the integer value of factor.
aMapInt = floor(aMap);
aMapIntPrev = offset(aMapInt, -1);
% determine change point based on timeframe.
aStartBits = (aMapInt ~= aMapIntPrev);
2. High price is trailing up (reset) at each checkpoint, Low price is trailing down at each checkpoint. For-loop in MATLAB is abit slow, so i will proposed to write the trailing up & down logic in C++.

#### 1 Comment

Steven on 31 Mar 2011
First of all, many thanks for chipping in with your answer :-)
I don't appear to have a function called offset. Can you provide it so I can test this out?

Michael Teo on 4 Apr 2011
% Offset the data array.
%{
For example, given an array of characters A, containing 'abcdef', one can
say that the element containing the letter 'c' has an offset of 2 from the
start of A.
@param
eArray - Data array.
eDist - Offset distance (optional, default -1 i.e. previous data).
%}
function [oArray] = offset(eArray, eDist)
if isempty(eArray)
oArray = [];
return;
end
if exist('eDist', 'var') == 0
eDist = -1; % previous data
end
% determine the number of columns
aColCount = cols(eArray);
aAbsDist = abs(eDist);
if islogical(eArray)
else
end
if eDist < 0
% populate NaN to offset rows, columns
% remove data overflow (neg. dist)
oArray(end+eDist+1:end,:) = [];
else
% populate NaN to offset rows, columns
% remove data overflow
oArray(1:eDist,:) = [];
end