TRACE and FISD

Corporate-bond research rests on two datasets: Mergent FISD for bond characteristics and TRACE for transactions. This chapter downloads both from WRDS, filters FISD to a clean universe of US corporate bonds, and pulls TRACE trades for those bonds. Use the R | Python toggle to switch.

library(tidyverse)
library(tidyfinance)
library(dbplyr)
library(RPostgres)

import pandas as pd
import numpy as np
import sqlite3
from sqlalchemy import create_engine

Connecting to WRDS

Both datasets live on WRDS, reached over an authenticated PostgreSQL connection (credentials kept out of the code, in environment variables).

wrds <- dbConnect(
  Postgres(),
  host = "wrds-pgdata.wharton.upenn.edu",
  dbname = "wrds", port = 9737, sslmode = "require",
  user = Sys.getenv("WRDS_USER"),
  password = Sys.getenv("WRDS_PASSWORD")
)

import os
connection_string = (
    "postgresql+psycopg2://"
    f"{os.getenv('WRDS_USER')}:{os.getenv('WRDS_PASSWORD')}"
    "@wrds-pgdata.wharton.upenn.edu:9737/wrds"
)
wrds = create_engine(connection_string, pool_pre_ping=True)

Bond characteristics from FISD

We query Mergent FISD for the bond master, keeping only US corporate, non-convertible, fixed-coupon, dollar-denominated bonds — the standard analysis universe. The filters drop the many special bond types that would contaminate yield comparisons.

fisd_query <- "
  SELECT complete_cusip, maturity, offering_amt, offering_date,
         dated_date, interest_frequency, coupon, last_interest_date,
         issue_id, issuer_id, sic_code
  FROM fisd.fisd_mergedissue
  WHERE security_level = 'SEN'
    AND (slob = 'N' OR slob IS NULL)
    AND security_pledge IS NULL
    AND (asset_backed = 'N' OR asset_backed IS NULL)
    AND (defeased = 'N' OR defeased IS NULL)
    AND bond_type IN ('CDEB','CMTN','CMTZ','CZ','USBN')
    AND (pay_in_kind != 'Y' OR pay_in_kind IS NULL)
    AND (yankee = 'N' OR yankee IS NULL)
    AND (canadian = 'N' OR canadian IS NULL)
    AND foreign_currency = 'N'
    AND coupon_type IN ('F')
    AND (convertible = 'N' OR convertible IS NULL)
    AND (exchangeable = 'N' OR exchangeable IS NULL)
"

fisd <- dbGetQuery(wrds, fisd_query)

fisd_query = """
  SELECT complete_cusip, maturity, offering_amt, offering_date,
         dated_date, interest_frequency, coupon, last_interest_date,
         issue_id, issuer_id, sic_code
  FROM fisd.fisd_mergedissue
  WHERE security_level = 'SEN'
    AND (slob = 'N' OR slob IS NULL)
    AND (asset_backed = 'N' OR asset_backed IS NULL)
    AND (defeased = 'N' OR defeased IS NULL)
    AND bond_type IN ('CDEB','CMTN','CMTZ','CZ','USBN')
    AND coupon_type IN ('F')
    AND foreign_currency = 'N'
    AND (convertible = 'N' OR convertible IS NULL)
"""

fisd = pd.read_sql_query(fisd_query, con=wrds)

Linking issuers and saving

FISD's issuer table adds the issuer's industry and domicile, joined on issuer_id. The cleaned master is stored locally for reuse by the bond chapters.

fisd <- download_data(type = "wrds_fisd")
write_parquet(fisd, "data-r/fisd.parquet")

fisd = tf.download_data(domain="wrds", dataset="fisd")
fisd.to_parquet("data-python/fisd.parquet")

Bond transactions from TRACE

We pull Enhanced TRACE trades for the FISD CUSIPs. The raw feed needs cleaning (cancellations, corrections, reversals) — handled by the helper download here, and detailed in the Cleaning Enhanced TRACE appendix. The result is a transaction panel of price, volume, and yield per trade.

cusips <- unique(fisd$complete_cusip)

trace_enhanced <- download_data(
  type = "wrds_trace_enhanced",
  cusips = cusips,
  start_date = "2014-01-01",
  end_date = "2016-11-30"
)

write_parquet(trace_enhanced, "data-r/trace_enhanced.parquet")

cusips = list(fisd["complete_cusip"].unique())

trace_enhanced = tf.download_data(
    domain="wrds", dataset="trace_enhanced",
    cusips=cusips, start_date="2014-01-01", end_date="2016-11-30"
)

trace_enhanced.to_parquet("data-python/trace_enhanced.parquet")

For readers without WRDS access, the bond pseudo-data appendix generates tables with this same structure so the downstream code still runs.

Study notes following the Tidy Finance curriculum by Scheuch, Voigt, Weiss, and Frey. Prose is my own; the R/Python code is reproduced from the book's open-source source, licensed CC BY-NC-SA 4.0.