0
$\begingroup$

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?

$\endgroup$
4
  • $\begingroup$ Database for ticks? Or for parameters? $\endgroup$
    – simsalabim
    Mar 29, 2021 at 14:07
  • $\begingroup$ As @wecandothis mentioned, the answer largely depends on the type of data. For end-of day-bars (plus some reference data) the requirements are might lighter. $\endgroup$ Mar 29, 2021 at 14:57
  • $\begingroup$ Thanks for both of the replies. I think of the question as a general situation of a quant trader who wants to build their own trading system. From this perspective, I can see that a trader who applies strategies such as factor investing would only require EOD data, while a day trader who utilize technical indicators or chart patterns may want tick data or higher frequency data at minute resolutions. I have an impression people always start building their own database from EOD data as it seems easier, and may progress to higher frequency, larger database. $\endgroup$
    – Alex Chan
    Mar 29, 2021 at 15:18
  • $\begingroup$ @Alex that's not correct. EOD as starting point for your database is just not correct. You should start with writing code that I) Retrieves stock data every minute (recommendation: do not start with one stock but get the whole index; write production code) and push it in a DB (NoSql DB is advised; if you are new then just start with sql), II) After a day you have N * M worth of data, III) Write code that retrieves this data from the database, analyse it, run your strategy and append the PnL matrix to a column in the DB, IV) Backtest it through a range of parameters and do paper trading $\endgroup$
    – simsalabim
    Mar 29, 2021 at 17:37

1 Answer 1

1
$\begingroup$

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'

enter image description here

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.

$\endgroup$
2
  • $\begingroup$ In the Getting started guide: Might be good to specify that /dev/tcp is a bash specific alias. With zsh you will get an error. $\endgroup$
    – Bob Jansen
    Apr 5, 2021 at 9:43
  • $\begingroup$ @BobJansen Thanks for the tip, I opted for netcat to make it work in all cases, assuming nc is installed :) $\endgroup$ Apr 5, 2021 at 19:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.