Stand alone tool for Visualization of optimization result in CSV/HTML file

Version 6.2 can help us run a lot of portfolio optimizations and save results in CSV or HTML files. While the optimization result still in AB, I can visualize it in 3D. Problem is AB can not visualize IMPORTED result stored in HTML. Is there a stand alone tool that let me visualize the optimization result in CSV or HTML files? I hope that tool also has the water level feature as in AB's visualization tool. Thanks for sharing.

There is absolutely zero need for 3rd party tool at all.

After optimization is finished all you have to do in order to archive opt. results to CSV is going to File - Export HTML/CSV and choose file type CSV and save it.

Then you create a little AFL program that stores three columns (comma-separated and with header) out of archived opt. file separately and runs O3G.exe (located in AmiBroker main directory) via ShellExecute function opening that AB generated second (3-columns) file.

Important note: all has to be created/run via AmiBroker program to not violate usage terms as it is prohibited to run AB 3D grapher stand alone to display non-AmiBroker generated files.

That's OK. As long as you run it in together with AmiBroker you are fine.

Best regards,
Tomasz Janeczko
amibroker.com

https://groups.yahoo.com/neo/groups/amibroker/conversations/messages/138374

05


Alternative option is importing archived CSV file (or directly copying result list) to Excel and creating pivot table and then creating 3D graph out of selected three metrics. Excel names 3D chart type -> Surface.

4 Likes

Or you can simply save/rename optimize.csv file (that is automatically created by AmiBroker when you choose to display 3D graph after optimization). Such file can be open later using O3G.exe (in AmiBroker directory).

2 Likes

@LearnBuySell this thread might be also interesting for you:

Not to gush (emotionally) but sometimes the scope of the capabilities of AB blows my mind...

FYI, I have made AFL program to view archived optimization files created via AmiBroker.

Default support for files with minimum 3 columns and maximum 39 columns (default number of columns: 37 AB inbuilt default metrics plus two Optimize() columns).

Usage:

  1. Save Optimization result list to CSV file via File - Export HTML/CSV (after optimization)
  2. Set path and filename in lines 1 and 2 of below AFL (if CSV file is located in AmiBroker main directory then set: path = ""; )
  3. Set metric via Gui Slider of chart
  4. Click GUI button of chart to start AmiBroker's 3D viewer

Program requires AmiBroker 6.30.

path = "C:\\_ABExport\\";
filename = "Optimize.csv";
/// ##################################################################################################################
/// AmiBroker 3D Optimization Graph Tool
/// by fxshrat@gmail.com, available at:
/// https://forum.amibroker.com/t/stand-alone-tool-for-visualization-of-optimization-result-in-csv-html-file/12296/6
/// This program is supposed to be used on ARCHIVED optimization files only.
/// Default support for files with minimum 3 columns and 
/// maximum 39 columns (default number of columns: 37 AB inbuilt metrics plus two Optimize() calls).
/// Usage:
/// 1. Save Optimization result list to CSV file via File - Export HTML/CSV (after optimization)
/// 2. Set path and filename in lines 1 and 2 of this AFL (if CSV file is in AmiBroker main directory then set: path = "";)
/// 3. Choose metric via Gui Slider of chart
/// 4. Click GUI button of chart to start AmiBroker's 3D viewer
/// ##################################################################################################################
Version( 6.30 );

if ( path != "" && path != StaticVarGetText("Export_path") ) {	
	fmkdir(path);
	StaticVarSetText("Export_path", path);
}

file = path + filename;
fstat = fgetstatus( file, 1, format = 5 );
printf( "File's mod. DT: %s", DateTimeToStr(fstat) );

n = 0; 	
line = ""; 
fh1 = fopen( file, "r" );  
if ( fh1 ) {
	line = fgets( fh1 );
	fclose( fh1 );
} else
	Error( StrFormat("ERROR: %s can not be open", file) );

cols = StrCount(StrTrim(line,",\n"), "," );
maxcol = Max(2,cols);  

if ( cols < 2 )
	Error( "Source file must have minimum 3 columns!" );	
if ( cols > 38 )
	Error( "More than two Optimize() calls seem to have created source file!\nChoose different one." );
	
x0 = Param( "3D Tool X-Position", 5, 0, 500, 1 );
y0 = Param( "3D Tool Y-Position", 5, 0, 500, 1 );
xButton = x0+210;
font = "Arial";

/// GUI start #############################################################################
slider = GuiSlider( id = 1, x0+10, y1 = y0+40, width = 355, height = 30, notifyEditChange );
printf( "slider: %g", slider );
    
// init values 
resetGUI = ParamTrigger( "Reset GUI", "CLICK HERE" );
if ( slider == guiNew OR resetGUI OR fstat != Nz(StaticVarGet("3D_fstatus")) ) {    
    GuiSetValue( id, Min(11, maxcol-2) );
    GuiSetRange( id, 0, maxcol-2, 1, 1 );
    StaticVarSet("3D_fstatus", fstat);
}

button = GuiButton( "Start AmiBroker 3D Viewer", 2, xButton, y1+height+60, 155, 25, 1 );

if ( button == guiNew OR resetGUI )
	GuiSetFont( font, 9 ); 

getID = GuiGetEvent( 0, 0 );
event = GuiGetEvent( 0, 1 );

metric_col = GuiGetValue(id);
metric_col = Max(0, Min(metric_col, maxcol-2));	
printf( "\ncolumns: %g, max. col: %g, metric col.: %g", cols+1, maxcol+1, metric_col+1 );	
/// GUI end ################################################################################

/// Gfx start ##############################################################################
GfxFillSolidRect( 0, 0, pxw = Status("pxwidth"), pxh = Status("pxheight"), colorBlack);

GfxSetBkMode( 1 );
GfxSetTextAlign( 0 | 0 );

GfxSetTextColor( colorRed );
GfxSelectFont( font, 14, 700, 0, 1, 0 );
GfxTextOut( gfxTitle = "AmiBroker 3D Optimization Grapher Tool", x0+10, y0+5 );
tWidth = GfxGetTextWidth(gfxTitle);

GfxSelectFont( font, 9, 500, 0, 0, 0 );
GfxSetTextColor( colorRed );
GfxTextOut( "Metrics:", x0+10, y2 = y1+height+10 );
for( i = 0; i <= Max(0, maxcol-2); i++ ) {
	if ( i == metric_col )
		GfxSetTextColor( colorBrightGreen );
	else 
		GfxSetTextColor( colorWhite );
	GfxTextOut( StrFormat( "%02.0f. %s", i+1, StrExtract(line, i)), x0+10, (i+1)*13+y1+height+10 );
}

GfxSelectStockObject(5);
GfxSelectPen( colorRed, 1 );
GfxRectangle( x0+1, y0+1, x0+tWidth+20, Max(y1+height+95, (i+1)*13+y1+height+20) );

GfxSetTextColor( colorRed );
GfxTextOut( "Optimize() Parameters:", xButton, y2 );
GfxSetTextColor( colorWhite );
GfxTextOut( StrFormat( "01. %s", StrExtract(line, maxcol-1)), xButton, y2+13 );
GfxTextOut( StrFormat( "02. %s", StrExtract(line, maxcol)), xButton, y2+26 );
/// GUI end ################################################################################

/// 3D output from file ####################################################################
if ( getID == 2 && event == 1 ) {	
	n = 0; 	 	
	fh1 = fopen( file, "r" );  
	if ( fh1 ) {
		while ( ! feof( fh1 ) ) {
			line = StrTrim(fgets( fh1 ), ",\n"); // read a line of text
			//_TRACE( line ); 		   
			
			col1 = StrExtract( line, metric_col);
			col2 = StrExtract( line, Max(0, maxcol-1) );
			col3 = StrExtract( line, maxcol );
			
			n++;
			
			VarSetText( "O3G_file" + n, StrFormat( "%s,%s,%s\n", col1, col2, col3 ) );
		}
		fclose( fh1 );
	} else
		Error( StrFormat("ERROR: %s can not be open", file) );
	//
	fh2 = fopen(file2 = path + "optimize_custom.csv", "w" );
	if ( fh2 ) { 
		for ( i = 0; i < n; i++ ) 
			fputs( VarGetText( "O3G_file" + i ), fh2 );
		fclose( fh2 );
	} else 
	   Error(StrFormat("ERROR: %s can not be open", file2)); 
	//   
	ShellExecute( "O3G.exe", file2, "" );
}

212

15 Likes

I have made a a few updates of upper program.

(1.) The program now lists all available CSV files of specified path of code line #1 of that AFL program (see Interpretation window output). So you just have to copy one of the listed names of Interpretation window via CTRL+C (after marking it) and insert it within quotation marks of code line #2 of AFL program.

09

Default path code line is

_N( path = fgetcwd() + "\\_Optimization\\" );

fgetcwd() function (of AB 6.10) returns full path to AmiBroker working directory. So better leave code line #1 of full code below the way it is. It gives less headaches. Just bother for line #2 -> insert correct existing file name there.


(2.) Also now the full specified path is programmatically created (with all sub-directories
e.g. if it is C:\\MyDirectory\\MySubDirectory1\\MySubDirectory2 ).


(3.) Next I have updated error messages (e.g if path/file do not exist). Now they are more detailed about what to do. So simply follow error message advice instead of getting "panic attacks" a la "AFL does not work". It is very simple. :slight_smile:

See example error:
05
And sample path/filename
08

As written above either in lines #1 and #2 of AFL edit path and/or filename or if (older) file(s) exist(s) elsewhere then move them to specified path returned in Interpretation window if you rather want to have those file(s) there in new folder.

But as mentioned in (1.) better leave line #1 the way it is and only take care of file name of code line #2 (as well as getting existing files into specified folder).


(4.) And I've made some other updates.


Now here is updated full code (Remember: You need minimum AmiBroker 6.30!). It's been tested and working. (If you still face issues on how to use it then leave message. But basically it should be more clear now.)

_N( path = fgetcwd() + "\\_Optimization\\" );// specify path to your archived optimization CSV files.
_N( filename = "optimize1.csv" );// within quotations set file name being located there
/// ##################################################################################################################
/// AmiBroker 3D Optimization Graph Tool
/// by fxshrat@gmail.com, available at:
/// @link https://forum.amibroker.com/t/stand-alone-tool-for-visualization-of-optimization-result-in-csv-html-file/12296/7
/// This program is supposed to be used on ARCHIVED optimization files only.
/// Default support for files with minimum 3 columns and maximum 39 columns 
/// (default number of columns: 37 AB inbuilt metrics plus two Optimize() calls).
/// Usage:
///		1. Save Optimization result list to CSV file via File - Export HTML/CSV (after optimization)
///		2. Set path (it must end with double backslash) and filename in lines 1 to 2 of this AFL. 
///			(If file is in AmiBroker main directory then you may set: path = "";)
///		3. Choose metric via GUI Slider of chart pane
///		4. Click GUI button of chart pane to start AmiBroker's 3D viewer
/// NOTE: if specified path/file do not exist then ERROR messages are returned. 
///	So please don't panic a la "AFL does not work". Simply READ and FOLLOW error message content.
/// Commercial use prohibited!
/// ##################################################################################################################
Version( 6.30 );
EnableTextOutput(0);
SetChartOptions(1, chartDisableTooltips | chartDisableYAxisCursor | chartHideQuoteMarker );

procedure fCreatePath( path ) {		
	/// creating full path if it does not exist e.g. 
	/// "C:\\MyDirectory\\MySubDirectory1\\MySubDirectory2"	
	/// @link http://tinyurl.com/y5lxo6af
	/// by fxshrat@gmail.com
	local i, path, dir, count_dir, sum_dir;
	path = StrTrim(path, "\\", 3);
	if ( path != "" ) {		
		sum_dir = "";
		count_dir = StrCount(path, "\\");	
		for ( i = 0; i <= count_dir; i++ ) {
			dir = StrExtract(path, i, '\\');
			sum_dir += dir + "\\";			
			fmkdir(sum_dir);
		}	
	}
}

/// File initial start #############################################################################
fCreatePath( path );// comment if you do not want to create path

file = path + filename;
fstat = fgetstatus( file, 1, format = 5 );
printf( "Specified path: <b>%s</b>\n", path );
printf( "Specified file name: <b>%s</b>\n", filename );
printf( "File's mod. DT: %s\n", DateTimeToStr(fstat) );
//
// List CSV files in specified folder start
printf( "\n<b>CSV files in %s:</b>\n", path);
_N( list = fdir( path+"*.csv", 1 ) ); 
for ( i = 0; ( filenames = StrExtract( list, i ) ) != ""; i++ ) 
    if ( filenames != "optimize_custom.csv" )
		printf( filenames + "\n" ); 
printf( "\n" );
// List CSV files in specified folder end
//
line = "";
fError = "ERROR: %s can not be open.\n" + 
		"Check whether path and file exist!\n" +
		"After finished optimization save optimization result list to CSV file via 'File - Export HTML/CSV'.\n" +
		"Save that CSV file to the specified path being printed in 'Interpretation' window.";
//
cols = 0;
default_max = 38; 
if ( Status( "actionex" ) != actionExEditVerifyFormula ) {
	fh1 = fopen( file, "r" );  
	if ( fh1 ) {
		line = fgets( fh1 );
		fclose( fh1 );
	} else
		Error( StrFormat(fError, file) );
	//
	cols = StrCount(StrTrim(line,",\n"), "," );
	if ( cols < 2 )
		Error( "Source file must have minimum 3 columns! Choose different one." );	
	if ( cols > default_max ) {
		colError =	"More than two Optimize() calls seem to have created source file!\n" +
					"Choose different file or you may increase 'default_max' variable's value.";
		Error( colError );
	}
}
maxcol = Max(2,cols); 
/// File initial end ##############################################################################
	
x0 = Param( "3D Tool X-Position", 5, 0, 500, 1 );
y0 = Param( "3D Tool Y-Position", 5, 0, 500, 1 );
resetGUI = ParamTrigger( "Reset GUI", "CLICK HERE" );

font = "Arial";
fnt_size2 = 9;
fnt_size1 = 14;
bk_color = colorBlack;//GetChartBkColor();//ColorRGB(240, 240, 240);//
fnt_color = colorLightGrey;
misc_color = colorRed;
mark_color = colorBrightGreen;

dist1 = fnt_size1 * 1.5;
dist2 = fnt_size2 * 1.5;

GfxSelectFont( font, fnt_size1, 700, 0, 1, 0 );
gfxTitle = "AmiBroker 3D Optimization Grapher Tool";
tWidth = GfxGetTextWidth(gfxTitle);
//printf( "%g\n", twidth);

GfxSelectFont( font, fnt_size2, 500, 0, 0, 0 );
tWidth2 = GfxGetTextWidth("08. Max. Trade % Drawdown");
//printf( "%g\n", twidth2);

tWidth3 = GfxGetTextWidth("Start AmiBroker 3D Viewer");
//printf( "%g\n", twidth3);

/// GUI start #############################################################################
x1 = xSlider = x0+10;
y1 = ySlider = y0+fnt_size1*2.5;
sWidth = tWidth-1;
sHeight = Max(30,dist1);
slider = GuiSlider( sID = 1, x1, y1, sWidth, sHeight, notifyEditChange );
//printf( "Slider: %g\n", slider );

// init slider
if ( slider == guiNew OR resetGUI OR fstat != Nz(StaticVarGet("3D_fstatus")) ) {      
    StaticVarSet("3D_fstatus", fstat);   
    GuiSetValue( sID, Min(11, maxcol-2) );  
    GuiSetRange( sID, 0, maxcol-2, 1, 1 );
    _TRACE("Reset Slider"); 
    _TRACEF( "%g", GuiGetValue(sID) );    
} 

xButton = x0+tWidth2+53;
yButton = y1+sHeight+4*dist2;
bWidth = tWidth3+14;
bHeight = 1.9*dist2;
button = GuiButton( "Start AmiBroker 3D Viewer", 2, xButton, yButton, bWidth, bHeight, 1 );
if ( button == guiNew OR resetGUI OR fnt_size2 != Nz(StaticVarGet("3D_fntsize")) ) {
	GuiSetFont( font, fnt_size2 ); 
	StaticVarSet("3D_fntsize", fnt_size2); 
	_TRACE("Reset Button"); 
}
//printf( "Button: %g\n", button );
	
getID = GuiGetEvent( 0, 0 );
event = GuiGetEvent( 0, 1 );

metric_col = GuiGetValue(sID);
metric_col = Max(0, Min(metric_col, maxcol-2));	
//printf( "columns: %g, max. col: %g, metric col.: %g\n", cols+1, maxcol+1, metric_col+1 );	
/// GUI end ################################################################################

/// Gfx start ##############################################################################
GfxFillSolidRect( 0, 0, pxw = Status("pxwidth"), pxh = Status("pxheight"), bk_color);

GfxSetBkMode( 1 );
GfxSetTextAlign( 0 | 0 );

GfxSetTextColor( misc_color );
GfxSelectFont( font, fnt_size1, 700, 0, 1, 0 );
GfxTextOut( gfxTitle, x0+10, y0+5 );

GfxSelectFont( font, fnt_size2, 500, 0, 0, 0 );
GfxTextOut( "Metrics:", x0+10, y2 = y1+sHeight+10 );

for ( i = 0; i <= Max(0, maxcol-2); i++ ) {
	y3 = (i+1)*dist2+y1+sHeight+10;
	if ( i == metric_col ) {	
		GfxSetTextColor( mark_color );
		GfxSelectFont( "Segoe UI", fnt_size2, 500, 0, 0, 0 );
		GfxTextOut( "\u25c0", x0+tWidth2+20, y3 );
		GfxSelectFont( font, fnt_size2, 500, 0, 0, 0 );
	} else 
		GfxSetTextColor( fnt_color );
	GfxTextOut( StrFormat( "%02.0f. %s", i+1, StrExtract(line, i)), x0+10, y3 );
}

GfxSetTextColor( misc_color );
GfxTextOut( "Optimize() Variables:", xButton, y2 );
GfxSetTextColor( fnt_color );
GfxTextOut( StrFormat( "01. %s", StrExtract(line, maxcol-1)), xButton, y2+dist2 );
GfxTextOut( StrFormat( "02. %s", StrExtract(line, maxcol)), xButton, y2+2*dist2 );

GfxSelectStockObject(5);
GfxSelectPen( misc_color, 1 );
x2_border = Max(xButton+bWidth, tWidth+15)+10;
y2_border = Max(y1+sHeight+95, (i+1)*dist2+y1+sHeight+20);
GfxRectangle( x0+1, y0+1, x2_border, y2_border );
/// Gfx end ################################################################################

/// 3D output from file ####################################################################
if ( getID == 2 && event == 1 ) {	
	n = 0; 	 	
	fh1 = fopen( file, "r" );  
	if ( fh1 ) {
		while ( ! feof( fh1 ) ) {
			line = StrTrim(fgets( fh1 ), ",\n"); // read a line of text
			//_TRACE( line ); 		   
			
			col1 = StrExtract( line, metric_col);
			col2 = StrExtract( line, Max(0, maxcol-1) );
			col3 = StrExtract( line, maxcol );
			
			n++;
			
			VarSetText( "O3G_file" + n, StrFormat( "%s,%s,%s\n", col1, col2, col3 ) );
		}
		fclose( fh1 );
	} else
		Error( StrFormat(fError, file) );
	//
	fh2 = fopen(file2 = path + "optimize_custom.csv", "w" );
	if ( fh2 ) { 
		for ( i = 0; i < n; i++ ) 
			fputs( VarGetText( "O3G_file" + i ), fh2 );
		fclose( fh2 );
	} else 
	   Error(StrFormat(fError, file2)); 
	//   
	shell = ShellExecute( "O3G.exe", "\"" + file2 + "\"", "" );
	_TRACEF( "shell: %g", shell);
	if ( shell <= 32 )
		PopupWindow( "Unable to start 3D viewer", "ERROR", 5 );
}
// 
22 Likes

Mr. fxshrat, thanks for the afl script.
It is an util which a lot of people love to have.

Hi fxshrat,

Is there any way to use Ami 3D Viewer without an optimization file?. i.e just arrays writed from the AFL if you need to display a 3D graphic plot for studying their relationship?
Best,

AmiBroker's 3D Graph viewer reads from CSV file. So you would have to store your data to such file via AmiBroker also. So yes, it is possible to read other data created by AFL program (not involving optimization).
You need minimum three columns with header.

23

22

1 Like

Hi Fxshrat,
Thanks for your explanation. Very clear. I will do that.
It would be great to consider, for next Ami releases, the possibility of using also the 3D viewer directly from the AFL arrays.

Best,

I bumped into this thread and not sure if others found another solution also but I did.

If it's allready know, like many things I am finding, then atleast someone bumping into this same thread, having the same "problem" will hopefully find it usefull :slight_smile:

I had the problem that it appeared like I was unable to reload saved reports and then use the 3d viewer.

Turns out, it IS possible.... using HTML export. If you export as HTML you can then import the old results again, you will get all the colums and you can select the results just like you have just run an optimize.

There is however a small "bug" (no idea how to call, can you call it a bug if it was not designed to work in the first place ?)

You have to run an optimize first (you can imediatly stop it, it does not have to finish) before Import HTML will work. If you run a backtest, or start up AB and import the HTML, you will get the message "3D graph available only after completed optimization".

So for me to have it working I do:

Click optimize button
Click stop button
Import HTML
open the 3D optimization chart

1 Like

As I wrote in my first reply a quicker method is to just use optimize.csv file saved by AmiBroker automatically.

Import/Export tools allow to load/save results of EVERYTHING that is done in Analysis, including optimization results.

The message "3d graph available only after completed optimization" is not a bug. It is deliberately designed as PROTECTION against users contacting support with nonsense "bug" claims because they click on all buttons without thinking and expect "something" to happen. If button did not respond with error message, I would receive tons of "bug reports" that button does not work. So it clearly says that you have to run optimization before you can see 3D optimization chart. HTML format does not have idea of "being result of optimization" so if you import it, the program does not know if it is the result of optimization.

1 Like