[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

[amibroker] Re: Difficulities in getting COM syntax with Excel VBA



PureBytes Links

Trading Reference Links

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@xxx> 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
 
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/