I'm not happy with this code as I'm fairly sure there is a better way to
do this than iterate through the collection one at a time but it does
work.
Function Retrieve(sTicker As String, DateToFind As Date)
Dim AB As Object
Dim qt As Object
Dim NumQuotes As Long
Dim i As Long
Dim NumQuotesRequired As Long
Dim aDate, aOpen, aHigh, aLow, aClose, aVolume, aOpenInt As Variant
If (sTicker = "") Or (Not IsDate(DateToFind)) Then
Retrieve = -2 'Return for blank. Note that bad tickers return
#VALUE!
Else
aDateToFind = DateValue(DateToFind)
Set AB = CreateObject("Broker.Application")
Set qt = AB.Stocks(CStr(sTicker)).Quotations
Qty = qt.Count
NumQuotes = qt.Retrieve(Qty, aDate, aOpen, aHigh, aLow, aClose, aVolume,
aOpenInt)
FoundDate = -1 ' Set up for error return indicating Date not found
For j = 0 To 3 ' Look for a long weekend
aDateToFind = aDateToFind + j
If FoundDate > -1 Then
Exit For
End If
For i = 1 To Qty - 1
If FoundDate > -1 Then
Exit For
End If
If aDate(i) = aDateToFind Then
FoundDate = i
Exit For
End If
Next i
If FoundDate = -1 Then
Retrieve = -1
Else
Retrieve = aClose(FoundDate)
Exit For
End If
Next j
End If ' Blank check
End Function
Good luck,
Pat.
--- In [email protected], "huandy631" <[EMAIL PROTECTED]> wrote:
>
> I'm sure this is an old question...but please can some point me in the
> right direction?
>
> Is it possible to save data in an excel spreadsheet (easy to update)
> and access the data from AB? ie rows have stock names, columns some
> variable like historical EPS.
>
> Or is this better using a VBArray?
>
> thanks
>
> Hugh
>
