PureBytes Links
Trading Reference Links
|
Thanks for all the emails to help. Embarrassingly, all this VBA interface with AB COM are really going over my head. I guess I'll have to slowly figure them out. And yes, GP, the syntaxes are tough, and I'll need those padding for the walls....
Till I figure something out!
Cheers, Winston
On 7/16/07, Ton Sieverding <ton.sieverding@xxxxxxxxxx
> wrote:
Strange indeed. Let me give you the characteristics
of my system : Intel 3,2Ghz,
Memory 1028MB running Windows XP Prof. So I assume that's more or less fast enough to get decent
results. BTW the +70 seconds I am getting is for just one run
thru the AFL code. Not for all three
...
Why you are not getting three times the overwrite
request is also a big question for me.
Is there a kind of setting in the preference window
to let the interpreter only
give just one request when writing something to the
disk ? Can you force the interpreter to skip the next two runs when writing to
disk ? I really don't know ...
I hope that the new User Knowledge Base of Herman
and Co. will give me a better
feeling about what the interpreter really does.
Still too many questions ...
Regards, Ton.
----- Original Message -----
Sent: Monday, July 16, 2007 12:43
AM
Subject: [amibroker] Re: Difficulities in
getting COM syntax with Excel VBA
Ton,
Strange, but I don't get that either.
When I run the
code as an exploration over a single stock, one for which I have data since
1997 and has been liquid since then, it takes about 15 seconds and only
prompts about overwriting the file once. Are you sure you're only doing it
over a single stock? This is on my laptop which is a 1.8GHz
Centrino.
Also, if I comment out the font setting statements, it only
takes about 6 seconds.
Occasionally I do get an odd thing happening
though. After running the exploration, the currently-displayed chart (the
one I just run the exploration over) disappears and gets replaced in all
panes with an error message to the effect that there was some sort of
exception. However, that only happens occasionally. Most of the time it
works okay.
Anyway, whatever the issue is, it looks like it's slow and
a bit flaky for some reason.
Regards, GP
--- In amibroker@xxxxxxxxxxxxxxx,
"Ton Sieverding" <ton.sieverding@xxx> wrote: > > I
checked your code. My remark was that AB hangs. Is this correct ? Answer,
not at all. It just takes a very long time to get the result. Why ? For two
good reasons. Firstly the AFL interpreter executes the code 3 times. You
can test this. Once you have a XLS and you want to run the same item again,
you therefore are getting 3 times the question if you want to overwrite the
XLS. Simply because the AFL interpreter is executing 3 times the same code.
Secondly because writing something to Excel takes a lot of time. Why ?
Don't know. I tested this with DebugView and it took me 74 seconds for
3.752 lines. The second problem is what it is. But the first problem could
be removed by testing if the XLS already exists. If true, skipping
the rest of the AFL procedure. Unless you have a better solution
... > > Regards, Ton. > > ----- Original Message
----- > From: gp_sydney > To: amibroker@xxxxxxxxxxxxxxx
> Sent: Saturday, July 14, 2007 11:13 AM > Subject: [amibroker]
Re: Difficulities in getting COM syntax with Excel VBA > >
> Ton, > > I didn't have that problem, but if an error
occurs before the workbook > is closed, the Excel object will still be
active and locking the > output file. The Excel process then needs to be
killed in Task > Manager. However, the locked file would prevent it
being opened in > Excel as well. > > Check in the Task
Manager process list (ie. under the Processes tab) > that no instances
of Excel are running before running the AFL code. If > AB still locks
up, check the Task Manager process list again and see > any Excel
processes are still running. If so, try killing them. > >
Otherwise I can only suggest using DebugView and trace statements to >
find out exactly how far it gets, although if you say the XLS file is >
being generated and that you can open it in Excel, then it would seem >
that it is getting right through it. > > GP > > ---
In amibroker@xxxxxxxxxxxxxxx,
"Ton Sieverding" > <ton.sieverding@> wrote: >
> > > I did a quick test with underneath mentioned code. Although
the code > is creating the requested XLS file and I can read the file in
Excel, > when running the AFL formula in AB the program hangs and I must
end > the AB task with Windows Task Manager. Any idea ? > >
> > Regards, Ton. > > > > ----- Original Message
----- > > From: gp_sydney > > To: amibroker@xxxxxxxxxxxxxxx
> > Sent: Saturday, July 14, 2007 10:06 AM > > Subject:
[amibroker] Re: Difficulities in getting COM syntax with > Excel
VBA > > > > > > I can fix the date issue by
printing it in US format: > > > > cell.Value =
StrFormat("%1.0f/%1.0f/%1.0f", mm, dd, yy); > > >
> That then correctly displays in Australian format with Australian >
> regional settings. > > > > Still not sure why the
"fnt.Bold = True" statement is taking so long > > though. >
> > > GP > > > > --- In amibroker@xxxxxxxxxxxxxxx,
"gp_sydney" <gp.investment@> wrote: > > > >
> > Winston, > > > > > > When it comes to
Microsoft applications and getting syntax right, > > > you'd
better pad up those brick walls or your head is going to get > > >
very sore! > > > > > > Here's some code that will
write the date and the opening and > closing > > > prices to
an XLS file. Put it in an exploration and just run it > over a >
> > single symbol, or it's going to take a very long time. > >
> > > > This code creates a new workbook and uses the first
worksheet. > It sets > > > the number format, alignmnent,
and width of columns A, B, and C, > puts > > > titles in the
first row of each of the columns with bold font and > > > centred
text, then fills the other rows with the data. > > > > >
> Not sure if I've got something wrong here, but I do get a couple
of > > > odd things happening. Firstly, the statements that set
the font seem > > > to take an unusally long time, and when I
look through the resulting > > > file, some of the dates have
US date format and some have > Australian. > > > >
> > Also, if you don't specify a path for the file, it seems to >
default to > > > the "My Documents" folder. > > >
> > > Regards, GP > > > > > > >
> > excel = CreateObject("Excel.application"); > >
> wb = excel.WorkBooks; > > > wb.Add; > > > wb1 =
wb.Item(1); > > > ws = wb1.WorkSheets; > > > ws1 =
ws.Item(1); > > > > > > cmn =
ws1.Columns("A"); > > > cmn.NumberFormat =
"d/mm/yyyy"; > > > cmn.ColumnWidth = 12; > > >
cmn.HorizontalAlignment = 4; // 2=left, 3=centre, 4=right > >
> cmn = ws1.Columns("B"); > > > cmn.NumberFormat =
"$0.000"; > > > cmn.ColumnWidth = 10; > > >
cmn.HorizontalAlignment = 4; > > > cmn =
ws1.Columns("C"); > > > cmn.NumberFormat = "$0.000"; >
> > cmn.ColumnWidth = 10; > > > cmn.HorizontalAlignment
= 4; > > > > > > cell = ws1.Range("A1"); >
> > cell.Value = "Date"; > > > fnt = cell.Font; > >
> fnt.Bold = True; > > > cell.HorizontalAlignment =
3; > > > cell = ws1.Range("B1"); > > > cell.Value
= "Open"; > > > fnt = cell.Font; > > > fnt.Bold =
True; > > > cell.HorizontalAlignment = 3; > > >
cell = ws1.Range("C1"); > > > cell.Value = "Close"; >
> > fnt = cell.Font; > > > fnt.Bold = True; > >
> cell.HorizontalAlignment = 3; > > > > > >
dn = DateNum(); > > > for (i = 0; i < BarCount; i++) >
> > { > > > yy = Int(dn[i] / 10000) + 1900; > >
> mm = Int((dn[i] % 10000) / 100); > > > dd = dn[i] %
100; > > > cell = ws1.Range("A"+(i+2)); > > >
cell.Value = StrFormat("%1.0f/%1.0f/%1.0f", dd, mm, yy); >
> > cell = ws1.Range("B"+(i+2)); > > > cell.Value =
Open[i]; > > > cell = ws1.Range("C"+(i+2)); > > >
cell.Value = Close[i]; > > > } > > >
ws1.SaveAs("c:\\Temp\\OpenClose_"+Name()+".xls"); >
> > wb.Close; > > > > > > > > >
> > > --- In amibroker@xxxxxxxxxxxxxxx,
"peakwk79" <phaser2679@> wrote: > > > > > >
> > Hi, > > > > > > > > I have been
trying to implement the COM interface to Excel for the > > >
> past few days but I simply can't get the syntax right. > > >
> > > > > For starters, I would like to get the opening and
closing > prices of a > > > > particular stock on a
particular trading day into Excel. > > > > > > >
> Can someone help? > > > > > > > > >
> > > - Winston > > > > > > > >
> >
__._,_.___
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
SPONSORED LINKS
__,_._,___
|