V1/engineering-best-practises/Example Data Load.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "777f25ae",
   "metadata": {},
   "outputs": [],
   "source": [
    "from lusidtools.jupyter_tools import toggle_code\n",
    "\n",
    "\"\"\"Example Data Load\n",
    "\n",
    "Loads some example data into LUSID\n",
    "\n",
    "Attributes\n",
    "----------\n",
    "transactions\n",
    "cutlabels\n",
    "quotes\n",
    "valuation\n",
    "portfolio groups\n",
    "\n",
    "\"\"\"\n",
    "\n",
    "toggle_code(\"Toggle Docstring\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9deef8e",
   "metadata": {},
   "source": [
    "# Example Data Load"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "46a8fcdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Importing required packages\n",
    "import os\n",
    "import pandas as pd\n",
    "import datetime\n",
    "from datetime import timezone\n",
    "import pytz\n",
    "import requests.exceptions\n",
    "\n",
    "import lusid\n",
    "import lusid.models as models\n",
    "from lusid.utilities import ApiClientFactory\n",
    "from lusidjam.refreshing_token import RefreshingToken\n",
    "\n",
    "pd.set_option(\"display.max_columns\", None)\n",
    "\n",
    "# Authenticate our user and create our API client\n",
    "secrets_path = os.getenv(\"FBN_SECRETS_PATH\")\n",
    "\n",
    "# Initiate an API Factory which is the client side object for interacting with LUSID APIs\n",
    "api_factory = lusid.utilities.ApiClientFactory(\n",
    "    token=RefreshingToken(),\n",
    "    api_secrets_filename=secrets_path,\n",
    "    app_name=\"LusidJupyterNotebook\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "206b2d5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# construct the API we want to use\n",
    "cut_label_definitions_api = api_factory.build(lusid.api.CutLabelDefinitionsApi)\n",
    "portfolios_api = api_factory.build(lusid.api.PortfoliosApi)\n",
    "transaction_portfolios_api = api_factory.build(lusid.api.TransactionPortfoliosApi)\n",
    "quotes_api = api_factory.build(lusid.api.QuotesApi)\n",
    "configuration_recipe_api = api_factory.build(lusid.api.ConfigurationRecipeApi)\n",
    "portfolio_groups_api = api_factory.build(lusid.api.PortfolioGroupsApi)\n",
    "instruments_api = api_factory.build(lusid.api.InstrumentsApi)\n",
    "valuations_api = api_factory.build(lusid.api.AggregationApi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3b097da4",
   "metadata": {},
   "outputs": [],
   "source": [
    "scope = \"Finbourne-Examples\"\n",
    "market_data_scope = \"FinbourneMarketData\"\n",
    "\n",
    "datafile = \"ExampleData.xlsx\"\n",
    "date = datetime.datetime(2021, 1, 1, 0, 0, 0, 0, tzinfo=timezone.utc)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "755491be",
   "metadata": {},
   "outputs": [],
   "source": [
    "## Loading Data\n",
    "portfolios = pd.read_excel(datafile,sheet_name = 'Portfolios')\n",
    "holdings = pd.read_excel(datafile,sheet_name = 'Holdings')\n",
    "transactions = pd.read_excel(datafile,sheet_name = 'Transactions')\n",
    "instruments = pd.read_excel(datafile,sheet_name = 'Instruments')\n",
    "quotes = pd.read_excel(datafile,sheet_name = 'Quotes').fillna('')\n",
    "rates = pd.read_excel(datafile,sheet_name = 'Rates').fillna('')\n",
    "recipes = pd.read_excel(datafile,sheet_name = 'Recipes')\n",
    "groups = pd.read_excel(datafile,sheet_name = 'Groups')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3ee9f06",
   "metadata": {},
   "source": [
    "### Creating CutLabels"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64ac4608",
   "metadata": {},
   "outputs": [],
   "source": [
    "cut_labels = [[\"LDN_Open\",\"LondonOpen\",\"London Market Open Time\", models.CutLocalTime(8, 0), \"Europe/London\"],\n",
    "             [\"LDN_Close\",\"LondonClose\",\"London Market Close Time\", models.CutLocalTime(16, 30), \"Europe/London\"],\n",
    "             [\"NY_Close\",\"NewYorkClose\",\"New York Market Close Time\", models.CutLocalTime(16, 00), \"America/New_York\"],\n",
    "             [\"NY_Open\",\"NewYorkOpen\",\"New York Market Open Time\", models.CutLocalTime(9, 30), \"America/New_York\"]]\n",
    "\n",
    "def create_cut_label(cut_label, overwrite=True):\n",
    "    '''\n",
    "    Input: \n",
    "        cut_label (list): ordered list where \n",
    "                            [0] == code (str), \n",
    "                            [1] == display_name (str), \n",
    "                            [2] == description (str), \n",
    "                            [3] == cut_local_time (models.cutLabelTime),\n",
    "                            [4] == time_zone (str)\n",
    "        overwrite (bool): whether to overwrite existing cutlabel in the provided code if it exists, defaults to True\n",
    "    Output:\n",
    "        resp (lusid.models.cut_label_definition.CutLabelDefinition): Cut Label definition of the created cut label\n",
    "    '''\n",
    "\n",
    "    cut_label_req = models.CreateCutLabelDefinitionRequest(code=cut_label[0],\n",
    "                                                     display_name=cut_label[1],\n",
    "                                                     description=cut_label[2],\n",
    "                                                     cut_local_time=cut_label[3],\n",
    "                                                     time_zone=cut_label[4])\n",
    "    \n",
    "    ## Try creating cutLabel, throws exception if already exists\n",
    "    try:\n",
    "        cut_label_definitions_api.create_cut_label_definition(create_cut_label_definition_request=cut_label_req)\n",
    "    except:\n",
    "        if overwrite:\n",
    "            resp = cut_label_definitions_api.delete_cut_label_definition(code=cut_label[0])\n",
    "            cut_label_definitions_api.create_cut_label_definition(create_cut_label_definition_request=cut_label_req)\n",
    "            \n",
    "    return cut_label_definitions_api.get_cut_label_definition(code=cut_label[0])\n",
    "\n",
    "created_cut_labels = [create_cut_label(c) \n",
    "                      for c in cut_labels]\n",
    "        \n",
    "created_cut_labels"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac864349",
   "metadata": {},
   "source": [
    "### Creating Instruments"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3f3d905a",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_instrument(instrument, overwrite=True):\n",
    "    '''\n",
    "    Input: \n",
    "        instrument (dict): dictionary with fields \n",
    "                            'name' == name of the instrument (str), \n",
    "                            'Figi' == Figi identifier (str)\n",
    "        overwrite (bool): whether to overwrite existing instrument (hard-delete) in the provided code if it exists, defaults to True\n",
    "    Output:\n",
    "        resp (lusid.models.instrument.Instrument): Instrument that was created\n",
    "    '''\n",
    "    ## Checks if Instrument already exist\n",
    "    try:\n",
    "        # Check if Instrument with FIGI already exist\n",
    "        resp = instruments_api.get_instrument(\"Figi\",instrument['Figi'])\n",
    "        if overwrite:\n",
    "            resp = instruments_api.delete_instruments(request_body = [resp.lusid_instrument_id],delete_mode = 'Hard')\n",
    "        else:\n",
    "            return resp\n",
    "\n",
    "    except:\n",
    "        pass\n",
    "\n",
    "    instrument_req = {\"inst\":models.InstrumentDefinition(\n",
    "                                    name=instrument[\"name\"],\n",
    "                                    identifiers={\"Figi\":models.InstrumentIdValue(value = instrument['Figi'])}\n",
    "                                    )\n",
    "                    }\n",
    "    instruments_api.upsert_instruments(request_body=instrument_req)\n",
    "    return instruments_api.get_instrument(\"Figi\",instrument['Figi'])\n",
    "\n",
    "created_instruments = [create_instrument(inst.to_dict()) \n",
    "                       for i,inst in instruments.iterrows()]\n",
    "    \n",
    "created_instruments"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "11dedbe8",
   "metadata": {},
   "source": [
    "### Creating Portfolios"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1bfa9cd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_portfolio(portfolio, overwrite=True):\n",
    "    '''\n",
    "    Input: \n",
    "        portfolio (dict): dictionary with fields \n",
    "                            'Code' == Code for the portfolio (str), \n",
    "                            'Display Name' == Display name of the identifier (str), \n",
    "                            'Base Currency' == Base Currency (str)\n",
    "        overwrite (bool): whether to overwrite existing portfolio (hard-delete) in the provided code if it exists, defaults to True\n",
    "    Output:\n",
    "        resp (lusid.models.portfolio.Portfolio): Portfolio that was created\n",
    "    '''\n",
    "    port_req = models.CreateTransactionPortfolioRequest(code=portfolio['Code'],\n",
    "                                                       display_name=portfolio['Display Name'],\n",
    "                                                       base_currency=portfolio['Base Currency'],\n",
    "                                                       created=date)\n",
    "    \n",
    "    ## Try creating portfolio, throws exception if already exists\n",
    "    try:\n",
    "        transaction_portfolios_api.create_portfolio(scope=scope,create_transaction_portfolio_request=port_req)\n",
    "    except:\n",
    "        if overwrite:\n",
    "            portfolios_api.delete_portfolio(scope=scope,code=portfolio['Code'])\n",
    "            transaction_portfolios_api.create_portfolio(scope=scope,create_transaction_portfolio_request=port_req)\n",
    "    \n",
    "    return portfolios_api.get_portfolio(scope=scope,code=portfolio['Code'])\n",
    "\n",
    "created_portfolios = [create_portfolio(port.to_dict()) \n",
    "                      for i,port in portfolios.iterrows()]\n",
    "\n",
    "created_portfolios"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c6ec941c",
   "metadata": {},
   "source": [
    "### Creating Portfolio Groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7dd1d925",
   "metadata": {},
   "outputs": [],
   "source": [
    "port_groups = groups[['Group Name', 'Display Name', 'Description','Created Date']].drop_duplicates().reset_index(drop=True)\n",
    "port_groups['Sub-groups'] = [list(set(groups.query(f\"`Group Name`=='{grp['Group Name']}' and `Display Name`=='{grp['Display Name']}' and Description == '{grp['Description']}' and `Created Date` == '{grp['Created Date']}' \").dropna(subset=['Sub-groups'])['Sub-groups'])) \n",
    "                            for i,grp in port_groups.iterrows()]\n",
    "port_groups['Portfolios'] = [list(set(groups.query(f\"`Group Name`=='{grp['Group Name']}' and `Display Name`=='{grp['Display Name']}' and Description == '{grp['Description']}' and `Created Date` == '{grp['Created Date']}' \").dropna(subset=['Portfolios'])['Portfolios'])) \n",
    "                            for i,grp in port_groups.iterrows()]\n",
    "portfolio_groups = [port_groups.iloc[i].to_dict() for i in range(len(port_groups))]\n",
    "portfolio_groups.reverse()\n",
    "\n",
    "def create_portfolio_group(portfolio_group, overwrite=True):\n",
    "    '''\n",
    "    Input: \n",
    "        portfolio_group (dict): dictionary with fields \n",
    "                                'Group Name': code of the portfolio group (str),\n",
    "                                'Display Name': Display name of the portfolio group (str),\n",
    "                                'Description': Description of the portfolio group (str),\n",
    "                                'Created Date': Date of creation in YYYY-MM-DDTHH:mm:ssZ (str),\n",
    "                                'Sub-groups': list of codes of sub portfolio groups that must exist (list[(str)]),\n",
    "                                'Portfolios': list of codes of portfolio that must exist (list[(str)])\n",
    "        overwrite (bool): whether to overwrite existing portfolioGroup (hard-delete) in the provided code if it exists, defaults to True\n",
    "    Output:\n",
    "        resp (lusid.models.portfolio_group.PortfolioGroup): portfolio_group that was created\n",
    "    '''\n",
    "    port_grp_req = models.CreatePortfolioGroupRequest(code=portfolio_group['Group Name'],\n",
    "                                                    created=portfolio_group['Created Date'],\n",
    "                                                    values=[lusid.ResourceId(scope=scope, code = port) \n",
    "                                                            for port in portfolio_group['Portfolios']], \n",
    "                                                    sub_groups=[lusid.ResourceId(scope=scope, code = subGrp) \n",
    "                                                                for subGrp in portfolio_group['Sub-groups']],\n",
    "                                                    description=portfolio_group['Description'],\n",
    "                                                    display_name=portfolio_group['Display Name'])\n",
    "    \n",
    "    ## Try creating portfolio, throws exception if already exists\n",
    "    try:\n",
    "        portfolio_groups_api.create_portfolio_group(scope=scope,create_portfolio_group_request=port_grp_req)\n",
    "    except:\n",
    "        if overwrite:\n",
    "            portfolio_groups_api.delete_portfolio_group(scope=scope, code= portfolio_group['Group Name'])\n",
    "            portfolio_groups_api.create_portfolio_group(scope=scope,create_portfolio_group_request=port_grp_req)\n",
    "    \n",
    "    return portfolio_groups_api.get_portfolio_group(scope=scope, code= portfolio_group['Group Name'])\n",
    "\n",
    "created_portfolio_groups = [create_portfolio_group(grp) \n",
    "                            for grp in portfolio_groups]\n",
    "    \n",
    "created_portfolio_groups"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8446f8d",
   "metadata": {},
   "source": [
    "### Set Holdings"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dfe5b7ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "def set_holdings(scope, port, port_holdings):\n",
    "    '''\n",
    "    Input: \n",
    "        port_holdings (pandas.DataFrame): pandas dataframe with the following columns \n",
    "                                'Portfolio Code': code of the portfolio (str),\n",
    "                                'instrument_uid': identifier of the instrument (identifierkey:value) (dict),\n",
    "                                'Instrument Name': name of the instrument (str),\n",
    "                                'Holding Type': type of the holding (str),\n",
    "                                'units': number of the given instrument (float),\n",
    "                                'price': price of the instrument (float),\n",
    "                                'cost.currency': CCY code (str),\n",
    "                                'purchase_date': purchase date in UTC (pandas._libs.tslibs.timestamps.Timestamp),\n",
    "                                'settlement_date': settlmeent date in UTC (pandas._libs.tslibs.timestamps.Timestamp),\n",
    "                                'cost.amount': cost of the transaction to get this holding in cost currency (float),\n",
    "                                'portfolio_cost': cost of the transaction to get this holding in portfolio currency (float)\n",
    "    Output:\n",
    "        resp (lusid.models.adjust_holding.AdjustHolding): AdjustHolding response\n",
    "    '''\n",
    "    holdingsReq = [models.AdjustHoldingRequest(instrument_identifiers={hold['instrument_uid'].split(':')[0]:hold['instrument_uid'].split(':')[1]},\n",
    "                                           tax_lots=[models.TargetTaxLotRequest(units=hold['units'],\n",
    "                                                                                cost=models.CurrencyAndAmount(amount=hold['cost.amount'],\n",
    "                                                                                                              currency=hold['cost.currency']),\n",
    "                                                                                portfolio_cost=hold['portfolio_cost'],\n",
    "                                                                                price=hold['price'],\n",
    "                                                                                purchase_date=pytz.timezone('UTC').localize(hold['purchase_date'].to_pydatetime()).isoformat(),\n",
    "                                                                                settlement_date=pytz.timezone('UTC').localize(hold['settlement_date'].to_pydatetime()).isoformat())],\n",
    "                                          currency=hold['cost.currency']) \n",
    "               for i,hold in port_holdings.iterrows()]\n",
    "\n",
    "    resp = transaction_portfolios_api.set_holdings(scope=scope,\n",
    "                                          code=port,\n",
    "                                          effective_at=date.isoformat(),\n",
    "                                          adjust_holding_request=holdingsReq)\n",
    "    return resp\n",
    "\n",
    "set_holdings_response = [set_holdings(scope, port, holdings.query(f\"`Portfolio Code` == '{port}'\")) \n",
    "                         for port in holdings['Portfolio Code'].unique()]\n",
    "    \n",
    "set_holdings_response"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6da88775",
   "metadata": {},
   "source": [
    "### Create Transactions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0833572a",
   "metadata": {},
   "outputs": [],
   "source": [
    "upsert_transaction_response = []\n",
    "\n",
    "def upsert_transactions(scope, port, port_trans):\n",
    "    '''\n",
    "    Input: \n",
    "        port_trans (pandas.DataFrame): pandas dataframe with the following columns \n",
    "                                'Portfolio Code': code of the portfolio (str),\n",
    "                                'transaction_id' : ID of this transaction (str),\n",
    "                                'instrument_uid': identifier of the instrument (identifierkey:value) (dict),\n",
    "                                'type' : type of this transaction (str),\n",
    "                                'transaction_date' : transaction date in UTC (pandas._libs.tslibs.timestamps.Timestamp),\n",
    "                                'settlement_date': settlement date in UTC (pandas._libs.tslibs.timestamps.Timestamp),\n",
    "                                'units': number of the given instrument (float),\n",
    "                                'total_consideration.currency' : CCY code (str),\n",
    "                                'transaction_price.price' : price at which the instrument was transacted at (float),\n",
    "                                'total_consideration.amount' : amount of the particular instrument transacted (float),\n",
    "                                'source : source of this transaction (str)'\n",
    "    Output:\n",
    "        resp (lusid.models.adjust_holding.AdjustHolding): AdjustHolding response\n",
    "    '''\n",
    "    trans_req = [models.TransactionRequest(transaction_id=trans['transaction_id'], \n",
    "                                          type=trans['type'],\n",
    "                                          instrument_identifiers={trans['instrument_uid'].split(':')[0]:trans['instrument_uid'].split(':')[1]}, \n",
    "                                          transaction_date=pytz.timezone('UTC').localize(trans['transaction_date'].to_pydatetime()).isoformat(),\n",
    "                                          settlement_date=pytz.timezone('UTC').localize(trans['settlement_date'].to_pydatetime()).isoformat(), \n",
    "                                          units=trans['units'],\n",
    "                                          transaction_price=models.TransactionPrice(price=trans['transaction_price.price']),\n",
    "                                          total_consideration=models.CurrencyAndAmount(amount=trans['total_consideration.amount'],\n",
    "                                                                                       currency=trans['total_consideration.currency']),\n",
    "                                          source=trans['source'])\n",
    "                for i,trans in port_trans.iterrows()]\n",
    "\n",
    "    resp = transaction_portfolios_api.upsert_transactions(scope=scope,\n",
    "                                                        code=port,\n",
    "                                                        transaction_request=trans_req)\n",
    "    return resp\n",
    "\n",
    "\n",
    "upsert_transaction_response = [upsert_transactions(scope, port, transactions.query(f\"`Portfolio Code` == '{port}'\")) \n",
    "                               for port in transactions['Portfolio Code'].unique()]    \n",
    "\n",
    "upsert_transaction_response"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34396f00",
   "metadata": {},
   "source": [
    "### Create Quotes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "96501eba",
   "metadata": {},
   "outputs": [],
   "source": [
    "quotes_req = {\n",
    "    f\"{quote['instrument_uid']}-{str(quote['effective_at'])}-{quote['field']}\":\n",
    "    lusid.UpsertQuoteRequest(\n",
    "        quote_id=lusid.QuoteId(\n",
    "            quote_series_id = lusid.QuoteSeriesId(provider = quote['provider'],\n",
    "                                                  price_source=quote[\"source\"],\n",
    "                                                  instrument_id = quote['instrument_uid'], \n",
    "                                                  instrument_id_type=quote['instrument_uid_type'], \n",
    "                                                  quote_type=quote['quote_type'], \n",
    "                                                  field=quote['field']),\n",
    "            effective_at= pytz.timezone('UTC').localize(quote['effective_at'].to_pydatetime()).isoformat()), \n",
    "        metric_value=lusid.MetricValue(value=quote['metric_value'],\n",
    "                                       unit=quote['metric_unit']))\n",
    "    for i,quote in quotes.iterrows()\n",
    "}\n",
    "    \n",
    "## Upserting Quotes\n",
    "quote_numbers = len(quotes_req)\n",
    "max_per_upsert = 500\n",
    "number_of_Req = quote_numbers/max_per_upsert\n",
    "quote_keys = list(quotes_req.keys())\n",
    "upsert_results = []\n",
    "for i in range(int(number_of_Req)):\n",
    "    request_keys = quote_keys[i*max_per_upsert : (i+1)*max_per_upsert]\n",
    "    request = dict(zip(request_keys, [quotes_req[key] for key in request_keys]))\n",
    "    upsert_results.append(quotes_api.upsert_quotes(scope=market_data_scope, request_body=request))\n",
    "    \n",
    "if quote_numbers - ((i+1)*max_per_upsert) > 0:\n",
    "    request_keys = quote_keys[((i+1)*max_per_upsert) : ]\n",
    "    request = dict(zip(request_keys, [quotes_req[key] for key in request_keys]))\n",
    "    upsert_results.append(quotes_api.upsert_quotes(scope=market_data_scope, request_body=request))\n",
    "\n",
    "    \n",
    "print(\"Failures: \", [i.failed for i in upsert_results])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0ee5b49b",
   "metadata": {},
   "source": [
    "### Create FX Rates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e171be13",
   "metadata": {},
   "outputs": [],
   "source": [
    "rates_req = {\n",
    "    f\"{rate['instrument_uid']}-{str(rate['effective_at'])}-{rate['field']}\" :\n",
    "    lusid.UpsertQuoteRequest(\n",
    "        quote_id=lusid.QuoteId(\n",
    "            quote_series_id = lusid.QuoteSeriesId(provider = 'Client', \n",
    "                                                  price_source=rate[\"source\"],\n",
    "                                                  instrument_id = rate['instrument_uid'], \n",
    "                                                  instrument_id_type=rate['instrument_uid_type'], \n",
    "                                                  quote_type=rate['quote_type'], \n",
    "                                                  field=rate['field']),\n",
    "            effective_at = pytz.timezone('UTC').localize(rate['effective_at'].to_pydatetime()).isoformat()), \n",
    "        metric_value=lusid.MetricValue(value=rate['metric_value'],\n",
    "                                       unit=rate['metric_unit']))\n",
    "    for i,rate in rates.iterrows()\n",
    "}\n",
    "\n",
    "## Upserting Quotes\n",
    "quote_numbers = len(rates_req)\n",
    "max_per_upsert = 500\n",
    "number_of_Req = quote_numbers/max_per_upsert\n",
    "quote_keys = list(rates_req.keys())\n",
    "upsert_results = []\n",
    "for i in range(int(number_of_Req)):\n",
    "    request_keys = quote_keys[i*max_per_upsert : (i+1)*max_per_upsert]\n",
    "    request = dict(zip(request_keys, [rates_req[key] for key in request_keys]))\n",
    "    upsert_results.append(quotes_api.upsert_quotes(scope=market_data_scope, request_body=request))\n",
    "    \n",
    "if quote_numbers - ((i+1)*max_per_upsert) > 0:\n",
    "    request_keys = quote_keys[((i+1)*max_per_upsert) : ]\n",
    "    request = dict(zip(request_keys, [rates_req[key] for key in request_keys]))\n",
    "    upsert_results.append(quotes_api.upsert_quotes(scope=market_data_scope, request_body=request))\n",
    "\n",
    "    \n",
    "print(\"Failures: \", [i.failed for i in upsert_results])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "78afc7ca",
   "metadata": {},
   "source": [
    "### Create Recipes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "aad6e7db",
   "metadata": {},
   "outputs": [],
   "source": [
    "created_recipes = []\n",
    "\n",
    "def create_recipe(code, rec_rules):\n",
    "    '''\n",
    "    Input: \n",
    "        rec_rules (pandas.DataFrame): pandas dataframe with the following columns \n",
    "                                'Description' : description of the recipe (str),\n",
    "                                'Key' : market data key (str),\n",
    "                                'Supplier' : supplier of the market data (str),\n",
    "                                'QuoteType' : type of the quote (str),\n",
    "                                'QuoteInterval' : interval of the quote (str),\n",
    "                                'Field' : field of the quote (str)\n",
    "    Output:\n",
    "        resp (lusid.models.adjust_holding.AdjustHolding): AdjustHolding response\n",
    "    '''\n",
    "    description = rec_rules.Description.unique()[0]\n",
    "    supplier = \"Client\" #rec_rules.Supplier.unique()[0]\n",
    "    recipe_req = models.UpsertRecipeRequest(\n",
    "                        configuration_recipe=lusid.ConfigurationRecipe(\n",
    "                            scope=scope,\n",
    "                            code=code,\n",
    "                            description=description,\n",
    "                            market=models.MarketContext(market_rules=[\n",
    "                                models.MarketDataKeyRule(key=rule['Key'],\n",
    "                                   supplier=rule['Supplier'],\n",
    "                                   data_scope=market_data_scope,\n",
    "                                   quote_type=rule['QuoteType'],\n",
    "                                   field=rule['Field'],\n",
    "                                   quote_interval=rule['QuoteInterval'])\n",
    "                            for i,rule in rec_rules.iterrows()],\n",
    "                            suppliers=lusid.MarketContextSuppliers(\n",
    "                                commodity=supplier,\n",
    "                                credit=supplier,\n",
    "                                equity=supplier,\n",
    "                                fx=supplier,\n",
    "                                rates=supplier),\n",
    "                           options=models.MarketOptions(\n",
    "                                default_supplier='DataScope',\n",
    "                                default_instrument_code_type='Figi',\n",
    "                                default_scope=market_data_scope)\n",
    "                               )\n",
    "                        )\n",
    "                ) \n",
    "\n",
    "    configuration_recipe_api.upsert_configuration_recipe(upsert_recipe_request = recipe_req)\n",
    "    return configuration_recipe_api.get_configuration_recipe(scope=scope, code=code)\n",
    "\n",
    "created_recipes = [create_recipe(rec, recipes.query(f\"Code == '{rec}'\")) \n",
    "                   for rec in recipes['Code'].unique()]\n",
    "\n",
    "created_recipes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97a95359",
   "metadata": {},
   "outputs": [],
   "source": [
    "[i.value.code for i in created_recipes]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a522d74d",
   "metadata": {},
   "source": [
    "### Test Aggregation using created Recipe"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7071e27c",
   "metadata": {},
   "outputs": [],
   "source": [
    "AGG_INSTR = \"Instrument/default/Name\"\n",
    "AGG_UID = \"Instrument/default/LusidInstrumentId\"\n",
    "AGG_PV = \"Holding/default/PV\"\n",
    "AGG_PRC = \"Holding/default/Price\"\n",
    "AGG_UNITS = \"Holding/default/Units\"\n",
    "AGG_COST = \"Holding/default/Cost\"\n",
    "AGG_TYPE = \"Holding/default/Type\"\n",
    "AGG_RATE = \"Holding/default/ExchangeRate\"\n",
    "\n",
    "\n",
    "metrics = [\n",
    "    models.AggregateSpec(AGG_INSTR, \"Value\"),\n",
    "    models.AggregateSpec(AGG_UID, \"Value\"),\n",
    "    models.AggregateSpec(AGG_TYPE, \"Value\"),\n",
    "    models.AggregateSpec(AGG_PV, \"Value\"),\n",
    "    models.AggregateSpec(AGG_UNITS, \"Value\"),\n",
    "    models.AggregateSpec(AGG_COST, \"Value\"),\n",
    "    models.AggregateSpec(AGG_RATE, \"Value\"),\n",
    "    models.AggregateSpec(AGG_PRC, \"Value\"),\n",
    "    ]\n",
    "\n",
    "\n",
    "valuation_request = models.ValuationRequest(\n",
    "    recipe_id=models.ResourceId(scope, created_recipes[0].value.code),\n",
    "    valuation_schedule=models.ValuationSchedule(effective_at=pd.to_datetime('2021-05-17', utc=True).isoformat()),\n",
    "    portfolio_entity_ids=[\n",
    "        models.PortfolioEntityId(\n",
    "                scope=created_portfolios[1].id.scope,\n",
    "                code=created_portfolios[1].id.code,\n",
    "                portfolio_entity_type=\"SinglePortfolio\")\n",
    "    ],\n",
    "    metrics=metrics\n",
    ")\n",
    "\n",
    "valuations_api.get_valuation(valuation_request=valuation_request)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6e84732",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}