Extracting cell data and finding difference in timed intervals

[HelloEx]

Hi. I have price data on cell b2 that gets updated automatically. I want to find the difference from current price and 2,5,10,15, and 30 minutes ago. Any help is appreciated.

 

[Diffy]

Where do you store the old prices?

 

[gecs]

You need to find a way to intercept the updating process and save the price values together with the time stamp somewhere (in another sheet, perhaps). How else would you quantify the time interval from the time you launch the difference testing and the moment(s) the price was changed?

Most probably using the Change event of that sheet and the timer function will do the job.

 

[HelloEx]

currently price is not getting stored. is there a way to store/save it with time also?

 

[gecs]

As I said before, you can use the Change event of that worksheet: right-click on the sheet tab -> View Code. You enter like that in the VB editor and from the window on top in the top-left drop-down list select Worksheet instead of General and from the top-right drop-down list select Change.

Modify the body of the Worksheet_Change event as in the following example:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    If Target.Address = "$B$2" Then
        If IsEmpty(i) Then
            i = 1
        Else
            i = i + 1
        End If
        Set r = ThisWorkbook.Sheets("Sheet2").Cells(i, 1)
        r.Value = Target.Value
        Set r = ThisWorkbook.Sheets("Sheet2").Cells(i, 2)
        r.Value = Time()
    End If
End Sub

Of course in the code above you have to change the name of the sheet (if you don't want to use Sheet2 for this) where you want to store the price values and the time stamps and you can also change the rows and columns used to do this (in the example storing takes place on columns A and B starting with the first row)
In the VBA project (top-left of the main VB editor window) right click on some item there belonging to your workbook and select Insert->Module. Type the following declaration in the (General) section:
Global i As Long