Ninja Indicator and Excel

[RedDuke]

Hello,

I have an excel file that gets updated in real time from an external feed. It then uses this data to perform calculations. Is there any way I can link a cell of this file into Ninja indicator? Is it is possible, where can I find documentation on how to do it?

Thanks,
redduke

[NinjaTrader_Ray]

Anything is possible in .NET but this is outside what we support and can provide documentation/reference samples for.

[RedDuke]

With a help of a fellow .net developer I was able to develop an indicator that will read dynamic excel content (by dynamic I mean it can be changed real time, and it does not have to be saved). There 2 glitches (will discuss below) currently, but it is working for the most part.

In order to compile attached code (remove .txt extension) you would need to create as assembly reference to System.Data.dll. Once you upload indicator, right click within indicator editor and go to References and click on Add. Navigate to C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322 and choose System.Data.dll.

The indicator is developed to extract value of cell A1 from Sheet2 for Ninja 6.5 version. It is triggered when a trade goes through and its volume is greater than 50 (you can add your own conditions). I perform all my calculation in Sheet1 (based on real time input from X-trader market data) and then link results to cell A1 of Sheet2. You want to keep as little data within selected Sheet as possible for performance reasons, but it does not have to be 1 cell if you need more obviously. Once value is extracted it is displayed within Output Window. This indicator has the least code that is required to get the data, you can add as much of your own as needed. I also added commented through out code to help with understanding of what is going on.

Now the glitches:

1) After some time (it is random), you will get Excel popup saying “that you trying to open an existing file and all data will be lost”. All you need to do is just say No. However until you do it your Ninja chart will be frozen. This used to happened much more frequently until I added code to call Garbage Collected after every 5 trades and closing/reopening connection after every 10 trades. This helped tremendously, but did not solve issue entirely. I usually get about 2 popups every hour.
2) I think it is related to 1. Once I try to disconnect, Ninja freezes and I have to close it via Task Manager. I have also seen similar freeze when I tried to place sim trade via DOM. So, it is Ok to use for charting purpose only for now.

If anyone has any insight on how these issues please reply.

Regards,
redduke

[Jim-Boulder]

Opening Excel file

My immediate question is whether you are opening the Excel file in read only mode or read/write mode...and whether Excel's autosave feature is then trying to save it midstream.

I apologize, but haven't had a chance to read your code yet.

[RedDuke]

Hi Jim,

I beleive it is being opened in read, but I am not sure, since I used the sample code myself.

I will disable auto save and see what happens.

If you can take a look at how connection is established and reply, it would be great.

Thanks,
redduke

[RedDuke]

I disabled auto save, but the issue still surfaced.

Regards,
redduke

[Jim-Boulder]

I've replicated the Excel Message

Excel's warning message is occuring becuase you are attempting to open the same file while it has already been opened, after data has changed on the sheet. It will warn you that re-opening will cause these changes to be lost.

What is probably happening is that you open the file to read the data, and while it is open the Excel sheet is updated by your real time data feed, and then your code attempts to open it again, rather than read from it without re-opening.

Josh has provided some sample code reading and writing from external files here:

http://www.ninjatrader-support.com/vb/showthread.php?t=3477

(there are other examples in the "Reference Code" section)

and in these you will notice how he controls the opening of the file to the first bar, then processing just the reading or writing on each qualified bar thereafter. This way the file is opened only once for the entire session.

I can't be sure if your code is doing this as I still have not read it in detail, but I did replicate the message in Excel by doing what I described above. If you simply open a file in Excel then attempt to reopen it it will not give you this warning unless something has changed on the sheet. This may explain the random or sporadic messages (maybe the data only changes every so often and thus the message isn't presented everytime), and since it is a warning message it requires the user to "dismiss" so it is suspending all processing awaiting your response.

Hope this helps.

[RedDuke]

Hi Jim,

I have seen Josh's code. The reason why excel pop up is strange is becuase the file gets updated multiple times within each seconds and constantly being read, yet the pop up only comes up 2-4 times per hour. And it used to come up much more often, before garbage collector call was added.

Thanks,
redduke