import pandas as pd
import numpy as np
import stringWRDS Pseudo Data
In this appendix chapter, we alleviate the constraints of readers who don’t have access to WRDS and hence cannot run the code that we provide. We show how to create a pseudo data that contains the WRDS tables and corresponding columns such that all code chunks in this book can be executed with this psueod data. We do not create pseudo data for tables of open-source data sources because they can be freely downloaded from the original sources; check out Accessing and Managing Financial Data.
We deliberately use the pseudo label because the data is not meaningful in the sense that it allows readers to actually replicate the results of the book. For legal reasons, the data does not contain any samples of the original data. We merely generate random numbers for all columns of the tables that we use throughout the books.
To generate the pseudo data, we use the following packages:
We store the data in the folder data-python. Be careful, if you already downloaded the data from WRDS, then the code in this chapter will overwrite your data!
import os
if not os.path.exists("data-python"):
os.makedirs("data-python")Since we draw random numbers for most of the columns, we also define a seed to ensure that the generated numbers are replicable. We also initialize vectors of dates of different frequencies over ten years that we then use to create yearly, monthly, and daily data, respectively.
np.random.seed(1234)
start_date = pd.Timestamp("2003-01-01")
end_date = pd.Timestamp("2022-12-31")
pseudo_years = np.arange(start_date.year, end_date.year + 1, 1)
pseudo_months = pd.date_range(start_date, end_date, freq="MS")
pseudo_days = pd.date_range(start_date, end_date, freq="D")Create Stock Pseudo Data
Let us start with the core data used throughout the book: stock and firm characteristics. We first generate a table with a cross-section of stock identifiers with unique permno and gvkey values, as well as associated exchcd, exchange, industry, and siccd values. The generated data is based on the characteristics of stocks in the crsp_monthly table of the original data, ensuring that the generated stocks roughly reflect the distribution of industries and exchanges in the original data, but the identifiers and corresponding exchanges or industries do not reflect actual firms. Similarly, the permno-gvkey combinations are purely nonsensical and should not be used together with actual CRSP or Compustat data.
number_of_stocks = 100
industries = pd.DataFrame(
{
"industry": [
"Agriculture",
"Construction",
"Finance",
"Manufacturing",
"Mining",
"Public",
"Retail",
"Services",
"Transportation",
"Utilities",
"Wholesale",
],
"n": [81, 287, 4682, 8584, 1287, 1974, 1571, 4277, 1249, 457, 904],
"prob": [
0.00319,
0.0113,
0.185,
0.339,
0.0508,
0.0779,
0.0620,
0.169,
0.0493,
0.0180,
0.03451,
],
}
)
exchanges = pd.DataFrame(
{
"exchange": ["AMEX", "NASDAQ", "NYSE"],
"n": [2893, 17236, 5553],
"prob": [0.113, 0.671, 0.216],
}
)
stock_identifiers_list = []
for x in range(1, number_of_stocks + 1):
exchange = np.random.choice(exchanges["exchange"], p=exchanges["prob"])
industry = np.random.choice(industries["industry"], p=industries["prob"])
exchcd_mapping = {
"NYSE": np.random.choice([1, 31]),
"AMEX": np.random.choice([2, 32]),
"NASDAQ": np.random.choice([3, 33]),
}
siccd_mapping = {
"Agriculture": np.random.randint(1, 1000),
"Mining": np.random.randint(1000, 1500),
"Construction": np.random.randint(1500, 1800),
"Manufacturing": np.random.randint(1800, 4000),
"Transportation": np.random.randint(4000, 4900),
"Utilities": np.random.randint(4900, 5000),
"Wholesale": np.random.randint(5000, 5200),
"Retail": np.random.randint(5200, 6000),
"Finance": np.random.randint(6000, 6800),
"Services": np.random.randint(7000, 9000),
"Public": np.random.randint(9000, 10000),
}
stock_identifiers_list.append(
{
"permno": x,
"gvkey": str(x + 10000),
"exchange": exchange,
"industry": industry,
"exchcd": exchcd_mapping[exchange],
"siccd": siccd_mapping[industry],
}
)
stock_identifiers = pd.DataFrame(stock_identifiers_list)Next, we construct three panels of stock data with varying frequencies: yearly, monthly, and daily. We begin by creating the stock_panel_yearly panel. To achieve this, we combine the stock_identifiers table with a new table containing the variable year from pseudo_years. The expand_grid() function ensures that we get all possible combinations of the two tables. After combining, we select only the gvkey and year columns for our final yearly panel.
Next, we construct the stock_panel_monthly panel. Similar to the yearly panel, we use the expand_grid() function to combine stock_identifiers with a new table that has the date variable from pseudo_months. After merging, we select the columns permno, gvkey, date, siccd, industry, exchcd, and exchange to form our monthly panel.
Lastly, we create the stock_panel_daily panel. We combine stock_identifiers with a table containing the date variable from pseudo_days. After merging, we retain only the permno and date columns for our daily panel.
stock_panel_yearly = pd.DataFrame(
{
"gvkey": np.tile(stock_identifiers["gvkey"], len(pseudo_years)),
"year": np.repeat(pseudo_years, len(stock_identifiers)),
}
)
stock_panel_monthly = pd.DataFrame(
{
"permno": np.tile(stock_identifiers["permno"], len(pseudo_months)),
"gvkey": np.tile(stock_identifiers["gvkey"], len(pseudo_months)),
"date": np.repeat(pseudo_months, len(stock_identifiers)),
"siccd": np.tile(stock_identifiers["siccd"], len(pseudo_months)),
"industry": np.tile(stock_identifiers["industry"], len(pseudo_months)),
"exchcd": np.tile(stock_identifiers["exchcd"], len(pseudo_months)),
"exchange": np.tile(stock_identifiers["exchange"], len(pseudo_months)),
}
)
stock_panel_daily = pd.DataFrame(
{
"permno": np.tile(stock_identifiers["permno"], len(pseudo_days)),
"date": np.repeat(pseudo_days, len(stock_identifiers)),
}
)Pseudo beta table
We then proceed to create pseudo beta values for our stock_panel_monthly table. We generate monthly beta values beta_monthly using the rnorm() function with a mean and standard deviation of 1. For daily beta values beta_daily, we take the pseudo monthly beta and add a small random noise to it. This noise is generated again using the rnorm() function, but this time we divide the random values by 100 to ensure they are small deviations from the monthly beta.
beta_pseudo = (stock_panel_monthly
.assign(
beta_monthly=np.random.normal(
loc=1, scale=1, size=len(stock_panel_monthly)
),
beta_daily=lambda x: (
x["beta_monthly"]+np.random.normal(scale=0.01, size=len(x))
)
)
)
beta_pseudo.to_parquet("data-python/beta.parquet")Pseudo compustat_annual table
To create pseudo annual firm characteristics, we take all columns from the compustat_annual table and create random numbers between 0 and 1. For simplicity, we set the datadate for each firm-year observation to the last day of the year, although it is empirically not the case.
relevant_columns = [
"seq",
"ceq",
"at",
"lt",
"txditc",
"txdb",
"itcb",
"pstkrv",
"pstkl",
"pstk",
"capx",
"oancf",
"sale",
"cogs",
"xint",
"xsga",
"be",
"op",
"at_lag",
"inv",
]
commands = {
col: np.random.rand(len(stock_panel_yearly)) for col in relevant_columns
}
compustat_pseudo = (stock_panel_yearly
.assign(
datadate=lambda x: pd.to_datetime(x["year"].astype(str) + "-12-31")
)
.assign(**commands)
)
compustat_pseudo.to_parquet("data-python/compustat_annual.parquet")Pseudo crsp_monthly table
The crsp_monthly table only lacks a few more columns compared to stock_panel_monthly: the returns ret drawn from a normal distribution, the excess returns ret_excess with small deviations from the returns, the shares outstanding shrout and the last price per month altprc both drawn from uniform distributions, and the market capitalization mktcap as the product of shrout and altprc.
crsp_monthly_pseudo = (
stock_panel_monthly.assign(
ret=lambda x: np.fmax(np.random.normal(size=len(x)), -1),
ret_excess=lambda x: x["ret"] - np.random.uniform(0, 0.0025, len(x)),
shrout=1000 * np.random.uniform(1, 50, len(stock_panel_monthly)),
altprc=np.random.uniform(0, 1000, len(stock_panel_monthly)),
)
.assign(mktcap=lambda x: x["shrout"] * x["altprc"])
.sort_values(by=["permno", "date"])
.assign(mktcap_lag=lambda x: x.groupby("permno")["mktcap"].shift(1))
.reset_index(drop=True)
)
crsp_monthly_pseudo.to_parquet("data-python/crsp_monthly.parquet")Pseudo crsp_daily table
The crsp_daily table only contains a date column and the daily excess returns ret_excess as additional columns to stock_panel_daily.
crsp_daily_pseudo = (stock_panel_daily
.assign(
ret_excess=lambda x: np.fmax(np.random.normal(size=len(x)), -1)
)
.reset_index(drop=True)
)
crsp_daily_pseudo.to_parquet("data-python/crsp_daily.parquet")Create Bond Pseudo Data
Lastly, we move to the bond data that we use in our books.
Pseudo fisd data
To create pseudo data with the structure of Mergent FISD, we calculate the empirical probabilities of actual bonds for several variables: maturity, offering_amt, interest_frequency, coupon, and sic_code. We use these probabilities to sample a small cross-section of bonds with completely made up complete_cusip, issue_id, and issuer_id.
number_of_bonds = 100
def generate_cusip():
"""Generate cusip."""
characters = list(string.ascii_uppercase + string.digits) # Convert to list
cusip = ("".join(np.random.choice(characters, size=12))).upper()
return cusip
fisd_pseudo = (
pd.DataFrame(
{"complete_cusip": [generate_cusip() for _ in range(number_of_bonds)]}
)
.assign(
maturity=lambda x: np.random.choice(pseudo_days, len(x), replace=True),
offering_amt=lambda x: np.random.choice(
np.arange(1, 101) * 100000, len(x), replace=True
),
)
.assign(
offering_date=lambda x: (
x["maturity"]
- pd.to_timedelta(
np.random.choice(np.arange(1, 26) * 365, len(x), replace=True),
unit="D",
)
)
)
.assign(
dated_date=lambda x: (
x["offering_date"]
- pd.to_timedelta(
np.random.choice(np.arange(-10, 11), len(x), replace=True),
unit="D",
)
),
interest_frequency=lambda x: np.random.choice(
[0, 1, 2, 4, 12], len(x), replace=True
),
coupon=lambda x: np.random.choice(
np.arange(0, 2.1, 0.1), len(x), replace=True
),
)
.assign(
last_interest_date=lambda x: (
x[["maturity", "offering_date", "dated_date"]].max(axis=1)
),
issue_id=lambda x: x.index + 1,
issuer_id=lambda x: np.random.choice(
np.arange(1, 251), len(x), replace=True
),
sic_code=lambda x: (
np.random.choice(np.arange(1, 10) * 1000, len(x), replace=True)
).astype(str),
)
)
fisd_pseudo.to_parquet("data-python/fisd.parquet")Pseudo trace_enhanced data
Finally, we create a pseudo bond transaction data for the fictional CUSIPs of the pseudo fisd data. We take the date range that we also analyze in the book and ensure that we have at least five transactions per day to fulfill a filtering step in the book.
number_of_bonds = 100
start_date = pd.Timestamp("2014-01-01")
end_date = pd.Timestamp("2016-11-30")
bonds_panel = pd.DataFrame(
{
"cusip_id": np.tile(
fisd_pseudo["complete_cusip"], (end_date - start_date).days + 1
),
"trd_exctn_dt": np.repeat(
pd.date_range(start_date, end_date), len(fisd_pseudo)
),
}
)
trace_enhanced_pseudo = (
pd.concat([bonds_panel] * 5)
.assign(
trd_exctn_tm=lambda x: pd.to_datetime(
x["trd_exctn_dt"].astype(str)
+ " "
+ np.random.randint(0, 24, size=len(x)).astype(str)
+ ":"
+ np.random.randint(0, 60, size=len(x)).astype(str)
+ ":"
+ np.random.randint(0, 60, size=len(x)).astype(str)
),
rptd_pr=np.random.uniform(10, 200, len(bonds_panel) * 5),
entrd_vol_qt=1000
* np.random.choice(range(1, 21), len(bonds_panel) * 5, replace=True),
yld_pt=np.random.uniform(-10, 10, len(bonds_panel) * 5),
rpt_side_cd=np.random.choice(
["B", "S"], len(bonds_panel) * 5, replace=True
),
cntra_mp_id=np.random.choice(
["C", "D"], len(bonds_panel) * 5, replace=True
),
)
.reset_index(drop=True)
)
trace_enhanced_pseudo.to_parquet("data-python/trace_enhanced.parquet")As stated in the introduction, the data does not contain any samples of the original data. We merely generate random numbers for all columns of the tables that we use throughout this book.