Hello guys
I have this program (attached) that I've been working on that does work.
It takes 1 Minute stock data and forms different time bars in this case 60 Minute time bars of the date, time, open,high,low,close.
The problem is that it takes 25 seconds for each days of data, which is 8 records per day. On a very large databaase of say 30 Years data that would equal approx 7500 days to create data for. That would take about 60 hours for each stock.
I was wondering if the code I've attached could be sped up or if i'm going about my approach all wrong.
I'm essentially creating a recordset based on a specific day and a specific Hourly time bar and then looping through this recordset to fill variables that will be written back to another table.
I've tried to fill the variables by querying each variable with a Select Max / Min but this was extremely slow. I've also tried a DMax / DMin approach but again this was much slower than looping through as I've done here.
Anyone have any suggestions as to how to speed this up? Maybe an array ?
I have this program (attached) that I've been working on that does work.
It takes 1 Minute stock data and forms different time bars in this case 60 Minute time bars of the date, time, open,high,low,close.
The problem is that it takes 25 seconds for each days of data, which is 8 records per day. On a very large databaase of say 30 Years data that would equal approx 7500 days to create data for. That would take about 60 hours for each stock.
I was wondering if the code I've attached could be sped up or if i'm going about my approach all wrong.
I'm essentially creating a recordset based on a specific day and a specific Hourly time bar and then looping through this recordset to fill variables that will be written back to another table.
I've tried to fill the variables by querying each variable with a Select Max / Min but this was extremely slow. I've also tried a DMax / DMin approach but again this was much slower than looping through as I've done here.
Anyone have any suggestions as to how to speed this up? Maybe an array ?
Code:
Sub Create60MinuteDataBar()
'' This program takes 1 minute stock data bars and forms
'' 60 minute stock data bars
'
' The program uses 2 tables
' SPX_1 Where 1 minute data bars are stored
' SPX_1 Fields (ID,BarOpen,BarHigh,BarLow,BarClose)
'
' SPX_60 Where the calculated 60 Minute Bars are created and then stored
' SPX_60 Fields (ID,BarOpen,BarHigh,BarLow,BarClose)
'
' The Program first creates a recordset rst1 of Distinct Days of 1 Minute
' bar data, which is used as a filter to gather that days records From
' SPX_1 to create the 60 Minute Data Bar
'
' The Program also uses a variable x as an additional filter to gather records
' for the current created Hour Daa Bar from the table SPX_1
'
' The program loops through the distinct days and then loops through and gathers
' Data from SPX_1 to create a recordset of the current Hour Data Bar being formed
'
' With The Recordset Now Formed The ( Open, High,Low,Close ) Can be Formed
'
' 4 Variables (CurOpen,CurHigh,CurLow,CurClose) are used to store required data
' while the information is gathered for each
'
' The 60 Minute Data Bar Open is simply the very first record BarOpen
' The 60 Minute BarHigh, Bar Low are created by looping through the recordset
' and comparing Each against the assigned variables
' The 60 Minute BarClose is simply the very last record BarClose
'
' With the Variables now filled the 60 Minute Data Bar can Now be Created
' and added to the table SPX_60
Dim dbs As Database
Dim rst As DAO.Recordset, rst1 As DAO.Recordset, rst2 As DAO.Recordset
Dim curYear As Integer, x As Integer, y As Long
Dim sql As String, CurDay As Date, sTime As Integer, eTime As Integer
Dim CurOpen As Double, CurHigh As Double, CurLow As Double, CurClose As Double
Dim CurDirection As String
Set dbs = CurrentDb
Set rst2 = dbs.OpenRecordset("SPX_60")
sql = "SELECT DISTINCT SPX_1.BarDate" & _
" From SPX_1" & _
" order by SPX_1.BarDate;"
Set rst1 = dbs.OpenRecordset(sql)
sql = ""
rst1.Move 100
'Do While Not rst1.EOF
For y = 100 To 110
Debug.Print (Now) ' This is used to start timer
For x = 900 To 1600 Step 100
CurDay = rst1!BarDate
sTime = x
eTime = sTime + 60
'Get Open Price
sql = "SELECT SPX_1.BarDate,SPX_1.BarTime," & _
" SPX_1.BarOpen, SPX_1.BarHigh, SPX_1.BarLow,SPX_1.BarClose" & _
" FROM SPX_1" & _
" WHERE SPX_1.BarDate = #" & CurDay & "#" & _
" And SPX_1.BarTime >=" & sTime & _
" And SPX_1.BarTime < " & eTime & _
" ORDER BY SPX_1.BarTime;"
Set rst = dbs.OpenRecordset(sql)
If Not rst.EOF Then
'Get Open and Set High / Low variables
rst.MoveFirst
CurOpen = rst!BarOpen
CurHigh = rst!BarHigh
CurLow = rst!BarLow
Do While Not rst.EOF
If rst!BarHigh > CurHigh Then
CurHigh = rst!BarHigh
End If
If rst!BarLow < CurLow Then
CurLow = rst!BarLow
End If
rst.MoveNext
Loop
'Get Low
rst.MoveLast
CurClose = rst!BarClose
End If
'' Uncomment the next block of code when updating SPX_60 Table
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
With rst2
'.AddNew
'!BarDate = CurDay
'!BarTime = sTime
'!BarOpen = CurOpen
'!BarHigh = CurHigh
'!BarLow = CurLow
'!BarClose = CurClose
'.Update
'End With
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Used for testing
Debug.Print CurDay, sTime, CurOpen, CurHigh, CurLow, CurClose
'Debug.Print y, CurDay, sTime ' Used to Monitor Program while its running
Next x
rst1.MoveNext
Next y
'Loop
Debug.Print (Now) ' Used for Timer
Debug.Print "Done" ' Used to inform that current iterations have ended
rst.Close
rst1.Close
rst2.Close
Set rst = Nothing
Set rst1 = Nothing
Set rst2 = Nothing
Set dbs = Nothing
End Sub
