cTrader Read Excel Data free

by ClickAlgo in category Other at 28/11/2015
Description

Execute trades example using LinqToExcel and LINQ to easily read an Excel or CSV file into your Automated Trading Robot.

Download Source Code Here

Please note that this is an example and not a working robot.

I found this very useful tool for working with Microsoft Excel data that I would like to share with you. If you need to get data out of Excel, which can be done using ADO.NET. However using LINQ to Excel makes this very easy for people who are not experienced programmers.

DATA - ANALYSIS - TRADE

This robot is an example to demonstrate the power you can have at your fingertips using cTrader, cAlgo and C#, this robot reads trades from an excel file and executes them in real time with the robot, you can dynamically modify the trade results with user defined parameters from the robots user interface or from within the code.

Watch uTube video about LinqToExcel to find out more...

https://www.youtube.com/embed/t3BEUP0OTFM


THE SIMPLEST WAY OF READING DATA FROM EXCEL

The example shows a list of trades for the day that have been entered onto a spreadsheet with separate sheets for different instruments, it does not matter if this would not be useful in real life, it is just to show what can be accomplished and possibilities. The image below shows information for opening new positions when the price reaches the entry price, the expiry date and time allows you to filter these out using LINQ from within your robot.

You will notice in the source code that there is a class called DailyTrade, this class is the data container which will automatically be populated with data from the spreadsheet. 

As you can see the class name is the same as the spreadsheet name dailyTrades.xls and each property has an attribute [ExcelColumn], this maps to the name of columns in the spreadsheet, so the property name does not have to be the same as the property name and you can have spaces in the column name.

The code that injects all the data into the class is a collection of DataTrade objects, so you will end up with a list of daily trades which you can iterate through to open the trades. You will be able to access the data in a clean and readable manner like; trade.EntryPrice or trade.ExpiryDate.

Download Source Code Here

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 cAlgo.API;
using cAlgo.API.Internals;
using cAlgo.API.Indicators;
using cAlgo.Indicators;

namespace cAlgo
{
    [Indicator(IsOverlay = false, TimeZone = TimeZones.UTC, AccessRights = AccessRights.None)]
    public class EmptyProject : Indicator
    {
        [Parameter(DefaultValue = 0.0)]
        public double Parameter { get; set; }

        [Output("Main")]
        public IndicatorDataSeries Result { get; set; }


        protected override void Initialize()
        {
            // Initialize and create nested indicators
        }

        public override void Calculate(int index)
        {
            // Calculate value at specified index
            // Result[index] = ...
        }
    }
}
Comments

ClickAlgo - December 12, 2015 @ 13:00

Bug Fixes

12/12/2015 | issue with removing items from trade collection so it would not execute again

GoldnOil750 - December 25, 2015 @ 14:58

HI Paul, again Great Work.

Any help on how I can get/access one new row when a new "OnBar" Method is called.   My data is in the Excel file rows. I want to pick one row at a time per "OnBar"....

///S.Khan

ClickAlgo - December 28, 2015 @ 09:31

There are a few ways to do this, the easiest to do is probably using a private class index variable that is accessible to all the class methods. set it to zero at the initialize of the cBot and increase by 1 on each onBar event. You can then access the class collection which will be your excel rows, for the example above it would be:

dailyTrade = dailyTrades[index];

index++;

this will return a class containing a single excel row of data

 

Andrew Kinsella - February 03, 2016 @ 19:54

Hello Paul,

Many thanks for the cbot.

I am having some issue with the installation. Perhaps you can help me please...

When I try to run I see the following error message...

03/02/2016 17:47:31.134 | cBot "cAlgo4u Excel Example" was started successfully for EURUSD, h1.
03/02/2016 17:47:31.650 | Crashed in OnStart with InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.
03/02/2016 17:47:31.697 | cBot "cAlgo4u Excel Example" was stopped for EURUSD, h1.

 

So looks like the error is related to OLEDB....

I checked your website and followed the instructions to download the access database engine.  After the download and install the same error message remains.

So, using visual studio blend...

I built the project in there....here i see more detailed error message....

Project "C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example.sln" is building "C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.csproj" (default targets):

 

C:\Program Files (x86)\MSBuild\12.0\bin\Microsoft.Common.CurrentVersion.targets(1697,5): warning MSB3270: There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "LinqToExcel", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.

cAlgo4u Excel Example -> C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\bin\Debug\cAlgo4u Excel Example.dll

Done building project "cAlgo4u Excel Example.csproj".

The build has successfully completed.np

........

So, it seems a configuration issue...sounds to me related to 32/64 bit and the expected 32/64bit of the Linq to excel....

It looks like I am using linktoexcel 1.10.1 ( based on info I see in visual studio 2013 blend.

If you can help, I will be most grateful.

Many thanks.

Andy

 

 

 

 

 

 

ClickAlgo - February 03, 2016 @ 20:44

Hi Andy,

I hope the chat we had solves your problem, for anyone else who experiences errors like this, you will need the following:

If you have Microsoft Office Installed

  • If your Office version is 32 bit, use the 32 bit version of linqToexcel
  • If you office version is 64 bit use the 32 bit version of linkToExcel

If you do not have Microsoft Office

  • Use the 32 bit version of the Microsoft.ACE.OLEDB.12.0
  • use the 32 bit version of LinqToExcel

I will update this page and my website later

 

ClickAlgo - February 03, 2016 @ 20:45

I really wish I could edit my messages:

Correction:

If you have Microsoft Office Installed

  • If your Office version is 32 bit, use the 32 bit version of linqToexcel
  • If you office version is 64 bit use the 64 bit version of linkToExcel

If you do not have Microsoft Office

  • Use the 32 bit version of the Microsoft.ACE.OLEDB.12.0
  • use the 32 bit version of LinqToExcel

Andrew Kinsella - February 06, 2016 @ 20:19

Hi Paul,

Thanks for your help so far - much appreciated.
I not quite there yet...
Positives:
MS Visual studio community 13 is working  - I can load the project :)
I tried using the NuGet for the linqtoexcel - that did not work - I was still getting the error message  ( I did follow the instructions and change the reference to the LOG4NET ).

Then I used the link you gave and downloaded the x86 / 32 bit version  ( at least I assume that is what is was...
https://code.google.com/archive/p/linqtoexcel/downloads
Linqtoexcel 1.7.1. zip.
I added these references in but still I have error messages...see attached file.


I will be most grateful if you can help.

Many thanks.

Error messages...

 

Warning               1             There was a mismatch between the processor architecture of the project being built "MSIL" and the processor architecture of the reference "LinqToExcel", "x86". This mismatch may cause runtime failures. Please consider changing the targeted processor architecture of your project through the Configuration Manager so as to align the processor architectures between your project and references, or take a dependency on references with a processor architecture that matches the targeted processor architecture of your project.               cAlgo4u Excel Example

Error      2             The type or namespace name 'Attributes' does not exist in the namespace 'LinqToExcel' (are you missing an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           9             19           cAlgo4u Excel Example

Error      3             The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           43           14           cAlgo4u Excel Example

Error      4             The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           43           14           cAlgo4u Excel Example

Error      5             The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           46           14           cAlgo4u Excel Example

Error      6             The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           46           14           cAlgo4u Excel Example

Error      7             The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           49           14           cAlgo4u Excel Example

Error      8             The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           49           14           cAlgo4u Excel Example

Error      9             The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           52           14           cAlgo4u Excel Example

Error      10           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           52           14           cAlgo4u Excel Example

Error      11           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           55           14           cAlgo4u Excel Example

Error      12           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           55           14           cAlgo4u Excel Example

Error      13           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           58           14           cAlgo4u Excel Example

Error      14           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           58           14           cAlgo4u Excel Example

Error      15           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           61           14           cAlgo4u Excel Example

Error      16           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           61           14           cAlgo4u Excel Example

Error      17           'LinqToExcel.ExcelQueryFactory' does not contain a definition for 'UsePersistentConnection' and no extension method 'UsePersistentConnection' accepting a first argument of type 'LinqToExcel.ExcelQueryFactory' could be found (are you missing a using directive or an assembly reference?)               C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           118        22           cAlgo4u Excel Example

Error      18           'LinqToExcel.ExcelQueryFactory' does not contain a definition for 'ReadOnly' and no extension method 'ReadOnly' accepting a first argument of type 'LinqToExcel.ExcelQueryFactory' could be found (are you missing a using directive or an assembly reference?)         C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs             121        22           cAlgo4u Excel Example

Error      19           'LinqToExcel.ExcelQueryFactory' does not contain a definition for 'Dispose' and no extension method 'Dispose' accepting a first argument of type 'LinqToExcel.ExcelQueryFactory' could be found (are you missing a using directive or an assembly reference?)         C:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs             134        26           cAlgo4u Excel Example

Error      20           The type or namespace name 'Attributes' does not exist in the namespace 'LinqToExcel' (are you missing an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           9             19           cAlgo4u Excel Example

Error      21           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           43           14           cAlgo4u Excel Example

Error      22           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           43           14           cAlgo4u Excel Example

Error      23           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           46           14           cAlgo4u Excel Example

Error      24           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           46           14           cAlgo4u Excel Example

Error      25           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           49           14           cAlgo4u Excel Example

Error      26           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           49           14           cAlgo4u Excel Example

Error      27           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           52           14           cAlgo4u Excel Example

Error      28           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           52           14           cAlgo4u Excel Example

Error      29           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           55           14           cAlgo4u Excel Example

Error      30           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           55           14           cAlgo4u Excel Example

Error      31           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           58           14           cAlgo4u Excel Example

Error      32           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           58           14           cAlgo4u Excel Example

Error      33           The type or namespace name 'ExcelColumn' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           61           14           cAlgo4u Excel Example

Error      34           The type or namespace name 'ExcelColumnAttribute' could not be found (are you missing a using directive or an assembly reference?)               c:\Users\AK\Documents\cAlgo\Sources\Robots\cAlgo4u Excel Example\cAlgo4u Excel Example\cAlgo4u Excel Example.cs           61           14           cAlgo4u Excel Example

 

 

 

ClickAlgo - February 06, 2016 @ 22:05

looks like you have set the build architecture to 64 bit in visual studio and you ar referencing a 32 bt assembly, you need to change your target processor architecture to 32 bit.

if you are using 64 bit version of office you need to download the 64 bit version of linq2excel and build with the 64 it processor target.

it should be a simple fix.

ClickAlgo - February 06, 2016 @ 22:11

if office is 64 bit use 64 bit version of linq2excel, set the build architecture to 64 bit
if office is 32 bit use 32 bit version of linq2excel, set the build architecture to 32 bit

Andrew Kinsella - February 12, 2016 @ 23:49

Hi Paul,

I have uninstalled MS office 32bit and installed 64bit office - working fine now.

(My system configuration was office 32 on 64 machine - I think all the associated packages are related to 64).

Many thanks.

ClickAlgo - February 14, 2016 @ 08:59

Nice Job Andy.

aimerdoux - April 02, 2016 @ 22:45

Hi paul thank you for this Robot im trying to acces to the link that has the information of How to Install it but is seems to be broken can you helo me 

aimerdoux - April 02, 2016 @ 22:46

http://www.calgo4u.com/tutorials/algo-excel-integration.html this is the broken link that apparently is not working

ClickAlgo - April 04, 2016 @ 09:13

Thank you very much, it has now been corrected.

jumpsolid1 - April 13, 2016 @ 14:49

Hello Paul Hayes, 

first of all , great work all around o.O

secondly, may i ask you to send me an email in order to help me out with a Cbot?

My email is jumpsolid@gmail.com

Respectfully

 

J.

 

ClickAlgo - May 16, 2016 @ 07:27

Hi J,

Sorry for late reply, but I do not get informed when a message is posted on a forum. I have sent you an email.

contact@clickalgo.com

Regards,

Paul.

5