Presentation given at DuckCon #6 2025

This presentation will give you 2 takeaways:

An overall impression of how DuckDB can be used in Finance.
Show you QStudio a free SQL client first released in 2013 and that today integrates DuckDB.

Bank Trading and Data Flow
Local Quant Analysis
Loading the NYSE TAQ Trade Data
Querying the Data
DuckDB Pivot
How to make money

If you want to run these demos yourself, download QStudio.

Bank Trading and Data Flow

Trading departments within Banks are large entities that consume data and either conduct trades for clients or on their own behalf on global markets
The left-hand side of this diagram shows
- Exchanges - LSE - London Stock Exchange, NYSE, XHKG, Euronext. Basically marketplaces where very large firms can trade with each other.
- Market Data Providers - Bloomberg, refinitiv, yahoo, polygon. That aggregate various exchanges and sources for you, rather than you having to negotiate a contract with each one.
- Alternative Data Sources
The three middle blocks: Data Platforms -> Analysis -> Trading represent the bank itself.
Finally the right-hand side represents the bank taking actions and interacting externally with clients of conducting trades on external markets.

Example Finance Use-cases

Historical market data storage and analysis. e.g. Backtesting. Exchange Data Arrives -> Stored on Big Data Platform -> Used as part of framework to replay a trading strategy to predict its' profitability or risk profile.
Local quant analysis e.g. Liquidity analysis, PnL analysis, profitability per client.
Prices aka quotes arrive from the market, the bank holds onto some stock but regularly resells it to clients via a trading platform (Like your local supermarket but at much higher speed). An analyst could be tasked with:
1. Examining the exchange data and the orders to see if they were executed well.
2. Examining everything that each client bought and sold and trying to find the profit or loss the bank made for that particular customer.
Real-time Streaming Calculation Engines e.g. Streaming VWAP, Streaming TCA

Local Quant Analysis

DuckDB is a powerful tool for local data analysis and I believe that's currently where it could best be used in finance. So let's look at real examples:

Loading the NYSE TAQ Trade Data

Download the data at https://ftp.nyse.com/Historical%20Data%20Samples/DAILY%20TAQ/,
in particular the EQY_US_ALL_TRADE_20240702.gz and unzip the file.
Download QStudio which bundles DuckDB and go to File menu -> New DuckDB Database.
Run the below SQL code to load the data into a table and to add a ttime column of the correct type.

-- Run the below SQL code to load the data into a table and to add a ttime column of the correct type.
CREATE OR REPLACE TABLE trade AS SELECT * FROM read_csv('F:\\temp\\data-analysis\\nyse\\EQY_US_ALL_TRADE_20240702', delim = '|', header = true);
ALTER TABLE trade ADD COLUMN ttime TIME;
UPDATE trade SET ttime=TRY_CAST(SUBSTRING("Time", 1, 2)||':'||SUBSTRING("Time", 3, 2)||':'||SUBSTRING("Time", 5, 2)||'.'||SUBSTRING("Time", 7) AS TIME);
ALTER TABLE trade ADD COLUMN ts TIMESTAMP;
UPDATE trade SET ts=TRY_CAST(CONCAT('2024-07-02 ',ttime) AS TIMESTAMP);
DESCRIBE trade;

SELECT COUNT(*) FROM trade LIMIT 10;
SELECT * FROM trade LIMIT 10;

Querying the Data

SELECT COUNT(*) FROM trade;
-- 68085148
DESCRIBE trade;

column_name	column_type	null
Time	VARCHAR	YES
Exchange	VARCHAR	YES
Symbol	VARCHAR	YES
Sale Condition	VARCHAR	YES
Trade Volume	BIGINT	YES
Trade Price	DOUBLE	YES
Trade Stop Stock Indicator	VARCHAR	YES
Trade Correction Indicator	VARCHAR	YES
Sequence Number	BIGINT	YES
Trade Id	BIGINT	YES
Source of Trade	VARCHAR	YES
Trade Reporting Facility	VARCHAR	YES
Participant Timestamp	VARCHAR	YES
Trade Reporting Facility TRF Timestamp	VARCHAR	YES
Trade Through Exempt Indicator	BIGINT	YES
ttime	TIME	YES

VWAP Volume Weighted Average Price

select Symbol,avg("Trade Price") AS avg, sum("Trade Volume" * "Trade Price")/sum("Trade Volume") AS wavg FROM trade GROUP BY ALL ORDER BY Symbol DESC LIMIT 20 ;
CREATE OR REPLACE function wavg(v,p) AS sum(v * p)/sum(v);
select Symbol,avg("Trade Price") AS avg,wavg("Trade Volume", "Trade Price") AS wavg FROM trade GROUP BY ALL ORDER BY Symbol DESC LIMIT 20;

DuckDB Pivot

Pivot tables allow exploring data easily. QStudio allows click and select to change pivots, leveraging the power of duckdb pivots and allowing you to (4) copy the pivot query.

select Symbol,Exchange,"Sale Condition","Source Of Trade", 
    COUNT(*) as num, 0.1+sum("Trade Volume") AS totalvolume, 
    avg("Trade Price") AS navg, sum("Trade Volume" * "Trade Price")/sum("Trade Volume") AS wavg
    FROM trade 
    WHERE Symbol IN (SELECT Symbol FROM 
        (SELECT Symbol,sum("Trade Volume") AS v FROM trade  GROUP BY ALL ORDER BY v DESC LIMIT 10))
    GROUP BY ALL ORDER BY Symbol;
    
PIVOT (
select Symbol,Exchange,"Sale Condition","Source Of Trade", 
    COUNT(*) as num, 0.1+sum("Trade Volume") AS totalvolume, 
    avg("Trade Price") AS navg, 
    sum("Trade Volume" * "Trade Price")/sum("Trade Volume") AS wavg
    FROM trade 
    WHERE Symbol IN (SELECT Symbol FROM 
    (SELECT Symbol,sum("Trade Volume") AS v FROM trade  
        GROUP BY ALL ORDER BY v DESC LIMIT 10))
    GROUP BY ALL ORDER BY Symbol) 
ON "Exchange" USING sum("totalvolume") as "totalvolume" GROUP BY "Symbol" ORDER BY "Symbol"

Candlesticks are easy with window functions

SELECT  min(ts) as ttime,
        min("Trade Price") as low, first("Trade Price") as open, 
        last("Trade Price") as close, max("Trade Price") as high,
        sum("Trade Volume") AS volume
                from trade where Symbol = 'AAPL'
            GROUP BY time_bucket(INTERVAL '30 minutes', ts) ORDER BY ttime);

How to make money with window functions

CREATE OR REPLACE MACRO xbarTime(n,x) AS 
    n*(((date_part('hour', x::Time)*60) + date_part('minute', x::Time))//n);

SELECT ttime,sums FROM (
    SELECT *,sum(profit) over (ORDER BY ttime ASC) AS sums FROM    
    (SELECT *,buy2*mv AS profit FROM
        (SELECT *,lag(buy) OVER (ORDER BY ttime ASC) AS buy2,(lead(price) OVER (ORDER BY ttime ASC))-price AS mv  FROM
            (SELECT *, CASE WHEN mavg15 > price THEN -1 ELSE 1 END AS buy FROM
                (SELECT ttime,price, avg(price) OVER(ORDER BY ttime ASC ROWS BETWEEN 15 PRECEDING AND CURRENT ROW) as mavg15
                    FROM 
                    (SELECT min(ttime) as ttime, 
                            avg("Trade Price") as price
                        from trade where Symbol = 'AAPL'
                            GROUP BY xbarTime(5,ttime) ORDER BY ttime)
                )
            )
        )
    )
);

Stock Data Analysis using DuckDB