record cell values every second for last 30 seconds

[GAURAV CHHABRA]

I need to record changing values of cell, but not every change.

the value of this cell is changing continuously, i want to record values for the past 30 seconds in a 1 second interval.. than if any value in particular is 2% away from the majority wanna ignore it.. add derive an average value..

basically we just need the values of the last 30 seconds so we will have typically have 30 values at any given point.

can you please help.. really appreciate your help..

thanks and regards

 

[Aaron Blood]

I can't give you code that looks backward in time 30 seconds to tell you what the values were... But, we can write code that will schedule a macro to run every second for 30 seconds into the future.

There are two steps involved:
1) Create a little macro to record data from a specified changing cell
2) Create a scheduler macro to execute the record data macro every second for 30 seconds

So the idea is, you click a button to run the scheduler and your record data macro is executed 30 times at 1 second intervals and this is what it might look like:

Sub Sched_Runs()
    Dim n As Integer, i As Integer
    n = 30

    
    Dim StartTime As Double
    StartTime = Now()

    
    Dim ProcName As String
    ProcName = "Record_Data"

        
    For i = 1 To n
        Application.OnTime StartTime + TimeSerial(0, 0, i), ProcName
    Next i
End Sub

Sub Record_Data()
    
    Dim ChgCell As Range
    Set ChgCell = Sheets("Sheet1").Range("F4")
    

    Dim LogSht As Worksheets
    Set LogSht = Sheets("Sheet1")
    

    Dim LogRng As Range
    Set LogRng = LogSht.Cells(LogSht.Rows.CountLarge, 1).End(xlUp).Offset(1, 0)
    

    With LogRng
        .Value = Now()
        .Offset(0, 1).Value = ChgCell.Value
    End With
    
End Sub

You can tweak the results and add formulas to adjust for your 2% check...

 

[GAURAV CHHABRA]

Dear Aaron,

really appreciate your reply..

i am completely new to excel, can you please guide how to implement the code..

e.g. the values to be recorded to are in cell "a1" and output could be in any cell say "D1"

I'm importing share price data from another software into excel, and need this code to continuously record data after the initial 30 seconds, it should ignore the 1st second data it started from and take the latest data.. thus after first 30 seconds would have data for latest previous 30 seconds..

also if this 30 seconds could be configurable to a cell value say "K1"

would be very helpful if you could take time to reply again..

Thanks Again.. Regards

 

[Aaron Blood]

The slightly modified code below would allow you to:
1) enter the time duration for recording (30) in cell K1
2) enter the time interval for recordings in cell K2 (1)
3) changes to cell A1 are then tracked in D2:E2

To make it work you need to:
1) Press Alt-F11 to open the VBE
2) add a code module to your workbook
3) copy/paste the code below exactly as it appears into the code module
4) assign the "Sched_Runs" macro to a control button
5) click the button to run the code

If any of the above steps seem baffling, you may have to post back with appropriate follow-up questions (in new threads) on how to do each. To best utilize my time, I generally avoid those types of questions.

Option Explicit

Sub Sched_Runs()
    Dim StartTime As Double
    StartTime = Now()
    Dim ProcName As String
    ProcName = "Record_Data"
    Dim j As Integer, n As Integer
    n = Range("K1").Value
    j = Range("K2").Value
    Dim i As Integer
    For i = j To n Step j
        Application.OnTime StartTime + TimeSerial(0, 0, i), ProcName
    Next i
 End Sub

Sub Record_Data()
    Dim ChgCell As Range
    Set ChgCell = Range("A1")
    Dim OutCell As Range
    Set OutCell = Range("D1")
    Dim LogRng As Range
    Set LogRng = Cells(ActiveSheet.Rows.CountLarge, OutCell.Column).End(xlUp).Offset(1, 0)
    With LogRng
        .Value = Now()
        .Offset(0, 1).Value = ChgCell.Value
    End With
End Sub