Brian,
Is there a Notebook that walks through joining Sharadar's ETF and Stock databases into one? A few strategies use ETF/Stock allocations at the same time.
Thank you,
Bruno
Hi Brian, are you able to help me with this issue?
Thank you,
Bruno
There are probably many ways to go about this and it depends on the specifics of what you’re trying to do. For example, Moonshot and get_prices accept multiple databases. QuantRocket is very flexible, but ultimately you have to spend time with the docs and determine the best way forward for your use case. Because each use case is different, there is often not going to be an exact example of what you're trying to do, but the raw pieces are there.
Hi Brian, thanks for getting back to me. I'm using Zipline and my strategy trades equities but hedges with bond or index ETFs. I tried creating a stock database and an ETF database separately, and then building a bundle from both those databases but that didn't work. This is what I tried:
from quantrocket_trading_calendars import get_calendar
from quantrocket.zipline import create_bundle_from_db
create_bundle_from_db("sharadar-us-stk-etf", from_db=["sharadar-us-stk-1d","sharadar-us-etf-1d"], calendar="NYSE", universes='all-usd', start_date="1998-1-1", exclude_universes=["nyse-adrs","nyse-reits"])
It would be greatly appreciated if you could help me find an actual solution as I've been trying for days with no success.
This isn't natively supported, but I think the easiest approach would be to copy the ETF database into the STK database and then ingest the STK database into Zipline. Combining the databases could be done like this:
import glob
import subprocess
def combine_sharadar_dbs(stk_db, etf_db):
shards = glob.glob(f"/var/lib/quantrocket/quantrocket.v2.history.{stk_db}.sqlite/year/*.sqlite")
shards.sort()
for stk_shard in shards:
etf_shard = stk_shard.replace(stk_db, etf_db)
print(f"Copying {etf_shard} into {stk_shard}")
queries = f"""
ATTACH DATABASE '{etf_shard}' as etf;
INSERT OR IGNORE INTO Price SELECT * FROM etf.Price;
"""
subprocess.check_output(
["sqlite3", stk_shard],
input=bytes(queries.encode("utf-8")))
combine_sharadar_dbs("sharadar-us-stk-1d", "sharadar-us-etf-1d")
This would need to be run again after re-collecting the data.
(Note: this is intended as a tip. This approach should work but doesn't come with the same kind of verification and support as a native feature would have.)
Hi Brian, I appreciate the help.
When ingesting the bundle I got the error below which is the same error I was getting before. Do you know what it means?
2021-08-30 18:27:47 quantrocket.zipline: INFO [sharadar-us-stk-1d-bundle] Ingesting sharadar-us-stk-1d-bundle bundle
2021-08-30 18:37:21 quantrocket.zipline: ERROR Traceback (most recent call last):
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "sym://qrocket_log_py", line 34, in wrapped
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "sym://qrocket_qrzipline_bundles_ingest_py", line 77, in mule_ingest_bundle
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "sym://qrocket_qrzipline_bundles_ingest_py", line 116, in _call_ingest_func
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "sym://qrocket_qrzipline_bundles_base_py", line 142, in ingest
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "sym://qrocket_qrzipline_bundles_fromdb_base_py", line 256, in _write_assets
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/zipline/assets/asset_writer.py", line 679, in write
2021-08-30 18:37:21 quantrocket.zipline: ERROR self._real_write(
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/zipline/assets/asset_writer.py", line 390, in _real_write
2021-08-30 18:37:21 quantrocket.zipline: ERROR self._write_df_to_table(
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/zipline/assets/asset_writer.py", line 694, in _write_df_to_table
2021-08-30 18:37:21 quantrocket.zipline: ERROR df.to_sql(
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/pandas/core/generic.py", line 2778, in to_sql
2021-08-30 18:37:21 quantrocket.zipline: ERROR sql.to_sql(
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/pandas/io/sql.py", line 590, in to_sql
2021-08-30 18:37:21 quantrocket.zipline: ERROR pandas_sql.to_sql(
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/pandas/io/sql.py", line 1844, in to_sql
2021-08-30 18:37:21 quantrocket.zipline: ERROR table.insert(chunksize, method)
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/pandas/io/sql.py", line 831, in insert
2021-08-30 18:37:21 quantrocket.zipline: ERROR exec_insert(conn, keys, chunk_iter)
2021-08-30 18:37:21 quantrocket.zipline: ERROR File "/opt/conda/lib/python3.8/site-packages/pandas/io/sql.py", line 1567, in _execute_insert
2021-08-30 18:37:21 quantrocket.zipline: ERROR conn.executemany(self.insert_statement(num_rows=1), data_list)
2021-08-30 18:37:21 quantrocket.zipline: ERROR sqlite3.IntegrityError: NOT NULL constraint failed: exchanges.country_code
2021-08-30 18:37:21 quantrocket.zipline: ERROR
The Sharadar ETF database has indexes which are not traded on an exchange, so the exchange country code is missing. You need to create a universe without these.
Thank you Peter, that fixed the problem. Brian should have you on the payroll =)