Thanks:  0
Likes:  0

# Thread: cant figure this one out

1. assume a1 is a counter. It counts nr of contracts sold for 1 minute, then flips back to 0 at the begining of the next minute and counts contracts again for the full minute etc etc.

assume b1 is a counter, which counts the seconds of the minute rolling back to 1 second at the beginning of the next minute.

I want to accumulate in a bucket, the number of contracts sold (from a1) for seconds 1 thru 15 and put them into a bucket c1.
Then, in d1, i want the contracts sold from 1 to 30 seconds. Then in e1 I want the contracts sold from 1 to 45 and finally in f1 I want the contracts (still getting them a1) for seconds 1 to 59.

And if that is not enough, I want to compare them with the number of contracts sold IN The Previous full minute.

My contracts keep going back to 0 whenever the seconds go to the next quarter minute.
hhheeeellllllppppp Thanks

2. On 2002-03-07 05:45, rpwagner wrote:
assume a1 is a counter. It counts nr of contracts sold for 1 minute, then flips back to 0 at the begining of the next minute and counts contracts again for the full minute etc etc.

assume b1 is a counter, which counts the seconds of the minute rolling back to 1 second at the beginning of the next minute.

I want to accumulate in a bucket, the number of contracts sold (from a1) for seconds 1 thru 15 and put them into a bucket c1.
Then, in d1, i want the contracts sold from 1 to 30 seconds. Then in e1 I want the contracts sold from 1 to 45 and finally in f1 I want the contracts (still getting them a1) for seconds 1 to 59.

And if that is not enough, I want to compare them with the number of contracts sold IN The Previous full minute.

My contracts keep going back to 0 whenever the seconds go to the next quarter minute.
hhheeeellllllppppp Thanks
I think this will do the trick. I named
A1 as CounterM; B1 as CounterS; C1 to F1
as Quarters; and G1 as Previous.

I also assumed that a cell named Contracts
would keep a running total of the number of
contracts sold. (You may have another way
of keeping track of the contracts, but the
following logic should still work).

'In the ThisWorkbook object:
Private Sub Workbook_Open()
Application.OnTime Now, "TimeLord"
End Sub

'In a regular module:
Public OldContracts As Long, NewContracts As Long

Sub timelord()
Dim WhatTime As Long

WhatTime = Second(Now)
With ActiveSheet
.Range("CounterS").Value = WhatTime
NewContracts = .Range("Contracts").Value - OldContracts
OldContracts = .Range("Contracts").Value
.Range("CounterM").Value = .Range("CounterM").Value + NewContracts
End With

With ActiveSheet.Range("Quarters")
.Cells(4).Value = .Cells(4).Value + NewContracts
If WhatTime <= 15 Then .Cells(1).Value = .Cells(1).Value + NewContracts
If WhatTime <= 30 Then .Cells(2).Value = .Cells(2).Value + NewContracts
If WhatTime <= 45 Then .Cells(3).Value = .Cells(3).Value + NewContracts
End With

With ActiveSheet
If WhatTime = 0 Then
.Range("Previous").Value = .Range("CounterM").Value
.Range("Quarters").ClearContents
.Range("CounterM").Value = 0
End If
End With

Application.OnTime Now + TimeValue("00:00:01"), "timelord"
End Sub

This seems to work. Good luck!

3. Tim, this is a great web page and guys like you make it greater. I will put the code in and try it out. And no, I am not going to give you any stock tips... we can both lose enough money trading ourselves with out anyone's help (on trading) but thanks again for your efforts on the excel I am going to put them in and try em.

4. Tim, Or anyone for that manner) just a quick question. whenyou say put it into ThisWorkbook module and the next routine in a regular module, could you briefly explain how to do that. I have used vba before in earlier versions of excel but not sure of how to place the routines you supplied in the workbook module and a regular module. Thanks in advance.

_________________
Never forget: "Time's running out, let's roll"

[ This Message was edited by: rpwagner on 2002-03-08 06:59 ]

5. On 2002-03-08 06:58, rpwagner wrote:
Tim, Or anyone for that manner) just a quick question. whenyou say put it into ThisWorkbook module and the next routine in a regular module, could you briefly explain how to do that. I have used vba before in earlier versions of excel but not sure of how to place the routines you supplied in the workbook module and a regular module. Thanks in advance.

_________________
Never forget: "Time's running out, let's roll"

[ This Message was edited by: rpwagner on 2002-03-08 06:59 ]
The first sub goes into the ThisWorkbook object that's automagically listed in the
Project Explorer int he Visual Basic editor.

The second sub can go into any VBA module--
you may need to create one if you don't have
a VBA macro already in the file.

HTH.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•