Re: [amibroker] AmiBroker Data to Excel

[NIck Iacovelli <nickhere@xxxx>]

peter you need optimization
first switch the for next loop to DO loop it faster
the best thing i just found in VB is if i had multiple calls to ami
I did a set value

example

writeTable(j, 2) = amibroker.stocks(found).quotations(x).high
writeTable(j, 3) = amibroker.stocks(found).quotations(x).low
writeTable(j, 4) = amibroker.stocks(found).quotations(x).Close
writeTable(j, 5) = amibroker.stocks(found).quotations(x).volume


run faster this way

set ami2=amibroker.stocks(found).quotations(x)
writeTable(j, 2) = ami2.high
writeTable(j, 3) = ami2.low
writeTable(j, 4) = ami2.Close
writeTable(j, 5) = ami2.volume











At 02:09 PM 7/10/2001 -0400, you wrote:

Attached is a spreadsheet that will import AmiBroker data. The process is
kind of slow - I am hoping Tomasz et all can tell me what I am doing wrong
or a shortcut of something I am missing.

Peter Gialames

PS Here is the VBA code:

Option Base 1

Sub GetAmiBrokerData()

Dim j As Integer, x As Integer, writeTable() As Variant
Dim DaystoLoad As Integer, Months As Integer
Dim symbol As String

Application.ScreenUpdating = False 'Don't update screen until Sub is
done
Application.DisplayAlerts = False 'Don't display messages

Set amibroker = CreateObject("Broker.Application")

ActiveWorkbook.Worksheets.Add

Months = 24 'Months of data to load
DaystoLoad = (Months) * 25 ' Convert Months to Estimated Days
symbol = UCase(InputBox("Enter Ticker Symbol"))

'Find Symbol in AmiBroker List
For i = 0 To (amibroker.stocks.Count - 1)
If amibroker.stocks(i).ticker = Trim(symbol) Then
found = i
End If
Next i

'Check to see if there is enough data in AmiBroker
If amibroker.stocks(found).quotations.Count < DaystoLoad Then
DaystoLoad = amibroker.stocks(found).quotations.Count
End If

ReDim writeTable(DaystoLoad + 1, 5) 'Array for data - copy to
spreadsheet later (faster)

For j = 1 To DaystoLoad - 1
x = j - 1
'Copy info to array
writeTable(j, 1) = amibroker.stocks(found).quotations(x).Date
Application.StatusBar = writeTable(j, 1) 'Show something in Excel
(This process may take awhile for AmiBroker?)
If writeTable(j, 1) = "N/A" Then
writeTable(j, 1) = ""
GoTo HERE
End If
writeTable(j, 2) = amibroker.stocks(found).quotations(x).high
writeTable(j, 3) = amibroker.stocks(found).quotations(x).low
writeTable(j, 4) = amibroker.stocks(found).quotations(x).Close
writeTable(j, 5) = amibroker.stocks(found).quotations(x).volume
Next j

HERE:

With ActiveSheet
.Name = symbol
With .Range(.Columns(1), .Columns(7))
.Cells.Clear
.Cells.HorizontalAlignment = xlRight
.Cells.NumberFormat = "General"
.ColumnWidth = 10
End With
.Cells(1, 1) = "Date"
.Cells(1, 2) = "High"
.Cells(1, 3) = "Low"
.Cells(1, 4) = "Close"
.Cells(1, 5) = "Volume"
.Range(.Cells(2, 1), .Cells(DaystoLoad + 1, 5)) = writeTable
'Write array to spreadsheet
End With

Application.StatusBar = False

End Sub

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