Forum Discussion

Johnny_Z2495's avatar
Johnny_Z2495
Copper Contributor
Jan 10, 2024

Static Record of Dynamically Updated Values

Hi all

In excel, I’ve got constantly updating current stock prices that’s fed in through an API. In the adjacent cells I'm hoping to statically record the reported stock prices in every 5 minute intervals. Say at 3pm the price was $1 and at 3.05pm it was $1.2. Is there anyway I can do this? Either vba or formula will be good. It should be automated. Thanks!

excel
Formulas and Functions
Macros and VBA
  • AshaKantaSharma's avatar
    AshaKantaSharma
    Iron Contributor
    Aug 20, 2024
    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