real time data

[Jimmy767]

Hi,

I have a trading program which is linked up to excel and is recieving a live feed which enters data into a single cell on the spreadsheet. However it is updated every second, so previous values are lost. I need a way to log this data down the spreadsheet and require every second of data regardless of whether it has changed in value. So for example cells 1-60 would represent 1 minute of recorded data.

I would really appreciate any help.
James

[PA HS Teacher]

Press Alt + F11 to get to the VBE editor
click on Sheet1 (or which ever sheet has the cell in question)

Select the worksheet change event

Paste in this code

Range("A65535").End(xlUp).Offset(1, 1).Value = Now
Range("A65535").End(xlUp).Offset(1, 0).Value = Range("A1").Value

Change A to match the column receiving the data, and A1 should be the cell receiving the data.

[erik.van.geit]

Hi,

Teacher, I've added some lines to your code, which are really necessary

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) <> "A1" Then Exit Sub

Application.EnableEvents = False

    With Range("A" & Rows.Count).End(xlUp)
    .Offset(1, 1) = Now
    .Offset(1, 0) = Range("A1").Value
    End With

Application.EnableEvents = True

End Sub

or to update a sheet named "log" replace the "With"-line by

    With Sheets("log").Range("A" & Rows.Count).End(xlUp)

you need to
1. exit the routine when another cell than A1 is changed
2. add the "EnableEvents"-lines, else your code will get stuck in a loop

but, Jimmy767, I'm not sure at all, if this code would be triggered when the change is made by an external source
if not, you will need an "OnTime"-trick

kind regards,
Erik