-
Jul 5th, 2020, 10:59 AM
#1
Thread Starter
New Member
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
I want to record the value data from all that row in the Log Sheet along with the timestamp every five mintues.
How can I make this.
-
Jul 6th, 2020, 04:12 AM
#2
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|