VBA code to convert 5-second period data to 5-minute period data?

[danjuma]

[FONT=&quot]Hello,

I would be most grateful if somebody could come up with a VBA code for the following please.

I get stock quotes into an excel spreadsheet in the following format. The quotes are updated every 5 seconds, in essence the data is for a 5-second period:[/FONT]

[FONT=&quot]5-seconds period[/FONT]
<table class="MsoNormalTable" style="border-collapse:collapse;border:none;mso-border-alt:solid black .5pt; mso-border-themecolor:text1;mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:19.5pt;border:solid black 1.0pt; mso-border-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26">
</td> <td style="width:74.25pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="99"> [FONT=&quot]A[/FONT]
</td> <td style="width:88.3pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="118"> [FONT=&quot]B[/FONT]
</td> <td style="width:42.35pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]C[/FONT]
</td> <td style="width:42.4pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]D[/FONT]
</td> <td style="width:42.4pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]E[/FONT]
</td> <td style="width:42.4pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]F[/FONT]
</td> <td style="width:42.4pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]G[/FONT]
</td> <td style="width:42.4pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]H[/FONT]
</td> <td style="width:42.4pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]I[/FONT]
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:19.5pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26"> [FONT=&quot]1[/FONT]
</td> <td style="width:74.25pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="99"> [FONT=&quot]STOCK[/FONT]
</td> <td style="width:88.3pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="118"> [FONT=&quot]Date / Time[/FONT]
</td> <td style="width:42.35pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]Open[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]High[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]Low[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]Close[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]Volume[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]WAP[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]Count[/FONT]
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:19.5pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26"> [FONT=&quot]2[/FONT]
</td> <td style="width:74.25pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="99"> [FONT=&quot]ABC[/FONT]
</td> <td style="width:88.3pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="118"> [FONT=&quot]1/21/2009 14:49[/FONT]
</td> <td style="width:42.35pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]18.95[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]18.97[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]18.91[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]19.93[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]66[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]189.5[/FONT]
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57"> [FONT=&quot]13[/FONT]
</td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes"> <td style="width:19.5pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26"> [FONT=&quot] 3[/FONT]
</td> <td style="width:74.25pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="99"> [FONT=&quot] XYZ[/FONT]
</td> <td style="width:88.3pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="118">
</td> <td style="width:42.35pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57">
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57">
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57">
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57">
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57">
</td> <td style="width:42.4pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="57">
</td> </tr> </tbody></table> [FONT=&quot]

5-minutes period[/FONT]
<table class="MsoNormalTable" style="border-collapse:collapse;border:none;mso-border-alt:solid black .5pt; mso-border-themecolor:text1;mso-yfti-tbllook:1184;mso-padding-alt:0cm 5.4pt 0cm 5.4pt" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="mso-yfti-irow:0;mso-yfti-firstrow:yes"> <td style="width:19.3pt;border:solid black 1.0pt; mso-border-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26">
</td> <td style="width:41.95pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]J[/FONT]
</td> <td style="width:42.0pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]K[/FONT]
</td> <td style="width:42.0pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]L[/FONT]
</td> <td style="width:42.0pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]M[/FONT]
</td> <td style="width:49.5pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> [FONT=&quot]N[/FONT]
</td> <td style="width:42.0pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]O[/FONT]
</td> <td style="width:42.2pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-left:none;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]P[/FONT]
</td> </tr> <tr style="mso-yfti-irow:1"> <td style="width:19.3pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26"> [FONT=&quot]1[/FONT]
</td> <td style="width:41.95pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]Open[/FONT]
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]High[/FONT]
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]Low[/FONT]
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]Close[/FONT]
</td> <td style="width:49.5pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66"> [FONT=&quot]Volume[/FONT]
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]WAP[/FONT]
</td> <td style="width:42.2pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56"> [FONT=&quot]Count[/FONT]
</td> </tr> <tr style="mso-yfti-irow:2"> <td style="width:19.3pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26"> [FONT=&quot]2[/FONT]
</td> <td style="width:41.95pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:49.5pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.2pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> </tr> <tr style="mso-yfti-irow:3;mso-yfti-lastrow:yes"> <td style="width:19.3pt;border:solid black 1.0pt; mso-border-themecolor:text1;border-top:none;mso-border-top-alt:solid black .5pt; mso-border-top-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="26">
</td> <td style="width:41.95pt;border-top:none;border-left: none;border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:49.5pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="66">
</td> <td style="width:42.0pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> <td style="width:42.2pt;border-top:none;border-left:none; border-bottom:solid black 1.0pt;mso-border-bottom-themecolor:text1; border-right:solid black 1.0pt;mso-border-right-themecolor:text1;mso-border-top-alt: solid black .5pt;mso-border-top-themecolor:text1;mso-border-left-alt:solid black .5pt; mso-border-left-themecolor:text1;mso-border-alt:solid black .5pt;mso-border-themecolor: text1;padding:0cm 5.4pt 0cm 5.4pt" valign="top" width="56">
</td> </tr> </tbody></table>
[FONT=&quot]What I want to do is represent the data above in the 5-second period as 5-minute period instead. This means capturing 60 x 5-seconds period data, computing the required values and displaying them in a table in the format as above. This will involve storing each of the following parameters (Open, High, Low, Close, Volume, WAP and Count) into a variable, with the first data starting from a 5-minute time (i.e. 14:50 to 14:55, 14:55 to 15:00 etc).

I. If we start with the Open:
Capture value for the first 5-second period at say 14:50 (time can be specified by user in a cell) for stock ABC (or whatever stock is in cell A2.), and return this value in cell J2.[/FONT]

[FONT=&quot]2. For High:[/FONT]
[FONT=&quot]Capture value for the first 5-second period at say 14:50 (time can be specified by user in a cell). Continue to capture values for next 59 x 5-seconds period (so last 5-second period will be at 14:55). Then get the highest value amongst the captured 60 x 5-seconds period data, and return this value in cell K2.[/FONT]

[FONT=&quot]3. For Low:[/FONT]
[FONT=&quot]Do the same as in two, but return the lowest value in cell L2[/FONT]

[FONT=&quot]4. For Close:[/FONT]
[FONT=&quot]Capture value for the last 5-second period (this would be at 14:55, in this example), and return this value in cell M2.[/FONT]

[FONT=&quot]5. For Volume and count:[/FONT]
[FONT=&quot]This will be the total of the volumes for the 60 x 5-second periods. Return value in cell N2. Same for count – total of counts for the 60 x 5-second periods. Return value in cell P2.[/FONT]

[FONT=&quot]6. For WAP (weighted average price)[/FONT]
[FONT=&quot]I haven’t got a clue how to calculate this, so will leave this for now.[/FONT]


[FONT=&quot]Continue doing this and updating cells J2, K2, L2, M2, N2, O2 and P2 with new values every 60 x 5-second periods. NB. This is just for one stock in one row. I could have up to 30 stocks in 30 rows.[/FONT]

[FONT=&quot]Many thanks[/FONT]

 

[danjuma]

Sorted!