SQL to Mongo transition

[alexveden]

1. Check data validity

   • Compare futures and options prices
   • Compare EXO and SmartEXO series

2. Check data online updates

3. Run MongoDB code in parallel

4. Replace old SQL code with new version

[alexveden]

My investigation of EXO prices showed:

1. Prices of futures and options are updating well, this is good.

2. Contfut series are pretty close, but not identical:

3. But the options EXOs are completely different! Here are CallSpread graphs:

@spickering-git probably we have problems with options calculations in Span data import scripts

[alexveden]

This is comparison notebook: https://10.0.1.2:8888/notebooks/development/new_mongo_sanity_checks/EXO%20Quotes%20comparison.ipynb

[spickering-git]

reloading all data from 2014 forward, found the error in iv calc.

how can i track the holdings in the 2 different scenarios, how about just the ZC callspread

[alexveden]

@spickering-git I've found the problem in my code, this was Risk-free rate calculation, missed division by 100.0.

After the fix all options prices prior 2014 looks exact.

Lets wait till the data from 2014 reloaded, and rerun EXO calculations once again.

[alexveden]

Lets wait till the data from 2014 reloaded, and rerun EXO calculations once again.

I've checked, and confirm that data after 2014 uploaded well, and I'm running EXO batch build to make final comparison.

[alexveden]

I've found and fixed a bug with rollover regimes for: ZC, ZW, ZS, CC, LBS and GC. The ContFut EXOs look close:

SmartEXO quotes built on new quotes look quite well. Except several instruments:

1. NG - all NG EXOs have these spikes in 2016-17

2. The reason of difference in changed rollover regime

The main problem that alphas probably will be changed too, so this could affect overall performance and probably production campaigns. I'm running alpha_rebalancer script to find out what will happen with alphas.

[nikolas-joyce]

I am only using the bi directional SmartEXO plus the put and call spreads in production. If those have not changed we might be ok.

Sent from my iPhone

On Apr 14, 2017, at 3:35 AM, alexveden notifications@github.com<mailto:notifications@github.com> wrote:

I've found and fixed a bug with rollover regimes for: ZC, ZW, ZS, CC, LBS and GC. The ContFut EXOs look close: [image]https://cloud.githubusercontent.com/assets/18488560/25041109/b0db7670-211e-11e7-894c-8dc61ca7ce8e.png

[image]https://cloud.githubusercontent.com/assets/18488560/25041115/bc96a746-211e-11e7-99b3-21f277afcb07.png

[image]https://cloud.githubusercontent.com/assets/18488560/25041119/c942d23a-211e-11e7-9d63-c681eeb3a2c2.png

SmartEXO quotes built on new quotes look quite well. Except several instruments:

1. NG - all NG EXOs have these spikes in 2016-17 [image]https://cloud.githubusercontent.com/assets/18488560/25041062/5014e02e-211e-11e7-9ea8-fad31ae41167.png

2. The reason of difference in changed rollover regime [image]https://cloud.githubusercontent.com/assets/18488560/25041143/f02f4e82-211e-11e7-9be9-08bdbd73f23d.png

[image]https://cloud.githubusercontent.com/assets/18488560/25041152/0b42de00-211f-11e7-8523-1c4fb3b14163.png

The main problem that alphas probably will be changed too, so this could affect overall performance and probably production campaigns. I'm running alpha_rebalancer script to find out what will happen with alphas.

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/trendmanagement/tmqrexo_alexveden/issues/157#issuecomment-294133275, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ARobLFlQyiqhAA0Gp_ybndZAIwCciwUvks5rv0vJgaJpZM4M0Gag.

[alexveden]

I've created notebook for campaigns results comparison: https://10.0.1.2:8888/notebooks/tools/All_Production_Campaigns_Totals-NEWMONGO.ipynb

New alphas performs little bit worse:

I'm rerunning EXO build and alpha rebalancer once again.

Finally we have following issues:

1. Minor differences in options prices, for some assets like ES, CL
2. Major problem with NG, which has huge spikes in SmartEXOs
3. Changes in rollover regimes for ZC, ZW, ZS, CC, LBS and GC.

And as a consequence we have difference in campaign equity.

[nikolas-joyce]

Since NG is not currently in production I could do a rebuild on that product.

Sent from my iPhone

On Apr 14, 2017, at 6:40 AM, alexveden notifications@github.com<mailto:notifications@github.com> wrote:

I've created notebook for campaigns results comparison: https://10.0.1.2:8888/notebooks/tools/All_Production_Campaigns_Totals-NEWMONGO.ipynb

New alphas performs little bit worse: [image]https://cloud.githubusercontent.com/assets/18488560/25044622/a765eca0-2138-11e7-9c06-f668eada0437.png

I'm rerunning EXO build and alpha rebalancer once again.

Finally we have following issues:

1. Minor differences in options prices, for some assets like ES, CL
2. Major problem with NG, which has huge spikes in SmartEXOs
3. Changes in rollover regimes for ZC, ZW, ZS, CC, LBS and GC.

And as a consequence we have difference in campaign equity.

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/trendmanagement/tmqrexo_alexveden/issues/157#issuecomment-294155272, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ARobLNSBLcB2qlAHHnwrrDTrAduM9Efpks5rv3dQgaJpZM4M0Gag.

[spickering-git]

How can i look at the NG_ContFut using tmldb_v2.

I will do some investigating into NG option prices from span. I am guessing in 2016 prices in span change by a factor of 10 giving the current results.

[alexveden]

You can try to use one of the notebooks in https://10.0.1.2:8888/notebooks/development/new_mongo_sanity_checks/ or manually request new mongo 'exo_data' there will be 'transactions' field it contains transactions.

[nikolas-joyce]

Alex, I have looked at the discrepancies and I think I could rerun/ tweak the few which need work but my bigger question is how we integrate the old and the new versions of the alphas into the campaigns so that the reporting is smooth.

I was thinking we could add a field to the campaign builder where I specify the dates to use and retire an alpha after the quantity field. That way if I make some new alphas to work with the discrepancies between mongo and sql we would have a record of when alphas were engaged and others retired. Is this too vague or do you have some alternative ideas? Thx

Sent from my iPhone

On Apr 14, 2017, at 10:05 AM, alexveden notifications@github.com<mailto:notifications@github.com> wrote:

You can try to use one of the notebooks in https://10.0.1.2:8888/notebooks/development/new_mongo_sanity_checks/ or manually request new mongo 'exo_data' there will be 'transactions' field it contains transactions.

— You are receiving this because you commented. Reply to this email directly, view it on GitHubhttps://github.com/trendmanagement/tmqrexo_alexveden/issues/157#issuecomment-294192396, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ARobLKd_FV248Qv1kOcaiYVdfcwYbhRrks5rv6degaJpZM4M0Gag.

[spickering-git]

@alexveden So was most of the discrepancy from the bug in the rollover regimes? I don't see any changes to the rollover_helper.py in the code committed to github in the development branch. Is the model still running from SQL?

[alexveden]

So was most of the discrepancy from the bug in the rollover regimes?

No, the discrepancy was there even before rollover regime fix.

Is the model still running from SQL?

Yes, we are using SQL. I was concerned about transition, because it might entirely change reports of campaigns, probably this is not good idea.

Probably I need to implement Nik's idea with alphas replacement, which allow us gracefully replace old alphas with new versions without affecting numbers in reports.

[alexveden]

I'm digging to find the source of the problem. I've decided to compare rollover regimes, in rollover_helper, I've applied to following methodology:

1. Reverted to the old rollover_helper code version (i.e. both SQL and New Mongo uses the same rollover code)
2. I want to compare the futures and options at the dates when old ContFut has different transactions compared to ContFut EXO in new Mongo DB.
3. I'm going to compare selected future, future, price, ATM option strike and price.

CL I've rebuild CL_ContFut in the old SQL EXO database, and it converges with new DB. Seems that the problem was in data discrepancy at the moment when old CL_ContFut was built.

All CL picked contracts at rollover date are matched between old and new version. All prices and strikes are equal. Only difference in contracts names, but this issue isn't playing any role.

[alexveden]

NG Has problems with IV.

[alexveden]

ZN Looks good

[alexveden]

ZC Also OK

ZS Matched

ZW Good

I would say that the discrepancies between old and new DB (using the same rollover helper regimes), could be result of data holes or different rollover helper rules at the moment of building these EXOs. Also I'm sure that we will get the exact results (except NG) if we wipe all EXOs in old DB and rerun the build.

Anyway, we have to fix rollover bugs to reflect valid rollovers and we will definitely get different EXO series. I would say the problem with data validity is cleared, and I'm going to implement old campaign retirement mechanism to make process run more smoothly.

[alexveden]

I've finished Mongo transition process.

Everything is fine, except some discrepancies in production campaigns:

Comparing https://10.0.1.2:8888/notebooks/tools/All_Production_Campaign_Settlements-Email_template.ipynb to the https://files.slack.com/files-pri/T0484J7T7-F513W4SKE/download/all_production_campaign_2017-04-18.zip

ES, CL, ZC - are exactly matched.

But ZN holdings and settlement changes are different

6E_Bidirectional_V3 - doesn't have any active alphas, because all of them are skipped (not retired and not kept)

Note

The main caveat about transition that all DataSourceSQL() initiation will raise NotImplementedError with message like this:

NotImplementedError: Please switch to DatasourceMongo

All you need to do is to replace old datasource with new one, like that:

futures_limit = 4 options_limit = 20

~#datasource = DataSourceSQL(SQL_HOST, SQL_USER, SQL_PASS, assetindex, futures_limit, options_limit, storage)~ datasource = DataSourceMongo(MONGO_CONNSTR, MONGO_EXO_DB, assetindex, futures_limit, options_limit, storage)

Note: futures_limit and options_limit variable might not be initiated before, you can just add them or replace by constant numbers in DataSourceMongo(MONGO_CONNSTR, MONGO_EXO_DB, assetindex, ~futures_limit~ 4, ~options_limit~ 20, storage)

P.S. Also I've changed WEB UI and public web API to new Mongo database