What is the quickest way to start a database for algo trading from scratch?

Question

Many people who are interested in algo/quant trading must have faced the same question before: how do I set up my own database from scratch?

I think much effort has been duplicated when we brainstorm the schematics and feed the same yahoo finance/quandl data to own database.

What is the quickest way to start a database for algo trading from scratch? Is there any one-click solution out there already? Or does it make sense for a retail trader to maintain own database at all?

Answer 1

I'm posting this as a proof that speed data-ing is possible in the world of databases.

Step 1: Install database - 2 minutes

Install Axibase TSD (my affiliation) as a Docker container. Alternatively, install it directly on a Linux host.

docker run -d -p 8443:8443 -p 8085:8085 -p 8091:8091 --env profile=FINANCE --name atsd axibase/atsd && \
docker logs -f atsd

Wait until "ATSD start completed" message. Replace SECRET with your password or login to https://localhost:8443 to setup a built-in account.

curl -s -k -w "status: %{http_code}\n" -o /dev/null https://localhost:8443/login \
-F "userBean.username=axibase" -F "userBean.password=SECRET" -F "repeatPassword=SECRET"

Step 2: Insert EOD data - 5 minutes

Replace API_KEY with polygon.io (no affiliation) API key. The command below will load data for the last 10 days, skipping weekends. The timeout is necessary due to throttling applied to free accounts. There is no timeout and no history limit for EOD data on paid accounts.

for ((i=1;i<=10;i++)); do \
  if (( $(date -d "-$i day" "+%u") > 5 )); then continue; fi; DT=$(date -d "-$i day" "+%Y-%m-%d"); \
  curl -s -w "$DT status: %{http_code}\n" -o "eod_$DT.json" "https://api.polygon.io/v2/aggs/grouped/locale/us/market/stocks/$DT?unadjusted=true&apiKey=API_KEY"; \
  echo "Wait 15s..."; sleep 15; \
done

Convert json files into csv files.

for f in eod*.json; do \
  (echo "datetime,exchange,class,symbol,open,high,low,close,vwap,voltoday,numtrades,valtoday" ; \
  cat $f | jq -c '.results[] | [(.t/1000 | todateiso8601),"SIP","SIP", .T, .o, .h, .l, .c, .vw//0, .v//0, .n//0, ((0+.vw)*(0+.v) | floor)]' | sort | \
  sed 's/\"//g;s/\[//g;s/\]//g') > "$(basename "$f" .json).csv" ; echo "Converted: $f"; \
done

Upload CSV files into the database. Replace SECRET again.

for f in eod*.csv; do \
  echo "Processing: $f"; curl -u axibase:SECRET -k -w " status: %{http_code}\n"\
  -F "data=@$f" -F "add_new_instruments=true" \
  https://localhost:8443/api/v1/trade-session-summary/import ; \
done

Step 3: Validate Data

curl -u axibase:SECRET -k https://localhost:8443/api/sql \
  --data "q=SELECT datetime, close FROM atsd_session_summary WHERE class = 'SIP' AND symbol = 'TSLA'"

"datetime","close"
"2021-03-19T20:00:00.000000Z",654.87
"2021-03-22T20:00:00.000000Z",670
"2021-03-23T20:00:00.000000Z",662.16
"2021-03-24T20:00:00.000000Z",630.27
"2021-03-25T20:00:00.000000Z",640.39
"2021-03-26T20:00:00.000000Z",618.71

Login into the database on https://localhost:8443 and execute a sample query in the SQL console. More on SQL syntax.

SELECT datetime, open, high, low, close, vwap 
  FROM atsd_session_summary 
WHERE class = 'SIP' AND symbol = 'TSLA'

Step 4: Daily Updates - 3 minutes

Add a cron script to load previous day results. The data is typically published shortly after midnight EST.

Replace API_KEY and SECRET accordingly. Set unadjusted=false to load split-adjusted prices, or load both types of prices with separate API calls.

DT=$(date -d "-1 day" +%F)

curl -o eod_$DT.json "https://api.polygon.io/v2/aggs/grouped/locale/us/market/stocks/$DT?unadjusted=true&apiKey=API_KEY"

echo "datetime,exchange,class,symbol,open,high,low,close,vwap,voltoday,numtrades,valtoday" > eod_$DT.csv
cat eod_$DT.json | jq -c '.results[] | [(.t/1000 | todateiso8601),"SIP","SIP", .T, .o, .h, .l, .c, .vw//0, .v//0, .n//0, ((0+.vw)*(0+.v) | floor)]' | sort | \
  sed 's/\"//g;s/\[//g;s/\]//g'  >> eod_$DT.csv

curl -u axibase:SECRET -k -w " status: %{http_code}\n"\
  -F "data=@eod_$DT.csv" -F "add_new_instruments=true" \
  https://localhost:8443/api/v1/trade-session-summary/import

Step 5 - Tick Data

Check out our Getting Started guide based on free IEX tick data which can be automated in a similar fashion on a Linux server with sufficient memory and disk. IEX data is typically available at midnight UTC on a T+1 basis.