none
VBA - Recording Events Triggered by RTD data RRS feed

  • Question

  • Hello all


    There is very little written about RTD/DDE and I got to say that I have almost zero experience with VBA,
    so I better just post the problem here.

    We want to record some data triggered by RTD cells (real time stock quotes) as explained below.

    This is the Data sheet:


    Column C (RTD) streams real time quotes.
    Column D have a price value.
    Column E "Trigger" displays "1" when the "Last" (RTD) price crosses above Price1.

    When "1" appears in the "Trigger" column, we want to record the value data from all that row (columns E to I) in the Log Sheet along with the timestamp.

    Like this:



    The sample file is here:
    RTD Recording Sample


    How can we do it?

    Sunday, October 9, 2016 8:25 PM

Answers

  • Hi,

    You could use Application.OnTime Method (Excel)

     

    Call the Procedure at the workbook_open event to startup, then use Application.OnTime to run it every 5s(in the example below).

    Since it needs some time to run the code, so when setting timevalue, you need to add the running time. 

    Private Sub Workbook_Open()
    Call test
    End Sub
    
    'use the following to get the running time for the first time
    Sub speed()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    StartTime = Timer
    'processing
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    End Sub
    
    Sub test()
    'change the timevalue into your interval plus running time
    Application.OnTime Now + TimeValue("00:00:5"), "test"
    Dim rng As Range
    Dim cell As Range
    Dim newR As Variant
    'use the following to get the running time for the first time
    'Dim StartTime As Double
    'Dim SecondsElapsed As Double
    'StartTime = Timer
    Application.ScreenUpdating = False
    Set rng = Sheets("DATA").Range("C3:C8")
    For Each cell In rng
    If cell.Value > 100 Then
    cell.Offset(0, 2).Value = "1"
    Sheets("LOG").ListObjects("Table1").ListRows.Add
    newR = Sheets("LOG").ListObjects("Table1").ListRows.Count + 1
    Debug.Print cell.Offset(0, 3).Address
    Sheets("DATA").Range(cell.Offset(0, 3).Address & ":" & cell.Offset(0, 6).Address).Copy
    Sheets("LOG").Select
    Sheets("LOG").Range("D" & newR).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
    Next cell
    Application.CutCopyMode = False
    'SecondsElapsed = Round(Timer - StartTime, 2)
    'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    End Sub
    

    • Marked as answer by NicoPer Thursday, October 13, 2016 1:02 PM
    Tuesday, October 11, 2016 5:52 AM
    Moderator

All replies

  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Monday, October 10, 2016 2:34 AM
  • Hi,

    You could use Application.OnTime Method (Excel)

     

    Call the Procedure at the workbook_open event to startup, then use Application.OnTime to run it every 5s(in the example below).

    Since it needs some time to run the code, so when setting timevalue, you need to add the running time. 

    Private Sub Workbook_Open()
    Call test
    End Sub
    
    'use the following to get the running time for the first time
    Sub speed()
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    StartTime = Timer
    'processing
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    End Sub
    
    Sub test()
    'change the timevalue into your interval plus running time
    Application.OnTime Now + TimeValue("00:00:5"), "test"
    Dim rng As Range
    Dim cell As Range
    Dim newR As Variant
    'use the following to get the running time for the first time
    'Dim StartTime As Double
    'Dim SecondsElapsed As Double
    'StartTime = Timer
    Application.ScreenUpdating = False
    Set rng = Sheets("DATA").Range("C3:C8")
    For Each cell In rng
    If cell.Value > 100 Then
    cell.Offset(0, 2).Value = "1"
    Sheets("LOG").ListObjects("Table1").ListRows.Add
    newR = Sheets("LOG").ListObjects("Table1").ListRows.Count + 1
    Debug.Print cell.Offset(0, 3).Address
    Sheets("DATA").Range(cell.Offset(0, 3).Address & ":" & cell.Offset(0, 6).Address).Copy
    Sheets("LOG").Select
    Sheets("LOG").Range("D" & newR).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End If
    Next cell
    Application.CutCopyMode = False
    'SecondsElapsed = Round(Timer - StartTime, 2)
    'MsgBox "This code ran successfully in " & SecondsElapsed & " seconds", vbInformation
    End Sub
    

    • Marked as answer by NicoPer Thursday, October 13, 2016 1:02 PM
    Tuesday, October 11, 2016 5:52 AM
    Moderator
  • Hi Celeste

    Thanks so much for taking the time to reply with the code. I highly appreciate it.

    So I´ve tested it and it keeps recording the same rows every 5 seconds.

    We only want to record the first time it happens => trigger going from "" to 1. And if there´s a way to do it without delay would be perfect. 

    The reason is that we want to simulate taking the trades in real time. This first log is meant to be for the entries.

    Could that be possible?

    Tuesday, October 11, 2016 2:19 PM
  • Hi,

    You could add an IF condition to only record data at the first time.

    For Each cell In rng
    If cell.Value > 100 Then
    cell.Offset(0, 2).Value = "1"
    
        Set srhR = Sheets("LOG").ListObjects("Table1").ListColumns("Data1").Range
        Set findC = srhR.Find(cell.Offset(0, 3).Value)
        If findC Is Nothing Then
        Sheets("LOG").ListObjects("Table1").ListRows.Add
        newR = Sheets("LOG").ListObjects("Table1").ListRows.Count + 1
        
        Sheets("DATA").Range(cell.Offset(0, 3).Address & ":" & cell.Offset(0, 6).Address).Copy
        Sheets("LOG").Select
        Sheets("LOG").Range("D" & newR).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
        End If
    End If
    Next cell

    >>if there´s a way to do it without delay would be perfect
    Do you mean keeping check the data all the time?Since the code takes time to execute, you could set TimeValue as the running time as a shortest TimeValue.

    i think the most suitable value is 0.5s or 1s even though the code might not spend more than 1s. (My test result shows the longest time is 0.06s). You could select a suitable value based on the longest time. Meaning all the data need to be added in the sheets("LOG") at once.

    Tuesday, October 11, 2016 3:31 PM
    Moderator
  • Thanks Celeste, 1/2 to 1 sec is great.

    I´ve changed that part of the code and it stopped recording every 5 secs, but it´s changing the Trigger cells formula.

    In the original file, the formulas for the Trigger cells are long as they have multiple conditions.
    (If all conditions are met, it shows 1, and that triggers the recording.)

    Here in the sample I just added a dummy formula like this:
    =IF([@Price1]>[@Last],1,"")


    But now after the changes, not only the formula from the Trigger cells got replaced by 1, it also doesn´t let me change them back.

    Any Ideas?

    I attached the new file for you to see just in case I´m doing something wrong:
    sample v2

    • Edited by NicoPer Tuesday, October 11, 2016 5:57 PM attached file
    Tuesday, October 11, 2016 5:40 PM
  • Hi,

    Sorry that I could not reproduce your issue since I don’t have your RTD server,

    However, according to your description, do you want to keep the formula?

    Please change

    If cell.Value > 100 Then

    cell.Offset(0, 2).Value = "1"

    Into

    If IsError(cell.Offset(0, 2)) = False Then

    If cell.Offset(0, 2).Value = 1 Then

    For Each cell In rng
    'If cell.Value > 100 Then
    'cell.Offset(0, 2).Value = "1"
    'the code above is actually same as the formula you used in cells
    If IsError(cell.Offset(0, 2)) = False Then ' i add this IsError function because i dont have your RTD server, so the data is #N/A
    ' you could omit IsError line if your tigger cell wouldnt be an error
    If cell.Offset(0, 2).Value = 1 Then



    Wednesday, October 12, 2016 6:39 AM
    Moderator
  • Awesome.. Thanks so much Celeste :)
    It seems to be recording them now.

    There are just a few little details to get it working perfectly.
    I highly appreciate if you can help with these last touches:

    1) The Hour and Action columns aren´t getting filled in the Log sheet.


    2) When there´s a recording, it jumps from the Data to the Log sheet for some reason.

    3) The actual trigger cells in the original file sometimes are loading (showing "Loading"), and that should be ignored.

    We want to trigger the recording again if this happens:
    1 => "" => 1 (so from nothing to 1, record every time it happens)
    (In this case all conditions stopped being true for some time, then they returned to be true, so it triggers again)

    Special cases with "Loading":

    1 => "Loading" => 1
    (It shouldn´t trigger here.)

    "" => "Loading" => 1
    (It should trigger here.)


    Basically ignoring the "Loading", and recording every time the trigger goes from "" to 1.

    I hope is clear enough. Again, I´m extremely thankful for your support.


    • Edited by NicoPer Wednesday, October 12, 2016 3:29 PM
    Wednesday, October 12, 2016 3:27 PM
  • Hi,

    To avoid jumping into the sheets log, you could remove the line to select the sheets and paste the value by

    Sheets("LOG").Range("D" & newR).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

                :=False, Transpose:=False

    Since we have helped you meet most parts of requirement, for the specific issue to log special time, I suggest you post a new thread. Besides, in your new thread, I suggest you share with us what value the action should be and where the value "Loading" comes from because it is "" or 1 in your formula of column Tigger.

    Thanks for your understanding.

    Thursday, October 13, 2016 9:55 AM
    Moderator
  • Np, I´ll check it and post another thread if needed.

    Thank you very much Celeste. You helped a lot. I appreciate it.

    Thursday, October 13, 2016 1:08 PM