Break between db and broker #836

TobiasAntiGravity · 2022-11-14T10:16:01Z

TobiasAntiGravity
Nov 14, 2022

Got a break message on the reconcile report, that is causing me some headache. Posting here in the hopes of getting some pointers / input;

I have two problems;

Do not understand why the contract date is changing on a contract with a position on
not sure how to fix.

but first, here is my issue;

My reconcile report issue

********************************************************************************
         Reconcile report report produced on 2022-11-11 23:36:19.380803         
********************************************************************************

 Breaks Broker vs Contract [MXP/20221200, EURCHF/20221200, LEANHOG/20230200, EURCHF/20221219, MXP/20221219, LEANHOG/20230214]

===============================================================
                        Positions in DB                        
===============================================================

   instrument_code contract_date          expiry_date  position
6           EURCHF      20221200  2022-12-01 00:00:00      -1.0
10         LEANHOG      20230200  2023-02-01 00:00:00       1.0
0              MXP      20221200  2022-12-01 00:00:00       1.0

===============================================================
                       Positions broker                        
===============================================================

   instrument_code contract_date          expiry_date  position
4           EURCHF      20221219  2022-12-19 00:00:00      -1.0
11         LEANHOG      20230214  2023-02-14 00:00:00       1.0
5              MXP      20221219  2022-12-19 00:00:00       1.0

(the snippet is reduced to the three contracts where break occurs)

My reading of the problem is that the contract date in the db is inconsistent with the contract date from IB.

1) Do not understand why the contract date is changing on a contract with a position on

My reports are commited to git every day. Looking at the reconcile report generated the day before - everything is fine;

********************************************************************************
         Reconcile report report produced on 2022-11-10 23:39:19.713369         
********************************************************************************

 Breaks Broker vs Contract []

===============================================================
                        Positions in DB                        
===============================================================

   instrument_code contract_date          expiry_date  position
9           EURCHF      20221219  2022-12-19 00:00:00      -1.0
13         LEANHOG      20230214  2023-02-14 00:00:00       1.0
0              MXP      20221219  2022-12-19 00:00:00       1.0

===============================================================
                       Positions broker                        
===============================================================

   instrument_code contract_date          expiry_date  position
12          EURCHF      20221219  2022-12-19 00:00:00      -1.0
1          LEANHOG      20230214  2023-02-14 00:00:00       1.0
13             MXP      20221219  2022-12-19 00:00:00       1.0

This has never happened before. I did merge from pysystemtrade on the 9th of november. Other than that nothing has changed in the production code. Only reason I can think of.

2) not sure how to fix.

Looking at the in the futures_contract_positions collection I see that contract_date is not stored. So I assume that contractid is a foreign key relationship to the futures_contracts collection where contract date is indeed stored.

Not sure this assumption is correct however - because looking into the futures_contracts collection for the MXP contract in question I find;

{'_id': ObjectId('62d4032814b2546baccbd26f'),
 'contract_date_dict': {'contract_list': [{'contract_date': '202212',
                                           'expiry_date': [2022, 12, 19]}]},
 'contract_key': 'MXP/20221200',
 'contract_params': {'sampling': True},
 'instrument_dict': {'instrument_code': 'MXP'}}

which is consitent with what is stated in the broker section of the reconcile report, not the db section.

I was hoping to correct the contract date in the db, but now I am not sure how to correct this issue.

Any input would be greatly appreciated!!

Answered by robcarver17

Nov 14, 2022

Perhaps confusingly, the dates in 'positions in DB' are actually coming from IB. Follow the logic:

def get_my_positions(data):
    data_broker = dataBroker(data)
    my_positions = data_broker.get_db_contract_positions_with_IB_expiries().as_pd_df()
    my_positions = my_positions.sort_values("instrument_code")

    return my_positions

This is done to avoid the edge case when an expiry date hasn't been updated in the database.

(We can have a further discusison about how likely this is, and whether the unexpected nature of this behaviour is worth changing it)

So what has probably happened is that IB has provided a different contract expiry date for the same contract within a minute or so.…

View full answer

robcarver17 · 2022-11-14T10:27:20Z

robcarver17
Nov 14, 2022
Maintainer

Perhaps confusingly, the dates in 'positions in DB' are actually coming from IB. Follow the logic:

def get_my_positions(data):
    data_broker = dataBroker(data)
    my_positions = data_broker.get_db_contract_positions_with_IB_expiries().as_pd_df()
    my_positions = my_positions.sort_values("instrument_code")

    return my_positions

This is done to avoid the edge case when an expiry date hasn't been updated in the database.

(We can have a further discusison about how likely this is, and whether the unexpected nature of this behaviour is worth changing it)

So what has probably happened is that IB has provided a different contract expiry date for the same contract within a minute or so. I've actually seen this behaviour myself this morning with a different instrument.

If you rerun the report it should hopefully work.

3 replies

TobiasAntiGravity Nov 14, 2022
Author

Thank you for the quick reply as always Mr. Carver!

I re-ran the report now and got the same result. But if you had the same issue this morning, it might still be an ongoing problem..

I will await the next report generation to investigate further (hopefully it will be solved by itself). Awaiting is my only choice right now, because there is a strike in my two year old's kindergarden (for the fourth week running) and so I am, for the most part, occupied with babysitting.

Thank you again!

TobiasAntiGravity Nov 14, 2022
Author

Just noticed warning messages in the command line when I was about to shut down;

2022-11-14 11:07:05 {'type': 'Interactive-Diagnostics', 'component': 'mongoIbBrokerClientIdData'}  Locked IB client ID 102
Error 200, reqId 49: No security definition has been found for the request, contract: Future(symbol='MXP', lastTradeDateOrContractMonth='202212', multiplier='500000', exchange='GLOBEX')
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData'} [Warning] Reqid 49: 200 No security definition has been found for the request  (MXP/202212)
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData'} [Warning] futuresInstrumentWithIBConfigData(instrument=MXP, ib_data=symbol='MXP', exchange='GLOBEX', currency='', ibMultiplier='500000.0', priceMagnifier=1.00, ignoreWeekly='False, effective_multiplier=500000.00' ) could not resolve contracts: No contracts found matching pattern
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData', 'instrument_code': 'MXP', 'contract_date': '20221200'} [Warning] Contract is missing can't get expiry
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'instrument_code': 'MXP', 'contract_date': '20221200'} [Warning] Contract MXP/20221200 is missing from IB probably expired - need to manually close on DB
Error 200, reqId 55: No security definition has been found for the request, contract: Future(symbol='RF', lastTradeDateOrContractMonth='202212', multiplier='125000', exchange='GLOBEX', currency='CHF')
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData'} [Warning] Reqid 55: 200 No security definition has been found for the request  (RF/202212)
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData'} [Warning] futuresInstrumentWithIBConfigData(instrument=EURCHF, ib_data=symbol='RF', exchange='GLOBEX', currency='CHF', ibMultiplier='125000.0', priceMagnifier=1.00, ignoreWeekly='False, effective_multiplier=125000.00' ) could not resolve contracts: No contracts found matching pattern
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData', 'instrument_code': 'EURCHF', 'contract_date': '20221200'} [Warning] Contract is missing can't get expiry
2022-11-14 11:07:11 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'instrument_code': 'EURCHF', 'contract_date': '20221200'} [Warning] Contract EURCHF/20221200 is missing from IB probably expired - need to manually close on DB
Error 200, reqId 59: No security definition has been found for the request, contract: Future(symbol='HE', lastTradeDateOrContractMonth='202302', multiplier='40000', exchange='GLOBEX')
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData'} [Warning] Reqid 59: 200 No security definition has been found for the request  (HE/202302)
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData'} [Warning] futuresInstrumentWithIBConfigData(instrument=LEANHOG, ib_data=symbol='HE', exchange='GLOBEX', currency='', ibMultiplier='40000.0', priceMagnifier=100.00, ignoreWeekly='False, effective_multiplier=400.00' ) could not resolve contracts: No contracts found matching pattern
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData', 'instrument_code': 'LEANHOG', 'contract_date': '20230200'} [Warning] Contract is missing can't get expiry
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'instrument_code': 'LEANHOG', 'contract_date': '20230200'} [Warning] Contract LEANHOG/20230200 is missing from IB probably expired - need to manually close on DB
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData', 'instrument_code': 'MXP', 'contract_date': '20221200'} [Warning] Contract is missing can't get expiry
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'instrument_code': 'MXP', 'contract_date': '20221200'} [Warning] Contract MXP/20221200 is missing from IB probably expired - need to manually close on DB
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData', 'instrument_code': 'EURCHF', 'contract_date': '20221200'} [Warning] Contract is missing can't get expiry
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'instrument_code': 'EURCHF', 'contract_date': '20221200'} [Warning] Contract EURCHF/20221200 is missing from IB probably expired - need to manually close on DB
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'component': 'ibFuturesContractData', 'instrument_code': 'LEANHOG', 'contract_date': '20230200'} [Warning] Contract is missing can't get expiry
2022-11-14 11:07:12 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 102, 'instrument_code': 'LEANHOG', 'contract_date': '20230200'} [Warning] Contract LEANHOG/20230200 is missing from IB probably expired - need to manually close on DB

Got me thinking about the IB exchange names - looks like that is the issue - I need to update to your last commit.

meldinman Nov 15, 2022

I had a similar issue today with BITCOIN and updating IB config did indeed fix the problem

oldlore · 2022-12-03T03:34:11Z

oldlore
Dec 3, 2022

Strangely, I just got this same error tonight. Have held the SOYBEAN_mini for a couple weeks with no breaks until tonight.

Breaks Broker vs Contract [SOYBEAN_mini/20230300, SOYBEAN_mini/20230314]

The "Position DB", which had been 03-14-2023 all along changed to 03-01-2023, presumably getting that from IB? After reading this thread, I tried running the a reconcile report twice more and got the same result. I checked my IB config and it was on the latest version and the specific SOYBEAN_mini config was ok. I also saw the same sort of error as Tobias did in the reconcile report.

2022-12-02 21:52:53 {'type': 'Interactive-Diagnostics'}  Running report Reconcile report sysproduction.reporting.reconcile_report.reconcile_report console {}
2022-12-02 21:52:54 {'type': 'Interactive-Diagnostics', 'component': 'mongoIbBrokerClientIdData'}  Locked IB client ID 191
Error 200, reqId 3: No security definition has been found for the request, contract: Future(symbol='YK', lastTradeDateOrContractMonth='202303', multiplier='1000', exchange='ECBOT', currency='USD')
2022-12-02 21:53:00 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 191, 'component': 'ibFuturesContractData'} [Warning] Reqid 3: 200 No security definition has been found for the request  (YK/202303)
2022-12-02 21:53:00 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 191, 'component': 'ibFuturesContractData'} [Warning] futuresInstrumentWithIBConfigData(instrument=SOYBEAN_mini, ib_data=symbol='YK', exchange='ECBOT', currency='USD', ibMultiplier='1000.0', priceMagnifier=100.00, ignoreWeekly='False, effective_multiplier=10.00' ) could not resolve contracts: No contracts found matching pattern
2022-12-02 21:53:00 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 191, 'component': 'ibFuturesContractData', 'instrument_code': 'SOYBEAN_mini', 'contract_date': '20230300'} [Warning] Contract is missing can't get expiry
2022-12-02 21:53:00 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 191, 'instrument_code': 'SOYBEAN_mini', 'contract_date': '20230300'} [Warning] Contract SOYBEAN_mini/20230300 is missing from IB probably expired - need to manually close on DB
2022-12-02 21:53:00 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 191, 'component': 'ibFuturesContractData', 'instrument_code': 'SOYBEAN_mini', 'contract_date': '20230300'} [Warning] Contract is missing can't get expiry
2022-12-02 21:53:00 {'type': 'Interactive-Diagnostics', 'broker': 'IB', 'clientid': 191, 'instrument_code': 'SOYBEAN_mini', 'contract_date': '20230300'} [Warning] Contract SOYBEAN_mini/20230300 is missing from IB probably expired - need to manually close on DB

And this is from the IB Gateway API log when running update_sampled_contracts

22:07:22:515 <- 9-8-3-0-YK-FUT-202209-0.0--1000-ECBOT--USD---0---
22:07:22:545 -> ---@4-2-3-200-No security definition has been found for the request-
22:07:22:585 <- 9-8-4-0-YK-FUT-202211-0.0--1000-ECBOT--USD---0---
22:07:22:606 -> ---@4-2-4-200-No security definition has been found for the request-
22:07:22:621 <- 9-8-5-0-YK-FUT-202301-0.0--1000-ECBOT--USD---0---
22:07:22:642 -> ---@4-2-5-200-No security definition has been found for the request-
22:07:22:663 <- 9-8-6-0-YK-FUT-202303-0.0--1000-ECBOT--USD---0---
22:07:22:686 -> ---@4-2-6-200-No security definition has been found for the request-
22:07:22:710 <- 9-8-7-0-YK-FUT-202305-0.0--1000-ECBOT--USD---0---
22:07:22:731 -> ---@4-2-7-200-No security definition has been found for the request-
22:07:22:762 <- 9-8-8-0-YK-FUT-202307-0.0--1000-ECBOT--USD---0---
22:07:22:783 -> ---@4-2-8-200-No security definition has been found for the request-
22:07:22:820 <- 9-8-9-0-YK-FUT-202308-0.0--1000-ECBOT--USD---0---
22:07:22:842 -> ---@4-2-9-200-No security definition has been found for the request-
22:07:22:868 <- 9-8-10-0-YK-FUT-202309-0.0--1000-ECBOT--USD---0---
22:07:22:889 -> ---A4-2-10-200-No security definition has been found for the request-
22:07:22:920 <- 9-8-11-0-YK-FUT-202311-0.0--1000-ECBOT--USD---0---
22:07:22:943 -> ---A4-2-11-200-No security definition has been found for the request-
22:07:22:963 <- 9-8-12-0-YK-FUT-202401-0.0--1000-ECBOT--USD---0---
22:07:22:985 -> ---A4-2-12-200-No security definition has been found for the request-
22:08:57:464 -> ---

I'll rerun the report Saturday morning just to see if it corrects itself. Maybe it's an IB issue. I'll post if it fixes itself.

By the way, what will the system try to do on Monday? Will it try to "correct" the break and buy a March 2023 contract again? If so, what do I need to do to prevent this?

Thanks!

2 replies

tgibson11 Dec 3, 2022

Most likely the exchange name changed from ECBOT to CME. See discussion #811

Update ib_config_futures.csv, or wait for Rob - he'll probably update it by Monday. Should fix itself after that.

The system will not try to make any trades - on the contrary, it will prevent any trades when there is a position discrepancy.

oldlore Dec 3, 2022

You're right! Thanks! For some reason I thought all the switches were done already. I didn't realize there was one last round. Thanks again.

rorymac · 2022-12-03T14:58:11Z

rorymac
Dec 3, 2022

I can confirm, doing the changes to the exchanges fixes the issue.

as an example :
GOLD_micro,MGC,NYMEX,USD,10,1,FALSE
to
GOLD_micro,MGC,COMEX,USD,10,1,FALSE
repairs the error.

0 replies

robcarver17 · 2022-12-04T13:42:15Z

robcarver17
Dec 4, 2022
Maintainer

I've updated the config, although I need to test as wave 4 is a bit vague.

(A good way to test this is to run get all trading hours as that will poll all the instruments)

0 replies

Break between db and broker #836

Uh oh!

Uh oh!

TobiasAntiGravity Nov 14, 2022

My reconcile report issue

1) Do not understand why the contract date is changing on a contract with a position on

2) not sure how to fix.

Replies: 5 comments · 5 replies

Uh oh!

robcarver17 Nov 14, 2022 Maintainer

Uh oh!

TobiasAntiGravity Nov 14, 2022 Author

Uh oh!

Uh oh!

TobiasAntiGravity Nov 14, 2022 Author

Uh oh!

meldinman Nov 15, 2022

Uh oh!

Uh oh!

oldlore Dec 3, 2022

Uh oh!

tgibson11 Dec 3, 2022

Uh oh!

oldlore Dec 3, 2022

Uh oh!

rorymac Dec 3, 2022

Uh oh!

robcarver17 Dec 4, 2022 Maintainer

TobiasAntiGravity
Nov 14, 2022

Replies: 5 comments 5 replies

robcarver17
Nov 14, 2022
Maintainer

TobiasAntiGravity Nov 14, 2022
Author

TobiasAntiGravity Nov 14, 2022
Author

oldlore
Dec 3, 2022

rorymac
Dec 3, 2022

robcarver17
Dec 4, 2022
Maintainer