Manual Portfolio interface

Dear Group,

This post may seem a bit trivial, given that it can be manually maintained in excel. However I wish to learn the process a bit, and am not able to really SEARCH for the solution through.
I wish to back-test creation of a model portfolio which we traded over last calendar year. The portfolio is a mix of CASH & STOCK component. The total of invested amount is fixed & CASH component too is in the form of ETF that is part of the database. As I get into one stock, I would be exiting equal amount of CASH from the portfolio. Basically the STOCKs won't be purchased all in one go & hence the entry dates would vary.
If I wish to track the performance of the above portfolio month on month or day on day, how do I go about it?
Note : This has nothing to do with actual strategy of trading as such, but only generate a report of the same.

Refer this link: http://www.amibroker.com/kb/2014/09/30/gen-backtest-from-a-file/

file = "C:\\Users\\Lappy\\Desktop\\Modified Cash PMS Returns 13.1.2020.txt"; // change this to real location of your data file
dt = DateTime();
//
// Initialize variables
Buy = Sell = possize = 0;
//
fh = fopen( file, "r" );
//
if( fh )
 {
     while( ! feof( fh ) )
     {
         line = fgets( fh );
         // get the ticker symbol from the file
         sym = StrExtract( line, 0 );
         // if ticker matches current symbol
         if ( Name() == sym )
         {
             // extract data from line of text
             trade = StrExtract( line, 1 );
             trade_datetime = StrToDateTime( StrExtract( line, 2 ) );
             price = StrToNum( StrExtract( line, 3 ) );
             shares = StrToNum( StrExtract( line, 4 ) );
             //
             if ( trade == "Buy" )
             {
                 newbuy = dt == trade_datetime;
                 Buy = Buy OR newbuy; // combine previous buy signals with new
                 BuyPrice = IIf( newbuy, price, BuyPrice );
                 possize = IIf( newbuy, shares, possize );
             }
             //
             if ( trade == "Sell" )
             {
                 newsell = dt == trade_datetime;
                 Sell = Sell OR newsell; // combine previous sell signals with new
                 SellPrice = IIf( newsell, price, SellPrice );
             }
         }
     }
     //
     fclose( fh );
 }
 /*else
 {
     Error("ERROR: file can not be open" );
 }
//
*/
//SetPositionSize( possize, spsShares )

Have made comments on code @
/*else
{
Error("ERROR: file can not be open" );
}
//
*/
//SetPositionSize( possize, spsShares )

I ran an exploration on all symbols as also backtest for LONG only. However the result draws a blank.

The csv or txt file has entries as follows

Symbol,Trade,Date,Price,Shares
STAR,Buy,2019-01-22,549.15,65
HINDUNILVR,Buy,2019-02-01,1822.94,45

If you not use SetPositionSize function, you are buying and selling 0 (zero) shares.

Try to change:

//SetPositionSize( possize, spsShares )

to:

SetPositionSize( possize, spsShares );

Yes did it as suggested. However I am still not able to get through. Either my file format is not in sync, or am missing something on BackTest basics
The new code is as below:

file = "C:\\Users\\Lappy\\Desktop\\Modified Cash PMS Returns 13.1.2020.txt"; // change this to real location of your data file
dt = DateTime();
//
// Initialize variables
Buy = Sell = possize = 0;
//
fh = fopen( file, "r" );
//
if( fh )
 {
     while( ! feof( fh ) )
     {
         line = fgets( fh );
         // get the ticker symbol from the file
         sym = StrExtract( line, 0 );
         // if ticker matches current symbol
         if ( Name() == sym )
         {
             // extract data from line of text
             trade = StrExtract( line, 1 );
             trade_datetime = StrToDateTime( StrExtract( line, 2 ) );
             price = StrToNum( StrExtract( line, 3 ) );
             shares = StrToNum( StrExtract( line, 4 ) );
             //
             if ( trade == "Buy" )
             {
                 newbuy = dt == trade_datetime;
                 Buy = Buy OR newbuy; // combine previous buy signals with new
                 BuyPrice = IIf( newbuy, price, BuyPrice );
                 possize = IIf( newbuy, shares, possize );
             }
             //
             if ( trade == "Sell" )
             {
                 newsell = dt == trade_datetime;
                 Sell = Sell OR newsell; // combine previous sell signals with new
                 SellPrice = IIf( newsell, price, SellPrice );
             }
         }
     }
     //
     fclose( fh );
 }
   else
 {
     Error("ERROR: file can not be open" );
 }

SetPositionSize( possize, spsShares );

Check Analysis Settings

  • Initial Equity
  • Positions

In Portfolio Tab check Limiting trade when there is volume

If still have problem try adding some addcolumns and see if your Buy and Sell are being populate

1 Like

Dear Awilson,

While moving through the formula, I found that the addColumn in itself is not populated when put out of the loop.

Still a bit stuck up.

Would be easier to help if you post more info, like post your last code.

1 Like

The code:

file = "C:\\Users\\Lappy\\Desktop\\ModifiedCash.csv"; // change this to real location of your data file
dt = DateTime();
//
// Initialize variables
Buy = Sell = possize = 0;
//
fh = fopen( file, "r" );
//
if( fh )
 {
     while( ! feof( fh ) )
     {
         line = fgets( fh );
         // get the ticker symbol from the file
         sym = StrExtract( line, 0 );
         // if ticker matches current symbol
         if ( Name() == sym )
         {
             // extract data from line of text
             trade = StrExtract( line, 1 );
             trade_datetime = StrToDateTime( StrExtract( line, 2 ) );
             price = StrToNum( StrExtract( line, 3 ) );
             shares = StrToNum( StrExtract( line, 4 ) );
             //
             if ( trade == "Buy" )
             {
                 newbuy = dt == trade_datetime;
                 Buy = Buy OR newbuy; // combine previous buy signals with new
                 BuyPrice = IIf( newbuy, price, BuyPrice );
                 possize = IIf( newbuy, shares, possize );
             }
             //
             if ( trade == "Sell" )
             {
                 newsell = dt == trade_datetime;
                 Sell = Sell OR newsell; // combine previous sell signals with new
                 SellPrice = IIf( newsell, price, SellPrice );
             }
         }
     }
     //
     fclose( fh );
 }
   else
 {
     Error("ERROR: file can not be open" );
 }

SetPositionSize( possize, spsShares );

Filter = 1;

AddColumn(C,"C",1.2);
AddColumn(bUYprice,"price",1.2);
AddColumn(POSSIZE,"shares",1.0);

The csv

Symbol,Trade,Date,Price,Shares
STAR,Buy,1/22/2019,549.15,65
HINDUNILVR,Buy,2/1/2019,1822.94,45
TECH MAHINDRA,Buy,2/6/2019,800.6,85
PFC,Buy,2/26/2019,112.07,500
DCB BANK,Buy,2/26/2019,185.35,500
BAJAJ AUTO,Buy,2/27/2019,2932.85,40
ICICIGI,Buy,3/1/2019,953.68,80
PNB,Buy,3/7/2019,85.42,1000
HDFCLIFE,Buy,3/12/2019,368.99,250
HDFCAMC,Buy,4/1/2019,1563.12,40
DELTACORP,Buy,4/3/2019,277.55,250
ADANIPORTS,Buy,6/3/2019,417.82,200
HDFC,Buy,6/27/2019,2188.78,35
ICICIPRULI,Buy,9/3/2019,425.7,160
MUTHOOT FINANCE,Buy,9/24/2019,679.36,100
GMR INFRA,Buy,10/9/2019,17.13,3500
BAJAJ AUTO,Buy,10/22/2019,3156.3,30
BF UTILITES,Buy,10/30/2019,257.51,200
ADANI PORTS,Buy,11/29/2019,383.77,190
BANCO INDIA,Buy,12/30/2019,108.27,600
LIQUID BEES,Buy,12/31/2018,1000,218.5
STAR,Sell,4/1/2019,473.09,65
HINDUNILVR,Sell,5/3/2019,1703.71,45
TECH MAHINDRA,Sell,6/27/2019,703.12,85
PFC,Sell,4/3/2019,122.26,500
DCB BANK,Sell,8/6/2019,181.49,500
BAJAJ AUTO,Sell,7/18/2019,2641.71,40
PNB,Sell,6/13/2019,74.12,1000
DELTACORP,Sell,4/22/2019,241.47,250
ADANIPORTS,Sell,8/22/2019,342.79,200

The exploration output (Know it is wrong for filter =1)

Ticker Date/Time C price shares
ADANIPORTS 1/15/2020 00:00:00 390.80 391.40 0
ASIANPAINT 1/15/2020 00:00:00 1842.25 1845.00 0
AXISBANK 1/15/2020 00:00:00 746.30 749.00 0
BAJAJ-AUTO 1/15/2020 00:00:00 3123.55 3138.00 0
BAJAJFINSV 1/15/2020 00:00:00 9589.15 9619.00 0
BAJFINANCE 1/15/2020 00:00:00 4207.25 4220.00 0
BHARTIARTL 1/15/2020 00:00:00 467.85 472.00 0
BPCL 1/15/2020 00:00:00 467.95 472.95 0
BRITANNIA 1/15/2020 00:00:00 3114.20 3133.95 0
CIPLA 1/15/2020 00:00:00 484.00 486.85 0
COALINDIA 1/15/2020 00:00:00 212.45 214.60 0
DRREDDY 1/15/2020 00:00:00 2918.65 2946.50 0
EICHERMOT 1/15/2020 00:00:00 20624.80 20650.00 0
GRASIM 1/15/2020 00:00:00 766.40 769.00 0
GAIL 1/15/2020 00:00:00 131.65 132.05 0
HCLTECH 1/15/2020 00:00:00 595.75 598.30 0
HDFCBANK 1/15/2020 00:00:00 1284.25 1287.95 0
HDFC 1/15/2020 00:00:00 2488.60 2495.00 0
HEROMOTOCO 1/15/2020 00:00:00 2476.30 2480.55 0
HINDALCO 1/15/2020 00:00:00 213.65 215.00 0
HINDUNILVR 1/15/2020 00:00:00 2021.65 2027.00 0
IBULHSGFIN 1/15/2020 00:00:00 311.45 316.45 0
ICICIBANK 1/15/2020 00:00:00 535.60 536.95 0
INDUSINDBK 1/15/2020 00:00:00 1400.50 1463.40 0
INFRATEL 1/15/2020 00:00:00 247.05 252.15 0
INFY 1/15/2020 00:00:00 767.55 773.85 0
IOC 1/15/2020 00:00:00 125.70 127.00 0
ITC 1/15/2020 00:00:00 242.40 243.00 0
JSWSTEEL 1/15/2020 00:00:00 282.50 283.40 0
KOTAKBANK 1/15/2020 00:00:00 1675.60 1686.60 0
LT 1/15/2020 00:00:00 1323.60 1336.15 0
M&M 1/15/2020 00:00:00 569.75 571.00 0
MARUTI 1/15/2020 00:00:00 7482.95 7505.00 0
NIFTY 1/15/2020 00:00:00 12343.30 12355.15 0
NTPC 1/15/2020 00:00:00 123.85 125.00 0
ONGC 1/15/2020 00:00:00 124.65 125.50 0
POWERGRID 1/15/2020 00:00:00 194.25 195.80 0
RELIANCE 1/15/2020 00:00:00 1523.85 1539.90 0
SBIN 1/15/2020 00:00:00 324.25 329.20 0
SUNPHARMA 1/15/2020 00:00:00 449.05 450.70 0
TATAMOTORS 1/15/2020 00:00:00 200.35 201.70 0
TATASTEEL 1/15/2020 00:00:00 502.10 503.55 0
TCS 1/15/2020 00:00:00 2226.90 2231.00 0
TECHM 1/15/2020 00:00:00 795.15 803.65 0
TITAN 1/15/2020 00:00:00 1184.85 1191.50 0
ULTRACEMCO 1/15/2020 00:00:00 4506.50 4525.25 0
UPL 1/15/2020 00:00:00 585.80 589.50 0
VEDL 1/15/2020 00:00:00 164.65 165.45 0
WIPRO 1/15/2020 00:00:00 248.25 256.00 0
YESBANK 1/15/2020 00:00:00 39.75 41.10 0
ZEEL 1/15/2020 00:00:00 272.80 274.20 0

Your code worked for me, with minor changes.

  1. I changed Filter = Buy
  2. and changed the date format at the csv file
    to match mine which is DD/MM/YYYY
  3. set Range to All Quotes in Analysis

Thanks @awilson that helped to a greater extent. However the next step is tracking the portfolio to current prices where net quantity != 0

The current afl

file = "C:\\Users\\Pushkaraj.GEPLDomain\\Desktop\\ModifiedCash.csv"; // change this to real location of your data file
dt = DateTime();
//
// Initialize variables
Buy = Sell = possize = tradeprice = value = 0;


//
fh = fopen( file, "r" );
//
if( fh )
 {
     while( ! feof( fh ) )
     {
         line = fgets( fh );
         // get the ticker symbol from the file
         sym = StrExtract( line, 0 );
         // if ticker matches current symbol
         if ( Name() == sym )
         {
             // extract data from line of text
             trade = StrExtract( line, 1 );
             trade_datetime = StrToDateTime( StrExtract( line, 2 ) );
             price = StrToNum( StrExtract( line, 3 ) );
             shares = StrToNum( StrExtract( line, 4 ) );
             //
             if ( trade == "Buy" )
             {
                 newbuy = dt == trade_datetime;
                 Buy = Buy OR newbuy; // combine previous buy signals with new
                 BuyPrice = IIf( newbuy, price, BuyPrice );
                 possize = IIf( newbuy, shares, possize );
                 
             }
             //
             if ( trade == "Sell" )
             {
                 newsell = dt == trade_datetime;
                 Sell = Sell OR newsell; // combine previous sell signals with new
                 SellPrice = IIf( newsell, price, SellPrice );
                 possize = IIf( newsell, -shares, possize );
                 
             }
             
             tradeprice = IIf( Buy, Buyprice, SellPrice );
            
             value=tradeprice*possize;
             
         }
         
      
     }
     //
     fclose( fh );
 }
   else
 {
     Error("ERROR: file can not be open" );
 }



SetPositionSize( possize, spsShares );

Filter = Buy OR Sell;


AddColumn(possize,"Quantity",1.0);

AddColumn(tradeprice,"TP",1.2);
AddColumn(value,"Initiating Value",1.2);


AddSummaryRows( 1, 1.2,5 );


Am attaching a screenshot for the current output and expected output (The highlighted portion). Thus it calculates the net open position.

Expected

Steps that I am listing out:

  1. Like SUMIF in excel, find the net quantity across each ticker
  2. Current Price - When running across all symbols & all bars, normal close that it returns is the close of day when Buy or Sell condition is met. We need the last traded close.