How to structure a stock market data database

Question

I would like to download stock market data from the internet (for example by scraping…) and organize them in a database (I am using python and SQL) which updates daily or on request. (The idea is to create a screener, do back testing and so on…) I am not very familiar with databases, so I am looking for some suggestions regarding the database structure.

The structure I have in mind is the following (see picture).

There are 8 tables (TICKER LIST, OHLCV and 6 different tables for the financial statements).

The main table is TICKER LIST: it should contain some sort of unique ID of the securities. I think the ticker symbol cannot be a unique ID, become ticker symbols for a company can change. The company name cannot be a unique ID become it is possible to have the same company on different exchanges. This is why I created the column ‘id’.

At each ‘id’ is associated an OHLCV table. For example BP will have a OHLCV table in USD from the NYSE exchange and a OHLC table in GBP from the LSE exchange). However the financial statements (Income, Balance, Cashflow…) should be the same for both of exchanges. This means I will have 2 replicates of the financial statements tables exactly identical, one for the id 987448 and the other for the id 239484 in this example. Maybe having replicates is not the best way to go…

My question is, does this structure make sense? How would you improve it? As I said I am not familiar with databases. Thanks

Answer 1

As always, any answer to this question is hugely driven by your use cases. The following is my interpretation and ansatz. Here on QSE and over at the DB exchange, I have seen various approaches to keeping financial / market data - and the corresponding discussions are quite heated... Some suggest simply dumping/reading to/from CSV, others suggest time series databases, etc. etc. It really (!) depends on your use case. Mine is keeping the data in a tidy fashion and having a self-explanatory and somewhat augmentable data model for my analysis use cases.

---

Ok, let's go: At this point, I interpret your question as saying:

"I want to store daily OHLCV data across a (limited) number of market instruments 
from a number of trading venues. I furthermore want to be able to link (in some 
sense to be yet defined) market data and financial statement data per company. 
Once that is done, I want to do some analysis, get some signals, backtesting etc.*

*Clearly, this statement is still very weak, but it helps to get at least a bit of the basics.

You are hence not worrying about concurrency, you are not worrying about (ultra low)latency / database performance, and you are not worrying about the added bonuses of time-series-databases too much - but you may want to worry about bitemporality, i.e. how do you want to let historical events be reflected in your data (corporate actions, say, or mergers)? For the sake of simplicity, let us assume that you simply re-load a full adjusted time series whenever a corporate action took place and that's that. Also, if there's a merger you simply 'drop' the old company...

As you have declared you want to use a relational database and SQL, let's stick with that. Let's split everything into entities, attributes, relationships now.

---

Entities and relationships you might want to care about

• Company: Clearly, each company is an entity. We are not considering any intra-company links, ownership relationships and the like and simply take a company as such. A company has a name and a reporting currency, at least.

• Instrument: Keeping it simple, each company offers one or more instruments, uniquely identified by its 12-digit ISIN. You can branch any other instrument related tables off this one at a later stage, if required.

• Market: A market (or venue) is the place where instruments are traded and prices are observed. For sake of simplicity, let's fix the currency on this level.

• Vendor: The vendor is the party responsible for getting the data to you. In a professional setting this might be Reuters or Bloomberg; on a simple level this could be AlphaVantage, Yahoo, Quandl, ...

• Symbol: In a market, you can uniquely identify an instrument via its symbol. Yet, if you use some available APIs, symbols might be vendor specific with non-deterministic 'maps'. It might be helpful to simply write them out on the level: symbol = <instrument, market, vendor>. (You might also want to be able to time-stamp this, see bitemporality above.)

• Price: Now you are able to put everything together: symbol,date,OHLCV lets you store everything you need here.

Now to the financial statement data.

As you want to work with Python for the data analysis, I would suggest the following additions:

• line item: Each item in a balance sheet / income statement, is more or less arranged hierarchically. I suggest setting this up in a way so that you can do simple aggregation later on. You may introduce on table for the balance sheet and one for income statements.

• statement header: This is the header table for each statement. It tells you the company, the date, and the statement type (income, balance..)

• statement data: This table holds the statement data, i.e. headerid, lineitem, value.

Notes:

• This setup is quite comprehensible IMHO, and I use it for my private endeavours (ex the financial statement stuff). I am covering ~150 tickers and load the data daily or a couple of times per week; I run analysis using SQL and R. For this little amount of data, this setup is sufficient. But I swap the setup effort for transparency, of course!
• FX: You could either introduce dummy corporates "EURUSD", "GBPUSD" and the like and store FX values the same way as you store stock prices, or you splash out another table for this. From a practical point of view, a distinct table might be preferrable, and since you do the analysis in Python you are not too restricted by SQL's corset, no?
• Referential integrity: You should enforce this, of course: A price has a symbol has an instrument/market/vendor has a company.
• As we have said above, we do not worry about bitemporality too much: if there's been some corporate event, we simply re-load all prices for a given symbol. IF you want to incorporate multiple versions of data, you need to add columns / tables for history-keeping, of course...

I HTH, or that it helps you get in the proper direction for your use cases...

Answer 2

In fact SQL is not a good idea, only NYSE can delivery 100 GB/day, I suggest you to start looking at MongoDB, take a look at this presentation James Blackburn - Python and MongoDB as a Platform for Financial Market Data

Answer 3

If the primary motivation is being able to query market data with SQL, take a look at Axibase Time Series Database (my affiliation).

Step 1: Sign-up for free Polygon API key.

Step 2: Install the database on a Linux machine. Generate API token for POST method to /api/v1/trade-session-summary/import endpoint.

Step 3: Download end of day bars for several days from Polygon. Each JSON file includes 9000+ US stocks but this endpoint is reasonably fast and it takes less than a minute.

declare -a DATES=(2021-03-10 2021-03-11 2021-03-12)
for DT in "${DATES[@]}"; do \
  curl -o eod_$DT.json "https://api.polygon.io/v2/aggs/grouped/locale/us/market/stocks/$DT?unadjusted=true&apiKey=POLYGON_API_KEY"; \
done

Step 4: Convert JSON to CSV

for DT in "${DATES[@]}"; do \
  (echo "datetime,exchange,class,type,stage,symbol,open,high,low,close,vwap,voltoday,numtrades,valtoday" ; \
  cat eod_$DT.json | jq -c '.results[] | [(.t/1000 | todateiso8601),"SIP","SIP","Day","C", .T, .o, .h, .l, .c, .vw//0, .v//0, .n//0, .v//(.vw//(.vw*.v))]' | \
  sed 's/\"//g;s/\[//g;s/\]//g' ) > eod_$DT.csv ; \
done

Step 5: Upload CSV files to ATSD

for DT in "${DATES[@]}"; do \
curl --header 'Authorization: Bearer ATSD_API_TOKEN' --insecure \
  -F "data=@eod_${DT}.csv" -F "add_new_instruments=true" \
  "https://atsd_hostname:8443/api/v1/trade-session-summary/import" ; \
done

Step 6: Query EOD data with SQL using the web console, or JDBC/ODBC driver, or via an API endpoint:

SELECT symbol, datetime,open,high,low,close,ROUND(vwap*voltoday/1000000) AS "daily_val, $M"
  FROM atsd_session_summary
WHERE class = 'SIP' AND symbol IN ('AAPL', 'TSLA')
  AND datetime BETWEEN '2021-03-01' AND '2021-03-15'
ORDER BY symbol, datetime

In terms of reference data, as a non-relational database with an extensible schema (on write) ATSD enables new columns to be added on the fly. For instance, Polygon reference data can be inserted just as easily without managing the schema.

SELECT symbol, entity.tags.name, entity.tags.sector, datetime, close
  FROM atsd_session_summary
WHERE class = 'SIP' AND entity.tags.sector IN ('Industrials', 'Basic Materials')
  AND IS_INSTRUMENT_IN_INDEX('CS10')
  AND datetime BETWEEN '2021-03-01' AND '2021-03-15'
WITH ROW_NUMBER(symbol ORDER BY datetime DESC) <= 1

| symbol | entity.tags.name             | entity.tags.sector | datetime            | close |
|--------|------------------------------|--------------------|---------------------|------:|
| AA     | Alcoa Corporation            | Basic Materials    | 2021-03-12 16:00:00 | 31.51 |
| AAL    | American Airlines Group Inc. | Industrials        | 2021-03-12 16:00:00 | 23.37 |