Here is code that should do what you need.
Code:
Sub Start_Timer()
Application.OnTime Now + TimeValue("00:00:30"), "Refresh_Query"
End Sub
Sub Refresh_Query()
Sheets("Sheet1").Activate
Range("a1").Select
Selection.QueryTable.Refresh BackgroundQuery:=True
' cell location for the sample counter number
Dim sampleCounterCell As Range
Set sampleCounterCell = Range("Sheet1!C2")
' save a timestamp
SaveSampleTimeStamp sampleCounterCell, Range("Sheet2!B3")
' save price samples for stock 1 & 2
SaveSample sampleCounterCell, Range("Sheet1!C4"), Range("Sheet2!C3") ' stock 1
SaveSample sampleCounterCell, Range("Sheet1!C5"), Range("Sheet2!D3") ' stock 2
'set up for next sample
IncrementSample sampleCounterCell
Start_Timer
End Sub
Sub SaveSampleTimeStamp( _
sampleCounterCell As Range, _
timeStampFirstCellInColumn As Range _
)
Dim currentSampleNum As Long
currentSampleNum = sampleCounterCell.Value
Dim timeStampTarget As Range
Set timeStampTarget = timeStampFirstCellInColumn.Offset(currentSampleNum)
timeStampTarget.Value = Now()
End Sub
Sub SaveSample( _
sampleCounterCell As Range, _
sampleSourceCell As Range, _
sampleTargetFirstCellInColumn As Range _
)
Dim currentSampleNum As Long
currentSampleNum = sampleCounterCell.Value
Dim currentSample As Variant
currentSample = sampleSourceCell.Value
Dim sampleTarget As Range
Set sampleTarget = sampleTargetFirstCellInColumn.Offset(currentSampleNum)
sampleTarget.Value = currentSample
End Sub
Sub IncrementSample(sampleCounterCell As Range)
Dim currentSampleNum As Long
currentSampleNum = sampleCounterCell.Value
currentSampleNum = currentSampleNum + 1
sampleCounterCell.Value = currentSampleNum
End Sub
Here is how it works.
(1) Choose a cell on your worksheet to hold the sample count.
(2) Modify this line of code with the cell location for the sample count:
Code:
Set sampleCounterCell = Range("Sheet1!C2")
In my example. I am storing the sample count in cell C2 on Sheet1
(3) In the morning before you start the macro, you need to set the value in the sample count cell to 0
(4) Choose a location to store your data samples for the day. In my example I have chosen to store the data on Sheet2.
(5) Modify the macro to point to your sample source cells and sample target cells:
Code:
SaveSampleTimeStamp sampleCounterCell, Range("Sheet2!B3")
SaveSample sampleCounterCell, Range("Sheet1!C4"), Range("Sheet2!C3")
SaveSample sampleCounterCell, Range("Sheet1!C5"), Range("Sheet2!D3")
In my example The prices of the 2 stocks are on Sheet1 in cells C4 and C5 respectively.
I store the data in column format on Sheet2 in columns B3, C3, and D3, where the column starting at B3 holds the time stamps, the column starting at C3 holds the prices for stock1 and the column starting at D3 holds the prices for stock2.
(6) At the end of the day, save the data in Sheet2 to a location of your choosing. Don't forget to reset the sample counter back to 0 for the next day.
(7) Add Additional calls to the "SaveSample" subroutine to save additional stock price samples
I have attached an excel file that demos how this works, using random values for the stock maret data since I don't have your code for downloading real-time prices
