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

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

crimson_b1ade

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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

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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539

ADVERTISEMENT

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

Well-known Member
Joined
Sep 27, 2008
Messages
1,557
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

New Member
Joined
Nov 30, 2008
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

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

Watch MrExcel Video

Forum statistics

Threads
1,132,671
Messages
5,654,655
Members
418,146
Latest member
Shnn028

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
Top