Static Record of Dynamically Updated Values

Dim nextUpdate As Double

Sub StartRecording()
' Set the next update interval (5 minutes = 300 seconds)
nextUpdate = Now + TimeValue("00:05:00")
Application.OnTime nextUpdate, "RecordStockPrice"
End Sub

Sub StopRecording()
On Error Resume Next
Application.OnTime nextUpdate, "RecordStockPrice", , False
End Sub

Sub RecordStockPrice()
Dim ws As Worksheet
Dim lastRow As Long
Dim currentPrice As Double
Dim timestamp As Date

' Set your worksheet
Set ws = ThisWorkbook.Sheets("Sheet1")

' Get the current stock price (change this line to your actual method of fetching the stock price)
' Example: currentPrice = Range("B1").Value
currentPrice = Range("B1").Value ' Update to match the cell where your stock price is fetched

' Find the last row with data in Column A
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1

' Record the current time and stock price
timestamp = Now
ws.Cells(lastRow, 1).Value = timestamp
ws.Cells(lastRow, 2).Value = currentPrice

' Schedule the next update
nextUpdate = Now + TimeValue("00:05:00")
Application.OnTime nextUpdate, "RecordStockPrice"
End Sub