TradeManager01_Dev.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "cf130727",
   "metadata": {},
   "source": [
    "# Trade_Manager01_Dev\n",
    "(c) Richard Barrett 12/08/22\n",
    "TradeManager runs once daily, after the market closes.\n",
    "TradeManager executes these functions:\n",
    "- User Notes\n",
    "-Libraries, Constants, etc.\n",
    "- Broker_Converter (Van function and TDA function)\n",
    "- Trade_Log_Manager\n",
    "- Reporter_At_Risk\n",
    "- Reporter_Cash_Flow\n",
    "- Reporter_Trade_Metrics\n",
    "- MsL_Crystal_Ball\n",
    "\n",
    "\n",
    "ToDo\n",
    "Get rid of all global variables.\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "a8817bf0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "wrote 3 results files for Broker_Converter.\n",
      "\n"
     ]
    },
    {
     "ename": "NameError",
     "evalue": "name 'stop' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Cell \u001b[1;32mIn [1], line 325\u001b[0m\n\u001b[0;32m    322\u001b[0m \u001b[38;5;28mprint\u001b[39m(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124mwrote 3 results files for Broker_Converter.\u001b[39m\u001b[38;5;124m'\u001b[39m)\n\u001b[0;32m    323\u001b[0m \u001b[38;5;28mprint\u001b[39m()\n\u001b[1;32m--> 325\u001b[0m \u001b[43mstop\u001b[49m\n",
      "\u001b[1;31mNameError\u001b[0m: name 'stop' is not defined"
     ]
    }
   ],
   "source": [
    "# *****************\n",
    "#\n",
    "# OptionManager01_Dev\n",
    "# Libraries, Constants, etc.\n",
    "# Broker_Converter currently supports two brokerage report formats: TDAmeritrade and Vanguard.\n",
    "# It uses a variable BROKERAGE = ('TDA, 'Vanguard') which selects the correct function for the broker reports.\n",
    "#\n",
    "# *****************\n",
    "#                          \n",
    "#\n",
    "#  Inputs - csv files of recent transactions and current positions (stocks and open options)\n",
    "#           root_data - csv file with list of roots (stock tickers) and current stock share price.\n",
    "#\n",
    "#  Outputs\n",
    "#   Use Vanguard conventions and formats for all numbers - fees, commsns, contracts, names. \n",
    "#   How much difference with TD Ameritrade?\n",
    "#\n",
    "#   09/03/22 - Started conversion of TDA broker reports\n",
    "#   06/12/22 - Started with code from Option 051.\n",
    "#\n",
    "#******************\n",
    "#\n",
    "# Import Libraries\n",
    "#\n",
    "# *****************\n",
    "\n",
    "import csv\n",
    "import copy\n",
    "import datetime\n",
    "import json\n",
    "import numpy as np\n",
    "import os\n",
    "import pandas as pd\n",
    "import requests   # for http requests\n",
    "import scipy\n",
    "from   scipy import stats\n",
    "from   scipy.stats import norm\n",
    "import time\n",
    "import yfinance as yf\n",
    "from   datetime import datetime\n",
    "from   datetime import date\n",
    "#from   datetime import time\n",
    "from   datetime import timedelta\n",
    "\n",
    "from   dateutil.relativedelta  import relativedelta\n",
    "from   yahoo_earnings_calendar import YahooEarningsCalendar\n",
    "\n",
    "\n",
    "# *****************\n",
    "#\n",
    "# GLOBAL VARIABLES - Eliminate these GLOBAL data elements. Convert these to dicts that are passed in line.\n",
    "# \n",
    "# *****************\n",
    "\n",
    "\n",
    "\n",
    "OPTION_XTN_TYPES = ('Buy to open', 'Sell to open', 'Buy to close', 'Sell to close', 'Assignment', 'Expired')\n",
    "OPEN_XTN_TYPES   = ('Sell to open', 'Buy to open')\n",
    "CLOSING_XTN_TYPES = ('Sell to close', 'Buy to close', 'Assignment', 'Expired')\n",
    "\n",
    "# PowerMax Column Names\n",
    "TRADE_LOG_COLS_DICT = {'TRADE_LOG_OPEN_COLS': ('Acct', 'Open Daysout', 'Expiry Date', 'Open Commsn', 'Open Date', \\\n",
    "                                               'Contracts', 'Open Fee / Contract', 'Open Root Price', 'Option Type', \\\n",
    "                                               'Root', 'Strike Price', 'Open Xtn Type', 'Open Net Proceeds'), \\\n",
    "                       'TRADE_LOG_OPEN_METRICS_COLS': ('Open IV', 'Open POW', 'Open ARR', 'Open % Fee', 'Open % OTM'), \\\n",
    "                                                       'Cash at Risk', 'Stock $ at Risk', 'Expiry Daysout'), \\\n",
    "                       'TRADE_LOG_CUR_COLS': ('Cur Price % Chg', 'Cur Root Price', 'Cur ARRBE Fee', 'Strike v Cur Root Price', \\\n",
    "                                              'Cur Fee / Contract', 'Cur Date', 'Cur Root Price', 'Cur IV', \\\n",
    "                                              'Cur Net Proceeds', 'Cur Daysout', 'Close Commsn'), \\\n",
    "                       'TRADE_LOG_CLOSE_COLS': ('Close Fee / Contract', 'Close Date', 'Close Root Price', 'Close IV', \\\n",
    "                                                'Close Net Proceeds', 'Close Daysout', 'Close Commsn', 'Close Xtn Type'), \\\n",
    "                                                'Close ARR'), \\\n",
    "                       'HOLDINGS_COLS': ('Root', 'Shares', 'Committed Shares', 'Share Price', 'Total Value', \\\n",
    "                                         'Expiry Date', 'Contracts', 'Option Type', 'Open Action', 'Strike Price', \\\n",
    "                                         'Contracts', 'Cur Fee / Contract')}\n",
    "\n",
    "\n",
    "trade_log_open = pd.DataFrame()\n",
    "\n",
    "def BrokerConverterTDA(root_data, broker_option_holdings, broker_option_xtns):\n",
    "\n",
    "    # *****************\n",
    "    # 1. Read broker report with option holdings.  \n",
    "    # 2. For open_options parse option 'contractSymbol' into root, option type (P or C), strike price, and expiry date.\n",
    "    # 3. Calculate 3 dfs for at risk dashbaord \n",
    "    #\n",
    "    # *****************\n",
    "\n",
    "    TDA_HOLDINGS_COLS = ('Symbol', 'Qty')\n",
    "    TDA_XTN_COLS      = ('DATE', 'TRANSACTION ID', 'DESCRIPTION', 'QUANTITY', 'SYMBOL', 'PRICE', 'COMMISSION', 'AMOUNT')\n",
    "    TDA_DROP_COLS     = ('REG FEE', 'SHORT-TERM RDM FEE', 'FUND REDEMPTION FEE', ' DEFERRED SALES CHARGE')\n",
    "    \n",
    "    # Column names in trade_log dfs. TL = TRADE_LOG\n",
    "    \n",
    "    # **********\n",
    "    # From brokerage holdings and activity statements, create open_options.\n",
    "    \n",
    "    # Create holdings df from top rows of broker_report. \n",
    "    \n",
    "    broker_option_holdings_mask = holdings_row_mask = broker_option_holdings.iloc[:,0] == 'Options'\n",
    "    holdings_row = broker_option_holdings[broker_option_holdings_mask]\n",
    "    \n",
    "    split_index = holdings_row.index.values.astype(int)[0] + 3\n",
    "    holdings = broker_option_holdings[broker_option_holdings.index >= split_index].copy()\n",
    "    \n",
    "    # Set column headers to first row, then reset index.\n",
    "    holdings.columns = holdings.iloc[0]\n",
    "    holdings = holdings[1:]\n",
    "    holdings.reset_index(drop = True, inplace = True)\n",
    "    \n",
    "    # Drop empty rows.\n",
    "    holdings = holdings[holdings['Symbol'].notna()].copy()\n",
    "    holdings.reset_index(drop = True, inplace = True)\n",
    "    \n",
    "    # Drop last row.\n",
    "    last_row = holdings.shape[0] - 1\n",
    "    holdings.drop(index = last_row, inplace = True)\n",
    "    \n",
    "    # Create open_options df. This contains all the option trades open now, both puts and calls.\n",
    "    #\n",
    "    open_options = pd.DataFrame()\n",
    "    open_options = holdings['Symbol'].str.split(' ',expand=True)\n",
    "    open_options.rename(columns = {0:'Root', 1: 'month', 2: 'day', 3: 'year', 4: 'Strike Price', 5: 'Option Type', \\\n",
    "                                   6: 'Freq'}, inplace = True)\n",
    "    open_options = pd.concat([holdings, open_options], axis = 1).copy()\n",
    "    \n",
    "    # Write expiry Date as datetime and string\n",
    "    open_options['month'] = pd.to_datetime(open_options.month, format='%b').dt.month.astype(str)\n",
    "    open_options['Expiry Date str'] = open_options['year'] +'-'+ open_options['month'] +'-'+ open_options['day']\n",
    "    open_options['Expiry Date'] = pd.to_datetime(open_options['Expiry Date str'], infer_datetime_format = True)\n",
    "    \n",
    "    open_options['Contracts'] = open_options['Qty']\n",
    "    open_options['Contracts'] = open_options['Contracts'].astype(int)\n",
    "    \n",
    "    # Convert 'Put' to 'P' and 'Call' to 'C'\n",
    "    open_options['Option Type'].mask(open_options['Option Type'] == 'Call', 'C', inplace = True)\n",
    "    open_options['Option Type'].mask(open_options['Option Type'] == 'Put', 'P', inplace = True)\n",
    "                                    \n",
    "    open_options.drop(columns = ['Underlying symbol', 'Symbol', 'Qty', 'month', 'day', 'year', \\\n",
    "                                 'Gain ($)', 'Maint req', 'Freq', 'Expiry Date str'], inplace = True)\n",
    "\n",
    "    # Update 'Cur Root Price' column in open_options from price in root_data. \n",
    "    open_options['Cur Root Price'] = 0.0\n",
    "    print('after col. drop open options shape = ')\n",
    "    print(open_options)\n",
    "    \n",
    "    unique_tickers = open_options['Root'].unique()\n",
    "    for tick in unique_tickers:\n",
    "        tick_indexes = open_options[open_options['Root'] == tick].index\n",
    "        # Fails with ticker 'MU'. So, for now skip MU to keep going w/debug.\n",
    "        if tick != 'MU':  # Skip MU since it fails. Fix it next.\n",
    "            open_options.loc[tick_indexes, 'Cur Root Price'] = root_data.loc[tick, 'root price']\n",
    "\n",
    "    opt_xtns = pd.DataFrame()  #Need to create this eventually.\n",
    "    \n",
    "    return holdings, open_options, opt_xtns\n",
    "\n",
    "# *****************\n",
    "#\n",
    "# Functions to convert broker reports into standard POWARRMax report formats for holdings and xtns.\n",
    "# \n",
    "# *****************\n",
    "\n",
    "def BrokerConverterVan(root_data, broker_report):\n",
    "\n",
    "    # *****************\n",
    "    # NOTE - This version works with the Vanguard report, which awkwardly combines holdings and transactions\n",
    "    #       into a single csv file. \n",
    "    #\n",
    "    # 1. Read broker report with holdings and xtns, which Vanguard combines into a single df. \n",
    "    # 2. Parse report into three dfs: stock holdings, xtns, and open option holdings (trades). \n",
    "    #     For open_options parse option 'contractSymbol' into root, option type (P or C), strike price, and expiry date.\n",
    "    #\n",
    "    #\n",
    "    # *****************\n",
    "    \n",
    "    # Vanguard Column Names\n",
    "    \n",
    "    VGD_COLS = ('Trade Date', 'Commission Fees', 'Share Price', 'Shares', 'Account Number', \\\n",
    "                'Accrued Interest', 'Account Type', 'Settlement Date', 'Investment Name', \\\n",
    "                'Transaction Description', 'Transaction Type', 'Net Amount', 'Principal Amount', \\\n",
    "                'Symbol')\n",
    "    \n",
    "    DROP_VGD_COLS = ['Account Number', 'Settlement Date', 'Transaction Description', 'Investment Name', 'Account Type']\n",
    "\n",
    "    VAN_HOLDINGS_COLS = ('Account Number', 'Investment Name', 'Symbol', 'Shares', 'Share Price')\n",
    "    \n",
    "    OPTION_XTN_TYPES = ('Buy to open', 'Sell to open', 'Buy to close', 'Sell to close', 'Assignment', 'Expired')\n",
    "    OPEN_XTN_TYPES   = ('Sell to open', 'Buy to open')\n",
    "    CLOSING_XTN_TYPES = ('Sell to close', 'Buy to close', 'Assignment', 'Expired')\n",
    "\n",
    "    # POWARRMax Column Names\n",
    "    TRADE_LOG_CLOSE_METRICS_COLS = ('Close ARR', 'Assigned ARR')\n",
    "\n",
    "    TRADE_LOG_OPEN_COLS = ('Acct', 'Open Daysout', 'Expiry Date', 'Expiry Daysout', 'Open Action', 'Open Commsn' \\\n",
    "                           'Open Date', 'Contracts', 'Open Fee / Share', 'Open Root Price', 'Option Type', \\\n",
    "                           'Root', 'Strike Price', 'Open Xtn Type')\n",
    "    \n",
    "    TRADE_LOG_OPEN_METRICS_COLS = ('Open IV', 'Open POW', 'ARR BE Close Fee', 'ARR BE Profit', 'Cur Price % Chg', \\\n",
    "                                   'Cur Root Price', 'Open % Fee', 'Open % OTM', 'Open ARR', \\\n",
    "                                   'Strike v Cur Root Price', 'Open Net Proceeds', 'Cash at Risk', \\\n",
    "                                   'Stock $ at Risk')\n",
    "\n",
    "    TRADE_LOG_CLOSE_COLS = ('Close Fee / Share', 'Close Date', 'Close Root Price', \\\n",
    "                            'Close Net Proceeds', 'Close Daysout', 'Close Commsn', 'Close Xtn Type')\n",
    "    HOLDINGS_COLS = ('Root', 'Shares', 'Committed Shares', 'Share Price', 'Total Value', 'Expiry Date', 'Contracts', \\\n",
    "                     'Option Type', 'Open Action', 'Strike Price', 'Contracts', 'Cur Fee / Share')\n",
    "\n",
    "    TRADE_LOG_COLS = TRADE_LOG_OPEN_COLS + TRADE_LOG_OPEN_METRICS_COLS + TRADE_LOG_CLOSE_COLS + TRADE_LOG_CLOSE_METRICS_COLS\n",
    "\n",
    "    # **********\n",
    "    # From brokerage holdings and activity statement, create 3 separate dfs: -- holdings, opt_xtns, and open_options.\n",
    "    \n",
    "    # 1. Create holdings df from top rows of broker_report.\n",
    "    xtns_row = broker_report.loc[broker_report['colx1'] == 'Trade Date']\n",
    "    split_index = xtns_row.index.values.astype(int)[0] \n",
    "    holdings = broker_report[broker_report.index < split_index].copy()\n",
    "   \n",
    "    # Remove unused columns from holdings\n",
    "    unused_cols = []\n",
    "    for i in range(5, 15):\n",
    "        col_name = 'colx' + str(i)\n",
    "        unused_cols.append(col_name)\n",
    "    \n",
    "    holdings.drop(columns = unused_cols, inplace = True)\n",
    "    \n",
    "    # restore oroginal column headers, from row 1 and delete 'colx' headers\n",
    "    holdings.rename(columns = holdings.iloc[0], inplace = True)\n",
    "    holdings.drop(index = 0, inplace = True)\n",
    "    holdings.drop(columns = 'Account Number', inplace = True)\n",
    "    \n",
    "    # 2. Create opt_xtns df from brokerage report. \n",
    "    #\n",
    "    xtns = broker_report[broker_report.index >= split_index].copy()\n",
    "\n",
    "    # Rename columns in xtns and reset index.\n",
    "    xtns.columns = xtns.iloc[0]\n",
    "    xtns = xtns[1:]\n",
    "    xtns.reset_index(drop = True, inplace = True)\n",
    "\n",
    "    # Add trade_log column names to xtns\n",
    "    xtns = xtns.reindex(columns = [*xtns.columns.tolist(), *TRADE_LOG_COLS], fill_value = None)\n",
    "    \n",
    "    # Select option trades only.\n",
    "    opt_xtns = xtns[xtns['Transaction Type'].isin(OPTION_XTN_TYPES)].copy()\n",
    "        \n",
    "    opt_xtns.drop(DROP_VGD_COLS, axis = 1, inplace=True)\n",
    "    opt_xtns.reset_index(drop = True, inplace = True)\n",
    "        \n",
    "    # 3. Create open_options df. This contains all the option trades open now, both puts and calls.\n",
    "    #\n",
    "    open_options = pd.DataFrame()\n",
    "    open_options = holdings['Symbol'].str.split(' ',expand=True)\n",
    "    open_options.rename(columns = {0:'Root', 1: 'Expiry Date', 2: 'Option Type', 3: 'Strike Price'}, inplace = True)\n",
    "    open_options = pd.concat([holdings, open_options], axis = 1).copy()\n",
    "\n",
    "    open_options = open_options[open_options['Option Type'].isin(['P', 'C'])].copy()\n",
    "    open_options['Contracts'] = open_options['Shares']\n",
    "    open_options['Contracts'] = open_options['Contracts'].astype(int)\n",
    "    \n",
    "    open_options.drop(columns = ['Investment Name', 'Symbol', 'Shares'], inplace = True)\n",
    "    \n",
    "#    Update 'Cur Root Price' column in open_options from price in root_data. \n",
    "#    print(root_data.head(10))\n",
    "    open_options['Cur Root Price'] = 0.0\n",
    "    unique_tickers = open_options['Root'].unique()\n",
    "    for tick in unique_tickers:\n",
    "        tick_indexes = open_options[open_options['Root'] == tick].index\n",
    "    return holdings, open_options, opt_xtns\n",
    "\n",
    "#************\n",
    "# Main Program calls Broker_Converter\n",
    "#************\n",
    "\n",
    "# Main program tests Broker_Converter ONLY. It uses three test files: trade_log_open, trade_log_closed,\n",
    "#      and opt_xtns. It reads these directly. \n",
    "root_data        = pd.read_csv('root_data.csv')\n",
    "root_data.sort_values('Ticker', axis = 0, inplace = True, ignore_index = True)\n",
    "root_data.set_index('Ticker', inplace = True)\n",
    "\n",
    "BROKERAGE = 'Vanguard'\n",
    "\n",
    "if BROKERAGE == 'TDA':\n",
    "    broker_open_options = pd.read_csv('TDA_positions.csv')\n",
    "    broker_xtns         = pd.read_csv('TDA_xtns.csv')\n",
    "    holdings, open_options, opt_xtns = BrokerConverterTDA(root_data, broker_open_options, broker_xtns)\n",
    "\n",
    "if BROKERAGE == 'Vanguard':\n",
    "    # Create temporary column headersbecause Vanguard concatenates the positions report and the transactions report.\n",
    "    col_headers = []\n",
    "    for i in range(15):\n",
    "        col_name = 'colx' +str(i)\n",
    "        col_headers.append(col_name)\n",
    "       \n",
    "    broker_report_van = pd.read_csv('broker_report_van.csv',header = None, names = col_headers, skip_blank_lines = True)\n",
    "    \n",
    "    holdings, open_options, opt_xtns = BrokerConverterVan(root_data, broker_report_van)\n",
    "\n",
    "holdings.to_csv('broker_holdings.csv', index = False)\n",
    "open_options.to_csv('broker_open_options.csv', index = False)\n",
    "opt_xtns.to_csv('broker_opt_xtns.csv', index = False)\n",
    "print()\n",
    "\n",
    "print('wrote 3 results files for Broker_Converter.')\n",
    "print()\n",
    "\n",
    "stop"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19eb871d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Trade_Log_Manager\n",
    "# Reads the transaction records from Broker_Converter.\n",
    "# Updates trade_log_open df and trade_log_closed df.\n",
    "# Reconciles closed xtns with existing open xtns to correctly match open and close trades.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ebee3cf7",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Reporter_At_Risk\n",
    "def DashboardAtRisk(open_options, root_data):\n",
    "    \n",
    "    # *****************\n",
    "    #\n",
    "    # Dashboard_At_Risk (DR) - 09/06/22 - Generates all the dfs for the dashboard section \"At Risk.\"\n",
    "    #                      \n",
    "    # *****************\n",
    "    #\n",
    "    #  Outputs - expiry_spread df\n",
    "    #            put_root_spread_puts df\n",
    "    #            call_root_spread_calls df\n",
    "    #            \n",
    "    # Use TADA reports for positions and transactions.  \n",
    "    # Use Vanguard conventions and formats for all numbers - fees, commsns, contracts, names.\n",
    "    #   \n",
    "    # 1. Create df for Expiry Spread table.\n",
    "    #\n",
    "    \n",
    "    # Remove all long puts and calls.\n",
    "    open_options = open_options[open_options['Contracts'] < 0].copy()\n",
    "        \n",
    "    open_options['Strike Price'] = open_options['Strike Price'].astype('float')\n",
    "\n",
    "    open_options['$ at Risk'] = -open_options['Contracts'] * open_options['Strike Price'] * 100.0\n",
    "    print('$ at Risk = ', open_options['$ at Risk'].sum())\n",
    "    \n",
    "    open_options['ITM'] = False\n",
    "    open_options['ITM'] = np.logical_xor((open_options['Option Type'] == 'P'), \\\n",
    "                                        (open_options['Strike Price'] < open_options['Cur Root Price']))\n",
    "    open_options['$ ITM'] = 0.0\n",
    "    open_options['$ ITM'].mask(open_options['ITM'], open_options['$ at Risk'], inplace = True)\n",
    "    \n",
    "    open_options['Open Trades'] = 1.0\n",
    "    expiry_spread = open_options.groupby('Expiry Date').agg({'Open Trades': 'count', '$ at Risk': 'sum', '$ ITM': 'sum'})\n",
    "    expiry_spread['ITM % of Expiry At Risk']  = expiry_spread['$ ITM'] * 100.0 / expiry_spread['$ at Risk']\n",
    "    expiry_spread['ITM % of Tot. ITM'] = expiry_spread['$ ITM'] * 100.0 / expiry_spread['$ ITM'].sum()\n",
    "    \n",
    "    # Add totals row at bottom.\n",
    "    expiry_spread.loc['Totals'] = expiry_spread.sum(numeric_only=True)\n",
    "    expiry_spread.loc['Totals', 'ITM % of Expiry At Risk'] = expiry_spread.loc['Totals', '$ ITM'] * 100 / \\\n",
    "                                                             expiry_spread.loc['Totals', '$ at Risk']\n",
    "    \n",
    "    # 2. Create df for put_root_spread table.\n",
    "    #\n",
    "    put_options = open_options[open_options['Option Type'] == 'P'].copy()\n",
    "    \n",
    "    #print(open_options)\n",
    "    #print(put_options)\n",
    "\n",
    "    # the magic of groupby\n",
    "    put_root_spread = put_options.groupby('Root').agg({'Contracts': 'sum', '$ at Risk': 'sum', '$ ITM': 'sum'})\n",
    "    put_root_spread.to_csv('groupby_put_root_spread.csv')\n",
    "    \n",
    "    total_cash_at_risk = put_root_spread['$ at Risk'].sum()\n",
    "    put_root_spread['% of $ at Risk'] = put_root_spread['$ at Risk'] *100 / total_cash_at_risk\n",
    "    put_root_spread['ITM % of Root $ at Risk'] = put_root_spread['$ ITM'] * 100.0 / put_root_spread['$ at Risk']\n",
    "    total_cash_ITM = put_root_spread['$ ITM'].sum()\n",
    "    put_root_spread['ITM % of Tot $ ITM'] = put_root_spread['$ ITM'] * 100.0 / total_cash_ITM\n",
    "    put_root_spread['ITM % of Tot $ at Risk'] = put_root_spread['$ ITM'] * 100.0 / total_cash_at_risk\n",
    "    \n",
    "    # Add totals row at bottom.\n",
    "    put_root_spread.loc['Totals'] = put_root_spread.sum(numeric_only=True)\n",
    "    put_root_spread.loc['Totals', ['ITM % of Root $ at Risk']] = ' '    \n",
    "    \n",
    "    #Rearrange column order for output to csv file.\n",
    "    put_root_spread = put_root_spread[['Contracts', '$ at Risk', '% of $ at Risk', '$ ITM', 'ITM % of Tot $ at Risk',\\\n",
    "                               'ITM % of Tot $ ITM', 'ITM % of Root $ at Risk']]\n",
    "    \n",
    "    # 3. Create df for call_root_spread table\n",
    "    #\n",
    "    call_options = open_options[open_options['Option Type'] == 'C'].copy()\n",
    "    \n",
    "    call_root_spread = call_options.groupby('Root').agg({'Contracts': 'sum', '$ at Risk': 'sum', '$ ITM': 'sum'})\n",
    "\n",
    "    col_name_switch = {'$ at Risk': 'Root $ at Risk', '$ ITM': 'Root $ ITM'}\n",
    "    call_root_spread.rename(columns = col_name_switch, inplace = True)\n",
    "\n",
    "    total_root_at_risk = call_root_spread['Root $ at Risk'].sum()\n",
    "    call_root_spread['% of Tot $ at Risk'] = call_root_spread['Root $ at Risk'] *100 / total_root_at_risk\n",
    "    call_root_spread['ITM % of Root $ at Risk'] = call_root_spread['Root $ ITM'] * \\\n",
    "                                                   100.0 / call_root_spread['Root $ at Risk']\n",
    "\n",
    "    total_root_ITM = call_root_spread['Root $ ITM'].sum()\n",
    "    call_root_spread['ITM % of Tot $ ITM'] = call_root_spread['Root $ ITM'] * 100.0 / total_root_ITM\n",
    "    call_root_spread['ITM % of Tot $ at Risk'] = call_root_spread['Root $ ITM'] * 100.0 / total_root_at_risk\n",
    "    \n",
    "    # Add totals row at bottom.\n",
    "    call_root_spread.loc['Totals'] = call_root_spread.sum(numeric_only=True)\n",
    "    call_root_spread.loc['Totals', 'ITM % of Root $ at Risk'] = ' '\n",
    "    \n",
    "    call_root_spread = call_root_spread[['Contracts', 'Root $ at Risk', '% of Tot $ at Risk', \\\n",
    "                                           'Root $ ITM', 'ITM % of Tot $ at Risk', \\\n",
    "                                           'ITM % of Tot $ ITM', 'ITM % of Root $ at Risk']]\n",
    "      \n",
    "    return expiry_spread, put_root_spread, call_root_spread\n",
    "\n",
    "\n",
    "\n",
    "#************\n",
    "# Main Program\n",
    "#************\n",
    "\n",
    "# This Main program tests DashboardAtRisk (DR) ONLY.  \n",
    "pd.options.display.float_format = '{: .0f}'.format\n",
    "print(open_options)\n",
    "\n",
    "expiry_spread, put_root_spread, call_root_spread = Dashboard_At_Risk(open_options, root_data)\n",
    "\n",
    "expiry_spread.round(decimals = 0)\n",
    "\n",
    "expiry_spread.to_csv('DR_expiry_spread.csv', float_format=\"%.0f\")\n",
    "put_root_spread.to_csv('DR_put_root_spread.csv', float_format=\"%.0f\")\n",
    "call_root_spread.to_csv('DR_call_root_spread.csv', float_format=\"%.0f\")\n",
    "\n",
    "\n",
    "print('Wrote 3 dashboard files. Well, that was easy, Hah.')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "75362884",
   "metadata": {},
   "source": [
    "# DashboardCashFlow (DC)\n",
    "\n",
    "10/17/22 This function calculates cashflow summaries and journals. It includes:\n",
    "\n",
    "cashflow STO and BTC fees.\n",
    "\n",
    "broker premiums (commsns)\n",
    "\n",
    "cashflow from assignments\n",
    "\n",
    "Share count changes from assignments\n",
    "\n",
    "Cum average cost basis for shares from option trade assignments.\n",
    "\n",
    "Monthly Cashflow journals by root, by option type.\n",
    "\n",
    "Block #1 Cum Cash Flow by group and by categories. (add ability for user to select period). Cash Flow Categories: Open Fees, Close Fees, Commsns, Assigned Cash Cash Flow Groups: by Option Type (Put/Call), by Close Action (TradeClose, Expired, Assigned) Cash Flow by Root: Lotsa roots\n",
    "\n",
    "Block #2 Cash Flow by Month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db0c66c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ****************\n",
    "# Cash Flow Breakdown by Cash Categories\n",
    "#\n",
    "# DashboardCashFlow (DC) 08/31/22 Updated df for dashboard from closed option files. \n",
    "# This Block #1 calculates cash flow breakdown by categories - \n",
    "# Last update 09/13/22 -Cash Flow Tables Work!! Next project is cash flow journals.\n",
    "# *****************\n",
    "# Goal - Create df with complete cash flow accounting for each trade, including cash flow from assignments. \n",
    "#\n",
    "#\n",
    "# Inputs  - trade_log_open and trade_log_closed\n",
    "#           root_data\n",
    "#           df with stock positions and cost basis at start date of analysis to track costs for covered call trades.\n",
    "#\n",
    "# Outputs - DC_by_root - cash flow table by root\n",
    "#           DC_by_option_type\n",
    "#           DC_by_close_action\n",
    "#           DC_journal_by_root - cash flows by month by root\n",
    "#           DC_journal_by_option_type  - cash flows by month by option_type\n",
    "#           DC_journal_by_close_action - cash flows by month by clsoe_action\n",
    "#\n",
    "# Function Process without Pandas implementation\n",
    "# 1. Group all trades by root, Option Type (P/C), Close Aciton (Exp, BTO, Assigned), Month\n",
    "# 2. Sum metrics for each group. Metrics = Open Fee, Close Fee, Open Commsn, Close Commsn, Assign Cash, Shares in/out,\n",
    "#         Share value in/out ($ * # shares)\n",
    "# 3. Delete all trade detail records. \n",
    "# 4. Create two cum lines: 1. Cash in/out, which is cash flow. 2. shares in/out and Share $ in/out. This creates cost basis.\n",
    "# 5. \n",
    "# 1. Set up cash flow data columns and calculate all cell values.\n",
    "# 2. Set up row mmulti-index.Include all cash flow rows in the mmulti_header. \n",
    "# 3. Unstack by month, so financial items remain as rows.\n",
    "# 4. Calculate groupby subtotals.\n",
    "# 5. Calculate totals at top level to show caash flows for entire POWARRMax project.\n",
    "\n",
    "# *****************\n",
    "#   Work Journal\n",
    "#   ToDo - Check cash sign on assigned trades.\n",
    "#          Add # trades for each group / category.\n",
    "#   08/31/22 - Created initial notebook code from DashboardTrades\n",
    "#\n",
    "# *****************\n",
    "#\n",
    "# CONSTANTS\n",
    "#\n",
    "# *****************\n",
    "\n",
    "FILE_ID_TAG       = 'test_DC'\n",
    "\n",
    "\n",
    "# *****************\n",
    "# DashboaardCashFlow written as Main Program. Later may convert to fct.\n",
    "#         Note DC does not calculate any of the trade_log_close column values. \n",
    "#         TradeLogger is the only module that writes into trade_log_close df.\n",
    "#         DC calculates aggregate statistics (sum, cum_sum, count, mean) only.\n",
    "#\n",
    "# MAIN PROGAM \n",
    "# \n",
    "# *****************\n",
    "\n",
    "#Cash Flow Calc Process\n",
    "# 1. Master df is trade_log_closed. Do not change it.  \n",
    "# 2. Create multi-level row index these keys - Root, Option Type, Close Action, Open Month\n",
    "# 3. Create two-level column index w / \"Metrics / met1, met2, ...\n",
    "# 4. Stack operates on column indexes. Starts with innermost COLUMN index level.\n",
    "# 4.1   Metrics / met1, met2... This may force other colums to stack also.\n",
    "#       Use p.MultiIndex.fromframe\n",
    "# 5. Groupby row indexes.\n",
    "# 6. Calculate metric aggregate stats with groupby columns.\n",
    "# 7. Delete individual trade records from summary dfs. DO NOT DELETE indiv trade records from trade_log_closed.\n",
    "# 8. Create new row index with Month as innermost key. Metric / met1, met2, ... must be higher level row keys.\n",
    "# 9. Unstack Month. \n",
    "# Now have df with metric aggregate values as rows, and Month # as row.\n",
    "\n",
    "# POWARRMax Column Names\n",
    "\n",
    "\n",
    "TRADE_LOG_OPEN_COLS = ('Acct', 'Open Daysout', 'Expiry Date', 'Expiry Daysout', 'Open Action', 'Open Commsn' \\\n",
    "                       'Open Date', 'Contracts', 'Open Fee / Share', 'Open Root Price', 'Option Type', \\\n",
    "                       'Root', 'Strike Price', 'Open Share Price')\n",
    "\n",
    "TRADE_LOG_OPEN_METRICS_COLS = ('Open IV', 'Open POW', 'ARR BE Close Fee', 'ARR BE Profit', 'Cur Price % Chg', \\\n",
    "                               'Cur Root Price', 'Open % Fee', 'Open % OTM', 'Open ARR', \\\n",
    "                               'Strike v Cur Root Price', 'Open Net Proceeds', 'Cash at Risk', \\\n",
    "                               'Shares at Risk', 'Stock $ at Risk')\n",
    "\n",
    "TRADE_LOG_CLOSE_COLS = ('Close Fee / Share', 'Close Date', 'Close Root Price', \\\n",
    "                        'Close Net Proceeds', 'Close Daysout', 'Close Commsn', 'Close Action', 'Assigned Cash', \\\n",
    "                        'Assigned Shares', 'Net Cash')\n",
    "\n",
    "TRADE_LOG_CLOSE_METRICS_COLS = ('Close ARR', 'Assigned ARR')\n",
    "\n",
    "HOLDINGS_COLS = ('Root', 'Shares', 'Committed Shares', 'Share Price', 'Total Value', 'Expiry Date', 'Contracts', \\\n",
    "                 'Option Type', 'Open Action', 'Strike Price', 'Contracts', 'Cur Fee / Share')\n",
    "\n",
    "TRADE_LOG_COLS = TRADE_LOG_OPEN_COLS + TRADE_LOG_OPEN_METRICS_COLS + TRADE_LOG_CLOSE_COLS + TRADE_LOG_CLOSE_METRICS_COLS\n",
    "\n",
    "tlc = pd.read_csv('test_DC_fake_data.csv')\n",
    "\n",
    "tlc.drop(columns = 'Open Date', inplace = True)\n",
    "# 1. Separate columns into closed_trades into keys, metrics, and other. \n",
    "sort_cols = ['Root', 'Option Type', 'Close Action', 'Open Month']\n",
    "tlc.sort_values(by = sort_cols, axis = 0, ascending = True, inplace = True)\n",
    "tlc_short_index = tlc.set_index(sort_cols, append = False).copy()\n",
    "\n",
    "tlc_agg_dict = {'Contracts': 'sum', 'Open Fee': 'sum', 'Open Commsn': 'sum'}\n",
    "\n",
    "tlc_mo_group = tlc_short_index.groupby(level = [0, 1, 2, 3]).agg(tlc_agg_dict)                                        \n",
    "tlc_mo_group.to_csv('DC_tlc_mo_group.csv')\n",
    "\n",
    "tlc_unstack_by_mo = tlc_mo_group.unstack()  #Unstack is general case of pivot. Typical is innermost index (row or col)\n",
    "\n",
    "tlc_unstack_by_mo.to_csv('DC_tlc_unstack_by_mo.csv')\n",
    "\n",
    "# ********************\n",
    "#Construct Cash Flow Tables\n",
    "# ********************\n",
    "#1. Construct dct, which is the master df for all cash flow tables. \n",
    "#   Copy dct from tlc, then drop columns and calculate columns for Assigned cash flow and total cash flows.\n",
    "\n",
    "table_drop_columns = ['Metric-POW', 'Metric-ARR', 'Metric-PctFee', 'Open Month']\n",
    "dct = tlc.copy()\n",
    "dct.drop(columns = table_drop_columns, inplace = True)\n",
    "\n",
    "dct['assigned_cf_sign'] = 1.0\n",
    "dct['assigned_cf_sign'] = dct['assigned_cf_sign'].where((dct['Option Type'] == 'Call') & \\\n",
    "                                                        (dct['Close Action'] == 'Assigned'), -1.0)\n",
    "#print(dct['assigned_cf_sign'])\n",
    "\n",
    "dct['assigned_cf_sign'] = dct['assigned_cf_sign'].where(dct['Close Action'] == 'Assigned', 0.0)\n",
    "#print()\n",
    "#print('assigned_cf after Assigned check')\n",
    "#print(dct['assigned_cf_sign'])\n",
    "#print()\n",
    "\n",
    "dct['Assigned Cash']        = dct['Strike Price'] * dct['Contracts'] * dct['assigned_cf_sign'] * -100.0\n",
    "dct['Tot. Open Fees']       = dct['Open Fee'] * dct['Contracts'] * -100.0\n",
    "dct['Tot. Close Fees']      = dct['Close Fee'] * dct['Contracts'] * 100.0\n",
    "dct['Broker Commsns']       = dct['Contracts'] * 2.0\n",
    "dct['Broker Commsns']       = dct['Broker Commsns'].where((dct['Close Action'] == 'BTC'), dct['Contracts'])\n",
    "dct['Tot. Cash this Trade'] = dct['Tot. Open Fees'] + dct['Tot. Close Fees'] + \\\n",
    "                              dct['Broker Commsns'] + dct['Assigned Cash']\n",
    "\n",
    "#2.0 Groupby Root and aggregate to get dct_by_root table. Add subtotals and pcts on right and bottom sides.\n",
    "\n",
    "dct_br = dct.drop(columns = ['Option Type', 'Close Action']).copy()\n",
    "dct_by_root_dict = {'Tot. Open Fees': 'sum', 'Tot. Close Fees': 'sum', 'Broker Commsns': 'sum', 'Assigned Cash': 'sum'}\n",
    "dct_by_root = dct_br.groupby(by = 'Root', axis = 0).agg(dct_by_root_dict)\n",
    "dct_by_root['Tot. Cash for Root'] = dct_by_root['Tot. Open Fees'] + dct_by_root['Tot. Close Fees'] + \\\n",
    "                                    dct_by_root['Broker Commsns'] + dct_by_root['Assigned Cash']\n",
    "\n",
    "\n",
    "#2.1 Do sums and pcts for totals of each column and for each root row\n",
    "dct_by_root.loc['Total'] = dct_by_root.sum()\n",
    "tot_cash                 = dct_by_root.loc['Total', 'Tot. Cash for Root']\n",
    "dct_by_root.loc['Pct']   = dct_by_root.loc['Total'] * 100.0 / tot_cash\n",
    "dct_by_root['Pct Root']  = dct_by_root['Tot. Cash for Root'] * 100.0 / tot_cash\n",
    "dct_by_root.loc['Pct', 'Tot. Cash for Root'] = ' '\n",
    "\n",
    "dct_by_root = dct_by_root.round(decimals = 0).copy()\n",
    "print(dct_by_root)\n",
    "dct_by_root.to_csv('DC_by_root.csv')\n",
    "\n",
    "#3.0 Groupby Option Type and aggregate to get dct_by_opt_type table. Add subtotals and pcts on right and bottom sides.\n",
    "\n",
    "# dct_bot = precursor for dct_by_opt_type\n",
    "dct_bot = dct.drop(columns = ['Root', 'Close Action']).copy()  \n",
    "dct_by_opt_type_dict = {'Tot. Open Fees': 'sum', 'Tot. Close Fees': 'sum', 'Broker Commsns': 'sum', 'Assigned Cash': 'sum'}\n",
    "dct_by_opt_type = dct_bot.groupby(by = 'Option Type', axis = 0).agg(dct_by_opt_type_dict)\n",
    "dct_by_opt_type['Tot. Cash for Option Type'] = dct_by_opt_type['Tot. Open Fees'] + \\\n",
    "                                               dct_by_opt_type['Tot. Close Fees'] + \\\n",
    "                                               dct_by_opt_type['Broker Commsns'] + dct_by_opt_type['Assigned Cash']\n",
    "\n",
    "#3.1 Do sums and pcts for totals of each column and for each option type row\n",
    "\n",
    "dct_by_opt_type.loc['Total']= dct_by_opt_type.sum()\n",
    "tot_cash = dct_by_opt_type.loc['Total', 'Tot. Cash for Option Type']\n",
    "dct_by_opt_type.loc['Pct'] = dct_by_opt_type.loc['Total'] * 100.0 / tot_cash\n",
    "dct_by_opt_type['Pct Root'] = dct_by_opt_type['Tot. Cash for Option Type'] * 100.0 / tot_cash\n",
    "dct_by_opt_type.loc['Pct', 'Tot. Cash for Option Type'] = ' '\n",
    "\n",
    "dct_by_opt_type = dct_by_opt_type.round(decimals = 0).copy()                   \n",
    "dct_by_opt_type.to_csv('DC_by_option_type.csv')\n",
    "print(dct_by_opt_type)\n",
    "\n",
    "#4.0 Groupby Close Action and aggregate to get dct_by_close_action table. Add subtotals and pcts on right and bottom sides.\n",
    "\n",
    "dct_bca = dct.drop(columns = ['Root', 'Option Type']).copy()\n",
    "dct_by_close_action_dict = {'Tot. Open Fees': 'sum', 'Tot. Close Fees': 'sum', 'Broker Commsns': 'sum', 'Assigned Cash': 'sum'}\n",
    "dct_by_close_action = dct_bca.groupby(by = 'Close Action', axis = 0).agg(dct_by_close_action_dict)\n",
    "dct_by_close_action['Tot. Cash for Close Action'] = dct_by_close_action['Tot. Open Fees'] + \\\n",
    "                                                    dct_by_close_action['Tot. Close Fees'] + \\\n",
    "                                                    dct_by_close_action['Broker Commsns'] + \\\n",
    "                                                    dct_by_close_action['Assigned Cash']\n",
    "\n",
    "#4.1 Do sums and pcts for totals of each column and for each option type row\n",
    "\n",
    "dct_by_close_action.loc['Total']= dct_by_close_action.sum()\n",
    "tot_cash = dct_by_close_action.loc['Total', 'Tot. Cash for Close Action']\n",
    "dct_by_close_action.loc['Pct'] = dct_by_close_action.loc['Total'] * 100.0 / tot_cash\n",
    "dct_by_close_action['Root Pct of Tot'] = dct_by_close_action['Tot. Cash for Close Action'] * 100.0 / tot_cash\n",
    "dct_by_close_action.loc['Pct', 'Tot. Cash for Close Action'] = ' '\n",
    "\n",
    "dct_by_close_action = dct_by_close_action.round(decimals = 0).copy()\n",
    "print('dct by close action')\n",
    "print(dct_by_close_action)\n",
    "dct_by_close_action.to_csv('DC_by_close_action.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0675aead",
   "metadata": {},
   "outputs": [],
   "source": [
    "# **********\n",
    "#\n",
    "# Block #2 - Cash Flows by Month - By Trade Open Month (connects cash to open action.)\n",
    "#                                       All cash for the trade recorded in the month the trade opened.\n",
    "#                                                            )\n",
    "#                                  True Cash Flow. Shows actual cash flows by date--Open, TradeClose, Assigned, Expired. \n",
    "#\n",
    "# ToDo\n",
    "#   1. Add Assigned cash.\n",
    "#   2. Track assigned shares, cum share count, and current share value. \n",
    "#   3. Get share cost basis from broker report analysis.\n",
    "#   4. Show separate row that totals net share gain / loss for trades.\n",
    "#\n",
    "# **********\n",
    "\n",
    "#print(dct[['Option Type', 'Close Action', 'assigned_cf_sign', 'Contracts', 'Strike Price', 'Assigned Cash']])\n",
    "\n",
    "#1. Construct DC_table_by_root. Abbr dct_by_root\n",
    "dct_by_root = dct.drop(columns = ['assigned_cf_sign', 'Close Action', 'Option Type']).copy()\n",
    "\n",
    "#Goal - Calculate cash flow by root, by Close Action by Option Type 4. By all\n",
    "tlc_agg_dict = {'Contracts': 'sum', 'Open Fee': 'sum', 'Open Commsn': 'sum'}\n",
    "\n",
    "tlc_mo_group = tlc_short_index.groupby(level = [0, 1, 2, 3]).agg(tlc_agg_dict)                                        \n",
    "\n",
    "#1. Reset index prior to dropping columns. \n",
    "#1a. Drop all unneeded index cols except month, cash cols, and the column which you to do cash flow for.\n",
    "#2. Set as index the target column and month.\n",
    "#3. Groupby index. This eliminates the subtotals.\n",
    "#4. Unstack by month.\n",
    "#5. Voila. See the cash flow. But only for a total row. Get subtotal lines later, by appending subtotals.\n",
    "\n",
    "# CASH FLOW BY ROOT - \n",
    "tlc_root_cash = tlc.copy()\n",
    "tlc_root_cash.reset_index(drop = True, inplace = True)\n",
    "cash_root_drop_cols = ['Option Type', 'Close Action', 'Contracts', 'Metric-POW', 'Metric-PctFee', 'Metric-ARR']\n",
    "tlc_root_cash = tlc.drop(columns = cash_root_drop_cols).copy()\n",
    "\n",
    "print('tlc_root_cash')\n",
    "print(tlc_root_cash)\n",
    "\n",
    "# CASH FLOW BY OPTION TYPE - \n",
    "tlc_option_type_cash = tlc.copy()\n",
    "tlc_option_type_cash.reset_index(drop = True, inplace = True)\n",
    "cash_option_drop_cols = ['Root', 'Metric-POW', 'Metric-PctFee', 'Metric-ARR']\n",
    "tlc_option_cash = tlc.drop(columns = cash_option_drop_cols).copy()\n",
    "print('tlc_option_cash')\n",
    "print(tlc_option_cash)\n",
    "\n",
    "stop\n",
    "\n",
    "#sort_cols = ['Root', 'Open Month']\n",
    "#tlc_root_cash.sort_values(by = sort_cols, axis = 0, ascending = True, inplace = True) #Maybe not needed.\n",
    "#tlc_root_cash.set_index(sort_cols, append = False, inplace = True)\n",
    "print('tlc_root_cash - row indexes Root, Open Month')\n",
    "print(tlc_root_cash)\n",
    "\n",
    "tlc_root_agg_dict = {'Open Fee': 'sum', 'Open Commsn': 'sum'}\n",
    "\n",
    "tlc_root_cash_agg = tlc_root_cash.groupby(level = 0).agg(tlc_root_agg_dict)                                        \n",
    "print('tlc_root_cash_agg, grouped by level = 0')\n",
    "print(tlc_root_cash_agg)\n",
    "tlc_root_cash_agg.to_csv('tlc_root_cash_agg.csv')\n",
    "\n",
    "# Unstack with sum for duplicates.\n",
    "tlc_root_cash_agg_unstack = tlc_root_cash_agg.unstack(level = -1).copy()\n",
    "print('tlc_root_cash_agg_unstack, level = -1 - should be month')\n",
    "print(tlc_root_cash_agg_unstack)\n",
    "\n",
    "tlc_root_cash_agg_by_mo = tlc_unstack_by_mo.copy()\n",
    "print(tlc_root_cash_agg_by_mo)\n",
    "tlc_root_cash_agg_by_mo.to_csv('tlc_cash_by_root.csv')\n",
    "\n",
    "# 2a. Create column multi-level index. Troublesome. \n",
    "tlc_short_index_unstacked = tlc_short_index.unstack()    # Creates multiindex on columns.\n",
    "print('tlc_short_index_unstacked')\n",
    "print(tlc_short_index_unstacked)\n",
    "tlc_short_index_unstacked.to_csv('test_DC_results_tlc_short_index_unstacked.csv')\n",
    "print(' Creates multiindex on columns.')\n",
    "print('     Learn how to address single cols with multiindex for groupby.agg')\n",
    "print(' ')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77870eb0",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ****************\n",
    "# DashboardTrades (DT) 08/28/22 Updated df for scoreboard from closed option files. \n",
    "# *****************\n",
    "# Goal - Create df with metrics in rows by month across the columns. Use stack, then groupby.\n",
    "#        Create df with metrics grouped by option_type, close_action     \n",
    "#\n",
    "# Inputs  - df of Closed trade file in POWARRMax format\n",
    "# Outputs - df of Score metrics in month order\n",
    "#\n",
    "# Next -  Get ER Dates from IEX. Fix ER to handle error \"ER date unavailable.\"\n",
    "#         For any ER Date that is past, just add 90 as an estimate.\n",
    "#         Fix columns so I do less manual work in Excel.\n",
    "#         Get new date suffix from keyboard input.\n",
    "#\n",
    "# TODO - ????\n",
    "# 1. Setup screens for five key parameters - POW, PctOTM, ARR, PctFee, Bid/Ask spread. Daysout not needed.  \n",
    "# 2. Create columns 'Pass__'for each screened factor. Simple 1/0 pass/fail.\n",
    "# 3. Outputs: good_trades_df - with pass cols, and good_trade_stats - factor settings, sensitivity, correls, etc.\n",
    "# \n",
    "#\n",
    "#   08/29/22 - Unstack works for the test file.\n",
    "#   08/14/22 - Started with coode from OptionAnalyzer50#\n",
    "#\n",
    "# *****************\n",
    "#\n",
    "# Import Libraries\n",
    "#\n",
    "# *****************\n",
    "#\n",
    "import csv\n",
    "import copy\n",
    "import datetime\n",
    "import json\n",
    "import numpy as np\n",
    "import os\n",
    "import pandas as pd\n",
    "import requests   # for http requests\n",
    "import scipy\n",
    "from   scipy import stats\n",
    "from   scipy.stats import norm\n",
    "import time\n",
    "from   datetime import datetime\n",
    "from   datetime import date\n",
    "#from   datetime import time\n",
    "from   datetime import timedelta\n",
    "\n",
    "from   dateutil.relativedelta  import relativedelta\n",
    "\n",
    "\n",
    "# *****************\n",
    "#\n",
    "# CONSTANTS\n",
    "#\n",
    "# *****************\n",
    "\n",
    "FILE_ID_TAG       = 'test_DT'\n",
    "\n",
    "\n",
    "# *****************\n",
    "# DashboaardTrades written as Main Program. Later convert to fct.\n",
    "# MAIN PROGAM STARTS HERE\n",
    "# \n",
    "# *****************\n",
    "\n",
    "#Stack / Groupby Process\n",
    "# 1. Separate columns into closed_trades into keys, metrics, and other. \n",
    "# 2. Create multi-level row index with all keys.\n",
    "# 3. Create two-level collumn index w / \"Metrics / met1, met2, ...\n",
    "# 4. Stack operates on column indexes. Starts with innermost COLUMN index level.\n",
    "# 4.1   Metrics / met1, met2... This may force other colums to stack also.\n",
    "#       Use p.MultiIndex.fromframe\n",
    "# 5. Groupby row indexes.\n",
    "# 6. Calculate metric aggregate stats with groupppby columns.\n",
    "# 7. Delete individual trade records.\n",
    "# 8. Create new row index with Month as innermost key. Metric / met1, met2, ... must be higher level row keys.\n",
    "# 9. Unstack Month. \n",
    "# Now have df with metric aggregate values as rows, and Month # as row.\n",
    "\n",
    "tlc = pd.read_csv('test_DT_fake_data.csv')\n",
    "#fake_data columns = key1, key2, key3, day, other-date1, Metric-POW, Metric-ARR, Metric-PctFee, other-acct\n",
    " \n",
    "# 1. Separate columns into closed_trades into keys, metrics, and other. \n",
    "#sort_cols = ['Root', 'Option Type', 'Open Date', 'Open Month']\n",
    "sort_cols = ['key1', 'key2', 'key3', 'day']\n",
    "tlc.sort_values(by = sort_cols, axis = 0, ascending = True, inplace = True)\n",
    "\n",
    "# 2. Create multi-level row index with all keys.\n",
    "index_labels = ['key1', 'key2', 'key3', 'day']\n",
    "tlc.set_index(index_labels, append = False, inplace = True)\n",
    "\n",
    "# Drop all columns net needed.\n",
    "tlc.drop(columns = 'date1')\n",
    "\n",
    "#MultiIndex = ([('3': 'Metric-POW'), ('3', 'Metric-ARR'), ('3', 'Metric-PctFee')],\n",
    "#              [('5': 'Metric-POW'), ('5', 'Metric-ARR'), ('5', 'Metric-PctFee')],\n",
    "#              [('7': 'Metric-POW'), ('7', 'Metric-ARR'), ('7', 'Metric-PctFee')],\n",
    "#              [('8': 'Metric-POW'), ('8', 'Metric-ARR'), ('8', 'Metric-PctFee')])\n",
    "\n",
    "unstacked_tlc = tlc.unstack()    # Single level stack creates a series. \n",
    "print('unstacked_tlc - 1 level')\n",
    "print(unstacked_tlc)\n",
    "unstacked_tlc.to_csv('test_DT_results_unstacked_tlc.csv')\n",
    "print(' ')\n",
    "\n",
    "# 3. Create two-level column index w / \"Metrics / met1, met2, ... Maybe not needed.\n",
    "\n",
    "# 4. UnStack Metrics / met1, met2... This may force other colums to stack also.\n",
    "#      Maybe not needed.\n",
    "#\n",
    "#5. Groupby row indexes. Identify row indexes by col names\n",
    "#y = df.groupby(level=['region'])['individuals'].mean()\n",
    "\n",
    "groupby_test = unstacked_tlc.groupby(level =['key2'])\n",
    "test_DT_groupby_stats\n",
    "# 6. Calculate metric aggregate stats with groupppby columns.\n",
    "# 7. Delete individual trade records.\n",
    "# 8. Create new row index with Month as innermost key. Metric / met1, met2, ... must be higher level row keys.\n",
    "# 9. Unstack Month. \n",
    "# Now have df with metric aggregate values as rows, and Month # as row.\n",
    "\n",
    "# multi_col_tlc = pd.MultiIndex.from_tuples([('Month', Root', 'Option Type', 'Open Date'],\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "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.10.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}