Designing Relational Tables for Stock Prices and Financial Statements

# How to structure a stock market data database


# How to structure a stock market data database












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 by Kermittfrog (score 4)

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

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













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!





- 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 by Clebson Derivan (score 1)

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

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

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

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 |
```