0
$\begingroup$

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

$\endgroup$
3
  • $\begingroup$ What's in the sample input file? $\endgroup$ – Sergei Rodionov Feb 1 at 7:44
  • $\begingroup$ For example the data I receive daily contains a csv with the following header: date,underlying,exchange,root_symbol,futures_symbol,futures_expiration_date,futures_close,options_expiration_date,strike,call_put,style,bid,ask,settlement, Volume......... My goal is to break up this data into a more manageable file system (by futures symbol, or options expiry?) for fast read times. Once in memory I can manipulate accordingly. $\endgroup$ – cvmn0207 Feb 2 at 12:06
  • $\begingroup$ This seems similar to examples at eodhistoricaldata.com/financial-apis/stock-options-data. The bulk of it is reference data which doesn't change. $\endgroup$ – Sergei Rodionov Feb 2 at 14:48
0
$\begingroup$

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 .

$\endgroup$
4
  • $\begingroup$ Hi, Thank you for your response. So from what I understand, you use one file per options time series. Is that one specific strike even? So a time series file similar to: \FutureEOD\S\SPX_SPM16_1250C.csv $\endgroup$ – cvmn0207 Feb 2 at 11:55
  • $\begingroup$ Also, to give you an idea. The current file system I use has only 4 futures symbols and each folder is a separate day. It takes about 90 seconds in C# to read in the EOD options data for a single futures contract. But with the number of futures contracts now closer to 50, if I use the same structure it will become even slower. Just curious as to what file structures other may use? thanks $\endgroup$ – cvmn0207 Feb 2 at 12:19
  • $\begingroup$ Yes, one file (=time-series) per instrument, i.e. one option with a given strike and maturity. Out of curiosity, how many instruments (i.e. different strikes etc.) would you have in total? $\endgroup$ – Enrico Schumann Feb 2 at 14:06
  • $\begingroup$ I probably have a 50 futures markets. Say 15 years per market. Say 4 expiries roll off per year. estimate 30 strikes per expiry. all x2 for one call and one put per strike. Probably a realistic rough guess. Is there a way to Tag a user in a thread in hopes thye look at it. When researching came across someone who may have a good solution. Thanks $\endgroup$ – cvmn0207 Feb 2 at 15:12
0
$\begingroup$

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

Query results CBOE option end-of-day data

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)

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.