Trading - Summarise 1 min data into 5 min data bars

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?

Answer 1

[Michael Teo]

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++.

[Steven]

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?

Answer 2

[Michael Teo]

% 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);

% construct empty block padding
aAbsDist = abs(eDist);
if islogical(eArray)
    aPad = false(aAbsDist,aColCount);
else
    aPad = NaN(aAbsDist,aColCount);
end

if eDist < 0
    % populate NaN to offset rows, columns
    oArray = [aPad; eArray];
    % remove data overflow (neg. dist)
    oArray(end+eDist+1:end,:) = [];
else
    % populate NaN to offset rows, columns
    oArray = [eArray; aPad];
    % remove data overflow
    oArray(1:eDist,:) = [];    
end