ffiec_call_reports_uninsured_deposits_vs_unrealized_losses.py

import os
import glob
import pandas as pd

from bokeh.plotting import figure, show
from bokeh.models import ColumnDataSource, HoverTool
from bokeh.transform import linear_cmap
from bokeh.palettes import Viridis256
from bokeh.io import output_notebook
from bokeh.models import NumeralTickFormatter
from bokeh.models import LabelSet
from bokeh.models import WheelZoomTool, PanTool, ResetTool
from bokeh.models import BoxZoomTool

folder = 'FFIEC CDR Call Bulk All Schedules 12312023'

df_por  = pd.read_csv(glob.glob(os.path.join('data', folder, 'FFIEC CDR Call Bulk POR *.txt'))[0],             sep='\t')
df_rc   = pd.read_csv(glob.glob(os.path.join('data', folder, 'FFIEC CDR Call Schedule RC *.txt'))[0],          sep='\t')
df_rco1 = pd.read_csv(glob.glob(os.path.join('data', folder, 'FFIEC CDR Call Schedule RCO *(1 of 2).txt'))[0], sep='\t')
df_rcri = pd.read_csv(glob.glob(os.path.join('data', folder, 'FFIEC CDR Call Schedule RCRI *.txt'))[0],        sep='\t')
df_rcb1 = pd.read_csv(glob.glob(os.path.join('data', folder, 'FFIEC CDR Call Schedule RCB *(1 of 2).txt'))[0], sep='\t')

df = df_por

df = pd.merge(df, df_rc,   on='IDRSSD', how='inner')
df = pd.merge(df, df_rco1, on='IDRSSD', how='inner')
df = pd.merge(df, df_rcri, on='IDRSSD', how='inner')
df = pd.merge(df, df_rcb1, on='IDRSSD', how='inner')

df = df.rename(columns={'RCON2200': 'deposits'})
df = df.rename(columns={'RCON5597': 'uninsured_deposits'})
df = df.rename(columns={'RCFA8274': 'T1C'})

df['RCFD1754'] = pd.to_numeric(df['RCFD1754'])
df['RCFD1771'] = pd.to_numeric(df['RCFD1771'])
df['RCFD1772'] = pd.to_numeric(df['RCFD1772'])
df['RCFD1773_y'] = pd.to_numeric(df['RCFD1773_y'])
df['T1C'] = pd.to_numeric(df['T1C'])

df['HTM_UL'] = df['RCFD1754'] - df['RCFD1771']   # Held-to-Maturity   unrealized loss
df['AFS_UL'] = df['RCFD1772'] - df['RCFD1773_y'] # Available-for-Sale unrealized loss

df['HTM_AFS_UL_T1C'] = (df['HTM_UL'] + df['AFS_UL']) / df['T1C']

df['deposits']           = pd.to_numeric(df['deposits'])
df['uninsured_deposits'] = pd.to_numeric(df['uninsured_deposits'])

df['uninsured_deposits_pct'] = df['uninsured_deposits'] / df['deposits']

tmp = df[(df['uninsured_deposits_pct'].notnull()) & (df['HTM_AFS_UL_T1C'].notnull())]

source = ColumnDataSource(tmp)

p = figure(title='Uninsured Deposits vs Unrealized Losses', x_axis_label='HTM_AFS_UL_T1C', y_axis_label='Uninsured Deposits %', tools='hover', tooltips='@{Financial Institution Name}', sizing_mode='stretch_both')

p.circle(x='HTM_AFS_UL_T1C', y='uninsured_deposits_pct', source=source)

labels = LabelSet(x='HTM_AFS_UL_T1C', y='uninsured_deposits_pct', text='Financial Institution Name', level='glyph', x_offset=5, y_offset=5, source=source)

labels.text_font_size = '8pt'

p.add_layout(labels)

p.xaxis.formatter = NumeralTickFormatter(format='0%')
p.yaxis.formatter = NumeralTickFormatter(format='0%')

p.add_tools(WheelZoomTool())
p.add_tools(PanTool())
p.add_tools(ResetTool())
p.add_tools(BoxZoomTool())

show(p)

# tmp = df[(df['uninsured_deposits_pct'].notnull()) & (df['HTM_AFS_UL_T1C'].notnull())]

# tmp.sort_values('uninsured_deposits_pct', ascending=False).head(20)[['Financial Institution Name', 'deposits', 'uninsured_deposits', 'uninsured_deposits_pct', 'HTM_AFS_UL_T1C']]