Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

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

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Robert P. Wagner
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    New Member
    Join Date
    Feb 2002
    Location
    Robert P. Wagner
    Posts
    22
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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