Hi All, is there a way to store my backtest results to a text file or a sql database. Is there a way to store backtest data through the AFL code programmatically.
For example these data:
Max. trade drawdown
Max. trade % drawdown
Max. system drawdown
Max. system % drawdown
Recovery Factor
CAR/MaxDD
RAR/MaxDD
Profit Factor
Payoff Ratio
Standard Error
Risk-Reward Ratio
Ulcer Index
Ulcer Performance Index
Sharpe Ratio of trades
K-Ratio
did you try the batch functionality with export to file?
@dhermanus perhaps this article will help. You can export as a csv file.
Or right click on the report and see how to export to Excel
@dhermanus, to do it programmatically you can take a look at the Stats object as documented in the Porfolio Backtester Interface Reference.
From there, you can write the calculated metrics to your own text file.
If you prefer to work with an SQL database, you should study the AFL section of the ODBC plugin's documentation (search the forum for "ODBC" to learn more about its potential use and caveats).
In particular, I think that you can achieve your goal using the odbcExecuteSQL( "sql statement" ) function.
See code here
http://www.amibroker.com/kb/2015/01/06/how-to-display-interest-gains-in-the-backtest-report/
or here
/// @link https://www.amibroker.com/guide/a_custombacktest.html
/// @link http://www.amibroker.com/kb/2015/01/06/how-to-display-interest-gains-in-the-backtest-report/
/// @link https://forum.amibroker.com/t/is-it-possible-to-get-result-from-backtest-and-optimization/12570/2
/// @link https://forum.amibroker.com/t/saving-the-backtest-result/26201/5
/// by fxshrat@gmail.com
EnableTextOutput(0);
metrics_list = "MaxTradeDrawdown,"+
"MaxTradeDrawdownPercent,"+
"MaxSystemDrawdown,"+
"MaxSystemDrawdownPercent,"+
"RecoveryFactor,"+
"CAR/MDD,"+
"RAR/MDD,"+
"ProfitFactor,"+
"PayoffRatio,"+
"StandardError,"+
"RRR," + //Risk-Reward Ratio
"UlcerIndex,"+
"UlcerPerformanceIndex,"+
"SharpeRatio,"+
"KRatio";
rows = StrCount(metrics_list, ",")+1;
SetCustomBacktestProc( "" );
if ( Status( "action" ) == actionPortfolio ) {
bo = GetBacktesterObject();
bo.Backtest(); // run default backtest procedure
mat = Matrix(rows, 1);
stats = bo.GetPerformanceStats( 0 );
for ( i = 0; i < rows; i++ ) {
mat[i][0] = stats.GetValue(StrExtract(metrics_list, i));
}
StaticVarSet( "CBT_StatsMat", mat);
}
// Dummy system
SetPositionSize(1, spsShares);
m = MA( Close, 20 );
Buy = Cross( Close, m );
Sell = Cross( m, Close );
Short = Cover = 0;
// Dummy system end
// Call metrics elsewhere AFTER backtest
// Exploration output or anything else...
mat = StaticVarGet( "CBT_StatsMat" );
SetOption("NoDefaultColumns",1);
Filter = 0;
AddtextColumn("", "Metric", 1);
AddColumn(Null, "Value", 1);
if ( typeof( mat ) == "matrix" ) {
if ( Status("stocknum") == 0 ) {
for ( i = 0; i < rows; i++ ) {
//printf("%s: %g\n", StrExtract(metrics_list, i), mat[i][0]);
AddRow(StrFormat("%s\t%g", StrExtract(metrics_list, i), mat[i][0]));
}
}
}
Then you can export Exploration list to CSV via File - Export HTML/CSV or via copy & pasting result list and inserting in Excel or via Batch export as posted in 2nd post of this thread.
Thanks for the reply guys and to point me to the directions to some of the solutions. I think it is an important capability to store backtest results in a sql database so that we can also query the results and to generate reports for our strategies. I shall put this challenge into my short term bucket list and I shall post my solution to this post. Thanks.
As for storing to external DB via ODBC plugin:
In connection variable you would have to insert DSN, driver, password, DB name, port,....
Columns ID, MetricName,MetricValue have to exist. But you may change to other names of your table in odbcExecuteSQL line.
/// @link https://www.amibroker.com/guide/a_custombacktest.html
/// @link http://www.amibroker.com/kb/2015/01/06/how-to-display-interest-gains-in-the-backtest-report/
/// @link https://forum.amibroker.com/t/is-it-possible-to-get-result-from-backtest-and-optimization/12570/2
/// @link https://forum.amibroker.com/t/saving-the-backtest-result/26201/7
/// by fxshrat@gmail.com
EnableTextOutput(0);
metrics_list = "MaxTradeDrawdown,"+
"MaxTradeDrawdownPercent,"+
"MaxSystemDrawdown,"+
"MaxSystemDrawdownPercent,"+
"RecoveryFactor,"+
"CAR/MDD,"+
"RAR/MDD,"+
"ProfitFactor,"+
"PayoffRatio,"+
"StandardError,"+
"RRR," + //Risk-Reward Ratio
"UlcerIndex,"+
"UlcerPerformanceIndex,"+
"SharpeRatio,"+
"KRatio";
rows = StrCount(metrics_list, ",")+1;
SetCustomBacktestProc( "" );
if ( Status( "action" ) == actionPortfolio ) {
bo = GetBacktesterObject();
bo.Backtest(); // run default backtest procedure
mat = Matrix(rows, 1);
stats = bo.GetPerformanceStats(0);
for ( i = 0; i < rows; i++ ) {
mat[i][0] = stats.GetValue(StrExtract(metrics_list, i));
}
StaticVarSet( "CBT_StatsMat", mat);
}
// Dummy system
SetPositionSize(1, spsShares);
m = MA( Close, 20 );
Buy = Cross( Close, m );
Sell = Cross( m, Close );
Short = Cover = 0;
// Dummy system end
// Call metrics elsewhere AFTER backtest
// Exploration output or anything else...
mat = StaticVarGet( "CBT_StatsMat" );
SetOption("NoDefaultColumns",1);
Filter = 0;
AddColumn(Null, "ID", 1);
AddtextColumn("", "Metric", 1);
AddColumn(Null, "Value", 1);
explore = Status("action") == actionExplore;
odbcTrigger = explore OR ParamTrigger("Store to external DB", "CLICK HERE");
connection = "ODBC;"+
"DSN=YOUR_DSN;"+
"DRIVER={YOUR_DRIVER};"+ // e.g {MariaDB ODBC 3.1 Driver}
"TCPIP=1;"+
"SERVER=127.0.0.1;"+
"UID=YOUR_UID;"+
"PWD=YOUR_PASSWORD;"+
"DATABASE=YOUR_DB;"+
"PORT=YOUR_PORT";
table_name = "YOUR_TABLE";// table name of external DB
odbcOpenDatabase(connection);
if ( typeof( mat ) == "matrix" ) {
if ( Status("stocknum") == 0 ) {
if ( odbcTrigger)
odbcExecuteSQL("DELETE FROM "+table_name+";");
//
for ( i = 0; i < rows; i++ ) {
//printf("%s: %g\n", StrExtract(metrics_list, i), mat[i][0]);
AddRow(StrFormat("%g\t%s\t%g", i+1, StrExtract(metrics_list, i), mat[i][0]));
//
if ( odbcTrigger ) {
odbcExecuteSQL(StrFormat("Insert INTO "+table_name+" (ID, MetricName, MetricValue) VALUES ( %1.0f, '%s', %g )",
i+1, StrExtract(metrics_list, i), mat[i][0]) );
}
}
}
}
Left part of picture shows MariaDB table after program execution in AmiBroker.
One note: try to avoid calling odbcOpenDatabase when you are NOT running any queries.
odbcOpenDatabase(connection); // this should be moved inside "if"
Hi All,
I've decided to go with the COM object route to save backtest results since it'll give me more flexibility in the future to automate my strategy.
And thanks to @fxshrat responce. I've manage to do that faster now.
This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.