portfolio_yield.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%reload_ext autoreload\n",
    "%autoreload 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from finargy.api.client import InvertirOnlineAPI\n",
    "import pandas as pd\n",
    "import datetime\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "client = InvertirOnlineAPI()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# print(client.access_token)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "historical_prices_path = \"data/historical_prices.csv\"\n",
    "account_movements_path = \"data/portfolio/account_movements_df.csv\"\n",
    "\n",
    "# Get historical prices\n",
    "historical_prices = pd.read_csv(historical_prices_path)\n",
    "historical_prices[\"fechaHora_date\"] = pd.to_datetime(\n",
    "    historical_prices[\"fechaHora_date\"]\n",
    ")\n",
    "# get movements\n",
    "movements_df = pd.read_csv(account_movements_path)\n",
    "movements_df[\"fechaOperada\"] = pd.to_datetime(movements_df[\"fechaOperada\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "historical_prices.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "movements_df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get discint types of movements\n",
    "distinct_types = movements_df[\"tipo\"].unique()\n",
    "distinct_types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# generate calendar_df\n",
    "\n",
    "# convert to datetime\n",
    "movements_df[\"fecha_operada\"] = pd.to_datetime(movements_df[\"fecha_operada\"])\n",
    "\n",
    "start_date = movements_df[\"fecha_operada\"].min()\n",
    "end_date = pd.to_datetime(datetime.datetime.now().date())\n",
    "calendar_df = pd.DataFrame(\n",
    "    pd.date_range(start=start_date, end=end_date, freq=\"D\"), columns=[\"fecha\"]\n",
    ")\n",
    "\n",
    "\n",
    "# now generate this calendar for each unique asset in the movements_df using the min and max for each asset. If max is not available then propage until current date.\n",
    "\n",
    "# get unique assets\n",
    "assets = movements_df[\"simbolo\"].unique()\n",
    "assets_df = pd.DataFrame(assets, columns=[\"simbolo\"])\n",
    "\n",
    "# perform a cross join to generate calendar for each asset\n",
    "\n",
    "df_combo = pd.merge(calendar_df, assets_df, how=\"cross\")\n",
    "\n",
    "\n",
    "# filter by min_date for each asset\n",
    "min_date = (\n",
    "    movements_df.groupby(\"simbolo\")[\"fecha_operada\"]\n",
    "    .min()\n",
    "    .reset_index()\n",
    "    .rename(columns={\"fecha_operada\": \"min_date\"})\n",
    ")\n",
    "\n",
    "df_combo = pd.merge(df_combo, min_date, on=\"simbolo\", how=\"left\")\n",
    "\n",
    "df_combo = df_combo[df_combo[\"fecha\"] >= df_combo[\"min_date\"]].reset_index(drop=True)\n",
    "\n",
    "df_combo.rename(columns={\"fecha\": \"fecha_operada\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# read cedear ratio correction data\n",
    "\n",
    "cedear_ratio_df = pd.read_csv(\n",
    "    \"data/portfolio/ajustes_cedears.csv\",\n",
    "    parse_dates=[\"fecha_operada\"],\n",
    "    date_format=\"%d/%m/%Y\",\n",
    "    usecols=[\"fecha_operada\", \"simbolo\", \"cantidad_correccion\", \"in_out\", \"market\"],\n",
    ")\n",
    "cedear_ratio_df\n",
    "\n",
    "# if out change sign\n",
    "cedear_ratio_df[\"cantidad_correccion\"] = np.where(\n",
    "    cedear_ratio_df[\"in_out\"] == \"OUT\",\n",
    "    -cedear_ratio_df[\"cantidad_correccion\"],\n",
    "    cedear_ratio_df[\"cantidad_correccion\"],\n",
    ")\n",
    "\n",
    "cedear_ratio_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select relevant columns\n",
    "movements_df = movements_df[\n",
    "    [\n",
    "        \"tipo\",\n",
    "        \"estado\",\n",
    "        \"mercado\",\n",
    "        \"simbolo\",\n",
    "        \"cantidadOperada\",\n",
    "        \"precioOperado\",\n",
    "        \"montoOperado\",\n",
    "        \"plazo\",\n",
    "        \"fecha_orden\",\n",
    "        \"fecha_operada\",\n",
    "    ]\n",
    "].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# correct movements. If cedear ratio correction is available then add it to the movements_df\n",
    "movements_df = pd.concat([movements_df, cedear_ratio_df], axis=0)\n",
    "\n",
    "\n",
    "\n",
    "movements_df[\"cantidad_correccion\"] = movements_df[\"cantidad_correccion\"].fillna(0)\n",
    "\n",
    "\n",
    "\n",
    "movements_df[\"cantidadOperada\"] = movements_df[\"cantidadOperada\"].fillna(0)\n",
    "\n",
    "\n",
    "\n",
    "movements_df[\"cantidadOperada\"] = (\n",
    "    movements_df[\"cantidadOperada\"] + movements_df[\"cantidad_correccion\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_combo[\"fecha\"] = pd.to_datetime(df_combo[\"fecha_operada\"])\n",
    "movements_df[\"fecha_operada\"] = pd.to_datetime(movements_df[\"fecha_operada\"])\n",
    "\n",
    "# tipo == Venta then add - quantity\n",
    "movements_df[\"cantidadOperada\"] = np.where(\n",
    "    movements_df[\"tipo\"] == \"Venta\",\n",
    "    -1 * movements_df[\"cantidadOperada\"],\n",
    "    movements_df[\"cantidadOperada\"],\n",
    ")\n",
    "\n",
    "\n",
    "# Sort both dataframes by symbol and date\n",
    "movements_df = movements_df.sort_values([\"simbolo\", \"fecha_operada\"]).reset_index(\n",
    "    drop=True\n",
    ")\n",
    "\n",
    "df_combo = df_combo.sort_values([\"simbolo\", \"fecha_operada\"]).reset_index(drop=True)\n",
    "# Calculate cumulative quantity (cum_qty)\n",
    "movements_df[\"cum_qty\"] = movements_df.groupby(\"simbolo\")[\"cantidadOperada\"].cumsum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "movements_df[movements_df[\"simbolo\"] == \"VIST\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Merge both dataframes using left join\n",
    "df_merged = pd.merge(\n",
    "    df_combo,\n",
    "    movements_df,\n",
    "    how=\"left\",\n",
    "    left_on=[\"simbolo\", \"fecha_operada\"],\n",
    "    right_on=[\"simbolo\", \"fecha_operada\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# perform forward fill \"cantidadOperada\" of for each asset\n",
    "df_merged[\"total_qty\"] = df_merged.groupby(\"simbolo\")[\"cum_qty\"].ffill()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged[df_merged[\"simbolo\"] == \"VIST\"].reset_index().to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# select relevant columns\n",
    "df_merged = df_merged[\n",
    "    [\n",
    "        \"fecha_operada\",\n",
    "        \"cantidadOperada\",\n",
    "        \"precioOperado\",\n",
    "        \"montoOperado\",\n",
    "        \"simbolo\",\n",
    "        \"min_date\",\n",
    "        \"market\",\n",
    "        \"total_qty\",\n",
    "    ]\n",
    "].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get global min date of transactions\n",
    "\n",
    "min_date = df_merged.groupby(\"simbolo\")[\"fecha_operada\"].min().min()\n",
    "# convert to datetime.datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from finargy.portfolio.analysis.mep import get_mep_dollar_history\n",
    "\n",
    "dolar_mep = dolar_mep = get_mep_dollar_history(\n",
    "    client,\n",
    "    symbol=\"AL30\",\n",
    "    dollar_symbol=\"AL30D\",\n",
    "    date_start=min_date,\n",
    "    date_end=datetime.datetime.now(),\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dolar_mep.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# generate plot of mep dollar\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "plt.plot(dolar_mep[\"fechaHora_date\"], dolar_mep[\"dollar_mep\"])\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_prices = historical_prices.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged[df_merged[\"simbolo\"] == \"VIST\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join prices\n",
    "df_merged_symbols = pd.merge(\n",
    "    df_merged,\n",
    "    df_prices,\n",
    "    how=\"inner\",\n",
    "    left_on=[\"fecha_operada\", \"simbolo\"],\n",
    "    right_on=[\"fechaHora_date\", \"symbol\"],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# calculate total value\n",
    "df_merged_symbols[\"total_value\"] = (\n",
    "    df_merged_symbols[\"total_qty\"] * df_merged_symbols[\"ultimoPrecio\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# count total days since 2021-01-01\n",
    "\n",
    "import datetime\n",
    "\n",
    "start_date = datetime.datetime(2021, 1, 1)\n",
    "end_date = datetime.datetime.now()\n",
    "days = (end_date - start_date).days\n",
    "\n",
    "# calculate total value in dollars\n",
    "days"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# remove AL30D, GD30D, GD30, AL30 from the symbols\n",
    "remove_symbols = [\"AL30D\", \"GD30D\", \"GD30\", \"AL30\"]\n",
    "df_merged_symbols = df_merged_symbols[\n",
    "    ~df_merged_symbols[\"simbolo\"].isin(remove_symbols)\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df_merged_symbols.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dolar_mep = dolar_mep[[\"fechaHora_date\", \"dollar_mep\"]].copy()\n",
    "# convert to date\n",
    "dolar_mep[\"fechaHora_date\"] = pd.to_datetime(dolar_mep[\"fechaHora_date\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "dolar_mep.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# join dolar mep\n",
    "df_merged_symbols = pd.merge(\n",
    "    df_merged_symbols,\n",
    "    dolar_mep,\n",
    "    how=\"left\",\n",
    "    on=\"fechaHora_date\",\n",
    ")\n",
    "\n",
    "df_merged_symbols[\"total_value_usd\"] = (\n",
    "    df_merged_symbols[\"total_value\"] / df_merged_symbols[\"dollar_mep\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# daily total_qty_variation\n",
    "df_merged_symbols[\"total_qty_variation\"] = df_merged_symbols[\"total_qty\"].diff()\n",
    "\n",
    "# fill first value with initial qty for each symbol"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# plot value of each symbol\n",
    "df_merged_symbols[\"total_value_usd\"] = df_merged_symbols[\"total_value_usd\"].fillna(0)\n",
    "\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# plot total value in dollars\n",
    "plt.figure(figsize=(20, 10))\n",
    "sns.lineplot(\n",
    "    data=df_merged_symbols, x=\"fechaHora_date\", y=\"total_value_usd\", hue=\"simbolo\"\n",
    ")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# max date by symbol\n",
    "max_date = df_merged_symbols.groupby(\"simbolo\")[\"fechaHora_date\"].max()\n",
    "max_date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# get latest value for each symbol\n",
    "latest_values = df_merged_symbols[\n",
    "    df_merged_symbols[\"fechaHora_date\"].isin(max_date)\n",
    "].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "latest_values.copy().to_clipboard()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# Set the default style\n",
    "plt.style.use(\"default\")\n",
    "\n",
    "# Create a figure and a set of subplots with secondary y-axis\n",
    "fig, ax1 = plt.subplots()\n",
    "\n",
    "# Plot the first dataset on the primary y-axis\n",
    "ax1.plot(\n",
    "    df_merged_VISTA[\"fechaHora_date\"],\n",
    "    df_merged_VISTA[\"total_value_usd\"],\n",
    "    label=\"Total Value USD\",\n",
    ")\n",
    "\n",
    "ax1.set_xlabel(\"Date\")\n",
    "ax1.set_ylabel(\"Total Value (millions)\")\n",
    "ax1.tick_params(axis=\"x\", rotation=45)\n",
    "\n",
    "# Create a secondary y-axis\n",
    "ax2 = ax1.twinx()\n",
    "\n",
    "# Plot the second dataset on the secondary y-axis with increased bar width\n",
    "ax2.bar(\n",
    "    df_merged_VISTA[\"fechaHora_date\"],\n",
    "    df_merged_VISTA[\"cantidadOperada\"],\n",
    "    color=\"red\",\n",
    "    width=0.8,\n",
    "    label=\"Total Qty Variation\",\n",
    ")\n",
    "ax2.set_ylabel(\"Total Qty Variation\")\n",
    "\n",
    "# Add legends\n",
    "fig.legend(loc=\"upper left\", bbox_to_anchor=(0.1, 0.9))\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "\n",
    "# Set the default style\n",
    "plt.style.use(\"default\")\n",
    "\n",
    "# Create a figure and a set of subplots with secondary y-axis\n",
    "fig, ax1 = plt.subplots()\n",
    "\n",
    "# Plot the first dataset on the primary y-axis\n",
    "ax1.plot(\n",
    "    df_merged_VISTA[\"fechaHora_date\"],\n",
    "    df_merged_VISTA[\"total_value\"],\n",
    "    label=\"Total Value USD\",\n",
    ")\n",
    "\n",
    "ax1.set_xlabel(\"Date\")\n",
    "ax1.set_ylabel(\"Total Value (millions)\")\n",
    "ax1.tick_params(axis=\"x\", rotation=45)\n",
    "\n",
    "# Create a secondary y-axis\n",
    "ax2 = ax1.twinx()\n",
    "\n",
    "# Plot the second dataset on the secondary y-axis with increased bar width\n",
    "ax2.bar(\n",
    "    df_merged_VISTA[\"fechaHora_date\"],\n",
    "    df_merged_VISTA[\"cantidadOperada\"],\n",
    "    color=\"red\",\n",
    "    width=0.8,\n",
    "    label=\"Total Qty Variation\",\n",
    ")\n",
    "ax2.set_ylabel(\"Total Qty Variation\")\n",
    "\n",
    "# Add legends\n",
    "fig.legend(loc=\"upper left\", bbox_to_anchor=(0.1, 0.9))\n",
    "\n",
    "# Show the plot\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".venv",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}