Alpaca Aggregate DB not collecting all in Universe

Technical Help
#1

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
image

universe used to configured DB
image

return from get_active_collections()
image

Any ideas what is causing this?

#2

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.
#3

Thank you for those tips!

I validated the DB had the correct universe setup:

image
image840×224 4.21 KB

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

Thanks in advance!

#4

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).

#5

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"])
sids = ['YOURSIDSHERE',
'YOURSIDSHERE',
'YOURSIDSHERE']
#display(securities)
filteredFrame = securities[securities.index.isin(sids)]
values_not_in_df = [val for val in sids if val not in securities.index]
display(values_not_in_df)
display(filteredFrame)

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!

#6

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')