AI/Fin Research
Guide

Make your data pull something an agent can run

Automation starts with the stage that has the clearest pass or fail. Turn the data pull into one command with checks and a manifest.

AI Fin ResearchStarter4 min read

Much of a research project goes into steps that are tedious and checkable. The data pull is the one to automate first, because it either returns the right rows or it does not. Once the pull runs from a single command and verifies itself, an AI coding agent can run it, rerun it with new dates and repair it when a table changes, and you can see whether it did the job.

This guide uses CRSP monthly returns from WRDS as the example. The pattern is the same for any source you query with code.

1. One command, no clicks

If your pull involves a web form, a notebook cell you run by hand or a file you rename afterward, an agent cannot repeat it and neither can a coauthor. The target is this:

pip install wrds pandas pyarrow
python pull.py --start 2000-01-01 --end 2023-12-31

2. Keep the query in its own file

-- sql/crsp_msf.sql
select permno, date, ret, prc, shrout
from crsp.msf
where date between %(start)s and %(end)s

A query in a file can be diffed, reviewed and hashed. Table and column names depend on which CRSP format your school subscribes to, so check them against your own library list.

3. Write the pull script

# pull.py
import argparse
import hashlib
import json
from datetime import datetime, timezone
from pathlib import Path

import pandas as pd
import wrds

SQL_FILE = Path("sql/crsp_msf.sql")
OUT_DIR = Path("data/raw")


def main() -> None:
    parser = argparse.ArgumentParser()
    parser.add_argument("--start", required=True)
    parser.add_argument("--end", required=True)
    args = parser.parse_args()

    sql = SQL_FILE.read_text()
    db = wrds.Connection()  # reads credentials from ~/.pgpass
    df = db.raw_sql(sql, params={"start": args.start, "end": args.end}, date_cols=["date"])
    db.close()

    check(df, args.start, args.end)

    OUT_DIR.mkdir(parents=True, exist_ok=True)
    df.to_parquet(OUT_DIR / "crsp_msf.parquet", index=False)

    manifest = {
        "query_file": str(SQL_FILE),
        "query_sha256": hashlib.sha256(sql.encode()).hexdigest(),
        "start": args.start,
        "end": args.end,
        "rows": len(df),
        "permnos": int(df["permno"].nunique()),
        "pulled_at": datetime.now(timezone.utc).isoformat(timespec="seconds"),
    }
    (OUT_DIR / "crsp_msf.manifest.json").write_text(json.dumps(manifest, indent=2))
    print(json.dumps(manifest, indent=2))


if __name__ == "__main__":
    main()

The manifest is the receipt. It records which query ran, over which dates, and what came back.

4. Add checks that fail loudly

Put this above main in the same file.

def check(df: pd.DataFrame, start: str, end: str) -> None:
    assert len(df) > 0, "pull returned no rows"
    assert not df.duplicated(["permno", "date"]).any(), "duplicate permno-date rows"
    assert df["date"].min() >= pd.Timestamp(start), "rows before the start date"
    assert df["date"].max() <= pd.Timestamp(end), "rows after the end date"
    assert df["ret"].notna().mean() > 0.9, "more than 10% of returns are missing"

An agent that breaks the query now gets a failed assertion instead of a quietly wrong dataset. Write the checks from what you know about the data: one row per security per month, dates inside the window, returns mostly present. When a check fails for a legitimate reason, change the check on purpose and say why in the commit.

5. Keep credentials and data away from the model

  • Credentials live in ~/.pgpass, which the wrds package can create for you with db.create_pgpass_file(). They never go in the repo, a prompt or a task file.
  • Raw data stays out of version control. Add data/ to .gitignore.
  • Vendor data is licensed. The agent needs your schema, your checks and the manifest, not the rows. Read your school’s data agreements before any licensed data goes to a hosted model.

6. Write the task file

An agent works from what you write down. Put the contract in a file at the top of the repo.

# Task: CRSP monthly pull

Goal: data/raw/crsp_msf.parquet covering the requested date range.

Run: python pull.py --start YYYY-MM-DD --end YYYY-MM-DD
Done when: the script exits 0 and prints a manifest.

You may edit: pull.py, sql/crsp_msf.sql
Do not: print or upload rows from data/, change a check to make it pass, touch credentials.
If a check fails: report the failure and the row counts. Do not work around it.

7. Hand it over, then read the manifest

Ask the agent to extend the pull to a new date range or add a column. Review two things: the diff to the SQL file and the manifest. If the query hash changed, the query changed. If the row count moved by more than the new months explain, ask why.

Next stage

The same pattern carries to cleaning and merging: one command, a file that defines the rule, checks that encode what you know, and a manifest that says what ran. Novy-Marx and Velikov’s working paper on generating finance papers automatically shows how far this can be pushed. You do not need to go that far to get the benefit. A pipeline an agent can rerun is also one a replicator can rerun.

References