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;
[COLOR="Blue"]using Excel = Microsoft.Office.Interop.Excel;
using System.IO;[/COLOR]
#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/library/syyd7czh(v=VS.80).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.

Good luck.

DT

 

[HowardCohodas]

OMG. Am I ever glad I use ThinkOrSwim. Two clicks. Done. Probably there are thing you can do that I can't, but your description would cause me pause even though my daughter and son-in-law work for Microsoft in Redmond.

 

[Trader333]

Excellent work.


Paul

 

[Lodian1019]

Thanks for this great work!

Lodian

 

[wackypete2]

Are you importing real-time data? Isn't excel rather slow for that? I'm not a big excel fan but perhaps it's improved lately.

Peter

 

[DionysusToast]

Peter - I'm not using it to import real time data - I have a fairly complex app and it's something I don't want to make public - so the example above is just to show the integration.

There are performance issues - you couldn't send 100,000 messages a second but it's a lot faster than DDE was.

I am caching information in my C# code before sending it to Excel and I have circuit breakers that prevent the app from sending information too frequently. It isn't lighting fast but it is as fast as the human eye so any code where you are visually representing something can basically throttle back the messaging to something that is still as fast as you can see.

 

[HowardCohodas]

Peter - I'm not using it to import real time data - I have a fairly complex app and it's something I don't want to make public - so the example above is just to show the integration.

There are performance issues - you couldn't send 100,000 messages a second but it's a lot faster than DDE was.

I am caching information in my C# code before sending it to Excel and I have circuit breakers that prevent the app from sending information too frequently. It isn't lighting fast but it is as fast as the human eye so any code where you are visually representing something can basically throttle back the messaging to something that is still as fast as you can see.

Wow, am I ever impressed. With Excel and a DDE connection, I am able to reduce the messaging to the minimum needed to support my several decision support dashboards. 100,000 messages a second is two orders of magnitude outside of any requirement my simple mind has been able to demand for any of my decision support processes where my eyes, brain and fingers are needed for execution.

 

[DionysusToast]

DDE is fairly limited in terms of capabilities - are you still poking & peeking?

This method is rather complex as it exposes the whole excel object model. What that means is you have total control of the whole excel app. You load the worksheet into an object and then you have methods that represent everything that Excel can do - saving files, setting fonts, formulas, pivot tables, splitting cells, macros - literally every option/command that Excel has is available for you to manipulate in real time.

Let's say you considered the method of calculating the SPY index to be flawed. You have your app sending in each tick of the 500 members for Excel to consolidate into a better index. Fairly simple but a lot of messages and all it does is build up an index. Not only would the ticks be messages but you'd also have additional messages telling Excel to do stuff too.

Like I say - super-fast, it ain't BUT the UI of most trading apps that have programming languages is limited to painting stuff on a chart or a matrix, Excel is a fairly simple way to break out of that UI model.

 

[HowardCohodas]

DDE is fairly limited in terms of capabilities - are you still poking & peeking?

Have you tried the ToS/Excel connection? I can get any data item displayed in TOS placed in a cell or used in an equation or... This updates faster than my eyes can follow. But then, my calculations are pretty simplistic and I only need one screen to manage my trading which is not very intensive. I use the second screen for all my other activities, like posting here.

I'm still impressed that you require a decision support calculation that requires 100,000 data items per second. I've no powers of imagination to picture the problem you are addressing.

 

[DionysusToast]

I don't use 100,000 data items per second. I simply said it wouldn't handle that load, I didn't say I was hitting that limit. My brain makes all the trading decisions, I just need information displayed in the way I require. The data I use does update thousands of times per second at peak times but I throttle that back to an update roughly 10 times a second.

I use Ninja, it uses C# and so can use Microsoft Interop sevices to control any Microsoft Office application. There seems to be no information on the net on how to do this, so I figured it out myself and this information is provided here.

Products that do not use C# will not be able to use the interop libraries and therefore will not give total control over Excel. Total control by it's nature also means complexity. There are lots of products with Excel interfaces and I have not used them myself.

The key is that a lot of people use Ninja. Interfacing to Excel is a nice thing to have but not a decision maker for a lot of people. I wouldn't move products for a different Excel interface but I am glad I now have it figured out for Ninja. I was doing similar stuff with Excel and a set of C libraries provided by a member of the Tradestation forum - that was way simpler than Ninja yet not as functional.

 

[gktk]

hi pete great explanation, opens up so much potential