Setting Up a Trading Database for Daily Stock Data

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


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












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 by Sergei Rodionov (score 1)

https://quant.stackexchange.com/a/63067

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.