Add updated cell value to list

[Hutchy]

Hi

I am new at working with VBA and Macros and would like to ask for some guidance on where to find a solution for the below procedure:

I have a sheet that is populated with live data I am fetching from Cryptocurrency exchanges (live meaning refreshing the data every 10 seconds). I perform some calculations to determine a % price/spread difference in a cell ("D5" in my case) What I would like to achieve is to start collecting this cell value ("D5") every minute and to start populating a list with these values so that over time I can use these data points to plot a graph of the percentage price difference between the two assets. The list can have a finite row length of say 240000 data points or less. Once the range limit has been reached the oldest data can be discarded.

Is it possible to have a graph updating automatically as the list values are added? I have added a screenshot of what I thought it could look like.

Any assistance would be greatly appreciated.

Thank you,

 

[mart37]

Sub Timer()
    Call AddSpread
    t = Now()
    h = Hour(t)
    m = Minute(t) + 1
    s = Second(t)
    Application.OnTime TimeValue(TimeSerial(h, m, s)), "timer"
End Sub

Sub AddSpread()
    Dim ws As Worksheet
    Dim rSpread As Range
    Dim lLastrow As Long, i As Long, x As Long
    x = 10 'how many data points
    Set ws = Worksheets("Blad1") 'name of sheet
    Set rSpread = ws.Range("D5") 'cell with data
    lLastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row + 1
    If lLastrow = 8 + x Then '8 is startrow table
        ws.Rows(8).EntireRow.Delete
        lLastrow = lLastrow - 1
    End If
    ws.Range("B" & lLastrow).Value = Now()
    ws.Range("C" & lLastrow) = rSpread
    ws.Range("C" & lLastrow).NumberFormat = "0.00%"
End Sub

 

[Hutchy]

Thank you Mart37. I can't get the app to run. Would you mind taking a look for me at the link supplied below.

Sub Timer()
    Call AddSpread
    t = Now()
    h = Hour(t)
    m = Minute(t) + 1
    s = Second(t)
    Application.OnTime TimeValue(TimeSerial(h, m, s)), "timer"
End Sub

Sub AddSpread()
    Dim ws As Worksheet
    Dim rSpread As Range
    Dim lLastrow As Long, i As Long, x As Long
    x = 10 'how many data points
    Set ws = Worksheets("Blad1") 'name of sheet
    Set rSpread = ws.Range("D5") 'cell with data
    lLastrow = ws.Range("B" & ws.Rows.Count).End(xlUp).Row + 1
    If lLastrow = 8 + x Then '8 is startrow table
        ws.Rows(8).EntireRow.Delete
        lLastrow = lLastrow - 1
    End If
    ws.Range("B" & lLastrow).Value = Now()
    ws.Range("C" & lLastrow) = rSpread
    ws.Range("C" & lLastrow).NumberFormat = "0.00%"
End Sub

LunoAPI.xlsm

drive.google.com

 

[mart37]

error: Set ws = Worksheets("Trading")
B7: Time
C7: Spread %

Start macro Timer