fr_y_9c_unrealized_loss.py

from bokeh.plotting import figure, show

import bokeh.models

import bokeh.palettes
import bokeh.transform

import pandas as pd

import csv

df = pd.read_csv('BHCF20231231\BHCF20231231.txt', sep='^', quoting=csv.QUOTE_NONE)

# df = pd.read_csv('BHCF20231231\BHCF20231231.txt', sep='^', quoting=csv.QUOTE_NONE, encoding='utf-8')

df['label'] = df['RSSD9001'].astype(str) + ' ' + df['RSSD9017']

df = df.rename(columns={ 'BHCK1754':'HTM_AC' }) # Held-to-Maturity   (Amortized Cost)
df = df.rename(columns={ 'BHCK1771':'HTM_FV' }) # Held-to-Maturity   (Fair Value)
df = df.rename(columns={ 'BHCK1772':'AFS_AC' }) # Available-for-Sale (Amortized Cost)
df = df.rename(columns={ 'BHCK1773':'AFS_FV' }) # Available-for-Sale (Fair Value)
df = df.rename(columns={ 'BHCA8274':'T1C' })    # Tier 1 Capital

df['HTM_UL'] = df['HTM_AC'] - df['HTM_FV']      # Held-to-Maturity   (Unrealized Loss)
df['AFS_UL'] = df['AFS_AC'] - df['AFS_FV']      # Available-for-Sale (Unrealized Loss)

df['HTM_AFS_UL'] = df['HTM_UL'] + df['AFS_UL']  # Held-to-Maturity + Available-for-Sale (Unrealized Loss)

df['HTM_AFS_UL_T1C'] = df['HTM_AFS_UL'] / df['T1C'] * 100 # Held-to-Maturity + Available-for-Sale (Unrealized Loss) / Tier 1 Capital

df[df['HTM_AFS_UL_T1C'].notnull()][['label', 'HTM_AFS_UL_T1C']].sort_values(by='HTM_AFS_UL_T1C', ascending=False).head(50)

tmp = df[df['HTM_AFS_UL_T1C'].notnull()][['label', 'HTM_AFS_UL_T1C']].sort_values(by='HTM_AFS_UL_T1C', ascending=False).head(50)

companies = tmp['label'].tolist()

values = tmp['HTM_AFS_UL_T1C'].tolist()

p = figure(y_range=bokeh.models.FactorRange(factors=companies), title='FR-Y-9C : HTM_AFS_UL_T1C', x_axis_label='HTM_AFS_UL_T1C', y_axis_label='RSSD9017', sizing_mode='stretch_both')

p.hbar(y=companies, right=values, height=0.5)

show(p)