SmartQuant Discussion

How to read/write to Excel file in the strategy code.
Page 1 of 1

Author:  Baraz Sergey [ Mon Nov 01, 2010 1:57 pm ]
Post subject:  How to read/write to Excel file in the strategy code.

The sample code below shows how to read from Excel file and write to it while a strategy is running. It reades two sma lengths from the SampleBook.xlsx file in the OnStrategyStart method and writes the last bar's close price to the Excel file in the OnBar method.
using System;
using System.Drawing;

using OpenQuant.API;
using OpenQuant.API.Indicators;

using Interop.Excel;

public class MyStrategy : Strategy
   SMA sma1;
   SMA sma2;
   Workbook workbook;
   Worksheet worksheet;
   public override void OnStrategyStart()
      // open Excel file
      Application excel = new Application();

      workbook = excel.Workbooks.Open(@"C:\Users\s.b\Documents\SampleBook.xlsx", 0, false, 5, "", "", true, Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, null, null);

      Sheets sheets = workbook.Worksheets;
      worksheet = (Worksheet)sheets.get_Item(1);

      // read A1 and A2 values
      double length1 = (double)worksheet.get_Range("A1", "A1").Value2;
      double length2 = (double)worksheet.get_Range("A2", "A2").Value2;
      sma1 = new SMA(Bars, (int)length1);
      sma2 = new SMA(Bars, (int)length2);

   public override void OnBar(Bar bar)
      worksheet.get_Range("B1", "B1").Value2 = bar.Close;      

   public override void OnStrategyStop()
      // save and close the workbook
      workbook.Close(true, null, null);

To build the sample you should add a reference to the Interop.Excel.dll, using the following steps:

- locate the Interop.Excel.dll file at "[Installation Directory]\Framework\bin" folder
- copy it to the "[Installation Directory]\bin" folder
- from the main menu go to Tools -> Options, in the opened Options dialog click "Projects and Solutions" node and select "Build" node. Click "Add" button, choose ".NET..." option and choose the Interop.Excel.dll file.

Page 1 of 1 All times are UTC + 3 hours
Powered by phpBB® Forum Software © phpBB Group