How to Add ETF instruments that are already part of a larger market group

One other thing I'm having difficulty with is organizing ETFs that are already included in the broader market or exchange, For example, I'm interested in the Canadian, US, and Japan markets. When I download the major exchange listing for all three of these (greater) markets, it downloads those fine, but then if I download the ETF listings for each of those markets each on their own since most of them are already included in their respective broader markets (ie. most, if not all, CDN ETFs are already within the broader TSX market) it seems to only download any of the new ones that weren't already included in the previous download fo the broader market.
Knowing this, how can I remedy things to have the full listings of an exchange available in the Group and/or Watchlist headings, but also have the full ETF list of instruments in that same Group and/or Watchlist Areas.

Kind regards

@Brossy,
I have separate database for each exchange. For example, my US is setup as below.
Market
I put ETF and ETN into another market.
In AFL, ETF can be screen out with MarketID(1) != "ETF".

@Peter2047
Ah that's probably very useful. Can you describe to me how you do that, I'm not sure I understand how you've created it using MarketID(1)!="ETF". Where do you go to be able to filter that out? I'm still learning the intricacies of this powerful software.
If I understand correctly, you've created a database for each marker (for example, US, Canada, Japan, etc) and then within there you can separate each market, is that correct?
Similarly, what's the best method to update all your databases especially if I just want to append the most recent days' data (or the last few days if I was away).

Yes. I have one database for each country. I go to each database by File->Recent Databases->Select database. I assume that you are using AmiQuote. Just select your data source e.g. Yahoo Finance and select the start date and end date for the new data and AmiQuote will automatically append the new data into your database.

Ah, ok. Yeah that makes sense. I have all 3 of mine currently in one database Japan, US and Canada. If it becomes cumbersome, I might change things similarly to how you've organized yours. Thanks for your input @Peter2047. Out of curiosity, how do you perform daily updates to your lists / databases and likewise how do you perform your database cleanups (new issues and delistings). Does the database cleanup tool work well for this. I'm scared to test it for fear of it messing up my databases and similarly, I think I saw it mentioned that it only updates north American markets data (I assume that would just be NASDAQ, NYSE and AMEX). I'm still learning and playing around with the software, but I'm liking what I'm seeing with it so far, along with this wonderful Amibroker user group community. Big learning curve but it's been fun.

I have script to download daily data either from Yahoo Finance or other exchange website. This can be done with AmiQuote as well. After each update, I use this AFL script to scan for missing data.

// Must set range to "1 recent bar(s)"

// Scan latest date
EnableScript("JScript");
<%
function Scan_latest_date(){
	AmiBroker = new ActiveXObject("Broker.Application");
	var bar_count, stock_count, date_num, latest_date_num, latest_date_str, oDate;
	stocks = AmiBroker.Stocks;
	stock_count = stocks.Count;
	latest_date_num = 20200101;
	for (i=51; i<75; i++) {
		stock_quote = stocks(i).Quotations;
		bar_count = stock_quote.count;
		oDate = new Date( stock_quote(bar_count-1).Date );
		date_num = oDate.getFullYear()*10000 + (oDate.getMonth()+1)*100 + oDate.getDate();
		if (date_num > latest_date_num) {
			latest_date_num = date_num;
			if ((oDate.getMonth()+1) < 10) {
				mth_str = "0" + (oDate.getMonth()+1);
			} else {
				mth_str = (oDate.getMonth()+1);
			}
			if (Number(oDate.getDate()) < 10) {
				day_str = "0" + oDate.getDate();
			} else {
				day_str = oDate.getDate();
			}
			latest_date_str = oDate.getFullYear() + "-" + mth_str + "-" + day_str;
		}
	}
	return latest_date_str;
}
%>

if ( status("stocknum") == 0 ) { //Set the latest date for the first time in exploration
	script = GetScriptObject();
	latest_date_str = script.Scan_latest_date();
	StaticVarSetText("missing_datetime",latest_date_str);
	fh = fopen("C:\\Users\\....\\missing_data.txt", "w",shared=True); \\replace .... with your file output dir
} else {
	fh = fopen("C:\\Users\\....\\missing_data.txt", "a",shared=True); \\replace .... with your file output dir
}

Sort_key = ParamList("Date Selection:","Auto Scan|Manual Input",defaultval=0);
yr = Param("Year",2021,2020,2030,1);
mth = Param("Month",1,1,12,1);
dy = Param("Day",1,1,31,1);

if (Sort_key == "Auto Scan") {
	ymd = StaticVarGetText("missing_datetime");}
else {
	ymd= NumToStr(yr,1.0,False) + "-" + NumToStr(mth,1.0) + "-" + NumToStr(dy,1.0); }

Date_time = DateTime();
Date_num = DateNum();
if (StrRight(Name(),2) == "_D") {
	Filter = 0; // Skip ticker symbol with "_D"
	if (fh) fclose(fh);
} else {
	if (IsNull(Lookup(BarIndex(),_DT(ymd),0))) {
		Filter = 1;
		if (fh)
		{
			if (StrLeft(Name(),1) != "^") {
				fputs( Name() + "\n" , fh );
			}
			fclose(fh);
		}
		else { //if fh is null, do fopen until fh is not null
			do
				fh = fopen("C:\\Users\\....\\missing_data.txt", "a",shared=True); \\replace .... with your file output dir
			while (!fh);
			if (StrLeft(Name(),1) != "^") {
				fputs( Name() + "\n" , fh );
			}
			fclose(fh);
		}
	}
	//if (Lookup(BarIndex(),_DT(ymd),0) == Null) Filter = 1; // This does not work. Why ?
	else {
		Filter = 0;
		if (fh) fclose(fh);
	}
}
SetOption("NoDefaultColumns",True);
AddTextColumn(Name(),"Ticker");

I use Nasdaq stock split to check for stock split.
Stock Splits Calendar | Nasdaq

For new tickers and delisted, I use this.
Stock Screener | Nasdaq

For delisted stocks, I add "_D" to the tickers as I notice after sometimes delisted tickers will be reused. Otherwise, it will mess up your database if you intend to keep delisted stocks in your database. If a ticker is having missing data, then it may not worth tracking as it is not worth investing.

2 Likes

IMPORTANT: It is recommended NOT to run script (JScript) from Analysis window.

Scripts should be run from the Tools menu (via wscript/cscript, the way how built-in cleanup script is called). See Tools->Cleanup database and (Tools->Customize, "Tools" tab)

Also AmiBroker has built-in tool for detecting stock splits, missing quotes, etc, it is available from Tools->Database Purify

Oh wow, thanks for this @Peter2047 and @Tomasz. I was busy this week but I'll play around with this a bit on the weekend.

Thanks @Peter2047 and @Tomasz ..... Finally got a chance to start looking into this. I'm going to have to delve deeper into this to see what each line does for my own purposes, but looks very interesting. As a start though can you provide some info as to how this script would be used (or any script for that matter) with Amiquote. I wasn't aware that jscripts could be done this way. Just want to understand from a broader angle before I go messing with my current databases. Thanks again as always.