Designing a Local Equities Securities Master for Research
Summary
This tutorial describes a MySQL and Python data store for daily equities history. Its proposed schema separates exchanges, data vendors, symbols, and daily prices, linking prices to both a security and its source. That structure supports multiple vendors, ticker metadata, and later expansion, while local storage can reduce data access delays and dependence on vendor availability. The article also explains choosing a transactional storage engine, indexing large price tables, and storing prices with decimal precision to avoid floating-point rounding issues.
The workflow loads end-of-day data for a set of equities into the database and retrieves adjusted closing prices through pandas for analysis. It gives a sample query and output, and notes that sequential downloads could be made concurrent. The examples are practical but limited: the schema sidesteps symbol changes and share classes, the data source and interface reflect the period’s tools, and the article does not provide a full treatment of production data validation, corporate actions, or performance benchmarking. It is infrastructure guidance rather than a trading strategy.
Key ideas
- A local securities master can consolidate historical prices and metadata from multiple vendors.
- Separating exchanges, vendors, symbols, and daily prices makes the data model easier to extend.
- Transactional storage and row-level locking support safer writes, while indexes can help read performance.
- Financial prices should use precise decimal storage rather than binary floating-point fields.
- Python and pandas can query the local database for research, while scheduled jobs can refresh end-of-day data.
Tags
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.