1 second rtd data to 1,15, and 60 minute intervals

[jetpack]

I have rtd data in the following format copied at 1 sec intervals;

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Open[/TD]
[TD="width: 64"]High [/TD]
[TD="width: 64"]Low [/TD]
[TD="width: 64"]CLOSE[/TD]
[/TR]
[TR]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[TD="align: right"]90[/TD]
[/TR]
[TR]
[TD="align: right"]94.5[/TD]
[TD="align: right"]94.5[/TD]
[TD="align: right"]94.5[/TD]
[TD="align: right"]94.5[/TD]
[/TR]
[TR]
[TD="align: right"]83[/TD]
[TD="align: right"]83[/TD]
[TD="align: right"]71.5[/TD]
[TD="align: right"]71.5[/TD]
[/TR]
[TR]
[TD="align: right"]80.5[/TD]
[TD="align: right"]80.5[/TD]
[TD="align: right"]80.5[/TD]
[TD="align: right"]80.5[/TD]
[/TR]
[TR]
[TD="align: right"]67.5[/TD]
[TD="align: right"]67.5[/TD]
[TD="align: right"]67.5[/TD]
[TD="align: right"]67.5[/TD]
[/TR]
[TR]
[TD="align: right"]63.5[/TD]
[TD="align: right"]73.75[/TD]
[TD="align: right"]43[/TD]
[TD="align: right"]51.5[/TD]
[/TR]
[TR]
[TD="align: right"]68.75[/TD]
[TD="align: right"]68.75[/TD]
[TD="align: right"]46[/TD]
[TD="align: right"]46[/TD]
[/TR]
[TR]
[TD="align: right"]47.75[/TD]
[TD="align: right"]47.75[/TD]
[TD="align: right"]35.75[/TD]
[TD="align: right"]35.75[/TD]
[/TR]
[TR]
[TD="align: right"]52[/TD]
[TD="align: right"]62.5[/TD]
[TD="align: right"]26.5[/TD]
[TD="align: right"]31.5[/TD]
[/TR]
[TR]
[TD="align: right"]47.75[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]35.25[/TD]
[TD="align: right"]40[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

I need to group the data in a new sheet for each new time interval; 1,15 and 60 minutes.

The data is copied with the newest data added to a new last row.

the resulting data will be used in a dynamic chart.

 

[Gerald Higgins]

OK . . . what exactly do you need help with ?

What do you want the results to look like ?

 

[jetpack]

OK . . . what exactly do you need help with ?

What do you want the results to look like ?

thank you very much for being willing to have a go at this...

I need help in automating the process of finding the start and end of the intervals and copying the result to a new sheets with vba.

the results will be in this format, with the last row showing, as an example, the formulas to be used to return the values for a 1 minute(60 second) interval;
[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64, align: right"][TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"][TABLE="width: 314"]
<colgroup><col><col span="2"><col></colgroup><tbody>[TR]
[TD]open[/TD]
[TD]high[/TD]
[TD]low[/TD]
[TD]close[/TD]
[/TR]
[TR]
[TD]90[/TD]
[TD]94.5[/TD]
[TD]26.5[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]A1[/TD]
[TD]MAX(A1:A60)[/TD]
[TD]MIN(A1:A60)[/TD]
[TD]A60
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[TD="width: 64, align: right"][/TD]
[/TR]
</tbody>[/TABLE]

since the chart will be candlestick, it requires OHLC for each candle, therefore 4 values for each interval. data would need to be copied into a table, if possible, to automate the dynamic capability, or if there is a better method, better still.

don't know if it's possible to create a chart on the same sheet as each interval with vba or not?

thanks again for your time.

 

[Gerald Higgins]

OK . . . so how DO you find the start and end of the intervals ?

 

[jetpack]

g'day d'ya.

if doing it manually, I would follow the logic in the example. since the rtd is in 1 second interval, for a 1 minute conversion, I would start at A1 and evaluate 60 rows. that would give me the converted values for a 1 minute(60 second) interval. to find the 15 minute interval I would do the same except the lookup would be A1:A900, 60(seconds) x 15 minutes, etc. every iteration would be a multiple of the starting value; 60,120,180,etc.

I don't have a problem with the formulae, just not conversant in vba to automate the task to the end of the rows.

thanks so much for spending your energies to help me on this mate. really appreciate it.