[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

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