How to Build Stock Research Agent for VPS

Wetin be self-hosted stock research agent

Self-hosted stock research agent na small program wey dey run for server wey you own. E dey collect market data according to schedule, keep am for local database, run screen over am, then ask large language model (LLM) to write wetin change. E dey read and filter data. E no dey trade, and nothing for this guide be financial advice.

Two people wey build this from scratch fit choose different libraries and still end up with the same four parts: feed wey dey supply prices and fundamentals, local store wey dey keep every row wey you don fetch before, job wey dey refresh the store according to timer, and LLM layer wey dey turn the rows wey remain into sentences. This guide dey build that structure with Python, DuckDB, systemd timer and Claude API (application programming interface). Execution na separate job with separate failure modes, and e suppose dey for VPS wey dem set up for trading bots instead.

Parts four dey, and wetin each one dey do

The feed na the only part wey dey talk to outside world. E sabi how to request ticker and date range, and how to return rows. Everything wey come after am dey read your database instead of the feed. So if feed outage happen, na one day of new data you go lose, instead of screen wey no work.

The store na the main reason for the whole work. Daily close wey you no record fit usually fetch again later. But intraday quote, estimate before dem revise am, or fundamentals figure before dem restate am no fit recover. Store na how you dey build record of wetin the data actually talk on the day e talk am.

The scheduler dey decide when refresh go happen. For server, na systemd timer be this. Na why VPS matter pass the code for this case.

The LLM layer dey read short text block wey your SQL produce, then write summary of am. E never connect to database, and e never build the query. If model write the SQL, one wrong token fit turn to wrong number inside fluent sentence, with nothing to compare am against. If SQL produce the numbers, model fit only get the prose wrong, and you fit check the prose against the rows wey you send.

Why you fit run am for VPS instead of laptop

The scheduler na the main reason. US market close for 16:00 New York time na 22:00 for Berlin and 04:00 the next morning for Jakarta. Laptop dey sleep for both times. If one run miss, e cost pass one late note: daily bars fit usually fetch again later, but anything wey get revised no fit, so the gap for your record permanent. The same reason apply to any agent wey schedule and stored state need survive reboot. Na wetin running KiroCrew as an always-on agent on your own VPS dey set up for.

The second reason small, but e still matter. The server hold one API key for one file. One system user wey no get login shell own am, and one job use am. This arrangement hard well-well to make for the laptop wey you also use browse web with. Keep the key outside the code and outside the model input. keeping API keys out of an AI agent na the topic wey explain this.

Sizing am: disk, RAM and tokens

Disk na the easy part. One daily bar na one row for each ticker for each trading day, and one US trading year get 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 na 5,040 rows after one year. The 500 tickers line reach 1,260,000 rows after ten years. Each row na one date plus small number of doubles, and DuckDB dey store columns compressed, so na tens of megabytes instead of gigabytes. No trust this estimate, including my own. Run du -h /opt/research/data/market.duckdb after your first backfill and use your own number.

RAM na where small VPS fit cause problem. By default, DuckDB dey take large part of the machine memory and all its cores for one query. This one make sense for analytics server, but e no make sense for 2 GB box wey dey run other things too. One aggregation across the whole prices table fit make kernel kill the process, and systemd go report Main process exited, code=killed, status=9/KILL while journalctl -k go show the out of memory kill. Set memory_limit and threads explicitly, and the query go slower instead of e dying.

You suppose measure tokens, no be estimate dem. Every Messages API response carry one usage object wey get input_tokens and output_tokens. Write both values into table for every call. After one week, you go know your real volume. Then multiply am by the price wey your model list for the day wey you check. Two things stable enough for planning. Output tokens cost pass input tokens for every Claude model, so capping the note at 200 words go reduce the bill more than trimming the data wey you send. Prompt caching no help for job wey run once every day, because cache lifetime dey measured in minutes. By the next run, the cached block don expire, so you go pay full input price again. Caching dey pay 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 skip the virtual environment and run pip install against the system Python, Ubuntu 24.04 go stop you with error: externally-managed-environment, because the distribution own /usr/lib/python3 and e no allow pip write there. The venv no be politeness. Na the only directory wey pip fit touch.

Put the API key for one file wey the service user fit read and nobody else fit read:

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 inside am, without quotes and without export, because systemd dey parse this file by itself instead of passing am 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 wey dey for (ticker, day) na wetin make the refresh safe to repeat. INSERT OR REPLACE dey overwrite row wey already dey exist for that ticker and that day, so if you run the backfill twice, e no go duplicate the table. Without the key, one rerun after crash go silently duplicate every bar, and every average wey you calculate afterwards go wrong, with no error message anywhere to tell you.

DuckDB allow exactly one process to hold the file open for writing. Second writer go fail immediately with Could not set lock on file, followed by the PID wey dey hold am. For practical use, na the interactive duckdb shell wey you leave open for another terminal. Readers fit pass read_only=True, na why connect dey take the flag. If several processes truly need write at the same time, na another engine suppose handle that work: SQLite for WAL mode allow readers work while one writer dey commit, and busy timeout make other writers wait instead of failing. DuckDB against SQLite for server workload compare the two, and running SQLite for production on VPS explain the settings wey 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 am by hand once:

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

The first run go backfill years and print one line for each ticker with count wey dey thousands. Run am again one minute later and each line go show 1 or 2 rows, because the job dey start from the last day wey don already dey stored. That second run na the real test: if the counts still dey thousands, max(day) no dey return anything and the insert dey rewrite your whole history every night.

The df.empty check na the most important line for the file. Ticker.history() no dey raise error for wrong or delisted symbol. E dey print warning say the symbol fit don delist with no price data found (the exact wording dey change between library versions) and e dey return empty DataFrame. Job wey no get that check no go write anything, e go exit 0, and systemd go show healthy green run while the table quietly stop growing. You go discover am weeks later, from screen wey dey return the same rows every day.

The timestamp handling get reason too. The index wey the feed return fit carry the exchange timezone, and removing the offset no be the same thing as converting to UTC. Tokyo session wey get midnight local time go convert to the previous calendar day for UTC, so UTC conversion go silently shift every Japanese bar go one day back and break the primary key. tz_localize(None) dey keep the exchange own session date, and na that daily bar mean.

Timer wey go fire when market close

US market dey close for 16:00 New York time, and e dey take a few minutes for the last prints to settle, so the job go run for 16:20. Write am for New York time, no be UTC. New York na UTC minus 5 for winter and UTC minus 4 for summer, so timer wey use fixed UTC hour go shift by one hour two times every year and start to fire before market close. systemd 252 and later accept timezone directly for OnCalendar, and Ubuntu 24.04 dey ship 255. Confirm your own version 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 na the only service type wey accept more than one ExecStart, and e dey run dem in order, stopping if one exit non zero. Na exactly this behaviour you want: failed refresh no suppose follow with screen wey still dey show stale data. Persistent=true matter for VPS wey dey reboot because of kernel updates. If you reboot for 16:15 without am, the run go simply lost; with am, the job go run as soon as the machine come back.

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

list-timers suppose show a NEXT column wey hold the next weekday run, converted to the server own local time. Empty list mean say the timer no dey enabled, or the unit no get [Install] section, so enable get nothing to link inside timers.target.

Screen: SQL first, model last

SQL exact and e no cost anything for each run. Model no be like that. So query dey reduce the whole set, and na only the rows wey remain go ever reach 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 no be decoration. ROWS BETWEEN 49 PRECEDING AND CURRENT ROW dey calculate average from any rows wey dey available, so the third row for one ticker go return average of three days and still call am ma50. Compare that with ma20 and you go create crossover for the beginning of every ticker history, even though e never happen. When you filter with row number, e remove the rows wey window never full.

Wetin model dey see, and wetin e never dey see

# /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()

Word limit wey dey system prompt dey cap the expensive part of the bill. Instruction wey say make e use only the rows wey you give am na the one you must verify, no be trust am blindly: remove one column from the block, run am again, then read the output. If figure for that column still show, model don fill the gap, and your prompt no tight enough. This test dey take two minutes, and na the only honest way to know.

Model never see API key, never see database path, and e never run query. E receive rows and return prose. Na this boundary make the output easy to check, because every number for the note suppose also dey inside the block wey you send, and you fit compare dem line by line. For more about the prompting side, using Claude for finance analysis explain more about wetin model good at reading.

One run, end to end

For 16:20 New York time, timer dey start the service. refresh.py dey ask the feed for each ticker, starting from the last day wey dem store, write one or two new bars for each one, then print one line per ticker. screen.py dey open the same file, run the moving average query, and collect some rows. Those rows become text block wey get some hundred tokens. One API call turn dem into short note. The note go journal, and one row go enter runs with the token counts and number of hits.

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

Healthy log get one line per ticker, then the note, then research-refresh.service: Deactivated successfully. After one week, you fit read your actual cost from 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 wey your model list on the day wey you read dem. That one give you the real figure instead of somebody estimate. Published prices dey change. The arithmetic no dey change.

Why backtests fit overfit, and how to watch am happen

Rewrite the screen as a function of the two window lengths, sweep a grid of pairs, and rank dem by return. The best pair go look excellent. Na that be the problem, no be the result. A grid of 200 pairs na 200 experiments, and you keep the one wey get the luckiest result.

You fit watch am happen within ten minutes. Split the store into two by date. Sweep the grid for the first half only and write down the winner. Sweep the same grid for the second half. If the two winners dey far apart, the parameters dey fit noise, and a pair wey only win for the half wey you tune am on no tell you anything about tomorrow.

Survivorship worse pass overfitting because tuning no fit fix am. Your ticker list na today's index members, so e contain only companies wey survive. If you ask the feed for a ticker wey delisted for 2019, e go return an empty frame. This means say the company never enter your store and never enter your test. Every backtest wey you run don already exclude the failures.

Restated fundamentals dey break the timeline. The revenue figure wey the API return today for a 2019 quarter no always be the figure wey dem publish for 2019. A screen wey mix today's fundamentals with 2019 prices dey use information wey no exist at that time. Prices usually safe for this case. Fundamentals usually no safe.

Adjusted prices move under you. With auto_adjust=True, the closes dey adjust backwards for dividends and splits, so the same query wey you run next month go return history wey change small. If you store the rows wey you actually use, na that one make the result reproducible. Na another reason why the local store dey exist at all.

The backtest still ignore commissions and slippage, and e assume say your order no go move the price. Those ones belong to execution, wey dey out of scope here and dem cover am for running trading bots on a VPS.

Failure modes and the strings you will see

error: externally-managed-environment wey pip dey run. You dey outside the virtual environment. Call /opt/research/venv/bin/pip with full path.

Could not set lock on file, with one PID after am. Another process dey hold the DuckDB file open for writing, usually na interactive shell wey you forget. Close am, or open the second connection with read_only=True.

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

A green run wey write nothing. systemctl status dey read active (exited) and the table never grow. The feed return empty frames. This failure fit hide pass the others, so make the job exit non zero when every ticker return empty.

The timer fire for market holiday. systemd no know the exchange calendar, so Mon-Fri include holidays. The run happen, the feed get nothing new, and the job suppose treat am as normal instead of error.

429 from the API. You don pass rate limit. The Anthropic SDK dey retry with backoff by itself, and Anthropic(max_retries=5) dey increase the attempt count. If e still fail every day, the job dey ask for too much in one burst.

Wetín this no be

This na research assistant. When model summarise filing, e dey produce one reading of that filing, and e fit confidently get number wey dey inside the text wrong. Na why every figure for the note must trace back to one row wey you send. Treat the output as shortlist of things wey you go read by yourself. Nothing here na financial advice, and none of am na signal.

Backtests useful to reject ideas, but dem no strong for confirming dem. Strategy wey fail for your own data don truly die. Strategy wey pass don only survive your data. That claim small well-well compared with how e dey feel by 1am.

Execution deliberately dey out of scope. Orders and broker credentials get different risk profile from read only research box, and mixing dem put trading keys for the same machine wey dey handle LLM prompt. If you wan see where this pattern dey beside other things wey worth running for your own server, the self-hosted AI agents wey worth running na the wider tour.

FAQ

I need paid market data feed?

You no need am for prototype. Free unofficial feed dey okay for learning how the system take work, but e go break because e depend on website wey no owe you anything. E dey usually break as empty frames instead of exception, so your job must check row counts. Move go paid feed wey get documented API and support address once the data start dey affect decision. Na the store make this change cheap: na only the fetch function go change, while schedule, schema and screen remain as dem be.

I suppose keep prices for SQLite or DuckDB?

DuckDB na columnar database wey dem build to scan plenty rows and calculate aggregate. Na exactly wetin moving average over ten years of bars need. SQLite dey row oriented and e better for many small reads and writes from several processes at the same time. For one scheduled job wey append few hundred rows and then scan millions, DuckDB fit better. If several processes must write at the same time, SQLite for WAL mode let readers work while one writer dey commit, and busy timeout make other writers wait instead of failing straight.

How much LLM calls go cost every month?

Log usage.input_tokens and usage.output_tokens from every response into table. Then multiply your weekly totals by the per million token price wey your model list on the day wey you check. Na this be the only figure wey go remain correct next quarter. One daily run over short screen na small number of calls. The length of the note na the part wey you control: capping the summary at 200 words save pass sending fewer rows, because output tokens cost pass input tokens for every Claude model.

Why my job succeed but e no write new rows?

Two normal causes dey. Market fit don close because systemd Mon-Fri schedule include exchange holidays. Or feed return empty frame for every ticker. Client libraries often report this as printed warning instead of exception, so process still exit 0 and systemd still show green run. Compare SELECT max(day) FROM prices with the last real trading day to know the difference. Make the job exit non zero when every ticker return empty.

Agent fit decide wetin to buy?

No. This na where these projects dey go wrong if you build am to try. The model no get market access, no know your position or tax situation, and no fit check its own numbers against anything. Wetin e good at na to read plenty text and tell you the few items wey deserve your attention today. Nothing wey e write be financial advice. You remain responsible for the decision and everything wey follow from am.