Add updated cell value to list

Hutchy

New Member
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
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
VBA Code:
``````Sub Timer()
t = Now()
h = Hour(t)
m = Minute(t) + 1
s = Second(t)
Application.OnTime TimeValue(TimeSerial(h, m, s)), "timer"
End Sub

Dim ws As Worksheet
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).NumberFormat = "0.00%"
End Sub``````

Hutchy

New Member
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()
t = Now()
h = Hour(t)
m = Minute(t) + 1
s = Second(t)
Application.OnTime TimeValue(TimeSerial(h, m, s)), "timer"
End Sub

Dim ws As Worksheet
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).NumberFormat = "0.00%"
End Sub``````

mart37

Well-known Member
B7: Time

Start macro Timer

Replies
1
Views
81
Replies
4
Views
197
Replies
0
Views
237
Replies
0
Views
32
Replies
11
Views
98

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.

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

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