Get cash position trend

​​
​​
Tags: #qonto #bank #statement #naas_drivers #plotly #linechart
Author: Florent Ravenel​
Input
Import library
1
from naas_drivers import qonto
2
from datetime import datetime
3
import pandas as pd
4
import plotly.graph_objects as go
Copied!
Get your Qonto credentials
​How to get your credentials ?​
1
QONTO_USER_ID = 'YOUR_USER_ID'
2
QONTO_SECRET_KEY = 'YOUR_SECRET_KEY'
Copied!
Parameters
1
# Date to start extraction, format: "AAAA-MM-JJ", example: "2021-01-01"
2
date_from = None
3
# Date to end extraction, format: "AAAA-MM-JJ", example: "2021-01-01", default = now
4
date_to = None
Copied!
Model
Get statement aggregated by date
1
df_statement = qonto.connect(QONTO_USER_ID, QONTO_SECRET_KEY).statements.get(
2
    to_group=["IBAN", "DATE"],
3
    date_from=date_from,
4
    date_to=date_to
5
)
6
df_statement
Copied!
Output
Plotting linechart to follow cash position
1
def get_trend(df_statement,
2
              date_col_name,
3
              value_col_name):
4
    
5
    # Init dataframe
6
    df = df_statement.copy()
7
    
8
    # Format date
9
    df[date_col_name] = pd.to_datetime(df[date_col_name]).dt.strftime("%Y-%m-%d")
10
    
11
    # Fill empty date
12
    d = datetime.now().date()
13
    d2 = df.loc[df.index[0], date_col_name]
14
    idx = pd.date_range(d2, d, freq = "D")
15
    
16
    df.set_index(date_col_name, drop=True, inplace=True)
17
    df.index = pd.DatetimeIndex(df.index)
18
    df = df.reindex(idx, fill_value=0)
19
    df[date_col_name] = pd.DatetimeIndex(df.index)
20
    df = df.reset_index(drop=True)
21
    for _, row in df.iterrows():
22
        if _ > 0:
23
            iban = df.loc[df.index[_-1], "IBAN"]
24
            n_1 = df.loc[df.index[_-1], value_col_name]
25
            n = df.loc[df.index[_], value_col_name]
26
            if n == 0:
27
                df.loc[_, value_col_name] = n_1
28
                df.loc[_, "IBAN"] = iban
29
    return df
30
​
31
df_trend = get_trend(df_statement, "DATE", "POSITION")
32
df_trend.tail(10)
Copied!
1
def create_linechart(df, date, value, var):    
2
    # Get last value
3
    df["VALUE_D"] = df[value].map("{:,.2f} €".format).str.replace(",", " ")
4
    df["VAR_D"] = df[var].map("{:,.2f} €".format).str.replace(",", " ")
5
    df.loc[df[var].astype(float) > 0, "VAR_D"] = "+" + df["VAR_D"]
6
    df["TEXT"] = ("<b>Cash position as of " + df["DATE"].astype(str) + " : </b>" + 
7
                  df["VALUE_D"] + "<br>" + 
8
                  df["VAR_D"] + " vs yesterday")
9
    
10
    last_value = df.loc[df.index[-1], "VALUE_D"]
11
    last_var = df.loc[df.index[-1], "VAR_D"]
12
    
13
    # Init
14
    fig = go.Figure()
15
    
16
    # Create fig
17
    fig.add_trace(
18
        go.Scatter(
19
            x=df[date],
20
            y=df[value],
21
            mode="lines",
22
            hoverinfo="text",
23
            text=df["TEXT"],
24
            line=dict(color="#6b5aed"),
25
        )
26
    )
27
    fig.update_traces(marker_color='black')
28
    fig.update_layout(
29
        title=f"💵<b> Qonto - Cash position trend</b><br><span style='font-size: 13px;'>Last position : {last_value} ({last_var} vs yesterday)</span>",
30
        title_font=dict(family="Arial", size=18, color="black"),
31
        plot_bgcolor="#ffffff",
32
        width=1200,
33
        height=800,
34
        paper_bgcolor="white",
35
        xaxis_title="Date",
36
        xaxis_title_font=dict(family="Arial", size=11, color="black"),
37
        yaxis_title='Amount',
38
        yaxis_title_font=dict(family="Arial", size=11, color="black"),
39
        margin_pad=10,
40
    )
41
    fig.show()
42
    return fig
43
​
44
fig = create_linechart(df_trend, "DATE", "POSITION", "AMOUNT")
Copied!
1
​
Copied!

Get statement ranking by category

Get positions

Last modified 2mo ago

Copy link

Edit on GitHub

Contents

Input

Model

Output