LLM’s are very useful in data analysis. In this blog I analyse a DTCC CFTC Swaps transaction file using LLMs with reasoning, such as ChatGPT o1 and Claude 3.7.

Background

CFTC Public Price Dissemination files are published on the DTCC Dashboard. These contain transaction records for OTC Derivatives traded by US persons for products under CFTC jurisdiction e.g. Interest Rate Swaps. These records consist of a 100 or more columns with details on, the product, size, price and time of trades.

Last week I wrote Analysing CFTC SDR Public Dissemination Data, in which I used Anthropic Claude 3.5 Sonnet, to analyse the data.

Reasoning Models

At the end of that article, I suggested that using a “reasoning” LLM could lead to more interesting results. Reasoning or “chain of thought’ models are designed to perform better on complex requests as they are able to break down the prompt into intermediate steps, which are worked on to give a better response.

Reasoning models are now widely available from OpenAI with ChatGPT o1, Anthropic Claude 3.7 Sonnet, Google Gemini 2.0 Flash Thinking Experimental, DeepSeek R1 and xAI Grok-3.

So, lets see how a few of these perform on our SDR data.

One-shot Prompt

I uploaded the DTCC CFTC SDR cumulative CSV file of all Interest Rate transactions for February 14, 2025, which has > 20,000 transaction rows, is 12.2Mb in size and entered the following prompt:

The attached file contains transaction records, suggest some interactive dashboards to analyse interesting aspects of the data for useful insights

ChatGPT reasoned for 6 seconds but then came back with a long winded text with generic headings including, Revenue and Volume by Category/Segment, Customer Analysis Dashboard, Payment Method and Insights…

So not useful at all.

Claude on the other hand, launched straight into listing and creating code to display dashboards named, Interest Rate Swap Analysis, Currency Exposure and Trends, Contract Maturity and Market Activity.

Unfortunately each of these came back with a failure, requiring me to select the “can you fix” button and on it went.

After 4 or 5 times of doing so and lines and lines of code scrolling down the screen while I twiddled my thumbs and wished that I had been asked a few questions before it began working, I was able to see dashboards with many charts.

While some of these charts were interesting (see below), overall it left me with a feeling of having made a request of a graduate trainee, who had gone away for a few days, (well in the LLMs case many minutes) and then came back with a lot of content that missed the point and jumped to erroneous conclusions!

Hmm, lets try one more LLM.

In this Gemini 2.0 Flash but not 2.0 Flash Experimental (which is best for multi-step reasoning) but does not currently support CSV file uploads.

And showing one of these:

Interesting the relatively high number of Correction and Modification records as compared to New Trades.

Taking it Step by Step

Clearly the one-shot prompt is asking for too much too soon.

Let’s pick one of these LLMs, in this case Claude, (as I like it’s Dashboard output) and see if we can do better by taking it slower and being more specific.

The attached file contains transaction records of OTC Derivatives trades for a single business day, ignoring those records with event timestamp of days prior to 2025-02-14, what are different types of analysis that could be created for the data in this file, just list and describe but do not perform the analysis or create charts

This returned a list of 12 analysis, the first 5 of which I show below:

Nice, let’s progress a few of these.

Using only records with Action type of NEWT and Event type of TRAD, create a dashboard for notional value distribution across different product types but convert notional from trade currency into usd

This returned:

and a few more charts, which look useful.

Block Trades

Next let’s explore the third suggestion on market structure and specifically block trades, which are large trades, subject to differing regulatory requirements.

Using only records with Action type of NEWT and Event type of TRAD, create a dashboard for Block trade and large notional off-facility swap elections but if using notional make sure to convert notional from trade currency into usd

I particularly like the Trading Activity by hour (time here is UMT) and the Block Trade Platform Distribution shows information that I was not aware of; that most are on TWSF and what is IMRD? So, naturally, I entered:

the platform IMRD is a MIC Code, can you find a full name for this trading platform?

Nice, just what I needed.

Cross Currency

Using only records with Action type of NEWT and Event type of TRAD, create a dashboard for Currency pair popularity in cross-currency transactions using trade counts only

That look’s good.

I should check this in Excel, but while the spirit is willing the flesh is weak.

I have covered a fair amount of ground today, so time for some reflection.

Learnings

The CFTC public dissemination file contains Swap transaction records.
I tried two “reasoning models” with one high level prompt, to try and do it all. That did not work.

A strategy of first asking for a list of useful analysis and then drilling down into those that looked interesting, with specific prompts, led to good results. Including insights that were new to me.

Some prior knowledge of the data is helpful in formulating good prompts.

From the LLMs I have used so far, I like Claude for Data analysis.

That’s all for today.