Option Compare Database
Public dbs As Database
Public StripNo As Integer
Public StripSymbol As String
Public StripOpen As Double
Public StripHigh As Double
Public StripLow As Double
Public StripClose As Double
Public StripHLCAverage As Double
Public StripDate As Date
Public Today As Date
Public DateDiff As Variant


Sub Refresh_NymexCrude_Strip_All()
Set dbs = CurrentDb
Set esignal = New IESignal.Hooks
Dim barItem As IESignal.BarData
Dim historyHandle As Long

st = Now()

'Clear the tbl_Nymex_Strip_Trash
SQL_Delete = " DELETE tbl_NymexCrude_Strip_Trash.*" _
    & " FROM tbl_NymexCrude_Strip_Trash ; "
    Set qdf_Delete = dbs.CreateQueryDef("", SQL_Delete)
    qdf_Delete.Execute (dbSeeChanges)

'Get the Max Trade Date that is currently in tbl_NYMEX_Strip
SQL_GetMaxDate = " SELECT Max(tbl_NYMEXCRUDE_Strip.TradeDate) AS MaxOfTradeDate " _
    & " FROM tbl_NYMEXCRUDE_Strip ; "

    Set qdf_GetMaxDate = dbs.CreateQueryDef("", SQL_GetMaxDate)
    Set rst_GetMaxDate = qdf_GetMaxDate.OpenRecordset(dbOpenDynaset, dbSeeChanges, dbReadOnly)
    
    rst_GetMaxDate.MoveFirst
    MaxTradeDate = rst_GetMaxDate!MaxOfTradeDate
    
'Compare the Max Trade date to todays date to see how many days need to be backfilled
    
    Today = Date
    
    DateDiff = Today - MaxTradeDate
    
'variable in history call needs to be a negative number

    DateDiff = DateDiff * -1
    
    
':::::::::::::::::::::::::::::::::::::
'Get list of Symbols
    'query to get the recordset of Nymex Strip Symbols
    SQL_GetSym = " SELECT tbl_CrudeStrip_Symbol.Strip, tbl_CrudeStrip_Symbol.StripSymbol " _
    & " FROM tbl_CrudeStrip_Symbol ; "
    Set qdf_GetSym = dbs.CreateQueryDef("", SQL_GetSym)
    Set rst_GetSym = qdf_GetSym.OpenRecordset(dbOpenDynaset, dbSeeChanges, dbReadOnly)
    rst_GetSym.MoveFirst
    
'This is the loop to cycle through All the missing days and add them to the table
Do Until DateDiff = 0
    
Line2:
    
    'This is the loop to get each individual strip's symbol and it's associated data from esignal
    Do Until rst_GetSym.EOF
    StripSymbol = rst_GetSym!StripSymbol
    StripNo = rst_GetSym!Strip
        
    'request the symbol from esignal
    esignal.RequestSymbol (StripSymbol), False
    
    'request the history from the symbol
    historyHandle = esignal.RequestHistory(StripSymbol, "D", btDAYS, 10, -1, -1)
    barItem = esignal.GetBar(historyHandle, DateDiff)
    
    'set the parameters that will be put into the table
    
    StripOpen = (barItem.dOpen)
    StripHigh = (barItem.dHigh)
    StripLow = (barItem.dLow)
    StripClose = (barItem.dClose)
    StripHLCAverage = Round(((StripHigh + StripLow + StripClose) / 3), 3)
    StripDate = Format(barItem.dtTime, "general date")
    
    'this if statement guards against esignal not bringing back data
    If StripOpen = 0 Then GoTo Line2
    
    
    'This if statement compares the backfilled date that esignal brought in and if it is already in the nymex strip table it ignores those strips data
    If StripDate <= MaxTradeDate Then GoTo Line1
    
FutureSource_Append_NymexCrude_Trash

    'release the history handle
    esignal.ReleaseHistory (historyHandle)
    

    rst_GetSym.MoveNext
    
    Loop
Line1:
DateDiff = DateDiff + 1
esignal.ReleaseHistory (historyHandle)
rst_GetSym.MoveFirst

Loop

FutureSource_Append_NymexCrude_All
    
en = Now()
diff = en - st
xx = 1

End Sub

Sub FutureSource_Append_NymexCrude_Trash()
Set dbs = CurrentDb
'Append new data to tbl_Nymex_Strip_Trash
SQL_Append = " PARAMETERS [pStripNo]int, [pStripDate]date, [pStripSymbol]string, [pStripOpen]double, [pStripHigh]double, [pStripLow]double, [pStripClose]double, [pStripHLCAverage]double ; " _
    & " INSERT INTO tbl_NymexCrude_Strip_Trash (Strip, StripDate, StripSymbol, [Open], High, Low, [Close], [HLC Average] ) " _
    & " SELECT [pStripNo] AS Strip, [pStripDate] as StripDate, [pStripSymbol] AS StripSymbol, [pStripOpen] AS [Open], [pStripHigh] AS High, [pStripLow] AS Low, [pStripClose] AS [Close], [pStripHLCAverage] AS [HLC Average];  " _
    
  
   
   Set qdf_Append = dbs.CreateQueryDef("", SQL_Append)
   
   qdf_Append.Parameters![pStripNo] = StripNo
   qdf_Append.Parameters![pStripDate] = StripDate
   qdf_Append.Parameters![pStripSymbol] = StripSymbol
   qdf_Append.Parameters![pStripOpen] = StripOpen
   qdf_Append.Parameters![pStripHigh] = StripHigh
   qdf_Append.Parameters![pStripLow] = StripLow
   qdf_Append.Parameters![pStripClose] = StripClose
   qdf_Append.Parameters![pStripHLCAverage] = StripHLCAverage
   qdf_Append.Execute (dbSeeChanges)

End Sub
Sub FutureSource_Append_NymexCrude_All()

Set dbs = CurrentDb
SQL_Append = " INSERT INTO tbl_NYMEXCRUDE_Strip ( Strip, TradeDate, [Open], High, Low, [Close], [HLC_Avg], Volume, [Open Interest] ) " _
    & " SELECT tbl_NymexCrude_Strip_Trash.Strip, tbl_NymexCrude_Strip_Trash.StripDate, tbl_NymexCrude_Strip_Trash.[Open], tbl_NymexCrude_Strip_Trash.High, tbl_NymexCrude_Strip_Trash.Low, tbl_NymexCrude_Strip_Trash.[Close], tbl_NymexCrude_Strip_Trash.[HLC Average], tbl_NymexCrude_Strip_Trash.Volume, tbl_NymexCrude_Strip_Trash.[Open Interest] " _
    & " FROM tbl_NymexCrude_Strip_Trash; "
    Set qdf_Append = dbs.CreateQueryDef("", SQL_Append)
    qdf_Append.Execute (dbSeeChanges)



End Sub