Dionysus New Year Present – Ninja & Excel

[DionysusToast]

All

I recently had the need to hook Ninja up to Excel. After perusing various forums, I couldn't find any instructions on how to do it. In the end, I had to figure it out myself. I posted the instructions below on another, less public forum before Christmas but decided since then to give it to a wider audience.

If you want Ninja hooked up to Excel - here's how to do it...

Microsoft Interop

This is a pre-requisite. It allows communications between Ninja & Excel. Microsoft has 2 versions:

Office 2003
Download details: Office 2003 Update: Redistributable Primary Interop Assemblies

Office 2007
Download details: 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies

The particular dll we are interested from is: Interop.Microsoft.Office.Interop.Excel.dll
The target directory for this dll should be: <My Documents>\NinjaTrader 7\bin\Custom

You can either install the Interop Assemblies into this directory or just move the dll file there.

Referencing the DLL in Ninja

Next you need to create a reference to the Interop DLL in Ninja. To do this, you need to get to the references window which is available when editing an indicator. Just choose any indicator for this.

Open Ninja Control Centre -> Tools -> Edit NinjaScript -> Indicator -> Select ANY Indicator

Once the indicator is open, right click in the body of the indicator window and select "References". You will see a window appear that looks like this:



Click "Add", it will then open up an explorer window looking at the <My Documents>\NinjaTrader 7\bin\Custom directory - locate the dll and click "insert"



Then you should see the following in the references window:



Now you are ready to go and start writing code to integrate with Excel


to be continued...

[DionysusToast]

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.

#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;
using Excel = Microsoft.Office.Interop.Excel; 
using System.IO;
#endregion

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.

        #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

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.

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));
            }
        }

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).

[DionysusToast]

As well as finding/opening the spreadsheet - we have to find the worsheet to play with. In the above example, we are looking have a sheet named "Sheet1". The following code will return the worksheet as an object to be manipulated.

        // Return the worksheet with the given name.
        private Excel.Worksheet FindSheet(Excel._Workbook excelWorkBook, string excelSheetName)
        {    
            foreach (Excel.Worksheet excelSheet in excelWorkBook.Sheets)    
            {        
                if (excelSheet.Name == excelSheetName) return excelSheet;   
            }    
            return null;
        }

So - now we have our spreadsheet open and we have a specific worksheet from that spreadsheet loaded as an object and we can manipulate it.

If we want to change the properties, you first create an object for the range of cells you want to change. For more info on the Excel object model - go here:

http://msdn.microsoft.com/en-US/libr...=VS.80%29.aspx

Here's some basic stuff:

                excelRange = excelSheet.get_Range("B1","B201");
                excelRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.DarkBlue);
                excelRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Cornsilk);
                excelRange.Font.Bold = true;
                excelRange.ClearContents();

The first line creates an object of cells and then the following 4 lines reformat and clear the cells.

To post to excel, you need to do cell by cell (as far as I am aware). In this case, you can't use the "A1" type column/row pairs references - you have to use row NUMBER and column. Again - there could be other ways that I am not aware of.

An example of posting data into cells would be :

            excelSheet.Cells[rowCount,1] = ToDay(Time[0]);
            excelSheet.Cells[rowCount,2] = ToTime(Time[0]);
            excelSheet.Cells[rowCount,3] = Open[0];
            excelSheet.Cells[rowCount,4] = High[0];
            excelSheet.Cells[rowCount,5] = Low[0];
            excelSheet.Cells[rowCount,6] = Close[0];
            excelSheet.Cells[rowCount,7] = Volume[0];

All fairly simple so far...

[DionysusToast]

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

[NinjaTrader_Brett]

Hello,

Wow, Very nice write up sir. I will be sure to forward others users and the NinjaScript support team that ask for such functionality be directed to this well written post. Thank You for your contribution to the NinjaTrader community!

[carlton]

Toast,

I would really love to try this out. However, I'm quite a novice to excel programming.

Can you please tell me how to incorporate the declarations/code into excel.

Cheers mate.

Carlton

[carlton]

Toast,

I download Download details: 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies

However, the download doesn't seem to include the Interop.Microsoft.Office.Interop.Excel.dll

Any suggestions.

Cheers

Carlton

[carlton]

Originally Posted by DionysusToast

So now we need to actually post data to Excel.

Toast,

I've found the DLL.

I'm such a novice to programming, therefore could you please start me off on how about posting the data to excel and where to insert the code??

Cheers mate.

[DionysusToast]

Carlton

Have you been through the tutorials in the Ninjatrader help guide? That should get you started in terms of coding.

For everything else - I have attached a zip file to post number 4 in this thread that has code in it. This will make sense if you have been through the tutorials.

Cheers

Pete

[carlton]

Toast,

Thanks for getting back to me.

I have read the help guides, which is far more descriptive than the tutorials, but I couldn't find information on adding code, in either.

Cheers

[amruta]

Hi DionysusToast ans carlton,

As mention in thread # 1, I download O2003pia.msi in my copmputer.

I have Windows Vista in my computer.

After this, I am unable to find the interop.Microsoft.Office.Interop.Excel.dll in my computer.

Kindly help to find this dll or let me know how can I get this dll in <My Documents>\NinjaTrader 7\bin\Custom directory ?


Thanks
Amruta

[DionysusToast]

Take a look in the drive:/WINDOWS/assembly or drive:/WINNT/assembly folders.

Lots of people have had trouble with the install not putting the files in the target directory. I checked Microsoft site and this is where it says the files will be installed.

Let me know if they are there or not. If they are there, you can copy them into the Ninja directory above.

http://msdn.microsoft.com/en-us/library/Aa159923

[newbie0101]

Hi DionysusToast, just found your thread and tried following to experiment with it. I downloaded the installer pkg from MS to the folder you specify and then run the installer and put it there as well, but it puts nothing on my computer that i can find except the EULA,Readme,and the o2007.msi which i click on and it runs then disappears. Did search for .dlls looking for
interop.Microsoft.Office.Interop.Excel.dll
not anywhere, went in folder assembly but cannot find. I have no winnt folder that i can find. I have office 2007 running on windows 7 ultimate is there something else i need to have? Any help with this is greatly appreciated and Kudos to you for a great thread.

[newbie0101]

Hi, well got them installed and can find them, if can help anyone else the way i got there is installed Visual Studio 10, then re-installed the Download details: 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies and the dlls were installed to Local disk/program files(x86)/microsoft visual studio 10.0/visual studio tools for office/PIA/office12 and office 14 folder. (being where office 12 is in path)

[DionysusToast]

Hi

I think the install is putting them in the same place regardless of the destination directory.

Try C:\windows\assembly - that's where mine are on Windows 7. If they are there you can just copy to the Ninja folder.

Let me know how it works.

Pete