Self-hosted stock research agent on a VPS

What a self-hosted stock research agent is

A self-hosted stock research agent is a small program on a server you own that pulls market data on a schedule, keeps it in a local database, runs a screen over it, and asks a large language model (LLM) to write up what changed. It reads and it filters. It does not trade, and nothing in this guide is financial advice.

Two people building this from scratch will pick different libraries and still end up with the same four parts: a feed that supplies prices and fundamentals, a local store that keeps every row you have ever fetched, a job that refreshes the store on a timer, and an LLM layer that turns the surviving rows into sentences. This guide builds that shape out of Python, DuckDB, a systemd timer and the Claude API (application programming interface). Execution is a separate job with separate failure modes, and it belongs on a VPS set up for trading bots instead.

The four parts, and what each one does

The feed is the only part that talks to the outside world. It knows how to ask for a ticker and a date range, and how to hand back rows. Everything downstream reads your database rather than the feed, so a feed outage costs you one day of new data instead of a broken screen.

The store is the point of the whole exercise. A daily close you did not record can usually be fetched again later. An intraday quote, an estimate before it was revised, or a fundamentals figure before it was restated cannot. The store is how you build a record of what the data actually said on the day it said it.

The scheduler decides when the refresh happens. On a server that is a systemd timer, which is the reason the VPS matters more here than the code does.

The LLM layer reads a short text block that your SQL produced, and writes a summary of it. It never connects to the database and it never builds the query. If the model writes the SQL, one wrong token becomes a wrong number inside a fluent sentence, with nothing to compare it against. If SQL produces the numbers, the model can only get the prose wrong, and you can check the prose against the rows you sent.

Why run it on a VPS instead of a laptop

The scheduler is the reason. A US market close at 16:00 New York time is 22:00 in Berlin and 04:00 the next morning in Jakarta. A laptop is asleep at both. A missed run costs more than a late note: daily bars can usually be refetched later, but anything that gets revised cannot, so the gap in your record is permanent.

The second reason is smaller and still real. The server holds one API key, in one file, owned by one system user with no login shell, used by one job. That is much harder to arrange on the laptop you also browse the web with. Keep the key out of the code and out of the model's input, which is the subject of keeping API keys out of an AI agent.

Sizing it: disk, RAM and tokens

Disk is the easy part. One daily bar is one row per ticker per trading day, and a US trading year is about 252 days.

[
  {
    "label": "20 tickers",
    "rows_after_1y": "5,040",
    "rows_after_10y": "50,400"
  },
  {
    "label": "100 tickers",
    "rows_after_1y": "25,200",
    "rows_after_10y": "252,000"
  },
  {
    "label": "500 tickers",
    "rows_after_1y": "126,000",
    "rows_after_10y": "1,260,000"
  }
]

Twenty tickers is 5,040 rows after a year. The 500 tickers line reaches 1,260,000 rows after ten years. Each row is a date and a handful of doubles, and DuckDB stores columns compressed, so this is tens of megabytes rather than gigabytes. Do not trust that estimate, including mine. Run du -h /opt/research/data/market.duckdb after your first backfill and use your own number.

RAM is where a small VPS bites. DuckDB by default takes a large share of the machine's memory and all of its cores for a single query, which is right on an analytics server and wrong on a 2 GB box that also runs other things. One aggregation over the whole prices table then gets the process killed by the kernel, and systemd reports Main process exited, code=killed, status=9/KILL while journalctl -k shows the out of memory kill. Set memory_limit and threads explicitly and the query gets slower instead of dying.

Tokens should be measured, not estimated. Every Messages API response carries a usage object with input_tokens and output_tokens. Write both into a table on every call, and after a week you know your real volume, which you multiply by whatever your model lists on the day you check. Two things are stable enough to plan around. Output tokens are priced above input tokens on every Claude model, so capping the note at 200 words moves the bill more than trimming the data you send. And prompt caching does not help a once a day job, because the cache lifetime is measured in minutes: by the next run the cached block has expired and you pay the full input price again. Caching pays when one run makes many calls over the same large block of text.

Install the pieces

sudo apt update
sudo apt install -y python3-venv
sudo useradd --system --create-home --home-dir /opt/research --shell /usr/sbin/nologin research
sudo -u research python3 -m venv /opt/research/venv
sudo -u research /opt/research/venv/bin/pip install duckdb pandas yfinance anthropic
sudo install -d -o research -g research -m 750 /opt/research/data

Check the install before you write any code:

sudo -u research /opt/research/venv/bin/python -c 'import duckdb, yfinance, anthropic; print("ok")'

That prints ok. If you skipped the virtual environment and ran pip install against the system Python, Ubuntu 24.04 stops you with error: externally-managed-environment, because the distribution owns /usr/lib/python3 and refuses to let pip write there. The venv is not politeness. It is the only directory pip is allowed to touch.

The API key goes in a file the service user can read and nobody else can:

sudo install -d -m 755 /etc/research
sudo install -m 640 -o root -g research /dev/null /etc/research/env
sudoedit /etc/research/env

Put one line in it, with no quotes and no export, because systemd parses this file itself instead of passing it to a shell:

ANTHROPIC_API_KEY=sk-ant-your-key-here

The store: two tables

# /opt/research/store.py
import duckdb

DB = '/opt/research/data/market.duckdb'

SCHEMA = [
    """
    CREATE TABLE IF NOT EXISTS prices (
      ticker VARCHAR,
      day    DATE,
      open   DOUBLE,
      high   DOUBLE,
      low    DOUBLE,
      close  DOUBLE,
      volume BIGINT,
      PRIMARY KEY (ticker, day)
    )
    """,
    """
    CREATE TABLE IF NOT EXISTS runs (
      started_at    TIMESTAMPTZ,
      model         VARCHAR,
      input_tokens  BIGINT,
      output_tokens BIGINT,
      hits          BIGINT
    )
    """,
]

def connect(read_only=False):
    con = duckdb.connect(DB, read_only=read_only)
    con.execute("SET memory_limit='512MB'")
    con.execute('SET threads=2')
    if not read_only:
        for statement in SCHEMA:
            con.execute(statement)
    return con

The primary key on (ticker, day) is what makes the refresh safe to repeat. INSERT OR REPLACE overwrites a row that already exists for that ticker and that day, so running the backfill twice does not double the table. Without the key, one rerun after a crash silently duplicates every bar, and every average you compute afterwards is wrong with no error message anywhere to tell you.

DuckDB allows exactly one process to hold the file open for writing. A second writer fails at once with Could not set lock on file, followed by the PID that holds it, which in practice is the interactive duckdb shell you left open in another terminal. Readers pass read_only=True, which is why connect takes the flag. If several processes genuinely need to write at the same moment, that is a different engine's job: SQLite in WAL mode lets readers work while one writer commits, and a busy timeout makes other writers wait instead of failing. DuckDB against SQLite for a server workload compares the two, and running SQLite in production on a VPS covers the settings that make WAL behave.

The refresh job

# /opt/research/refresh.py
import sys
import pandas as pd
import yfinance as yf
from store import connect

TICKERS = ['AAPL', 'MSFT', 'KO', 'SAP', 'TSM']
FIRST_DAY = '2016-01-01'
COLS = ['ticker', 'day', 'open', 'high', 'low', 'close', 'volume']

def fetch(ticker, start):
    df = yf.Ticker(ticker).history(start=start, auto_adjust=True)
    if df.empty:
        return None
    df = df.reset_index()
    stamps = pd.to_datetime(df['Date'])
    if stamps.dt.tz is not None:
        stamps = stamps.dt.tz_localize(None)
    df['day'] = stamps.dt.date
    df['ticker'] = ticker
    df = df.rename(columns={'Open': 'open', 'High': 'high', 'Low': 'low',
                            'Close': 'close', 'Volume': 'volume'})
    return df[COLS]

def main():
    con = connect()
    empty = 0
    for ticker in TICKERS:
        last = con.execute('SELECT max(day) FROM prices WHERE ticker = ?',
                           [ticker]).fetchone()[0]
        rows_df = fetch(ticker, str(last) if last else FIRST_DAY)
        if rows_df is None:
            print(ticker + ': feed returned no rows', file=sys.stderr)
            empty += 1
            continue
        con.register('rows_df', rows_df)
        con.execute('INSERT OR REPLACE INTO prices '
                    'SELECT ticker, day, open, high, low, close, volume FROM rows_df')
        print(ticker + ': ' + str(len(rows_df)) + ' rows')
    con.close()
    if empty == len(TICKERS):
        print('every ticker returned nothing: the feed is broken', file=sys.stderr)
        sys.exit(1)

main()

Run it by hand once:

sudo -u research /opt/research/venv/bin/python /opt/research/refresh.py

The first run backfills years and prints one line per ticker with a count in the thousands. Run it again a minute later and each line shows 1 or 2 rows, because the job starts from the last day already stored. That second run is the real test: if the counts are still in the thousands, max(day) is returning nothing and the insert is rewriting your whole history every night.

The df.empty check is the most important line in the file. Ticker.history() does not raise for a wrong or delisted symbol. It prints a warning about the symbol possibly being delisted with no price data found (the exact wording moves between library versions) and returns an empty DataFrame. A job without that check writes nothing, exits 0, and systemd shows a healthy green run while the table quietly stops growing. You find out weeks later, from a screen that returns the same rows every day.

The timestamp handling has a reason too. The index the feed returns can carry the exchange timezone, and dropping the offset is not the same as converting to UTC. A Tokyo session stamped at midnight local time converts to the previous calendar day in UTC, so a UTC conversion silently shifts every Japanese bar back one day and breaks the primary key. tz_localize(None) keeps the exchange's own session date, which is what a daily bar means.

The timer that fires at market close

The US market closes at 16:00 New York time, and the last prints take a few minutes to settle, so the job runs at 16:20. Write that in New York time, not in UTC. New York is UTC minus 5 in winter and UTC minus 4 in summer, so a timer written as a fixed UTC hour drifts by an hour twice a year and starts firing before the close. systemd 252 and later accept a timezone directly in OnCalendar, and Ubuntu 24.04 ships 255. Confirm yours with systemctl --version.

# /etc/systemd/system/research-refresh.service
[Unit]
Description=Refresh market data and run the daily screen
Wants=network-online.target
After=network-online.target

[Service]
Type=oneshot
User=research
Group=research
WorkingDirectory=/opt/research
EnvironmentFile=/etc/research/env
ExecStart=/opt/research/venv/bin/python /opt/research/refresh.py
ExecStart=/opt/research/venv/bin/python /opt/research/screen.py

# /etc/systemd/system/research-refresh.timer
[Unit]
Description=Run the refresh after the US market close

[Timer]
OnCalendar=Mon-Fri 16:20 America/New_York
Persistent=true
RandomizedDelaySec=180

[Install]
WantedBy=timers.target

Type=oneshot is the only service type that accepts more than one ExecStart, and it runs them in order, stopping if one exits non zero. That is exactly the behaviour you want: a failed refresh must not be followed by a screen over stale data. Persistent=true matters on a VPS that reboots for kernel updates. Reboot at 16:15 without it and the run is simply lost; with it, the job runs as soon as the machine is back.

sudo systemctl daemon-reload
sudo systemctl enable --now research-refresh.timer
systemctl list-timers research-refresh.timer

list-timers should show a NEXT column holding the next weekday run, converted into the server's own local time. An empty list means the timer is not enabled, or the unit is missing its [Install] section, so enable had nothing to link into timers.target.

The screen: SQL first, the model last

SQL is exact and costs nothing per run. The model is neither. So the query narrows the universe, and only the survivors are ever sent to the model. Save this as /opt/research/screen.sql.

WITH ma AS (
  SELECT ticker, day, close,
         avg(close) OVER w20 AS ma20,
         avg(close) OVER w50 AS ma50,
         row_number() OVER (PARTITION BY ticker ORDER BY day) AS n
  FROM prices
  WINDOW
    w20 AS (PARTITION BY ticker ORDER BY day ROWS BETWEEN 19 PRECEDING AND CURRENT ROW),
    w50 AS (PARTITION BY ticker ORDER BY day ROWS BETWEEN 49 PRECEDING AND CURRENT ROW)
)
SELECT ticker, day, close, round(ma20, 2) AS ma20, round(ma50, 2) AS ma50
FROM ma
WHERE n > 50 AND ma20 > ma50
ORDER BY day DESC, ticker
LIMIT 20;

The n > 50 filter is not decoration. ROWS BETWEEN 49 PRECEDING AND CURRENT ROW averages whatever rows exist, so the third row of a ticker returns the average of three days and still calls it ma50. Compare that against ma20 and you invent a crossover at the start of every ticker's history that never happened. Filtering on the row number drops the rows where the window was not full.

What the model sees, and what it never sees

# /opt/research/screen.py
from anthropic import Anthropic
from store import connect

SYSTEM = (
    'You are a research assistant. Use only the rows in the message. '
    'If a number is not in the rows, say that it is not available. '
    'Do not give investment advice, price targets or buy and sell calls. '
    'Write at most 200 words.'
)

con = connect()
sql = open('/opt/research/screen.sql').read()
rows = con.execute(sql).fetchall()
block = '\n'.join(' / '.join(str(v) for v in row) for row in rows)

client = Anthropic(max_retries=5)   # reads ANTHROPIC_API_KEY from the environment
resp = client.messages.create(
    model='claude-sonnet-5',
    max_tokens=600,
    system=SYSTEM,
    messages=[{'role': 'user', 'content': 'Screen hits, ticker / day / close / ma20 / ma50:\n' + block}],
)

print(resp.content[0].text)
con.execute('INSERT INTO runs VALUES (now(), ?, ?, ?, ?)',
            ['claude-sonnet-5', resp.usage.input_tokens,
             resp.usage.output_tokens, len(rows)])
con.close()

The word limit in the system prompt caps the expensive half of the bill. The instruction to use only the given rows is the one you have to verify rather than trust: delete one column from the block, run it again, and read the output. If a figure for that column still appears, the model filled the gap, and your prompt is not tight enough. That test takes two minutes and it is the only honest way to find out.

The model never sees the API key, never sees the database path, and never runs a query. It receives rows and returns prose. That boundary is what makes the output checkable, because every number in the note should also appear in the block you sent, and you can compare them line by line. For more on the prompting side of this, using Claude for finance analysis goes further into what the model is good at reading.

One run, end to end

At 16:20 New York time the timer starts the service. refresh.py asks the feed for each ticker starting from the last stored day, writes one or two new bars each, and prints a line per ticker. screen.py opens the same file, runs the moving average query, and gets back a handful of rows. Those rows become a text block of a few hundred tokens. One API call turns them into a short note, the note goes to the journal, and one row lands in runs with the token counts and the number of hits.

journalctl -u research-refresh.service -n 50 --no-pager

A healthy log holds one line per ticker, then the note, then research-refresh.service: Deactivated successfully. After a week, read your own cost back out of the database:

SELECT count(*) AS runs,
       sum(input_tokens)  AS in_tokens,
       sum(output_tokens) AS out_tokens
FROM runs;

Multiply those totals by the per million token prices your model lists on the day you read them. That gives you the real figure instead of somebody's estimate. Published prices change. The arithmetic does not.

Why backtests overfit, and how to watch it happen

Rewrite the screen as a function of the two window lengths, sweep a grid of pairs, and rank them by return. The best pair will look excellent. That is the problem, not the result. A grid of 200 pairs is 200 experiments, and you kept the luckiest one.

You can watch it happen in ten minutes. Split the store in half by date. Sweep the grid on the first half only and write down the winner. Sweep the same grid on the second half. If the two winners are far apart, the parameters are fitting noise, and a pair that only wins on the half you tuned it on says nothing about tomorrow.

Survivorship is worse than overfitting because tuning cannot fix it. Your ticker list is today's index members, so it contains only the companies that survived. Ask the feed for a ticker that was delisted in 2019 and it hands back an empty frame, which means that company never enters your store and never enters your test. Every backtest you run has already excluded the failures.

Restated fundamentals break the timeline. The revenue figure the API returns today for a 2019 quarter is not always the figure that was published in 2019. A screen mixing today's fundamentals with 2019 prices is using information that did not exist then. Prices are usually safe here. Fundamentals usually are not.

Adjusted prices move under you. With auto_adjust=True the closes are adjusted backwards for dividends and splits, so the same query run next month returns a slightly different history. Storing the rows you actually used is what makes a result reproducible, and it is another reason the local store exists at all.

The backtest also ignores commissions and slippage, and it assumes your order does not move the price. Those belong to execution, which is out of scope here and covered in running trading bots on a VPS.

Failure modes and the strings you will see

error: externally-managed-environment when pip runs. You are outside the virtual environment. Call /opt/research/venv/bin/pip by full path.

Could not set lock on file, with a PID after it. Another process holds the DuckDB file open for writing, usually an interactive shell you forgot. Close it, or open the second connection with read_only=True.

Main process exited, code=killed, status=9/KILL in systemctl status. The kernel killed the job for memory. Confirm with journalctl -k | grep -i oom, then lower memory_limit in store.py.

A green run that writes nothing. systemctl status reads active (exited) and the table has not grown. The feed returned empty frames. This failure hides the longest, so make the job exit non zero when every ticker comes back empty.

The timer fired on a market holiday. systemd does not know the exchange calendar, so Mon-Fri includes holidays. The run happens, the feed has nothing new, and the job should treat that as normal rather than as an error.

429 from the API. You are over a rate limit. The Anthropic SDK retries with backoff on its own, and Anthropic(max_retries=5) raises the attempt count. If it still fails every day, the job is asking for too much in one burst.

What this is not

This is a research assistant. A model summarising a filing produces a reading of that filing, and it can be confidently wrong about a number printed in the text, which is why every figure in the note must trace back to a row you sent. Treat the output as a shortlist of things to read yourself. Nothing here is financial advice, and none of it is a signal.

Backtests are useful for rejecting ideas and weak at confirming them. A strategy that fails on your own data is genuinely dead. A strategy that passes has only survived your data, which is a much smaller claim than it feels like at 1am.

Execution stays deliberately out of scope. Orders and broker credentials carry a different risk profile from a read only research box, and mixing them puts trading keys on the same machine as an LLM prompt. If you want to see where this pattern sits next to the other things worth running on your own server, the self-hosted AI agents worth running is the wider tour.

FAQ

Do I need a paid market data feed?

Not for a prototype. A free unofficial feed is fine for learning the shape of the system, and it will break, because it depends on a website that owes you nothing. It usually breaks as empty frames rather than as an exception, so your job must check row counts. Move to a paid feed with a documented API and a support address once the data starts feeding a decision. The store is what makes that swap cheap: only the fetch function changes, and the schedule, schema and screen stay as they are.

Should I keep prices in SQLite or DuckDB?

DuckDB is columnar and built for scanning many rows to compute an aggregate, which is exactly what a moving average over ten years of bars is. SQLite is row oriented and better at many small reads and writes from several processes at once. For a single scheduled job that appends a few hundred rows and then scans millions, DuckDB is the better fit. If several processes must write at the same time, SQLite in WAL mode lets readers work while one writer commits, and a busy timeout makes other writers wait instead of failing outright.

How much do the LLM calls cost per month?

Log usage.input_tokens and usage.output_tokens from every response into a table, then multiply your weekly totals by the per million token price your model lists on the day you check. That is the only figure that stays true next quarter. One daily run over a short screen is a small number of calls, and the length of the note is the part you control: capping the summary at 200 words saves more than sending fewer rows, because output tokens are priced above input tokens on every Claude model.

Why did my job succeed but write no new rows?

There are two ordinary causes. The market was closed, because a systemd Mon-Fri schedule includes exchange holidays. Or the feed returned an empty frame for every ticker, which client libraries often report as a printed warning rather than an exception, so the process still exits 0 and systemd still shows a green run. Tell them apart by comparing SELECT max(day) FROM prices against the last real trading day, and make the job exit non zero when every ticker comes back empty.

Can the agent decide what to buy?

No, and building it to try is where these projects go wrong. The model has no market access, no view of your position or tax situation, and no way to check its own numbers against anything. What it is good at is reading a large amount of text and telling you which few items deserve your attention today. Nothing it writes is financial advice, and the decision, along with the responsibility for it, stays with you.