[amibroker] Re: MS Excel - accessing data in excel from AB

["phringa" <phringa@xxxxxxxxx>]

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 amibroker@xxxxxxxxxxxxxxx, "huandy631" <hugh.andersen@xxx> 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
>




Please note that this group is for discussion between users only.

To get support from AmiBroker please send an e-mail directly to 
SUPPORT {at} amibroker.com

For NEW RELEASE ANNOUNCEMENTS and other news always check DEVLOG:
http://www.amibroker.com/devlog/

For other support material please check also:
http://www.amibroker.com/support.html
 
Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/amibroker/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/amibroker/join
    (Yahoo! ID required)

<*> To change settings via email:
    mailto:amibroker-digest@xxxxxxxxxxxxxxx 
    mailto:amibroker-fullfeatured@xxxxxxxxxxxxxxx

<*> To unsubscribe from this group, send an email to:
    amibroker-unsubscribe@xxxxxxxxxxxxxxx

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/