Exporting a csv file for Interactive Brokers BasketTrader

jptrader · August 21, 2024, 9:07pm

Hello,

I am trying to write a code to export a csv file to be used by Interactive Brokers BasketTrader. I have two issues that I am unable to solve on my own. I would appreciate your help with those.

/// Example System

Buy   = RSI(2) < 20 AND Close > MA(Close, 200);
Sell  = RSI(2) > 80;
Short = RSI(2) > 80 AND Close < MA(Close, 200);
Cover   = RSI(2) < 20;

Buy   = ExRem(Buy, Sell);
Sell  = ExRem(Sell, Buy);
Short = ExRem(Short, Cover);
Cover = ExRem(Cover, Short);

LmtPrice = IIf(Buy, Close * 0.93, IIf(Short, Close * 1.07, 0));
StpPrice = IIf(Buy, Close * 0.83, IIf(Short, Close * 1.17, 0));
Action   = WriteIf(Buy, "BUY", WriteIf(Short, "SELL", "0"));

/// Exploration

Filter = Buy OR Short;
AddtextColumn(Action, "Action");
AddColumn(LmtPrice, "LmtPrice");
AddColumn(StpPrice, "StpPrice");

/// Writing a csv file

fh = fopen( "c:\\AmibrokerDataExport\\Example.csv", "w");
if( fh )
{
   fputs("Symbol,SecType,Exchange,Date,Action,TimeInForce,OrderType,UsePriceMgmtAlgo,LmtPrice,QuantityValue\n", fh);
   
      fputs(Name() + "," + "STK,SMART/AMEX," + Date() + "," + Action + "," + "GTC,LMT,true," + StrFormat("%.2f", LmtPrice) + "," + StrFormat("%.0f", 100), fh);

   fclose( fh );
}

The first issue comes with the following line. I do not get why WriteIf does not recognise the Buy or Short case and displays the third case 0.

Action   = WriteIf(Buy, "BUY", WriteIf(Short, "SELL", "0"));

The second issue is related with the csv exportation. The present code gives just one line, for the last day in range of the last symbol evaluated (there is no buy or short signal for that day and symbol).

Could you give me some orientation to keep working? I would need the code to provide a csv list that
a) contains only the lines corresponding to buy or short signals for b) all the symbols in the exploration list.

Thank you in advance for your help.

Regards.

mradtke · August 21, 2024, 9:49pm

It looks like you are assuming that WriteIf() returns an array of strings. If you look closely at the documentation, you will see that it does not; it returns a single string.

As for your csv file, you're calling fopen() with the mode set to "w". That will delete the file if it already exists and write a new file. You should be using a mode of "w" to write the headers once (when status("stocknum") == 0), and then use a mode of "a" (append) to add the rows for each order.

jptrader · August 22, 2024, 12:08pm

mradtke,

Thank you for your help and prompt response. I am afraid I need to learn more afl coding to solve these issues.

1) About WriteIf()

I have tried several ways to produce a text from a Buy / Short signal but I have been unable to solve it. For example, the following code also produces the same result.

for( i = 1; i < BarCount; i++ )
{
   if (Buy[i] == 1) {Action = "SELL";}
   else if (Short[i] == 1) {Action = "SELL";}
   else {Action = "0";}
}

Could you provide me with an additional idea to solve this issue. For using BasketTrader I do need a "BUY" / "SELL" text to export.

2) About the csv file

Taking into account your indication, I kept searching and found the following entry, where you solved a similar code issue.

Write exploration to file with header

I have modified the code as it is shown below and it works almost as intended.


Path = "c:\\AmibrokerDataExport\\Example.csv";

if(status("stocknum") == 0);
	{
	fh = fopen(Path, "w");
	fputs("Date,Symbol,SecType,Exchange,Action,TimeInForce,OrderType,UsePriceMgmtAlgo,LmtPrice,QuantityValue\n", fh);
	fclose(fh);
	}

y = Year(); 
m = Month(); 
d = Day(); 
bir = Status("barinrange");

for( i = 0; i < BarCount; i++){
	if(bir[i]){
		fh = fopen(Path, "a", True);
		if(fh AND Buy[i] == 1 OR Short[i] == 1){
			ds = StrFormat("%02.0f%02.0f%02.0f,", y[ i ], m[ i ], d[ i ] ); 
			fputs( ds, fh ); 
			fputs(Name() + "," + "STK,SMART/AMEX," + Action + "," + "GTC,LMT,true," + StrFormat("%.2f", LmtPrice [i]) + "," + StrFormat("%.0f", 100) + "\n", fh);}
	    fclose(fh);}}

There is only one remaining issue. I have a watchlist with three symbols for this example: AAPL, MSFT, NVDA, the exploration window registers the trades for all of them. However, AAPL is missing in the csv list.

Would you know why is happening that?

Again, thank you for your attention.

Regards.

mradtke · August 22, 2024, 2:56pm

Why are you looping through bars? If you're trying to create a basket order file for IB, I would think that you're only interested in whether or not there's a signal on the final bar.

Regarding your first loop (which is probably not needed), you are assigning the Action variable over and over. Only the last assignment will have any effect. Also, you're assigning the string "SELL" to the Action variable both when Buy is true and when Short is true. This appears to be a coding error -- I suspect you should assign the string "BUY" to the Action variable when LastValue(Buy) is true.

Why is AAPL missing from the CSV file? I have no idea, because you have not posted all of your code. I suggest you review this post: How do I debug my formula?.

jptrader · August 22, 2024, 6:24pm

In effect, when I am trading I will be only interested on the signal for the last bar. Now I am looping through the bars from a range mainly to be sure that the code works properly and takes the values from where it is intended. Then, I would only have to change the range to 1 recent bar.

When it comes to LastValue(Buy), do you mean something like the following code? I am unable to make it work.

Action = "0";
if(LastValue(Buy)==1){Action = "BUY";}
if(LastValue(Short)==1){Action = "SELL";}

The last column on the exploration screenshot indicates easily what signals are Buy or Short (1 or -1 respectively). I am afraid I cannot see something that should be so really easy as transforming those 1 and - 1 in a "BUY" and a "SELL" text.

This is the complete code for the exploration part:

/// Example System

Buy   = RSI(2) < 20 AND Close > MA(Close, 200);
Sell  = RSI(2) > 80;
Short = RSI(2) > 80 AND Close < MA(Close, 200);
Cover = RSI(2) < 20;

Buy   = ExRem(Buy, Sell);
Sell  = ExRem(Sell, Buy);
Short = ExRem(Short, Cover);
Cover = ExRem(Cover, Short);

LmtPrice = IIf(Buy, Close * 0.93, IIf(Short, Close * 1.07, 0));
StpPrice = IIf(Buy, Close * 0.83, IIf(Short, Close * 1.17, 0));

Action = "0";
if(LastValue(Buy)==1){Action = "BUY";}
if(LastValue(Short)==1){Action = "SELL";}


/// Exploration

Filter = Buy OR Short;
AddtextColumn(Action, "Action");
AddColumn(LmtPrice, "LmtPrice");
AddColumn(StpPrice, "StpPrice");
AddColumn(Buy - Short, "Buy - Short");

mradtke · August 22, 2024, 6:50pm

The documentation for AddTextColumn() states:

Please note that AddTextColumn takes single string as a parameter, so you can only display text that does NOT vary on bar-by-bar basis.

To have text that varies by bar in the Exploration, you would need to use AddMultiTextColumn().

For the CSV file, if you're going to loop through all the bars, then you will need to set the Action variable at the time that you detect the Buy or Short signal, i.e. inside this if block:

if(fh AND Buy[i] == 1 OR Short[i] == 1)

jptrader · August 23, 2024, 12:03pm

mradtke,

Thank you for your last message, it has been of great help.

Exploration

AddMultiTextColumn() was the easy solution I was really looking for, now that code works as intended. I was not aware of its existence.

Writing the csv file

I have used a slighty different aproach from the if statement you has proposed.

if(fh AND Buy[i] == 1){
	fputs("BUY," + mainorder, fh);}
if(fh AND Short[i] == 1){
	fputs("SELL," + mainorder, fh);}

This way the statements produce the "BUY" / "SELL" text that I need for buying or shorting with the BasketTrader.

The full code goes this way:

/// Example System

Buy   = RSI(2) < 20 AND Close > MA(Close, 200);
Sell  = RSI(2) > 80;
Short = RSI(2) > 80 AND Close < MA(Close, 200);
Cover = RSI(2) < 20;

Buy   = ExRem(Buy, Sell);
Sell  = ExRem(Sell, Buy);
Short = ExRem(Short, Cover);
Cover = ExRem(Cover, Short);

LmtPrice  = IIf(Buy, Close * 0.93, IIf(Short, Close * 1.07, 0));
StopPrice = IIf(Buy, Close * 0.83, IIf(Short, Close * 1.17, 0));

SetPositionSize(1, spsPercentOfEquity);


/// Exploration

Filter = Buy OR Short;
AddMultitextColumn(1 * Buy + 2 * Short, "0\nBUY\nSELL", "Action");
AddColumn(LmtPrice, "LmtPrice");
AddColumn(StopPrice, "StpPrice");
AddColumn(Buy - Short, "Buy - Short");


/// Writing a csv file

Path = "c:\\AmibrokerDataExport\\Example.csv";

if(status("stocknum") == 0);
	{
	fh = fopen(Path, "w");
	fputs("Action,Date,Symbol,Quantity,LmtPrice,StopPrice,OrderId,ParentOrderId,SecType,Exchange,OrderType,TimeInForce,OutsideRth,Account\n", fh);
	fclose(fh);
	}

y = Year(); m = Month(); d = Day(); 
bir = Status("barinrange");

for( i = 0; i < BarCount; i++){
	if(bir[i]){
		fh  = fopen(Path, "a", True);
		dat = StrFormat("%02.0f%02.0f%02.0f", y[i], m[i], d[i]);
		tik = Name();
		qty = StrFormat("%.0f", 100);
		lmt = StrFormat("%.2f", LmtPrice [i]);
		stp = " ";
		oid = StrFormat("%.0f", 12345);
		oip = " ";
		txt = "STK,SMART/AMEX,LMT,DAY,false,U12345\n";
		mainorder = dat + "," + tik + "," + qty + "," + lmt + "," + stp + "," + oid + "," + oip + "," + txt; 
		if(fh AND Buy[i] == 1){
			fputs("BUY," + mainorder, fh);}
		if(fh AND Short[i] == 1){
			fputs("SELL," + mainorder, fh);}
	    fclose(fh);}}

The last issue is that I get Error 26 when running the exploration (and I do not keep the csv file open during the process)

This is line 35.

fputs("Action,Date,Symbol,Quantity,LmtPrice,StopPrice,OrderId,ParentOrderId,SecType,Exchange,OrderType,TimeInForce,OutsideRth,Account\n", fh);

However, the problem is located in the last part of the code, if I delete it the code works well.

for( i = 0; i < BarCount; i++){
	if(bir[i]){
		fh  = fopen(Path, "a", True);
		dat = StrFormat("%02.0f%02.0f%02.0f", y[i], m[i], d[i]);
		tik = Name();
		qty = StrFormat("%.0f", 100);
		lmt = StrFormat("%.2f", LmtPrice [i]);
		stp = " ";
		oid = StrFormat("%.0f", 12345);
		oip = " ";
		txt = "STK,SMART/AMEX,LMT,DAY,false,U12345\n";
		mainorder = dat + "," + tik + "," + qty + "," + lmt + "," + stp + "," + oid + "," + oip + "," + txt; 
		if(fh AND Buy[i] == 1){
			fputs("BUY," + mainorder, fh);}
		if(fh AND Short[i] == 1){
			fputs("SELL," + mainorder, fh);}
	    fclose(fh);}}

I cannot see why the file could not be open... I have tried multiple combinations opening and closing the file from differents parts of the for statement, with similar results. Do you know what could cause it?

awilson · August 23, 2024, 12:35pm

/// Example System

Buy   = RSI(2) < 20 AND Close > MA(Close, 200);
Sell  = RSI(2) > 80;
Short = RSI(2) > 80 AND Close < MA(Close, 200);
Cover = RSI(2) < 20;

Buy   = ExRem(Buy, Sell);
Sell  = ExRem(Sell, Buy);
Short = ExRem(Short, Cover);
Cover = ExRem(Cover, Short);

LmtPrice  = IIf(Buy, Close * 0.93, IIf(Short, Close * 1.07, 0));
StopPrice = IIf(Buy, Close * 0.83, IIf(Short, Close * 1.17, 0));

SetPositionSize(1, spsPercentOfEquity);


/// Exploration

Filter = Buy OR Short;
AddMultitextColumn(1 * Buy + 2 * Short, "0\nBUY\nSELL", "Action");
AddColumn(LmtPrice, "LmtPrice");
AddColumn(StopPrice, "StpPrice");
AddColumn(Buy - Short, "Buy - Short");


/// Writing a csv file

Path = "c:\\AmibrokerDataExport\\Example.csv";

if(status("stocknum") == 0);
	{
	fh = fopen(Path, "w");
	fputs("Action,Date,Symbol,Quantity,LmtPrice,StopPrice,OrderId,ParentOrderId,SecType,Exchange,OrderType,TimeInForce,OutsideRth,Account\n", fh);
	fclose(fh);
	}

y = Year(); m = Month(); d = Day(); 
bir = Status("barinrange");

fh  = fopen(Path, "a", True);
if( fh )
{
for( i = 0; i < BarCount; i++){
	if(bir[i]){
		dat = StrFormat("%02.0f%02.0f%02.0f", y[i], m[i], d[i]);
		tik = Name();
		qty = StrFormat("%.0f", 100);
		lmt = StrFormat("%.2f", LmtPrice [i]);
		stp = " ";
		oid = StrFormat("%.0f", 12345);
		oip = " ";
		txt = "STK,SMART/AMEX,LMT,DAY,false,U12345\n";
		mainorder = dat + "," + tik + "," + qty + "," + lmt + "," + stp + "," + oid + "," + oip + "," + txt; 
		if(fh AND Buy[i] == 1){
			fputs("BUY," + mainorder, fh);}
		if(fh AND Short[i] == 1){
			fputs("SELL," + mainorder, fh);}
	    } // end if(bir)
 } // end for
 fclose(fh);

} // end if( fh)

mradtke · August 23, 2024, 2:16pm

As @awilson has illustrated, it would probably be better if you didn't open and close the file for every bar of every symbol. Either open and close it once per symbol as in his example, or only open and close it when you're going to call fputs().

Also, this line is incorrect:

By adding a semicolon at the end of that line, you have terminated the "if" statement. That means that everything inside the curly braces after the "if" will run unconditionally.

Finally, make sure you don't have the file open in Excel or a text editor when you run the AFL or else there will be a sharing violation and AmiBroker won't be able to open the file.

jptrader · August 23, 2024, 5:16pm

mradtke, awilson,

Thank you for your help and attention. I have learn a lot and now the code works perfectly:

This is the final code, includind the last correction by mradtke:

/// Example System

Buy   = RSI(2) < 20 AND Close > MA(Close, 200);
Sell  = RSI(2) > 80;
Short = RSI(2) > 80 AND Close < MA(Close, 200);
Cover = RSI(2) < 20;

Buy   = ExRem(Buy, Sell);
Sell  = ExRem(Sell, Buy);
Short = ExRem(Short, Cover);
Cover = ExRem(Cover, Short);

LmtPrice  = IIf(Buy, Close * 0.93, IIf(Short, Close * 1.07, 0));
StopPrice = IIf(Buy, Close * 0.83, IIf(Short, Close * 1.17, 0));


/// Exploration

Filter = Buy OR Short;
AddMultitextColumn(1 * Buy + 2 * Short, "0\nBUY\nSELL", "Action");
AddColumn(LmtPrice, "LmtPrice");
AddColumn(StopPrice, "StpPrice");
AddColumn(Buy - Short, "Buy - Short");


/// Writing a csv file

Path = "c:\\AmibrokerDataExport\\Example.csv";

if(status("stocknum") == 0)
	{
	fh = fopen(Path, "w");
	fputs("Action,Date,Symbol,Quantity,LmtPrice,StopPrice,OrderId,ParentOrderId,SecType,Exchange,OrderType,TimeInForce,OutsideRth,Account\n", fh);
	fclose(fh);
	}

y = Year(); m = Month(); d = Day(); 
bir = Status("barinrange");

fh  = fopen(Path, "a", True);
if(fh)
{
for( i = 0; i < BarCount; i++){
	if(bir[i]){
		dat = StrFormat("%02.0f%02.0f%02.0f", y[i], m[i], d[i]);
		tik = Name();
		qty = StrFormat("%.0f", 100);
		lmt = StrFormat("%.2f", LmtPrice [i]);
		stp = " ";
		oid = StrFormat("%.0f", 12345);
		oip = " ";
		txt = "STK,SMART/AMEX,LMT,DAY,false,U12345\n";
		mainorder = dat + "," + tik + "," + qty + "," + lmt + "," + stp + "," + oid + "," + oip + "," + txt; 
		if(fh AND Buy[i] == 1){
			fputs("BUY," + mainorder, fh);}
		if(fh AND Short[i] == 1){
			fputs("SELL," + mainorder, fh);}}}
 fclose(fh);}

Regards.