ffiec-unrealized-loss-vs-uninsured-deposits-minimal.ps1

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

$result_por   = Import-Csv 'FFIEC CDR Call Bulk POR 12312022.txt'             -Delimiter "`t" # FFIEC CDR Call Bulk POR 12312022.txt
$result_rc    = Import-Csv 'FFIEC CDR Call Schedule RC 12312022.txt'          -Delimiter "`t" # FFIEC CDR Call Schedule RC 12312022.txt
$result_rco1  = Import-Csv 'FFIEC CDR Call Schedule RCO 12312022(1 of 2).txt' -Delimiter "`t" # FFIEC CDR Call Schedule RCO 12312022(1 of 2).txt
$result_rcri  = Import-Csv 'FFIEC CDR Call Schedule RCRI 12312022.txt'        -Delimiter "`t" # FFIEC CDR Call Schedule RCRI 12312022.txt
$result_rcb1  = Import-Csv 'FFIEC CDR Call Schedule RCB 12312022(1 of 2).txt' -Delimiter "`t" # FFIEC CDR Call Schedule RCB 12312022(1 of 2).txt

function create-index ($data)
{
    $index = @{}; 
    
    foreach ($row in $data)
    { 
        $index.Add($row.IDRSSD, $row) 
    }

    $index
}

$index_rc    = create-index $result_rc
$index_por   = create-index $result_por
$index_rco1  = create-index $result_rco1
$index_rcri  = create-index $result_rcri
$index_rcb1  = create-index $result_rcb1

$table = foreach ($row in $result_rc | Select-Object -Skip 1)
{
    $rc    = $index_rc[$row.IDRSSD]
    $por   = $index_por[$row.IDRSSD]
    $rco1  = $index_rco1[$row.IDRSSD]
    $rcri  = $index_rcri[$row.IDRSSD]
    $rcb1  = $index_rcb1[$row.IDRSSD]
        
    $total_assets = if     ($rc.RCON2170 -ne '') { [decimal] $rc.RCON2170 }
                    elseif ($rc.RCFD2170 -ne '') { [decimal] $rc.RCFD2170 }
                    else                         { '' }    

    $deposits                   = [decimal] $row.RCON2200
    $uninsured_deposits         = [decimal] $rco1.RCON5597
    $uninsured_deposits_percent = if ($deposits -eq 0 -or $deposits -match '[a-z]') { '' } else { [math]::Round($uninsured_deposits / $deposits * 100, 2) }
    
    $tier_1_capital = if ($rcri.RCOA8274 -ne '') { [decimal] $rcri.RCOA8274 }
                  elseif ($rcri.RCFA8274 -ne '') { [decimal] $rcri.RCFA8274 }
                    else                         { '' }
    
    # --------------------------------------------------
    if ($rcb1.RCFD1754 -ne '' -and $rcb1.RCFD1771 -ne '')
    {
        $htm_amortized_cost = $rcb1.RCFD1754
        $htm_fair_value     = $rcb1.RCFD1771
    }
    elseif ($rcb1.RCON1754 -ne '' -and $rcb1.RCON1771 -ne '')
    {
        $htm_amortized_cost = $rcb1.RCON1754
        $htm_fair_value     = $rcb1.RCON1771
    }

    $htm_ac_sub_fv = $htm_amortized_cost - $htm_fair_value
    # --------------------------------------------------
    if ($rcb1.RCFD1772 -ne '' -and $rcb1.RCFD1773 -ne '')
    {
        $afs_amortized_cost = $rcb1.RCFD1772
        $afs_fair_value     = $rcb1.RCFD1773
    }
    elseif ($rcb1.RCON1772 -ne '' -and $rcb1.RCON1773 -ne '')
    {
        $afs_amortized_cost = $rcb1.RCON1772
        $afs_fair_value     = $rcb1.RCON1773
    }

    $afs_ac_sub_fv = $afs_amortized_cost - $afs_fair_value
    # --------------------------------------------------
    
    $ur_htm_afs_pct_t1c = [math]::Round(($htm_ac_sub_fv + $afs_ac_sub_fv) / $tier_1_capital * 100)
    
    [PSCustomObject]@{
        IDRSSD                     = $row.IDRSSD
        NAME                       = $por.'Financial Institution Name'
        total_assets               = $total_assets
        deposits                   = $deposits
        ui_dep                     = $uninsured_deposits
        ui_dep_pct                 = $uninsured_deposits_percent
        t1c                        = $tier_1_capital
        ur_htm_afs_pct_t1c         = $ur_htm_afs_pct_t1c
    }
}

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

$items = $table | ? total_assets -GE 5000000 | ? total_assets -LT 10000000 | ? ui_dep_pct -GE 40

$items | ConvertTo-Csv -NoTypeInformation > c:\temp\out.csv