<jupyter_start><jupyter_text>PnL Explain :  Estimating PnL using sensitivities and Market DataFor more context and definitions around pnl explained, [check out our article on atoti.io](https://www.atoti.io/pnl-explained-with-atoti/). A few definitions:- [Portfolio](https://www.investopedia.com/terms/p/portfolio.asp) refers to any collection of financial assets such as stocks, bonds and cash. - [PnL](https://www.investopedia.com/terms/p/plstatement.asp) is a common term used in trading referring to the total "Profit and Loss" made by a portfolio over a certain time period.- [Maturity date](https://www.investopedia.com/terms/m/maturitydate.asp) refers to the due date on which a borrower must pay back the principal of a debt, i.e. the initial amount of money borrowed.- [Tenor](https://www.investopedia.com/terms/t/tenor.asp) refers to the length of time remaining in a contract, while maturity refers to the initial length of the agreement upon its inception. The tenor of a financial instrument declines over time, whereas its maturity remains constant.- [Yield curve](https://www.investopedia.com/terms/y/yieldcurve.asp) is a graphical representation of interest rates per maturity date.- [Sensitivity](https://www.investopedia.com/terms/s/sensitivity.asp) is the magnitude of a financial instrument's reaction to changes in underlying factors.- [Greeks](https://www.investopedia.com/terms/g/greeks.asp) describes the different dimensions of risk involved in taking an options position. - [Delta](https://www.investopedia.com/terms/d/delta.asp), in particular, is a first-order greek, and represents the ratio that compares the change in the price of an asset to the corresponding change in the price of its derivative. For example, if a stock option has a delta value of 0.75, this means that if the underlying stock increases in price by 1 dollar per share, the option on it will rise by 0.75 dollars per share. IntroductionThe PnL explain technique seeks to estimate the daily PnL from the change in the underlying risk factors.In this case the risk factors are determined by the yield curve plotting interest rates for each tenor.Usually, a portfolio risk manager will monitor the risk factors that impact his portfolio, instead of monitoring all the positions booked in his portfolio. To assess what will be the value of his portfolio, a portfolio risk manager is interested in performing what-if analysis based on a scenario of the risk factor values.In this notebook, we perform a simplified PnL Explained by using Delta(Δ) to represent our sensitivity instead of the full Greeks. We will utilize the various features of atoti libraries to:- Load data into a multi-dimensional cube- Explore the Data using the embedded visualization or atoti UI- Calculate Estimated PnL using sensitivities and Market Data- Run multiple scenarios of the Yield Curve Stress Test<jupyter_code>import atoti as tt
# Create an atoti session
session = tt.create_session(config={"user_content_storage": "content"})<jupyter_output><empty_output><jupyter_text>1.1 Loading Data and creating ActivePivot multidimensional environment Creating atoti DataStore There are many ways atoti can consume data. In this notebook, we will be using read_csv to load data into the datastores.  Position Sensitivities<jupyter_code>position_sensitivity = session.read_csv(
    "s3://data.atoti.io/notebooks/pnl-explained/position_sensitivities.csv",
    keys=["book_id", "instrument_code", "currency", "curve", "tenor"],
    table_name="Position_Sensitivities",
)
# you can use head(n) to view the first n rows of the store.
# likewise, you can use position_sensitivity.columns to view the columns available in the store
# lastly, you can use {"columns": len(position_sensitivity.columns), "rows": len(position_sensitivity)} to view the number of rows and columns loaded into the table
position_sensitivity.head(5)<jupyter_output><empty_output><jupyter_text>Position Data<jupyter_code>position_table = session.read_csv(
    "s3://data.atoti.io/notebooks/pnl-explained/position_data.csv",
    keys=["book_id", "instrument_code"],
    table_name="Position",
)
position_table.head(5)<jupyter_output><empty_output><jupyter_text>Portfolio structure<jupyter_code>trading_desk_table = session.read_csv(
    "s3://data.atoti.io/notebooks/pnl-explained/trading_desk.csv",
    keys=["book_id"],
    table_name="Trading_Desk",
)
trading_desk_table.head(5)<jupyter_output><empty_output><jupyter_text>Market data<jupyter_code>market_data_table = session.read_csv(
    "s3://data.atoti.io/notebooks/pnl-explained/market_data.csv",
    keys=["currency", "curve", "tenor"],
    table_name="Market_Data",
)
market_data_table.head()<jupyter_output><empty_output><jupyter_text>Creating references between stores  We will proceed to set up references between the stores that we just created. We will perform the join from `position_sensitivity`.<jupyter_code>position_sensitivity.join(trading_desk_table, mapping={"book_id": "book_id"})
position_sensitivity.join(
    position_table, mapping={"book_id": "book_id", "instrument_code": "instrument_code"}
)
position_sensitivity.join(
    market_data_table,
    mapping={"currency": "currency", "curve": "curve", "tenor": "tenor"},
)<jupyter_output><empty_output><jupyter_text>Creating cube  We create the cube using the base store *position_sensitivity*.   Note that we have not passed in any mode in create_cube().  This means that a hierarchy will be automatically created for each non-numeric column and measure will be automatically created for each numeric column.<jupyter_code>cube = session.create_cube(position_sensitivity, "Position_Sensitivities")<jupyter_output><empty_output><jupyter_text>We can see that all the stores are joined to *Position Sensitivities Store*, this is what we call the base store.  For a record to be reachable in the cube, it must exists in the base store.<jupyter_code>cube.schema<jupyter_output><empty_output><jupyter_text>Explore the Data Set as a CubeWe have the option to visualize the cube in chart, feature-value, pivot-table or tabular.  Let's look at the sensitivity across currency for each asset class.<jupyter_code>session.visualize()<jupyter_output><empty_output><jupyter_text>Adding business logic calculation    Let's assign a variable to the attributes of the cube, so that we can:  * create measures* create hierarchies<jupyter_code>m = cube.measures
h = cube.hierarchies
lvl = cube.levels<jupyter_output><empty_output><jupyter_text>Let's inspect what hierarchies have been automatically generated during cube creation<jupyter_code>h<jupyter_output><empty_output><jupyter_text>Let's inspect what measures have been automatically generated during cube creation<jupyter_code>m<jupyter_output><empty_output><jupyter_text>Creating MeasuresFrom the *Market Data* store, we have the *start of day* and *end of day* value which we used to calculate the change in yields.<jupyter_code>m["last.VALUE"] = tt.value(market_data_table["last"])
m["start_of_day.VALUE"] = tt.value(market_data_table["start_of_day"])<jupyter_output><empty_output><jupyter_text>Parameter simulation setupWe are going to create a parameter simulation measure `last parameter` that will allow us to create fluctuations to the `last.VALUE`.This measure will be added to the `last.VALUE` based on scenarios, at specific *tenor* and *currency* levels.The default value of `last parameter` will be 0.0, hence zero fluctuation from the original value when not defined in the scenario.  We will label the curve derived from the original data set as *Last Curve*<jupyter_code>curve_simulation = cube.create_parameter_simulation(
    "Curve Simulation",
    measure_name="last parameter",
    default_value=0.0,
    levels=[lvl["tenor"], lvl["currency"]],
    base_scenario_name="Last Curve",
)<jupyter_output><empty_output><jupyter_text>As the fluctuations will be by [basis points](https://www.investopedia.com/ask/answers/what-basis-point-bps/), let's format the `last parameter` to show 3 decimal points.<jupyter_code>m["last parameter"].formatter = "DOUBLE[#.000]"<jupyter_output><empty_output><jupyter_text>Taking in consideration the potential fluctuations in the `last.VALUE` induced by the simulation, let's compute the `effective last` as follows:<jupyter_code>m["effective last"] = m["last.VALUE"] + m["last parameter"]<jupyter_output><empty_output><jupyter_text>We will come back to the simulations later on in the notebook. For now, let's look at how we can compute our Theoretical Pnl.  Theoretical PnlWe derive our Delta by applying `sensi.SUM` to the [notional](https://www.investopedia.com/terms/n/notionalvalue.asp). We, then apply Delta to our change in yield to get the impact of prices:  $Theoretical Pnl = (Delta) \times (Yield Change)$  We will aggregate the *Theoretical PnL* over the levels listed in the scope below as we will be exploring the measure over these levels.  Notice that we are using the `effective last` measure here to compute the delta.<jupyter_code>m["Theoretical PnL"] = tt.agg.sum(
    m["sensi.SUM"]
    * (m["effective last"] - m["start_of_day.VALUE"])
    * m["notional.SUM"],
    scope=tt.scope.origin(
        lvl["currency"],
        lvl["curve"],
        lvl["tenor"],
        lvl["book_id"],
        lvl["instrument_code"],
    ),
)<jupyter_output><empty_output><jupyter_text>Adding new multi-level hierarchy for portfolio structure  Based on the data that are of interest to the [Buy-Side](https://www.investopedia.com/terms/b/buyside.asp) and [Sell-Side](https://www.investopedia.com/terms/s/sellside.asp), we are going to see how we can structure hierarchies to facilitate the navigation of data in a cube.    Asset Mangement : Buy-Side  *Buy-Side* purchases stocks, securities and other financial products, according to the needs and strategy of a portfolio.  It would make data navigation more intuitive to create an *Investment Portfolio Hierarchy* that has Asset Class, Sub Asset Class, Fund and Portfolio as levels.  We would be able to have a global view over the Asset Class level.  If we needed more granular information, we could easily drill-down to the Sub Asset class, Fund and all the way down to the portfolio holding the Asset.<jupyter_code>h["Investment Portfolio Hierarchy"] = {
    "Asset Class": cube.levels["asset_class"],
    "Sub Asset Class": cube.levels["sub_asset_class"],
    "Fund": cube.levels["fund"],
    "Portfolio": cube.levels["portfolio"],
}<jupyter_output><empty_output><jupyter_text>Investment Banks : Sell-Side  *Sell-Side* help companies to raise debt and equity capital and then sell those securities to the *Buy-Side*.  \*Sell-Side* would then be interested in having a global view over the *Business Unit* such as Rates & Credit, Forex, Equity etc.    They could then drill-down to the *Sub Business Unit* to see its performance, the *Trading Desk* and all the way to the *Book* level.<jupyter_code>h["Trading Book Hierarchy"] = {
    "Business Unit": lvl["business_unit"],
    "Sub Business Unit": lvl["sub_business_unit"],
    "Trading Desk": lvl["trading_desk"],
    "Book": lvl["book"],
}<jupyter_output><empty_output><jupyter_text>Explore the Theoretical PnL by Investment Portfolio HierarchyWe shall explore the Theoretical PnL from the Buy-Side perspective.    Let's visualize the data in a chart to see the spread of the *Theoretical PnL* across the funds for each *Asset Class*.<jupyter_code>session.visualize("Theoretical PnL spread across funds")<jupyter_output><empty_output><jupyter_text>In the next 2 visualizations, we shall see the impact of having the *Investment Portfolio Hierarchy*.In the first tree map, we perform a split at the *portfolio* level from *Investment Portfolio Hierarchy*. This means that we are drilling down from the Asset class to Sub Asset Class, Fund and then to the Portfolio level. E.g. we will see the portfolio HE01 under the Asset Class *Rates & Credit* and *Forex*.<jupyter_code>session.visualize("Investment Portfolio concentration")<jupyter_output><empty_output><jupyter_text>In this second tree map, we perform a split at the *portfolio* hierarchy.  Hence we will only see the collective *Theoretical PnL* of the portfolio. E.g. we will only see 1 HE01 in this map.<jupyter_code>session.visualize("Portfolio concentration")<jupyter_output><empty_output><jupyter_text>In the below pivot-table, we can easily drill-down the levels in the *Investment Portfolio Hierarchy* to see measures at granular levels.  Naturally, this could also be achieved by clicking on `>+` and manually adding a hierarchy to drill down to. It's just a little more tedious.<jupyter_code>session.visualize("Investment Portfolio Hierarchy Pivot Table")<jupyter_output><empty_output><jupyter_text>Yield CurveThe *Yield Curve* gives insights to the future interest rate changes and economic activity.   A normal yield curve which shows an upward curve, shows longer-term bonds having higher yields than short-term ones. Short-term interest rates are the lowest because there is less embedded inflation risk. This shows economic expansion.An inverted curve which has a downward slope, is a sign of an upcoming recession. The shorter-term bonds yields higher than the longer-term ones.  A flat or humped yield curve shows that the yields for shorter- and longer-term bonds are very close to each other. Investors are expecting interest rates to remain about the same, probably an economic transition.  We will use the start of day and last rates against the tenor to plot our yield curve. We should be able to see a normal upward yield curve in the chart below.<jupyter_code>session.visualize("Yield Curve")<jupyter_output><empty_output><jupyter_text>We see that *Rates & Credit* has the highest *Theoretical PnL* among the Asset class.<jupyter_code>session.visualize("Theoretical PnL")<jupyter_output><empty_output><jupyter_text>By applying sensitivity against the Asset Class and Currency, we can see that the 3 peaks are in the order of *Rates & Credit, EUR*, *Forex, EUR* and lastly *Equity, EUR*.<jupyter_code>session.visualize("Risk Map")<jupyter_output><empty_output><jupyter_text>What-ifs using Parameter simulationsWe will run simulations to see the impact of shifts in curves: - Parallel Shift- Curve Inversion- Curve Inversion Stress  Shift Simulation    A parallel shift in the yield curve happens when the interest rates on all fixed-income maturities increase or decrease by the same number of basis point.  The curve does not change but it shifts to the left or to the right. This is most common when the yield curve is upward sloping.    This simulation is important for investors who might liquidate their positions before maturity, as the shift can cause bond prices to fluctuate substantially.  Investors could mitigate this risk by reducing the bond duration, alleviate the volatility. Earlier, we have created the parameter simulation `curve_simulation`.  We will use it to simulate parallel shift in yield curve by applying a negative 10 [bps](https://www.investopedia.com/ask/answers/what-basis-point-bps/) shift on the last rate for Euro currency on all Tenor.<jupyter_code>curve_simulation += ("Curve Parallel Shift", None, "EUR", -0.001)<jupyter_output><empty_output><jupyter_text>The above snippet creates a scenario `Curve Parallel Shift`, followed by the levels that is affected by this simulation and the value of `last parameter`.  `None` is a wildcard value, indicating that it will affect all the Tenors  Explore Curve Parallel Shift scenario impact on Theoretical PnL    We see that while the curve retains its shape, all the data points moved to the right of the graph.<jupyter_code>session.visualize("Yield Curve Curve Parallel Shift")<jupyter_output><empty_output><jupyter_text>Let's now go into the investment portfolio to see the impact of this shift on the *Theoretical PnL*.  \We see the *Theoretical PnL* went negative on a 10bps shift, with *Rates & Credit* suffering the most loss.<jupyter_code>session.visualize("Theoretical PnL Parallel Shift - Investment Portfolio")<jupyter_output><empty_output><jupyter_text>Curve Inversion SimulationAn inverted yield curve is a predictor of economic recession as it implies that interest rates are going to fall. In fact, recessions usually cause interest rates to fall.    Short-term bills are expected to plummet if recession is coming, as Federal Reserve will lower the fed funds rate when economy slows down. Therefore investors may avoid Treasurys with maturities of less than two years to have a safe investment. This sets back the demand for those bills and sends their yields up. Higher demands in the longer-term instruments lower the yields, hence an inverted curve occurs. Let's run another simulation on the currency EUR and see the impact on the *Theoretical PnL*.We will set `last parameter` to a negative 20bps for the currency EUR for tenors 5Y and above and observe the change in the curve shape.<jupyter_code>curve_simulation += ("Curve Inversion", "5Y", "EUR", -0.002)
curve_simulation += ("Curve Inversion", "6Y", "EUR", -0.002)
curve_simulation += ("Curve Inversion", "7Y", "EUR", -0.002)
curve_simulation += ("Curve Inversion", "8Y", "EUR", -0.002)
curve_simulation += ("Curve Inversion", "9Y", "EUR", -0.002)<jupyter_output><empty_output><jupyter_text>Explore Curve Inversion scenario impact on Theoretical PnL    We see a slight inversion in the curve from the tenor 4Y to 5Y before it becomes a shift to the right, as in the case of a parallel shift.  In this case, we predict that the yields will dip for instruments with more than 5Y maturity.<jupyter_code>session.visualize("Yield Curve Inversion")<jupyter_output><empty_output><jupyter_text>As expected, we can see drastic drop in the *Theoretical PnL* across all the asset classes, the greatest loss incurred in the asset class *Rates & Credit*.<jupyter_code>session.visualize("Theoretical PnL Curve Inversion")
session.visualize("Theoretical PnL against Tenor")<jupyter_output><empty_output><jupyter_text>Curve Inversion Stress Simulation    We will create a new scenario where we stress the curve further by assuming further drops in rates for the longer-term instruments.<jupyter_code>curve_simulation += ("Curve Inversion Stress", "5Y", "EUR", -0.002)
curve_simulation += ("Curve Inversion Stress", "6Y", "EUR", -0.002)
curve_simulation += ("Curve Inversion Stress", "7Y", "EUR", -0.004)
curve_simulation += ("Curve Inversion Stress", "8Y", "EUR", -0.004)
curve_simulation += ("Curve Inversion Stress", "9Y", "EUR", -0.004)<jupyter_output><empty_output><jupyter_text>Explore Curve Inversion Stress scenario impact on Theoretical PnL    We see the curve is starting to slope more downward.<jupyter_code>session.visualize("Yield Curve Inversion Stress")<jupyter_output><empty_output><jupyter_text>We see the downward slope dipped further in the stress scenario.<jupyter_code>session.visualize("Theoretical PnL Curve Inversion Stress")
session.visualize("Scenarios Comparison")<jupyter_output><empty_output><jupyter_text>Build Your Standalone App using atoti UI:* Publish Yield Curve and PnL Views* Add Page Quick filters* Compare scenarios* Save Dashboards    You can access atoti UI with the link below:<jupyter_code>session.link()<jupyter_output><empty_output><jupyter_text>We can publish the visualizations above as widgets to atoti UI. Click on *Open App* when you have published all the widgets of interest.   We can quickly put together a PnL Explained dashboard.      Access the above dashboard with the link provided below:<jupyter_code>session.link(path="/#/dashboard/223")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.08223650647893072, 'fraction_numerical': 0.004999489847974697, 'mean_word_length': 5.624873267995945, 'pattern_counts': {'":': 17, '<': 149, '<?xml version=': 0, '>': 150, 'https://': 14, 'lorem ipsum': 0, 'www.': 14, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '37', 'dir': 'jupyter-structured-clean-dedup', 'id': '41434', 'license': 'Apache-2.0', 'n_tokens_mistral': 5829, 'n_tokens_neox': 5466, 'n_words': 2246, 'path': 'notebooks/pnl-explained/main.ipynb', 'repo_name': 'resslerruntime/notebooks', 'size': '19602'}