• If this is your first visit, you will have to register before you can post. To view messages, please scroll below and select the forum that you would like to visits. Questions? Be sure to check out the Forum FAQ.

Announcement

Collapse
No announcement yet.

Partner 728x90

Collapse

Dionysus New Year Present – Ninja & Excel

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

    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,
    BertrandNinjaTrader Customer Service

    Comment


      Originally posted by NinjaTrader_Bertrand View Post
      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.
      Attached Files

      Comment


        Thanks, we'll check it out.
        BertrandNinjaTrader Customer Service

        Comment


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


          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
          Last edited by NinjaTrader_JessicaP; 07-06-2018, 01:06 PM.
          Jessica P.NinjaTrader Customer Service

          Comment


            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.
            Attached Files
            Last edited by NinjaTrader_JessicaP; 07-06-2018, 01:08 PM.
            Jessica P.NinjaTrader Customer Service

            Comment


              Originally posted by NinjaTrader_JessicaP View Post
              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!

              Comment


                Originally posted by calhawk01 View Post
                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:

                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
                Code:
                excelSheet.Cells[rowCount,7]
                on NT8 chart? How can we do this? I tried adding additional declarations needed to plot:

                Code:
                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:

                Code:
                			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.

                Comment


                  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.


                  Code:
                              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));
                              }
                  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/dot...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.
                  Attached Files
                  Last edited by NinjaTrader_JessicaP; 08-14-2018, 10:07 AM.
                  Jessica P.NinjaTrader Customer Service

                  Comment


                    Originally posted by NinjaTrader_JessicaP View 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.


                    Code:
                                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));
                                }
                    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/dot...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!
                    Attached Files

                    Comment

                    Latest Posts

                    Collapse

                    Topics Statistics Last Post
                    Started by Atilla, Today, 04:05 PM
                    0 responses
                    15 views
                    0 likes
                    Last Post Atilla
                    by Atilla
                     
                    Started by Displacer, Today, 02:57 PM
                    2 responses
                    13 views
                    0 likes
                    Last Post Displacer  
                    Started by SAILIKITY, 05-22-2019, 05:41 PM
                    1 response
                    9 views
                    0 likes
                    Last Post NinjaTrader_PaulH  
                    Started by rmillar, 01-09-2019, 06:02 PM
                    6 responses
                    74 views
                    1 like
                    Last Post eDanny
                    by eDanny
                     
                    Started by mlarocco, Today, 11:39 AM
                    4 responses
                    23 views
                    0 likes
                    Last Post mlarocco  
                    Working...
                    X