Introduction

Strategy Explainer is the next version of the Explainer. New features include
ability to run it on any strategy (not just a combination of child accounts)
base currency of the strategy is the currency of the report (so that gives a flexiblity to generate the report in any currency)
output also includes the opening and closing holdings, as well as the transactions done during the time period in consideration
It can be called as an API (and is also embedded into the Manager Module). The required parameters are
from_date
to_date
strategy_id (defaults to by_entire_account)
Output

The output has two parts
JSON (which gives a summary of the results)
{
  "download_Url": "<https://images.canopy.cloud/financial-statements/YgtXolkQStDQaLNOOxNAjnagzPxDChQoSJqYWKswhyfpMoprZlbyrsGHeqrnnQRhBCfrEuEAneMnRoNvCUaAEJPbPSUtAfKbCVBaqqhmftvgtyRFHoAySvDlHFoguSfuSmFAyOUyWxgaMmHawHhTjU/strategy_explainer.xlsx>",
  "realized_earnings": {
    "distributions": {
      "total_for_category": 1500.0,
      "detail": [
        {
          "user_asset_class": "Equity",
          "total_for_asset_class": 1500.0,
          "details": [
            {
              "ticketref": "TRX-000879991",
              "traded_on": "03-09-2020",
              "amount": 1500.0
            }
          ]
        }
      ]
    },
    "interest_income": {},
    "interest_expense": {},
    "misc_income": {},
    "misc_expense": {
      "total_for_category": -500000.0,
      "detail": [
        {
          "user_asset_class": "Cash",
          "total_for_asset_class": -500000.0,
          "details": [
            {
              "ticketref": "TRX-001908190",
              "traded_on": "03-09-2020",
              "amount": -500000.0
            }
          ]
        }
      ]
    },
    "execution_cost": {},
    "fx_transactions": {},
    "realized_trading_gain_loss": {
      "total": 109190.18076228205,
      "details": [
        {
          "ticketref": "TRX-001191012",
          "traded_on": "05-03-2021",
          "amount": -6286
        },
        {
          "ticketref": "TRX-000879982",
          "traded_on": "03-09-2020",
          "amount": 37.700831025
        },
        {
          "ticketref": "TRX-001190633",
          "traded_on": "08-03-2021",
          "amount": 114400.000000001
        },
        {
          "ticketref": "TRX-001195903",
          "traded_on": "01-03-2021",
          "amount": 8768
        },
        {
          "ticketref": "TRX-001471866",
          "traded_on": "05-07-2021",
          "amount": -2722
        },
        {
          "ticketref": "TRX-001557665",
          "traded_on": "03-09-2020",
          "amount": 37.479931256
        },
        {
          "ticketref": "TRX-001190888",
          "traded_on": "03-03-2021",
          "amount": -5044.99999999995
        }
      ]
    }
  },
  "unrealized_earnings": {
    "contributions": {},
    "unrealized_trading_gain_loss": {
      "total": 4008933.5482844315,
      "details": [
        {
          "ticker": "Cash",
          "security_name": null,
          "custodian_name": null,
          "amount": -2200.0
        },
        {
          "ticker": "Cash",
          "security_name": null,
          "custodian_name": null,
          "amount": -25.65
        },
        {
          "ticker": "SG6TA0000005",
          "security_name": "FCL TREASURY PTE LTD",
          "custodian_name": "DBS Singapore",
          "amount": 5931.369375
        },
        {
          "ticker": "RT2063239237",
          "security_name": "Pok Fu Lam House Hong Kong",
          "custodian_name": "Real Estate",
          "amount": 495.0
        },
        {
          "ticker": "BGAHA6U_LX",
          "security_name": "Bgf Asia H/Y Bnd-A6 Usd",
          "custodian_name": "IB US",
          "amount": -5319.150000000008
        },
        {
          "ticker": "SG6UH9000009",
          "security_name": "EZION HOLDINGS LTD",
          "custodian_name": "DBS Singapore",
          "amount": -194735.613
        },
        {
          "ticker": "XS0971721963",
          "security_name": "RUSSIAN FEDERATION",
          "custodian_name": "DBS Singapore",
          "amount": -718.75
        },
        {
          "ticker": "GLD_US",
          "security_name": "Spdr Gold Shares",
          "custodian_name": "IB US",
          "amount": 80082.0
        },
        {
          "ticker": "XS0766460157",
          "security_name": "PCCW CAPITAL NO 4 LTD",
          "custodian_name": "DBS Singapore",
          "amount": -8863.0
        },
        {
          "ticker": "XS1062931396",
          "security_name": "TML HOLDINGS PTE LTD",
          "custodian_name": "DBS Singapore",
          "amount": 1264.0
        },
        {
          "ticker": "EQUITYOPTION_TENCENTHLDG_95433055",
          "security_name": "TENCENT HLDG RG PUT W30.JANUARY 350 2018",
          "custodian_name": "IB US",
          "amount": -0.069015
        },
        {
          "ticker": "JPM_US",
          "security_name": "Jpmorgan Chase & Co",
          "custodian_name": "IB US",
          "amount": 103734.0
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "DBS Singapore",
          "amount": -21369.389352599996
        },
        {
          "ticker": "GUOL_SP",
          "security_name": "Guocoland Ltd",
          "custodian_name": "IB US",
          "amount": -219.3300000000019
        },
        {
          "ticker": "XS1037253645",
          "security_name": "NWD MTN LTD",
          "custodian_name": "DBS Singapore",
          "amount": -6135.0
        },
        {
          "ticker": "VMGRX_US",
          "security_name": "Vanguard Mid Cap Growth",
          "custodian_name": "IB US",
          "amount": 27940.0
        },
        {
          "ticker": "LoanRef_canopy_demo332-rere-01-gbp-01_600000",
          "security_name": "LoanRef_canopy_demo332-rere-01-gbp-01_600000",
          "custodian_name": "Real Estate",
          "amount": -44921.952
        },
        {
          "ticker": "AAL_US",
          "security_name": "AMERICAN AIRLINES GROUP INC",
          "custodian_name": "IB US",
          "amount": 20250.0
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "Real Estate",
          "amount": 38454.219
        },
        {
          "ticker": "FDX_US",
          "security_name": "FEDEX CORP",
          "custodian_name": "IB US",
          "amount": 547620.0
        },
        {
          "ticker": "AMGN_US",
          "security_name": "Amgen Inc",
          "custodian_name": "IB US",
          "amount": 89140.0
        },
        {
          "ticker": "LoanRef_demo-realestate_rere01_sgd_01_DBS30Y_BB+1.20",
          "security_name": "LoanRef_demo-realestate_rere01_sgd_01_DBS30Y_BB+1.20",
          "custodian_name": "Real Estate",
          "amount": -64369.2
        },
        {
          "ticker": "STRUCTUREDPRODUCT_MAYBANK_2018-05-14_SIME",
          "security_name": "SMILE_IJM|MAYBANK|SIME_14/05/2018",
          "custodian_name": "IB US",
          "amount": 1980.0
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "Real Estate",
          "amount": 10.5
        },
        {
          "ticker": "LoanRef_canopy_demo332-rere-01-hkd-01_PFLHK",
          "security_name": "LoanRef_canopy_demo332-rere-01-hkd-01_PFLHK",
          "custodian_name": "Real Estate",
          "amount": -325.5
        },
        {
          "ticker": "XS0835676353",
          "security_name": "NTPC LTD",
          "custodian_name": "DBS Singapore",
          "amount": -6783.0
        },
        {
          "ticker": "LoanRef_demo_fixed_income_dbsg01_sgd_01_7623625",
          "security_name": "LoanRef_demo_fixed_income_dbsg01_sgd_01_7623625",
          "custodian_name": "DBS Singapore",
          "amount": -26521.209132
        },
        {
          "ticker": "RT6713920822",
          "security_name": "Mansion House London",
          "custodian_name": "Real Estate",
          "amount": 63765.0
        },
        {
          "ticker": "GE_US",
          "security_name": "General Electric Co",
          "custodian_name": "IB US",
          "amount": 89055.9
        },
        {
          "ticker": "700_HK",
          "security_name": "Tencent Holdings Ltd",
          "custodian_name": "IB US",
          "amount": 21911.727
        },
        {
          "ticker": "KHC_US",
          "security_name": "Kraft Heinz Co/The",
          "custodian_name": "IB US",
          "amount": 80950.0
        },
        {
          "ticker": "AMZN_US",
          "security_name": "Amazon.Com Inc",
          "custodian_name": "IB US",
          "amount": 1875720.0
        },
        {
          "ticker": "C_US",
          "security_name": "Citigroup Inc",
          "custodian_name": "IB US",
          "amount": 51420.0
        },
        {
          "ticker": "Property_TheSail@MarinaBay-68-02",
          "security_name": "The Sail @ Marina Bay Unit 68-02 3BR 1991sqft",
          "custodian_name": "Real Estate",
          "amount": 93780.9237
        },
        {
          "ticker": "STRUCTUREDPRODUCT_3988_HK|386_HK_2018-06-13_13/06/2018",
          "security_name": "SMILE_Bank of China|China Petroleum & Chemical",
          "custodian_name": "IB US",
          "amount": 12.75
        },
        {
          "ticker": "USJ77549AA18",
          "security_name": "SUMITOMO LIFE INSUR",
          "custodian_name": "DBS Singapore",
          "amount": -5058.0
        },
        {
          "ticker": "USQ12441AB91",
          "security_name": "BHP BILLITON FIN USA LTD",
          "custodian_name": "DBS Singapore",
          "amount": -38308.75
        },
        {
          "ticker": "XS1054375446",
          "security_name": "THETA CAPITAL PTE LTD",
          "custodian_name": "DBS Singapore",
          "amount": -511.0
        },
        {
          "ticker": "SG6SE6000003",
          "security_name": "NAM CHEONG LTD",
          "custodian_name": "DBS Singapore",
          "amount": 4842.56175
        },
        {
          "ticker": "ACCUMULATOR_SOFTBANK_8378.1732_MD1800500172",
          "security_name": "Accumulator - SOFTBANK GROUP CORP (FORMERLY KNOWN AS SOFTBANK CORP) 06/07/18",
          "custodian_name": "IB US",
          "amount": 71.25965631337382
        },
        {
          "ticker": "RT2612129250",
          "security_name": "Lake House on the Lake",
          "custodian_name": "IB US",
          "amount": -30900.0
        },
        {
          "ticker": "PIMIX_US",
          "security_name": "Pimco Income Fund-Ins",
          "custodian_name": "IB US",
          "amount": 480.0
        },
        {
          "ticker": "Cash",
          "security_name": null,
          "custodian_name": null,
          "amount": -9.549694368615746e-12
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "IB US",
          "amount": -2.5
        },
        {
          "ticker": "AAPL_US",
          "security_name": "Apple Inc",
          "custodian_name": "IB US",
          "amount": 509422.5
        },
        {
          "ticker": "PIMCMEI_ID",
          "security_name": "Pimco Gis-Income Fund-Ei",
          "custodian_name": "IB US",
          "amount": -897.6660000000056
        },
        {
          "ticker": "EQ4354118736",
          "security_name": "CROMWELL EUROPEAN REIT ORD SGXC37098255",
          "custodian_name": "IB US",
          "amount": 638224.375
        },
        {
          "ticker": "IBM_US",
          "security_name": "Intl Business Machines Corp",
          "custodian_name": "Private Custodian",
          "amount": -356.40000000000003
        },
        {
          "ticker": "NFLX_UW",
          "security_name": "Netflix Inc",
          "custodian_name": "Private Custodian",
          "amount": 289.6
        },
        {
          "ticker": "MINT_SP",
          "security_name": "Mapletree Industrial Trust",
          "custodian_name": "IB US",
          "amount": 124.87499999999712
        },
        {
          "ticker": "NFLX_UW",
          "security_name": "Netflix Inc",
          "custodian_name": "IB US",
          "amount": 2869.999999999998
        },
        {
          "ticker": "XS1753595328",
          "security_name": "ABJA INVESTMENT CO",
          "custodian_name": "IB US",
          "amount": 57.99999999999636
        },
        {
          "ticker": "NFLX_US",
          "security_name": "Netflix Inc",
          "custodian_name": "Real Estate",
          "amount": 3991.9999999999973
        },
        {
          "ticker": "NETLINK_SP",
          "security_name": "Netlink Nbn Trust",
          "custodian_name": "IB US",
          "amount": 127.8199999999998
        },
        {
          "ticker": "RT1812761129",
          "security_name": "Apartment in CBD",
          "custodian_name": "Real Estate",
          "amount": 12300.0
        },
        {
          "ticker": "RT2311242117",
          "security_name": "No-Code Experiment",
          "custodian_name": "IB US",
          "amount": 3105.5939999999982
        },
        {
          "ticker": "RT1112131213",
          "security_name": "This is a test Asset",
          "custodian_name": "IB US",
          "amount": 2518.50239999999
        },
        {
          "ticker": "RT2911221626",
          "security_name": "Lake Chalet in Zurich",
          "custodian_name": "IB US",
          "amount": -295.5
        },
        {
          "ticker": "RT1212259125",
          "security_name": "No-Code Experiment",
          "custodian_name": "IB US",
          "amount": 3105.5939999999982
        },
        {
          "ticker": "IBM_US",
          "security_name": "Intl Business Machines Corp",
          "custodian_name": "Real Estate",
          "amount": 336.40000000000003
        },
        {
          "ticker": "WORK_US",
          "security_name": "Slack Technologies Inc- cl A",
          "custodian_name": "IB US",
          "amount": 108.92
        },
        {
          "ticker": "RT1429881126",
          "security_name": "No-Code Experiment",
          "custodian_name": "IB US",
          "amount": 3105.5939999999982
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "Private Custodian",
          "amount": 5.684341886080802e-14
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "Real Estate",
          "amount": -13321.47
        },
        {
          "ticker": "Cash",
          "security_name": null,
          "custodian_name": null,
          "amount": -830.297734999996
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "Real Estate",
          "amount": 2.6716406864579767e-12
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "IB US",
          "amount": 31175.8
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "IB US",
          "amount": -11834.304399999985
        },
        {
          "ticker": "Cash",
          "security_name": "Cash",
          "custodian_name": "IB US",
          "amount": 2220.0248000000342
        },
        {
          "ticker": "QQQ_US",
          "security_name": "Invesco Qqq Trust Series 1",
          "custodian_name": "IB US",
          "amount": -14420.000000000988
        },
        {
          "ticker": "NFLX_US",
          "security_name": "Netflix Inc",
          "custodian_name": "IB US",
          "amount": 1191.9999999999927
        },
        {
          "ticker": "IBM_US",
          "security_name": "Intl Business Machines Corp",
          "custodian_name": "IB US",
          "amount": 94987.43923771901
        }
      ]
    },
    "change_in_accrued_interest": {
      "total": -34010.337425,
      "details": [
        {
          "ticker": "SG6TA0000005",
          "security_name": "FCL TREASURY PTE LTD",
          "custodian_name": "DBS Singapore",
          "amount": 1.50495
        },
        {
          "ticker": "XS1121907676",
          "security_name": "TATA MOTORS LTD",
          "custodian_name": "DBS Singapore",
          "amount": -3417.4
        },
        {
          "ticker": "SG6UH9000009",
          "security_name": "EZION HOLDINGS LTD",
          "custodian_name": "DBS Singapore",
          "amount": -7771.8683
        },
        {
          "ticker": "XS0971721963",
          "security_name": "RUSSIAN FEDERATION",
          "custodian_name": "DBS Singapore",
          "amount": -2611.0
        },
        {
          "ticker": "XS0766460157",
          "security_name": "PCCW CAPITAL NO 4 LTD",
          "custodian_name": "DBS Singapore",
          "amount": -2044.4
        },
        {
          "ticker": "XS1062931396",
          "security_name": "TML HOLDINGS PTE LTD",
          "custodian_name": "DBS Singapore",
          "amount": -4025.0
        },
        {
          "ticker": "XS1037253645",
          "security_name": "NWD MTN LTD",
          "custodian_name": "DBS Singapore",
          "amount": -495.8
        },
        {
          "ticker": "XS0835676353",
          "security_name": "NTPC LTD",
          "custodian_name": "DBS Singapore",
          "amount": -2533.2
        },
        {
          "ticker": "USJ77549AA18",
          "security_name": "SUMITOMO LIFE INSUR",
          "custodian_name": "DBS Singapore",
          "amount": -3466.5
        },
        {
          "ticker": "USQ12441AB91",
          "security_name": "BHP BILLITON FIN USA LTD",
          "custodian_name": "DBS Singapore",
          "amount": -3187.5
        },
        {
          "ticker": "XS1054375446",
          "security_name": "THETA CAPITAL PTE LTD",
          "custodian_name": "DBS Singapore",
          "amount": 427.6
        },
        {
          "ticker": "SG6SE6000003",
          "security_name": "NAM CHEONG LTD",
          "custodian_name": "DBS Singapore",
          "amount": 109.025925
        },
        {
          "ticker": "XS1753595328",
          "security_name": "ABJA INVESTMENT CO",
          "custodian_name": "IB US",
          "amount": -4995.8
        }
      ]
    }
  },
  "fund_flow": {
    "incoming_funds": {
      "total_for_category": 117355.6,
      "detail": [
        {
          "user_asset_class": "Cash",
          "total_for_asset_class": 117355.6,
          "details": [
            {
              "ticketref": "TRX-000892988",
              "traded_on": "10-09-2020",
              "amount": 1181.5
            },
            {
              "ticketref": "TRX-000885670",
              "traded_on": "30-08-2020",
              "amount": 100000.0
            },
            {
              "ticketref": "TRX-000900390",
              "traded_on": "18-09-2020",
              "amount": 15000.0
            },
            {
              "ticketref": "TRX-000878415",
              "traded_on": "31-08-2020",
              "amount": 73.9
            },
            {
              "ticketref": "TRX-000892989",
              "traded_on": "11-09-2020",
              "amount": 1100.2
            }
          ]
        }
      ]
    },
    "outgoing_funds": {
      "total_for_category": -500000.0,
      "detail": [
        {
          "user_asset_class": "Cash",
          "total_for_asset_class": -500000.0,
          "details": [
            {
              "ticketref": "TRX-000879992",
              "traded_on": "03-09-2020",
              "amount": -500000.0
            }
          ]
        }
      ]
    },
    "incoming_securities": {
      "total_for_category": 736082.9199999999,
      "detail": [
        {
          "user_asset_class": "Art",
          "total_for_asset_class": 250000.0,
          "details": [
            {
              "ticketref": "TRX-001262590",
              "traded_on": "31-12-2020",
              "amount": 100000.0
            },
            {
              "ticketref": "TRX-001262589",
              "traded_on": "31-12-2020",
              "amount": 150000.0
            }
          ]
        },
        {
          "user_asset_class": "Equity",
          "total_for_asset_class": 486082.92,
          "details": [
            {
              "ticketref": "TRX-001557681",
              "traded_on": "11-09-2020",
              "amount": 121581.45999999999
            },
            {
              "ticketref": "TRX-001557682",
              "traded_on": "11-09-2020",
              "amount": 121460.0
            },
            {
              "ticketref": "TRX-000932415",
              "traded_on": "11-09-2020",
              "amount": 121460.0
            },
            {
              "ticketref": "TRX-000932416",
              "traded_on": "11-09-2020",
              "amount": 121581.45999999999
            }
          ]
        }
      ]
    },
    "outgoing_securities": {}
  },
  "opening_networth": 13729631.292930566,
  "closing_networth": 17668683.204552278,
  "change_in_networth": 3939051.9116217135,
  "total_realized_earning": -389309.81923771795,
  "total_unrealized_earning": 3974923.2108594314,
  "total_fund_flow": 353438.51999999996,
  "total_unexplained": -5.9322320692e-11
}
Link to an Excel sheet that has two tabs
1. Change in Holdings Report
2. SLT with Realized Gain Calcs
Calculation Methodology

Step 1: Create Change in Holdings Report

This report cannot be created directly from consolidated holdings and needs to be calculated from scratch
This is because the user has the option of choosing any combination of transactions and base currency in the strategy definition
As a result this report is calculated from scratch using the following steps
1. Calculate opening positions by selecting transactions from SLT table which belong to the particular strategy and are <= from_date
2. Mark each position to market (both for price and fx)
3. Do the same for closing positions by selecting transactions which are <= to_date
Introduction

Output

Calculation Methodology

Step 1: Create Change in Holdings Report

Step 2: Figure out Base Ccy Fx Rate for all Transactions
