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 [email protected], "peakwk79" <[EMAIL PROTECTED]> 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
>
