Storing options EOD time series in Flat Files

Question

I have purchased data for EOD settlements of options prices for USA futures for personal use. I will not need multiple user access or real time access. I am not an expert programmer but use C# and R fairly regularly. Each future (say SPX) will have several option 'chains' each day. If a single date in the Time Series were May 25, 2016, we may have 3 chains trading . For example, SPM16 (June expiration), SPU16 (September), SPZ16 (December) Each chain will consist of many associated strike prices. Say 50 strike prices per chain (I call these StrikeRecords). Each strike will have an associated Call and Put. I call these individual call and put records SettlementRecords.

I was going to try to use a filesystem method that drobertson had suggested in this thread:

Building Financial Data Time Series Database from scratch

I found it very useful as I am not overly familiar with databases and I think I might end up spending more time on a PostgreSQL learning curve as opposed to studying the data.

He mentioned a file structure for EOD stock data..something in futures space which might look like:

\FutureEOD{YYYY}{Initial}_symbol.json

SPX data for 2016 would be

\FutureEOD\2016\S_SPX.json

Something like this File System structure could work, but I would have many options symbols/strikes contained within this one 2016 file for SPX.

I wondered if this file system might best with the /{YYYY} removed and split up further into options symbols? A separate file for each chain ordered by settlement DateTime? Something like:

\FutureEOD\S\SPX_SPM16.csv

\FutureEOD\S\SPX_SPU16.csv

\FutureEOD\S\SPX_SPZ16.csv

Each of these files would then contain all the strikes for each chain for every day that the contract existed (~2 yrs). I was going to use a SortedList to handle the data in memory and to get sorted back into the ordered TimeSeries.

Am I on the wrong track here?

Also, does it make sense to try to store in flat files as a CSV as opposed to JSON? Any other file structures best for reading speed with C#?

Has anyone tried to store options EOD data in file system? Any advice greatly appreciated.

Thanks

Answer 1

Axibase Time Series Database is a non-relational alternative with a built-in schema for tick, EOD, and reference data. Querying statistics in ATSD by common criteria such as symbol, date range, strike price etc. should be much faster than reading from the input files since the order of records in files is not guaranteed and one generally needs to read the entire file to check all records for match.

ATSD is free for production on single node.

Using CBOE EOD format (layout, as they call it) as an example:

underlying_symbol,quote_date,root,expiration,strike,option_type,open,high,low,close,trade_volume,bid_size_1545,bid_1545,ask_size_1545,ask_1545,underlying_bid_1545,underlying_ask_1545,bid_size_eod,bid_eod,ask_size_eod,ask_eod,underlying_bid_eod,underlying_ask_eod,vwap,open_interest,delivery_code
^VIX,2016-06-01,VIX,2016-11-16,65.000,P,0.0000,0.0000,0.0000,0.0000,0,6003,45.1000,7831,45.4000,14.2400,14.2400,572,45.1000,3340,45.3000,14.2000,14.2000,0.0000,0,

Run a python script to parse and upload EOD statistics into atsd_session_summary table:

import csv
from dateutil.parser import parse
from decimal import Decimal
from atsd_client import connect
from dateutil.tz import gettz

def to_option_symbol(root_symbol, expiration_date, option_type, strike):
    nd = expiration_date.replace("-", "")
    return root_symbol + nd + option_type + "{:09.3f}".format(Decimal(strike)).replace(".", "")

def norm(d):
    return d.quantize(Decimal(1)) if d == d.to_integral() else d.normalize()

def to_summary(row, columns):
    dt = parse(row['quote_date'] + ' 16:00:00 ET', tzinfos={"ET": gettz("US/Eastern")})
    res = 'CBOE,XCBO,' + to_option_symbol(row['root'], row['expiration'], row['option_type'], row['strike'])
    res += ',' + dt.isoformat()
    res += ',Day,C'
    for c in columns:
        res += ',' + str(norm(Decimal(row[c])))
    return res

fields_eod =  ['open', 'high', 'low', 'close', 'trade_volume', 
    'bid_size_1545', 'bid_1545', 'ask_size_1545', 'ask_1545', 'underlying_bid_1545', 'underlying_ask_1545', 
    'bid_size_eod', 'bid_eod', 'ask_size_eod', 'ask_eod', 'underlying_bid_eod', 'underlying_ask_eod', 'vwap', 'open_interest']
fields_sum =  ['open', 'high', 'low', 'closeprice', 'voltoday', 
    'custom_num_01', 'custom_num_02', 'custom_num_03', 'custom_num_04', 'custom_num_05', 'custom_num_06',
    'biddepth', 'bid', 'offerdepth', 'offer', 'underlying_bid', 'underlying_offer', 'vwap', 'numcontracts']

lines = set()
file = 'UnderlyingOptionsEODQuotes_2016-06-01.csv'
csvfile = open(file, 'r')
reader = csv.DictReader(csvfile, delimiter=',')
for row in reader:
    lines.add(to_summary(row, fields_eod))

hdr = "exchange,class,symbol,datetime,type,stage"
for h in fields_sum:
    hdr += ',' + h

conn = connect('./connection.properties')
conn.post_plain_text('/api/v1/trade-session-summary/import?add_new_instruments=true', hdr + "\n" + "\n".join(lines))

Once the records are in the database, you can query statistics with SQL:

SELECT exchange, class, symbol, datetime, open, high, low, closeprice AS close, voltoday AS trade_volume,
  bid, biddepth, offer, offerdepth, underlying_bid, underlying_offer, vwap, numcontracts AS opencontracts,
  custom_num_01 AS bid_size_1545, custom_num_02 AS bid_1545, custom_num_03 AS offer_size_1545, custom_num_04 AS offer_1545,
  CAST(CONCAT(SUBSTR(symbol, LENGTH(symbol)-7, 5), '.', SUBSTR(symbol, LENGTH(symbol)-2, 3))AS number) AS strike
FROM atsd_session_summary WHERE symbol LIKE 'VIX%'
  AND datetime BETWEEN '2016-06-01' AND '2016-06-02'
  AND strike BETWEEN underlying_bid*1.00 AND underlying_offer*1.05
ORDER BY entity.tags.symbol, datetime

It helps to store reference data such as strike prices, expiration dates, underlying and root symbols, ISINs, CUSIPs, CIKs etc in the database as well, so that you can filter statistics using reference fields close to data, speeding up data retrieval.

  WHERE CAST(entity.tags.strike AS number) BETWEEN 10 and 15

(Disclaimer: I work for Axibase)

Answer 2

What suits you will depend much on your requirements, such as how fast does the db have to be, and the type of your analysis, e.g. whether you care more about the cross-section or time-series.

I regularly store EOD option prices in CSV files (one file per option time-series), and then aggregate them as needed. I've written a small R package for that (https://github.com/enricoschumann/tsdb). If you want to try it and speed is an issue, there is a test script at https://github.com/enricoschumann/tsdb/blob/master/inst/tests/write_read.R .