Qonto/Qonto_Get_cash_position_trend.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "unlikely-singles",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "<img width=\"8%\" alt=\"Qonto.png\" src=\"https://raw.githubusercontent.com/jupyter-naas/awesome-notebooks/master/.github/assets/logos/Qonto.png\" style=\"border-radius: 15%\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "peaceful-usage",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "# Qonto - Get cash position trend\n",
    "<a href=\"https://bit.ly/3JyWIk6\">Give Feedback</a> | <a href=\"https://github.com/jupyter-naas/awesome-notebooks/issues/new?assignees=&labels=bug&template=bug_report.md&title=Qonto+-+Get+cash+position+trend:+Error+short+description\">Bug report</a>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4056e87a-47ab-4486-ab3f-4cd4aa423387",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Tags:** #qonto #bank #statement #naas_drivers #plotly #linechart #finance #analytics #dataframe"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d789ff4f-542b-49a1-9eb6-d552555e1726",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Author:** [Florent Ravenel](https://www.linkedin.com/in/florent-ravenel/)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1442d19f-65d3-4cb7-a28d-b42b6d2e6250",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "**Last update:** 2023-04-12 (Created: 2022-02-16)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "naas-description",
   "metadata": {
    "papermill": {},
    "tags": [
     "description"
    ]
   },
   "source": [
    "**Description:** This notebook provides an overview of cash position trends for Qonto customers."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "input_cell",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Input"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "import_cell",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Import library"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "85814a37-8123-4212-9032-1ad014682ed7",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "from naas_drivers import qonto\n",
    "from datetime import datetime\n",
    "import pandas as pd\n",
    "import plotly.graph_objects as go"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a49acf96-fb45-4766-be46-b56dbfd06f3f",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get your Qonto credentials\n",
    "<a href='https://www.notion.so/naas-official/Qonto-driver-Get-your-credentials-0cc97828b4e7467c8bfbcf704a77e5f4'>How to get your credentials ?</a>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "72ec3d85-f759-44a8-8e45-a27272214efd",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "QONTO_USER_ID = \"YOUR_USER_ID\"\n",
    "QONTO_SECRET_KEY = \"YOUR_SECRET_KEY\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f268b70-d3af-4f5d-879c-666475a171ab",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Parameters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "dbc49850-b292-40d0-95d7-4c77b50129bf",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Date to start extraction, format: \"AAAA-MM-JJ\", example: \"2021-01-01\"\n",
    "date_from = None\n",
    "# Date to end extraction, format: \"AAAA-MM-JJ\", example: \"2021-01-01\", default = now\n",
    "date_to = None"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "model_cell",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Model"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d90ccdb4-5db1-4982-86d9-e01465240f7f",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Get statement aggregated by date"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c1bfb12-bbb8-4149-b213-c56a00e4fead",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "df_statement = qonto.connect(QONTO_USER_ID, QONTO_SECRET_KEY).statements.get(\n",
    "    to_group=[\"IBAN\", \"DATE\"], date_from=date_from, date_to=date_to\n",
    ")\n",
    "df_statement"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "output_cell",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "## Output"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cd521f05-5048-491b-b092-176c10cdbd94",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "source": [
    "### Plotting linechart to follow cash position"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e9bf44d-cf03-4ba5-a8ed-f9314b99b71b",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def get_trend(df_statement, date_col_name, value_col_name):\n",
    "\n",
    "    # Init dataframe\n",
    "    df = df_statement.copy()\n",
    "\n",
    "    # Format date\n",
    "    df[date_col_name] = pd.to_datetime(df[date_col_name]).dt.strftime(\"%Y-%m-%d\")\n",
    "\n",
    "    # Fill empty date\n",
    "    d = datetime.now().date()\n",
    "    d2 = df.loc[df.index[0], date_col_name]\n",
    "    idx = pd.date_range(d2, d, freq=\"D\")\n",
    "\n",
    "    df.set_index(date_col_name, drop=True, inplace=True)\n",
    "    df.index = pd.DatetimeIndex(df.index)\n",
    "    df = df.reindex(idx, fill_value=0)\n",
    "    df[date_col_name] = pd.DatetimeIndex(df.index)\n",
    "    df = df.reset_index(drop=True)\n",
    "    for _, row in df.iterrows():\n",
    "        if _ > 0:\n",
    "            iban = df.loc[df.index[_ - 1], \"IBAN\"]\n",
    "            n_1 = df.loc[df.index[_ - 1], value_col_name]\n",
    "            n = df.loc[df.index[_], value_col_name]\n",
    "            if n == 0:\n",
    "                df.loc[_, value_col_name] = n_1\n",
    "                df.loc[_, \"IBAN\"] = iban\n",
    "    return df\n",
    "\n",
    "\n",
    "df_trend = get_trend(df_statement, \"DATE\", \"POSITION\")\n",
    "df_trend.tail(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de83ccda-3312-45ec-b746-f6c89c798c3c",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": [
    "def create_linechart(df, date, value, var):\n",
    "    # Get last value\n",
    "    df[\"VALUE_D\"] = df[value].map(\"{:,.2f} €\".format).str.replace(\",\", \" \")\n",
    "    df[\"VAR_D\"] = df[var].map(\"{:,.2f} €\".format).str.replace(\",\", \" \")\n",
    "    df.loc[df[var].astype(float) > 0, \"VAR_D\"] = \"+\" + df[\"VAR_D\"]\n",
    "    df[\"TEXT\"] = (\n",
    "        \"<b>Cash position as of \"\n",
    "        + df[\"DATE\"].astype(str)\n",
    "        + \" : </b>\"\n",
    "        + df[\"VALUE_D\"]\n",
    "        + \"<br>\"\n",
    "        + df[\"VAR_D\"]\n",
    "        + \" vs yesterday\"\n",
    "    )\n",
    "\n",
    "    last_value = df.loc[df.index[-1], \"VALUE_D\"]\n",
    "    last_var = df.loc[df.index[-1], \"VAR_D\"]\n",
    "\n",
    "    # Init\n",
    "    fig = go.Figure()\n",
    "\n",
    "    # Create fig\n",
    "    fig.add_trace(\n",
    "        go.Scatter(\n",
    "            x=df[date],\n",
    "            y=df[value],\n",
    "            mode=\"lines\",\n",
    "            hoverinfo=\"text\",\n",
    "            text=df[\"TEXT\"],\n",
    "            line=dict(color=\"#6b5aed\"),\n",
    "        )\n",
    "    )\n",
    "    fig.update_traces(marker_color=\"black\")\n",
    "    fig.update_layout(\n",
    "        title=f\"💵<b> Qonto - Cash position trend</b><br><span style='font-size: 13px;'>Last position : {last_value} ({last_var} vs yesterday)</span>\",\n",
    "        title_font=dict(family=\"Arial\", size=18, color=\"black\"),\n",
    "        plot_bgcolor=\"#ffffff\",\n",
    "        width=1200,\n",
    "        height=800,\n",
    "        paper_bgcolor=\"white\",\n",
    "        xaxis_title=\"Date\",\n",
    "        xaxis_title_font=dict(family=\"Arial\", size=11, color=\"black\"),\n",
    "        yaxis_title=\"Amount\",\n",
    "        yaxis_title_font=dict(family=\"Arial\", size=11, color=\"black\"),\n",
    "        margin_pad=10,\n",
    "    )\n",
    "    fig.show()\n",
    "    return fig\n",
    "\n",
    "\n",
    "fig = create_linechart(df_trend, \"DATE\", \"POSITION\", \"AMOUNT\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a28b810a-89a3-4835-8f98-5bc5dc9a9268",
   "metadata": {
    "papermill": {},
    "tags": []
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.8"
  },
  "naas": {
   "notebook_id": "a7d100e0889cb14f5c824f377c6a44885ee52aa18f9155282c1d08bd2c642f26",
   "notebook_path": "Qonto/Qonto_Get_cash_position_trend.ipynb"
  },
  "papermill": {
   "default_parameters": {},
   "environment_variables": {},
   "parameters": {},
   "version": "2.3.3"
  },
  "widgets": {
   "application/vnd.jupyter.widget-state+json": {
    "state": {},
    "version_major": 2,
    "version_minor": 0
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}