Excel Optimization Analyser

[darckeen]

Here is a quick and dirty excel workbook that i threw together the other day to analyse the results of multi-instrument optimizations. I've found it imensely useful for analysing large optimization runs so i though i'd share it. Note: it was thrown together very quickly and i havent programmed in VBA in a long time so the code is pretty ugly and it has no error checking and theres plenty of room for improvement, feel free to pitch in and make it better

How it works:

Basically it will import your optimization csv into a worksheet as well as parse the run settings into columns. You can then create 3D surface charts of any parameter columns versus result columns. You can also add parameter filters and if you want you can add your own columns and create charts off of those. The chart will be an average matrix of the parameter matches. So if i chart fastperiod vs slow period for Profit Factor and you have 10 instruments it will report the average profit factor for all instruments for each parameter set.

How to use it:
1)set the file path and name
2)click import file button
3)specify chart parameters
4)click create chart

To specify chart parameters you need to name 'X Axis' 'Y Axis' and 'Z Axis'. The name needs to exactly match the column headers from the 'data' sheet. X and Y must be strategy parameters because the macro doesn't categorize result parameters. Also the X and Y must have atleast two categories or excel wont make a 3D chart. Finally you can add filter to chart parameters. Filters will only pick values that match.

The orange area is for adhoc charts and corresponds to the create chart button. The yellow area is for batch runs, once there are two blank rows the batch will stop.

[NinjaTrader_JoshP]

Thanks for sharing darckeen!

[Anagoge]

This is a good idea. I like the chart batch feature and it is nice that it automates separating out the parameters. It would be great to see some similar Excel automation support built into NT someday. Some ideas:

• Wrap the chart creation code in Application.ScreenUpdating = False/True to speed it up a bit and cut down on most of the screen flickering as the macro runs
• I set the chart colors to default to the black background with gradient blue tones for the surface, but this may only work in Excel 2007:
  

  ActiveChart.ChartStyle = 43

• You can auto-scale the min/max for the Z axis like this:
  

      ActiveChart.Axes(xlValue).MaximumScaleIsAuto = False
      ActiveChart.Axes(xlValue).MinimumScaleIsAuto = False
      ActiveChart.Axes(xlValue).MinimumScale = Application.Min(Range(outsheet.Cells(rowoff + 1, coloff + 1), outsheet.Cells(rowoff + UBound(xcat) + 1, coloff + UBound(ycat) + 1)))
      ActiveChart.Axes(xlValue).MaximumScale = Application.Max(Range(outsheet.Cells(rowoff + 1, coloff + 1), outsheet.Cells(rowoff + UBound(xcat) + 1, coloff + UBound(ycat) + 1)))

• I added a form control button to the form to select the file to open and attached it to this macro, to cut down on typos:
  

  Public Function FileNameFromPath(strFullPath As String) As String
      FileNameFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "\"))
  End Function
  
  Public Function FolderFromPath(strFullPath As String) As String
      FolderFromPath = Left(strFullPath, InStrRev(strFullPath, "\"))
  End Function
  
  Sub SelectFileButtonClick()
      Dim fn As Variant
      fn = Application.GetOpenFilename("CSV Files (*.csv),*.csv", _
          1, "Select a .csv File To Open", , False)
      If TypeName(fn) = "Boolean" Then Exit Sub
      Dim FileName As String
      FileName = fn
      Cells(1, 2) = FolderFromPath(FileName)
      Cells(2, 2) = FileNameFromPath(FileName)
  End Sub

• I made the import a little more forgiving in how you can format the file location/name data:
  

      Dim csvfile As String
      With Sheets("GUI")
          If Right(.Cells(1, 2), 1) = "\" Then
              csvfile = .Cells(1, 2) & .Cells(2, 2)
          Else
              csvfile = .Cells(1, 2) & "\" & .Cells(2, 2)
          End If
          If Not Right(csvfile, 4) = ".csv" Then csvfile = csvfile & ".csv"
      End With

• I initialized rowoff like this so the data and chart don't overlap for me: rowoff = 38
  
• I added a check for invalid empty values before moving the average values onto the sheet with the chart. This happens when your parameter range has 500 data points, but you tell NT to only keep the best 100, for example. Here is the change:
  

      For i = 0 To UBound(xcat)
          For j = 0 To UBound(ycat)
              If Not IsEmpty(count(i, j)) Then
                  outsheet.Cells(i + rowoff + 1, j + coloff + 1) = values(i, j) / count(i, j)
              End If
          Next
      Next

• It might be nice to have a dropdown for GUI sheet column B listing the column names from the data sheet to prevent typos. I didn't try to add this.
• It might be useful to non-programmers who don't want to dig into the code to show a friendly error when the file does not exist, when there is a typo in a column name (GUI column B) and when you click import data twice without first manually deleting that tab. I didn't try to add this stuff either.

[NinjaTrader_Ben]

Hello,

Thank you for your suggestions! I will send them to our development department for consideration.

[PrTester]

This is a good idea. I like the chart batch feature and it is nice that it automates separating out the parameters. It would be great to see some similar Excel automation support built into NT someday. Some ideas:

Do you think you can upload your version?

[darckeen]

Thanks for the input Anagoge, those are all great additions and very nice code. I had originally wanted to do a drop down with the column headers like you are suggesting but i'm using excel 2002 and the ide for forms sucks so i just gave up. I used to have vb 6 which was great for vba, mabey i'll try to dig that up. Been working on a related excel workbook for portfolio equity curve, between the two projects i think my code is slowly improving towards its former glory lol. I'll probably do an overhaul on this one once i hit a creative low with the strategy i'm working on since i'm finding this tool to be invaluable for identifying parameter relationships and what not.

[Anagoge]

Do you think you can upload your version?

I'd rather let the darckeen post a version with any of my changes he wants to keep, so we don't have multiple versions from multiple authors floating around. If he doesn't want to do that, I'll post mine.

[darckeen]

Go ahead and post your version Anagoge. The only thing I'd like to ask is that the workbook be in 1997-2003 format.

[Anagoge]

Here is the spreadsheet. I don't have older versions of Excel here to test, but I have a feeling the chart style (coloring) code/values will differ in older releases. Other parts of the macros may also need changes.

[Arpad]

Decimal and thousands delimiter in Excel Optimization Analyser

To NinjaTrader customer support:
NT7, latest.
Which setting determines the decimal and field delimiters in the .csv file that is created when I select Save as... in the Strategy Analyzer window?
On my PC, NinjaTrader uses space as thousands separator and comma as decimal mark.
The .csv file exported by NinjaTrader contains comma as decimal mark and semicolon as delimiter between fields.

Where can I change these?


To Anagoge or darckeen:
I assume that the .xls VB script (posted in #9 by Anagoge) is awaiting coma as field delimiter and point as decimal separator.
Am I right?

What do you think? Where and how should I change the settings in order the VB script to function? The .csv file should be in the originally awaited format, or is there a way to change something in Excel to correspond to the different format, before the file import implemented by the macro?


Many thanks,
Arpad

[NinjaTrader_JoshP]

Arpad,

Those export things usually come from your local PC's locale. You can change this in your Windows Control Panel.