Recording changing values from one cell into a separate Cell or column?

[gammahedge1]

I have option data (price, delta and gamma etc) streaming through Excel via an API and what I would like to do is keep track of the changes every 1 minute (or any other timeframe) and record them in a different column (or worksheet) For example if I have the live price of the option populating B2 how would I record the changing value every minute in cell b5, b6, b7, b8 (or anywhere else) I've seen answers that are very similar to this question but when I try to copy and paste the code into excel it simply doesn't work. Does that make sense?

 

[Yongle]

Welcome to MrExcel

Give this a try

1. In cell B5 enter the word "Old"

2. In any cell but NOT in column B, this formula

=B2

The above formula is used to trigger the macro below

3. Right click on sheet tab \ view code \ paste code below into the window that opens up
(this places macro in the sheet code window)

Private Sub Worksheet_Calculate()
    Dim Previous As Range, Latest As Range, Current As Range
    Set Latest = Cells(2, 2)
    Set Previous = Cells(Rows.Count, 2).End(xlUp)
    Set Current = Previous.Offset(1)
   
    If Not Latest = Previous Then
        Current.NumberFormat = Latest.NumberFormat
        Application.EnableEvents = False
        Current = Latest
        Application.EnableEvents = True
    End If
End Sub

It works for me.
Let me know if it does what you want or if you have any problems

 

[gammahedge1]

YES! Thanks so much Yongle! Can I change the timeframe that it records events?

 

[Yongle]

At the moment the code is triggered whenever the value in B2 is refreshed.
An entry is made if the value has changed since the previous entry.

How frequently is the value in B2 refreshed?

 

[gammahedge1]

its a stock price so its refreshing by the second. Essentially I'm trying to keep track of the Greeks of specific options as the price changes so It would be ideal to record them as price changed exactly but it would be so much data it would probably crash. Here are some of data I'm keeping track of. I'd like I'm also having trouble cutting/copying cells in the sheet. Is that normal for something macro enabled?