Category Other Published on 26/02/2021

cTrader Read Excel Data

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

Contact: instant chat group
Website: https://clickalgo.com

Twitter | Facebook | YouTube | Pinterest | LinkedIn

Source Code


ï»¿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] = ...
        }
    }
}

ClickAlgo

Joined on 05.02.2015

Distribution: Free
Language: C#
Trading platform: cTrader Automate
File name: Empty Project.algo
Rating: 5
Installs: 3179

Download

Warning! Running cBots downloaded from this section may lead to financial losses. Use them at your own risk.

Note that publishing copyrighted material is strictly prohibited. If you believe there is copyrighted material in this section, please use the Copyright Infringement Notification form to submit a claim.

Comments

ClickAlgo · 8 years ago

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.

jumpsolid1 · 8 years ago

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

ClickAlgo · 8 years ago

Thank you very much, it has now been corrected.

aimerdoux · 8 years ago

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

aimerdoux · 8 years ago

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

ClickAlgo · 8 years ago

Nice Job Andy.

trader_ak · 8 years ago

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 · 8 years ago

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

ClickAlgo · 8 years ago

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.

trader_ak · 8 years ago

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 · 8 years ago

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

ClickAlgo · 8 years ago

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

trader_ak · 8 years ago

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 · 8 years ago

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

GoldnOil750 · 8 years ago

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 · 8 years ago

Bug Fixes

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

Copyright © 2024 Spotware Systems Ltd. All rights reserved.
The services provided by Spotware Systems Ltd. are not available to citizens or residents of the USA. Neither is the information on our websites directed toward soliciting citizens or residents of the USA.