Warren Buffet is a legend.

From 1965 to 2023, his company Berkshire Hathaway has delivered compounded annual gains of 19.8%. This outperforms the S&P 500's 10.2% annual return during the same period.

Thousands of investors attempt to replicate his results.

The easy way to start is to screen stocks that Warren considers “cheap.”

Today, we’ll use Financial Modeling Prep (FMP) and ArcticDB to download and store 197,291 rows of historic financial ratios.

We’ll then use ArcticDB’s Query Builder to screen for stocks Warren might like.

To follow along, you’ll need an FMP API key. You can sign up with a great discount here.

Let’s go!

How to quickly save 197,291 historic financial ratios

Value investing relies on using financial ratios to identify undervalued stocks. These are referred to as "value stocks." The goal is to achieve long-term returns by purchasing them at a discount.

This approach uses fundamental analysis. This approach focuses on ratios like price-to-earnings (P/E), price-to-book (P/B), and dividend yield to assess whether a stock is trading below its intrinsic value.

In practice, value investors like Warren gather historical financial data, standardize it for comparison across various companies and time periods, and integrate these ratios into screening models. The process involves analyzing financial statements to uncover stocks that are overlooked by the market.

Professionals can use financial ratios to look for value stocks with potential for future growth. Lucky for us, we have Python to do the hard work for us.

Let’s see how it works.

Set up the ArcticDB connection and helper functions

First, we import our libraries, establish a connection to the ArcticDB database, and define a helper function to construct API URLs.

1import io
2import os
3import time
4import requests
5import pandas as pd
6import arcticdb as adb
7
8
9arctic = adb.Arctic("lmdb://fundamantals")
10lib = arctic.get_library("financial_ratios", create_if_missing=True)
11
12def build_fmp_url(request, period, year):
13    apikey = os.environ.get("FMP_API_KEY")
14    return f"https://financialmodelingprep.com/api/v4/{request}?year={year}&period={period}&apikey={apikey}"

We initiate a connection to the ArcticDB, a DataFrame database designed for handling time-series data. We specify the database location using a URL and connect to a library within the database called "financial_ratios".

The helper function build_fmp_url constructs URLs for API requests to FMP. It uses environmental variables to retrieve the API key so make sure you set an environment variable with your key.

Retrieve financial data from the API

Next, we define a function that retrieves financial data from the FMP API and converts it into a pandas DataFrame.1def get_fmp_data(request, period, year):

def get_fmp_data(request, period, year):
    url = build_fmp_url(request, period, year)
    response = requests.get(url)
    csv = response.content.decode("utf-8")
    return pd.read_csv(io.StringIO(csv), parse_dates=True)

ratios = get_fmp_data("ratios-bulk", "quarter", "2020")

This function fetches data from the FMP API using the URL constructed by build_fmp_url. It sends a GET request to the API and receives a CSV formatted response containing financial ratios for companies.

The CSV data is decoded and then read into a pandas DataFrame for easier manipulation and analysis. The function is called with specific parameters to retrieve data for the year 2020, and the resulting DataFrame is stored in the ratios variable.

As of the time of writing, this call returns 197,291 rows of data.

Store financial data in ArcticDB

Now, we iterate over multiple years to store their financial ratios into the ArcticDB.

1for year in [2020, 2021, 2022]:
2    ratios = get_fmp_data("ratios-bulk", "quarter", year)
3    adb_sym = f"financial_ratios/{year}"
4    adb_fcn = lib.update if lib.has_symbol(adb_sym) else lib.write
5    adb_fcn(adb_sym, ratios)
6    time.sleep(3)

We loop over a list of years from 2020 to 2022, retrieving financial data for each year using the get_fmp_data function. For each year, we construct a unique symbol name to store the data in ArcticDB.

We check if the symbol already exists in the library. If it does, we update it with the new data, otherwise, we write a new entry. We wait for 3 seconds between API requests to avoid hitting rate limits.

Filter and query financial data

Finally, we define a function to filter financial data based on Warren’s criteria and return the results as a pandas DataFrame.

1def filter_by_year(year):
2    # Identify the columns to return
3    cols = [
4        "symbol",
5        "period",
6        "date",
7        "debtEquityRatio", 
8        "currentRatio", 
9        "priceToBookRatio", 
10        "returnOnEquity", 
11        "returnOnAssets", 
12        "interestCoverage"
13    ]
14    
15    # Build the ArcticDB query
16    q = adb.QueryBuilder()
17    filter = (
18        (q["debtEquityRatio"] < 0.5)
19        & (
20            (q["currentRatio"] > 1.5) & (q["currentRatio"] < 2.5)
21        )
22        & (q["priceToBookRatio"] < 1.5)
23        & (q["returnOnEquity"] > 0.08)
24        & (q["returnOnAssets"] > 0.06)
25        & (q["interestCoverage"] > 5)
26    )
27    q = q[filter]
28    
29    # Filter and return the results
30    return lib.read(
31        f"financial_ratios/{year}", 
32        query_builder=q
33    ).data[cols].set_index("symbol")

This function filters financial data for a specified year based on certain financial metrics. It selects a subset of columns to focus on key financial ratios. The QueryBuilder is used to construct a query that filters data based on predefined criteria, such as debt-to-equity ratio and return on equity.

The data is read from ArcticDB using this query, and the results are returned as a pandas DataFrame with the company symbol as the index, allowing easy access and analysis.

Your next steps

Try changing the filtering criteria to explore different financial metrics. For example, adjust the debt-to-equity ratio threshold to see how it impacts the results. Experiment with different years to compare financial health across time. Exploring these variations will help you gain a deeper understanding of the financial data.