QuantRocket

Handling of same symbol but change in conid


#1

Does QuantRocket handle a situation where the symbol stays the same but the underlying conid changes?

TVIX recently reverse-split and the conid changed (from 320851107 to 393728477), but the symbol stays the same.

The database with the old conid no longer picks up the history (no data since the split on Dec 2), and I don’t see in the guide a way to handle this situation (unlike symbol change).

Should I just create a new database with the new conid, which is possible but then it messes up everything that tracks based on conid (e.g. blotter positions), or
Is there a way to “merge” the two conid?


#2

For data collection, if your database config is tied to a universe you can simply append the new conid to the universe and collect data as normal in the existing database. If your database config is tied to specific conids, you can specify the conids (including the new TVIX one) each time you collect data using the conids parameter, and keep it all in one database. Or you can create a new database and if you need to combine the data from both databases, just query both (get_prices and Moonshot DB accept a list of databases).

Closed historical positions should be fine remaining under the old conid, with new positions going under the new conid. If you have an open position in TVIX under the old conid, that’s trickier. You may need to update the blotter database records to reflect the new conid. Let me know and I can advise you.

In version 2 of QuantRocket (a few months away), security IDs will be based on Bloomberg composite FIGIs (i.e. country-level FIGIs), rather than conids. This will be better because the composite FIGI doesn’t change in a case like this, whereas most IDs do (the IB conid, the ISIN, and the non-composite FIGI all change).


#3

Hi Brian, would you please let me know how to update the blotter database records?


#4

First, please make a backup of your executions database in case you need to revert:

cp /var/lib/quantrocket/quantrocket.blotter.executions.sqlite /var/lib/quantrocket/quantrocket.blotter.executions.sqlite.BAK

Update the old conid to the new conid, multiplying the old price by 10 and dividing the old quantity by 10. Do this in the Executions table (so the PNL for your open TVIX position will be accurate when you close the position) and in the LatestPosition table (so Moonshot will know how many shares you have now that there was a split):

sqlite3 /var/lib/quantrocket/quantrocket.blotter.executions.sqlite 'UPDATE Execution SET ConId = 393728477, Quantity = Quantity / 10, Price = Price * 10, AvgPrice = AvgPrice * 10  WHERE ConId = 320851107'
sqlite3 /var/lib/quantrocket/quantrocket.blotter.executions.sqlite 'UPDATE LatestPosition SET ConId = 393728477, Quantity = Quantity / 10 WHERE ConId = 320851107'

I think that should be all you need to do.