cTrader Simplified Excel Write free

by ClickAlgo in category Other at 27/12/2015
Description

The robot gets the market series data for an instrument and stores the information in an excel file specified from your user-defined parameters. To operate you run a back-test between the start and end dates for the data that you wish to save and execute the backtest, at the end of the test an excel file is created or updated with the following data, but you can use it store any data you wish.

Unfortunately, the cAlgo platform does not like compiling the robot with the referenced NPOI assemblies, so you will need to comment all the code below where it says 

// UNCOMMENT CODE BELOW

Also, you can only use this example if you open it with Visual Studio and manage references with NuGet as explained below:

The robot is an example using a 3rd party utility to simplify writing data to an excel file using NPOI
https://npoi.codeplex.com/

Usage With Visual Studio

The simplest method to set this up is to edit the cBot with the visual studio from cAlgo and from visual studio Manage NuGet Packages from the solution and then search for NPOI online, simply install the package and you’re ready to go.

You should see the file references as shown below:

What's NPOI

This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project which can help you read/write xls, doc, ppt files. It has a wide application.

For example, you can use it to

a. generate a Excel report without Microsoft Office suite installed on your server and more efficient than call Microsoft Excel ActiveX at background;

b. extract text from Office documents to help you implement full-text indexing feature (most of time this feature is used to create search engines). 
c. extract images from Office documents

d. generate Excel sheets that contains formulas

NPOI advantages 
a. It's totally free to use 
b. Cover most Excel features (cell styles, data formats, formulas and so on) 
c. Supports .xls, .xlsx, .docx 
d. Designed to be interface-oriented (in NPOI.SS namespace) 
e. Supports not only export but also import 
f. .NET 2.0 based even for xlsx and docx (but it also supports .NET 4.0) 
g. Successful use cases all over the world 
h. Great amount of basic and to the point samples 

Download: https://npoi.codeplex.com/releases


Paul Hayes
Sales & Marketing
Emailcontact@clickalgo.com
Phone: (44) 203 289 6573
Websitehttps://clickalgo.com

Twitter | Facebook | YouTube | Pinterest | LinkedIn

PS: Why not join our instant chat group on Telegram.

 

Warning! Executing the following cBot may result in loss of funds. Use it at your own risk.
Notification Publishing copyrighted material is strictly prohibited. If you believe there is copyrighted material in this section you may use the Copyright Infringement Notification form to submit a claim.
Formula / Source Code
Language: C#
Trading Platform: cAlgo
using System;
using System.Linq;
using cAlgo.API;
using cAlgo.API.Indicators;
using cAlgo.API.Internals;
using cAlgo.Indicators;
using System.IO;

// UNCOMMENT CODE BELOW

//using NPOI.HSSF.UserModel;
//using NPOI.HPSF;
//using NPOI.POIFS.FileSystem;
//using NPOI.SS.UserModel;
//using NPOI.HSSF.Model;
//using NPOI.SS.Formula.Functions;

// This has been written by Paul Hayes | http://www.cAlgo4u.com | 26/12/2015

// The robot is an example using a 3rd party utility to simplify writing data to an excel file using NPOI
// https://npoi.codeplex.com/

// The robot gets the market series data for an instrument and stores the information in an excel file specified from your 'user defined parameters'.
// To operate you back-test between start and end dates for the data that you wish to save and at the and of the test an excel file is created or updated 
// with the following data shown below. You can also use it to store any data you wish.

// OPEN TIME
// OPEN PRICE
// HIGH PRICE
// LOW PRICE
// CLOSE PRICE
// TICK VOLUME

namespace cAlgo
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FileSystem)]
    public class cAlgo4uSimplifiedExcelWrite : Robot
    {
        // User enters the actual path to the media file.
        [Parameter("Excel File Path", DefaultValue = "C:\\Users\\Paul\\Documents\\test.xls")]
        public string ExcelFilePath { get; set; }

        // UNCOMMENT CODE BELOW

        //static HSSFWorkbook hssfworkbook;
        //HSSFSheet workSheet;

        #region cTrader events

        protected override void OnStart()
        {

        }

        protected override void OnTick()
        {
        }

        /// <summary>
        /// When back-testing is complete, when the robot stops, all th data gathered is written to the excel file.
        /// </summary>
        protected override void OnStop()
        {
            // UNCOMMENT CODE BELOW

            //InitializeWorkbook();

            //// create xls if not exists
            //if (!File.Exists(ExcelFilePath))
            //{
            //    hssfworkbook = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());

            //    // create work sheet
            //    workSheet = (HSSFSheet)hssfworkbook.CreateSheet("MARKET SERIES");

            //    CreateColumnHeaders();

            //    // iterate through the entire date range for the back-test and export the data to an excel file.
            //    for (int i = 1; i < MarketSeries.Close.Count; i++)
            //    {
            //        var r = workSheet.CreateRow(i);

            //        // create columns
            //        r.CreateCell(0).SetCellValue(MarketSeries.OpenTime[i]);
            //        r.CreateCell(1).SetCellValue(MarketSeries.Open[i]);
            //        r.CreateCell(2).SetCellValue(MarketSeries.High[i]);
            //        r.CreateCell(3).SetCellValue(MarketSeries.Low[i]);
            //        r.CreateCell(4).SetCellValue(MarketSeries.Close[i]);
            //        r.CreateCell(5).SetCellValue(MarketSeries.TickVolume[i]);
            //    }

            //    using (var fs = new FileStream(ExcelFilePath, FileMode.Create, FileAccess.Write))
            //    {
            //        hssfworkbook.Write(fs);
            //    }
            //}
        }

        #endregion

        #region excel logic

        /// <summary>
        /// Create the column headers on the first row
        /// </summary>
        private void CreateColumnHeaders()
        {
            // UNCOMMENT CODE BELOW

            //var r = workSheet.CreateRow(0);
            //r.CreateCell(0).SetCellValue("OPEN TIME");
            //r.CreateCell(1).SetCellValue("OPEN");
            //r.CreateCell(2).SetCellValue("HIGH");
            //r.CreateCell(3).SetCellValue("LOW");
            //r.CreateCell(4).SetCellValue("CLOSE");
            //r.CreateCell(5).SetCellValue("TICK VOLUME");
        }

        /// <summary>
        /// initialize the document's summary information, its more of a nice to have.
        /// </summary>
        static void InitializeWorkbook()
        {
            // UNCOMMENT CODE BELOW

            //hssfworkbook = new HSSFWorkbook();

            //////create a entry of DocumentSummaryInformation
            //DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            //dsi.Company = "cAlgo4u";
            //hssfworkbook.DocumentSummaryInformation = dsi;

            //////create a entry of SummaryInformation
            //SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            //si.Subject = "cAlgo4u writing to excel example";
            //hssfworkbook.SummaryInformation = si;
        }

        #endregion
    }
}
Comments

aisaac - January 22, 2016 @ 20:48

good day ,how can use this robot ? step by step please.

for example , 1.install NPOI 2. run cbot on calgo  other solution 1.add istance on calgo eur\usd for example and run cbot 2.change , 40. [Parameter("Excel File Path", DefaultValue = "C:\\Users\\Paul\\Documents\\test.xls")] with your folder etc....

 

thanks.

ClickAlgo - January 29, 2016 @ 11:03

Hi Aisaac, Have you managed to use the library in your project?

amosmsiwa - March 30, 2017 @ 22:54

Hi, Mr Hayes, I'm looking to extract closing sample market data for lets say USDGBP on a daily basis to load to excel for quantitative analysis

namespace cAlgo
{
    [Robot(TimeZone = TimeZones.UTC, AccessRights = AccessRights.FullAccess)]
    public class cAlgo4uExcelcBotExample : Robot
    {
        #region excel data objects

        // class name must be same as the excel sheet name. The values must be same as the column names. This is will contain just a single row
        public class DailyTrade
        {
            [ExcelColumn("Friday")]
            public int ID { get; set; }

            [ExcelColumn("Thursday")]
            public DateTime ExpiryDate { get; set; }

            [ExcelColumn("Wednesday")]
            public long Volume { get; set; }

            [ExcelColumn("Tuesday")]
            public string TradeType { get; set; }

            [ExcelColumn("Monday")]
            public double EntryPrice { get; set; }

            
        }

        #endregion

 

amosmsiwa - March 30, 2017 @ 22:56

To load data to SSIS and SSAS (SQL Server)

amosmsiwa - March 30, 2017 @ 22:59

Using Select * From * where Alpha>1 and Beta<1

0