Read Stock Pair Names in Excel and Store them in a List

Author: Shourui

Creation Date: 9/15/2010 2:39 PM

Wealth Lab » WealthScript (.NET) » Read Stock Pair Names in Excel and Store them in a List

Shourui

9/15/2010 2:39 PM

Can anyone help me translate the following 4.5 chartscript?
It read stock pair information from a excel file and store them in different Tlists. Thanks a lot.

CODE:
Please log in to see this code.

Size:

Color:

Can anyone help me translate the following 4.5 chartscript?
It read stock pair information from a excel file and store them in different Tlists. Thanks a lot.
[CODE]if getsymbol<>watchlistsymbol(0) then exit;
const dir = 'C:\temp\';
var XLS: ComVariant = CreateOleObject('Excel.Sheet');
var XL: ComVariant = XLS.Application;
var XLW: ComVariant;
const XLName = 'PairsTrades.xls';
const XLSName = 'PairsTrades';
var XLWfound: boolean;
XL.DisplayAlerts := true;
XL.ScreenUpdating := false;
var j: integer;
if XL.Visible then
for j := 1 to XL.WorkBooks.Count do begin
    if uppercase(XL.WorkBooks(j).Name) = uppercase(XLName) then
    begin
      XLWfound := true;
      XLW := XL.WorkBooks(j);
      break;
    end;
end;
if not XLWfound then begin
   var File: COMVariant = CreateOleObject('Scripting.FileSystemObject');
   if not File.FileExists(dir + XLName) then begin
      ShowMessage('File "' + dir + XLName + '" not found');
      exit;
   end;
   XLW := XL.WorkBooks.Open(dir + XLName);
end;
XLS := XLW.WorkSheets(XLSName);

var w: integer;
var TopList:TList = TList.create;
var BottomList:TList = TList.create;
var UniverseList:TList = TList.create;
var SectorList:TList = TList.create;
var SharpeList:TList = TList.create;
var Top:string;
var Bottom:string;
var Universe:string;
var Sector:String;
var Sharpe:string;

for w := 1 to 65536 do begin
      Top:=XLS.cells(w,1).text;
      Bottom:=XLS.cells(w,2).text;
      Universe:=XLS.cells(w,3).text;
      Sector:=XLS.cells(w,4).text;
      Sharpe:=XLS.cells(w,5).text;

if length(Top)=0 then break;
      TopList.add(Top);
      BottomList.add(Bottom);
      UniverseList.add(Universe);
      SectorList.add(Sector);
      SharpeList.add(Sharpe);
      print(inttostr(w)+','+Top+','+Bottom);
end;

[/CODE]

Eugene

9/15/2010 4:02 PM

There are two ways that you can go: translating this to C# or rewriting the code from scratch. There are countless examples on the net so below you'll find just a couple of pointers:

1. The first one assumes you're still using COM i.e. Excel.Sheet.

Reading Excel From C#
How to automate Excel by using Visual C# to fill or to obtain data in a range by using arrays

I guess that Visual Studio Tools for Office is a prerequisite.

2. The other means dropping COM and reading Excel sheet names and cell values using database technology:

Reading and Writing Excel Spreadsheets Using ADO.NET C# DbProviderFactory

It's good that you're using "XLS" files and not "XLSX". If your operating system is 32-bit, consider yourself lucky: using ODBC is relatively easy.

Using Office 2007/2010 files and especially a 64-bit Windows edition opens a big can of worms. MS Jet 4.0 is incompatible with Win x64, so instead of the built-in driver, installing an additional component would be required (2007/2010 Office System Driver: Data Connectivity Components, x64 version) and the connection string will be different.