Help with logging live data

[sk4520]

I'd like to log live update data continuously,i.e. as the data in the cell changes. The problem I'm facing is that the data comes in at uneven intervals-- ranging from 30 to 50 ticks per second. So I can't really use a timer function. I need to use some function which saves the data as the cell value changes. So, for example, if cell A1 gets updated continuously, cell B1 could save the first value of A1 and then cell B2 could save the second value of cell A1-- and so forth.

Any help would be greatly appreciated.
sk

 

[crimson_b1ade]

You can try something like this. Hold Alt+F11 , paste the below code into Sheet1 vba module. Everytime Cell A1 changes, the current date and time and the value of A1 will be pasted below it, into cells A2 (date and time)and B2 (value). I went down the rows rather than across the columns since there are more rows than there are columns:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = 1 And Target.Column = 1 Then

Range("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("A2").Value = Now
Range("B2").Value = Range("A1").Value
Else
Exit Sub

End If
End Sub

 

[sk4520]

crimson_b1ade,

Thanks for your help. The problem I'm facing is that I need to continue to log in values after the first. So in your example, A2 and B2 would have the first value of A1 and B1. 10 seconds later, the value of A1 and B1 changes- and I'd like A3 and B3 to reflect the new value. The next change in A1,B1's value would be saved in A4 and B4-- and so forth. So, you can visualize the end result as a long chain-- maybe even 60,000 rows long-- a record of all the values in cells A1 and B1.

I've tried so hard to construct this chain but it fails to update after the first value, i.e the subsequent rows get populated with just the first value of A1 in memory rather than the changing values of A1. I think I need some sort of a loop or some condition to help resolve this. Any help would be great. Thanks again.

 

[crimson_b1ade]

Try this (hold Alt+F11 keys, and paste below code into Sheet1 vba module):

Private Sub Worksheet_Change(ByVal Target As Range)

c = Range("A" & Rows.Count).End(xlUp).Row 'Identifies last row in Column A with data


If Target.Row = 1 And Target.Column = 1 Or Target.Row = 1 And Target.Column = 2 Then


Range("A" & c + 1).Value = Range("A1").Value
Range("B" & c + 1).Value = Range("B1").Value

Else
Exit Sub

End If
End Sub

 

[Joe Was]

If the cell is being updated by code then an Event will not trigger, for the Change Event to trigger the cell must be updated through the keyboard.

How is your cell being updated, as their are other ways of doing this?

 

[crimson_b1ade]

I think I see your issue, you might be linked to Bloomberg or something and are not actually 'changing' the cell. Try this if the previous code I sent hasn't worked:

hold Alt+F11 keys, and paste below code into Sheet1 vba module

Private Sub Worksheet_Calculate()

c = Range("A" & Rows.Count).End(xlUp).Row 'Identifies last row in Column A with data


Range("A" & c + 1).Value = Range("A1").Value
Range("B" & c + 1).Value = Range("B1").Value


End Sub

 

[sk4520]

crimson_b1ade and Joe Was,

Yes, that is correct-- the cell is updated by a DDE link to Reuters-- its a data feed with live tick data and time. Several ticks could have the same time value because, for example ,20 ticks could come in during the same second. So I'm trying to construct a chain of ever increasing rows-- each row being updated with the most recent tick value and time. Thanks.

 

[Joe Was]

Most code to update continuous processes will prevent other sheet work or tie up the system so if you could updated cells independently it would be in slow motion!

If this is OK I will try to work someting up, if not I do not want to waste my time?

 

[Joe Was]

Try this it will update a sheet function every second!

Private Sub Worksheet_Activate()
'Then add the Sheet module code below.

Call Recalculate
End Sub


Then add Module code:

Public myStop As Integer

Public Sub Recalculate()
'Standard module code.
'This starts the timer!

On Error GoTo Err

On myStop <> 0 GoTo Err

Calculate
Application.OnTime earliesttime:=Now + TimeValue("00:00:01"), _
procedure:="Recalculate"
End

Err:
End Sub

Public Sub myEnd()
'Standard module code.

myStop = myStop + 1
End Sub

Public Sub myReSet()
'Standard module code.

myStop = 0
End Sub <!-- / message --><!-- sig --><!-- / message --><!-- sig -->

 

[sk4520]

Most code to update continuous processes will prevent other sheet work or tie up the system so if you could updated cells independently it would be in slow motion!

If this is OK I will try to work someting up, if not I do not want to waste my time?

I have some very fast processors-- so I don't really mind it being slow-- I just need it to be continuous. I can't really use a timer of one second because sometimes 40 or 50 ticks come in one second and I need to capture all of those ticks. Thanks Joe.