Help with logging live data

sk4520

New Member
Joined
Nov 30, 2008
Messages
9
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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 -->
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,304
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top