export to excel using ninjascript

[ionaz]

Hi Ninjas

I am interested in programatically exporting the backtest results to excel.
Would it be possible to NinjaScript the export to excel of any of the folowing:

• All Trades
• Performace tatistics (eg. combined results of the backtest)

If not, is anybody aware of any workarounds to achieve this in a rather automatic way?

[koganam]

Hi Ninjas

I am interested in programatically exporting the backtest results to excel.
Would it be possible to NinjaScript the export to excel of any of the folowing:

• All Trades
• Performace tatistics (eg. combined results of the backtest)

If not, is anybody aware of any workarounds to achieve this in a rather automatic way?

What is wrong with right-clicking on the grid, and selecting to export to Excel in the popup menu?

[ionaz]

Hi koganam

Thanks, well, I have good reasons for that. I have 1 kid and soon will have another so if I can program anything, I'd better program it... System development is time consuming and time is of immense value for me.

I'm also sure there'd be more people who'd be interested in the analysis of the trades placed by their backtested systems. Analysing trades is highly suggested by T. Stridsman in his book. Since this is one of the best books (along with R. Pardo book ) and since there are not many books on this subject (system development) - I reckon there should be quite a few people interested in this in the NT community.

To be very honest with you I'd really hate if I had to switch to Tradestation now simply because there's no such functionality/workaround for this in NT.

In case of summary stats, you're right, it's just a few clicks.

But when it comes to trades, pls correct me if I'm wrong, there's no such thing as all trades for all stocks in one place. The only way I know how to retrieve this data is once a backtest is complete, on the GUI, I have to select a particular stock from the list and then go to Trades tab for that PARTICULAR stock. Which means each time I test something/run a backtest on SP500 it would translate to ennormous effort (checking 500 stocks manually...).

[NinjaTrader_Bertrand]

ionaz, thanks for sharing your thoughts - an export all trades from all instruments in your basket test in the Strategy Analyzer is on our feedback tracking list in product management for consideration, I've just added a vote here on your behalf in. A current workaround would be coding up a MultiSeries strategy that would cumulate results in our strategy instance, so the basket test would not be needed.

[ionaz]

Hi Betrand

Thanks for your post and adding this to the product management discussion.

Do you know when would it be clear whether NT would develop this functionality or not? And if yes, how long would it take?

Meanwhile I'd like to try doing the workaround. I didn't quite underdstand your proposed solution:

coding up a MultiSeries strategy that would cumulate results in our strategy instance

.
I thought I'd start from definitions, searched for Multiseries st-gy definition in the NT manual but couldn't find much. Same with strategy instance, I can only guess what that is
Could you possibly drop some references or explain further?

Also, please suggest if that could be done relatively easily.
I have 2 years of experience with NT scripting. Developed a few strategies and indicators, thanks to great support from the forum I should admit.

[NinjaTrader_JC]

Hello ionaz,

We do not have a time frame on when/if this functionality will be implemented into NinjaTrader.

I believe what Bertrand was trying to point you towards is that you can add multiple instrument inside of a single Strategy so that the NinjaTrader Performance logs will show all of the instruments that you have added in a single grid so if you export it you would only have to do it once. Here is a link to our Help Guide that has more information and an example code.

http://www.ninjatrader.com/support/helpGuides/nt7/multi_time_frame__instruments.htm

With that said, there is a thread that has an Indicator that will export Historical Data into an Excel file that you may use as a reference if you would like to try to Export the data programmatically in the following link:

http://www.ninjatrader.com/support/forum/showthread.php?p=212402#post212402

Let us know if we can be of further assistance.

[ionaz]

JC,

With regards to the Help Guide link you included, namely if I add multiple instrument inside of a single Strategy, would this actually help me to export strategy's TRADES data in one go for all the instruments added?

[NinjaTrader_PatrickH]

Hello ionaz,

Thank you for your response.

That is correct, using multiple instruments in your strategy you can then view all trades for that strategy (which would include all instruments that traded in the strategy).

Please let me know if I may be of further assistance.

[ionaz]

Thanks Patrick

Sorry for such attention to detail, before I start just want to make it crystal clear whether I will be able to accomplish both goals (in CAPITALS below) by adding multiple instruments to the strategy:

1. export TRADES data of the startegy (all trades made by the strategy)
2. export this data IN ONE GO (ie don't have to go instrument by instrument to acumulate a full list of trades made by the strategy)

Cheers

Jonas

[NinjaTrader_PatrickH]

Hello Jonas,

Thank you for your response.

You can export the data of all instruments traded (if they are added into the strategy through programming in NinjaScript) on the Backtest results by right clicking on the Trades tab results > Grid > Export to Excel.

There will be no need to go instrument by instrument if you have added each instrument into the strategy.

Please let me know if I may be of further assistance.

[suroot]

HI,

You can export the data of all instruments traded (if they are added into the strategy through programming in NinjaScript)

Can this be done also in NT8? If yes, where do I declare the instruments to be traded?

A short code snippet of this would make my day, if it is not asking for too much, for pointing me in the right direction.

Thanks,
Chris

[NinjaTrader_PatrickH]

Hello suroot,

Thank you for your post.

To add the instruments in NinjaTrader 8 you would use the AddDataSeries() function: https://ninjatrader.com/support/help...dataseries.htm

Trading to those instruments would require a working knowledge of multiple instruments or timeframes in your code. Please refer to our help guide article on this item at the following link: https://ninjatrader.com/support/help...nstruments.htm

Please let me know if you have any questions.

[npvrader]

StreamWriter output

I use streamwriter to create a csv file, there is no short snippet - if you google it I think you will find a good example within ninjascript. You have to set up the streamwriter object, create the output file, create the output code, and then dispose of the stream writer object.....

FYI - I have created all my variables for output as this is an indicator not a strategy.

protected override void OnStartUp()/////////////////////////////////////////////////////////////////////
{
ModelName();
//+ read file that has ..... to go to root directory.
//+ Commented out, not being used currently as we are using the built in path to the NT7 location.
//StreamReader sr = File.OpenText(path2);
//string dpath1 = sr.ReadLine();
//sr.Close();
//+ To use file based path, uncomment the 3 lines above...
//+ If you want to use the hardcoded path change dPath2 to dpath1.............
DirectoryInfo tempFolder = new DirectoryInfo( pathMain ); //tempFolder is the root,comes from txt file in NT7 folder

DirectoryInfo subFolder = tempFolder.CreateSubdirectory( newDir ); //Creates subFolder if needed.
instSymb = Instrument.MasterInstrument.Name;
//++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++ +++
//+ Backtest Data Path, File and Streamwriter setup....
//++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++ +++


string dataPath = pathMain +@"\" + newDir;
string dest = dataPath+filename;

string defname = dataPath + @"\" +Instrument.FullName+"_"+BarsPeriod.Value+BarsPeri od.Id+modlName+".csv";
string dataHdr1 = "Date,Roll,Model,Instrument,Sym,Open,Hi,Lo,Close,V ol,JBCSS";
string ltModHdr = "TrendDir,TrendPx,TrendDte" ;
string modHdr = "mPos,modPx[0],entDate,kSCO,sarPx,kHOR,kHOS,kBool,kSpark,kSCOflg ,kToday,kValue";
string resHdr1 = "entDate,exDate,entPx,exitPx,tradePts,tradVal,oteD elt,instVal";
string resHdr2 = "winCnt,winVal,lossCnt,loseVal,hiVal,cHiVal,cLoVal ";
string upLdheader = dataHdr1+dSep+ltModHdr+dSep+modHdr+dSep+resHdr1+dS ep+resHdr2;

//defname = defname.Replace('-','_'); // Replaces non-ISO file characters in Futures names
// If file at 'path' doesn't exist it will create the file. If it does exist it will append the file.
if (useDefaultFilename == false)
{
sw = File.AppendText(dest);
}
else
{
sw = File.CreateText(defname);
sw.WriteLine(upLdheader);
}

This is how I output it....the if statements are irrelevant to you... dSep is a comma

private void StringIt()
{
if(dateCompInt < 1)
{
if (trendDir[0] == 1)
{

majorHand = kHOS;
}
if (trendDir[0] == -1)
{
majorHand = kHOR;
}


writeIt = (Time[0].Date.ToShortDateString()+dSep
+roll+dSep+modlName2+dSep+Instrument.FullName+dSep +instSymb+dSep
+Open[0]+dSep+High[0]+dSep+Low[0]+dSep+Close[0]+dSep+Volume[0]+dSep+jbcssValue+dSep //dataHdr1
+trendDir[0]+dSep+trendPx+dSep+trendDate.ToShortDateString()+d Sep //ltModHdr
+mPos+dSep+modPx[0]+dSep+entDate.ToShortDateString()+dSep+kSCO+dSep+s arPx+dSep+kHOR+dSep+kHOS+dSep+kBool+dSep+kSpark+dS ep+kSCOflg+dSep+kToday+dSep+kValue+dSep//modHdr
+entryDate+dSep+exitDate+dSep+entPx+dSep+exitPx+dS ep+rTradePts+dSep+rTradeVal+dSep+oteDelta+dSep+ins tVal+dSep //resHdr1
+winCnt+dSep+winVal+dSep+lossCnt+dSep+loseVal+dSep +hiVal+dSep+cHiVal+dSep+cLoVal); //resHdr2
}

I call StringIt after processing.. in onbarupdate...
.
StringIt();
if(writeComp < 0 || skipDateChk == true)
{
if((string.IsNullOrEmpty(writeIt) == false && writeRec > 0) || skipDateChk == true) sw.WriteLine(writeIt);

sw.Flush();

}
public override void Dispose()
{
// Disposes resources used by the StreamWriter
if(sw != null)
{
sw.Dispose();
sw = null;
}