Dionysus New Year Present – Ninja & Excel

[NinjaTrader_Bertrand]

Thanks for the post calhawk01, could it be you accidentially posted the MACD indicator instead of the test script you tried on NT8? If we had that, I would ask our QA team to take a quick look if this popular technique could made work in principle for NT8 as well. Thanks again,

[staycool3_a]

Thanks for the post calhawk01, could it be you accidentially posted the MACD indicator instead of the test script you tried on NT8? If we had that, I would ask our QA team to take a quick look if this popular technique could made work in principle for NT8 as well. Thanks again,

You're right! I accidentally posted the macd indicator lol. See attached. Add the excel reference to any indicator and then Add the indicator to indicator folder and open up NT8.

[NinjaTrader_Bertrand]

Thanks, we'll check it out.

[NinjaTrader_JessicaP]

Hello calhawk01,


While there is still more work to be done before we have a working solution, I wanted to let you know how to reference the correct .dll right away. Microsoft Office Tools for Visual Studio became a built-in after 2010, so we can find the correct .dll by creating a new Visual Studio 2017 Community project. This video has the details.

https://jessica-ninjatrader.tinytake.com/sf/Mjc0NDUzM184MjI3ODgx

Steps taken in that video

1. Create a new Excel 2013 and 2016 VSTO Workbook project in Microsoft Visual Studio 2017
2. In the Project Explorer, under References, double-click Microsoft.Office.Interop.Excel
3. Copy its path to your clipboard
4. In the NinjaScript editor, right-click and select references
5. Click add
6. In the path portion of the explorer window that comes up, paste what you have in the clipboard in, minus the file portion
7. Double-click Microsoft.Office.Interop.Excel

I am happy to answer any questions I can

[NinjaTrader_JessicaP]

With the above post in mind, and keeping in mind that this is not a service we typically provide, since this has proven to be one of the more popular tools on the forums, I am happy to provide a complete NT8 solution.


You will need to create the excel file C:\DTTest.xlsx on your computer for this to work.


One other note : I used an extension method I wrote to convert WPF brushes to OLE codes directly. This was done because NinjaTrader 8 uses WPF exclusively, and there are many System.Drawing objects which share namespace with WPF objects.


Please let us know if there are any other ways we can help.

[staycool3_a]

With the above post in mind, and keeping in mind that this is not a service we typically provide, since this has proven to be one of the more popular tools on the forums, I am happy to provide a complete NT8 solution.


You will need to create the excel file C:\DTTest.xlsx on your computer for this to work.


One other note : I used an extension method I wrote to convert WPF brushes to OLE codes directly. This was done because NinjaTrader 8 uses WPF exclusively, and there are many System.Drawing objects which share namespace with WPF objects.


Please let us know if there are any other ways we can help.

Jessica! Thank you so much! Apologies for the tardy response, I got busy with work stuff. I'm going to try this out asap and get back to you!

[staycool3_a]

Jessica! Thank you so much! Apologies for the tardy response, I got busy with work stuff. I'm going to try this out asap and get back to you!

Works great! Thank you so much!

I'd hate to ask but I think you're like a super programmer so here it goes:

Many people on this same exact thread have asked for a way to feed data from excel to NT. The current method writes to Excel. How can we read from excel? Using the same example code:

           
            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];

Say I wanted to plot

excelSheet.Cells[rowCount,7]

on NT8 chart? How can we do this? I tried adding additional declarations needed to plot:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Xml.Serialization;
using System.ComponentModel.DataAnnotations;
using Excel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Windows.Media;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Input;
using System.Windows.Media;
using System.Xml.Serialization;
using NinjaTrader.Cbi;
using NinjaTrader.Gui;
using NinjaTrader.Gui.Chart;
using NinjaTrader.Gui.SuperDom;
using NinjaTrader.Data;
using NinjaTrader.NinjaScript;
using NinjaTrader.Core.FloatingPoint;
using NinjaTrader.NinjaScript.DrawingTools;

and then tried:

			Value[0] =excelSheet.Cells[rowCount,7];

However, because excelSheet.Cells[rowCount,7] is an object, and Value[0] is a double; it does not compile.

I think if we can get this final solution to work, it would be much appreciated! It would provide NT8 community a single thread where users can read AND write to an excel file using an unsupported method.

Thank you in advance.

[NinjaTrader_JessicaP]

The safest way to approach this is to use double.Parse inside a try/catch block. This way, if you didn't actually feed a double value into the spreadsheet cell you are accessing, you will get a log message that will point you directly at what happened.

[FONT=Courier New]            try
            {
                double example = double.Parse((excelSheet.Cells[rowCount, 6] as Excel.Range).Value.ToString());
                Print(String.Format("Close price is {0:0.00}", example));
            }
            catch (FormatException e)
            {
                Print(String.Format("6th row ({0}) was not a double, received {1}", excelSheet.Cells[rowCount,6], e));
            }[/FONT]

Regarding "Excel.Range", since as you mentioned this is a complicated object, we need to find publicly available documentation describing the object to know how to access this object. Microsoft's Range interface is documented fully at this publicly available link,

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range?view=excel-pia

I have attached a working example starting on line 78 of the attached script. Please let us know if there are any other ways we can help.

[staycool3_a]

The safest way to approach this is to use double.Parse inside a try/catch block. This way, if you didn't actually feed a double value into the spreadsheet cell you are accessing, you will get a log message that will point you directly at what happened.

[FONT=Courier New]            try
            {
                double example = double.Parse((excelSheet.Cells[rowCount, 6] as Excel.Range).Value.ToString());
                Print(String.Format("Close price is {0:0.00}", example));
            }
            catch (FormatException e)
            {
                Print(String.Format("6th row ({0}) was not a double, received {1}", excelSheet.Cells[rowCount,6], e));
            }[/FONT]

Regarding "Excel.Range", since as you mentioned this is a complicated object, we need to find publicly available documentation describing the object to know how to access this object. Microsoft's Range interface is documented fully at this publicly available link,

https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel.range?view=excel-pia

I have attached a working example starting on line 78 of the attached script. Please let us know if there are any other ways we can help.

Jessica, Thank you!

OP, Thank you!

NT Community: this thread should serve as a good way to read and write to Excel!

For writing to excel, please see OP and the latest version of indicator Jessica posted earlier. I'm just attaching the indicators for everyone in a single post.

NtToExcel will write to excel.
ExcelToNT will plot values to NT chart.

You will still need to follow the procedures to add Excel Interlop! See previous posts for directions on how to do that.

The ExcelToNT indicator is a very dirty version, you will have to modify the excel sheet. For my purposes, I had values in Excel that I wanted to plot to NT. So what I did was, I used NtToExcel indicator and retrieved the date and time in column A and B in the excel file attached, Then, combined the date and time in column I, then did a vlookup for the appropriate values in column H from sheet2 (where my data is sitting) and then the indicator is able to plot these values in a chart in NT.

I think this is sufficient enough for anyone to get started for connecting NT with Excel for reading and writing. Good luck!

Thanks to OP and Jessica!

[FelixMMM]

Hello,
I´m quite new to programming. I have already programmed my strategy, but for it to work i need to use data from an excel file. I need to access the digits of an indicator, which results i plot over time on excel. Now i would like to acces that number and read it out. I have already followed those steps above and already got that dll file, but now i struggle to programm how to access that one digit, which i would use to create a boolean on wheter i would like to enter a trade or not. How can i do that, im stuck.
Thanks for your help,
i appreciate a lot, Felix

[NinjaTrader_Kate]

Hello FelixMMM,

Thank you for your note.

I would recommend starting with staycool3_a's ExcelToNT.cs in the post before yours. This seems to be a variation on Jessica's Excel8 script that takes values from an Excel file and saves them to a data series.

You may also want to check out Jessica's advice on this from a previous post:

The safest way to approach this is to use double.Parse inside a try/catch block. This way, if you didn't actually feed a double value into the spreadsheet cell you are accessing, you will get a log message that will point you directly at what happened.

[FONT=Courier New] try
{
double example = double.Parse((excelSheet.Cells[rowCount, 6] as Excel.Range).Value.ToString());
Print(String.Format("Close price is {0:0.00}", example));
}
catch (FormatException e)
{
Print(String.Format("6th row ({0}) was not a double, received {1}", excelSheet.Cells[rowCount,6], e));
}[/FONT]

If this gets too complicated, you may want to look into using StreamReader/StreamWriter instead:

https://ninjatrader.com/support/helpGuides/nt8/en-us/using_streamreader_to_read_fro.htm

https://ninjatrader.com/support/helpGuides/nt8/en-us/using_streamwriter_to_write_to.htm

Writing to and reading from Excel can be pretty advanced and a lot of this thread verges on unsupported code, and you've said you're pretty new to programming. If you're just needing to use values from an indicator in a strategy, you can actually call that indicator inside your strategy and use the signals it generates to place your trades.

I'd recommend writing into us at platformsupport[at]ninjatrader[dot]com so we on the NinjaScript team can discuss with you what you're trying to achieve and help you with some educational resources and examples. I'd also recommend checking out the SampleMACrossOver strategy that comes with the platform that you can open up in the NinjaScript editor as an example of using indicators within a strategy.

Please let us know if we may be of further assistance to you.

[FelixMMM]

Thank you Kate for taking time. I love the support here.
For my Strategy i just need to read 1 Cell at a specific date. So as an example; if its Monday the 22 nd in January the Strategy should read that cell and give back one number. I dont quite now how to do that and some more help would be nice.
Thank you very much for your help
Felix

[Branpo]

Hi guys,

since youve got quite some experience with NT and Excel maybe you can help me with a Problem on NT8.

I made an indicator that imports and processes some Data from a csv i created manually.
It imports the Numbers and compares it to each other after that it outputs 1, 2 or 3 as a plot on the chart.
So far everything is working perfectly as i intended it to do.
But if i try to use the output in the Strategy Builder it stops working.

Instead of the 1,2 or 3 it Displays on the Chart it outputs only 0.

I added some screenshots, the cs and the csv im reading from, the csv needs to be in NinjaTrader.Core.Globals.UserDataDir\APData\COT\OI \ but feel free to change that in the code.

I would be very gratefull if you could help me!

Thanks a lot in advance

Branpo

[Branpo]

Here are the csv and the cs

[NinjaTrader_Kate]

Hello Branpo,

Thank you for your post.

I've made a quick test script and ran into the same issue. I'll confer with my colleagues first thing in the morning and see what they think may be causing the issue.

Thanks for your patience; I look forward to resolving this for you.