P&L report not showing all positions

[TobiasAntiGravity]

Hi

Hope you all are well!!

I noticed my P&L report did not list all of the positions that exist in the system. Compared to the Reconcile report there are 5 instruments that is both with the broker and in the db, but that is not listed in the P&L report.

The thought about missing / delayed data crossed my mind. All but one of the missing instruments figure in the Status Report under the Delayed adjusted / FX prices section. So I guess that isn't the explanaition either

Overview of instruments missing from the P&L:

None of the missing instruments are new - VNKI was on the Trade Report but the position was only further reduced, not opened.

Anyone know of the reason why instruments are included in the P&L, please let me now!

[robcarver17]

Could be many reasons? Have you tried debugging to find out?

(Apart from IRON these are all Asian markets... could be useful to know?)

Here is what I would do:

Looking at sysproduction/reporting/pandl_report.py it calls quite a few functions in the reporting API:

list_of_func_names = [
        "body_text_total_capital_pandl",
        "table_pandl_for_instruments_across_strategies",
        "body_text_total_pandl_for_futures",
        "body_text_residual_pandl",
        "table_strategy_pandl_and_residual",
        "table_sector_pandl",
    ]

It looks like this bad boy is the one we want: table_pandl_for_instruments_across_strategies

In sysproduction.reporting.api:

    def table_pandl_for_instruments_across_strategies(self):

        pandl_for_instruments_across_strategies_df = (
            self.pandl_for_instruments_across_strategies()
        )
        pandl_for_instruments_across_strategies_df = (
            pandl_for_instruments_across_strategies_df.round(1)
        )

        return table(
            "P&L by instrument for all strategies",
            pandl_for_instruments_across_strategies_df,
        )

So we actually get the data from pandl_for_instruments_across_strategies():

    def pandl_for_instruments_across_strategies(self) -> pd.DataFrame:
        pandl_for_instruments_across_strategies = getattr(
            self, "_pandl_for_instruments_across_strategies", missing_data
        )
        if pandl_for_instruments_across_strategies is missing_data:
            pandl_for_instruments_across_strategies = (
                self._pandl_for_instruments_across_strategies
            ) = self._get_pandl_for_instruments_across_strategies()

        return pandl_for_instruments_across_strategies

This is just a cacheing function here is where it comes from:

    def _get_pandl_for_instruments_across_strategies(self) -> pd.DataFrame:

        pandl_for_instruments_across_strategies_df = (
            self.pandl_calculator.get_ranked_list_of_pandl_by_instrument_all_strategies_in_date_range()
        )

        return pandl_for_instruments_across_strategies_df

OK how do we get a pandl_calculator?

    @property
    def pandl_calculator(self) -> pandlCalculateAndStore:
        pandl_calculator = getattr(self, "_pandl_calculator", missing_data)
        if pandl_calculator is missing_data:
            pandl_calculator = self._pandl_calculator = self._get_pandl_calculator()

        return pandl_calculator

    def _get_pandl_calculator(self) -> pandlCalculateAndStore:
        return pandlCalculateAndStore(
            self.data, start_date=self.start_date, end_date=self.end_date
        )

We need to go to another file now, sysproduction/reporting/data/pandl.py

Note I'm doing all this in pycharm by just doing right click / go to / declaration. I'm sure you IDE has a similar function.

This is the method we wanted (remember from earlier self.pandl_calculator.get_ranked_list_of_pandl_by_instrument_all_strategies_in_date_range()):

    def get_ranked_list_of_pandl_by_instrument_all_strategies_in_date_range(self):
        list_pandl = (
            self.get_period_perc_pandl_for_all_instruments_all_strategies_in_date_range()
        )
        list_pandl = [pandl for pandl in list_pandl if pandl.pandl != 0]
        list_pandl.sort(key=lambda r: r.pandl)

        pandl_as_df = list_pandl_to_df(list_pandl)

        return pandl_as_df

Notice that zero p&l are filtered out: this is important for later.

Stepping into get_period_perc_pandl_for_all_instruments_all_strategies_in_date_range():

    def get_period_perc_pandl_for_all_instruments_all_strategies_in_date_range(self):
        diag_positions = diagPositions(self.data)
        instrument_list = diag_positions.get_list_of_instruments_with_any_position()

        list_pandl = [
            PandL(
                instrument_code,
                self.get_period_perc_pandl_for_instrument_all_strategies_in_date_range(
                    instrument_code
                ),
            )
            for instrument_code in instrument_list
        ]

        return list_pandl

To delve deeper would involve getting into the weeds of my mongo API, and I'm hoping any problem isn't there.

We have enough information to start doing some testing.

We can perform the same exercise (going from top to bottom), but in code. At a python prompt:

>>> from sysproduction.reporting.api import *
>>> api = reportingApi(dataBlob(), calendar_days_back=1)
>>> from sysproduction.reporting.data.pandl import *
>>> self = api.pandl_calculator ## I do this so I can just copy and paste code from sysproduction.data.pandl. All line numbers refer to that file
>>> self.get_ranked_list_of_pandl_by_instrument_all_strategies_in_date_range().sort_values('codes') ## line 89 plus a sort

           codes     pandl
3            AUD -0.066840
4            BRE -0.064874
10          CORN  0.088464
13  CRUDE_W_mini  0.271470
6            DOW -0.060057
5       EU-BASIC -0.061048
11   GAS_US_mini  0.117952
1         GBPEUR -0.140625
9     GOLD_micro  0.023197
8           IRON  0.019575
2            JPY -0.101980
7      NIKKEI400 -0.036133
12       SOYBEAN  0.127782
14        SOYOIL  0.273649
0         YENEUR -0.288296

It is instructive to compare this with my current list of held positions:

       strategy_name instrument_code  position
3   dynamic_TF_carry             BRE         1
5   dynamic_TF_carry            CORN         1
1   dynamic_TF_carry    CRUDE_W_mini         1
10  dynamic_TF_carry        EU-BASIC         1
8   dynamic_TF_carry     GAS_US_mini         2
7   dynamic_TF_carry          GBPEUR        -1
2   dynamic_TF_carry      GOLD_micro         1
11  dynamic_TF_carry            IRON         1
0   dynamic_TF_carry             JPY        -1
6   dynamic_TF_carry            KR10        -1
4   dynamic_TF_carry       NIKKEI400         1
9   dynamic_TF_carry         SOYBEAN         1
12  dynamic_TF_carry          SOYOIL         1

OK so KR10 is missing from the p&l list. An Asian market!. There are also extra items, but they are probably things I've closed since yesterday. Let's step into the code

>>> diag_positions = diagPositions(self.data) ## line 118
>>> instrument_list = diag_positions.get_list_of_instruments_with_any_position() # line 119
>>> instrument_list
['OAT', 'NASDAQ', 'EU-DIV30', 'SILVER', 'R1000', 'BOBL', 'NIFTY', 'IRON', 'DJSTX-SMALL', 'SOYMEAL', 'GAS_US_mini', 'AEX', 'PLAT', 'VIX', 'BTP', 'CNH', 'HEATOIL', 'REDWHEAT', 'DOW', 'CRUDE_W_mini', 'GAS_US', 'EURO600', 'WHEAT', 'FEEDCOW', 'EU-RETAIL', 'SHATZ', 'US2', 'SMI', 'KRWUSD', 'NASDAQ_micro', 'EUR', 'EU-TRAVEL', 'AUSSTIR', 'KOSPI_mini', 'MUMMY', 'BRENT-LAST', 'AUS10', 'RUR', 'CHF', 'US5', 'KR10', 'CAC', 'NZD', 'US20', 'AUS3', 'BRE', 'BUND', 'JPY', 'CORN', 'LIVECOW', 'USIRS5', 'EU-BASIC', 'SOYOIL', 'RUSSELL', 'EU-OIL', 'EU-HEALTH', 'KOSDAQ', 'TOPIX', 'GOLD', 'EUROSTX', 'HEAT_S', 'NIKKEI400', 'US30', 'EU-TECH', 'SP500', 'CRUDE_W', 'BITCOIN', 'MXP', 'FTSETAIWAN', 'MSCISING', 'SP400', 'PALLAD', 'YENEUR', 'DAX', 'SOYBEAN', 'BUXL', 'GASOILINE', 'GOLD_micro', 'ASX', 'FTSECHINAA', 'SP500_micro', 'EU-AUTO', 'EU-UTILS', 'COPPER', 'EU-BANKS', 'LEANHOG', 'US10', 'NIKKEI', 'INR', 'EDOLLAR', 'KR3', 'CAD', 'GBP', 'FTSECHINAH', 'FTSE', 'GBPEUR', 'KOSPI', 'V2X', 'AUD']

So this actually includes all instruments I have ever held a position in.

>>> list_pandl = [   # line 121
...     PandL(
...         instrument_code,
...         self.get_period_perc_pandl_for_instrument_all_strategies_in_date_range(
...             instrument_code
...         ),
...     )
...     for instrument_code in instrument_list
... ]

[PandL(code='OAT', pandl=0.0), PandL(code='NASDAQ', pandl=0.0), PandL(code='EU-DIV30', pandl=0.0), PandL(code='SILVER', pandl=0.0), PandL(code='R1000', pandl=0.0), PandL(code='BOBL', pandl=0.0), PandL(code='NIFTY', pandl=0.0), PandL(code='IRON', pandl=0.019574971372929253), PandL(code='DJSTX-SMALL', pandl=0.0), PandL(code='SOYMEAL', pandl=0.0), PandL(code='GAS_US_mini', pandl=0.11795234725171042), PandL(code='AEX', pandl=0.0), PandL(code='PLAT', pandl=0.0), PandL(code='VIX', pandl=0.0), PandL(code='BTP', pandl=0.0), PandL(code='CNH', pandl=0.0), PandL(code='HEATOIL', pandl=0.0), PandL(code='REDWHEAT', pandl=0.0), PandL(code='DOW', pandl=-0.060057403475662505), PandL(code='CRUDE_W_mini', pandl=0.27146951089671173), PandL(code='GAS_US', pandl=0.0), PandL(code='EURO600', pandl=0.0), PandL(code='WHEAT', pandl=0.0), PandL(code='FEEDCOW', pandl=0.0), PandL(code='EU-RETAIL', pandl=0.0), PandL(code='SHATZ', pandl=0.0), PandL(code='US2', pandl=0.0), PandL(code='SMI', pandl=0.0), PandL(code='KRWUSD', pandl=0.0), PandL(code='NASDAQ_micro', pandl=0.0), PandL(code='EUR', pandl=0.0), PandL(code='EU-TRAVEL', pandl=0.0), PandL(code='AUSSTIR', pandl=0.0), PandL(code='KOSPI_mini', pandl=0.0), PandL(code='MUMMY', pandl=0.0), PandL(code='BRENT-LAST', pandl=0.0), PandL(code='AUS10', pandl=0.0), PandL(code='RUR', pandl=0.0), PandL(code='CHF', pandl=0.0), PandL(code='US5', pandl=0.0), PandL(code='KR10', pandl=0.0), PandL(code='CAC', pandl=0.0), PandL(code='NZD', pandl=0.0), PandL(code='US20', pandl=0.0), PandL(code='AUS3', pandl=0.0), PandL(code='BRE', pandl=-0.0648737909884406), PandL(code='BUND', pandl=0.0), PandL(code='JPY', pandl=-0.1019796335613742), PandL(code='CORN', pandl=0.08846426043878274), PandL(code='LIVECOW', pandl=0.0), PandL(code='USIRS5', pandl=0.0), PandL(code='EU-BASIC', pandl=-0.06104805575214352), PandL(code='SOYOIL', pandl=0.2736494456239671), PandL(code='RUSSELL', pandl=0.0), PandL(code='EU-OIL', pandl=0.0), PandL(code='EU-HEALTH', pandl=0.0), PandL(code='KOSDAQ', pandl=0.0), PandL(code='TOPIX', pandl=0.0), PandL(code='GOLD', pandl=0.0), PandL(code='EUROSTX', pandl=0.0), PandL(code='HEAT_S', pandl=0.0), PandL(code='NIKKEI400', pandl=-0.03613312164059797), PandL(code='US30', pandl=0.0), PandL(code='EU-TECH', pandl=0.0), PandL(code='SP500', pandl=0.0), PandL(code='CRUDE_W', pandl=0.0), PandL(code='BITCOIN', pandl=0.0), PandL(code='MXP', pandl=0.0), PandL(code='FTSETAIWAN', pandl=0.0), PandL(code='MSCISING', pandl=0.0), PandL(code='SP400', pandl=0.0), PandL(code='PALLAD', pandl=0.0), PandL(code='YENEUR', pandl=-0.2882961833026434), PandL(code='DAX', pandl=0.0), PandL(code='SOYBEAN', pandl=0.12778170952268617), PandL(code='BUXL', pandl=0.0), PandL(code='GASOILINE', pandl=0.0), PandL(code='GOLD_micro', pandl=0.023197294959502936), PandL(code='ASX', pandl=0.0), PandL(code='FTSECHINAA', pandl=0.0), PandL(code='SP500_micro', pandl=0.0), PandL(code='EU-AUTO', pandl=0.0), PandL(code='EU-UTILS', pandl=0.0), PandL(code='COPPER', pandl=0.0), PandL(code='EU-BANKS', pandl=0.0), PandL(code='LEANHOG', pandl=0.0), PandL(code='US10', pandl=0.0), PandL(code='NIKKEI', pandl=0.0), PandL(code='INR', pandl=0.0), PandL(code='EDOLLAR', pandl=0.0), PandL(code='KR3', pandl=0.0), PandL(code='CAD', pandl=0.0), PandL(code='GBP', pandl=0.0), PandL(code='FTSECHINAH', pandl=0.0), PandL(code='FTSE', pandl=0.0), PandL(code='GBPEUR', pandl=-0.1406249999999984), PandL(code='KOSPI', pandl=0.0), PandL(code='V2X', pandl=0.0), PandL(code='AUD', pandl=-0.0668396634426372)]

Now this list is subsequently filtered to remove zeros (line #89).

PandL(code='KR10', pandl=0.0

So if an instrument will be missing if it has never had a position (unlikely!), the position records are wrong (also unlikely) or the p&l is zero. The cause of the p&l being zero could be because the price hasn't changed, or because we had no position on.

>>> instrument_code="KR10"
>>> pandl_across_contracts = self.pandl_for_instrument_across_contracts(
...     instrument_code
... )
>>> 
>>> pandl_across_contracts
Empty DataFrame
Columns: [20220600]
Index: []
>>> pandl_df_all_data = get_df_of_perc_pandl_series_for_instrument_all_strategies_across_contracts_in_date_range(
...     self.data, instrument_code, self.start_date, self.end_date
... )
>>> pandl_df_all_data
                     20220600
2021-12-23 01:00:00  0.000000
2021-12-23 02:00:00  0.000000
2021-12-23 03:00:00  0.000000
2021-12-23 04:00:00  0.000000
2021-12-23 05:00:00  0.000000
...                       ...
2022-04-26 03:00:00 -0.054924
2022-04-26 04:00:00  0.076894
2022-04-26 05:00:00 -0.010985
2022-04-26 06:00:00 -0.009416
2022-04-26 07:00:00 -0.010985

[416 rows x 1 columns]

OK there is the problem. I'm running this code at 0930 on the 27th April. Since 0930 yesterday, and today, the price hasn't been updated.

To be 100% confident this was the problem I could run a p&l report with a 2 day, not 1 day, window. If that still broke, it could be something more serious. But let's assume it is the problem.

Quick way to check if a price has been updated.

rob@TradingPC2:~/pysystemtrade/sysproduction/linux/scripts$ . interactive_diagnostics 


0: backtest objects
1: View instrument configuration
2: logs, emails, and errors
3: View prices
4: View capital
5: View positions & orders
6: Reports


Your choice? <RETURN for EXIT> 3
30: Individual futures contract prices
31: Multiple prices
32: Adjusted prices
33: FX prices
34: Spreads


Your choice? <RETURN for Back> 30
Instrument code?KR10
Available contract dates ['20131200', '20140300', '20140600', '20140900', '20141200', '20150300', '20150600', '20150900', '20151200', '20160300', '20160600', '20160900', '20161200', '20170300', '20170600', '20170900', '20171200', '20180300', '20180600', '20180900', '20181200', '20190300', '20190600', '20190900', '20191200', '20200300', '20200600', '20200900', '20201200', '20210300', '20210600', '20210900', '20211200', '20220300', '20220600p', '20220900f']
p = currently priced, c=current carry, f= current forward
Contract date? [yyyymm or yyyymmdd] (ignore suffixes)20220600
                       OPEN    HIGH     LOW   FINAL  VOLUME
index                                                      
2021-12-23 01:00:00  124.70  124.70  124.70  124.70       0
.... SNIP....

2022-04-26 03:00:00  114.88  115.23  114.87  115.22    9682
2022-04-26 04:00:00  115.22  115.22  114.59  114.73    8430
2022-04-26 05:00:00  114.73  114.82  114.65  114.80    6911
2022-04-26 06:00:00  114.80  114.89  114.59  114.86   10197
2022-04-26 07:00:00  114.85  114.98  114.77  114.93    6751

I could trawl through log files to see why, but it makes perfect sense to have a delay updating Asian prices, since when my price update ran on the 26th the last price available was 7am. I can check there are no serious issues:

entIdData'}  Released IB client ID 923
rob@TradingPC2:~/pysystemtrade/sysproduction/linux/scripts$ . interactive_manual_check_historical_prices 
/home/rob/.local/lib/python3.8/site-packages/arctic/_util.py:6: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.
  from pandas.util.testing import assert_frame_equal
/home/rob/.local/lib/python3.8/site-packages/arctic/store/_pandas_ndarray_store.py:6: FutureWarning: The Panel class is removed from pandas. Accessing it from the top-level namespace will also be removed in the next version
  from pandas import DataFrame, Series, Panel


sysproduction.interactive_manual_check_historical_prices.interactive_manual_check_historical_prices:
Do a daily update for futures contract prices, using IB historical data

If any 'spikes' are found, run manual checks

:return: Nothing



Arguments:
[]


Instrument code?(Return to EXIT)KR10
2022-04-27 09:42:22 {'type': 'Update-Historical-prices-manually', 'instrument_code': 'KR10', 'contract_date': '20220600', 'component': 'mongoIbBrokerClientIdData'}  Locked IB client ID 923
 Manually checking prices for KR10/20220600 


2022-04-27 09:42:30 {'type': 'Update-Historical-prices-manually', 'component': 'arcticFuturesContractPriceData', 'instrument_code': 'KR10', 'contract_date': '20220600'}  Wrote 422 lines of prices for KR10/20220600 to Arctic connection: host 127.0.0.1, db production, collection futures_contract_prices
2022-04-27 09:42:32 {'type': 'Update-Historical-prices-manually', 'component': 'arcticFuturesContractPriceData', 'instrument_code': 'KR10', 'contract_date': '20220600'}  Added 7 additional rows of data

All good. If I rerun my pandl code I should now see KR10:

>>> from sysproduction.reporting.api import *
>>> api = reportingApi(dataBlob(), calendar_days_back=1)
>>> from sysproduction.reporting.data.pandl import *
>>> self = api.pandl_calculator 
>>> self.get_ranked_list_of_pandl_by_instrument_all_strategies_in_date_range().sort_values('codes')
           codes     pandl
3            AUD -0.066840
4            BRE -0.064874
11          CORN  0.088464
14  CRUDE_W_mini  0.271470
6            DOW -0.060057
5       EU-BASIC -0.061048
12   GAS_US_mini  0.117952
1         GBPEUR -0.140625
10    GOLD_micro  0.023197
9           IRON  0.019575
2            JPY -0.101980
8           KR10 -0.021980
7      NIKKEI400 -0.036133
13       SOYBEAN  0.127782
15        SOYOIL  0.273649
0         YENEUR -0.288296

Now KR10 is in the list.

[TobiasAntiGravity]

Thank you for the step through - really appreciate it and i will be looking at it thourougly!

As for the remark about me free loading - I have to say I am left with a visceral feeling of embarrassement and shame that I have not felt since I was a kid!

I really did not think of my self as a mooch, but I realize that this post falls within that category. There is no excuse - I guess the reason for it is that I have been so eager to try to get things into production - to "not miss the train" (FOMO maybe more descriptive), at the same time as I have felt useless with regrads to parsing the code - it is like I am missing a piece of the puzzle with regards to understanding it. But those are my problems and I should just put in enough time until I finally get it. (it is something I want to do, and was planning to do, just again wanted to forge ahead because of - FOMO)

I will definitly take your point and will self correct!

I owe you time for both this, and for answers to non leaching questions as well. If there is anything I can help - anything - please let me know! If I lived in your country I would offer to paint your house if needed. I would be happy to do it - as I really would like to repent, and show my gratitude for everything you have created.

Again - my deepest apologies!!!

[robcarver17]

You are forgiven.

[TobiasAntiGravity]

Thank you!