Master db = how to distinguish Stocks from ETFs?

#1

How could we distinguish stocks from ETFs in the master DB?

In the master DB I see these 2 columns: SecType and Etf.
where Etf takes values 0 and 1. I thought Etf=0 would mean the security is not an ETF and Etf=1 would mean the security if of type ETF. But I did find quite a few contradicting examples where [SecType=STK and Etf=0 and the security is an actual ETF], refer to the attached screen-shot.

is there any good way to confidently separate ETFs from Stocks?

etfs
etfs806×119 21 KB

QuantRocket 1.5.0 Release Notes
#2

Thanks for reporting.

The IB API doesn't distinguish ETFs from stocks (they're all STK), so QuantRocket classifies ETFs in 2 ways. The primary way is that QuantRocket downloads the symbol lists from the IB website, where they are grouped into stocks vs ETFs. Additionally some European ETFs have a TradingClass of "ETF", so QuantRocket checks that as well.

Unfortunately it looks like the IB website lists some LSE ETFs under "stocks", even though there's a separate heading for ETFs.

There are several ways forward.

  1. If the IB website also lists the ETFs under the "ETF" section, which it probably does, then the simplest solution would be to tell QuantRocket to collect ETFs by passing --sec-types 'ETF'. This will allow QuantRocket to see and store that they are ETFs. (This will work even if you later collect the stock listings again - once QuantRocket sees it's an ETF, it will always be an ETF.)
  2. Another option is to manually curate your master file to include/exclude ETFs as desired and create a universe that fits your goals and use the universe rather than relying on the Etf field.

Finally, I think it would be good for the software to automatically load a list of known ETFs so that it doesn't get tripped up if IB lists ETFs under "stocks" on their website.

#3

I followed suggestion (1) which seems to me most "automatizable"
it indeed cleand up quite a lot of securities (from 40 etfs eto 1.5k).
however I still see some ETFs in there categorized as stocks (ref to the screen-shot).

One further step I thought would help doing this without manual intervetion was to look at the securities without "sector". But the question I have is: are all the securities without sector ETFs? or we would just have some stocks where IB has not set their sector?

etf_1
etf_11194×88 19 KB

#4

I don't think I would trust that the absence of sector means it's an ETF. Hard to prove that no stock will ever be missing a sector.

If you can send me the list of known ETFs that aren't categorized as such, I think QuantRocket should automatically load those in the future. You can email the CSV to brian at quantrocket dot com.

#5

ETF identification is significantly improved in version 1.5.0: