GenAI offers the potential of writing your own highly specific software applications and in this article I do this for information in Regulatory Reports of US Bank Holding Companies

Introduction

The general paradigm in software is to build applications that if successful are used by thousands or millions of users. So a relatively small number of applications used by a large population (1000x) of users.

GenAI and LLMs offer the potential of everyone creating their own applications, be-spoke and customised just for them. So a world of millions of applications used by individuals or small groups.

If that future, does come to pass, it would certainly change the economic model of the Software business. While it is too early to call judgement on the likelihood of such a future, it is important for those of us working in the software industry to pay close attention to what is possible with GenAI tools today and is likely to be tomorrow.

In today’s article I build a custom software application to get insights on this journey to personal software.

Background

In my recent blog, Examining Consolidated Financial Statements for Bank Holding Companies, I downloaded FR Y-9C reports from the FFEIC NIC website.

Today, I will use the FFIEC102 Market Risk Regulatory report as my data source:

Quarterly FFIEC102 reports for the past 5 years are available on the website for US Bank Holding Companies, in both PDF and CSV format.

However, what I am really interested in is a comparison of the six largest US Banks for the past year of quarterly returns of market risk capital measures such as Value-at-Risk.

That is something I cannot get without a lot of manual effort to pull together the data.

My goal today is to build an application to do this for a recent history of quarterly reports.

One that I should also be able to use in future when new ones becomes available as they will shortly for June 30, 2025 (probably before this blog is published).

In my usual blogs, I start at the beginning step by step, showing my work process and end with the final result. However today, I will flip the order and first show the application I built, what data it shows, before covering the process it took to build it.

The Application

Use the Upload CSV button to select the file with data from FFIEC102 reports, which then shows:

My csv data file covers 6 Institutions for 5 (quarterly) reporting periods, a total of 2,020 metrics in 10 sections.

Value-at-Risk

The selectors at the top, allow me to pick a Section of the report and then a Metric. Let’s start with the Value-at-risk Section and the Previous Days VaR-based Measure.

(The units here are in thousands of USD)

The VaR metric is for a 10-day holding period and 99% confidence level, meaning that a firm could lose at least this amount in 1 out of 100 days, due to market prices changes over 10-days. (See Code of Federal Regulations Title 12 Chapter II Subchapter A Part 217 Subpart F, here).

From the Chart, we can see:

• Goldman Sachs has the highest VaR for every quarter, starting at $430 million on March 31, 2024, a high of $501 million on Dec 31, 2024 and down to $370 million in the recent quarter (hovering over the points in the chart will show exact values)
• Wells Fargo has the lowest VaR for every quarter, starting at $46 million and ending at $93 million
• Giving us an insight into the very different magnitude of market risk taken by these institutions
• JP Morgan Chase, the largest US Bank, has the largest QoQ change of 48%, from $146 million on Dec 31, 2024 to $216 million on Mar 31, 2025 (ignore the YoY legend, bug in the software, not picked up in testing, my bad, ….)

These VaR measures have been calculated using market prices for a recent 1-year period prior to the quarter end dates.

What would happen in stressed markets?

There is a metric for that – Stressed VaR.

• Goldman Sachs has the highest Stressed VaR for every quarter and it’s $1.2 billion(!) on Sep 30, 2024, is 2.7X higher than it’s VaR for that period. (The Stress period is 1-year starting June 5, 2008, Great Financial Crises).
• Wells Fargo Stressed VaR stands-out as being close to or higher than the other 4 banks, in contrast to its VaR which was materially lower. It’s Stressed VaR for Dec 31, 2024 is $709 million, materially higher then the other 4 banks which are around $400 million, for this date and for Wells Fargo’s other quarters, both of which are surprising. The Stress period start date used by Wells is Mar 29, 2019 , the Covid Crisis.
• Bank of America shows the largest QoQ increase and Wells Fargo the largest decrease (again please ignore YoY legend).

Market Risk Weighted Assets

There are more metrics in the data covering Specific Risk AddOns, Incremental Risk and Comprehensive Risk, which lead to the final result required for Regulatory Capital; namely Market Risk Weighted Assets (RWAs).

Selecting the Standardized Metric (as opposed to Advanced):

For March 31, 2025:

• JP Morgan Chase has the highest mRWA of $99.4 billion
• Goldman Sachs is next at $83.5 billion
• Bank of America with $81.4 billion
• Citigroup with $71.6 billion
• Wells Fargo $68.2 billion
• Morgan Stanley $59 billion

JP Morgan with the largest QoQ increase and Goldman Sachs the largest decrease (ignore YoY legend).

Try Yourself

To use the application yourself, download the csv file, then launch the Interactive Dashboard, upload the csv file and select a Section and Metric to view the data.

My Process

Next, as promised, some details on what it took to build this application.

First, I started with the source data.

• Viewing an FFIEC102 report in PDF to understand what it showed
• Examining the CSV for the same PDF
• Realising that while the CSV had codes for a metric, e.g. MRRRS298 and a (long) Description, it did not have the Section / Category, without which it would be difficult to find/select a metric

Now I could have tried to do everything in the Context window of a GenAI App; so load all the individual FFIEC102 CSV files, for 6 firms and 5 quarters, that is 30 files and it may or may not have worked given the number of files and size limits.

Either way, that would not be an efficient way to use LLMs.

Far better to pre-aggregate data.

So using Claude Desktop, I uploaded the FFIEC102 pdf and csv files and entered the following prompt:

The attached CSV file has a row for each ItemName and these ItemNames are also found in the PDF, where they are under a specific section with a line number. 

As examples:ItemName MRRRS298 is under the Section “Value-at-risk (VaR)-based Capital Requirement and LineNumber is 1. ItemName MRRRS306 is under the Section “Specific Risk Add-Ons” and LineNumber is 9. 

Write a python script that creates a mapping.csv file that for every ItemName with a numeric value in the input CSV file, works out the Section and LineNumber from the input PDF. 

In addition ItemNames in the following list do not need mappings: Zip Code, ID_RSSD.

It did take a few attempts to write that, as it is a fine balance between not being specific enough and being too specific (can be taken too literally, with unforeseen side effects).

A couple of subsequent prompts got me the mapping file I wanted and then I entered:

Now create a python script that uses the FFIEC102.CSV and the mapping file to create a new csv with the following columns ID_RSSD, InstitutionName, ReportDate, ItemName, Section, LineNumber, Description, Value. 

Only rows for which an entry is found in the mapping file should be written to the output csv. 

After creating the output file, confirm that it has the correct numbers of rows expected given the above logic.

After a few further prompts, to transform ReportDate in YYYYMMDD format and change the python to take a top-level directory argument, I was able to run the code.

This found all the downloaded FFEIC files and created the combined csv file I needed for my app.

Now I was ready to build a Web App to view this data.

So using Claude Desktop, I uploaded the combined csv file and entered:

Create an interactive dashboard so I can visualise the data. 

I need to be able to select a specific ItemName, but as this are just codes, I need to first select Section and be shown a list of LineNumbers and Descriptions for that Section from which I select one row. This selection should be quick, easy to use and take minima vertical screen space on the dashboard. 

For the selection, I should see a line chart showing the trend over time for each institution. And below the chart a commentary that highlights the institutions with the largest YoY increase for that metric and on the next line the largest YoY decrease.

The look and feel of the dashboard should be modern and concise, so the layout should not require any or much vertical scrolling to view. 

The dashboard should also have a file upload function, so a user can upload their own ffiec file.

Again a few prompts to get to this version and on running the artefact there was a code error, which I asked Claude to resolve and I had my application to publish and share.

While I did test the metrics shown were correct by comparing against an Excel workbook and Pivot Tables, I did not check the Year-on-Year (YoY) percentages. In reality instead of the YoY in my prompt, the calculations were Quarter-on-Quarter (QoQ), my bad for not testing.

In addition, the App does need further developments, for instance:

• Not all the metrics are USD thousands, some are number of days, a few are ratios. (To fix that I would have need to have a unit_type column in my data file).
• I could have converted USD thousands to USD in the data source.
• Another view I would want is to select multiple metrics to show in a table for a single institution over time or for multiple institutions and one report date.

But those are tasks for another day.

Thoughts

It took about 3 hours of elapsed time from start to finish.

Not bad, though like most projects, at the start you make good progress but the last 15% takes a lot longer than expected.

With GenAI this is even more pronounced, the project at first seems deceptively simple and the work of one or two prompts.

However reality then bites and you have to think deeper and iterate to make the progress you want.

And you certainly cannot abdicate all thinking to the LLM.

E.g. I first tried to ask Claude to create a short name (20 chars) for each metric from the long description, for ease of use in the GUI, but the result was not useable (lots of duplicate names, too literal in it’s interpretation of my ask).

Each step requires iteration: ask for code feature, inspect, run, test , repeat.

Much like programming, you progress in steps, something goes wrong, back and forth to fix, continue to progress until what you are building is good enough given the time you have.

In Summary

Used GenAI to create an application to view the market risk regulatory capital metrics of the six largest U.S. banks.

Analysis reveals interesting insights : Goldman Sachs consistently has the highest VaR, Wells Fargo shows low VaR but unusually high Stressed VaR, and one of JPMorgan or Goldman Sachs has the highest RWA for market risk depending on the quarter.

Process shows the potential of be-spoke personal software application development, while underscoring the need for human upfront thinking, step by step by guidance, iterative development and testing.