It's killing me (Tim Wright are you still there).... Help

[rpwagner]

I link up with a data supplier through Excel and get a spread sheet with the following data during the day (930 to 4pm). I can not modify the data they give me (as it is an array) but I am free to use the rest of the spread sheet as I see fit. Lets assume it is 1049am and I have the following data on the Supplied spread sheet:

time price nr of contracts
1048 1152.52 323
1049 1152.75 221

then, at 1050am, the 1049am data rolls up and i have this:

time price nr of contracts
1049 1152.75 221
1050 1151.25 190

Only the price and nr of contracts for the current minute changes during the minute. As you can see, the entire row is rolled up at the end of a minute. This process goes on from 930 am to 400pm every trading day. Unfortunetly, the input data does not have the time broken out into hhmmss format and I cant get it from the data supplier (it appears as if they only supply hhmm and I was unable to reformat their array format to make their hhmm hhmmss). I do have my system clocked synchronized with the supplier of the data so when their times roll up to the previous row, it is at my systems clock 00 seconds


Here is what I am trying to do. During each current minute, I would like to compare evey 15 seconds the accumulated nr of contracts with the previous minute's nr of contracts. So, in my example above, assume it is 105015 and already I have 190 contracts. This mean that in only 1/4 of a minute (1050 and 15 seconds), I already have almost the same amount of contracts as the entire previous minute (1049 221 contracts). (Figuring out the Excel way for percentage (221 to 190) and firing off sound alerts (ie., system sends wav file "Volume alert" ) is my next challange but I will get to that later (after I (or someone far better than me) figures this first part out)

Tim Frances-Wright was kind enough to send me the following code but it just did not quite work and I have used 4/5 of my (limited ) grey matter cells trying to figure it out. It seems as if there is a problem determining (within the current minute) the number of Contracts from the current second to the last second. Tim's formulas were changing the 15 second Quarters time frames correctly but the formula was computing wierd numbers of contracts (minus values, double posting etc) and puting the wierd numbers into the 15 second buckets. It is as if the computations of newcontracts minus old contracts was not working from second to second.

In my limited knowledge of VB, I have been battling to learn the syntax and techniques of the Editor. I assure you, I am not looking to have someone hold my hand but after hours (and hours) of effort, I thought I would put it up again with hopefully a better explanation of what I am wanting to do.

Here is the code that Tim Frances-Wright was kind enough to supply me. He wrote:
"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
end of Tim's code.

In the above code, it is the following code that gives the problems:
NewContracts = .Range("Contracts").Value - OldContracts
OldContracts = .Range("Contracts").Value
.Range("CounterM").Value = .Range("CounterM").Value + NewContracts

His assumption:
"I also assumed that a cell named Contracts
would keep a running total of the number of
contracts sold"
was not correct as I was not totalling the contracts in a running total as they kept flipping to zero.
I suppose I could alter the code to provide dummy data for the number of contracts (if the real system is not up (on Saturday)). Anyway, that is my problem and if anyone (Tim) could take another look at it, I will really be thankfull


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

[ This Message was edited by: rpwagner on 2002-03-16 06:01 ]

[rpwagner]

Wait on minute.. i may have made a breakthrough.... i am testing it now with dummy data (adds 1 contract every second) and trying to check results. will advise if i still need help