AmiBroker Users’ Knowledge Base » Reading/Backing-up the TWS Exported Execution Report

February 18, 2008

Reading/Backing-up the TWS Exported Execution Report

Please be sure you set up the TWS according to the instructions provided in Setting Up your TWS before you test this code. The code presented here reads the execution report, converts it to a .csv format, date-stamps it, backs it up for later use, and optionally displays it in the chart Title. The code doesn’t do anything important besides displaying the information in the Title. The idea is to show you how to read the file so that you can extract real execution prices, use them in your calculations, and plot them on your chart. The Param options are self-explanatory:

The name used for the execution report generated by the TWS is not date-stamped. For example, if you set up the TWS to export executions under the name Simulated.Trades, this same name will be used on successive days. If the TWS finds a tradelist from the previous day, it will simply overwrite it. To prevent losing this AFL readable file it is important to back up the tradelist at the end of the day. The format of the execution report exported by the TWS looks like this:

ATVI;SLD;75;26.19;21:29:33;20080125;ARCA;DU1195;;;DEMO;
ALTR;BOT;100;18.54;21:53:12;20080125;ARCA;DU1195;;;DEMO;
ALTR;BOT;100;18.58;21:55:59;20080125;ISLAND;DU1195;;;DEMO;
ALTR;BOT;100;18.55;21:56:00;20080125;ARCA;DU1195;;;DEMO;
ALTR;BOT;100;18.58;21:58:47;20080125;ISLAND;DU1195;;;DEMO;

The .csv format of the backup file produced by the code below can be directly imported into Excel and looks like this (note the semicolons have been replaced by commas):

ATVI,SLD,75,26.19,21:29:33,20080125,ARCA,DU1195,,,DEMO,
ALTR,BOT,100,18.54,21:53:12,20080125,ARCA,DU1195,,,DEMO,
ALTR,BOT,100,18.58,21:55:59,20080125,ISLAND,DU1195,,,DEMO,
ALTR,BOT,100,18.55,21:56:00,20080125,ARCA,DU1195,,,DEMO,
ALTR,BOT,100,18.58,21:58:47,20080125,ISLAND,DU1195,,,DEMO,

In Excel, the file will look like this after activating Text to columns:

Please be aware that the minimum update interval that the TWS exports the execution report is approximately one-minute. This means it will take some time for your trades to show up in the list.

Before tackling the main backup function, there are a few helper functions you will need. While these are available elsewhere on this site, they are repeated below for your convenience. To prevent conflicts between static variables used in different programs, you should key their names with those charted; see Keying Static Variables for more information on this. The DateNumToStr() converts DateNumbers to a standard date string.

The TWSBackupTradeList( TWSInputPath ) listed below reads the TWS tradelist, extracts the date, converts it to the .csv format, saves it in a different location, and optionally displays both tradelists in the chart Title. To test this function, Apply it to a new Indicator, open the Param window, set up the parameters, and click BACKUP. The backup file is saved in the path defined by the TradebackupFolder variable. If the function finds the execution report and its display is turned on in the Param window, this should look like this in the Title (only a few lines shown):

And, when displayed, the backup file should look like that below:

 
InIndicator 	= Status( "Action" ) == 1;
StaticVarKey 	= GetChartID();
 
procedure xStaticVarSet( SName, SValue )
{
    global StaticVarKey;
 
    if ( InIndicator )
        StaticVarSet( Sname + StaticVarKey, Svalue );
}
 
function xStaticVarGet( SName )
{
    global StaticVarKey;
    return StaticVarGet( Sname + StaticVarKey );
}
 
procedure xStaticVarSetText( SName, SValue )
{
    global StaticVarKey;
 
    if ( InIndicator )
        StaticVarSetText( Sname + StaticVarKey, Svalue );
}
 
function xStaticVarGetText( SName )
{
    global StaticVarKey;
    return StaticVarGetText( Sname + StaticVarKey );
}
 
function DateNumToStr( DtNum )
{
    DayNm = round( frac( DtNum / 100 ) * 100 );
    MthNm = round( frac( DtNum / 10000 ) * 100 );
    YrNm = int( DtNum / 10000 ) + 1900;
    return NumToStr( MthNm, 1.0 ) + "/" + NumToStr( DayNm, 1.0 ) + "/" + NumToStr( YrNm, 1.0, False );
}
 
procedure TWSBackupTradeList( TWSInputPath )
{
    global TradebackupFolder, AccountType;
    fh1 = fopen( TWSInputPath, "r" );
 
    if ( fh1 )
    {
        Line 				= StrReplace( fgets( fh1 ), ";", "," );
        DateStr 			= StrExtract( Line, 5 );
        YearNum			= StrToNum( StrLeft( DateStr, 4 ) );
        MonthNum			= StrToNum( StrMid( DateStr, 4, 2 ) );
        DayNum			= StrToNum( StrRight( DateStr, 2 ) );
        DateNumber 		= ( YearNum - 1900 ) * 10000 + 100 * MonthNum + DayNum;
        DateNumStr 		= NumToStr( DateNumber, 1.0, False );
        BackupFilename = AccountType + DateNumStr + ".csv";
        BackupPath 		= TradebackupFolder + BackupFilename;
        fclose( fh1 );
    }
 
    fh1 = fopen( TWSInputPath, "r" );
 
    fdelete( BackupPath );
    fh2 = fopen( BackupPath, "a" );
    LineNum = 0;
    TWSTradeList = CSVTradelist = "";
 
    if ( fh1 )
    {
        if ( fh2 )
        {
            while ( ! feof( fh1 ) )
            {
                Line = fgets( fh1 );
                TWSTradeList = TWSTradeList + Line;
                Line = StrReplace( Line, ";", "," );
                CSVTradelist = CSVTradelist + Line;
                LineNum++;
 
                if ( Line != "" )
                {
                    fputs( Line, fh2 );
                }
            }
        }
 
        xStaticVarSetText( "TWSTradelist", TWSTradelist );
 
        xStaticVarSetText( "CSVTradelist", CSVTradelist );
    }
    else
    {
        if ( fh1 == 0 )
        {
            PopupWindow( "Could NOT Open InputPath: " + TWSInputPath,
                         "TWS EXPORTED TRADELIST", timeout = 5, left = -1, top = -1 );
        }
 
        if ( fh2 == 0 )
        {
            PopupWindow( "Could not open OutputPath: " + OutputPath,
                         "TWS EXPORTED TRADELIST", timeout = 5, left = -1, top = -1 );
        }
    }
 
    if ( fh1 )
        fclose( fh1 );
 
    if ( fh2 )
        fclose( fh2 );
 
    Caption = "TWS EXPORTED TRADELIST";
 
    Message = "The TWS Tradelist: \n   " + TWSInputPath + " [" + NumToStr( LineNum, 1.0, False ) +
              " Trades/" + DateNumToStr( DateNumber ) + "]" +
              " \nHas been saved in csv format as:\n   " + BackupPath;
 
    PopupWindow( Message, Caption, timeout = 20, left = -1, top = -1 );
}
 
_SECTION_BEGIN( "BACKUP TWS TRADELIST" );
TWSInputPath				= ParamStr( "TWS Tradelist (Folder)", "C:\\Jts\\" );
AccountType					= ParamList( "TWS Account Type (Filename)", "Real|Simulated|Demo", 1 );
TWSInputFilename			= AccountType + ".Trades";
TWSInputPath 				= TWSInputPath + TWSInPutFilename;
TradebackupFolder			= ParamStr( "Backup Destination Folder", "C:\\Jts\\TWSTrades\\" );
BackupTWSTradeList		= ParamTrigger( "Create Backup Tradelist", "BACKUP" );
ShowTWSTradeList			= ParamToggle( "TWS Tradelist", "HIDE|SHOW", 1 );
ShowCSVTradeList			= ParamToggle( "CSV Tradelist", "HIDE|SHOW", 1 );
_SECTION_END();
 
if ( BackupTWSTradeList )
    TWSBackupTradeList( TWSInputPath );
 
TWSStr = WriteIf( ShowTWSTradeList, "\nTWS Exported Tradelist: \n" + xStaticVarGetText( "TWSTradelist" ) + "\n", "" );
 
CSVStr = WriteIf( ShowCSVTradelist, "\nCSV Exported Tradelist: \n" + xStaticVarGetText( "CSVTradelist" ), "" );
 
Title = TWSStr + CSVStr;