Results 1 to 2 of 2

Thread: Record real time data along with time stamp

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2020
    Posts
    1

    Record real time data along with time stamp

    I have little experience in VBA and doing a personal project but got stuck with this issue hope someone with good experience can help me on it.

    SO I have stock prices that changes in real-time when market opens and I want to record it in a new sheet with time

    Name:  Screen Shot 2020-07-05 at 9.07.49 PM.png
Views: 176
Size:  25.3 KB

    I want to record the value data from all that row in the Log Sheet along with the timestamp every five mintues.

    Name:  Screen Shot 2020-07-05 at 9.12.27 PM.png
Views: 170
Size:  24.6 KB

    How can I make this.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Record real time data along with time stamp

    you could use application.ontime to run code every five minutes, but an API timer would probably be better

    Code:
    sub myupdate()
    set logsht = sheets("logsheet")    'change both sheet names to suit
    set datsht = sheets("data")
    set nwrow = .cells(.rows.count, 1).end(xlup).offset(1)
    with nwrow
        .value = time
        .offset(,1) = datsht.cells(3, 3)
        .offset(,2) = datsht.cells(4, 3)
        .offset(,3) = datsht.cells(5, 3)
    end with
    applicatiion.ontime (now + timeserial(0, 5, 0),"myupdate")   'run again in 5 minutes
    end sub
    this is just typed in the browser,so may contain typos or code errors
    you will need to call the sub initially with a call from workbook open to the sub to run immediately or a call to application.ontime (as above) to call the procedure with a 5 minute delay, else a manual call using a button or similar, you may also wish to format the time or other data to suit

    edit fix code, where quotes required
    Last edited by westconn1; Jul 6th, 2020 at 04:19 PM.
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width