Dionysus New Year Present – Ninja & Excel

[DionysusToast]

Hi there,

does anyone know if the method suggested in this tread works for PULLING LIVE FEEDS IN EXCEL from Ninja? I need some FX live feeds and I am thinking to get them from Kinetick but I just want to be sure that I am not wasting time and money... Does anyone know if the resulting connection is stable (no stale feed etc)?

Many thanks
BC

Yes - that's what I was doing - grabbing live depth and trade data and pushing it to Excel live.

It worked fine.

[bondcrash]

Thanks a lot DT - awesome job

BC

[sdruley]

So now we need to actually post data to Excel. The whole of Excels object model is available to you and I can't show you how to use all of it but I can show you how to do the basics. The goal of this post is to show you how to open a workbook.

1) Using Declarations

Any Indicator/Strategy that uses Excel will first need to ensure that the we include the microsoft assembly. We are also using System.IO to do some work on the file name.

[FONT=Courier New][SIZE=2]#region Using declarations
using System;
using System.ComponentModel;
using System.Diagnostics;
using System.Drawing;
using System.Drawing.Drawing2D;
using System.Xml.Serialization;
using NinjaTrader.Cbi;
using NinjaTrader.Data;
using NinjaTrader.Gui.Chart;
[COLOR=Blue][B]using Excel = Microsoft.Office.Interop.Excel;[/B][/COLOR][/SIZE][/FONT] [FONT=Courier New][SIZE=2]
[B][COLOR=Blue]using System.IO;[/COLOR][/B]
#endregion[/SIZE][/FONT]

Now - you can either invoke Excel with a blank sheet OR you can have a pre-formatted spreadsheet to open. I prefer the latter - set up my spreadsheet with the correct formats etc. and then invoke it from Ninja, so we need some variables to store the file name and worksheet name. In this case, the spreadsheet is C:\DTTest.xls. Other vars here should become clear as we continue.

[FONT=Courier New][SIZE=2]        #region Variables
        // Wizard generated variables
            private int myInput0 = 1; // Default setting for MyInput0
        // User defined variables (add any user defined variables below)
            private string excelFile = @"C:\DTTest.xls"; 
            private string excelSheetName = "Sheet1";
            private bool workSheetFound = false;
            private bool excelOpen=false;
            private string fullFileName;
            private string simpleFileName;
            Excel.Application excelApp;
            Excel._Workbook excelWorkBook;
            Excel._Worksheet excelSheet;
            Excel.Range excelRange;
            private int rowCount = 1;
            private int temp;
        #endregion[/SIZE][/FONT][SIZE=2]
[/SIZE]

This part alone was a real pain in the ass to get right, although the actual code I ended up with looks so simple! If the spreadsheet is open, it will hook up to it, so you can work with the spreadsheet. If it isn't already open, then it will open it for you.

[FONT=Courier New][SIZE=2]private void OpenWorkbook(string FileName)
        {

            try
            {
                excelApp = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
            }
            catch
            {
                excelApp = new Microsoft.Office.Interop.Excel.Application();
            }
            simpleFileName = Path.GetFileName(excelFile);
            try
            {
               excelWorkBook = excelApp.Workbooks.get_Item(simpleFileName);
            }
            catch
            {
                excelWorkBook = (Excel._Workbook) (excelApp.Workbooks.Open(excelFile,
                    false, true, Type.Missing,Type.Missing, Type.Missing, Type.Missing,Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing,Type.Missing, Type.Missing, Type.Missing,Type.Missing));
            }
        }[/SIZE][/FONT]

The OpenWorkbook method, when invoked will ensure your workbook is open & ready to use (note that I'll put up a full NinjaScript at the end).

Dionysus,

I am so impressed with your work in creating a bridge between the millions of users out there with Excel and NT... well done!
I have incorporated the Interop dll into NT and have reviewed your code.
The next questioin I have is how does one incorporate your code into NT... what steps do I follow?

[sdruley]

BigSteveExcel

Dionysus,

I appreciate your work with BigMike... well I'm BigSteve. And, in the interest of beginning with the end in mind, I have attached a file that describes the type of data I need from Ninja Trader in real time. I would like to know if you believe this is possible and would like your help with some sample code. I would be willing to pay you a fee if necessary to get this endstate. What are your thoughts?

Steve

This is pretty much all there is to it. So - it's time for an example.

I am attaching an indicator called "BigMikeExcel" (no prizes for guessing which forum I posted this to first), as usual - just attach it to a chart. Note that you will not be able to import this indicator if you haven't installed and referenced the Microsoft DLL. This is a simple indicator without clutter for you to examine how this works. Please don't complain about there being little in the way of error trapping here!

When you attach the indicator, you will see that you have a space to put in Excel File and workseet name. I am attaching a spreadsheet that can be used. You can put it anywhere on your hard drive - just make sure you change these parameters.



The indicator will do the following:
1 - Find the open spreadsheet OR open it if it is not already open
2 - Format the background colours & foreground colours of colums A-G
3 - Put in headers in row 1
4 - Post the date, time, OHLC, volume to columns A-G
5 - When it's posted 200 prices, it'll start again from the top & keep rolling round

I know this is fairly useless in terms of functionality - it's just to show you how to get the comms working.

Your spreadsheet should look like this:



That's it - any questions - feel free to ask. Like I say, I'm not a C# programmer, so my code wont be perfect. The whole of Excels object model is available so you can literally do anything that Excel can do.

NOTE - on other forums, I also included a spreadsheet "DTTest.rar" - unfortunately, this forum won't let me attach that !!

Good luck.

DT

[tickling]

Thank you Dionysus for this. A true gem !

I have got it to work. However, I do not have autocomplete in Ninja editor for the excel functions and I am having a hard time figuring out the function calls and parameters. Any suggestion/bypass ?

[flyswatter77]

VBA code to C#

Hello all,

I am new to ninjatrader and was looking for a way to convert VBA code to c# and I noticed this thread connecting excel to ninjatrader. It is my understanding that excel is written in VBA and that NT is written in C#. Is that correct, and if so how difficult would it be to convert vba code to c#?

I have some code written for another trading software platform that uses vba and the developer has given permission to convert it for use in other platforms. But I am not a programmer and do not understand the complexities of doing so.

Any advice for how to go about doing this?

Thank you for any constructive feedback,

Michael

[coolmoss]

Could someone provide a hint or link as to a tiny bit of sample code to read from Excel. Specifically, I'm trying to figure out what the code would look like to grab a value from a particular excel cell, and assign that value to a variable in Ninja.

Thanks much to all who contributed on this.

[Saxo_]

Could someone provide a hint or link as to a tiny bit of sample code to read from Excel. Specifically, I'm trying to figure out what the code would look like to grab a value from a particular excel cell, and assign that value to a variable in Ninja.

Thanks much to all who contributed on this.

It would also interest me how to read values from an Excel sheet. I have an Excel sheet where I have instruments listed and in the columns have values I want to read into NT per instrument (if they are found in the Sheet).

When trying to apply the Excel integration steps outlined in the current thread I pretty much ran into the problems mentioned by others here (installing a too old Interop.Excell.dll being unable to find a new separate one on the Internet => going for forrestang's solution to look inside the Windows folder - but not getting further than post #38 - because I can see files with desired names under Windows - but I'm unable to select them for reference in NT or to copy them into NT ... and I'm not sure that they are dlls, because they don't have the file extension and don't have the same icon as shown in forrestang's screen shots in #38). So currently I'm stuck trying to execute the code mentioned in the current thread.

Looking for other read/write solutions I found:

http://www.bigmiketrading.com/ninjatrader-programming/732-writing-reading-data-into-out-ninjatrader.html

It contains a SimpleExcelWriter.cs that works nicely - but as the name says ... it is only a writer.

[tickling]

Well I got it working to read and write after following this thread, took some fiddling though but now it is working in production with no problems.

You may have found the wrong DLL - it is not the same for each version of Excel. Which excel do you have installed ? Make sure you have the right one.

Now windows explorer does not display those files the regular way for whatever reason, esp in those system directories. However they are there and can be copied around. I had to use command prompt to handle them and copy them.

Hope this helps

[Saxo_]

Well I got it working to read and write after following this thread, took some fiddling though but now it is working in production with no problems.

You may have found the wrong DLL - it is not the same for each version of Excel. Which excel do you have installed ? Make sure you have the right one.

Now windows explorer does not display those files the regular way for whatever reason, esp in those system directories. However they are there and can be copied around. I had to use command prompt to handle them and copy them.

Hope this helps

Thanks tickling - that was really helpful. But there was more to my problem. Here is what I did (hopefully this can help others - so that is why I'm outlining all the details):

1) Started cmd.exe as administrator by writing 'cmd.exe' in the 'Start search'-bar under 'Start' and then right click on the 'cmd.exe' that appears as search result - and the select 'Run as administrator' (the is to prevent 'Access denied' when copying the dll). Used 'cd [dir name]' (use 'cd ..' to leave dir and write 'dir' to see content of a directory) to drill down to the folder in C:>Windows>Assemby>GAC>Microsoft.Office.Interop.Ex cel>12.0.0.0__71e9bc11e9429c (find a similar folder) where the dll is found. Then write 'copy Microsoft.Office.Interop.Excel.dll c:\' (or another destination) and the file Microsoft.Office.Interop.Excel.dll is copied to c:\.

2) Then I moved the file to the folder: C:\....\Documents\NinjaTrader 7\bin\Custom

3) In NinjaTrader I added the reference to the file by opening an indicator in edit mode and right clicking on the code and then press 'References...' and selecting 'C:\....\Documents\NinjaTrader 7\bin\Custom\Microsoft.Office.Interop.Excel.dll'

4) Restarted the computer just to be on the safe side.

5) When I tried to run the 'BigMikeExcel' I still got the message, that made me believe that I was running an outdated version of the Microsoft.Office.Interop.Excel.dll (the one from Office 2003). The message said: "Error on calling 'OnBarUpdate' method for indicator 'BigMikeExcel' on bar 0: Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))
". I found the following http://www.add-in-express.com/creati...-type-library/ that suggested to install the MS Office Multilingual User Interface Pack to fix the problem, which requires the Office Communicator to be installed - which I did. But I still got the same error message. Changing the 'Primary editing language' to 'English (United States)' under 'Microsoft Office 2007 Language Settings' did not help either (and I did restart the computer). I then followed the code advice from the site and build the following code into the BigMikeExcel indicator:

using System.Threading;
using System.Globalization;


- and
protectedoverridevoid Initialize()
{

System.Globalization.CultureInfo newCulture = new System.Globalization.CultureInfo("en-US");
System.Threading.Thread.CurrentThread.CurrentCultu re = newCulture;
}

- and this finally made the integration and script run without problems I don't know if this is a general MS Office 2007 problem or because en-US not being the default language.

Note that I did not have to extract and reference other dlls than the one for Excel (a previous post mentioned 2 others).

[tickling]

OK over my head here. I have Excel 2003, up to your item 5 all match, then I got it to work.
It does seem to relate to non-us thing.

Check this out, may help

http://stackoverflow.com/questions/5180713/old-format-or-invalid-type-library-exception-from-hresult-0x80028018-type-e

I kept this excel - and machine - strictly US-en only because of too many marks on my back. No Multilingual pack, no globalization issues

Good Luck

[Tdschulz]

I recently installed BigMikeExcel but get the error "Error on calling 'OnBarUpdate' method for indicator 'BigMikeExcel' on bar 65535: Exception from HRESULT:0x800A03EC"
It consistently gives me 65535 lines in the spreadsheet but then stops working and gives that error.
Has anyone else ran into a limitation on the amount of excel lines like this? Any suggestions or ideas why this might occur?

[Tdschulz]

Never mind. It's an excel limitation when writing data directly to the program. I should have researched before posting. Any one know how to change BigMikeExcel to write to a .csv file instead? Apparently writing to a .csv and then pasting into excel is a workaround.

[Radical]

Hi Tdschulz, I'm not familiar with the BigMikeExcel indicator I believe the 'ExportData' strategy can write to csv:

http://www.ninjatrader.com/support/forum/showpost.php?p=266227&postcount=21

[coolmoss]

No excel output

Wondering if anyone has any suggestions...

I finally found the dll online and placed it in \Custom folder of NT. I imported the BigMikeExcel indicator. I verified the interop reference. I compiled the indicator and added it to a chart. I have the correct excel spreadsheet saved and opened in the correct location. Everything "works" but nothing happens to the spreadsheet and no error messages arise.

EDIT: I forgot to check the log. I don't know exactly what this means....

Error on calculating indicator min/max value for indicator 'BigMikeExcel'. Please check the 'GetMinMaxValues' method: Could not load file or assembly 'Microsoft.Office.Interop.Excel, Version=10.0.4504.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. The system cannot find the file specified.