J
jain.pb
Hi,
I am an amateur VBA programmer and need some help with real time data .
I am using the reuters tool to get the real time data. I intend to
capture this real time data in Excel every ten seconds for all the
stocks that i want to monitor and store it in an array(probably in a
different sheet) of size ten. This would mean that after 100 seconds
when all the cells of all the arrays are filled up the first cell in
each array should be cleared and assigned the current stock value.
This way each cell in the array should get a new value every 100
seconds.
Problem:- I have tried using Application.Ontime and Application .Wait
in a loop to capture these values every ten seconds. But when used in a
loop , the worksheet freezes meaning it stops recieving real time data.
And each cell of any particular array is filled with the same value as
the first cell. I need to somehow figure out how to introduce a delay
in the Sub without freezing the real time data feed . Here is a simple
code i'm trying to use. Please help me with this. I'll greatly
appreciate it.
---------------------------------------------------------------------------------------------------------------------------------------
Sub calling()
Dim i
For i = 1 To 10
Call dataCopy(i)
Call delay
Next
End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Sub dataCopy(rec)
Dim i, j
j = rec
For i = 1 To 6
Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i,
4)
Next
Exit Sub
End Sub
---------------------------------------------------------------------------------------------------------------------------------------
Sub delay()
Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub
I am an amateur VBA programmer and need some help with real time data .
I am using the reuters tool to get the real time data. I intend to
capture this real time data in Excel every ten seconds for all the
stocks that i want to monitor and store it in an array(probably in a
different sheet) of size ten. This would mean that after 100 seconds
when all the cells of all the arrays are filled up the first cell in
each array should be cleared and assigned the current stock value.
This way each cell in the array should get a new value every 100
seconds.
Problem:- I have tried using Application.Ontime and Application .Wait
in a loop to capture these values every ten seconds. But when used in a
loop , the worksheet freezes meaning it stops recieving real time data.
And each cell of any particular array is filled with the same value as
the first cell. I need to somehow figure out how to introduce a delay
in the Sub without freezing the real time data feed . Here is a simple
code i'm trying to use. Please help me with this. I'll greatly
appreciate it.
---------------------------------------------------------------------------------------------------------------------------------------
Sub calling()
Dim i
For i = 1 To 10
Call dataCopy(i)
Call delay
Next
End Sub
--------------------------------------------------------------------------------------------------------------------------------------
Sub dataCopy(rec)
Dim i, j
j = rec
For i = 1 To 6
Worksheets("Sheet2").Cells(i, j).Value = Worksheets("Sheet1").Cells(i,
4)
Next
Exit Sub
End Sub
---------------------------------------------------------------------------------------------------------------------------------------
Sub delay()
Dim newHour, newMinute, newSecond, waitTime
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 10
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub