Indigo_SP_Rewards_Calculations.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "6f388101",
   "metadata": {},
   "source": [
    "# Indigo SP INDY rewards Calx\n",
    "\n",
    "**The below note book is useful to calcuate INDY rewards distributed to depositors of the Stability Pool(SP) of Indigo Protocol. Indigo Protocol currently has 3 iAsset(iBTC, iETH, iUSD) which have their individual stability pools. These SP pools support the liquidations that occur in the protocol and the depositors of these pools are rewarded with ADA as well as INDY rewards. The APR for these rewards is reported on a per epoch basis and is displyed on the official Indigo Protocol dashboard.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "5424ddda",
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "# ! pip install openpyxl \n",
    "import calendar, time;\n",
    "import os\n",
    "import pandas as pd\n",
    "import glob\n",
    "from datetime import date"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1c6d26ec",
   "metadata": {},
   "source": [
    "**The amount of INDY rewards distributed to SP depositors daily is as follows:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ef9ca7ef",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "path: str = os.getcwd() + \"\\\\INDY_Rewards\"\n",
    "    \n",
    "excel_files = [file for file in os.listdir(path) if file.endswith('.xlsx')]\n",
    "df = pd.concat((pd.read_excel(os.path.join(path, file), engine='openpyxl') for file in excel_files), ignore_index=True)\n",
    "reward_df=df.loc[(df['date'] >= '2024-09-22') & (df['date'] <= '2024-09-26')]\n",
    "\n",
    "columns_to_drop = ['addr','Expiration','AvailableAt']\n",
    "reward_df = reward_df.drop(columns=columns_to_drop)\n",
    "reward_df['amount']=reward_df['amount']/1000000\n",
    "reward_df=reward_df.groupby(['epoch','date','purpose'])\n",
    "\n",
    "filtered_df1_month=reward_df.sum()\n",
    "\n",
    "filtered_df1_month = filtered_df1_month.reset_index()\n",
    "print(filtered_df1_month.to_string())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8b08b8a0",
   "metadata": {},
   "source": [
    "**The SP balance of individual SP depositors is as follows:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fa1eca49",
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import time\n",
    "from datetime import datetime\n",
    "from typing import Final\n",
    "import glob\n",
    "\n",
    "REF_DATE: Final[datetime] = datetime(2022, 11, 27)\n",
    "EPOCH: Final[int] = 379\n",
    "\n",
    "def get_difference(date: datetime) -> int:\n",
    "    difference = date - REF_DATE\n",
    "\n",
    "    return difference.days\n",
    "\n",
    "path: str = os.getcwd() + \"\\\\SP_Staking\"\n",
    "\n",
    "if \"SP_Staking_Combined\" not in os.listdir(os.getcwd()):\n",
    "    os.mkdir(f\"{os.getcwd()}\\\\SP_Staking_Combined\")\n",
    "\n",
    "timestamps: list[int] = []\n",
    "days = []\n",
    "\n",
    "for filename in os.listdir(path):\n",
    "    filename = filename.replace(\".\", \"-\")\n",
    "    fname: list[str] = filename.split(\"-\")\n",
    "    timestamps.append(int(fname[-2]))\n",
    "\n",
    "for timestamp in timestamps:\n",
    "    full_date = time.strftime(\"%d %b %Y\", time.gmtime(timestamp))\n",
    "    date = pd.to_datetime(full_date)\n",
    "    diff = get_difference(date)\n",
    "    days.append(diff)\n",
    "\n",
    "for index, file in list(enumerate(os.listdir(path))):\n",
    "    df = pd.read_csv(f\"{path}\\\\{file}\")\n",
    "    df.insert(0, column=\"timestamp\", value=timestamps[index])\n",
    "    df.insert(1, column=\"Date\", value=pd.to_datetime(time.strftime(\"%d %b %Y\", time.gmtime(timestamps[index]))))\n",
    "    df.insert(2, column=\"epoch\", value= 379 + (days[index])//5 )\n",
    "    df.to_csv(f\"{os.getcwd()}\\\\SP_Staking_Combined\\\\{timestamps[index]}.csv\")\n",
    "#     print(file)\n",
    "\n",
    "# Get CSV files list from a folder\n",
    "path = \"SP_Staking_Combined\"\n",
    "csv_files = glob.glob(path + \"/*.csv\")\n",
    "\n",
    "# Read each CSV file into DataFrame\n",
    "# This creates a list of dataframes\n",
    "df_list = (pd.read_csv(file) for file in csv_files)\n",
    "\n",
    "# Concatenate all DataFrames\n",
    "big_df   = pd.concat(df_list, ignore_index=True)\n",
    "columns_to_drop = ['owner','opened_at']\n",
    "\n",
    "# Drop the specified columns\n",
    "big_df = big_df.drop(columns=columns_to_drop)\n",
    "\n",
    "big_df.insert(6, column=\"Actual_iAsset_Staked\", value=big_df['iasset_staked']/1000000)\n",
    "big_df=big_df.drop(\"Unnamed: 0\",axis=1)\n",
    "print(big_df.to_string())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "571e1bf0",
   "metadata": {},
   "source": [
    "**The combined total SP pool deposits for the selected dates is as follows:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a32d030",
   "metadata": {},
   "outputs": [],
   "source": [
    "filtered_df2_month = big_df.loc[(big_df['Date'] >= '2024-09-22')\n",
    "                     & (big_df['Date'] <= '2024-09-26')]\n",
    "\n",
    "filtered_df2_month=filtered_df2_month.groupby(['epoch','Date','asset'])\n",
    "filtered_df2_month=filtered_df2_month.sum()\n",
    "print(filtered_df2_month.to_string())\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "85bc9e59",
   "metadata": {},
   "source": [
    "**The oracle prices of iAssets are obtained from the Coingecko API. They are as follows:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf577e78",
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "from pycoingecko import CoinGeckoAPI\n",
    "cg = CoinGeckoAPI()\n",
    "\n",
    "########INDY\n",
    "data1=cg.get_coin_market_chart_by_id(id='indigo-dao-governance-token', vs_currency='usd', days=365)\n",
    "# pd.read_json(data)\n",
    "data1.pop('market_caps')\n",
    "data1.pop('total_volumes')\n",
    "dates1=[]\n",
    "price1=[]\n",
    "for i,j in data1[\"prices\"]:\n",
    "    new1=str(pd.to_datetime(time.strftime(\"%d %b %Y\", time.gmtime(i/1000))))\n",
    "    block=new1.split(\" \")[0]\n",
    "    if pd.to_datetime(block)==date.today():\n",
    "        break\n",
    "    if block in dates1:\n",
    "        break\n",
    "    else:\n",
    "        dates1.append(block)\n",
    "    price1.append(j)\n",
    "\n",
    "\n",
    "dictionary1= {'Date':dates1,'INDY Price':price1}\n",
    "\n",
    "########BTC\n",
    "data2=cg.get_coin_market_chart_by_id(id='bitcoin', vs_currency='usd', days=365)\n",
    "data2.pop('market_caps')\n",
    "data2.pop('total_volumes')\n",
    "dates2=[]\n",
    "price2=[]\n",
    "for i,j in data2[\"prices\"]:\n",
    "    new2=str(pd.to_datetime(time.strftime(\"%d %b %Y\", time.gmtime(i/1000))))\n",
    "    dates2.append(new2.split(\" \")[0])\n",
    "    price2.append(j)\n",
    "\n",
    "dictionary2= {'Date':dates2,'BTC Price':price2}\n",
    "# dictionary2\n",
    "\n",
    "########ETH\n",
    "data3=cg.get_coin_market_chart_by_id(id='ethereum', vs_currency='usd', days=365)\n",
    "data3.pop('market_caps')\n",
    "data3.pop('total_volumes')\n",
    "dates3=[]\n",
    "price3=[]\n",
    "for i,j in data3[\"prices\"]:\n",
    "    new3=str(pd.to_datetime(time.strftime(\"%d %b %Y\", time.gmtime(i/1000))))\n",
    "    dates3.append(new3.split(\" \")[0])\n",
    "    price3.append(j)\n",
    "dictionary3= {'Date':dates3,'ETH Price':price3}\n",
    "# dictionary3\n",
    "\n",
    "########iUSD\n",
    "data4=cg.get_coin_market_chart_by_id(id='tether', vs_currency='usd', days=365)\n",
    "# pd.read_json(data)\n",
    "data4.pop('market_caps')\n",
    "data4.pop('total_volumes')\n",
    "dates4=[]\n",
    "price4=[]\n",
    "for i,j in data4[\"prices\"]:\n",
    "    new4=str(pd.to_datetime(time.strftime(\"%d %b %Y\", time.gmtime(i/1000))))\n",
    "    date=new4.split(\" \")[0]\n",
    "    if date in dates4:\n",
    "        continue\n",
    "    dates4.append(date)\n",
    "    price4.append(j)\n",
    "dictionary4= {'Date':dates4,'BTC Price':price4}\n",
    "\n",
    "##################logic_BTC\n",
    "btc_price=[]\n",
    "for i in dictionary1[\"Date\"]:\n",
    "#     print(i)\n",
    "    for j in set(dates2):\n",
    "        if i==j:\n",
    "            btc_price.append(price2[dates2.index(j)])\n",
    "# len(btc_price)\n",
    "# ##################logic_ETH\n",
    "eth_price=[]\n",
    "for i in dictionary1[\"Date\"]:\n",
    "    for j in set(dates3):\n",
    "        if i==j:\n",
    "            eth_price.append(price3[dates3.index(j)])\n",
    "# eth_price\n",
    "# ##################logic_USDT\n",
    "usd_price=[]\n",
    "for i in dictionary1[\"Date\"]:\n",
    "    for j in set(dates4):\n",
    "        if i==j:\n",
    "            usd_price.append(price4[dates4.index(j)])\n",
    "# len(usd_price)\n",
    "\n",
    "\n",
    "dictionary_combo= {'Date':dates1,'INDY_Price':price1, 'iBTC_Price':btc_price, 'iETH_Price':eth_price, 'iUSD_Price':usd_price}\n",
    "df1 = pd.DataFrame(dictionary_combo)\n",
    "print(df1.to_string())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a36ad1de",
   "metadata": {},
   "source": [
    "**The APR for iBTC is calculated below:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2c71150e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# iBTC\n",
    "arr1=filtered_df1_month.date.unique()\n",
    "ent2=[]\n",
    "for ent in arr1:\n",
    "    ent=ent.strftime('%Y-%m-%d')\n",
    "    ent=ent.split(\" \")[0]\n",
    "    ent2.append(ent)\n",
    "#     print(ent)\n",
    "print(\"Dates: \"+ str(ent2))\n",
    "BTC=[]\n",
    "INDY_price=[]\n",
    "asset_price=[]\n",
    "APR=[]\n",
    "\n",
    "for i in ent2:\n",
    "    date=i\n",
    "    # print(i)\n",
    "\n",
    "    gk1=filtered_df2_month.groupby(['Date','asset'])\n",
    "    place1=gk1.get_group((date,'iBTC')).sum()\n",
    "    s11=place1.Actual_iAsset_Staked \n",
    "    df2= df1[df1['Date'] == date]\n",
    "    asset=df2.iBTC_Price.tolist()[0]\n",
    "    INDY=df2.INDY_Price.tolist()[0]\n",
    "    asset_price.append(asset)\n",
    "    INDY_price.append(INDY)\n",
    "    BTC.append(s11)\n",
    "    \n",
    "\n",
    "filtered_df1_month_r=filtered_df1_month[filtered_df1_month[\"purpose\"] == \"SP reward for iBTC\"]\n",
    "gk=filtered_df1_month_r.drop(columns=[\"purpose\"])\n",
    "gk1=gk.groupby([\"date\"])[\"amount\"].apply(list)\n",
    "gk1\n",
    "actual_amount = [];\n",
    "for i in gk1:\n",
    "    actual_amount.append(sum(i))\n",
    "\n",
    "print(\"asset_staked:\"+ str(BTC))\n",
    "print(\"asset_price:\"+str(asset_price))\n",
    "print(\"INDY_price:\"+str(INDY_price))\n",
    "print(\"INDY_reward:\"+str(actual_amount))\n",
    "\n",
    "numerator=[x * y for x, y in zip(INDY_price, actual_amount)]\n",
    "print(numerator)\n",
    "denominator=[x * y for x, y in zip(asset_price, BTC)]\n",
    "print(denominator)\n",
    "\n",
    "result = [(x / y)*100*365 if y != 0 else float('inf') for x, y in zip(numerator, denominator)]\n",
    "print(result)\n",
    "print(\"iBTC Mean APR:\"+str(sum(result)/len(result)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "492fc841",
   "metadata": {},
   "source": [
    "**The APR for iETH is calculated below:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "744a106b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# ##for iETH  SP APY% for a month\n",
    "arr1=filtered_df1_month.date.unique()\n",
    "ent2=[]\n",
    "for ent in arr1:\n",
    "    ent=ent.strftime('%Y-%m-%d')\n",
    "    ent=ent.split(\" \")[0]\n",
    "    ent2.append(ent)\n",
    "#     print(ent)\n",
    "print(\"Dates: \"+ str(ent2))\n",
    "ETH=[]\n",
    "INDY_price=[]\n",
    "asset_price=[]\n",
    "APR=[]\n",
    "\n",
    "for i in ent2:\n",
    "    date=i\n",
    "    \n",
    "\n",
    "    gk1=filtered_df2_month.groupby(['Date','asset'])\n",
    "    place1=gk1.get_group((date,'iETH')).sum()\n",
    "    s11=place1.Actual_iAsset_Staked \n",
    "    df2= df1[df1['Date'] == date]\n",
    "    asset=df2.iETH_Price.tolist()[0]\n",
    "    INDY=df2.INDY_Price.tolist()[0]\n",
    "    asset_price.append(asset)\n",
    "    INDY_price.append(INDY)\n",
    "    ETH.append(s11)\n",
    "    \n",
    "\n",
    "filtered_df1_month_r=filtered_df1_month[filtered_df1_month[\"purpose\"] == \"SP reward for iETH\"]\n",
    "gk=filtered_df1_month_r.drop(columns=[\"purpose\"])\n",
    "gk1=gk.groupby([\"date\"])[\"amount\"].apply(list)\n",
    "gk1\n",
    "actual_amount = [];\n",
    "\n",
    "for i in gk1:\n",
    "    actual_amount.append(sum(i))\n",
    "\n",
    "print(\"asset_staked:\"+ str(ETH))\n",
    "print(\"asset_price:\"+str(asset_price))\n",
    "print(\"INDY_price:\"+str(INDY_price))\n",
    "print(\"INDY_reward:\"+str(actual_amount))\n",
    "\n",
    "numerator=[x * y for x, y in zip(INDY_price, actual_amount)]\n",
    "print(numerator)\n",
    "denominator=[x * y for x, y in zip(asset_price, ETH)]\n",
    "print(denominator)\n",
    "\n",
    "result = [(x / y)*100*365 if y != 0 else float('inf') for x, y in zip(numerator, denominator)]\n",
    "print(result)\n",
    "print(\"iETH Mean APR:\"+str(sum(result)/len(result)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e1bfe644",
   "metadata": {},
   "source": [
    "**The APR for iUSD is calculated below:**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a9b0922",
   "metadata": {},
   "outputs": [],
   "source": [
    "# for iUSD  SP APY% for a month\n",
    "arr1=filtered_df1_month.date.unique()\n",
    "ent2=[]\n",
    "for ent in arr1:\n",
    "    ent=ent.strftime('%Y-%m-%d')\n",
    "    ent=ent.split(\" \")[0]\n",
    "    ent2.append(ent)\n",
    "#     print(ent)\n",
    "print(\"Dates: \"+ str(ent2))\n",
    "USD=[]\n",
    "INDY_price=[]\n",
    "asset_price=[]\n",
    "APR=[]\n",
    "\n",
    "for i in ent2:\n",
    "    date=i\n",
    "    \n",
    "\n",
    "    gk1=filtered_df2_month.groupby(['Date','asset'])\n",
    "    place1=gk1.get_group((date,'iUSD')).sum()#########1#####change1\n",
    "    s11=place1.Actual_iAsset_Staked \n",
    "    df2= df1[df1['Date'] == date]\n",
    "    asset=df2.iUSD_Price.tolist()[0]#####change2\n",
    "    INDY=df2.INDY_Price.tolist()[0]\n",
    "    asset_price.append(asset)\n",
    "    INDY_price.append(INDY)\n",
    "    USD.append(s11)\n",
    "    \n",
    "\n",
    "filtered_df1_month_r=filtered_df1_month[filtered_df1_month[\"purpose\"] == \"SP reward for iUSD\"]\n",
    "gk=filtered_df1_month_r.drop(columns=[\"purpose\"])\n",
    "gk1=gk.groupby([\"date\"])[\"amount\"].apply(list)\n",
    "gk1\n",
    "actual_amount = [];\n",
    "for i in gk1:\n",
    "    actual_amount.append(sum(i))\n",
    "\n",
    "print(\"asset_staked:\"+ str(USD))\n",
    "print(\"asset_price:\"+str(asset_price))\n",
    "print(\"INDY_price:\"+str(INDY_price))\n",
    "print(\"INDY_reward:\"+str(actual_amount))\n",
    "\n",
    "numerator=[x * y for x, y in zip(INDY_price, actual_amount)]\n",
    "print(numerator)\n",
    "denominator=[x * y for x, y in zip(asset_price, USD)]\n",
    "print(denominator)\n",
    "\n",
    "result = [(x / y)*100*365 if y != 0 else float('inf') for x, y in zip(numerator, denominator)]\n",
    "print(result)\n",
    "print(\"iUSD Mean APR:\"+str(sum(result)/len(result)))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "32b1d6b6",
   "metadata": {},
   "source": [
    "**The APRs are as follows:**\n",
    "\n",
    "iBTC: 5.25%\n",
    "\n",
    "iETH: 10.52%\n",
    "\n",
    "iUSD: 12.98%"
   ]
  }
 ],
 "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.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}