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

D

danjuma

Active Member
Joined
Sep 21, 2008
Messages
251
[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]
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
Doing this by hand would be overkill... you need to look into pivottables, which are meant for just such tasks...

Simply follow the pivottable wizard and afterwards apply grouping on your datetimes of 5 minute intervals and the pivottable does all the calculations for you...

If you haven't made pivottables before, google them, there are plenty good tutorials out there.
 
Upvote 0
Just had a quick look at pivot tables, and I am not sure how this can be used without VBA? The cells in B2 to I2 and constantly updating and changing every 5 seconds, so the values have to be stored somewhere automatically for use, or am I missing something?
 
Upvote 0
I see, I guess I misread/misunderstood...
I thought you would be working with the resulting data, not one row that is continuously updated...

You want to see this resulting data in 5 minute intervals in realtime while the 5-second data is still flowing in? Sounds like a real challenge, with which I cannot help you I'm afraid.
 
Upvote 0
Ok, trying to code this myself. The idea of my code below is to go through rows 8 to 68 and return the Open, High, Low, Close and WAP at 5minute intervals. For some reason, the code just seems to go through one row, and also only returns the Open and High for the one row. What is wrong with the code please? The code is quite lengthy and without proper explanation might be difficult to understand. However, at this stage, I just want to find out why it does not seem to be scrolling through the rows. Many thanks.

Code: 
Private Sub CommandButton1_Click()
Dim r As Integer, CVal As Integer
With Worksheets(16)
Range("S1").value = "start"
Range("AC8:AG68").value = 0
While Range("S1").value = "start"
  For r = 8 To 68
  CVal = Cells(1, 25).value            'Column where  extracted minutes value are
   If Cells(r, 24).value = "Y" Then     'checks cell contains a stock and correct minutes
    Select Case CVal
    Case 0
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 50
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 455 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
     
    Case 5
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 100
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 955 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
     
   Case 10
    Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 150
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 1455 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
    
    Case 15
      Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 200
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 1955 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
    Case 20
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 250
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 2455 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
      
    Case 25
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 300
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 2955 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
    Case 30
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 350
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 3455 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
    Case 35
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 400
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 3955 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
    Case 40
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 450
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 4455 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If



Case 45
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 500
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 4955 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
    Case 50
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 550
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 5455 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
      
    Case 55
     Cells(r, 29).value = Cells(r, 15).value  'Open
     While Cells(r, 28).value <> 0
      If Cells(r, 16).value > Cells(r, 30).value Then  'High
      Cells(r, 30).value = Cells(r, 16).value
      End If
      If Cells(r, 17).value < Cells(r, 31).value Then 'Low
      Cells(r, 31).value = Cells(r, 17).value
      End If
     DoEvents
     Wend
     If Cells(r, 28).value = 5955 Then
     Cells(r, 33).value = Cells(r, 20).value 'WAP
     Cells(r, 32).value = Cells(r, 18).value 'Close
     End If
    End Select
   End If
 Next
 DoEvents
 Wend
 End With
End Sub
 
Upvote 0
Ok, looks like quite a few errors and wrong logic in the code above, please ignore for now. Thanks.
 
Upvote 0
Still chipping away at this, but getting somewhere. My problem with the code so far is that the columns for the High, Low and Volume just keep permanent updating as if stuck in an endless loop with the spreadsheet becoming non-responsive as a result. If somebody has a minute to have a look at my code below to see what might be the cause. I would be most grateful.

Many thanks.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("N8:N68")) Is Nothing Then
Application.EnableEvents = False
Dim r As Integer
With Worksheets(16)
If Range("S1").value = "start" Then
 Range("AC8:AH68").value = 0 'reset cells to zero at the start
 For r = 8 To 68
  If Cells(r, 24).value = "Y" Then     'checks cell contains a ticker i.e. cell not empty
    If Cells(r, 23).value = TimeSerial(0, 0, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 0, 0) And Cells(r, 23).value <= TimeSerial(0, 4, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 4, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 5, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 5, 0) And Cells(r, 23).value <= TimeSerial(0, 9, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 9, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    
     If Cells(r, 23).value = TimeSerial(0, 10, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 10, 0) And Cells(r, 23).value <= TimeSerial(0, 14, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value  'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 14, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 15, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 15, 0) And Cells(r, 23).value <= TimeSerial(0, 19, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 19, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 20, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 20, 0) And Cells(r, 23).value <= TimeSerial(0, 24, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 24, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 25, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 25, 0) And Cells(r, 23).value <= TimeSerial(0, 29, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 29, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
     If Cells(r, 23).value = TimeSerial(0, 30, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 30, 0) And Cells(r, 23).value <= TimeSerial(0, 34, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 34, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 35, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 35, 0) And Cells(r, 23).value <= TimeSerial(0, 39, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 39, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 40, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 40, 0) And Cells(r, 23).value <= TimeSerial(0, 44, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 44, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 45, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 45, 0) And Cells(r, 23).value <= TimeSerial(0, 49, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 49, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    
    If Cells(r, 23).value = TimeSerial(0, 50, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 50, 0) And Cells(r, 23).value <= TimeSerial(0, 54, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 54, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    
    If Cells(r, 23).value = TimeSerial(0, 55, 0) Then
     Cells(r, 29).value = Cells(r, 15).value  'Open price
     Cells(r, 30).value = 0
     Cells(r, 31).value = 0
     Cells(r, 34).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 55, 0) And Cells(r, 23).value <= TimeSerial(0, 59, 55) Then
        Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
        If Cells(r, 16).value > Cells(r, 30).value Then  'High price
        Cells(r, 30).value = Cells(r, 16).value
        End If
        If Cells(r, 31).value = 0 Then                    'Low price
        Cells(r, 31).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 31).value Then
        Cells(r, 31).value = Cells(r, 17).value
        End If
    End If
    If Cells(r, 23).value = TimeSerial(0, 59, 55) Then
        Cells(r, 33).value = Cells(r, 20).value 'WAP
        Cells(r, 32).value = Cells(r, 18).value 'Close price
    End If
    
    End If
   Next r
  End If
 End With
Application.EnableEvents = True
End If
End Sub
 
Upvote 0
Ok, not getting much joy here. Would somebody be kind enough to let me know whether the following code is logical please?

If Cells(r, 23).value >= TimeSerial(0, 50, 0) And Cells(r, 23).value <= TimeSerial(0, 54, 55) Then
Cells(r, 34).value = Cells(r, 34).value + Cells(r, 19).value 'volume
If Cells(r, 16).value > Cells(r, 30).value Then 'High price
Cells(r, 30).value = Cells(r, 16).value
End If
If Cells(r, 31).value = 0 Then 'Low price
Cells(r, 31).value = Cells(r, 17).value
ElseIf Cells(r, 17).value < Cells(r, 31).value Then
Cells(r, 31).value = Cells(r, 17).value
End If
End If

With the first line in bold, basically I want cells(r,34) to be the current total volume, so value in cells(r,19) added to current value in cells(r,34). cells(r,19) is updated every 5 secs.

With the second line in bold, if the current value in cells(r,16) (which is updated every 5 secs) is greater than the current value in cells(r,30), then the cells(r,30) takes the current value in cells(r,16).

The rest of the lines in bold are self-explanatory.

My question is, is the coding above correct, or do I need to assign variables to do this instead. If that's the case, how would I assign/declare the variables, as the cells are on different rows 'r' where 'r' is from 8 to 68, if you know what I mean?

Many thanks
 
Upvote 0
Ok, I have managed to get this coded myself and seems to be doing what I want it to do. Unfortunately I am having a bit of problem with the next stage. I am plodding along, but in the meantime will appreciate some assistance from anyone willing to help please. A quick summary:

Firstly, I am still a novice in VBA and some of my coding is probably not the most efficient way to do things, and just doing more by trial and error.

The code below is the main code I am using to capture the 5-second bars to make them into 5-min bars. I have put this in a module as it seems the only way I could get it work with a sort of timer I was using to call the sub every second. The variable 'r' I have declared Public in the module as I am using it for both this sub and another sub called 'PasteValues', which is also inserted in the same module.

Like I have already stated, the capturing of the 5-sec period data into every 5-min period seems to be working fine. The next stage for me was to capture these values into columns AI:AN for open one column at a time, AO:AT for high one column at a time, AU:AZ for low one column at a time, BA:BF for close one column at a time, and BG:BL for WAP one column at a time. And when all the 6 columns in each is populated, then move the values from the columns to the right to the columns to the left, with the 6th column for each (open, high, low, close, WAP) having the most recent values. So basically, the 6th column will have the most recent value, and 1st column will have the oldest value, if you know what I mean.

The logic I was using for my code is to use CountBlank function to count the number cells, for each row, in just columns AI:AN, and paste values to the relevant column depending on how many columns already have values. So the idea is say we take as example row 8, so first time around, row 8, column AI will be empty, so paste values in this column, second time around, paste values in row 8 column AJ, third time, row 8 column AK, and so on until it gets to row 8 col AN, after which it starts shifting the values from right to left. So value row 8 column AN jumps to row 8 column AM, and value in row 8 column AM jumps to row 8 column AL and so on.

What is happening, however, is that all the columns seem to be populated at once with the same value, and not one at a time with different values? The codes related to what I am trying to do that's giving me problem is the bolded one in the CaptureBars sub, and the sub PasteValues.

Many thanks.

Code: 
Sub CaptureBars()
Dim mycount As Long
With Worksheets(16)
 For r = 8 To 68
   If Cells(r, 24).value = "Y" Then     'checks cell contains a ticker i.e. cell not empty
    [COLOR=Blue][B] mycount = Application.WorksheetFunction.CountBlank(Range(Cells(r, 35), Cells(r, 40)))
     Cells(r, 33).value = mycount[/B][/COLOR]
    If Cells(r, 23).value = TimeSerial(0, 0, 0) Then  'cells(r,23) contains the mm:ss extracted from Col N
     Cells(r, 26).value = Cells(r, 15).value  'Open price
     Cells(r, 27).value = 0
     Cells(r, 28).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 0, 0) And Cells(r, 23).value < TimeSerial(0, 5, 0) And Cells(r, 26).value <> 0 Then
        If Cells(r, 16).value > Cells(r, 27).value Then  'High price
        Cells(r, 27).value = Cells(r, 16).value
        End If
        If Cells(r, 28).value = 0 Then                    'Low price
        Cells(r, 28).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 28).value Then
        Cells(r, 28).value = Cells(r, 17).value
        End If
        If Cells(r, 23).value = TimeSerial(0, 4, 55) Then
        Cells(r, 30).value = Cells(r, 20).value 'WAP
        Cells(r, 29).value = Cells(r, 18).value 'Close price
        PasteValues
        End If
    End If
    
    If Cells(r, 23).value = TimeSerial(0, 5, 0) Then
     Cells(r, 26).value = Cells(r, 15).value  'Open price
     Cells(r, 27).value = 0
     Cells(r, 28).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 5, 0) And Cells(r, 23).value < TimeSerial(0, 10, 0) And Cells(r, 26).value <> 0 Then
        If Cells(r, 16).value > Cells(r, 27).value Then  'High price
        Cells(r, 27).value = Cells(r, 16).value
        End If
        If Cells(r, 28).value = 0 Then                    'Low price
        Cells(r, 28).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 28).value Then
        Cells(r, 28).value = Cells(r, 17).value
        End If
        If Cells(r, 23).value = TimeSerial(0, 9, 55) Then
        Cells(r, 30).value = Cells(r, 20).value 'WAP
        Cells(r, 29).value = Cells(r, 18).value 'Close price
        PasteValues
        End If
    End If
,
,
,
,
,
,
If Cells(r, 23).value = TimeSerial(0, 55, 0) Then
     Cells(r, 26).value = Cells(r, 15).value  'Open price
     Cells(r, 27).value = 0
     Cells(r, 28).value = 0
    End If
    If Cells(r, 23).value >= TimeSerial(0, 55, 0) And Cells(r, 23).value <> TimeSerial(0, 0, 0) And Cells(r, 26).value <> 0 Then
        If Cells(r, 16).value > Cells(r, 27).value Then  'High price
        Cells(r, 27).value = Cells(r, 16).value
        End If
        If Cells(r, 28).value = 0 Then                    'Low price
        Cells(r, 28).value = Cells(r, 17).value
        ElseIf Cells(r, 17).value < Cells(r, 28).value Then
        Cells(r, 28).value = Cells(r, 17).value
        End If
        If Cells(r, 23).value = TimeSerial(0, 59, 55) Then
        Cells(r, 30).value = Cells(r, 20).value 'WAP
        Cells(r, 29).value = Cells(r, 18).value 'Close price
        PasteValues
        End If
    End If
    
    End If
   Next r
 End With
End Sub
Code: 
Sub PasteValues()
With Worksheets(16)
Select Case Cells(r, 33).value
Case 6
Cells(r, 35).value = Cells(r, 26).value 'open
Cells(r, 41).value = Cells(r, 27).value 'high
Cells(r, 47).value = Cells(r, 28).value 'low
Cells(r, 53).value = Cells(r, 29).value 'close
Cells(r, 59).value = Cells(r, 30).value 'WAP
Case 5
Cells(r, 36).value = Cells(r, 26).value 'open
Cells(r, 42).value = Cells(r, 27).value 'high
Cells(r, 48).value = Cells(r, 28).value 'low
Cells(r, 54).value = Cells(r, 29).value 'close
Cells(r, 60).value = Cells(r, 30).value 'WAP
Case 4
Cells(r, 37).value = Cells(r, 26).value 'open
Cells(r, 43).value = Cells(r, 27).value 'high
Cells(r, 49).value = Cells(r, 28).value 'low
Cells(r, 55).value = Cells(r, 29).value 'close
Cells(r, 61).value = Cells(r, 30).value 'WAP
Case 3
Cells(r, 38).value = Cells(r, 26).value 'open
Cells(r, 44).value = Cells(r, 27).value 'high
Cells(r, 50).value = Cells(r, 28).value 'low
Cells(r, 56).value = Cells(r, 29).value 'close
Cells(r, 62).value = Cells(r, 30).value 'WAP
Case 2
Cells(r, 39).value = Cells(r, 26).value 'open
Cells(r, 46).value = Cells(r, 27).value 'high
Cells(r, 51).value = Cells(r, 28).value 'low
Cells(r, 57).value = Cells(r, 29).value 'close
Cells(r, 63).value = Cells(r, 30).value 'WAP
Case 1
Cells(r, 40).value = Cells(r, 26).value 'open
Cells(r, 47).value = Cells(r, 27).value 'high
Cells(r, 52).value = Cells(r, 28).value 'low
Cells(r, 58).value = Cells(r, 29).value 'close
Cells(r, 64).value = Cells(r, 30).value 'WAP
Case 0
Cells(r, 35).value = Cells(r, 36).value 'open
Cells(r, 36).value = Cells(r, 37).value
Cells(r, 37).value = Cells(r, 38).value
Cells(r, 38).value = Cells(r, 39).value
Cells(r, 39).value = Cells(r, 40).value
Wait
Cells(r, 40).value = Cells(r, 26).value

Cells(r, 41).value = Cells(r, 42).value 'high
Cells(r, 42).value = Cells(r, 43).value
Cells(r, 43).value = Cells(r, 44).value
Cells(r, 44).value = Cells(r, 45).value
Cells(r, 45).value = Cells(r, 46).value
Wait
Cells(r, 46).value = Cells(r, 27).value

Cells(r, 47).value = Cells(r, 48).value 'low
Cells(r, 48).value = Cells(r, 49).value
Cells(r, 49).value = Cells(r, 50).value
Cells(r, 50).value = Cells(r, 51).value
Cells(r, 51).value = Cells(r, 52).value
Wait
Cells(r, 52).value = Cells(r, 28).value

Cells(r, 53).value = Cells(r, 54).value 'close
Cells(r, 54).value = Cells(r, 55).value
Cells(r, 55).value = Cells(r, 56).value
Cells(r, 56).value = Cells(r, 57).value
Cells(r, 57).value = Cells(r, 58).value
Wait
Cells(r, 58).value = Cells(r, 29).value

Cells(r, 59).value = Cells(r, 60).value 'WAP
Cells(r, 60).value = Cells(r, 61).value
Cells(r, 61).value = Cells(r, 62).value
Cells(r, 62).value = Cells(r, 63).value
Cells(r, 63).value = Cells(r, 64).value
Wait
Cells(r, 64).value = Cells(r, 30).value

End Select
End With
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
623
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA for Web Scraping help
Replies
0
Views
921
epactheactor
E
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
B
  • Question Question
Appending a Value to Each Element of a Range Array
Replies
7
Views
2K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,600
Messages
6,179,836
Members
452,947
Latest member
Gerry_F

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top