SQL Query using ODBC executes only once per date range in Analysis window

Hi All,

So for the market I trade in, India, NSE, there is no easily available Index constituent data from any data provider. Prior to buying Amibroker, I'd made a SQL database with basic stocks data and whatever index constituent data I could find (2016 onwards) which I was using for other purposes.

Now I would like to use the same data in Amibroker backtest to ensure that only index constituent stocks are being traded in the backtest. To that end I used ODBC plugin in AFL formula and read the database data.
The database table CNX500_Historical has fields FromDate and ToDate for inclusion of the stock in the index. So my intent is to check whether a data row for the current symbol exists in the database between the 2 dates fields, if so, then it is a valid stock to be considered index constituent for that time period.

connectionStr = "Provider=SQLOLEDB.1;Password=xxxxxxx;Persist Security Info=True;User ID=xxxxxxx;Initial Catalog=Stocks;Data Source=(local);";

sqlResult = 0;
inIndex = false;

dateArray = DateTime();
bi = BarIndex();
arrayitem = SelectedValue( bi ) - bi[ 0 ];
dt = dateArray[arrayItem];

query = "Select 1 from ['CNX500_Historical'] where Symbol_Yahoo = '"+ Name() +"' and '"+ DateTimeToStr(dt, 4) +"' between FromDate and ISNULL(ToDate, getdate())";

sqlResult = odbcGetValueSQL(query);
if(sqlResult == 1)
	inIndex = True;
	inIndex = False;
_TRACE(DateTimeToStr( dt, 4) +" Symbol=" + Name() +" bi=" + BarIndex() +" arrayItem="+ arrayItem);

Filter = 1;

AddColumn(Close, "Price", 1.2); 
AddColumn(sqlResult, "sqlResult", 1);
AddColumn(inIndex, "InIndex", 1);
AddColumn(DateTime(), "DateTime", formatDateTime );

SetSortColumns(2, 1);

I expected that the above code will run the SQL query once for each date in the exploration range and return the result.
However the SQL query is being executed only once on the last date of the date range and the result is being applied to all the rows in AA results window.

e.g. ACE (the only stock in watchlist i'm testing) is index constituent between 01-APR-2023 to 29-SEP-2023. If I run the exploration for a date range of 03-APR-2023 to 05-SEP-2023, it ODBC query returns "1" for all records in the range - but it executes only once on 05-SEP-2023, not daily through the range as shown in Log in screenprint below.

So when I select a data range where this ticker was in the index i.e. query returns 1 and the 0 when it falls out of the index, the ODBC query again runs only once on the last date of the range and returns a 0 for all dates

I did see @fxshrat detailed response to handle this within Amibroker itself Help Needed! Build a database with all historical index constituent, but since I already have this database I would prefer to use it as the single source for this data.

Please advise, Any pointers will help, thanks in advance!

You need to read the manual.

The formula is executed ONCE PER SYMBOL, for ALL bars at once. AFL is array processing language. It processes entire array (vector) in parallel - like supercomputers and graphic cards do.

In your SQL, you should be returning ARRAY (column) not one value.

Thanks Tomasz, I didn't realize this was true for external datasets being used as lookup/reference values too. My current list of Index constituents is not in a daily array format which is what will be needed, so either I need to convert my data into daily arrays first or to use the method mentioned by Fxshrat I will need to atleast reformat the data to import into Aux1/Aux2 fields.
On the whole now I see I'll be better off from Amibroker context to use the solution provided in the thread, even though I'll need to keep 2 datasets updated.

Thank you for the pointers!

This topic was automatically closed 100 days after the last reply. New replies are no longer allowed.