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

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!
Copy link
Edit on GitHub