Automate a historical liquidity ratio analysis with Python

This optional Python guide continues the site’s original practical finance approach. You will read account balances, calculate three ratios, export the results, and plot them. If you prefer a no-code start, begin with Analyze financial statements with AI assistance instead.

Originally published 14 August 2020. The refreshed Python workflow was executed on 10 October 2026. It uses historical data, not current Apple analysis. Earlier dataset rows have not been individually reconciled to filings; a successful code run does not establish their source accuracy.

1. Understand the data and formulas

The historical case-study repository supplies apple.csv. It contains 11 fiscal-end observations, 2009–2019, despite the original description referring to ten years. The last observation is 28 September 2019. Amounts are USD millions for the checked filing rows; ratios are scale-independent when all inputs use matching units.

The 2017/2018 values match the consolidated balance sheet in Apple’s 2018 Form 10-K. The 2018/2019 values match Apple’s 2019 Form 10-K. Earlier rows remain historical repository transcriptions pending individual filing reconciliation; do not treat the entire series as fully source-verified.

Current ratio = Current assets / Current liabilities
Quick ratio in this exercise = (Current assets - Inventories) / Current liabilities
Cash ratio = Combined cash and cash equivalents / Current liabilities

The quick ratio retains the original inventory-only exclusion. Other current assets may also be nonquick; this dataset cannot establish a stricter quick-assets definition. The cash ratio excludes marketable securities outside the supplied combined cash/equivalents amount. Do not add cash equivalents a second time.

The CFI financial-ratio overview provides background on liquidity ratios. A ratio does not, by itself, establish the timing or certainty of payment capacity.

2. Prepare the files and environment

Use the revision 2.0 learning kit for the refreshed instructions. Keep financialratios.py, liquidity_analysis.py, apple.csv, and requirements.txt in one folder. The original repositories remain historical references; their code has not been updated remotely.

Tested runtime: Python 3.12.14; pandas 3.0.1; Matplotlib 3.11.2; openpyxl 3.1.5. These are tested versions rather than a latest-version recommendation. Completion time has not been measured in a reader pilot.

Windows PowerShell, from that folder:

python -m venv .venv
.\.venv\Scripts\python.exe -m pip install -r requirements.txt
.\.venv\Scripts\python.exe -m unittest -v
.\.venv\Scripts\python.exe liquidity_analysis.py

On macOS/Linux use python3 -m venv .venv and ./.venv/bin/python in place of the Windows environment interpreter path. The Python venv documentation explains isolated environments and running their interpreter directly. Clean-machine setup remains a reader check; the code run used the recorded bundled runtime.

3. Run a complete script

Save the following as liquidity_analysis.py. The companion financialratios.py is supplied in the kit and shown in the module guide.

"""Reproduce a historical CSV analysis; never fetch data or execute model code."""
from pathlib import Path
import argparse
import pandas as pd
import matplotlib
matplotlib.use("Agg")
import matplotlib.pyplot as plt
import financialratios as fr

ACCOUNTS = ["current assets", "current liabilities", "inventories", "cash & cash equivalent"]


def calculate(source):
    data = pd.read_csv(source)
    required = ["Date", *ACCOUNTS]
    if list(data.columns) != required:
        raise ValueError(f"Expected columns in this order: {required}")
    if data.empty:
        raise ValueError("Input has no records.")
    data["Date"] = pd.to_datetime(data["Date"], format="%m/%d/%Y", errors="raise")
    if data["Date"].isna().any() or data["Date"].duplicated().any():
        raise ValueError("Missing or duplicate fiscal-end dates.")
    for col in ACCOUNTS:
        data[col] = pd.to_numeric(data[col], errors="raise")
        if not data[col].map(lambda v: pd.notna(v) and abs(v) != float("inf")).all():
            raise ValueError(f"Missing or nonfinite amount: {col}")
    if (data[ACCOUNTS] < 0).any().any() or (data["current liabilities"] <= 0).any():
        raise ValueError("Require nonnegative assets and positive current liabilities.")
    if (data["inventories"] > data["current assets"]).any() or (data["cash & cash equivalent"] > data["current assets"]).any():
        raise ValueError("Inventory or cash exceeds current assets; check inputs.")
    data = data.sort_values("Date").set_index("Date")
    ratios = pd.DataFrame(index=data.index)
    ratios["Current ratio"] = [fr.get_current_ratio(a,l) for a,l in zip(data["current assets"],data["current liabilities"])]
    ratios["Quick ratio (inventory-only exclusion)"] = [fr.get_quick_ratio(a,i,l) for a,i,l in zip(data["current assets"],data["inventories"],data["current liabilities"])]
    ratios["Cash ratio"] = [fr.get_cash_ratio(c,l) for c,l in zip(data["cash & cash equivalent"],data["current liabilities"])]
    return data, ratios


def run(source, output):
    data, ratios = calculate(source)
    output = Path(output)
    output.mkdir(parents=True, exist_ok=True)
    ratios.to_csv(output / "liquidity-ratios.csv", date_format="%Y-%m-%d", float_format="%.10f")
    with pd.ExcelWriter(output / "liquidity-ratios.xlsx", engine="openpyxl") as writer:
        ratios.to_excel(writer, sheet_name="Ratios")
        data.to_excel(writer, sheet_name="Inputs")
    plt.rcParams.update({"font.family":"Arial", "font.size":11})
    fig, axes = plt.subplots(3,1,figsize=(10,9),sharex=True)
    for ax, col in zip(axes, ratios.columns):
        ax.plot(ratios.index.year, ratios[col], marker="o", color="#256349")
        ax.set_title(col,loc="left",fontsize=12)
        ax.set_ylabel("Ratio (times)")
        ax.grid(axis="y",alpha=.2)
    axes[-1].set_xticks(ratios.index.year)
    axes[-1].set_xlabel("Fiscal-end year")
    fig.suptitle("Apple historical teaching dataset · 2009–2019",fontsize=15)
    fig.text(.05,.015,"Source: legacy repository CSV. Inventory-only quick ratio. No current industry benchmark.",fontsize=10)
    fig.tight_layout(rect=(0,.04,1,.95))
    fig.savefig(output / "liquidity-ratios.png",dpi=140)
    plt.close(fig)
    print(ratios.tail(3).to_string(float_format=lambda v:f"{v:.4f}"))
    return ratios


if __name__ == "__main__":
    parser=argparse.ArgumentParser(description=__doc__)
    parser.add_argument("--input",type=Path,default=Path(__file__).with_name("apple.csv"))
    parser.add_argument("--output",type=Path,default=Path(__file__).with_name("results"))
    args=parser.parse_args()
    run(args.input,args.output)

The script uses pandas.read_csv, an explicit date format, and checks before calculating. It rejects missing amounts, duplicate dates, invalid numerics, negative account balances, nonpositive current liabilities, and cash/inventory exceeding current assets. It sorts dates before plotting. The module functions take scalar values, so the script calls them once per row instead of passing a whole pandas Series.

The script saves a CSV, an XLSX with Ratios and Inputs sheets, and one PNG containing three plots. The Excel values are a snapshot of the run, not formulas: rerun Python after changing the CSV. Repeated runs replace those output files in the selected results folder. Matplotlib savefig saves the plots without requiring an interactive window.

4. Check the executed results

Actual results from the refreshed code, rounded here to four decimals:

For the last row, independently calculate 162,819 / 105,718, (162,819 − 4,106) / 105,718, and 48,844 / 105,718. Display rounding does not change stored calculation precision. Confirm the exported table contains 11 rows and keeps the fiscal-end dates.

Generated from the historical repository CSV using the refreshed script. No old screenshots were reused. Ratios are in times; the x-axis is fiscal-end year. Earlier source transcriptions remain subject to the limitation above.

The old 2020 industry comparison lines have been removed. A current comparison would require a dated, documented peer group, matching periods, and the same ratio definitions.

5. Optional AI-assisted interpretation

After checking the calculation, paste the result table and definitions into:

Help draft an educational description of these historical liquidity ratios.
[Paste the table and formula conventions above.]
These are 2017–2019 observations, not current results. The quick ratio excludes
inventory only; cash/equivalents are already combined. Use only supplied values.
Describe changes between years and list missing evidence. Do not infer causes,
industry superiority, investment attractiveness, or present-day payment ability.
Return under 100 words and label any proposed investigation as a question.

Illustrative editorial interpretation: All three displayed ratios rose between 2018 and 2019. The table alone does not identify the causes or establish current liquidity. Review asset composition, liability maturities, and cash flows before extending the interpretation. This paragraph is not a captured AI-service response.

Recover and reuse

If a library import fails, check that installation and execution use the same interpreter. If an input check fails, correct or investigate the source rather than replacing a missing value with zero. If the chart includes the wrong dates, inspect the CSV and retain actual fiscal ends.

For a practice copy, increase a current-liability value and rerun; all three ratios for that row should decrease, with other rows unchanged. For current analysis, obtain new source-checked balances and update period, units, notes, and chart labels together. Continue with the module guide to understand how the functions are reused.