Query a free market data API from a VPS

Query a free market data API from a VPS: the short version

Querying a free market data API from a VPS takes one POST request. You send SQL in a JSON body, and JSON rows come back. There is no client library to install and no set of per-symbol endpoints to stitch together, so a small Python script is enough. The job in this guide runs once a day, spends one query, pulls US daily bars for a watchlist, and writes them into a SQLite file on the same server.

Disclosure: the service used here is Strasmore, which is built by our team. Everything this guide says about the API comes from the public reference at https://api.strasmore.com/docs, read on 30 September 2026. Plan limits and table lists change, so treat every figure below as a dated figure, and confirm it against the schema endpoint on the day you write your job.

None of this is financial advice. It is a data pipeline, and what it stores is raw data.

Create a free API key

Keys are made in the console at https://ai.strasmore.com. Sign in, open the API keys section, and create one. The key starts with sk_live_ and it is displayed once, because the service stores only the SHA-256 hash of it (SHA-256 is a one-way hash, so the key cannot be read back out of storage). Lose it and you create another.

One detail to know before you plan around it: a key resolves to an organization, and the rate limits apply per organization rather than per key. Creating a second key does not give you a second quota.

Put the key into your shell without typing it on the command line:

read -rs STRASMORE_API_KEY
export STRASMORE_API_KEY

read -rs does not echo what you paste and does not write it to ~/.bash_history, which export STRASMORE_API_KEY=sk_live_... does. A key that reaches a history file or a script in git stays there until someone notices, and the same care applies as in keeping API keys out of the agents you run on a VPS.

Read the schema endpoint first, because it is not metered

GET /v1/schema returns every queryable table and column, with the restrictions that apply to your plan: history windows, data caveats, and which tables need a paid tier. It takes the same bearer token as a query and it does not come off your daily query cap, so read it before you spend anything.

curl -s https://api.strasmore.com/v1/schema \
  -H "Authorization: Bearer $STRASMORE_API_KEY" > schema.json
jq 'keys' schema.json

A 401 here and nothing else is wrong with your setup: the token is missing or wrong. A JSON document means you are authenticated, and you should now read it for three specific things. First, the date your plan's history starts. Second, which tables your plan cannot touch. Third, the columns that carry a caveat, because those caveats decide whether your query is measuring what you think it is.

The caveats are not decoration. Daily bars live in global_markets.stocks_daily_aggs, and the docs note that its volume is not the sum of the raw trade tape: it reflects condition code rules, so it will not match a number you compute yourself from tick data. The same table has test issues removed, such as Nasdaq's ZVZZT and NYSE's NTEST. Minute bars live in delayed_stocks_minute_aggs, whose volume excludes the closing auction print, so an end-of-day total built from minute bars is short by the largest print of the session.

Tables are addressed as global_markets.<table_name>. There is also GET /v1/health, which needs no token and only reports that the service is alive. It does not check the warehouse, so a healthy response there tells you nothing about whether a query will succeed.

Send one SQL query and read the whole response

POST /v1/query takes a JSON body with one field, sql: a single read-only statement, either SELECT or WITH ... SELECT, up to 100,000 characters. The dialect is ClickHouse SQL. Multiple statements, a SETTINGS clause, table functions such as remote or url, and anything outside the global_markets database are all rejected by the gate.

curl -s -X POST https://api.strasmore.com/v1/query \
  -H "Authorization: Bearer $STRASMORE_API_KEY" \
  -H "Content-Type: application/json" \
  --data-binary @- <<'JSON' | jq '{row_count, truncated, elapsed, history_window}'
{"sql": "SELECT ticker, toString(date) AS bar_date, toFloat64(close) AS close_price, toFloat64(volume) AS volume_shares FROM global_markets.stocks_daily_aggs WHERE ticker IN ('AAPL', 'MSFT') AND date >= today() - 7 AND date < today() ORDER BY ticker, date"}
JSON

Run the same command with jq '.rows[0]' to see one row. The response carries columns, rows, row_count, truncated, elapsed, generated_sql (always null today), and history_window.

Give each computed column an alias that is not the name of the column it wraps. toFloat64(close) AS close asks the engine to define close in terms of close, and it is easier to avoid that than to work out which analyzer version tolerates it.

truncated is the flag most scripts forget. A result that hits the 20,000-row cap is cut, not refused, so you get 200 OK with a partial answer. If your job trusts the rows blindly, it will store three quarters of a week and report success. Fail on truncated and narrow the window instead.

history_window is an object with start, requested_from, and clipped. Windows count whole calendar years, so in 2026 a free key starts at 1 January 2025. Ask for data from 2024 and the rows before the window are simply absent: clipped comes back true, start names the first date you are allowed, and requested_from names the date you asked for. No error is raised, which means a backfill script can look like it worked while it quietly stored a shorter history than you planned. Log clipped every run.

The server also enforces a 60-second query timeout, so a query that scans too much dies rather than hanging your script.

Which errors must a script retry, and which must stop it?

Every error returns JSON with detail, a stable error_code, and a request_id. Log the request_id, because it is what identifies one failed call later. The status code alone is not enough to decide what to do, and 429 is the clearest example: two of its three codes are worth a retry and one is not.

• 401: the key is missing, malformed, unknown, or revoked. Stop. A retry sends the same bad key.
• 402 with error_code HISTORY_WINDOW_EXCEEDED: the query asks only for dates before your plan's window. Stop. No backoff changes a date. Fix the query or the plan.
• 429 with RATE_LIMITED: you passed the per-minute request limit. Wait the number of seconds in the Retry-After header, then retry the identical request.
• 429 with THROTTLED: your organization's concurrency share is full. Same reaction, Retry-After then retry. More workers will only produce more of these.
• 429 with DAILY_QUOTA_EXCEEDED: the free tier's daily query cap is spent. Stop until tomorrow. Retrying costs nothing but noise, because the counter resets on a day boundary and not on a timer.
• 503 with WAREHOUSE_UNREACHABLE: a server-side problem, with Retry-After. Retry.
• 400: the gate rejected your SQL, or you asked for a table your tier cannot read. Stop. A tier rejection includes upgrade_plan and checkout_url in the body.
• 422: the body is malformed, usually a missing sql field or one over 100,000 characters. Stop.

So a rule of "retry on 429" is wrong. It turns a spent daily quota into a loop that runs until your timer gives up, and it hides the one error you actually wanted to see in the log.

Why one query for the whole watchlist beats one query per ticker

The docs are direct about this, under two headings. The first is filtering on sort order: the tick and minute tables are partitioned by day and sorted by ticker, then time, so you name the tickers exactly with = or IN, or by a prefix, and you always give a time range. The published block counts for one example query show what the difference is worth: an exact ticker match reads 14 blocks, a prefix match reads 978, and a pattern that does not start at the beginning of the string reads 828,045 blocks, around 6.8 billion rows. Never wrap the ticker column in a function, because that throws the sort order away.

The second heading is asking for more in each query. Each request carries a fixed startup cost, so the docs report one query for 20 contracts running about three times faster than 20 single-contract queries, and one query for 100 contracts about 1.5 times faster than five 20-contract queries. An IN list plus a date range is exactly the shape the sort order wants, so batching costs you nothing in scan efficiency.

There is a third reason that matters more on a free key. A loop over eight tickers fires eight requests within a few seconds, against a per-minute limit of ten. That loop works today and starts collecting 429s the moment your watchlist reaches eleven names. One query stays one query no matter how long the list gets, until you approach the 20,000-row cap.

[
  {
    "label": "Free plan daily cap",
    "queries_per_day": 100
  },
  {
    "label": "This job, one query a day",
    "queries_per_day": 1
  },
  {
    "label": "Same watchlist, one query per ticker",
    "queries_per_day": 8
  }
]

The script: one query into SQLite

Install what the script needs, then create a system user that owns the data and nothing else.

sudo apt update && sudo apt install -y python3-requests sqlite3 jq
sudo useradd --system --no-create-home --shell /usr/sbin/nologin marketdata
sudo install -d -m 755 /opt/market-pull

Write /opt/market-pull/pull_daily_bars.py:

#!/usr/bin/env python3
"""Fetch recent daily bars for a watchlist and store them in SQLite."""
import os
import re
import sqlite3
import sys
import time

import requests

API = 'https://api.strasmore.com/v1/query'
KEY = os.environ['STRASMORE_API_KEY']
DB = os.environ.get('MARKET_DB', '/var/lib/marketdata/market.db')
WATCHLIST = ['AAPL', 'AMD', 'AMZN', 'GOOGL', 'META', 'MSFT', 'NVDA', 'TSLA']
DAYS_BACK = 4
SAFE_TICKER = re.compile(r'[A-Z0-9.-]{1,12}$')

SCHEMA = '''
CREATE TABLE IF NOT EXISTS daily_bars (
  ticker TEXT NOT NULL,
  date TEXT NOT NULL,
  open REAL, high REAL, low REAL, close REAL, vwap REAL,
  volume REAL, transactions INTEGER,
  PRIMARY KEY (ticker, date)
) WITHOUT ROWID;
'''

SQL = (
    'SELECT ticker, toString(date) AS bar_date, '
    'toFloat64(open) AS open_price, toFloat64(high) AS high_price, '
    'toFloat64(low) AS low_price, toFloat64(close) AS close_price, '
    'toFloat64(vwap) AS vwap_price, toFloat64(volume) AS volume_shares, '
    'transactions '
    'FROM global_markets.stocks_daily_aggs '
    'WHERE ticker IN ({tickers}) '
    'AND date >= today() - {days} AND date < today() '
    'ORDER BY ticker, date'
)


def ticker_list():
    for t in WATCHLIST:
        if not SAFE_TICKER.match(t):
            sys.exit('refusing to send this ticker: ' + t)
    return ', '.join("'%s'" % t for t in WATCHLIST)


def run_query(sql, tries=4):
    for attempt in range(tries):
        r = requests.post(
            API,
            headers={'Authorization': 'Bearer ' + KEY},
            json={'sql': sql},
            timeout=90,
        )
        if r.status_code == 200:
            return r.json()
        try:
            body = r.json()
        except ValueError:
            body = {}
        code = body.get('error_code', '-')
        detail = body.get('detail', r.text[:200])
        rid = body.get('request_id', '-')
        stamp = '%s %s (request_id %s)' % (r.status_code, code, rid)
        if r.status_code == 429 and code == 'DAILY_QUOTA_EXCEEDED':
            print(stamp + ': quota spent, stopping until tomorrow', file=sys.stderr)
            sys.exit(75)
        if r.status_code in (429, 503):
            wait = int(r.headers.get('Retry-After', 5 * 2 ** attempt))
            print(stamp + ': waiting %ss' % wait, file=sys.stderr)
            time.sleep(wait)
            continue
        sys.exit(stamp + ': ' + str(detail))
    sys.exit('gave up after repeated 429 or 503')


def main():
    data = run_query(SQL.format(tickers=ticker_list(), days=DAYS_BACK))
    if data['truncated']:
        sys.exit('result hit the 20,000 row cap: narrow the window')
    window = data.get('history_window') or {}
    if window.get('clipped'):
        print('history clipped: plan starts %s, query asked from %s'
              % (window.get('start'), window.get('requested_from')),
              file=sys.stderr)
    if data['row_count'] == 0:
        print('no rows in window: weekend, holiday, or data not in yet',
              file=sys.stderr)
        return
    rows = [
        (r['ticker'], r['bar_date'], r['open_price'], r['high_price'],
         r['low_price'], r['close_price'], r['vwap_price'],
         r['volume_shares'], r['transactions'])
        for r in data['rows']
    ]
    con = sqlite3.connect(DB, timeout=30)
    con.execute('PRAGMA journal_mode=WAL')
    con.executescript(SCHEMA)
    with con:
        con.executemany(
            'INSERT OR REPLACE INTO daily_bars (ticker, date, open, high, '
            'low, close, vwap, volume, transactions) '
            'VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)', rows)
    con.close()
    print('stored %d rows in %.2fs of server time' % (len(rows), data['elapsed']))


if __name__ == '__main__':
    main()

Four choices in there are worth explaining, because copying them without the reason will hurt later.

The watchlist goes through SAFE_TICKER before it reaches the SQL. The API takes SQL as a string, so there are no bind parameters and nothing escapes your values for you. A hardcoded list is safe, and the check exists for the day someone reads the list from a file or a web form.

The window is the last four days, not yesterday alone. Markets close on weekends and holidays, and a row can arrive later than you expect. INSERT OR REPLACE with a primary key of ticker and date makes a re-fetch harmless, so a run that was skipped on Monday is repaired by Tuesday's run at no extra cost in queries. The docs say the tape is current to the prior session; they do not promise an hour, so do not build a job that needs yesterday's bar at 00:05.

The prices are cast with toFloat64 in the SQL. The warehouse stores them as Decimal(18, 4), and a decimal that arrives as a JSON string would land in a SQLite REAL column as text, where it sorts and compares as text. Casting once on the server is cheaper than repairing types in every query you write against the file afterwards.

SQLite is the right store for this shape of job: one writer, a file on the same disk, no daemon to run or secure. running SQLite as a production database on a VPS covers the WAL mode and backup details, and if the file grows into analytical queries over millions of bars, the comparison between DuckDB and SQLite on a server is where to decide whether to move.

Run it from a systemd timer, not from cron

The key goes in an environment file that systemd reads, not in the script. Create it empty with tight permissions, then open it in an editor so the key never appears on a command line:

sudo install -m 600 -o root -g root /dev/null /etc/market-pull.env
sudo nano /etc/market-pull.env

One line in the file: STRASMORE_API_KEY=sk_live_... with no quotes and no export. Mode 0600 owned by root is correct even though the service runs as marketdata, because systemd reads EnvironmentFile= as root before it drops privileges. The service user never needs to read the file. If you also want to run the script by hand as that user, widen it to 0640 root:marketdata and no further. The same reasoning applies to container work in keeping secrets out of Docker Compose files.

/etc/systemd/system/market-pull.service:

[Unit]
Description=Pull daily market bars into SQLite
After=network-online.target
Wants=network-online.target

[Service]
Type=oneshot
User=marketdata
Group=marketdata
EnvironmentFile=/etc/market-pull.env
Environment=MARKET_DB=/var/lib/marketdata/market.db
StateDirectory=marketdata
ExecStart=/usr/bin/python3 /opt/market-pull/pull_daily_bars.py
SuccessExitStatus=75
NoNewPrivileges=true
PrivateTmp=true
ProtectSystem=strict
ProtectHome=true

/etc/systemd/system/market-pull.timer:

[Unit]
Description=Daily market data pull

[Timer]
OnCalendar=Tue-Sat 09:30 America/New_York
RandomizedDelaySec=300
Persistent=true

[Install]
WantedBy=timers.target

StateDirectory=marketdata creates /var/lib/marketdata owned by the service user and keeps it writable while ProtectSystem=strict makes the rest of the filesystem read-only to this unit. SuccessExitStatus=75 is why the script exits 75 on a spent quota: without that line systemd records a failed unit, and a failed unit is an alert you do not want for something that is expected. Persistent=true makes systemd run a missed occurrence after a reboot, so a box that was down at 09:30 catches up. The timezone suffix on OnCalendar means you do not have to convert market hours into UTC in your head twice a year.

Enable it, then prove it works before you walk away:

sudo systemctl daemon-reload
sudo systemctl enable --now market-pull.timer
systemctl list-timers market-pull.timer
sudo systemctl start market-pull.service
journalctl -u market-pull.service -n 30 --no-pager

list-timers should show a NEXT column with a real date. The journal should end with a line like stored 32 rows in 0.41s of server time, and systemctl status market-pull.service should read inactive (dead) with status=0/SUCCESS, which is what a healthy oneshot unit looks like after it finishes. Then check the data itself:

sudo -u marketdata sqlite3 /var/lib/marketdata/market.db \
  'SELECT count(*), min(date), max(date) FROM daily_bars;'

A count near your ticker count times the number of trading days in the window means the whole path works. Zero rows with a clean exit means the window held no trading day, which you can confirm against global_markets.stocks_market_holidays.

A timer instead of cron buys you two things here. The environment is explicit, so the class of failure where a job runs with a minimal PATH and no variables does not apply: the reasons a cron job silently does nothing are mostly environment and mostly absent here. And the run is a unit, so journalctl -u has its output without you redirecting anything to a log file. writing a systemd service and timer on a VPS goes through the unit options in more depth.

Budget the free tier in the open

[
  {
    "plan": "Free",
    "requests_per_minute": 10
  },
  {
    "plan": "Developer",
    "requests_per_minute": 60
  },
  {
    "plan": "Pro",
    "requests_per_minute": 600
  },
  {
    "plan": "Enterprise",
    "requests_per_minute": 1200
  }
]

As of 30 September 2026 a free key allows 10 requests a minute and 100 queries a day, counted per organization. The job above spends 1 query a day. Schema reads are not queries, so exploring the tables costs nothing against the cap.

That leaves room to be useful. At one query per run you could poll hourly during the session and still use under a fifth of the cap. The per-ticker version of the same job spends 8 queries a day, which also fits, and then stops fitting the minute limit as soon as the watchlist grows. Add a second job later, for treasury yields from treasury_yields or dividends from stocks_dividends, and each one is one more query a day.

The limit you are most likely to hit first is concurrency, not the cap. The docs suggest starting with four or five parallel workers and warn that more workers than your share produces 429s and nothing else. For a daily job with one query, one worker is the whole design.

What a paid plan adds

Stated as the docs state it, with no prices here. The history window widens: free reads one calendar year, Developer three, and Pro and Enterprise read the full history, which for equities starts in September 2003. The request rate rises to 60 a minute on Developer and 1200 on Enterprise, and the concurrency share grows with the plan. Whole tables open up: tick-level stocks_trades, options_trades, and the daily options_greeks table are paid tier, and the quote cache tables cache_stocks_quotes and cache_options_quotes give a five-day preview on free with full history behind a paid plan. Ask for a restricted table and the reply is a 400 whose body carries upgrade_plan and checkout_url, so your script does not have to guess which tables it may read.

If what you want from these rows is analysis rather than storage, that is a separate job from this one, and using Claude to analyse market data covers it. Keep the collector boring. A job that stores raw bars reliably every day is worth more than a clever one that fails quietly in March.

FAQ

Why does my query return 402 when the dates in it look fine?

A 402 with error_code HISTORY_WINDOW_EXCEEDED means every date your query asks for is older than your plan's history window. Windows count whole calendar years, so on a free plan in 2026 the first readable date is 1 January 2025, and a query for 2024 alone returns 402. A query that spans the boundary does not fail: you get the rows inside the window, and the history_window object in the response comes back with clipped set to true. No retry and no backoff will change this, so the script should stop and either narrow the dates or the plan should change.

Should a script retry after a 429?

Only after two of the three. Read error_code, not just the status. RATE_LIMITED means you exceeded the per-minute request limit and THROTTLED means your concurrency share is full, and both are worth waiting the seconds named in the Retry-After header and then sending the identical request again. DAILY_QUOTA_EXCEEDED means the free tier's daily cap is spent, and no retry inside that day can succeed, so the job should log the request_id and exit. A blanket retry rule turns that third case into a loop that burns wall-clock time and hides the reason in a wall of identical log lines.

Why one query for a whole watchlist instead of one query per ticker?

Three reasons, all measurable. Each request carries a fixed startup cost, and the docs report one query covering 20 symbols running about three times faster than 20 single-symbol queries. The tables are sorted by ticker and then time, so an IN list with a date range reads the same small set of blocks a single ticker would, while a pattern that does not start at the beginning of the ticker string reads hundreds of thousands of blocks. And a loop sends one request per symbol, so an eight-symbol loop is eight of the ten requests a free key may send in a minute, which breaks as soon as the watchlist grows.

The timer runs but the script cannot find the API key. What is wrong?

The journal will show KeyError: 'STRASMORE_API_KEY' from the os.environ lookup. A variable exported in your interactive shell is not visible to a systemd unit, because the unit gets the environment systemd gives it and nothing else. Check that EnvironmentFile=/etc/market-pull.env is in the [Service] section, that the file holds a bare STRASMORE_API_KEY=sk_live_... line with no export keyword and no surrounding quotes, and that you ran sudo systemctl daemon-reload after editing the unit. Confirm what the service actually sees with sudo systemctl show market-pull.service -p Environment, which lists the variables without printing the contents of the environment file.