GDS_Individual_Project.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "a03c5a0d",
   "metadata": {},
   "source": [
    "# FIN41910 Green Data Science: Portfolio Decarbonisation\n",
    "\n",
    " Aman Parary - 18417714"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "690ee045",
   "metadata": {
    "code_folding": [
     17,
     26,
     40,
     54,
     67,
     88,
     113,
     129,
     142,
     155,
     199,
     239,
     273,
     300,
     322,
     345,
     366,
     388,
     404,
     427,
     445,
     465,
     467,
     476,
     481,
     506,
     583,
     631,
     682,
     715,
     808
    ],
    "run_control": {
     "marked": false
    }
   },
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "from pypfopt.efficient_frontier import EfficientFrontier\n",
    "from pypfopt.expected_returns import mean_historical_return\n",
    "from pypfopt.risk_models import CovarianceShrinkage\n",
    "from pypfopt import base_optimizer\n",
    "from pypfopt import objective_functions\n",
    "import cvxpy as cp\n",
    "from pypfopt import base_optimizer\n",
    "from pypfopt.exceptions import OptimizationError\n",
    "\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "\n",
    "class PortfolioDecarbonisation:\n",
    "\n",
    "    def __init__(self, filename):\n",
    "        \"\"\"\n",
    "        Initialise the class with a file name\n",
    "\n",
    "        Parameters:\n",
    "        filename (str): Name of the Excel file\n",
    "        \"\"\"\n",
    "        self.filename = filename\n",
    "\n",
    "    def load_excel_data(self, sheetname, skiprows=None):\n",
    "        \"\"\"\n",
    "        Load an excel file with given filename, sheetname and skiprows\n",
    "\n",
    "        Parameters:\n",
    "        filename (str): Name of the Excel file\n",
    "        sheetname (str): Sheetname in the Excel file\n",
    "        skiprows (list): List of rows to skip from top. None by default\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: A pandas DataFrame containing the data from the Excel file.\n",
    "        \"\"\"\n",
    "        return pd.read_excel(self.filename, sheet_name=sheetname, skiprows=skiprows)\n",
    "\n",
    "    def rename_dataframe_columns(self, df, column_mapping):\n",
    "        \"\"\"\n",
    "        Rename the columns in a DataFrame based on a mapping\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): DataFrame to be renamed\n",
    "        column_mapping (dict): A dictionary mapping old column names to new column names\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: The DataFrame with renamed columns.\n",
    "        \"\"\"\n",
    "        df.rename(columns=column_mapping, inplace=True)\n",
    "        return df\n",
    "\n",
    "    def fix_unnamed_columns(self, df):\n",
    "        \"\"\"\n",
    "        Fix 'Unnamed' columns in the dataframe\n",
    "\n",
    "        Parameters:\n",
    "        dataframe (DataFrame): The pandas DataFrame to be processed.\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: The DataFrame with fixed 'Unnamed' columns.\n",
    "        \"\"\"\n",
    "        df.columns = df.columns.to_series().mask(lambda x: x.str.startswith('Unnamed')).ffill()\n",
    "        return df\n",
    "\n",
    "    def create_multi_index(self, df):\n",
    "        \"\"\"\n",
    "        Create MultiIndex df from the given df.\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): The pandas DataFrame to be processed.\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: The MultiIndex DataFrame.\n",
    "        \"\"\"\n",
    "        copy_df = df.copy()\n",
    "        copy_df.drop(['Dates'], axis=1, inplace=True)\n",
    "        multi_index = pd.MultiIndex.from_arrays([copy_df.columns, copy_df.iloc[0].values])\n",
    "\n",
    "        df = df.drop([0]).reset_index(drop=True)\n",
    "        df['Dates'] = pd.to_datetime(df['Dates'], errors='coerce').dt.strftime('%Y-%m-%d')\n",
    "        df.set_index(df['Dates'], inplace=True)\n",
    "        df.drop(['Dates'], axis=1, inplace=True)\n",
    "        df.columns = multi_index\n",
    "        return df\n",
    "    \n",
    "    def _process_data_common(self, df, new_columns, selected_columns=None):\n",
    "        \"\"\"\n",
    "        Common processing for financial data, emission data and ESG Score data\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): DataFrame to be processed\n",
    "        new_columns (list): Column names for the DataFrame\n",
    "        selected_columns (list): Columns to select from the processed DataFrame. None by default\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: Processed DataFrame\n",
    "        \"\"\"\n",
    "        df = df.stack(0, dropna=False)\n",
    "        df.rename_axis(index=['Dates', 'Ticker'], inplace=True)\n",
    "        df = df.swaplevel()\n",
    "        df = df.sort_values(['Ticker', 'Dates'], ascending=[True, True])\n",
    "        columns_to_convert = df.columns\n",
    "        df[columns_to_convert] = df[columns_to_convert].apply(pd.to_numeric, errors='coerce')\n",
    "        df.columns = new_columns\n",
    "        \n",
    "        if selected_columns is not None:\n",
    "            df = df[selected_columns]\n",
    "            \n",
    "        return df\n",
    "\n",
    "    def process_financial_data(self, df):\n",
    "        \"\"\"\n",
    "        Process the financial data df to final form.\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): The pandas DataFrame to be processed.\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: The final DataFrame.\n",
    "        \"\"\"\n",
    "\n",
    "        new_columns = ['MarketCap', 'Revenue', 'TotalAssets', 'Total Equity']\n",
    "        selected_columns = ['MarketCap', 'TotalAssets']\n",
    "    \n",
    "        return self._process_data_common(df, new_columns, selected_columns)\n",
    "\n",
    "    def process_emission_data(self, df):\n",
    "        \"\"\"\n",
    "        Process the emission data dataframe to final form\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): The pandas DataFrame to be processed\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: The final DataFrame.\n",
    "        \"\"\"\n",
    "        new_columns = ['Scope1', 'Scope2Location', 'Scope2Market', 'Scope3']\n",
    "        return self._process_data_common(df, new_columns)\n",
    "\n",
    "    def process_esg_score_data(self, df):\n",
    "        \"\"\"\n",
    "        Process the ESG Score data df to final form.\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): The pandas DataFrame to be processed.\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: The final DataFrame.\n",
    "        \"\"\"\n",
    "        new_columns = ['ESG Score', 'Environmental Score', 'Governance Score', 'Social Score']\n",
    "        return self._process_data_common(df, new_columns)\n",
    "\n",
    "    def process_data(self, sheetnames=['Company List', 'Financial Data', 'GHG Emission', 'ESG Score'],\n",
    "                     filter_date='2022-12-31'):\n",
    "        \"\"\"\n",
    "        Load, process, and merge data from multiple sheets of an Excel file.\n",
    "\n",
    "        Parameters:\n",
    "        sheetnames (list): A list of sheet names in the Excel file\n",
    "\n",
    "        filter_date (str): A date string in 'yyyy-mm-dd' format to filter the final DataFrame\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: A pandas DataFrame containing processed data from all specified sheets\n",
    "        \"\"\"\n",
    "\n",
    "        dataframes = {}\n",
    "        for sheetname in sheetnames:\n",
    "            df = self.load_excel_data(sheetname, skiprows=[0, 1, 2])\n",
    "            df = self.fix_unnamed_columns(df)\n",
    "            df.columns.values[0] = 'Dates'\n",
    "            df = self.create_multi_index(df)\n",
    "            dataframes[sheetname] = df\n",
    "\n",
    "        # process each dataframe individually\n",
    "        R3000list = self.rename_dataframe_columns(dataframes['Company List'], {'BBG Ticker': 'Ticker'})\n",
    "        fin_panel = self.process_financial_data(dataframes['Financial Data'])\n",
    "        emission_panel = self.process_emission_data(dataframes['GHG Emission'])\n",
    "        esg_score_panel = self.process_esg_score_data(dataframes['ESG Score'])\n",
    "\n",
    "        # merge all processed dataframes\n",
    "        df1 = pd.merge(emission_panel, esg_score_panel, how='outer', on=['Ticker', 'Dates'])\n",
    "        df2 = pd.merge(df1, fin_panel, how='outer', on=['Ticker', 'Dates'])\n",
    "        # Filter for the specified date\n",
    "        final_data = df2[df2.index.get_level_values('Dates') == filter_date]\n",
    "\n",
    "        # Check for missing values in 'Scope1', 'Scope2Location', 'Revenue'\n",
    "        drop_list = final_data[final_data[['Scope1', 'Scope2Location', 'TotalAssets']].isna().any(axis=1)]\n",
    "        drop_list.reset_index(drop=False, inplace=True)\n",
    "        drop_list = drop_list['Ticker']\n",
    "\n",
    "        # Drop companies with missing values\n",
    "        final_data = final_data.drop(drop_list, level=0, axis=0)\n",
    "\n",
    "        return final_data\n",
    "\n",
    "    def process_stock_price_data(self, sheetname='Stock', skiprows=[0, 1, 2]):\n",
    "        \"\"\"\n",
    "        Load and process stock price data from an Excel file.\n",
    "\n",
    "        Parameters:\n",
    "        sheetname (str): Sheetname in Excel file\n",
    "\n",
    "        skiprows (list): Skip first 3 rows\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: Pandas DataFrame containing processed stock price data.\n",
    "        \"\"\"\n",
    "        # Load stock price data from the Excel file\n",
    "        stock_data = self.load_excel_data(sheetname, skiprows)\n",
    "\n",
    "        # Fix 'Unnamed' columns\n",
    "        stock_data = self.fix_unnamed_columns(stock_data)\n",
    "        stock_data = stock_data.drop([0]).reset_index(drop=True)\n",
    "        stock_data.columns.values[0] = 'Dates'\n",
    "\n",
    "        # Create MultiIndex DataFrame\n",
    "        stock_data = self.create_multi_index(stock_data)\n",
    "\n",
    "        # Process the data\n",
    "        stock_data = stock_data.stack(0, dropna=False)\n",
    "        stock_data.rename_axis(index=['Dates', 'Ticker'], inplace=True)\n",
    "        stock_data = stock_data.swaplevel()\n",
    "        stock_data = stock_data.sort_values(['Ticker', 'Dates'], ascending=[True, True])\n",
    "        stock_data = stock_data.astype(float)\n",
    "        stock_data.columns = ['Closing Stock Price']\n",
    "        stock_closing_price = stock_data.unstack().T\n",
    "        stock_closing_price.reset_index(drop=False, inplace=True)\n",
    "        stock_closing_price.set_index('Dates', inplace=True)\n",
    "        stock_closing_price.drop('level_0', axis=1, inplace=True)\n",
    "        stock_closing_price.sort_index(axis=1, inplace=True)\n",
    "        ##### Checking for missing values\n",
    "        stock_closing_price = stock_closing_price.dropna(axis='columns')\n",
    "\n",
    "        return stock_closing_price\n",
    "    \n",
    "    def group_data_by_sector(self, financial_environmental_data, financial_stock_data):\n",
    "        \"\"\"\n",
    "        Group the processed data by sector and sort by MarketCap\n",
    "\n",
    "        Parameters:\n",
    "        filter_date (str): A date string in 'yyyy-mm-dd' format to filter the final DataFrame\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: DataFrame grouped by 'GICS Sector' and 'Ticker' and sorted by 'MarketCap'\n",
    "        \"\"\"\n",
    "        # Obtain the processed data\n",
    "        #processed_data = self.process_data(filter_date=filter_date)\n",
    "\n",
    "        market_cap = pd.DataFrame(financial_environmental_data['MarketCap'])\n",
    "        market_cap.reset_index(drop=False, inplace=True)\n",
    "        market_cap.drop('Dates', axis=1, inplace=True)\n",
    "        market_cap.set_index('Ticker', inplace=True)\n",
    "        data_step1 = market_cap.T\n",
    "\n",
    "        # Rename the DataFrame columns\n",
    "        R3000list = self.rename_dataframe_columns(self.load_excel_data('Company List'), {'BBG Ticker': 'Ticker'})\n",
    "\n",
    "        keep_list = list(set(list(financial_stock_data.columns)) & set(list(data_step1.columns)))\n",
    "\n",
    "        data_step1 = data_step1[keep_list]\n",
    "        market_cap = data_step1.T.reset_index(drop=False)\n",
    "        companies_df = pd.merge(R3000list, market_cap, on='Ticker', how='inner')\n",
    "        companies_df.drop(['ISIN', 'Company'], axis=1, inplace=True)\n",
    "\n",
    "        sector_groups = pd.DataFrame(companies_df.groupby(['GICS Sector', 'Ticker'])['MarketCap'].mean()).sort_values(\n",
    "            ['GICS Sector', 'MarketCap'], ascending=[True, False])\n",
    "\n",
    "        return sector_groups\n",
    "    \n",
    "    def get_random_tickers(self, df, n):\n",
    "        \"\"\"\n",
    "        Selects n random tickers from each sector in a DataFrame\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): Sector dataframe\n",
    "        n (int): The number of random tickers to select from each sector\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: A new DataFrame containing the random tickers selected from each sector.\n",
    "        \"\"\"\n",
    "        np.random.seed(1)  \n",
    "        random_tickers = df.groupby(level=0).apply(lambda x: x.sample(n) if len(x) >= n else x).droplevel(0)\n",
    "\n",
    "        # If there are less than 100 tickers, randomly add more tickers from any sector\n",
    "        while len(random_tickers) < 100:\n",
    "            # Choose a sector\n",
    "            sector = np.random.choice(df.index.get_level_values(0).unique())\n",
    "            # Choose a ticker from the sector that is not already in the list\n",
    "            additional_ticker = df.loc[sector].loc[~df.loc[sector].index.isin(random_tickers.index)].sample(1)\n",
    "            # Preserve the sector information\n",
    "            additional_ticker.index = pd.MultiIndex.from_tuples([(sector, additional_ticker.index[0])], names = [\"Sector\", \"Ticker\"])\n",
    "            # Add the chosen ticker to the list\n",
    "            random_tickers = pd.concat([random_tickers, additional_ticker])\n",
    "\n",
    "        return random_tickers\n",
    "    \n",
    "    def get_final_data(self, random_tickers, financial_environmental_data, financial_stock_data):\n",
    "        \"\"\"\n",
    "        Filters the financial and environmental data to include only the randomly selected tickers\n",
    "        Parameters:\n",
    "        random_tickers (DataFrame): DataFrame containing randomly selected tickers from each sector\n",
    "        financial_environmental_data (DataFrame): DataFrame containing the financial and environmental data\n",
    "\n",
    "        Returns:\n",
    "        DataFrame: DataFrame containing financial, environmental, stock data and sector information for the randomly selected tickers\n",
    "        \"\"\"\n",
    "        R3000list = self.rename_dataframe_columns(self.load_excel_data('Company List'), {'BBG Ticker': 'Ticker'})\n",
    "\n",
    "        company_list = list(random_tickers.index.get_level_values('Ticker'))\n",
    "        df_final = financial_environmental_data[financial_environmental_data.index.get_level_values('Ticker').isin(company_list)]\n",
    "        df_final = pd.merge(df_final, R3000list[['Ticker', 'GICS Sector']], on='Ticker', how='left')\n",
    "        df_final['Carbon Intensity'] = (df_final['Scope1'] + df_final['Scope2Location'])/df_final['TotalAssets']\n",
    "        df_final.set_index('Ticker',inplace=True)\n",
    "        stock_prices = financial_stock_data[company_list]\n",
    "        stock_prices.sort_index(axis=1, inplace=True)\n",
    "\n",
    "        return df_final, stock_prices\n",
    "    \n",
    "    def get_descriptive_statistics(self, df_market_cap, df_stock):\n",
    "        \"\"\"\n",
    "        Descriptive statistics for a given DataFrame\n",
    "\n",
    "        Parameters:\n",
    "        df (DataFrame): DataFrame for which to compute statistics\n",
    "\n",
    "        Returns:\n",
    "        tuple: Mean historical return and covariance matrix of the DataFrame\n",
    "        \"\"\"\n",
    "        market_cap = df_market_cap['MarketCap']\n",
    "        stats = {\n",
    "            'mean': market_cap.mean(),\n",
    "            'std_dev': market_cap.std(),\n",
    "            'min': market_cap.min(),\n",
    "            'max': market_cap.max(),\n",
    "            'median': market_cap.median(),\n",
    "            'mu': mean_historical_return(df_stock, frequency=12),\n",
    "            'S': CovarianceShrinkage(df_stock, frequency=12).ledoit_wolf()\n",
    "        }\n",
    "\n",
    "        return stats\n",
    "    \n",
    "    def get_benchmark_portfolio(self, df_final, mu):\n",
    "        \"\"\"\n",
    "        Calculate benchmark portfolio using market capitalization as weights.\n",
    "\n",
    "        Parameters:\n",
    "            df_final (DataFrame): Dataframe containing financial and environmental data for the 100 stocks\n",
    "            mu (Series): Expected returns for all stocks\n",
    "            \n",
    "        Returns:\n",
    "            DataFrame: Benchmark portfolio with weights and expected stock returns\n",
    "        \"\"\"\n",
    "        market_cap = df_final[['GICS Sector','MarketCap']]\n",
    "        benchmark_portfolio = pd.DataFrame()\n",
    "        benchmark_portfolio['GICS Sector'] = market_cap['GICS Sector']\n",
    "        benchmark_portfolio['Weights'] = market_cap['MarketCap']/market_cap['MarketCap'].sum()\n",
    "        benchmark_portfolio['ExpStockReturns'] = mu * benchmark_portfolio['Weights'] * 100\n",
    "        portfolio_scores = ['Carbon Intensity', 'ESG Score', 'Environmental Score', 'Social Score', 'Governance Score']\n",
    "        for score in portfolio_scores:\n",
    "            benchmark_portfolio[f'Exp{score}'] = df_final[score] * benchmark_portfolio['Weights']\n",
    "        return benchmark_portfolio\n",
    "    \n",
    "    def get_portfolio_performance(self, portfolio_weights, mu, S, risk_free_rate=0.09):\n",
    "        \"\"\"\n",
    "        Calculate portfolio performance including expected return, volatility, sharpe ratio, and number of companies\n",
    "\n",
    "        Parameters:\n",
    "            portfolio_weights (DataFrame): Portfolio weights for each stock\n",
    "            mu (Series): Expected returns for all stocks\n",
    "            S (DataFrame): Covariance matrix of stock returns\n",
    "            risk_free_rate (float, optional): Risk-free rate. Defaults to 0.09.\n",
    "            \n",
    "        Returns:\n",
    "            dict: A dictionary containing performance metrics\n",
    "        \"\"\"\n",
    "        performance = base_optimizer.portfolio_performance(portfolio_weights['Weights'], mu, S, verbose=True,\n",
    "                                                              risk_free_rate=risk_free_rate)\n",
    "        return {\n",
    "            'Annualised_ExpReturn': performance[0]*100,\n",
    "            'Annualised_Volatility': performance[1]*100,\n",
    "            'Sharpe_Ratio': performance[2],\n",
    "            'Num_Companies': portfolio_weights[portfolio_weights['Weights'] > 0].count()['Weights']\n",
    "        }\n",
    "\n",
    "    def print_portfolio_stats(self, benchmark_performance):\n",
    "        \"\"\"\n",
    "        Print portfolio performance metrics.\n",
    "\n",
    "        Parameters:\n",
    "            benchmark_performance (dict): Portfolio performance metrics\n",
    "            \n",
    "        Returns:\n",
    "            None\n",
    "        \"\"\"\n",
    "        print(\"\\n\")\n",
    "        print(f\"Annualised Expected Return of Portfolio = {benchmark_performance['Annualised_ExpReturn']:.2f}%\")\n",
    "        print(f\"Annualised Volatility of Portfolio = {benchmark_performance['Annualised_Volatility']:.2f}\")\n",
    "        print(f\"Sharpe Ratio of Portfolio = {benchmark_performance['Sharpe_Ratio']}\")\n",
    "        print(f\"No. of companies invested in Portfolio = {benchmark_performance['Num_Companies']}\")\n",
    "\n",
    "    def get_portfolio_scores(self, portfolio_weights, df_final, is_benchmark=False):\n",
    "        \"\"\"\n",
    "        Calculate ESG scores for the portfolio\n",
    "\n",
    "        Parameters:\n",
    "            portfolio_weights (DataFrame): Portfolio weights for each stock\n",
    "            df_final (DataFrame): Final DataFrame containing all financial and environmental data of 100 stocks\n",
    "            is_benchmark (bool, optional): Whether the portfolio is a benchmark portfolio. Defaults to False.\n",
    "            \n",
    "        Returns:\n",
    "            dict: A dictionary containing portfolio ESG scores\n",
    "        \"\"\"\n",
    "        portfolio_scores = ['Carbon Intensity', 'ESG Score', 'Environmental Score', 'Social Score', 'Governance Score']\n",
    "        scores = {}\n",
    "        if is_benchmark:\n",
    "            for score in portfolio_scores:\n",
    "                modified_txt = \"Exp\" + score\n",
    "                scores[score] = portfolio_weights[modified_txt].sum()\n",
    "        else:  # If decarbonised portfolio\n",
    "            for score in portfolio_scores:\n",
    "                scores[score] = (df_final[score] * portfolio_weights).sum()\n",
    "        return scores\n",
    "\n",
    "    def print_portfolio_scores(self, portfolio_scores):\n",
    "        \"\"\"\n",
    "        Print ESG scores for the portfolio.\n",
    "\n",
    "        Parameters:\n",
    "            portfolio_scores (dict): Portfolio ESG scores\n",
    "            \n",
    "        Returns:\n",
    "            None\n",
    "        \"\"\"\n",
    "        print(\"\\n\")\n",
    "        print(f\"Portfolio Carbon Intensity =  {portfolio_scores['Carbon Intensity']:.4f} tCO2 / million$ of revenue\")\n",
    "        print(f\"Portfolio ESG Score =  {portfolio_scores['ESG Score']:.2f}\")\n",
    "        print(f\"Portfolio Environmental Score = {portfolio_scores['Environmental Score']:.2f}\")\n",
    "        print(f\"Portfolio Social Score = {portfolio_scores['Social Score']:.2f}\")\n",
    "        print(f\"Portfolio Governance Score = {portfolio_scores['Governance Score']:.2f}\")\n",
    "        print(\"\\n\")\n",
    "    \n",
    "    def market_cap_portfolio(self, df_final, mu, S, risk_free_rate=0.09):\n",
    "        \"\"\"\n",
    "        Calculate market cap weighted portfolio, its performance metrics, and ESG scores\n",
    "\n",
    "        Args:\n",
    "            df_final (DataFrame): Final DataFrame containing all financial and environmental data of 100 stocks\n",
    "            mu (Series): Expected returns for all stocks\n",
    "            S (DataFrame): Covariance matrix of stock returns\n",
    "            risk_free_rate (float, optional): Risk-free rate. Defaults to 0.09\n",
    "            \n",
    "        Returns:\n",
    "            Tuple: A tuple containing portfolio weights DataFrame, performance dictionary, and ESG scores dictionary\n",
    "        \"\"\"\n",
    "        benchmark_portfolio = self.get_benchmark_portfolio(df_final, mu)\n",
    "        benchmark_performance = self.get_portfolio_performance(benchmark_portfolio, mu, S, risk_free_rate)\n",
    "        self.print_portfolio_stats(benchmark_performance)\n",
    "        benchmark_scores = self.get_portfolio_scores(benchmark_portfolio, df_final, is_benchmark=True)\n",
    "        self.print_portfolio_scores(benchmark_scores)\n",
    "        return benchmark_portfolio, benchmark_performance, benchmark_scores\n",
    "        \n",
    "    def _objective_value(self, w, obj):\n",
    "        if isinstance(w, np.ndarray):\n",
    "            if np.isscalar(obj):\n",
    "                return obj\n",
    "            elif np.isscalar(obj.value):\n",
    "                return obj.value\n",
    "            else:\n",
    "                return obj.value.item()\n",
    "        else:\n",
    "            return obj\n",
    "\n",
    "    def ex_ante_tracking_error(self, w, cov_matrix, benchmark_weights):\n",
    "        relative_weights = w - benchmark_weights\n",
    "        tracking_error = cp.quad_form(relative_weights, cov_matrix)\n",
    "        return self._objective_value(w, tracking_error)\n",
    "    \n",
    "    def sector_balance(self, final_data, random_tickers ):\n",
    "        \"\"\"\n",
    "        Balance the sector composition for the portfolio.\n",
    "\n",
    "        Parameters:\n",
    "            final_data (DataFrame): DataFrame containing all financial and environmental data\n",
    "            random_tickers (DataFrame): DataFrame containing random stock tickers\n",
    "            \n",
    "        Returns:\n",
    "            Tuple: A tuple containing sector mapping dictionary, lower bound for each sector, and upper bound for each sector\n",
    "        \"\"\"\n",
    "        ticker_counts = random_tickers.groupby('GICS Sector').size()/100\n",
    "        # Sector Mapping as a Dictionary\n",
    "        sector_mapping = final_data.reset_index(drop=False)[['GICS Sector','Ticker']]\n",
    "        sector_mapping.set_index('Ticker',inplace=True)\n",
    "        sector_mapping_dict = sector_mapping.to_dict()['GICS Sector']\n",
    "        lower = 0.7    # Lower Bound of change (-30%)\n",
    "        upper = 1.3    # Upper Bound of change (+30%)\n",
    "        sector_lower = {}\n",
    "        sector_upper = {}\n",
    "        for sector, count in ticker_counts.items():\n",
    "            sector_lower[sector] = count * lower\n",
    "            sector_upper[sector] = count * upper\n",
    "        return sector_mapping_dict, sector_lower, sector_upper\n",
    "         \n",
    "    def decarbonised_portfolio(self, total_initial_carbon_intensity, intensity_rate, base_optimizer, stock_prices, \n",
    "                               mu, S, carbon_intensity, df_final, benchmark_weights, random_tickers=None, \n",
    "                               is_portfolio4 = False ):\n",
    "        \"\"\"\n",
    "        Creates a portfolio with decarbonisation constraints. It takes the maximum tracking error, \n",
    "        a target carbon intensity and an optimiser as inputs and returns a DataFrame with the optimal portfolio \n",
    "        weights that minimises the portfolio's carbon intensity subject to the constraints\n",
    "\n",
    "        Parameters:\n",
    "        total_initial_carbon_intensity (float):, initial portfolio carbon intensity\n",
    "        intensity_rate (float): proportion to reduce the initial carbon intensity by to get the target carbon intensity\n",
    "        base_optimizer (object): instance of the BaseConvexOptimizer class to solve the optimisation problem\n",
    "        stock_prices (DataFrame): DataFrame containing stock prices data\n",
    "        mu (np.array): Expected returns of each stock\n",
    "        S (np.array): Covariance matrix of the returns of the stocks\n",
    "        carbon_intensity (np.array): Carbon intensity of each stock\n",
    "        df_final (DataFrame): Final DataFrame containing all financial and environmental data of 100 stocks\n",
    "        benchmark_weights (np.array): Weights of the benchmark portfolio\n",
    "        random_tickers (list, optional): List of random tickers for portfolio 4\n",
    "        is_portfolio4 (bool): Flag to check if it's portfolio 4\n",
    "\n",
    "        Returns:\n",
    "        DC_weights (DataFrame): Weights of the decarbonised portfolio\n",
    "        DC_performance (dict): Performance of the decarbonised portfolio\n",
    "        DC_scores (dict): Scores of the decarbonised portfolio\n",
    "        \"\"\"\n",
    "\n",
    "        \n",
    "        # Setting up maximum tracking error at 1%\n",
    "        max_tracking_error = 0.01\n",
    "        # Target carbon intensity to be 50% (default) of initial\n",
    "        target_carbon_intensity = total_initial_carbon_intensity * intensity_rate\n",
    "\n",
    "        # Create the EfficientFrontier object\n",
    "        efficient_dc = base_optimizer.BaseConvexOptimizer(\n",
    "            n_assets=len(stock_prices.columns),\n",
    "            tickers=stock_prices.columns,\n",
    "            weight_bounds=(0, 1)\n",
    "        )\n",
    "        \n",
    "        sector_mapping_dict = None\n",
    "        sector_lower = None\n",
    "        sector_upper = None\n",
    "        \n",
    "        if is_portfolio4:\n",
    "            efficient_dc.add_constraint(lambda w: w @ carbon_intensity == target_carbon_intensity)\n",
    "            efficient_dc.add_constraint(lambda w: cp.sum(w) == 1)\n",
    "            sector_mapping_dict, sector_lower, sector_upper = self.sector_balance(df_final, random_tickers)\n",
    "            efficient_dc.add_sector_constraints(sector_mapper = sector_mapping_dict, \n",
    "                               sector_lower = sector_lower,  # Current mapping * 0.7 (-30%)\n",
    "                               sector_upper = sector_upper)  # Current mapping * 1.3 (+30%)\n",
    "        else:\n",
    "            # Add the tracking error constraint\n",
    "\n",
    "            efficient_dc.add_constraint(lambda w: self.ex_ante_tracking_error(w, S, benchmark_weights) - max_tracking_error <= 0)\n",
    "\n",
    "            # Adding Carbon Constraint\n",
    "            efficient_dc.add_constraint(lambda w: w @ carbon_intensity == target_carbon_intensity)\n",
    "\n",
    "            # Adding constraint for weights to sum to 1\n",
    "            efficient_dc.add_constraint(lambda w: cp.sum(w) == 1)\n",
    "        \n",
    "\n",
    "        # Adding the decarbonisation objective function\n",
    "        efficient_dc.convex_objective(lambda w: w @ carbon_intensity, weights_sum_to_one=True)\n",
    "\n",
    "        # Compute the optimal weights\n",
    "        cleaned_weights_dcp = efficient_dc.clean_weights()\n",
    "        DC_weights = pd.DataFrame(cleaned_weights_dcp, columns=cleaned_weights_dcp.keys(), index=['Weights']).T\n",
    "        DC_weights['GICS Sector'] = df_final['GICS Sector']\n",
    "        # Calculate and print portfolio performance and scores\n",
    "        DC_performance = self.get_portfolio_performance(DC_weights, mu, S)\n",
    "        self.print_portfolio_stats(DC_performance)\n",
    "        DC_scores = self.get_portfolio_scores(DC_weights['Weights'], df_final, is_benchmark=False)\n",
    "        self.print_portfolio_scores(DC_scores)\n",
    "        return DC_weights, DC_performance, DC_scores\n",
    "\n",
    "    def mean_variance_efficient_portfolio(self, mu, S, df_final, benchmark_weights, total_initial_carbon_intensity, carbon_intensity,\n",
    "                                          is_decarbonised=False, risk_free_rate=0.09):\n",
    "        \"\"\"\n",
    "        Creates a mean-variance efficient portfolio with or without decarbonisation constraints. \n",
    "        It takes expected returns, a covariance matrix, a DataFrame of processed data and benchmark weights as inputs \n",
    "        and returns a DataFrame with the optimal portfolio weights that maximise the Sharpe ratio\n",
    "\n",
    "        Parameters:\n",
    "        mu (np.array): Expected returns of each stock.\n",
    "        S (np.array): Covariance matrix of the returns of the stocks.\n",
    "        df_final (DataFrame): Final DataFrame after cleaning and pre-processing.\n",
    "        benchmark_weights (np.array): Weights of the benchmark portfolio.\n",
    "        total_initial_carbon_intensity (float): Initial portfolio carbon intensity.\n",
    "        carbon_intensity (np.array): Carbon intensity of each stock.\n",
    "        is_decarbonised (bool): Whether to include decarbonisation constraints.\n",
    "        risk_free_rate (float): Risk-free rate used to calculate Sharpe ratio. Default is 0.09.\n",
    "\n",
    "        Returns:\n",
    "        MVP_weights (DataFrame): Weights of the mean-variance efficient portfolio.\n",
    "        MVP_perf (dict): Performance of the mean-variance efficient portfolio.\n",
    "        MVP_scores (dict): Scores of the mean-variance efficient portfolio.\n",
    "        \"\"\"\n",
    "\n",
    "\n",
    "        efficient_frontier = EfficientFrontier(mu, S, weight_bounds=(0, 1))\n",
    "\n",
    "        if is_decarbonised:\n",
    "\n",
    "            target_carbon_intensity = total_initial_carbon_intensity * 0.5\n",
    "            efficient_frontier.add_constraint(lambda w: w @ carbon_intensity == target_carbon_intensity)\n",
    "            efficient_frontier.add_constraint(lambda w: cp.sum(w) == 1)\n",
    "            raw_weights = efficient_frontier.max_sharpe(risk_free_rate=0.09)\n",
    "\n",
    "        else:\n",
    "            efficient_frontier.add_constraint(lambda w: w >= 0)\n",
    "            efficient_frontier.add_constraint(lambda w: w <= 1)\n",
    "            efficient_frontier.add_constraint(lambda w: cp.sum(w) == 1)\n",
    "            raw_weights = efficient_frontier.max_sharpe(risk_free_rate=risk_free_rate)\n",
    "\n",
    "        cleaned_weights = efficient_frontier.clean_weights()\n",
    "        MVP_weights = pd.DataFrame(cleaned_weights, columns=cleaned_weights.keys(), index=['Weights']).T\n",
    "        MVP_weights['GICS Sector'] = df_final['GICS Sector']\n",
    "        MVP_perf = self.get_portfolio_performance(MVP_weights, mu, S)\n",
    "        self.print_portfolio_stats(MVP_perf)\n",
    "        MVP_scores = self.get_portfolio_scores(MVP_weights['Weights'], df_final, is_benchmark=False)\n",
    "        self.print_portfolio_scores(MVP_scores)\n",
    "        return MVP_weights, MVP_perf, MVP_scores\n",
    "\n",
    "    def create_esg_performance_df(self, benchmark_scores, dcp_1_scores, dcp_2_scores, dcp_3_scores, dcp_4_scores, \n",
    "                                  mvp_scores, mvp_dcp_5_scores, portfolios_list):\n",
    "        \n",
    "        \"\"\"\n",
    "        Creates a DataFrame with the ESG performance of different portfolios. It takes scores of \n",
    "        different portfolios and a list of portfolio names as inputs and returns a DataFrame with the ESG scores.\n",
    "\n",
    "        Parameters:\n",
    "        benchmark_scores (dict): Scores of the benchmark portfolio.\n",
    "        dcp_1_scores (dict): Scores of decarbonised portfolio 1.\n",
    "        dcp_2_scores (dict): Scores of decarbonised portfolio 2.\n",
    "        dcp_3_scores (dict): Scores of decarbonised portfolio 3.\n",
    "        dcp_4_scores (dict): Scores of decarbonised portfolio 4.\n",
    "        mvp_scores (dict): Scores of the mean-variance portfolio.\n",
    "        mvp_dcp_5_scores (dict): Scores of decarbonised portfolio 5.\n",
    "        portfolios_list (list): Names of the portfolios.\n",
    "\n",
    "        Returns:\n",
    "        Portfolio_ESGPerf (DataFrame): ESG performance of the portfolios.\n",
    "        \"\"\"\n",
    "        \n",
    "        Portfolio_ESGPerf = pd.DataFrame(columns=['Mean Annual Emission', 'Avg ESG Score', 'Avg Environment Score',\n",
    "                                                  'Avg Governance Score', 'Avg Social Score'], index=portfolios_list)\n",
    "\n",
    "        Portfolio_ESGPerf['Mean Annual Emission'] = [benchmark_scores['Carbon Intensity'], dcp_1_scores['Carbon Intensity'], \n",
    "                                                     dcp_2_scores['Carbon Intensity'], dcp_3_scores['Carbon Intensity'], \n",
    "                                                     dcp_4_scores['Carbon Intensity'], mvp_scores['Carbon Intensity'], \n",
    "                                                     mvp_dcp_5_scores['Carbon Intensity']]\n",
    "        \n",
    "        Portfolio_ESGPerf['Avg ESG Score'] = [benchmark_scores['ESG Score'], dcp_1_scores['ESG Score'], \n",
    "                                                     dcp_2_scores['ESG Score'], dcp_3_scores['ESG Score'], \n",
    "                                                     dcp_4_scores['ESG Score'], mvp_scores['ESG Score'], \n",
    "                                                     mvp_dcp_5_scores['ESG Score']]\n",
    "        \n",
    "        Portfolio_ESGPerf['Avg Environment Score'] = [benchmark_scores['Environmental Score'], dcp_1_scores['Environmental Score'], \n",
    "                                                     dcp_2_scores['Environmental Score'], dcp_3_scores['Environmental Score'], \n",
    "                                                     dcp_4_scores['Environmental Score'], mvp_scores['Environmental Score'], \n",
    "                                                     mvp_dcp_5_scores['Environmental Score']]\n",
    "        \n",
    "        Portfolio_ESGPerf['Avg Governance Score'] = [benchmark_scores['Governance Score'], dcp_1_scores['Governance Score'], \n",
    "                                                     dcp_2_scores['Governance Score'], dcp_3_scores['Governance Score'], \n",
    "                                                     dcp_4_scores['Governance Score'], mvp_scores['Governance Score'], \n",
    "                                                     mvp_dcp_5_scores['Governance Score']]\n",
    "        \n",
    "        Portfolio_ESGPerf['Avg Social Score'] = [benchmark_scores['Social Score'], dcp_1_scores['Social Score'], \n",
    "                                                     dcp_2_scores['Social Score'], dcp_3_scores['Social Score'], \n",
    "                                                     dcp_4_scores['Social Score'], mvp_scores['Social Score'], \n",
    "                                                     mvp_dcp_5_scores['Social Score']]\n",
    "\n",
    "        return Portfolio_ESGPerf\n",
    "    \n",
    "    def sector_analysis(self, benchmark_portfolio, DCP1_weights, DCP2_weights, DCP3_weights, DCP4_weights, MVP_weights, \n",
    "                        DCP5_weights):\n",
    "        \n",
    "        \"\"\"\n",
    "        Creates a DataFrame with the sector composition of different portfolios. It takes portfolio \n",
    "        weights as inputs and returns a DataFrame with the sector composition.\n",
    "\n",
    "        Parameters:\n",
    "        benchmark_portfolio (DataFrame): Weights of the benchmark portfolio\n",
    "        DCP1_weights (DataFrame): Weights of decarbonised portfolio 1\n",
    "        DCP2_weights (DataFrame): Weights of decarbonised portfolio 2\n",
    "        DCP3_weights (DataFrame): Weights of decarbonised portfolio 3\n",
    "        DCP4_weights (DataFrame): Weights of decarbonised portfolio 4\n",
    "        MVP_weights (DataFrame): Weights of the mean-variance portfolio\n",
    "        DCP5_weights (DataFrame): Weights of decarbonised portfolio 5\n",
    "\n",
    "        Returns:\n",
    "        sector_composition (DataFrame): Sector composition of the portfolios\n",
    "        sector_composition_grouped (DataFrame): Grouped sector composition of the portfolios\n",
    "        \"\"\"\n",
    "\n",
    "        \n",
    "        sector_composition = benchmark_portfolio[['GICS Sector','Weights']].copy()\n",
    "        sector_composition.rename(columns={'Weights':'Benchmark'},inplace=True)\n",
    "        sector_composition['DC_Portfolio1'] = DCP1_weights['Weights']\n",
    "        sector_composition['DC_Portfolio2'] = DCP2_weights['Weights']\n",
    "        sector_composition['DC_Portfolio3'] = DCP3_weights['Weights']\n",
    "        sector_composition['DC_Portfolio4'] = DCP4_weights['Weights']\n",
    "        sector_composition['MV_Portfolio']  = MVP_weights['Weights']\n",
    "        sector_composition['DC_Portfolio5'] = DCP5_weights['Weights']\n",
    "        sector_composition_grouped = sector_composition.groupby('GICS Sector')['Benchmark','DC_Portfolio1','DC_Portfolio2','DC_Portfolio3','DC_Portfolio4','MV_Portfolio','DC_Portfolio5'].sum()\n",
    "        return sector_composition, sector_composition_grouped\n",
    "    \n",
    "    def create_and_analyze_portfolios(self, base_optimizer, portfolio_num, portfolios_list):\n",
    "        \n",
    "        \"\"\"\n",
    "        Generates and analyzes various types of portfolios, including benchmark and decarbonised portfolios. \n",
    "        This function returns the portfolio weights, performance statistics, ESG performance, and sector analysis of each portfolio\n",
    "\n",
    "        Parameters:\n",
    "        base_optimizer (object): Instance of the BaseConvexOptimizer class to solve the optimisation problem\n",
    "        portfolio_num (int): Number of random portfolios to create for the sector-balanced decarbonised portfolio\n",
    "        portfolios_list (list): Names of the portfolios\n",
    "\n",
    "        Returns:\n",
    "        None. This method prints out the portfolio statistics, ESG performance, and sector composition of each portfolio\n",
    "        \"\"\"\n",
    "\n",
    "        \n",
    "        financial_environmental_data = self.process_data()\n",
    "        financial_stock_data = self.process_stock_price_data()\n",
    "        final_data = self.group_data_by_sector(financial_environmental_data, financial_stock_data)\n",
    "        random_tickers = self.get_random_tickers(final_data, portfolio_num)\n",
    "        random_tickers = random_tickers.dropna()\n",
    "        df_final, stock_prices = self.get_final_data(random_tickers, financial_environmental_data, financial_stock_data)\n",
    "        descriptive_statistics = self.get_descriptive_statistics(df_final, stock_prices)\n",
    "        \n",
    "        print(\"----------BENCHMARK PORTFOLIO: MARKET CAP-WEIGHTED PORTFOLIO----------\\n\")\n",
    "        benchmark_portfolio, benchmark_perf, benchmark_scores = self.market_cap_portfolio(df_final, descriptive_statistics['mu'], descriptive_statistics['S'])\n",
    "        \n",
    "        benchmark_weights = benchmark_portfolio['Weights'].values\n",
    "        carbon_intensity = df_final['Carbon Intensity'].values\n",
    "        total_initial_carbon_intensity = (benchmark_weights * carbon_intensity).sum()\n",
    "        \n",
    "        print(\"----------DECARBONISED PORTFOLIO 1----------\\n\")\n",
    "        dcp_1_weights, dcp1_perf, dcp_1_scores = self.decarbonised_portfolio(total_initial_carbon_intensity, 0.5, base_optimizer, \n",
    "                                                            stock_prices, descriptive_statistics['mu'], \n",
    "                                                            descriptive_statistics['S'], carbon_intensity, df_final, benchmark_weights)\n",
    "\n",
    "        print(\"----------DECARBONISED PORTFOLIO 2----------\\n\")\n",
    "        dcp_2_weights, dcp2_perf, dcp_2_scores = self.decarbonised_portfolio(total_initial_carbon_intensity, 0.75, base_optimizer, \n",
    "                                                            stock_prices, descriptive_statistics['mu'], \n",
    "                                                            descriptive_statistics['S'], carbon_intensity, df_final, benchmark_weights)\n",
    "\n",
    "        print(\"----------DECARBONISED PORTFOLIO 3----------\\n\")\n",
    "        dcp_3_weights, dcp3_perf, dcp_3_scores = self.decarbonised_portfolio(total_initial_carbon_intensity, 0.9, base_optimizer, \n",
    "                                                            stock_prices, descriptive_statistics['mu'], \n",
    "                                                            descriptive_statistics['S'], carbon_intensity, df_final, benchmark_weights)\n",
    "\n",
    "        print(\"----------DECARBONISED PORTFOLIO 4----------\\n\")\n",
    "        dcp_4_weights, dcp4_perf, dcp_4_scores = self.decarbonised_portfolio(total_initial_carbon_intensity, 0.5, base_optimizer,\n",
    "                                                            stock_prices, descriptive_statistics['mu'], \n",
    "                                                            descriptive_statistics['S'], carbon_intensity, \n",
    "                                                            df_final, benchmark_weights, random_tickers, is_portfolio4=True)\n",
    "\n",
    "        print(\"----------MEAN-VARIANCE EFFICIENT PORTFOLIO----------\\n\")\n",
    "        mvp_weights, mvp_perf, mvp_scores = self.mean_variance_efficient_portfolio(descriptive_statistics['mu'], descriptive_statistics['S'], \n",
    "                                                                df_final, benchmark_weights, total_initial_carbon_intensity, \n",
    "                                                                carbon_intensity, False)\n",
    "\n",
    "        print(\"----------MEAN-VARIANCE EFFICIENT PORTFOLIO: DECARBONISED PORTFOLIO 5----------\\n\")\n",
    "        mvp_dcp_5_weights, mvp_dcp_5_perf, mvp_dcp_5_scores = self.mean_variance_efficient_portfolio(descriptive_statistics['mu'], descriptive_statistics['S'],\n",
    "                                                                                df_final, benchmark_weights, total_initial_carbon_intensity, \n",
    "                                                                                carbon_intensity, True)\n",
    "\n",
    "        print(\"----------ESG PERFORMANCE STATS----------\\n\")\n",
    "        print(self.create_esg_performance_df(benchmark_scores, dcp_1_scores, dcp_2_scores, dcp_3_scores, \n",
    "                                           dcp_4_scores, mvp_scores, mvp_dcp_5_scores, portfolios_list))\n",
    "        \n",
    "        print(\"----------SECTOR/INDUSTRY COMPOSITION FOR ALL CONSTRUCTED PORTFOLIOS----------\\n\")\n",
    "        sector_composition_companies, sector_composition_grouped = self.sector_analysis(benchmark_portfolio, \n",
    "                                                                                                dcp_1_weights, dcp_2_weights, \n",
    "                                                                                                dcp_3_weights, dcp_4_weights, \n",
    "                                                                                                mvp_weights, mvp_dcp_5_weights)\n",
    "\n",
    "        print(\"\\n Sector Analysis by company\")\n",
    "        print(sector_composition_companies)\n",
    "        print(\"\\n Sector Analysis by group\")\n",
    "        print(sector_composition_grouped)\n",
    "\n",
    "        portfolio_stats_df = pd.DataFrame(columns=['Companies Invested','Sharpe Ratio', 'Monthly ExpReturn',\n",
    "                                                    'Annual ExpReturn', 'Volatility','Monthly StdDev','Annual StdDev'], \n",
    "                                           index=portfolios_list)\n",
    "\n",
    "        portfolio_stats_df['Companies Invested'] = [benchmark_perf['Num_Companies'], dcp1_perf['Num_Companies'], dcp2_perf['Num_Companies'], dcp3_perf['Num_Companies'], dcp4_perf['Num_Companies'], mvp_perf['Num_Companies'], mvp_dcp_5_perf['Num_Companies']]\n",
    "        portfolio_stats_df['Annual ExpReturn'] = [benchmark_perf['Annualised_ExpReturn'], dcp1_perf['Annualised_ExpReturn'], dcp2_perf['Annualised_ExpReturn'], dcp3_perf['Annualised_ExpReturn'], dcp4_perf['Annualised_ExpReturn'], mvp_perf['Annualised_ExpReturn'], mvp_dcp_5_perf['Annualised_ExpReturn']]\n",
    "        portfolio_stats_df['Volatility'] = [benchmark_perf['Annualised_Volatility'], dcp1_perf['Annualised_Volatility'], dcp2_perf['Annualised_Volatility'], dcp3_perf['Annualised_Volatility'], dcp4_perf['Annualised_Volatility'], mvp_perf['Annualised_Volatility'], mvp_dcp_5_perf['Annualised_Volatility']]\n",
    "        portfolio_stats_df['Sharpe Ratio'] = [benchmark_perf['Sharpe_Ratio'], dcp1_perf['Sharpe_Ratio'], dcp2_perf['Sharpe_Ratio'], dcp3_perf['Sharpe_Ratio'], dcp4_perf['Sharpe_Ratio'], mvp_perf['Sharpe_Ratio'], mvp_dcp_5_perf['Sharpe_Ratio']]\n",
    "\n",
    "        portfolio_stats_df['Monthly ExpReturn'] = portfolio_stats_df['Annual ExpReturn']/12\n",
    "        portfolio_stats_df['Annual StdDev'] = np.sqrt(portfolio_stats_df['Volatility'])\n",
    "        portfolio_stats_df['Monthly StdDev'] = portfolio_stats_df['Annual StdDev']/np.sqrt(12)\n",
    "\n",
    "        print(\"\\n----------PORTFOLIO ANALYSIS----------\\n\")\n",
    "        print(portfolio_stats_df)\n",
    "    \n",
    "    def run_portfolio_decarbonisation(self, base_optimizer):\n",
    "        \n",
    "        \"\"\"\n",
    "        Runs the portfolio decarbonisation process. This function creates and analyses various types of portfolios \n",
    "        and displays the ESG performance and sector analysis of each portfolio\n",
    "\n",
    "        Parameters:\n",
    "        base_optimizer (object): Instance of the BaseConvexOptimizer class to solve the optimisation problem.\n",
    "\n",
    "        Returns:\n",
    "        None. This method prints out the portfolio statistics, ESG performance, and sector composition of each portfolio.\n",
    "        \"\"\"\n",
    "        \n",
    "        portfolios_list = ['Benchmark Market Cap-Based Portfolio', 'Decarbonised Portfolio1 (50% reduction)',\n",
    "                            'Decarbonised Portfolio2 (25% reduction)', 'Decarbonised Portfolio3 (10% reduction)',\n",
    "                            'Decarbonised Portfolio4 (50%, Sector-Balanced)', 'Mean-Variance Portfolio',\n",
    "                            'Decarbonised Mean-Variance Portfolio']\n",
    "        \n",
    "        self.create_and_analyze_portfolios(base_optimizer, 9, portfolios_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "9f70ee9f",
   "metadata": {
    "code_folding": [],
    "run_control": {
     "marked": false
    },
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "----------BENCHMARK PORTFOLIO: MARKET CAP-WEIGHTED PORTFOLIO----------\n",
      "\n",
      "Expected annual return: 9.8%\n",
      "Annual volatility: 15.1%\n",
      "Sharpe Ratio: 0.06\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 9.84%\n",
      "Annualised Volatility of Portfolio = 15.05\n",
      "Sharpe Ratio of Portfolio = 0.05586386019581172\n",
      "No. of companies invested in Portfolio = 100\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.1159 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  59.36\n",
      "Portfolio Environmental Score = 48.55\n",
      "Portfolio Social Score = 37.19\n",
      "Portfolio Governance Score = 92.22\n",
      "\n",
      "\n",
      "----------DECARBONISED PORTFOLIO 1----------\n",
      "\n",
      "Expected annual return: 5.3%\n",
      "Annual volatility: 18.4%\n",
      "Sharpe Ratio: -0.20\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 5.29%\n",
      "Annualised Volatility of Portfolio = 18.36\n",
      "Sharpe Ratio of Portfolio = -0.20226056402331394\n",
      "No. of companies invested in Portfolio = 100\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.0579 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  53.90\n",
      "Portfolio Environmental Score = 38.37\n",
      "Portfolio Social Score = 32.58\n",
      "Portfolio Governance Score = 90.62\n",
      "\n",
      "\n",
      "----------DECARBONISED PORTFOLIO 2----------\n",
      "\n",
      "Expected annual return: 5.0%\n",
      "Annual volatility: 18.6%\n",
      "Sharpe Ratio: -0.21\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 5.03%\n",
      "Annualised Volatility of Portfolio = 18.57\n",
      "Sharpe Ratio of Portfolio = -0.21385263753458342\n",
      "No. of companies invested in Portfolio = 100\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.0870 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  54.46\n",
      "Portfolio Environmental Score = 39.43\n",
      "Portfolio Social Score = 33.09\n",
      "Portfolio Governance Score = 90.72\n",
      "\n",
      "\n",
      "----------DECARBONISED PORTFOLIO 3----------\n",
      "\n",
      "Expected annual return: 4.9%\n",
      "Annual volatility: 18.7%\n",
      "Sharpe Ratio: -0.22\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 4.95%\n",
      "Annualised Volatility of Portfolio = 18.67\n",
      "Sharpe Ratio of Portfolio = -0.21705482704893306\n",
      "No. of companies invested in Portfolio = 100\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.1044 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  54.68\n",
      "Portfolio Environmental Score = 39.88\n",
      "Portfolio Social Score = 33.28\n",
      "Portfolio Governance Score = 90.76\n",
      "\n",
      "\n",
      "----------DECARBONISED PORTFOLIO 4----------\n",
      "\n",
      "Expected annual return: 4.8%\n",
      "Annual volatility: 19.0%\n",
      "Sharpe Ratio: -0.22\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 4.81%\n",
      "Annualised Volatility of Portfolio = 18.97\n",
      "Sharpe Ratio of Portfolio = -0.22078256068504923\n",
      "No. of companies invested in Portfolio = 100\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.0580 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  53.74\n",
      "Portfolio Environmental Score = 37.98\n",
      "Portfolio Social Score = 32.34\n",
      "Portfolio Governance Score = 90.76\n",
      "\n",
      "\n",
      "----------MEAN-VARIANCE EFFICIENT PORTFOLIO----------\n",
      "\n",
      "Expected annual return: 26.7%\n",
      "Annual volatility: 16.9%\n",
      "Sharpe Ratio: 1.05\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 26.66%\n",
      "Annualised Volatility of Portfolio = 16.90\n",
      "Sharpe Ratio of Portfolio = 1.0450930273504717\n",
      "No. of companies invested in Portfolio = 6\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.0488 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  55.46\n",
      "Portfolio Environmental Score = 50.11\n",
      "Portfolio Social Score = 26.20\n",
      "Portfolio Governance Score = 89.94\n",
      "\n",
      "\n",
      "----------MEAN-VARIANCE EFFICIENT PORTFOLIO: DECARBONISED PORTFOLIO 5----------\n",
      "\n",
      "Expected annual return: 26.4%\n",
      "Annual volatility: 16.7%\n",
      "Sharpe Ratio: 1.04\n",
      "\n",
      "\n",
      "Annualised Expected Return of Portfolio = 26.44%\n",
      "Annualised Volatility of Portfolio = 16.71\n",
      "Sharpe Ratio of Portfolio = 1.043628237015723\n",
      "No. of companies invested in Portfolio = 7\n",
      "\n",
      "\n",
      "Portfolio Carbon Intensity =  0.0580 tCO2 / million$ of revenue\n",
      "Portfolio ESG Score =  55.84\n",
      "Portfolio Environmental Score = 50.76\n",
      "Portfolio Social Score = 26.33\n",
      "Portfolio Governance Score = 90.28\n",
      "\n",
      "\n",
      "----------ESG PERFORMANCE STATS----------\n",
      "\n",
      "                                                Mean Annual Emission  \\\n",
      "Benchmark Market Cap-Based Portfolio                        0.115927   \n",
      "Decarbonised Portfolio1 (50% reduction)                     0.057950   \n",
      "Decarbonised Portfolio2 (25% reduction)                     0.086956   \n",
      "Decarbonised Portfolio3 (10% reduction)                     0.104355   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)              0.057973   \n",
      "Mean-Variance Portfolio                                     0.048791   \n",
      "Decarbonised Mean-Variance Portfolio                        0.057967   \n",
      "\n",
      "                                                Avg ESG Score  \\\n",
      "Benchmark Market Cap-Based Portfolio                59.363524   \n",
      "Decarbonised Portfolio1 (50% reduction)             53.903887   \n",
      "Decarbonised Portfolio2 (25% reduction)             54.459823   \n",
      "Decarbonised Portfolio3 (10% reduction)             54.684380   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)      53.741045   \n",
      "Mean-Variance Portfolio                             55.461650   \n",
      "Decarbonised Mean-Variance Portfolio                55.836072   \n",
      "\n",
      "                                                Avg Environment Score  \\\n",
      "Benchmark Market Cap-Based Portfolio                        48.553987   \n",
      "Decarbonised Portfolio1 (50% reduction)                     38.367330   \n",
      "Decarbonised Portfolio2 (25% reduction)                     39.432653   \n",
      "Decarbonised Portfolio3 (10% reduction)                     39.877943   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)              37.981755   \n",
      "Mean-Variance Portfolio                                     50.105207   \n",
      "Decarbonised Mean-Variance Portfolio                        50.757754   \n",
      "\n",
      "                                                Avg Governance Score  \\\n",
      "Benchmark Market Cap-Based Portfolio                       92.217445   \n",
      "Decarbonised Portfolio1 (50% reduction)                    90.618979   \n",
      "Decarbonised Portfolio2 (25% reduction)                    90.718727   \n",
      "Decarbonised Portfolio3 (10% reduction)                    90.758080   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)             90.760380   \n",
      "Mean-Variance Portfolio                                    89.942902   \n",
      "Decarbonised Mean-Variance Portfolio                       90.276174   \n",
      "\n",
      "                                                Avg Social Score  \n",
      "Benchmark Market Cap-Based Portfolio                   37.189886  \n",
      "Decarbonised Portfolio1 (50% reduction)                32.584039  \n",
      "Decarbonised Portfolio2 (25% reduction)                33.088247  \n",
      "Decarbonised Portfolio3 (10% reduction)                33.277857  \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)         32.338599  \n",
      "Mean-Variance Portfolio                                26.195680  \n",
      "Decarbonised Mean-Variance Portfolio                   26.333051  \n",
      "----------SECTOR/INDUSTRY COMPOSITION FOR ALL CONSTRUCTED PORTFOLIOS----------\n",
      "\n",
      "\n",
      " Sector Analysis by company\n",
      "                           GICS Sector  Benchmark  DC_Portfolio1  \\\n",
      "Ticker                                                             \n",
      "AAL UW Equity              Industrials   0.002432        0.00466   \n",
      "ADNT UN Equity             Industrials   0.000774        0.00992   \n",
      "AES UN Equity                Utilities   0.005652        0.00159   \n",
      "AGR UN Equity                Utilities   0.004883        0.01094   \n",
      "ALL UN Equity               Financials   0.010480        0.01331   \n",
      "...                                ...        ...            ...   \n",
      "WRK UN Equity                Materials   0.002309        0.00637   \n",
      "WU UN Equity                Financials   0.001511        0.01282   \n",
      "X UN Equity                  Materials   0.001681        0.00070   \n",
      "YUM UN Equity   Consumer Discretionary   0.010539        0.01111   \n",
      "ZD UW Equity    Communication Services   0.001099        0.01230   \n",
      "\n",
      "                DC_Portfolio2  DC_Portfolio3  DC_Portfolio4  MV_Portfolio  \\\n",
      "Ticker                                                                      \n",
      "AAL UW Equity         0.00725        0.00821        0.00369           0.0   \n",
      "ADNT UN Equity        0.00977        0.00963        0.01299           0.0   \n",
      "AES UN Equity         0.00470        0.00639        0.00136           0.0   \n",
      "AGR UN Equity         0.01103        0.01094        0.01442           0.0   \n",
      "ALL UN Equity         0.01164        0.01115        0.01053           0.0   \n",
      "...                       ...            ...            ...           ...   \n",
      "WRK UN Equity         0.00835        0.00901        0.00773           0.0   \n",
      "WU UN Equity          0.01142        0.01099        0.01050           0.0   \n",
      "X UN Equity           0.00302        0.00461        0.00093           0.0   \n",
      "YUM UN Equity         0.01093        0.01074        0.01059           0.0   \n",
      "ZD UW Equity          0.01106        0.01070        0.01108           0.0   \n",
      "\n",
      "                DC_Portfolio5  \n",
      "Ticker                         \n",
      "AAL UW Equity         0.00000  \n",
      "ADNT UN Equity        0.00000  \n",
      "AES UN Equity         0.00245  \n",
      "AGR UN Equity         0.00000  \n",
      "ALL UN Equity         0.00000  \n",
      "...                       ...  \n",
      "WRK UN Equity         0.00000  \n",
      "WU UN Equity          0.00000  \n",
      "X UN Equity           0.00000  \n",
      "YUM UN Equity         0.00000  \n",
      "ZD UW Equity          0.00000  \n",
      "\n",
      "[100 rows x 8 columns]\n",
      "\n",
      " Sector Analysis by group\n",
      "                        Benchmark  DC_Portfolio1  DC_Portfolio2  \\\n",
      "GICS Sector                                                       \n",
      "Communication Services   0.087722        0.11706        0.10894   \n",
      "Consumer Discretionary   0.055659        0.09694        0.09475   \n",
      "Consumer Staples         0.214090        0.09293        0.09559   \n",
      "Energy                   0.101449        0.06970        0.08078   \n",
      "Financials               0.059923        0.11141        0.09964   \n",
      "Health Care              0.115267        0.11223        0.10223   \n",
      "Industrials              0.071283        0.07491        0.08270   \n",
      "Information Technology   0.120034        0.10777        0.10009   \n",
      "Materials                0.014195        0.04310        0.05563   \n",
      "Real Estate              0.039059        0.10486        0.09889   \n",
      "Utilities                0.121319        0.06905        0.08071   \n",
      "\n",
      "                        DC_Portfolio3  DC_Portfolio4  MV_Portfolio  \\\n",
      "GICS Sector                                                          \n",
      "Communication Services        0.10596        0.10470       0.14863   \n",
      "Consumer Discretionary        0.09329        0.09208       0.00000   \n",
      "Consumer Staples              0.09552        0.08987       0.10972   \n",
      "Energy                        0.08393        0.08384       0.00000   \n",
      "Financials                    0.09628        0.09489       0.35105   \n",
      "Health Care                   0.09865        0.09440       0.23037   \n",
      "Industrials                   0.08502        0.08687       0.00000   \n",
      "Information Technology        0.09727        0.09391       0.00000   \n",
      "Materials                     0.06257        0.08028       0.00000   \n",
      "Real Estate                   0.09642        0.09362       0.00000   \n",
      "Utilities                     0.08515        0.08556       0.16023   \n",
      "\n",
      "                        DC_Portfolio5  \n",
      "GICS Sector                            \n",
      "Communication Services        0.14228  \n",
      "Consumer Discretionary        0.00000  \n",
      "Consumer Staples              0.10255  \n",
      "Energy                        0.00000  \n",
      "Financials                    0.34457  \n",
      "Health Care                   0.22252  \n",
      "Industrials                   0.00000  \n",
      "Information Technology        0.00000  \n",
      "Materials                     0.00000  \n",
      "Real Estate                   0.00000  \n",
      "Utilities                     0.18808  \n",
      "\n",
      "----------PORTFOLIO ANALYSIS----------\n",
      "\n",
      "                                                Companies Invested  \\\n",
      "Benchmark Market Cap-Based Portfolio                           100   \n",
      "Decarbonised Portfolio1 (50% reduction)                        100   \n",
      "Decarbonised Portfolio2 (25% reduction)                        100   \n",
      "Decarbonised Portfolio3 (10% reduction)                        100   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)                 100   \n",
      "Mean-Variance Portfolio                                          6   \n",
      "Decarbonised Mean-Variance Portfolio                             7   \n",
      "\n",
      "                                                Sharpe Ratio  \\\n",
      "Benchmark Market Cap-Based Portfolio                0.055864   \n",
      "Decarbonised Portfolio1 (50% reduction)            -0.202261   \n",
      "Decarbonised Portfolio2 (25% reduction)            -0.213853   \n",
      "Decarbonised Portfolio3 (10% reduction)            -0.217055   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)     -0.220783   \n",
      "Mean-Variance Portfolio                             1.045093   \n",
      "Decarbonised Mean-Variance Portfolio                1.043628   \n",
      "\n",
      "                                                Monthly ExpReturn  \\\n",
      "Benchmark Market Cap-Based Portfolio                     0.820085   \n",
      "Decarbonised Portfolio1 (50% reduction)                  0.440588   \n",
      "Decarbonised Portfolio2 (25% reduction)                  0.419052   \n",
      "Decarbonised Portfolio3 (10% reduction)                  0.412377   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)           0.401030   \n",
      "Mean-Variance Portfolio                                  2.221888   \n",
      "Decarbonised Mean-Variance Portfolio                     2.203171   \n",
      "\n",
      "                                                Annual ExpReturn  Volatility  \\\n",
      "Benchmark Market Cap-Based Portfolio                    9.841021   15.054840   \n",
      "Decarbonised Portfolio1 (50% reduction)                 5.287052   18.357249   \n",
      "Decarbonised Portfolio2 (25% reduction)                 5.028627   18.570607   \n",
      "Decarbonised Portfolio3 (10% reduction)                 4.948528   18.665661   \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)          4.812360   18.967259   \n",
      "Mean-Variance Portfolio                                26.662654   16.900557   \n",
      "Decarbonised Mean-Variance Portfolio                   26.438051   16.709064   \n",
      "\n",
      "                                                Monthly StdDev  Annual StdDev  \n",
      "Benchmark Market Cap-Based Portfolio                  1.120076       3.880057  \n",
      "Decarbonised Portfolio1 (50% reduction)               1.236839       4.284536  \n",
      "Decarbonised Portfolio2 (25% reduction)               1.244006       4.309363  \n",
      "Decarbonised Portfolio3 (10% reduction)               1.247186       4.320377  \n",
      "Decarbonised Portfolio4 (50%, Sector-Balanced)        1.257221       4.355142  \n",
      "Mean-Variance Portfolio                               1.186752       4.111029  \n",
      "Decarbonised Mean-Variance Portfolio                  1.180009       4.087672  \n"
     ]
    }
   ],
   "source": [
    "if __name__ == \"__main__\":\n",
    "    \"\"\"\n",
    "    This method instantiates the PortfolioDecarbonisation class with a specified data source and runs the portfolio decarbonisation process\n",
    "\n",
    "    Parameters:\n",
    "    'R3000 - V02.xlsx' (str): Filename of the Excel spreadsheet containing financial and environmental data\n",
    "    base_optimizer (object): Instance of the BaseConvexOptimizer class used to solve the optimisation problem\n",
    "\n",
    "    Returns:\n",
    "    None. The script prints out the portfolio statistics, ESG performance, and sector composition of each portfolio\n",
    "    \"\"\"\n",
    "    PD = PortfolioDecarbonisation('R3000 - V02.xlsx')\n",
    "    PD.run_portfolio_decarbonisation(base_optimizer=base_optimizer)   "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ee5976dd",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e3a378a6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "obb",
   "language": "python",
   "name": "obb"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.16"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}