Choosing Databases for Stock Screening and Market Data
Summary
The document considers database design for a stock screening and charting application. Its proposed data includes company reference records, frequently refreshed intraday prices, daily history, and fundamental reports. Example queries combine time-series calculations, such as moving averages, with screening on valuation, sector, and company growth. These workloads require both analytical queries and concurrent reads and writes.
The responses favor relational databases for structured records, joins, ad hoc queries, and data integrity, with PostgreSQL or MySQL suggested for the core data. One response says low-frequency intraday bars can also fit in SQL, while very large tick-level histories may call for specialized storage or file formats designed around expected queries. Another suggests using different stores for different data types, and mentions a time-series dataframe database built on MongoDB. The guidance is general: the right design depends on data volume, query patterns, update rates, and concurrency needs, none of which are quantified here.
Key ideas
- Company, historical, and fundamental records are presented as structured data suited to relational storage.
- Relational databases support joins, flexible queries, and consistency constraints for screening applications.
- Minute bars may fit in SQL, while very large tick datasets may need storage optimized for their query patterns.
- Database choice should account for data volume, update frequency, concurrency, and analytical workload.
Tags
Full text
# Which Database (MySql or NoSQL) for a Stock market App # Which Database (MySql or NoSQL) for a Stock market App I'm re-creating an app for Stockmarket Screening & Realtime charting Display. The database wireframe which i propose to design is as follows: 1. Company master - Where all the information of the company is given: Vendor Code|Company Full Name|Company Short name|Industry Code|Industry Full Name|Promoter Group Code|Promoter Group|EXCHANGE1 CODE|STOCK CATEGORY|EXCHANGE2 CODE|TYPE|ISIN CODE|STOCK TYPE 2. ) Intraday Data - Where every minutes the price of a stock is Stored: (This would be overwritten the next trading day) EXCHANGE1 CODE | OPEN PRICE | LAST PRICE | DAYHIGH | DAYLOW | Offer Price | Offer Qty | VOLUME |VALUE |Date & Time Stamp 3. Historical Data (Day wise): EXCHANGE1 CODE | OPEN PRICE | CLOSE PRICE | DAYHIGH | DAYLOW | VOLUME | Date 4. Fundamental Data (Not Yet given full thought): This would store all fundamental data like last 4 qtrly reports, Competitors, Balance sheets, Financial Ratios, P&L Statement, Promoter Details etc.. Typical Queries would be: Stock Quote Page: Time Series Price Charts with user selection, Intraday, 1 week, 1 month, 6months, 1 year, 5 years Fundamental Data presented in chart form (i.e growth in profits, growth in sales) plus Other fundamental Data & News Stock Screening : (Example Queries) - Show me the stock of companies who have grown their sales by 20% per year over the last 3 years - Show me the companies whos PE is less than 10 - Show me the company whose qtrly profit has grown by 15% per year over past 5 years - Show me the companies in Automobiles sector whose last 100 day avg price is less than current price - Show 50day, 100day, 200day simple moving averages etc etc.. Right now i'm at a stage wherein i've to decide which database to use MySQL or MongoDB (NoSQL Document) or Cassandra (NoSQL Column). So in the above case which database should i use? and why? (Advantages/Disadvantages) I want fast execution, data integrity, high concurrency, data aggregation & calculations (Analysis). Plus we have to account that the data tables are being updates every minute and also serving visitor requests from the same DB simultaneously. So consistent & error free read/write is also of importance. Any comments/critiques on my DB wireframe also welcome. Regards Sunny ## Answer by Finn Espen Gundersen (score 7) https://quant.stackexchange.com/a/25277 An SQL database is generally best for structured data, ad-hoc queries and for queries involving joining several entities together to find the results. It will also help you maintain data consistency and integrity by forcing this more structured design. Recent in-memory features of modern database engines offer most of the remaining performance advantages of NoSQL. If the fundamentals part of your database consists of unstructured data (such as file attachments), then NoSQL is a good choice here. But an SQL table with file name references works almost just as well. My recommendation for your project is PostgreSQL, but if it has to be one of the mentioned then MySQL. ## Answer by Thomas Johnson (score 4) https://quant.stackexchange.com/a/25305 You're going to want different databases for different data. For instance, the company master, historical data, and fundamental data can probably all live in a standard SQL database (MySQL or Postgres are both reasonable choices). If the intraday data is relatively low-frequency (e.g., 1-minute bars or lower), that can probably be put into the SQL DB as well. If it's very large (e.g., tick-by-tick data from multiple exchanges), then SQL might not be a great choice. In that case, you will want to go with something like HDF5 files, or even a custom file format depending on what kind of queries you want to do. When data gets very large, it's important to think about exactly what kind of queries you're going to be running on it, and then optimize the data structure (backend, file format, column layout, etc) to suit your queries. ## Answer by maoyang (score 0) https://quant.stackexchange.com/a/64243 Some Quantitative department use Arctic which is a timeseries/dataframe database built on top of MongoDB.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.