Alpaca Aggregate DB not collecting all in Universe

I have an alpaca aggregate DB I am using for intraday trading. When I initially setup a database, I used a universe containing 39 stocks. I have noticed that when its actually collecting data, it only collects 27 of those stocks.

Showing number of tickers

universe used to configured DB

return from get_active_collections()

Any ideas what is causing this?

Two things to try/confirm:

  1. Show the db config for the tick database underlying your aggregate database and make sure it points to the 40-volitile-stocks universe.
  2. Are any stocks in the 40-volitile-stocks universe marked as delisted? The realtime service will query the universe with exclude_delisted=True.

Thank you for those tips!

I validated the DB had the correct universe setup:

Is there an easy way to find and remove delisted stocks in a universe?

Thanks in advance!

You can query the universe and look at the Delisted field, or query with get_securities(..., exclude_delisted=True) to exclude delisted. If you want to re-create the universe without delisted stocks, you can re-recreate the universe with the desired stocks, using create_universe(..., replace=True).

I was able to find out a few of the stocks listed in the universe weren't in the Master Securities DB.

In case anyone finds themselves in a position of needing to know what is in a universe, you can look at the universe table itself using sqlite3.

Run this in a terminal:

cd /var/lib/quantrocket
sqlite3 quantrocket.v2.master.main.sqlite "SELECT * FROM Universe"

This will list out all the universes with their SIDs.

From there, you can create a notebook and put those SIDs in a dict and compare them against your master security DB to see if all your SIDs are present.

from quantrocket.master import get_securities
securities = get_securities(exchanges=["XNYS"])
filteredFrame = securities[securities.index.isin(sids)]
values_not_in_df = [val for val in sids if val not in securities.index]

I copied and pasted the terminal output from SQLite3 into vscode and used it to lay out the sids in dict format for python.

Happy Trading!

To see what securities are in your universe, you can just use get_securities:

from quantrocket.master import get_securities
securities = get_securities(universes='my-universe')