Add updated cell value to list

Hutchy

New Member
Joined
Feb 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
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,
 

Attachments

  • Arb prototype.jpg
    Arb prototype.jpg
    75.8 KB · Views: 6

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
 
Solution

Hutchy

New Member
Joined
Feb 19, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Thank you Mart37. I can't get the app to run. Would you mind taking a look for me at the link supplied below.
VBA Code:
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


 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
error: Set ws = Worksheets("Trading")
B7: Time
C7: Spread %

Start macro Timer
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,336
Members
417,021
Latest member
moon miner

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