Profit Table To Excel

AmiBroker
1

Hello,

Is there any way to get the values in profit table to Excel Sheet? From the knowledge base article I found that it is not really a table but a bitmap image with fixed dimensions.

https://www.amibroker.com/kb/2014/09/16/too-small-unreadable-profit-table-in-the-backtest-report/.

Is there any way to get the results in exploration window?

_SECTION_BEGIN("3. Profit Table");
eq = C;

yr = Year();
mo = Month();

YearChange = yr != Ref( yr,1 );
MonChange = mo != Ref( mo, 1 );

FirstYr = 0;
LastYr = 0;

startbar = 0;

////////////////////////////
// SKIP non-trading bars
////////////////////////////
for( i = 0; i < BarCount; i++ )
{
  if( eq[ i ] )
  {
    startbar = i;
    break;
  } 
}

////////////////////////////
// collect yearly / monthly changes in equity
// into dynamic variables
////////////////////////////

LastYrValue = eq[ startbar  ];
LastMoValue = eq[ startbar  ];

MaxYrProfit = MinYrProfit = 0;
MaxMoProfit = MinMoProfit = 0;

for( i = startbar + 1; i < BarCount; i++ )
{
  if( YearChange[ i ] || i == BarCount - 1 )
  {
    Chg = 100 * ( -1 + eq[ i ] / LastYrValue );
    VarSet("ChgYear"+ yr[ i - 1 ], Chg );

    MaxYrProfit = Max( MaxYrProfit, Chg );
    MinYrProfit = Min( MinYrProfit, Chg );

    if( FirstYr == 0 ) FirstYr = yr[ i - 1 ];
    LastYr = yr[ i ];

    LastYrValue = eq[ i ];
  }

  if( MonChange [ i ] || i == BarCount - 1 )
  {
    mon = mo[ i - 1 ];

    Chg = 100 * ( -1 + eq[ i ] / LastMoValue );

    VarSet("ChgMon" + yr[ i - 1 ] + "-" + mon, Chg );
    VarSet("SumChgMon"+ mon, Chg + Nz( VarGet("SumChgMon"+ mon ) ) );
    VarSet("SumMon" + mon, 1 + Nz( VarGet("SumMon"+ mon ) ) );
 
    MaxMoProfit = Max( MaxMoProfit, Chg );
    MinMoProfit = Min( MinMoProfit, Chg );

    LastMoValue = eq[ i ];
  }
}

/////////////////////////////////////////////////
// Drawing code & helper functions
////////////////////////////////////////////////

GfxSetOverlayMode( 2 );

CellHeight = (Status("pxheight")-1)/(LastYr - FirstYr + 3 ); 
CellWidth = (Status("pxwidth")-1)/14; 
GfxSelectFont( "Tahoma", 8.5 ); 

GfxSetBkMode( 1 );

function PrintInCell( string, row, Col ) 
{
 Color =  ColorRGB( IIf( row == 0 || col == 0 || col == 13, 220, 255 ), 255, IIf( row % 2, 255, 220 ) );
 GfxSelectSolidBrush( Color   );
 GfxRectangle( Col * CellWidth, 
                    row * CellHeight, (Col + 1 ) * CellWidth + 1, 
                    (row + 1 ) * CellHeight  + 1); 
 GfxDrawText( string, Col * CellWidth + 1, 
                    row * CellHeight + 1, 
                    (Col + 1 ) * CellWidth, (row + 1 ) * CellHeight, 32+5 ); 
} 

YOffset = 25;
XOffset = 15;

function DrawBar( text, bar, numbars, y, Miny, Maxy )
{
 BarWidth = (Status("pxwidth") - 4 * XOffset )/( numbars + 1 ); 
 BarHeight = Status("pxheight") - 2 * YOffset;
 relpos = ( y - Miny ) / (Maxy - Miny );

 xp = XOffset + ( bar + 0.5 ) * BarWidth;
 yp = YOffset + BarHeight * ( 1 - relpos );
 xe = XOffset + ( bar + 1 ) * BarWidth;
 ye = YOffset + BarHeight * ( 1 - ( -miny )/( maxy - miny ) );
  
 if( y > 0 )
 {
 GfxGradientRect( xp, yp, 
                  xe , ye,
                  ColorHSB( 70, 255 * relpos, 255 ), ColorHSB( 70, 20, 255 ) ); 
 }
 else
 {
 GfxGradientRect( xp, ye, 
                  xe , yp,
                  ColorHSB( 0, 20, 255 ), ColorHSB( 0, 255 * ( 1 - relpos ), 255 ) ); 
 }
 GfxTextOut( text, xp, ye );
 GfxTextOut( StrFormat("%.2f", y ), xp, yp );
}    

function DrawLevels( Miny, Maxy )
{
  range = Maxy - Miny;

  grid = 100;
  if( range < 10 ) grid = 1;
  else 
  if( range < 20 ) grid = 2;
  else 
  if( range < 50 ) grid = 5;
  else 
  if( range < 100 ) grid = 10;
  else 
  if( range < 200 ) grid = 20;
  else 
  if( range < 500 ) grid = 50;

  _TRACE("grid = "+grid +" range "+range );
  
  width = Status("pxwidth") - 4 * XOffset;
  height = Status("pxheight") - 2 * YOffset;

  GfxSelectPen( colorBlack, 1, 2 );
  for( y = grid * ceil( Miny / grid ); y <= grid * floor( Maxy / grid ); y += grid )
  {
    yp =  YOffset + Height * ( 1 -  ( y - Miny ) / (Maxy - Miny ) );

    GfxMoveTo( XOffset, yp );
    GfxLineTo( XOffset + width , yp );
    GfxTextOut( ""+ y, XOffset + 2 + width, yp );
  }

  GfxSelectPen( colorBlack, 1, 0 );
  GfxMoveTo( XOffset, YOffset );
  GfxLineTo( XOffset + width, YOffset );
  GfxLineTo( XOffset + width, YOffset + Height );
  GfxLineTo( XOffset , YOffset + Height );
  GfxLineTo( XOffset , YOffset );
}

MonthNames = "Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec";

function DisplayProfitTable( )
{
 Header = "Year,"+MonthNames+",Yr%";
 for( Col = 0; (Colname = StrExtract( Header, Col ) ) != ""; Col++ )
 {
  PrintInCell( ColName, 0, Col );
 }

 Row = 1;
 for( y = FirstYr; y <= LastYr; y++ )
 {
  PrintInCell( StrFormat("%g", y ), Row, 0 ); 
  PrintInCell( StrFormat("%1.2f%%", VarGet("ChgYear" + y ) ), Row, 13 ); 
  for( m = 1; m <= 12; m++ )
  { 
   Chg = VarGet("ChgMon" + y + "-" + m);
   if( Chg ) 
     PrintInCell( StrFormat("%1.2f%%", Chg ), Row, m );
   else
     PrintInCell( "N/A", Row, m );
  }
  Row++;
 } 

 PrintInCell("Avg", Row, 0 );
 for( m = 1; m <= 12; m++ )
 { 
   PrintInCell( StrFormat("%1.2f%%",  Nz( VarGet("SumChgMon" + m)/VarGet("SumMon" + m ) ) ), Row, m );
 }

}

function DisplayYearlyProfits()
{
 Bar = 0;
 for( y = FirstYr; y <= LastYr; y++ )
 {
   Chg = VarGet("ChgYear" + y );
   DrawBar( ""+y, Bar++, ( LastYr - FirstYr + 1 ), Chg, MinYrProfit, MaxYrProfit );
 }
 GfxTextOut("Yearly % Profit chart", 10, 10 );

 DrawLevels( MinYrProfit, MaxYrProfit ); 
}

function DisplayMonthlyProfits()
{
 Bar = 0;
 
 MinAvgProf = MaxAvgProf = 0;
 for( y = 1; y <= 12; y++ )
 {
   Chg = VarGet("SumChgMon" + y ) / VarGet("SumMon" + y );
   MinAvgProf = Min( MinAvgProf, Chg );
   MaxAvgProf = Max( MaxAvgProf, Chg );
 }

 for( y = 1; y <= 12; y++ )
 {
   Chg = VarGet("SumChgMon" + y ) / VarGet("SumMon" + y );
   DrawBar( StrExtract(MonthNames, y-1 ), Bar++, 13, Chg, MinAvgProf , MaxAvgProf );
 }
 GfxTextOut("Avg. Monthly % Profit chart", 10, 10 );

 DrawLevels( MinAvgProf , MaxAvgProf ); 
}

///////////////////////////
// This function checks if currently selected symbol
// is portfolio equity
//////////////////////////
function CheckSymbol()
{
 if( Name() != "~~~EQUITY" )
 {
  GfxSelectFont( "Tahoma", 20 ); 
  GfxSetBkMode( 2 );
  //GfxTextOut("For accurate results switch to ~~~EQUITY symbol", 10, 10 );
 }
}

////////////////////////////
// Main program - chart type switch
////////////////////////////
type = ParamList("Chart Type", "Profit Table|Yearly Profits|Avg. Monthly Profits", 0 );

switch( type )
{
 case "Profit Table": 
         DisplayProfitTable();  
         break;
 case "Yearly Profits": 
         DisplayYearlyProfits();
         break;
 case "Avg. Monthly Profits": 
         DisplayMonthlyProfits();
         break;
}

CheckSymbol();
_SECTION_END();

Thank you

1 Like
2

You can use below HTML version by Amibroker

/// CODE BY WWW.AMIBROKER.COM
EnableTextOutput( 3 ); // enable HTML output into report (Version 5.84 or higher!)

eq = C;
yr = Year();
mo = Month();

// change between new and old -> 0 or 1
// old profit table format: trade at next open    : set bardelay = 1;  
// new profit table format: trade on current close: set bardelay = 0;
bardelay = 0;
if( bardelay > 0 )
	 refdelay = -1;	
else refdelay =  1; 

YearChange = yr != Ref( yr, refdelay );
MonChange = mo != Ref( mo, refdelay );

FirstYr = 0;
LastYr = 0; 
startbar = 0;

////////////////////////////
// SKIP non-trading bars
////////////////////////////

for ( i = 0; i < BarCount; i++ )
{
    if ( eq[ i ] )
    {
        startbar = i;
        break;
    }
}

////////////////////////////
// collect yearly / monthly changes in equity
// into dynamic variables
////////////////////////////

LastYrValue = eq[ startbar  ];
LastMoValue = eq[ startbar  ];

MaxYrProfit = MinYrProfit = 0;
MaxMoProfit = MinMoProfit = 0;

for ( i = startbar + 1; i < BarCount; i++ )
{
    if ( YearChange[ i ] || i == BarCount - 1 )
    {
        Chg = 100 * ( eq[ i ] / LastYrValue - 1 );
        VarSet( "ChgYear" + yr[ i - bardelay ], Chg );

        MaxYrProfit = Max( MaxYrProfit, Chg );
        MinYrProfit = Min( MinYrProfit, Chg );

        if ( FirstYr == 0 )
            FirstYr = yr[ i - bardelay ];

        LastYr = yr[ i ];  
        LastYrValue = eq[ i ];
    }

    if ( MonChange [ i ] || i == BarCount - 1 )
    {
        mon = mo[ i - bardelay ]; 
        Chg = 100 * ( eq[ i ] / LastMoValue - 1 );

        VarSet( "ChgMon" + yr[ i - bardelay ] + "-" + mon, Chg );
        VarSet( "SumChgMon" + mon, Chg + Nz( VarGet( "SumChgMon" + mon ) ) );
        VarSet( "SumMon" + mon, 1 + Nz( VarGet( "SumMon" + mon ) ) );

        MaxMoProfit = Max( MaxMoProfit, Chg );
        MinMoProfit = Min( MinMoProfit, Chg );

        LastMoValue = eq[ i ];
    }
}

MonthNames = "Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec";


function GenProfitTableHTML( )
{
    printf( "<table border='1' bordercolor='#000000' cellspacing='0' cellpadding='3'style='border-collapse:collapse;'>\n" );
    printf( "<tr bgcolor='#eeffee' >\n" );

    Header = "Year," + MonthNames + ",Yr%%";

    for ( Col = 0; ( Colname = StrExtract( Header, Col ) ) != ""; Col++ )
    {
        printf( "<td><b>" + Colname + "</b></td>" );
    }

    printf( "</tr>\n" );

    for ( y = FirstYr; y <= LastYr; y++ )
    {
        //Color =  ColorRGB( IIf( row == 0 || col == 0 || col == 13, 220, 255 ), 255, IIf( row % 2, 255, 220 ) );

        // new row
        if ( y % 2 )
            printf( "<tr bgcolor='#ffffff'>\n<td bgcolor='#eeffff'>" );
        else
            printf( "<tr bgcolor='#ffffee'>\n<td bgcolor='#eeffee'>" );

        printf( "<b>%g</b></td>", y );

        for ( m = 1; m <= 12; m++ )
        {
            Chg = VarGet( "ChgMon" + y + "-" + m );

            if ( NOT IsNull( Chg ) )
            {
                if ( Chg >= 0 )
                    printf( "<td nowrap>%.2f%%</td>", Chg );
                else
                    printf( "<td nowrap><font color='880000'>%.2f%%</font></td>", Chg );
            }
            else printf( "<td>N/A</td>" );
        }

        if ( y % 2 )
            printf( "<td nowrap bgcolor='#eeffff'>" );
        else
            printf( "<td nowrap bgcolor='#eeffee'>" );

        x = VarGet( "ChgYear" + y );

        if ( x >= 0 )
            printf( "<b>%.2f%%</b></td>", x );
        else
            printf( "<font color='880000'><b>%.2f%%</b></font></td>", x );

        printf( "</tr>\n" ); // end row
    }


    printf( "<tr bgcolor='#eeffee' >\n" ); // new row  
    printf( "<td><b>Avg</b></td>" );

    for ( m = 1; m <= 12; m++ )
    {
        x = Nz( VarGet( "SumChgMon" + m ) / VarGet( "SumMon" + m ) );

        if ( x >= 0 )
            printf( "<td nowrap><b>%.2f%%</b></td>", x );
        else
            printf( "<td nowrap><font color='880000'><b>%.2f%%</b></font></td>", x );
    }

    printf( "<td>&nbsp;</td>" ); 
    printf( "</tr></table>\n" ); 
}

///////////////////////////
// This function checks if currently selected symbol
// is portfolio equity
//////////////////////////
function CheckSymbol()
{
    if ( Name() != "~~~EQUITY" AND Name() != "~~~OSEQUITY" )
    {
        printf( "For accurate results switch to ~~~EQUITY symbol<br>" );
    }
}

CheckSymbol();

////////////////////////////
// Main program
////////////////////////////
GenProfitTableHTML();

Then you can copy&paste table directly from Report to Excel via CTRL+C and CTRL+V.
(Or import charts.html from Reports folder of HDD to Excel)

6

3 Likes
3

It is all explained in the Knowledge Base article:

http://www.amibroker.com/kb/2014/12/10/copy-backtest-result-to-excel/

1 Like
4

Sorry to bother again. I am not able to understand how to get the Html report for the above profit table formula.

If it is a trading system then I copy the backtested html reports to excel. The link posted by Tomasz is for trading system.

Could you please explain how to use the HTML version of the profit table?

Thank you

5

  1. You have to save the posted AFL to Report charts folder of Charts window.
    In that folder there are three default ones already.
    But you can place any AFL making sense to be part of final report there.
    (Note: you can give your AFL any name you like)
    11

  2. Then do a backtest and open backtest report in analysis and go to Charts tab there in backtest report. There in that category of BT report there will be the HTML profit table you are looking for then.

If you want to have table of price data (you seem to as you have commented check for ~Equity symbol in first post's code) then you can do like this:

/// buy&hold backtest code to get stats for price data 
/// (e.g. profit table of report charts) as asked here
/// https://forum.amibroker.com/t/profit-table-to-excel/18723
first_price = LastValue(ValueWhen(Status("firstbarinrange"), C));
SetOption("InitialEquity", first_price);
SetOption("CommissionMode", 2);
SetOption("CommissionAmount", 0);
SetPositionSize(1, spsShares);
Buy = 1;
BuyPrice = Close;
Sell = Short = Cover = 0;

This will output e.g. profit table based on price movement (in backtest report).

2 Likes
6

Thank you so much for the clear explanation.

7

Can it be made Weekly?. Thanks