|
PureBytes Links
Trading Reference Links
|
Dear Peter,
Slow import to Excel - there are generally two reasons for this:
1. Cell by cell operations in Excel are slow by nature because Excel
updates the sheet when cell is changed. You use an array so it is a
little bit better. Generally speaking working on bigger arrays gives better
results.
2. Out-of-process call to AmiBroker.
Since AmiBroker is an out-proc server (living in a separate address space)
calling AmiBroker objects accross process boundary requires
address translation and repackaging of parameters.
Exactly the same happens if you want to access Excel or Word object
from outside program. This is Windows OLE-related issue and could not
be fixed as long as the server lives in a separate address space.
The solution is to implement the COM object in a DLL that is loaded in the
client address space and does not require address translation and all out-proc
overhead.
The difference is obvious when you use AmiBroker objects within AFL scripting
- it runs much, much faster simply because the caller (AFL scripting host) and
the server (AmiBroker) live in the same address space.
Best regards,
Tomasz Janeczko
===============
AmiBroker - the comprehensive share manager.
http://www.amibroker.com
----- Original Message -----
From: "Peter Gialames" <investor@xxxx>
To: <amibroker@xxxxxxxxxxxxxxx>
Sent: Tuesday, July 10, 2001 8:09 PM
Subject: [amibroker] AmiBroker Data to Excel
> 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 http://docs.yahoo.com/info/terms/
>
>
|