Security master download doesn't get all securities in database

Technical Help
#1

After banging my head against the wall for a few hours, I realized that that the "missing" stocks I was looking for were actually in my Security database, but were not being downloaded with the following security master download command. I would expect all available securities to be downloaded when no filters are given. I also don't see any defaults documented that would explain this behavior.

quantrocket master get -o full_security_master.csv

I have 21,041 securities in my quantrocket.v2.master.main.sqlite database, but only 20,875 are downloaded. I noticed this issue when I couldn't find a specific symbol in my security master download, but was able to query it directly from the security master database.

Oddly enough, I found that if I specify the vendor (alpaca) in which the security (FIBBG001J2N4H0) belongs, then I get this security in the master download. However, what I want is a full security master with the total unique combination from both vendors (alpaca and usstock). It's almost as if it drops some securities if they're not available from both vendors when you don't specify a vendor?

Any insight is much appreciated; thanks!

P.S. Is there anyway to log the actual SQL queries that are being sent to the sqlite databases? This would help figure out what's going on in these situations.

#2

That’s not the expected result and not a result I can reproduce. These numbers should generally match:

$ quantrocket master get | tail -n+2 | wc -l
130847
$ sqlite3 /var/lib/quantrocket/quantrocket.v2.master.main.sqlite 'SELECT COUNT(*) FROM (SELECT Sid FROM SecurityIBKR UNION SELECT Sid FROM SecurityAlpaca UNION SELECT Sid FROM SecurityUSStock UNION SELECT Sid FROM SecurityEDI UNION SELECT Sid FROM SecuritySharadar)'
130847
#3

So I do get the same number when I run both the commands you provided as shown below:

$ quantrocket master get | tail -n+2 | wc -l
20874
$ sqlite3 /var/lib/quantrocket/quantrocket.v2.master.main.sqlite 'SELECT COUNT(*) FROM (SELECT Sid FROM SecurityIBKR UNION SELECT Sid FROM SecurityAlpaca UNION SELECT Sid FROM SecurityUSStock UNION SELECT Sid FROM SecurityEDI UNION SELECT Sid FROM SecuritySharadar)'
20874

However, I get a larger number if I query the main Security table as shown below:

$ sqlite3 /var/lib/quantrocket/quantrocket.v2.master.main.sqlite 'SELECT COUNT(DISTINCT Sid) FROM Security'
21041

I seem to be losing 167 securities somewhere as shown in the below query:

SELECT COUNT(*) FROM Security WHERE Sid NOT IN (SELECT Sid FROM SecurityIBKR UNION SELECT Sid FROM SecurityAlpaca UNION SELECT Sid FROM SecurityUSStock)
167

I only included Alpaca and USStock because nothing is in the other tables. I went ahead and dumped the results to a csv (see here).

Here's a few of the missing Sids:

QL000000000194
QL000000000211
QL000000000235
QL000000000379
QL000000000504
QL000000000891
QL000000000911

Note that I only collected Alpaca, USStocks, and Figi by following the usage guide directly; nothing else. I then collected the daily history for USStock and then the Zipline minute bundle for USStock.

Any help is appreciated!

USStock Missing Securities?
#4

Nothing is missing, it’s just an artifact of how QuantRocket maps and combines the data. Database tables are not part of the public API so I can’t really take the time to explain the inner workings. You’re looking at internal data structures and they don’t match your expectations but it’s your expectation that is off, rather than the tables. You are welcome to poke around in undocumented internals but it's not really feasible to offer support related to that.

#5

Lol thanks for the support.