Excel Timeframes

[jarlvdb]

Hell guys,
I am using Excel to test strategies. To break it down i got an entire list of minute data (open, high, low, close).
I am using formulas such as:
(SMA) =ALS(AANTALARG(EURUSD!$E$5:$E5)>A$3;GEMIDDELDE(VERSCHUIVING(EURUSD!$E5;0;0;-A$3));NB())
Where A$3 is the amount of periods.

I got most important indicators for me (sma, ema, macd, rsi, stochastic, volatility, price levels/fibos).
However i am at the point now to integrate it with higher timeframe scales. This requires grouping the minute data at exactly the correct places (eg: 23:00 --> 23:04 (for 5min)) or (23:00 --> 23:59 (for hour)) However i got no clue how to get this done :S

This is the A1 collom how i see what time it is: "zo 02 mei 2012 - 23:00" (ddd dd mmm jjjj - uu:mm)

Should I include the timeframe in my formula or transfer the data? And how ?

Thanks,
Jarl

[garyfritz]

I think that would be extremely difficult in basic Excel formulas, unless you could guarantee that you NEVER had holes in your data. It would still be clumsy.

For example if you wanted to simulate H1, you could look at every 60th M1 data point for the close, MAX and MIN (or AANTALARG or whatever to get the High and Low of the H1 bar, etc.

I suspect to do this properly you would have to write everything (including your strategy) in VBA.

Probably the easiest solution would be to convert your M1 data to H1 or H4 or whatever. Generate a new worksheet that has one H1 data point on each line instead of one M1 data point. You could do that with basic Excel formulas for example by creating 5 new columns next to your M1 data, and only filling in one out of every 60 lines. In that every-60-lines entry you would copy the M1 Open of 59 bars ago (the H1 Open), the MAX High and MIN Low of the last 60 M1 bars (the H1 High and Low), and the Close of the current M1 bar (the H1 close). Also include the timestamp for the H1 bar, probably taken from 59 M1 bars ago if you want to use the Empty4 standard of timestamping the start of the bar.

Then copy those 5 columns to a new sheet and sort them by the timestamp column -- and now you have a sheet with H1 data.

It would be possible to build a more complex worksheet that would extract any timeframe out of the M1 data, by accessing every N bars instead of every 60, but that would be a bigger job. Especially if you wanted to handle the Friday-to-Sunday gap properly.

[jarlvdb]

Ahh damn those gaps didnt even think of them.

It sounds like a lot of work so what is the best way to test my strategies etc?
In a nut shell i am basically using properties indicators to make a personal Logit model so i require the use of excel to get my data.

Should i get a closer look at VBA? What does this do actually because i am not very familiar with it.

For now i will change some data for 1 week so i dont get problem with teh gaps but i need a solution for the long run cos this is quite labor intensive.

Thanks for now!