FRY9C-general.ps1

$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

Write-Host 'Done' -ForegroundColor Yellow

# ----------------------------------------------------------------------

$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)}
    # ------------------------------------------------------------
    [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
    }
}

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'
)

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'
}

# legend

$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 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 ($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 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
    
    $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 HTM_AFS_UL_T1C NFNRP_TA

# scatter HTM_AFS_UL_T1C LSBRE_TA

# scatter LEVERAGE LSBRE_TA

# scatter CAIL_TA NFNRP_TA