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:

clip_image002

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:

clip_image004

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):

clip_image006

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

clip_image008


InIndicator     Status"Action" ) == 1;
StaticVarKey     GetChartID();

procedure xStaticVarSetSNameSValue )
{
    global StaticVarKey;

    if ( InIndicator )
        StaticVarSetSname StaticVarKeySvalue );
}

function xStaticVarGetSName )
{
    global StaticVarKey;
    return StaticVarGetSname StaticVarKey );
}

procedure xStaticVarSetTextSNameSValue )
{
    global StaticVarKey;

    if ( InIndicator )
        StaticVarSetTextSname StaticVarKeySvalue );
}

function xStaticVarGetTextSName )
{
    global StaticVarKey;
    return StaticVarGetTextSname StaticVarKey );
}

function DateNumToStrDtNum )
{
    DayNm roundfracDtNum 100 ) * 100 );
    MthNm roundfracDtNum 10000 ) * 100 );
    YrNm intDtNum 10000 ) + 1900;
    return NumToStrMthNm1.0 ) + "/" NumToStrDayNm1.0 ) + "/" NumToStrYrNm1.0False );
}

procedure TWSBackupTradeListTWSInputPath )
{
    global TradebackupFolderAccountType;
    fh1 fopenTWSInputPath"r" );

    if ( fh1 )
    {
        Line                 StrReplacefgetsfh1 ), ";""," );
        DateStr             StrExtractLine);
        YearNum            StrToNumStrLeftDateStr) );
        MonthNum            StrToNumStrMidDateStr4) );
        DayNum            StrToNumStrRightDateStr) );
        DateNumber         = ( YearNum 1900 ) * 10000 100 MonthNum DayNum;
        DateNumStr         NumToStrDateNumber1.0False );
        BackupFilename AccountType DateNumStr ".csv";
        BackupPath         TradebackupFolder BackupFilename;
        fclosefh1 );
    }

    fh1 fopenTWSInputPath"r" );

    fdeleteBackupPath );
    fh2 fopenBackupPath"a" );
    LineNum 0;
    TWSTradeList CSVTradelist "";

    if ( fh1 )
    {
        if ( fh2 )
        {
            while ( ! feoffh1 ) )
            {
                Line fgetsfh1 );
                TWSTradeList TWSTradeList Line;
                Line StrReplaceLine";""," );
                CSVTradelist CSVTradelist Line;
                LineNum++;

                if ( Line != "" )
                {
                    fputsLinefh2 );
                }
            }
        }

        xStaticVarSetText"TWSTradelist"TWSTradelist );

        xStaticVarSetText"CSVTradelist"CSVTradelist );
    }
    else
    {
        if ( fh1 == )
        {
            PopupWindow"Could NOT Open InputPath: " TWSInputPath,
                         "TWS EXPORTED TRADELIST"timeout 5left = -1top = -);
        }

        if ( fh2 == )
        {
            PopupWindow"Could not open OutputPath: " OutputPath,
                         "TWS EXPORTED TRADELIST"timeout 5left = -1top = -);
        }
    }

    if ( fh1 )
        fclosefh1 );

    if ( fh2 )
        fclosefh2 );

    Caption "TWS EXPORTED TRADELIST";

    Message "The TWS Tradelist: \n   " TWSInputPath " [" NumToStrLineNum1.0False ) +
              " Trades/" DateNumToStrDateNumber ) + "]" +
              " \nHas been saved in csv format as:\n   " BackupPath;

    PopupWindowMessageCaptiontimeout 20left = -1top = -);
}

_SECTION_BEGIN"BACKUP TWS TRADELIST" );
TWSInputPath                ParamStr"TWS Tradelist (Folder)""C:\\Jts\\" );
AccountType                    ParamList"TWS Account Type (Filename)""Real|Simulated|Demo");
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");
ShowCSVTradeList            ParamToggle"CSV Tradelist""HIDE|SHOW");
_SECTION_END();

if ( BackupTWSTradeList )
    TWSBackupTradeListTWSInputPath );

TWSStr WriteIfShowTWSTradeList"\nTWS Exported Tradelist: \n" xStaticVarGetText"TWSTradelist" ) + "\n""" );

CSVStr WriteIfShowCSVTradelist"\nCSV Exported Tradelist: \n" xStaticVarGetText"CSVTradelist" ), "" );

Title TWSStr CSVStr;

Edited by Al Venosa.

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.00 out of 5)
Loading...Loading...

Comments are closed.