Sort text or csv column by Date

mfu5324 · June 4, 2018, 6:12pm

So I am exporting my trades to a text (csv) file... Amibroker lists trades by date with the exit date on the same line. I want to list trades by Date.. showing BUYS and SELLS on that date...the code below, I successfully created the txt (csv) file to export BUYS by date and SELLS by date... however I need to now sort the text file by DATE,.... so it shows buys and sells be date... is there a way to fopen a txt file and sort by column(0) and save the text file?

Thanks
Brian

fh = fopen( "C:\\TESTOUTPUT\\TEST77.csv", "w"); 
//fputs("Symbol,Name,Entry Date,Profit,Bars,ExitDate",fh);
//fputs("\n",fh);

     for ( trade = bo.GetFirstTrade(); trade; trade = bo.GetNextTrade() )
     {
        EntryIndex = LastValue( ValueWhen( trade.EntryDateTime == Dates,Indices ) );
	ExitIndex = LastValue( ValueWhen( trade.ExitDateTime == Dates,Indices ) );

	y=Year(); m=Month(); d=Day();
	exENTERDATE = StrFormat("%02.0f-%02.0f-%02.0f,", y[ entryindex ], m[ entryindex ], d[ entryindex ] ); 
	exExitDATE = StrFormat("%02.0f-%02.0f-%02.0f,", y[ exitindex ], m[ exitindex ], d[ exitindex ] );
			
	exSYMBOL = TRADE.Symbol;
	exNAME = TRADE.FullName();
	exPROFIT = Prec(trade.GetPercentProfit,1);
	exBARS = trade.BarsInTrade;
	exEntryPrice = trade.EntryPrice;
		
	// this code will export trades in the same format as the backtest window (I DO NOT WANT THIS)
	//fputs(exEnterDate  + exExitDate  + exsymbol + "," + exname + "," + exEntryPrice + "," + exBars + "," + enDate2 + "," + exDate2,FH);
		//fputs("\n",FH);
		
		
	// *******this will export trades - BUY trades and SELL Trades on thier own line
	fputs(exEnterDate + "BUY," + exsymbol + "," + exname + "," + exEnterDate + "--,--",FH);
	fputs("\n",FH);
	fputs(exExitDate + "SELL," + exsymbol + "," + exname + "," + exEnterDate  + exBars + "," + exProfit ,FH);
	fputs("\n",FH);
     }

fclose(FH);

mfu5324 · June 4, 2018, 6:20pm

Or better yet... is there a way to sort by date prior to writing to text file? the reason for this is if I want to write the trades to a html file the I can write the sorted trades to it as well...

thanks again

mradtke · June 4, 2018, 6:55pm

A similar question was just addressed:

mfu5324 · June 4, 2018, 7:23pm

Yes I read that post prior to posting.... I couldn't get that to work out to what I am trying to accomplish tho

PanoS · June 4, 2018, 7:46pm

Hi
If you want to run in exploration mode and the output to be just one trade per row you can do it like this line

AddColumn(IIf( Sell, lastpriceBuy, 
			IIf( Cover, lastpriceShort, Null )) ,"Entry@",1.4,colorBlue,CellColor,60);

to sort any column in your code you can use SetSortColumns( 3 ); // sort by 3th column in ascending order
https://www.amibroker.com/guide/afl/setsortcolumns.html
and later on you can export your files from explorer

// this sample code is for exploration mode and the output is one trade per row

myBuyPrice = BuyPrice = Open;  // insert your BuyPrice here
mySellPrice = SellPrice=Open;  // insert your SellPrice here

lastpriceBuy=ValueWhen(Buy,myBuyPrice );		
lastpriceSELL=ValueWhen(Sell,mySellprice);

Filter = Sell OR Cover;  //OR Buy OR Short ; 

textColor  = colorBlack;
CellColor=IIf(Sell, bkgndColor =colorAqua, IIf(Cover, bkgndColor =51, 23)); //35 , 47


SetOption("NoDefaultColumns", True); 
AddTextColumn(Name(), "Symbol", 77, textColor  ,CellColor, 70); 
AddColumn(IIf(Sell, 76, IIf(Cover, 83, 32)), "Trade", formatChar, textColor  , CellColor,20);

// ENTER TRADE
EnterTime=IIf(Sell , ValueWhen(Buy ,DateTime()),IIf( Cover,ValueWhen(Short,DateTime()), Null));
AddColumn(EnterTime, "Entry Date/Time", formatDateTime, textColor  , CellColor, 105);  
AddColumn(IIf( Sell, lastpriceBuy, 
			IIf( Cover, lastpriceShort, Null )) ,"Entry@",1.4,colorBlue,CellColor,60);

// EXIT TRADE
ExitTime=IIf(Sell , ValueWhen(Sell ,DateTime()),IIf( Cover,ValueWhen(Cover,DateTime()), Null));
AddColumn(ExitTime, "Exit Date/Time", formatDateTime, textColor, CellColor, 105); 
AddColumn(IIf( Sell, lastpricesell, 
			IIf( Cover, lastpricecover, Null )) ,"Exit@",1.4,colorBlue,CellColor,60);
			
SetSortColumns( 3 ); // sort by 3th column in ascending order 

// SetSortColumns( -3 )  // sort by 3rd column in descending order

mfu5324 · June 4, 2018, 7:58pm

thanks PanoS.... unfortunately I need to run this during the backtest to get all backtest stats etc and have it export the file during my batch processing of each backtest......

mradtke · June 4, 2018, 8:11pm

Care to elaborate on that?

mfu5324 · June 4, 2018, 8:21pm

in my code, I am looking at each trade (firsttrade, nextrade) and recording the BuyDate and SellDate as separate lines..... below is what the txt file looks like... the first column is what I define as Date... notice the first trade of IBM BUY.3/15/91.. the next line is the IBM Sell on 09/20/91... but what I need is all the buys on 03/15/91 listed first... then the IBM sell should appear in the trades listed for 09/20/91..

I think my problem is I am using trade.getfirsttrade, nexttrade.... using this, I don't get the isentry or isexit. I would have to use signals... but if i use signals... then I wont get all the stats that I need using the getfirsttrade, nextrade objects....

I am thinking I need to set the Date (column 1) as a variable before fput to text....sort by date and then fput it to text file.... I was thinking to use both getfirstsignal with getfirsttrade but that slowed the whole backtest down and created a very large txt file...........

1991-03-15,BUY,IBM,International Business Machines Corp Common,1991-03-15,--,--
1991-09-20,SELL,IBM,International Business Machines Corp Common,1991-03-15,131,-15.7
1991-03-15,BUY,CVX,Chevron Corp Common,1991-03-15,--,--
1992-03-20,SELL,CVX,Chevron Corp Common,1991-03-15,257,-12.9
1991-03-15,BUY,MRK,Merck & Co Inc Common,1991-03-15,--,--
1992-03-20,SELL,MRK,Merck & Co Inc Common,1991-03-15,257,38.7
1991-03-15,BUY,PG,Procter & Gamble Co Common,1991-03-15,--,--

mradtke · June 4, 2018, 8:56pm

You could, of course, sort everything after the fact using Excel. And you could write everything to your text file ordered by date if you would process signals rather than closed trades. So what sort of trade stats do you need that you can't get from the signal object?

mfu5324 · June 4, 2018, 9:03pm

that is what I do now.. however I am converting everything to batches.. and want to be able to auot update my web page with historical trades without having to do any manual sorting.... that said... I don't understand this very well.. but I was thinking if there is a way to do something like this

IF trade.entrydatetime == (date of trade) THEN write the BUY
ELSE
If trade.exitdatetime == (date of trade) THEN write the SELL

But I do not know how to get the variable (Date of Trade) when I loop through firsttrade, nexttrade...?

mfu5324 · June 4, 2018, 9:17pm

well... that wouldn't work anyway.... I need to combine getfirstsignal with getfirsttrade I think... grrrr

mradtke · June 4, 2018, 11:49pm

A couple of thoughts:

It would be easier to picture what you're trying to accomplish if you could give an example of your web site output.
You could certainly write a utility to open the file in Excel, sort it, and save it again. This utility could easily be called from an AB batch file.

mfu5324 · June 5, 2018, 5:58am

Ok... if you follow my original code... the csv file looks like this..

orig1

What want it to look like is below.. where Col A is sorted by date to list the buys and sells of each evaluation date

desired

mfu5324 · June 5, 2018, 6:02am

here is the code again

if ( Status( "action" ) == actionPortfolio )
{
     bo = GetBacktesterObject();
     bo.Backtest( 1 ); // Run backtest without listing trades yet
     st=bo.GetPerformanceStats(0);
     Indices = BarIndex();
     Dates = DateTime();
	fh = fopen( "C:\\TESTOUTPUT\\TEST77.csv", "w"); ;
   
     for ( trade = bo.GetFirstTrade(); trade; trade = bo.GetNextTrade() )
     {
		
        EntryIndex = LastValue( ValueWhen( trade.EntryDateTime == Dates,Indices ) );
		ExitIndex = LastValue( ValueWhen( trade.ExitDateTime == Dates,Indices ) );

		y=Year(); m=Month(); d=Day();
		exENTERDATE = StrFormat("%02.0f-%02.0f-%02.0f,", y[ entryindex ], m[ entryindex ], d[ entryindex ] ); 
		exExitDATE = StrFormat("%02.0f-%02.0f-%02.0f,", y[ exitindex ], m[ exitindex ], d[ exitindex ] );
		
		exSYMBOL = TRADE.Symbol;
		exNAME = TRADE.FullName();
		exPROFIT = Prec(trade.GetPercentProfit,1);
		exBARS = trade.BarsInTrade;
		exEntryPrice = trade.EntryPrice;
		
		// BELOW WILL EXPORT the trade on one line showing buy and sell on one line (like the backtest report)
		//fputs(exEnterDate  + exExitDate  + exsymbol + "," + exname + "," + exEntryPrice + "," + exBars + "," + enDate2 + "," + exDate2,FH);
		//fputs("\n",FH);	

		// this will export entry and exits on seperate line
		fputs(exEnterDate + "BUY," + exsymbol + "," + EXNAME + "," + exEnterDate + "--,--",FH);
		fputs("\n",FH);
		fputs(exExitDate + "SELL," + exsymbol + "," + EXNAME + "," + exEnterDate  + exBars + "," + exProfit ,FH);
		fputs("\n",FH);
     }

fclose(FH);

mradtke · June 5, 2018, 12:23pm

If all you need is the number of bars in the trade and the profit, then why not just find the trade object with bo.FindOpenPos() when you see the exit signal? You may need to calculate the the profit manually depending on what your exit price is and whether or not you're using commissions. You could also find the trades after they're closed (so profit would be correct), but that would be more time-consuming from an execution standpoint

beppe · June 5, 2018, 1:52pm

@mfu5324, since you want to sort on dates (that are numbers) one way to do it, is using a matrix and some dynamic vars, like in the following example:
(ignore the fake trading system I added at the top: it is there just to allow any other users to test the formula to see how it works)

Version( 6.28 ); // Tested version

/// Added a fake Trading System (built using AB snippets!) to quicly test the code
maxpos = 10; // maximum number of open positions
SetOption( "InitialEquity", 100000 ); // set initial equity = 100K
SetOption( "MaxOpenPositions", maxpos );
SetPositionSize( 100 / maxpos, spsPercentOfEquity );

period = 10; // 10 bars
ChannelBreakUp = Close > Ref( HHV( High, period ), -1 );
Buy = ChannelBreakUp;
Sell = 0;

period = 22; // ATR period
multiplier = 3; // ATR multiplier
ApplyStop( stopTypeTrailing, stopModePoint, multiplier*ATR( period ), True, True );

PositionScore = ROC( C, 50 ); // Favor stocks in medium term positive trend
/// END of fake system.....


/// Code modified (assuming that it was correct) from: 
/// https://forum.amibroker.com/t/sort-text-or-csv-column-by-date/6275/14

SetCustomBacktestProc( "" );
counter = 0;

if( Status( "action" ) == actionPortfolio )
{
    bo = GetBacktesterObject();
    bo.Backtest( 1 ); // Run backtest without listing trades yet
    st = bo.GetPerformanceStats( 0 );
    Indices = BarIndex();
    Dates = DateTime();

    for( trade = bo.GetFirstTrade(); trade; trade = bo.GetNextTrade() )
    {
        counter++;
        EntryIndex = LastValue( ValueWhen( trade.EntryDateTime == Dates, Indices ) );
        ExitIndex = LastValue( ValueWhen( trade.ExitDateTime == Dates, Indices ) );

        y = Year();
        m = Month();
        d = Day();
        exEnterDate = StrFormat( "%02.0f-%02.0f-%02.0f,", y[ entryindex ], m[ entryindex ], d[ entryindex ] );
        exExitDate  = StrFormat( "%02.0f-%02.0f-%02.0f,", y[ exitindex ], m[ exitindex ], d[ exitindex ] );

        exSYMBOL = TRADE.Symbol;
        exNAME = TRADE.FullName();
        exPROFIT = Prec( trade.GetPercentProfit, 1 );
        exBARS = trade.BarsInTrade;
        exEntryPrice = trade.EntryPrice;

        inDT  = trade.EntryDateTime;
        outDT = trade.ExitDateTime;
        //_TRACE( exSymbol + " - Enter date: " + DateTimeToStr( inDT ) + " - Exit Date: " + DateTimeToStr( outDT ) );

        // this will save the entry and exits on separate lines
        EXNAME = "\"" + EXNAME + "\""; // Add "" to full name to avoid potential issues importing .csv file in Excel or other apps
        VarSet( "TradeDate_" + counter, inDT );
        VarSetText( "TradeInfo_" + counter, "BUY," + exsymbol + "," + EXNAME + "," + exEnterDate + "--,--" );
        counter++;
        VarSet( "TradeDate_" + counter, outDT );
        VarSetText( "TradeInfo_" + counter, "SELL," + exsymbol + "," + EXNAME + "," + exEnterDate  + exBars + "," + exProfit );
    }
}

// Sort using a matrix
if( counter > 0 )
{
    //_TRACE( "Counter = " + counter + " - Creating matxix" );
    mx = Matrix( counter, 2, 0 );
    //_TRACE( MxToString( mx ) + " " );

    for( j = 0; j < counter; j++ )
    {
        dateTrade = VarGet( "TradeDate_" + ( j + 1 ) );
        mx[j][0] = dateTrade;
        mx[j][1] = j + 1; // progressive order index
        //_TRACE( "" + ( j + 1 ) + " - Adding to matrix date : " + DateTimeToStr( dateTrade ) );
    }

    //_TRACE( MxToString( mx ) + " " );
    mxs = mxSortRows( mx, True, 0 ); // sort by first column (by trade date, ascending)
    //_TRACE( MxToString( mxs ) + " " );

    // Write .csv file
    fh = fopen( "C:\\SORTED.csv", "w" );

    if( fh )
    {
        // .CSV file header / remove if not needed or adapt to other formats
        fputs( "\"sep=,\"\n", fh ); // trick to force Excel to open .csv file using a , as a separator independently of the regional settings...
        fputs( "Date,Action,Symbol,Name,PurchDate,Bars,Profit%\n", fh );

        for( j = 0; j < counter; j++ )
        {
            index = mxs[j][1]; // get the original line index
            dateStr = DateTimeToStr( mxs[j][0] ); // get the sorted data and convert to a string
            //_TRACE( "" + index + " " + DateStr + "," + VarGetText( "TradeInfo_" + index ) );
            // write the line getting it from the dynamic var at the "index"
            fputs( DateStr + "," + VarGetText( "TradeInfo_" + index ) + "\n", fh );
        }

        fclose( fh );
    }
    else
    {
        Error( "ERROR: export file cannot be written" );
    }
}

immagine

mfu5324 · June 5, 2018, 2:07pm

Oh my!!! I never thought of using the matrix functions.... have never used before. I quickly plugged it in and it looks great. Thank you so much for your expertise on this. I will work through the code tonight and post my results.

THANK YOU!!!!!!!

mfu5324 · June 5, 2018, 8:47pm

MANY THANKS TO ALL OF YOU THAT HELPED ... SPECIAL THANKS TO BEPPE!!!! below is a screen shot of the trade list exported to html....... the code worked perfect!

Capture22

mfu5324 · June 5, 2018, 8:49pm

And for kicks... here is the Current position html...

Capture23

Milosz · June 5, 2018, 9:09pm

So you can like @beppe post and mark it as the solution ...

obraz