Skip to content

Instantly share code, notes, and snippets.

@dharmatech

dharmatech/FR-Y-9C.ps1

Created December 21, 2023 20:14
Show Gist options
  • Save dharmatech/5d7b787b82f45756f852a179a05987fa to your computer and use it in GitHub Desktop.
Save dharmatech/5d7b787b82f45756f852a179a05987fa to your computer and use it in GitHub Desktop.
Download ZIP
Raw
FR-Y-9C.ps1
# https://www.ffiec.gov/npw/FinancialReport/FinancialDataDownload
# $result_bhcf = Import-Csv .\BHCF.csv
# $result_rssd = Import-Csv .\RSSD.csv
Write-Host 'Importing CSV...' -ForegroundColor Yellow -NoNewline
# $result = Import-Csv -Delimiter '^' -Path BHCF20221231.txt
# $result = Import-Csv -Delimiter '^' -Path .\BHCF20230331\BHCF20230331.txt
$result = Import-Csv -Delimiter '^' -Path .\BHCF20230630\BHCF20230630.txt
Write-Host 'Done' -ForegroundColor Yellow
# ----------------------------------------------------------------------
function percent ($val)
{
[math]::Round($val * 100, 2)
}
# $table_alt = foreach ($row in $result)
# {
# [PSCustomObject]@{
# LEGAL_NAME = $row.RSSD9017
# TOTAL_ASSETS = $row.BHCK2170
# RSSD_ID = $row.RSSD9001
# }
# }
$table = foreach ($row in $result)
{
$TOTAL_ASSETS = [decimal] $row.BHCK2170
$LSBRE = [decimal] $row.BHCK1410 # Loans secured by real estate
if ($TOTAL_ASSETS -eq '') { continue; }
$LEVERAGE = [math]::Round([decimal] $row.BHCA7204, 2) # Leverage ratio
$T1C = [decimal] $row.BHCA8274 # Tier 1 Capital
$LSBRE_TA = [math]::Round($LSBRE / $TOTAL_ASSETS * 100, 2)
$OONFNRP = [decimal] $row.BHCKF160 # Loans secured by owner-occupied nonfarm nonresidential properties
$OTNFNRP = [decimal] $row.BHCKF161 # Loans secured by other nonfarm nonresidential properties
$NFNRP = $OONFNRP + $OTNFNRP # Loans secured by nonfarm nonresidential properties
$NFNRP_TA = [math]::Round($NFNRP / $TOTAL_ASSETS * 100, 2)
# ------------------------------------------------------------
$CAIL = [decimal] $row.BHDM1766 # Commercial and industrial loans
$CAILUS = [decimal] $row.BHCK1763 # Commercial and industrial loans :: to U.S. addresses (domicile)
$CAILNUS = [decimal] $row.BHCK1764 # Commercial and industrial loans :: to non-U.S. addresses (domicile)
$CAILBOTH = [decimal] $row.BHCKKX56 # Commercial and industrial loans :: to U.S. addresses (domicile) and non-U.S. addresses (domicile)
$CAIL_TA = [math]::Round($CAIL / $TOTAL_ASSETS * 100, 2)
# ------------------------------------------------------------
$HTM_AC = $row.BHCK1754 # Held-to-Maturity Amortized Cost
$HTM_FV = $row.BHCK1771 # Held-to-Maturity Fair Value
$AFS_AC = $row.BHCK1772 # Available-for-Sale Amortized Cost
$AFS_FV = $row.BHCK1773 # Available-for-Sale Fair Value
$HTM_UL = $HTM_AC - $HTM_FV # Held-to-Maturity unrealized loss
$AFS_UL = $AFS_AC - $AFS_FV # Available-for-Sale unrealized loss
$HTM_AFS_UL = $HTM_UL + $AFS_UL # (HTM + AFS) unrealized loss
# $HTM_AFS_UL_T1C = [math]::Round($HTM_AFS_UL / $T1C * 100, 2)
$HTM_AFS_UL_T1C = if ($T1C -eq 0) { '' } else { [math]::Round($HTM_AFS_UL / $T1C * 100, 2)}
# ------------------------------------------------------------
$TRAS = [decimal] $row.BHCT3545 # Total trading assets
$TRLI = [decimal] $row.BHCK3548 # Total trading liabilities
$SHORT = ([decimal] $row.BHCKG209 + [decimal] $row.BHCKG210 + [decimal] $row.BHCKG211) # Liability for short positions
# $TRLI_T1C = percent ($TRLI / $T1C)
# ------------------------------------------------------------
[PSCustomObject]@{
RSSD_ID = $row.RSSD9001
LEGAL_NAME = $row.RSSD9017
TOTAL_ASSETS = $TOTAL_ASSETS
LEVERAGE = $LEVERAGE
T1C = $T1C
LSBRE = $LSBRE
LSBRE_TA = $LSBRE_TA
NFNRP = $NFNRP
NFNRP_TA = $NFNRP_TA
HTM_UL = $HTM_UL
AFS_UL = $AFS_UL
HTM_AFS_UL_T1C = $HTM_AFS_UL_T1C
CAIL = $CAIL
CAILUS = $CAILUS
CAILNUS = $CAILNUS
CAILBOTH = $CAILBOTH
CAIL_TA = $CAIL_TA
TRAS = $TRAS
TRLI = $TRLI
SHORT = $SHORT
TRLI_T1C = $TRLI_T1C
}
}
function create-format ($name)
{
@{
Label = $name
Expression = [System.Management.Automation.ScriptBlock]::Create('$_.{0}.ToString("N0")' -f $name)
Align = 'right'
}
}
$fields = @(
'RSSD_ID'
'LEGAL_NAME'
create-format 'TOTAL_ASSETS'
'LEVERAGE'
create-format 'T1C'
create-format 'LSBRE'
'LSBRE_TA'
create-format 'NFNRP'
'NFNRP_TA'
# create-format 'HTM_UL'
# create-format 'AFS_UL'
'HTM_AFS_UL_T1C'
create-format 'CAIL'
# create-format 'CAILUS'
# create-format 'CAILNUS'
# create-format 'CAILBOTH'
'CAIL_TA'
create-format 'TRAS'
create-format 'TRLI'
# create-format 'SHORT'
# 'TRLI_T1C'
)
function legend ()
{
Write-Host -ForegroundColor Yellow 'LSBRE Loans secured by real estate'
Write-Host -ForegroundColor Yellow 'LSBRE_TA (Loans secured by real estate) / total assets'
Write-Host -ForegroundColor Yellow 'NFNRP Loans secured by nonfarm nonresidential properties'
Write-Host -ForegroundColor Yellow 'NFNRP_TA Loans secured by nonfarm nonresidential properties / total assets'
Write-Host -ForegroundColor Yellow 'HTM_UL Held-to-Maturity unrealized loss'
Write-Host -ForegroundColor Yellow 'AFS_UL Available-for-Sale unrealized loss'
Write-Host -ForegroundColor Yellow 'HTM_AFS_UL_T1C (HTM UL + AFS UL) / T1C'
Write-Host -ForegroundColor Yellow 'CAIL Commercial and industrial loans'
Write-Host -ForegroundColor Yellow 'CAILUS Commercial and industrial loans :: to U.S. addresses (domicile)'
Write-Host -ForegroundColor Yellow 'CAILNUS Commercial and industrial loans :: to non-U.S. addresses (domicile)'
Write-Host -ForegroundColor Yellow 'CAILBOTH Commercial and industrial loans :: to U.S. addresses (domicile) and non-U.S. addresses (domicile)'
Write-Host -ForegroundColor Yellow 'CAIL_TA (Commercial and industrial loans) / total assets'
Write-Host -ForegroundColor Yellow 'TRAS Total trading assets'
Write-Host -ForegroundColor Yellow 'TRLI Total trading liabilities'
Write-Host -ForegroundColor Yellow 'SHORT Liability for short positions'
}
# legend
$table | Sort-Object TOTAL_ASSETS -Descending | ft $fields
$table | Measure-Object | Select-Object Count
# $table | Select-Object -First 30 | ft $fields
# $table | Sort-Object LEVERAGE -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object NFNRP_TA -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object HTM_AFS_UL_T1C -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object TOTAL_ASSETS -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object CAIL -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object CAILUS -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object CAILNUS -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object CAILBOTH -Descending | Select-Object -First 30 | ft $fields
# $table | Sort-Object CAIL_TA -Descending | Select-Object -First 30 | ft $fields
$table | ConvertTo-Csv -NoTypeInformation > .\FRY9C-general.csv
# ----------------------------------------------------------------------
$labels = @{
LSBRE = 'Loans secured by real estate'
LSBRE_TA = '(Loans secured by real estate) / total assets'
NFNRP = 'Loans secured by nonfarm nonresidential properties'
NFNRP_TA = 'Loans secured by nonfarm nonresidential properties / total assets'
HTM_UL = 'Held-to-Maturity unrealized loss'
AFS_UL = 'Available-for-Sale unrealized loss'
HTM_AFS_UL_T1C = '(HTM unrealized losses + AFS unrealized losses) / T1C'
LEVERAGE = 'Leverage Ratio'
CAIL_TA = 'Commercial and industrial loans / total assets'
}
# ----------------------------------------------------------------------
function scatter ($table, $x, $y)
{
$counter = [PSCustomObject]@{ Value = 0 }
$chunk_size = [math]::Ceiling($table.Count / 10)
$chunks = $table | Sort-Object TOTAL_ASSETS -Descending | Group-Object -Property { [math]::Floor($counter.Value++ / $chunk_size) }
$json = @{
chart = @{
type = 'scatter'
data = @{
datasets = $(foreach ($chunk in $chunks)
{
@{
data = $chunk.Group.ForEach(
{
@{
x = $_.$x
y = $_.$y
z = $_.LEGAL_NAME
}
}
)
}
})
}
options = @{
title = @{ display = $true; text = 'FR Y-9C 2023 Q2' }
scales = @{
xAxes = @(
@{
scaleLabel = @{
display = $true
# labelString = '(HTM unrealized losses + AFS unrealized losses) / T1C'
labelString = $labels.$x
}
ticks = @{ max = [math]::Round(($table | Measure-Object -Property $x -Maximum).Maximum * 1.1) }
}
)
yAxes = @(
@{
scaleLabel = @{
display = $true
# labelString = 'loans backed by non-farm non-residential properties / total assets'
labelString = $labels.$y
}
ticks = @{ max = [math]::Round(($table | Measure-Object -Property $y -Maximum).Maximum * 1.1) }
}
)
}
plugins = @{
datalabels = @{
display = $true
align = 'right'
}
datalabelsZAxis = @{ enabled = $true }
}
}
}
} | ConvertTo-Json -Depth 100
$result = Invoke-RestMethod -Method Post -Uri 'https://quickchart.io/chart/create' -Body $json -ContentType 'application/json'
# Start-Process $result.url
$id = ([System.Uri] $result.url).Segments[-1]
Start-Process ('https://quickchart.io/chart-maker/view/{0}' -f $id)
}
# ----------------------------------------------------------------------
exit
# ----------------------------------------------------------------------
$table | ? LEGAL_NAME -Match 'LINC'
# Search by RSSD ID
#
# https://www.ffiec.gov/npw/
# scatter HTM_AFS_UL_T1C NFNRP_TA
scatter $table HTM_AFS_UL_T1C NFNRP_TA
scatter ($table | Where-Object LEGAL_NAME -NE 'IFS 1820 BANCORP, MHC') HTM_AFS_UL_T1C NFNRP_TA
$table | ? LEGAL_NAME -Match 'first command'
$table | ? LEGAL_NAME -Match 'sandy' # $SASR
$table | ? LEGAL_NAME -Match 'industry'
$table | ? LEGAL_NAME -Match 'republic first' | ft * # FRBK
$table | ? LEGAL_NAME -Match 'metropolitan' | ft *
# scatter HTM_AFS_UL_T1C LSBRE_TA
# scatter LEVERAGE LSBRE_TA
# scatter CAIL_TA NFNRP_TA
$table_original = $table
$table_original | ft $fields
$table | Select-Object -First 30 | ft $fields
$table = $table | Where-Object LEGAL_NAME -NE 'IFS 1820 BANCORP, MHC'
# ----------------------------------------------------------------------
# chart.js
# ----------------------------------------------------------------------
$chart_template = @"
<div>
<canvas id="myChart"></canvas>
</div>
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
const ctx = document.getElementById('myChart');
new Chart(ctx, {0});
</script>
"@
function scatter-chartjs ($table, $x, $y)
{
$counter = [PSCustomObject]@{ Value = 0 }
$chunk_size = [math]::Ceiling($table.Count / 10)
$chunks = $table | Sort-Object TOTAL_ASSETS -Descending | Group-Object -Property { [math]::Floor($counter.Value++ / $chunk_size) }
$json = @{
type = 'scatter'
data = @{
datasets = $(foreach ($chunk in $chunks)
{
@{
data = $chunk.Group.ForEach(
{
@{
x = $_.$x
y = $_.$y
z = $_.LEGAL_NAME
}
}
)
}
})
}
options = @{
title = @{ display = $true; text = 'FR Y-9C 2022-12-31' }
scales = @{
xAxes = @(
@{
scaleLabel = @{
display = $true
# labelString = '(HTM unrealized losses + AFS unrealized losses) / T1C'
labelString = $labels.$x
}
ticks = @{ max = [math]::Round(($table | Measure-Object -Property $x -Maximum).Maximum * 1.1) }
}
)
yAxes = @(
@{
scaleLabel = @{
display = $true
# labelString = 'loans backed by non-farm non-residential properties / total assets'
labelString = $labels.$y
}
ticks = @{ max = [math]::Round(($table | Measure-Object -Property $y -Maximum).Maximum * 1.1) }
}
)
}
plugins = @{
datalabels = @{
display = $true
align = 'right'
}
datalabelsZAxis = @{ enabled = $true }
}
}
} | ConvertTo-Json -Depth 100
(Get-Content .\page-template.html -Raw) `
-replace '---MAIN---', ($chart_template -f $json) `
-replace '---SCRIPTS---', '' `
> .\out.html
# $result = Invoke-RestMethod -Method Post -Uri 'https://quickchart.io/chart/create' -Body $json -ContentType 'application/json'
# # Start-Process $result.url
# $id = ([System.Uri] $result.url).Segments[-1]
# Start-Process ('https://quickchart.io/chart-maker/view/{0}' -f $id)
}
scatter-chartjs $table HTM_AFS_UL_T1C NFNRP_TA
# ----------------------------------------------------------------------
$table | Where-Object LEGAL_NAME -Match 'west banc|qnb|farmers national|midwestone' | ft $fields
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment