Static data include:
- Futures, and specific contract details
- System parameters
- Price data
- Volume data
- Fundamental data if used, eg PE ratios for equities
- Accounting data
- Diagnostic data, storing what the system did at various points in the past
State data relates to the control of the trading system and I won't be going into details here - its just static data which is frequently modified.
Code is in the usual git repo. You will need pandas and sqllite3 (which came with my python distro automatically so check your own).
Creating the databasedbname="mydb"
setup_blank_tables(dbfilename, ["CREATE TABLE timeseries (datetime text, code text, price float)",
"CREATE TABLE static (code text, fullname text)"])
Here we're creating a database file with two tables in it. Notice the use of database names to abstract away from where they are stored. I find the performance of sqllite3 with massive files isn't great so I tend to stick to one table per file in practice, but for this simple example we don't need to.
If you're a SQL whizz you'll see that I am not doing any relational stuff here.
st_table.add("FTSE", "FTSE 100 index")
st_table.modify("FTSE", "FTSE all share")
Notice that we use staticdata so we don't need to use any SQL in these commands (in case the underlying table structure changes and to avoid having reams of repetitive nonsense), and within that the connection object ensures that the staticdata code isn't specific to sqlite3.
The sqlite3 read returns lists of tuples, which staticdata.read() resolves to a single string.
someprices=pd.TimeSeries(range(100), pd.date_range('1/1/2014', periods=100))
We use pandas TimeSeries class as the input and output, which is then translated into database terms. sqlite has no native datetime format, only text or float, so we need to translate between pandas/datetime and text. I define a specific format for the text representation to be precise and ensure the database is forward compatible to any changes in pandas.