Exporting to excel...

[roonius]

I have recently came across a dll for exporting data to Excel
Haven't playd much, but it seems working.
Maybe someone will want to play as well.

1 #region Using declarations
2 using System;
3 using System.Diagnostics;
4 using System.Drawing;
5 using System.Drawing.Drawing2D;
6 using System.ComponentModel;
7 using System.Xml.Serialization;
8 using NinjaTrader.Cbi;
9 using NinjaTrader.Data;
10 using NinjaTrader.Gui.Chart;
11 using CarlosAg.ExcelXmlWriter;
12 #endregion
13
14 namespace NinjaTrader.Indicator
15 {
16 [Description("")]
17 [Gui.Design.DisplayName("SimpleExcelWriter")]
18 public class SimpleExcelWriter : Indicator
19 {
20 #region Variables
21 private int date = 0;
22 private double currentOpen = 0;
23 private double currentHigh = 0;
24 private double currentLow = 0;
25 private double currentClose = 0;
26 private bool currentOpenSaved = false;
27 private bool currentLowSaved = false;
28 private Workbook book = new Workbook();
29 private Worksheet sheet;
30 private WorksheetRow row;
31 private string path = Cbi.Core.UserDataDir.ToString() + "test.xls";
32
33 #endregion
34
35 protected override void Initialize()
36 {
37 CalculateOnBarClose = true;
38 }
39
40 protected override void OnBarUpdate()
41 {
42 if(book != null)
43 {
44 if (CurrentBar == 0)
45 {
46 sheet = book.Worksheets.Add("Simple quote");
47 row = sheet.Table.Rows.Add();
48 row.Cells.Add("Time");
49 row.Cells.Add("Open");
50 row.Cells.Add("High");
51 row.Cells.Add("Low");
52 row.Cells.Add("Close");
53 row.Cells.Add("Volume");
54 }
55 row = sheet.Table.Rows.Add();
56 row.Cells.Add(new WorksheetCell(Time[0].ToString()));
57 row.Cells.Add(new WorksheetCell(Open[0].ToString(), DataType.Number));
58 row.Cells.Add(new WorksheetCell(High[0].ToString(), DataType.Number));
59 row.Cells.Add(new WorksheetCell(Low[0].ToString(), DataType.Number));
60 row.Cells.Add(new WorksheetCell(Close[0].ToString(), DataType.Number));
61 row.Cells.Add(new WorksheetCell(Volume[0].ToString(), DataType.Number));
62 book.Save(path);
63 }
64 }
65
66
67
68 #region Properties
69
70 #endregion
71 }
72 }

[whitier]

Thanks roonie, just a question: How can i make it work? I'm a NT new user,and it will be very useful to export chart data to an excel.
Thanks again

[MicroAl]

Writing to excel worksheet

Thanks roonius this is exactly what I have been looking for.

I can understand that having provided this to the forum you don't want spend all your time answering questions on its use.

I presume that the zip file adds the dll and the SimpleExcelWriter file demonstates its use. I am also assuming that the path variable should be changed to where we wish to place the workbook file.

If anyone can provide a few practical hints of how we can actually use this to input to an excel worksheet it would be very much appreciated. I assume that we have to use the DDE functions of excel.

Thanks

[roonius]

Thanks roonius this is exactly what I have been looking for.

I can understand that having provided this to the forum you don't want spend all your time answering questions on its use.

I presume that the zip file adds the dll and the SimpleExcelWriter file demonstates its use. I am also assuming that the path variable should be changed to where we wish to place the workbook file.

If anyone can provide a few practical hints of how we can actually use this to input to an excel worksheet it would be very much appreciated. I assume that we have to use the DDE functions of excel.

Thanks

MicroAl,

Your presumtions are right. As I stated in my previous post, I came across this utility and just coded a quick sample for exporting simple data from the chart.
I am not familiar with all the features of dll. As long as I understood it's using XML for transferring data, so I don't think it's going to work with real time interaction with Excel.

[MicroAl]

Exporting data to excel

Thanks Roonius

To use this utility I

1) Used ninja to input the Carlos.Ag zip file
2) Copied the Simple.ExcelWriter.cs to the Documents\Ninja 6.5\bin\Custom\Indicator section
3)Opened the file in the Ninja editor and compiled
4)Applied it as an indicator on a chart
5)Open the test file in the Documents \ NinjaTrader 5.5 directory

Hope it helps

[roonius]

Thanks Roonius

To use this utility I

1) Used ninja to input the Carlos.Ag zip file
2) Copied the Simple.ExcelWriter.cs to the Documents\Ninja 6.5\bin\Custom\Indicator section
3)Opened the file in the Ninja editor and compiled
4)Applied it as an indicator on a chart
5)Open the test file in the Documents \ NinjaTrader 5.5 directory

Hope it helps

Please be aware that I posted this for testing purposes only - it is not finished script. Since we are dealing with COM objects here, the memory release issues should be seriously taken into consideration. As of this state the script does not clean after itself, so you will be left with excel process hanging in the memory.

[MicroAl]

Exporting data to excel

Thanks for the info Roonius. I guess until the problem is solved it's a case of use at your own risk.

[iwannatoscript]

MicroAl,
I am not familiar with all the features of dll. As long as I understood it's using XML for transferring data, so I don't think it's going to work with real time interaction with Excel.

great JOB!

is it possible to export bid and price? bid and ask vol? and all the book?

I don't know anything about to export in XML, what is the aim to use XML?

Is it possible to use in it in EXCEL or website?

Could you tell us a link how to use XML?

http://www.ninjatrader-support2.com/vb/showthread.php?t=15955

Great guys

[MicroAl]

Can anyone help with this?

Originally posted by Roonius

"Please be aware that I posted this for testing purposes only - it is not finished script. Since we are dealing with COM objects here, the memory release issues should be seriously taken into consideration. As of this state the script does not clean after itself, so you will be left with excel process hanging in the memory."

If someone could take a look at this and fix the problems as stated by Roonius, I, and I suspect others would be very grateful.

Currently downloading data into text files and importing into excel, therefore using this method would save a lot of time.

[tamas]

Hello,

i also use this excel xml writer and i have just realised problem, and i dont know what is the cause. I add this sample value "2,34" and this will be 234,00. So integer will be. In the code you can find this (sample):

[SIZE=2]RowExecutions.Cells.Add([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"2,34"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af]DataType[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Number, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"StyleNumber2DigitWhite"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]);[/SIZE]

and the style:

[SIZE=2][SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af]WorksheetStyle[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] StyleNumber2DigitWhite = styles.Add([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"StyleNumber2DigitWhite"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]);[/SIZE]
[SIZE=2]StyleNumber2DigitWhite.Alignment.Horizontal = [/SIZE][SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af]StyleHorizontalAlignment[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Right;[/SIZE]
[SIZE=2]StyleNumber2DigitWhite.Alignment.Vertical = [/SIZE][SIZE=2][COLOR=#2b91af][SIZE=2][COLOR=#2b91af]StyleVerticalAlignment[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2].Center;[/SIZE]
[SIZE=2]StyleNumber2DigitWhite.NumberFormat = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"#,##0.00_ ;[Red]\\-#,##0.00\\ "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2];[/SIZE][/SIZE]

I checked the help, i created sample excel xml and cheked the CarlosAg generator, but no solution was found. I also cheked the code of roonius, it has the same result. What is not correct in the code or what did i dont see? Any idea would be great. Thanks in advance ...

[tamas]

... What is not correct in the code or what did i dont see? ...

The problem: the Regional and Language Options setting of the operation system causes this. The NT ToString() method will form accordingly the number to text (correctly).

row.Cells.Add(new WorksheetCell(Close[0].[B][COLOR=darkred]ToString()[/COLOR][/B], DataType.Number));

If you see the example of the previous post, then at my end because of the Hungarian setting 2,34 will get into the xml file instead of 2.34. This is a problem.

<s:Cell s:StyleID="StyleNumber2DigitWhite">
     <s:Data s:Type="Number">[B][COLOR=darkred]2,34[/COLOR][/B]</s:Data>
</s:Cell>

One possible solution:

using System.Globalization;

Add to the class:

// Field for CultureInfo class.
private CultureInfo UnitedStates = new CultureInfo("en-US");

The rows of roonius will change to this in the OnBarUpdate() method:

row.Cells.Add(new WorksheetCell(Open[0].ToString("G", UnitedStates), DataType.Number));
row.Cells.Add(new WorksheetCell(High[0].ToString("G", UnitedStates), DataType.Number));
row.Cells.Add(new WorksheetCell(Low[0].ToString("G", UnitedStates), DataType.Number));
row.Cells.Add(new WorksheetCell(Close[0].ToString("G", UnitedStates), DataType.Number));
row.Cells.Add(new WorksheetCell(Volume[0].ToString("G", UnitedStates), DataType.Number));

[Santi]

I realized that while exporting data to an excel file from an indicator, if I refresh de windows explorer, the indicator stops exporting. It was not a problem so I decided not to refresh... but now I have a new computer, and when exporting, the ninja log told me:

"Error on calling the 'OnBarUpdate' method for indicator 'ExtractorPercent' on bar 898:The requested operation cannot be performed on a file with a user-mapped section open." and it stops with a few lines written...

Help please! (and sorry about my english...)

[NinjaTrader_JoshP]

Please ensure you have absolutely no other program exploring, viewing, editing, reading, etc. the file you are writing to.

[Santi]

I uncheck the "resident protection" of AVG antivirus and it works! ;-)

Thanks a lot!

[edward_bell]

I have recently came across a dll for exporting data to Excel
Haven't playd much, but it seems working.
Maybe someone will want to play as well.

1 #region Using declarations
2 using System;
3 using System.Diagnostics;
4 using System.Drawing;
5 using System.Drawing.Drawing2D;
6 using System.ComponentModel;
7 using System.Xml.Serialization;
8 using NinjaTrader.Cbi;
9 using NinjaTrader.Data;
10 using NinjaTrader.Gui.Chart;
11 using CarlosAg.ExcelXmlWriter;
12 #endregion
13
14 namespace NinjaTrader.Indicator
15 {
16 [Description("")]
17 [Gui.Design.DisplayName("SimpleExcelWriter")]
18 public class SimpleExcelWriter : Indicator
19 {
20 #region Variables
21 private int date = 0;
22 private double currentOpen = 0;
23 private double currentHigh = 0;
24 private double currentLow = 0;
25 private double currentClose = 0;
26 private bool currentOpenSaved = false;
27 private bool currentLowSaved = false;
28 private Workbook book = new Workbook();
29 private Worksheet sheet;
30 private WorksheetRow row;
31 private string path = Cbi.Core.UserDataDir.ToString() + "test.xls";
32
33 #endregion
34
35 protected override void Initialize()
36 {
37 CalculateOnBarClose = true;
38 }
39
40 protected override void OnBarUpdate()
41 {
42 if(book != null)
43 {
44 if (CurrentBar == 0)
45 {
46 sheet = book.Worksheets.Add("Simple quote");
47 row = sheet.Table.Rows.Add();
48 row.Cells.Add("Time");
49 row.Cells.Add("Open");
50 row.Cells.Add("High");
51 row.Cells.Add("Low");
52 row.Cells.Add("Close");
53 row.Cells.Add("Volume");
54 }
55 row = sheet.Table.Rows.Add();
56 row.Cells.Add(new WorksheetCell(Time[0].ToString()));
57 row.Cells.Add(new WorksheetCell(Open[0].ToString(), DataType.Number));
58 row.Cells.Add(new WorksheetCell(High[0].ToString(), DataType.Number));
59 row.Cells.Add(new WorksheetCell(Low[0].ToString(), DataType.Number));
60 row.Cells.Add(new WorksheetCell(Close[0].ToString(), DataType.Number));
61 row.Cells.Add(new WorksheetCell(Volume[0].ToString(), DataType.Number));
62 book.Save(path);
63 }
64 }
65
66
67
68 #region Properties
69
70 #endregion
71 }
72 }

That code will overwrite the existing data on the rows. It will not add another row sometimes. Please someone help on adding new row on excel?