Cache API Responses When Building Excel Reports

A report that pulls its data from an API is slow to develop. Every time you change a column width or fix a label, the script fetches thousands of records again, waits on rate limits, and sometimes fails halfway because the service hiccuped. In production the same thing happens on a smaller scale: several reports call the same endpoint for the same data within minutes of each other. A cache solves both. During development it makes reruns instant and offline; in production it removes duplicate calls and makes reruns after a failure cheap. The danger is the obvious one — a report built from stale data without anyone knowing. This page adds a cache with explicit expiry rules, shows a hand-rolled version for requests that are not simple GETs, and makes the workbook state how old its data is. It belongs to Moving Data Between Excel and Databases.

Prerequisites

pip install requests requests-cache pandas openpyxl

The examples call three endpoints of a sales API: exchange rates, monthly sales per region, and a stock query sent as a POST with a JSON body. They run against a small local stand-in for the API (address in BASE) that counts how many requests actually reach it — the number that caching is meant to reduce. The examples were run with requests-cache 1.3.

Step 1 — Measure the problem

A typical report script calls the API in several places, often for the same data. Count the requests it makes:

import requests

def build_report(session):
    rates = session.get(f"{BASE}/rates", timeout=30).json()["rates"]
    frames = {}
    for region in ["North", "South", "East", "North"]:          # North requested twice
        frames[region] = session.get(f"{BASE}/sales", params={"region": region}, timeout=30).json()["rows"]
    return rates, frames

start = hits["n"]
build_report(requests.Session())
build_report(requests.Session())                                 # a rerun after changing a format
print("requests reaching the API:", hits["n"] - start)

Two runs of a small script made ten requests, two of them duplicates within a single run. Scale that to hundreds of regions or products and a few dozen development reruns and the API sees thousands of calls — which means slow development, rate-limit errors, and, for paid APIs, real cost.

Step 2 — A transparent cache with requests-cache

requests_cache.CachedSession is a drop-in replacement for requests.Session that stores responses in a local SQLite file and answers repeat requests from it. Expiry can be set per URL pattern:

import requests_cache
from datetime import timedelta

cached = requests_cache.CachedSession(
    "report_cache",                              # report_cache.sqlite next to the script
    expire_after=timedelta(hours=1),             # default for everything
    urls_expire_after={
        f"{BASE}/rates": timedelta(days=1),      # rates change daily
        f"{BASE}/sales*": timedelta(minutes=15),
    },
    allowable_codes=(200,),                      # never cache errors
    allowable_methods=("GET",),
)
cached.cache.clear()

start = hits["n"]
build_report(cached)
first = hits["n"] - start
build_report(cached)
print("first run:", first, "requests | second run:", hits["n"] - start - first, "requests")
r = cached.get(f"{BASE}/rates")
print("from cache:", r.from_cache, "| expires:", r.expires is not None)

The first run makes four requests — the duplicate North call within the run is already served from the cache — and the second run makes none: everything comes from report_cache.sqlite. urls_expire_after gives each endpoint an expiry that matches how often its data changes: exchange rates for a day, sales for fifteen minutes, everything else for an hour. allowable_codes=(200,) is important: without it, a cached 503 error could be served back on the next run and make a temporary outage look permanent. Every response carries from_cache and expires, which the report uses in Step 5.

The saving grows with the number of reruns: a morning spent adjusting a report's layout might run the script thirty times, and with the cache only the first run touches the API. It also makes the script usable offline — on a train, or while the API is down for maintenance — as long as the cached responses have not expired.

Step 3 — Errors are not cached

Make sure a temporary failure does not get stuck in the cache. Request a failing endpoint twice:

start = hits["n"]
for _ in range(2):
    resp = cached.get(f"{BASE}/sales", params={"region": "broken"})
    print(resp.status_code, "from cache:", resp.from_cache)
print("requests reaching the API:", hits["n"] - start)

Both requests reach the API and neither is served from the cache, because only 200 responses are stored. The next run, after the service recovers, will fetch real data. Combine this with retries for transient errors — the session from Fetch Paginated API Data into Excel handles 429 and 5xx — and the cache never has to deal with failures at all.

Step 4 — A hand-rolled cache for POST queries

Some APIs take their query as a POST body, which HTTP caches deliberately do not cache. For those, a small file cache keyed by a hash of the request is easy to write and easy to reason about:

import hashlib
import json
import time
from pathlib import Path

CACHE_DIR = Path("api_cache")
CACHE_DIR.mkdir(exist_ok=True)

def cached_post(url, payload, max_age_s=900, refresh=False):
    key = hashlib.sha256(json.dumps([url, payload], sort_keys=True).encode()).hexdigest()[:16]
    path = CACHE_DIR / f"{key}.json"
    if path.exists() and not refresh and time.time() - path.stat().st_mtime < max_age_s:
        return json.loads(path.read_text()), True
    resp = requests.post(url, json=payload, timeout=30)
    resp.raise_for_status()
    path.write_text(resp.text)
    return resp.json(), False

start = hits["n"]
q = {"skus": ["DRL-01", "SAW-02"], "warehouse": "LDS"}
print([cached_post(f"{BASE}/stock", q)[1] for _ in range(3)], "| requests:", hits["n"] - start)
same_q_reordered = {"warehouse": "LDS", "skus": ["DRL-01", "SAW-02"]}
print("reordered keys hit the cache:", cached_post(f"{BASE}/stock", same_q_reordered)[1])

The key is a hash of the URL and the payload serialised with sort_keys=True, so the same query with its keys in a different order hits the same cache entry; the first of the three calls goes to the API, the next two are answered from disk, and the reordered query is a cache hit too — one request in total. Freshness is the file's age compared with max_age_s, and refresh=True bypasses the cache. Only successful responses are written, because raise_for_status() raises before the file is saved. Keep the cache directory out of version control — it can contain business data — by adding it to .gitignore.

Step 5 — Force fresh data for production, and say how old it is

Caching is for development and for repeat calls; the production run should usually start fresh and the workbook should say when its data was fetched. requests-cache can bypass the cache on demand, and the response tells you whether it came from the cache:

import os
from datetime import datetime, timezone
import pandas as pd

FRESH = os.environ.get("REPORT_FRESH") == "1"
session = requests_cache.CachedSession("report_cache", expire_after=timedelta(hours=1), allowable_codes=(200,))

resp = session.get(f"{BASE}/sales", params={"region": "North"}, force_refresh=FRESH)
sales = pd.DataFrame(resp.json()["rows"])
fetched = resp.created_at if resp.from_cache else datetime.now(timezone.utc)
note = f"Data fetched {fetched:%Y-%m-%d %H:%M} UTC" + (" (from cache)" if resp.from_cache else "")
with pd.ExcelWriter("north_sales.xlsx", engine="openpyxl") as xw:
    sales.to_excel(xw, sheet_name="Sales", index=False, startrow=2)
    xw.sheets["Sales"]["A1"] = note
print(note)

Setting REPORT_FRESH=1 in the production scheduler makes every request skip the cache with force_refresh=True — and still stores the new responses for any later call in the same run. In development the variable is unset and the cache does its job. Either way the first line of the sheet records when the data was fetched and whether it came from the cache, so nobody mistakes a development workbook for a current report. Keeping such switches in configuration rather than code is covered in Keep Excel Report Settings in a Config File.

Common pitfalls

• Caching errors. Restrict caching to 200 responses.
• One expiry for everything. Match expiry to how often each endpoint's data changes.
• Stale data in production without a trace. Force refresh for scheduled runs and record the fetch time in the workbook.
• Cache keys that depend on dictionary order. Serialise payloads with sort_keys=True.
• Committing the cache. It may hold business data; ignore it in version control.

Conclusion

Caching API responses makes Excel report development fast and production reruns cheap. requests_cache.CachedSession adds a transparent SQLite-backed cache with expiry per URL pattern; restrict it to successful GET responses. For POST queries, a small file cache keyed by a hash of the URL and a sorted-key payload does the same job. Force fresh requests for production runs with a configuration flag, and write the fetch time — and whether it came from the cache — into the workbook so readers know how current the figures are.

Frequently asked questions

How do I cache API calls in a Python report script? Replace requests.Session with requests_cache.CachedSession, which stores responses in a local SQLite file and serves repeat requests from it until they expire.

How do I set different cache times for different endpoints? Pass urls_expire_after to CachedSession with URL patterns mapped to expiry times, for example a day for exchange rates and fifteen minutes for sales data.

Can requests-cache cache POST requests? Not by default, because POST requests are not normally cacheable. Write a small file cache keyed by a hash of the URL and the JSON payload instead.

How do I make sure a scheduled report uses fresh data? Pass force_refresh=True for production runs, controlled by an environment variable or config setting, and write the fetch time into the workbook.

Related

• Up one level: Moving Data Between Excel and Databases — getting data in and out.
• Fetch API Data into Excel with Python requests — the requests that get cached.
• Skip a Report When the Input File Has Not Changed — avoiding work at the report level.
• Store Credentials for Excel Report Scripts Safely — the API keys behind the requests.