Accessing and Managing Financial Data

Note

You are reading Tidy Finance with Python. You can find the equivalent chapter for the sibling Tidy Finance with R here.

In this chapter, we suggest a way to organize your financial data. Everybody who has experience with data is also familiar with storing data in various formats like CSV, XLS, XLSX, or other delimited value storage. Reading and saving data can become very cumbersome when using different data formats and across different projects. Moreover, storing data in delimited files often leads to problems with respect to column type consistency. For instance, date-type columns frequently lead to inconsistencies across different data formats and programming languages.

This chapter shows how to import different open-source datasets. Specifically, our data comes from the application programming interface (API) of Yahoo Finance, a downloaded standard CSV file, an XLSX file stored in a public Google Drive repository, and other macroeconomic time series. We store all the data in a single database folder, which serves as the only source of data in subsequent chapters. We conclude the chapter by providing some tips on managing databases.

First, we load the Python packages that we use throughout this chapter. Later on, we load more packages in the sections where we need them.

import pandas as pd
import numpy as np
import io
import re
import zipfile
from curl_cffi import requests

Moreover, we initially define the date range for which we fetch and store the financial data, making future data updates tractable. In case you need another time frame, you can adjust the dates below. Our data starts with 1960 since most asset pricing studies use data from 1962 on.

start_date = "1960-01-01"
end_date = "2024-12-31"

Fama-French Data

We start by downloading some famous Fama-French factors (e.g., Fama and French 1993) and portfolio returns commonly used in empirical asset pricing. The data are freely available from Kenneth French’s Data Library, but the raw files come in a rather idiosyncratic format. If you access the data via the website, the manual raw workflow looks like this:

Go to the website
Find the right dataset
Download a ZIP file
Extract the CSV inside
Select the right data table from the file and import the table into Python
Clean the dates, scale the returns, fix column names, handle missing values, etc.

Doing this once is fine; doing it repeatedly across projects is exactly the type of boilerplate that’s easy to mess up and annoying to maintain. It is therefore natural to automate these steps in Python.

From manual steps to a download script

A minimal download script mirrors the manual steps one by one. For example, to fetch a Fama–French dataset you first construct the URL:

dataset = "F-F_Research_Data_Factors"
base_url = "http://mba.tuck.dartmouth.edu/pages/faculty/ken.french/ftp/"
url = f"{base_url}{dataset}_CSV.zip"

Next, you replace the browser download with an HTTP request and extract the ZIP in memory:

resp = requests.get(url)
resp.raise_for_status()

with zipfile.ZipFile(io.BytesIO(resp.content)) as zf:
    file_name = zf.namelist()[0]  # Ken French ZIPs contain one file
    raw_text = zf.read(file_name).decode("latin1")

The most important part of this chunk is the requests.get() call. This is the moment where we replace all the manual browser work (open the website, click download, save the file) with a single, reproducible line of code. Then, calling raise_for_status() ensures we stop immediately if the server returns an error (e.g. HTTP 404 or 500) instead of quietly handling a broken file. Once this succeeds, resp.content is guaranteed to contain valid ZIP bytes that we can open in memory.

The raw file contains documentation text followed by the actual data table(s). To emulate scrolling down until the numbers start, you can split the file into blocks and keep the long one that contains the table:

chunks = raw_text.split("\r\n\r\n")
table_text = max(chunks, key=len)

Within this block, the first CSV header line starts at the first line beginning with a comma. We add a “Date” label for the index and pass everything to read_csv:

match = re.search(r"^\s*,", table_text, flags=re.M)
start = match.start()
csv_text = "Date" + table_text[start:]

factors_ff_raw = pd.read_csv(io.StringIO(csv_text), index_col=0)

At this point, the index still consists of integer date codes with different lengths depending on the frequency. We need a bit of logic to convert them into a proper DatetimeIndex:

s = factors_ff_raw.index.astype(str)

if (s.str.len() == 8).all():  # daily: YYYYMMDD
    dt = pd.to_datetime(s, format="%Y%m%d")
elif (s.str.len() == 6).all():  # monthly: YYYYMM
    dt = pd.to_datetime(s + "01", format="%Y%m%d")
elif (s.str.len() == 4).all():  # annual: YYYY
    dt = pd.to_datetime(s + "0101", format="%Y%m%d")
    dt = dt.dt.to_period("A-DEC").dt.to_timestamp("end")
else:
    raise ValueError("Unknown date format in Fama–French index.")

factors_ff_raw = factors_ff_raw.set_index(dt)
factors_ff_raw.index.name = "date"

Finally, we still have to clean the data:

Convert returns from percent to decimal.
Standardize column names (e.g., all lower case and Mkt-RF to mkt_excess, RF to risk_free)
Replace special missing-value codes (-99.99, -999) with actual missing values
Filter the data by a start and end date

This all could look like this:

# start and end dates
if start_date:
    factors_ff_raw = factors_ff_raw[factors_ff_raw.index >= pd.to_datetime(start_date)]
if end_date:
    factors_ff_raw = factors_ff_raw[factors_ff_raw.index <= pd.to_datetime(end_date)]

factors_ff3_monthly = (factors_ff_raw
    .div(100)
    .reset_index(names="date")
    .rename(columns=str.lower)
    .rename(columns={"mkt-rf": "mkt_excess", "rf": "risk_free"})
    .replace({"-99.99": pd.NA, -99.99: pd.NA, -999: pd.NA})
)
factors_ff3_monthly

	date	mkt_excess	smb	hml	risk_free
0	1960-01-01	-0.0698	0.0212	0.0265	0.0033
1	1960-02-01	0.0116	0.0060	-0.0197	0.0029
2	1960-03-01	-0.0163	-0.0055	-0.0275	0.0035
3	1960-04-01	-0.0171	0.0022	-0.0214	0.0019
4	1960-05-01	0.0312	0.0129	-0.0373	0.0027
...	...	...	...	...	...
775	2024-08-01	0.0160	-0.0349	-0.0110	0.0048
776	2024-09-01	0.0172	-0.0013	-0.0277	0.0040
777	2024-10-01	-0.0100	-0.0099	0.0086	0.0039
778	2024-11-01	0.0649	0.0446	0.0015	0.0040
779	2024-12-01	-0.0317	-0.0271	-0.0300	0.0037

780 rows × 5 columns

All of these steps are doable, but none of them are really about finance - they are just the technical scaffolding required before you can work with the actual factor returns. That’s where a dedicated helper or package becomes invaluable. The tidyfinance package performs this entire workflow under the hood: you request a Fama–French dataset and receive a clean, consistently formatted data table from Kenneth French’s Data Library.. This avoids repetitive boilerplate, reduces errors, and lets you focus on modeling and analysis rather than on data plumbing.

Using `tidyfinance` instead of reimplementing the plumbing

import tidyfinance as tf

For example, we can use the tf.download_data() function of the package to download monthly Fama-French factors. The set Fama/French 3 Factors contains the return time series of the market (mkt_excess), size (smb), and value (hml) factors alongside the risk-free rates (risk_free). Note that the tf.download_data() function parses all the columns correctly and already scale them appropriately, as the raw Fama-French data comes in a unique data format. For precise descriptions of the variables, we suggest consulting Prof. Kenneth French’s finance data library directly. If you are on the website, check the raw data files to appreciate the time you can save thanks to the tidyfinance package.

factors_ff3_monthly = tf.download_data(
    domain="famafrench",
    dataset="F-F_Research_Data_Factors",
    start_date=start_date,
    end_date=end_date,
)

We also download the set 5 Factors (2x3), which additionally includes the return time series of the profitability (rmw) and investment (cma) factors. We demonstrate how the monthly factors are constructed in Replicating Fama and French Factors.

factors_ff5_monthly = tf.download_data(
    domain="famafrench",
    dataset="F-F_Research_Data_5_Factors_2x3",
    start_date=start_date,
    end_date=end_date,
)

It is straightforward to download the corresponding daily Fama-French factors with the same function.

factors_ff3_daily = tf.download_data(
    domain="famafrench",
    dataset="F-F_Research_Data_Factors_daily",
    start_date=start_date,
    end_date=end_date,
)

In a subsequent chapter, we also use the monthly returns from ten industry portfolios, so let us fetch that data, too.

industries_ff_monthly = tf.download_data(
    domain="famafrench",
    dataset="10_Industry_Portfolios",
    start_date=start_date,
    end_date=end_date,
)

It is worth taking a look at all available portfolio return time series from Kenneth French’s homepage. You should check out the other sets by calling tf.get_available_famafrench_datasets().

q-Factors

In recent years, the academic discourse experienced the rise of alternative factor models, e.g., in the form of the Hou et al. (2014) q-factor model. We refer to the extended background information provided by the original authors for further information. The q-factors can be downloaded directly from the authors’ homepage from within pd.read_csv().

We also need to adjust this data. First, we discard information we will not use in the remainder of the book. Then, we rename the columns with the “R_”-prescript using regular expressions and write all column names in lowercase. We then query the data to select observations between the start and end dates. Finally, we use the double asterisk (**) notation in the assign function to apply the same transform of dividing by 100 to all four factors by iterating through them. You should always try sticking to a consistent style for naming objects, which we try to illustrate here - the emphasis is on try. You can check out style guides available online, e.g., Hadley Wickham’s tidyverse style guide. note that we temporarily adjust the SSL certificate handling behavior in Python’s ssl module when retrieving the \(q\)-factors directly from the web, as demonstrated in Working with Stock Returns. This method should be used with caution, which is why we restore the default settings immediately after successfully downloading the data.

import ssl

ssl._create_default_https_context = ssl._create_unverified_context

factors_q_monthly_link = (
    "https://global-q.org/uploads/1/2/2/6/122679606/q5_factors_monthly_2024.csv"
)

factors_q_monthly = (
    pd.read_csv(factors_q_monthly_link)
    .assign(
        date=lambda x: (
            pd.to_datetime(x["year"].astype(str) + "-" + x["month"].astype(str) + "-01")
        )
    )
    .drop(columns=["R_F", "R_MKT", "year"])
    .rename(columns=lambda x: x.replace("R_", "").lower())
    .query(f"date >= '{start_date}' and date <= '{end_date}'")
    .assign(**{col: lambda x: x[col] / 100 for col in ["me", "ia", "roe", "eg"]})
)

ssl._create_default_https_context = ssl.create_default_context

Again, you can use the tidyfinance package for a shortcut:

tf.download_data(
    domain="factors_q",
    dataset="q5_factors_monthly",
    start_date=start_date,
    end_date=end_date,
)

Macroeconomic Predictors

Our next data source is a set of macroeconomic variables often used as predictors for the equity premium. Welch and Goyal (2008) comprehensively reexamine the performance of variables suggested by the academic literature to be good predictors of the equity premium. The authors host the data on Amit Goyal’s website. Since the data is an XLSX-file stored on a public Google Drive location, we need additional packages to access the data directly from our Python session. Usually, you need to authenticate if you interact with Google drive directly in Python. Since the data is stored via a public link, we can proceed without any authentication.

sheet_id = "1bM7vCWd3WOt95Sf9qjLPZjoiafgF_8EG"
sheet_name = "macro_predictors.xlsx"
macro_predictors_link = (
    f"https://docs.google.com/spreadsheets/d/{sheet_id}"
    f"/gviz/tq?tqx=out:csv&sheet={sheet_name}"
)

Next, we read in the new data and transform the columns into the variables that we later use:

The dividend price ratio (dp), the difference between the log of dividends and the log of prices, where dividends are 12-month moving sums of dividends paid on the S&P 500 index, and prices are monthly averages of daily closing prices (Campbell and Shiller 1988; Campbell and Yogo 2006).
Dividend yield (dy), the difference between the log of dividends and the log of lagged prices (Ball 1978).
Earnings price ratio (ep), the difference between the log of earnings and the log of prices, where earnings are 12-month moving sums of earnings on the S&P 500 index (Campbell and Shiller 1988).
Dividend payout ratio (de), the difference between the log of dividends and the log of earnings (Lamont 1998).
Stock variance (svar), the sum of squared daily returns on the S&P 500 index (Guo 2006).
Book-to-market ratio (bm), the ratio of book value to market value for the Dow Jones Industrial Average (Kothari and Shanken 1997).
Net equity expansion (ntis), the ratio of 12-month moving sums of net issues by NYSE listed stocks divided by the total end-of-year market capitalization of NYSE stocks (Campbell et al. 2008).
Treasury bills (tbl), the 3-Month Treasury Bill: Secondary Market Rate from the economic research database at the Federal Reserve Bank at St. Louis (Campbell 1987).
Long-term yield (lty), the long-term government bond yield from Ibbotson’s Stocks, Bonds, Bills, and Inflation Yearbook (Welch and Goyal 2008).
Long-term rate of returns (ltr), the long-term government bond returns from Ibbotson’s Stocks, Bonds, Bills, and Inflation Yearbook (Welch and Goyal 2008).
Term spread (tms), the difference between the long-term yield on government bonds and the Treasury bill (Campbell 1987).
Default yield spread (dfy), the difference between BAA and AAA-rated corporate bond yields (Fama and French 1989).
Inflation (infl), the Consumer Price Index (All Urban Consumers) from the Bureau of Labor Statistics (Campbell and Vuolteenaho 2004).

For variable definitions and the required data transformations, you can consult the material on Amit Goyal’s website.

ssl._create_default_https_context = ssl._create_unverified_context

macro_predictors = (
    pd.read_csv(macro_predictors_link, thousands=",")
    .assign(
        date=lambda x: pd.to_datetime(x["yyyymm"], format="%Y%m"),
        dp=lambda x: np.log(x["D12"]) - np.log(x["Index"]),
        dy=lambda x: np.log(x["D12"]) - np.log(x["Index"].shift(1)),
        ep=lambda x: np.log(x["E12"]) - np.log(x["Index"]),
        de=lambda x: np.log(x["D12"]) - np.log(x["E12"]),
        tms=lambda x: x["lty"] - x["tbl"],
        dfy=lambda x: x["BAA"] - x["AAA"],
    )
    .rename(columns={"b/m": "bm"})
    .get(
        [
            "date",
            "dp",
            "dy",
            "ep",
            "de",
            "svar",
            "bm",
            "ntis",
            "tbl",
            "lty",
            "ltr",
            "tms",
            "dfy",
            "infl",
        ]
    )
    .query("date >= @start_date and date <= @end_date")
    .dropna()
)

ssl._create_default_https_context = ssl.create_default_context

To get the equivalent data through tidyfinance, you can call:

tf.download_data(
    domain="macro_predictors",
    dataset="monthly",
    start_date=start_date,
    end_date=end_date,
)

Other Macroeconomic Data

The Federal Reserve bank of St. Louis provides the Federal Reserve Economic Data (FRED), an extensive database for macroeconomic data. In total, there are 817,000 US and international time series from 108 different sources. As an illustration, we use the tidyfinance package to fetch consumer price index (CPI) data that can be found under the CPIAUCNS key.

series = "CPIAUCNS"
url = f"https://fred.stlouisfed.org/graph/fredgraph.csv?id={series}"

We can then use the requests module to request the CSV, extract the data from the response body, and convert the columns to a tidy format:

resp = requests.get(url)
resp_csv = pd.io.common.StringIO(resp.text)

cpi_monthly = (
    pd.read_csv(resp_csv)
    .assign(
        date=lambda x: pd.to_datetime(x["observation_date"]),
        value=lambda x: pd.to_numeric(x[series], errors="coerce"),
        series=series,
    )
    .get(["date", "series", "value"])
    .query("date >= @start_date & date <= @end_date")
    .assign(cpi=lambda x: x["value"] / x["value"].iloc[-1])
)

The last line sets the current (latest) price level as the reference price level.

The tidyfinance package can, of course, also fetch the same index data and many more data series:

tf.download_data(
    domain="fred", series="CPIAUCNS", start_date=start_date, end_date=end_date
)

	date	series	value
0	1960-01-01	CPIAUCNS	29.300
1	1960-02-01	CPIAUCNS	29.400
2	1960-03-01	CPIAUCNS	29.400
3	1960-04-01	CPIAUCNS	29.500
4	1960-05-01	CPIAUCNS	29.500
...	...	...	...
775	2024-08-01	CPIAUCNS	314.796
776	2024-09-01	CPIAUCNS	315.301
777	2024-10-01	CPIAUCNS	315.664
778	2024-11-01	CPIAUCNS	315.493
779	2024-12-01	CPIAUCNS	315.605

780 rows × 3 columns

To download other time series, we just have to look it up on the FRED website and extract the corresponding key from the address. For instance, the producer price index for gold ores can be found under the PCU2122212122210 key. If your desired time series is not supported through tidyfinance, we recommend working with the fredapi package. Note that you need to get an API key to use its functionality. We refer to the package documentation for details.

Setting Up a Database

Now that we have downloaded some (freely available) data from the web into the memory of our Python session let store that information for future use. We will use the data stored throughout the following chapters, but you could alternatively implement a different strategy and replace the respective code.

There are many ways to set up and organize your data, depending on the use case. Storing data as Parquet files creates a universal data format that seamlessly works across different programming languages and platforms. Unlike CSV files that often lose data types when shared between R, Python, Julia, or other languages, Parquet files maintain perfect data integrity regardless of which tool reads them. A dataset saved as Parquet in Python can be opened directly in Python’s pandas, or Julia’s DataFrames.jl.¹

import os

if not os.path.exists("data-python"):
    os.makedirs("data-python")

Next, we create a remote table with the monthly Fama-French factor data. We do so with the pandas function to_pandas(), which stores the data to parquet files.

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

Now, if we want to have the whole table in memory, we need to call pd.read_parquet() with the corresponding query.

pd.read_parquet("data-python/factors_ff3_monthly.parquet")

	date	mkt_excess	smb	hml	risk_free
0	1960-01-01	-0.0698	0.0212	0.0265	0.0033
1	1960-02-01	0.0116	0.0060	-0.0197	0.0029
2	1960-03-01	-0.0163	-0.0055	-0.0275	0.0035
3	1960-04-01	-0.0171	0.0022	-0.0214	0.0019
4	1960-05-01	0.0312	0.0129	-0.0373	0.0027
...	...	...	...	...	...
775	2024-08-01	0.0160	-0.0349	-0.0110	0.0048
776	2024-09-01	0.0172	-0.0013	-0.0277	0.0040
777	2024-10-01	-0.0100	-0.0099	0.0086	0.0039
778	2024-11-01	0.0649	0.0446	0.0015	0.0040
779	2024-12-01	-0.0317	-0.0271	-0.0300	0.0037

780 rows × 5 columns

The last couple of code chunks are really all there is to organizing a simple database! You can also share the parquet files across devices and programming languages.

Before we move on to the next data source, let us also store the other six tables in our new database.

data_dict = {
    "factors_ff5_monthly": factors_ff5_monthly,
    "factors_ff3_daily": factors_ff3_daily,
    "industries_ff_monthly": industries_ff_monthly,
    "factors_q_monthly": factors_q_monthly,
    "macro_predictors": macro_predictors,
    "cpi_monthly": cpi_monthly,
}

for key, value in data_dict.items():
    value.to_parquet("data-python/" + key + ".parquet")

Key Takeaways

Importing Fama-French factors, q-factors, macroeconomic indicators, and CPI data is simplified through API calls, CSV parsing, and web scraping techniques.
The tidyfinance Python package offers pre-processed access to financial datasets, reducing manual data cleaning and saving valuable time.
Creating a centralized SQLite database helps manage and organize data efficiently across projects, while maintaining reproducibility.
Structured database storage supports scalable data access, which is essential for long-term academic projects and collaborative work in finance.

Exercises

Download the monthly Fama-French factors manually from Kenneth French’s data library and read them in via pd.read_csv(). Validate that you get the same data as via the tf.download_data() package.
Download the daily Fama-French 5 factors using the tf.download_data() function. After the successful download and conversion to the column format that we used above, compare the risk_free, mkt_excess, smb, and hml columns of factors_ff3_daily to factors_ff5_daily. Discuss any differences you might find.

References

Ball, Ray. 1978. “Anomalies in relationships between securities’ yields and yield-surrogates.” Journal of Financial Economics 6 (2–3): 103–26. https://doi.org/10.1016/0304-405X(78)90026-0.

Campbell, John Y. 1987. “Stock returns and the term structure.” Journal of Financial Economics 18 (2): 373–99. https://doi.org/10.1016/0304-405X(87)90045-6.

Campbell, John Y., Jens Hilscher, and Jan Szilagyi. 2008. “In search of distress risk.” The Journal of Finance 63 (6): 2899–939. https://doi.org/10.1111/j.1540-6261.2008.01416.x.

Campbell, John Y., and Robert J. Shiller. 1988. “Stock prices, earnings, and expected dividends.” The Journal of Finance 43 (3): 661–76. https://doi.org/10.1111/j.1540-6261.1988.tb04598.x.

Campbell, John Y., and Tuomo Vuolteenaho. 2004. “Inflation illusion and stock prices.” American Economic Review 94 (2): 19–23. https://www.aeaweb.org/articles?id=10.1257/0002828041301533.

Campbell, John Y., and Motohiro Yogo. 2006. “Efficient tests of stock return predictability.” Journal of Financial Economics 81 (1): 27–60. https://doi.org/10.1016/j.jfineco.2005.05.008.

Fama, Eugene F., and Kenneth R. French. 1989. “Business conditions and expected returns on stocks and bonds.” Journal of Financial Economics 25 (1): 23–49. https://doi.org/10.1016/0304-405X(89)90095-0.

Fama, Eugene F., and Kenneth R. French. 1993. “Common risk factors in the returns on stocks and bonds.” Journal of Financial Economics 33 (1): 3–56. https://doi.org/10.1016/0304-405X(93)90023-5.

Guo, Hui. 2006. “On the out-of-sample predictability of stock market returns.” The Journal of Business 79 (2): 645–70. https://doi.org/10.1086/499134.

Hou, Kewei, Chen Xue, and Lu Zhang. 2014. “Digesting anomalies: An investment approach.” Review of Financial Studies 28 (3): 650–705. https://doi.org/10.1093/rfs/hhu068.

Kothari, S. P., and Jay A. Shanken. 1997. “Book-to-market, dividend yield, and expected market returns: A time-series analysis.” Journal of Financial Economics 44 (2): 169–203. https://doi.org/10.1016/S0304-405X(97)00002-0.

Lamont, Owen. 1998. “Earnings and expected returns.” The Journal of Finance 53 (5): 1563–87. https://doi.org/10.1111/0022-1082.00065.

Welch, Ivo, and Amit Goyal. 2008. “A comprehensive look at the empirical performance of equity premium prediction.” Review of Financial Studies 21 (4): 1455–508. https://doi.org/10.1093/rfs/hhm014.

Footnotes

In previous editions of this book, we suggested using SQLite databases. However, Parquet files are more versatile and easier to handle across different programming languages. Should you still prefer using SQLite, you can find the relevant code snippets in a separate blog post.↩︎

Fama-French Data

From manual steps to a download script

Using tidyfinance instead of reimplementing the plumbing

q-Factors

Macroeconomic Predictors

Other Macroeconomic Data

Setting Up a Database

Key Takeaways

Exercises

References

Footnotes

Using `tidyfinance` instead of reimplementing the plumbing