Need for Speed: Using Compressed Data Containers for Faster Backtesting at Scale

Eddie Hebert

Software Engineer
Quantopian, Inc.

ehebert@quantopian.com

Platform allows algorithm writers to not worry about the plumbing.

This talk pulls back the curtain on some of the plumbing, the trade data pipeline, and the work we've been doing to optimize it.

Minute Bars For Everyone

U.S. Equity Data Set

OHLCV at Minute Granularity

Provided by vendor in CSV format.

Roughly ~35 GB of data, with ~8000 equities traded per day.

In [1]:
# Calculate upper bound of rows, assuming full liqudity.

minutes_per_day = 390
num_equities = 8000
days_in_year = 252
years_in_dataset = 13
approx_minute_rows = minutes_per_day * num_equities * days_in_year * years_in_dataset
print "Approx_minute_rows={:e}".format(approx_minute_rows)
print "^-- ~10 Billion OHLCV Data Points"
Approx_minute_rows=1.022112e+10
^-- ~10 Billion OHLCV Data Points

MongoDB

MongoDB: What worked well.

MongoDB: What didn't work.

Because of the CPU and memory inefficiences, the cap was set to 2% universe size or 200 harcoded equities.

Sorting As A Bottlneck

'day': 11111,
'sid': 24,
'trades': {
    [
    'dt': ...,
    'open': ...,
    'high': ...,
    'low': ...,
    'close': ...,
    'volume': ...,
    ],
    [
    'dt': ...,
    'open': ...,
    'high': ...,
    'low': ...,
    'close': ...,
    'volume': ...,
    ]
}

Why not use Mongo but change the data structure and index?

HDF5

http://www.hdfgroup.org/HDF5/

Hierarchical Data Format

Benefits from HDF5

Benchmarking

'no-op' algorithm


def initialize(context):
    set_universe(universe.DollarVolumeUniverse({floor}, 100.0))

def handle_data(context, data):
    pass

Memory Benchmarks

Time Benchmarks

Smaller Universes

Enter bcolz

Smallest Universes

bcolz: columnar and compressed data containers

https://github.com/Blosc/bcolz

Francesc Alted (also maintainer of PyTables)

https://github.com/FrancescAlted

https://www.youtube.com/watch?v=-lKV4zC1gss

Blosc

http://www.blosc.org/

https://github.com/Blosc/c-blosc

blosclz

Based on several other compression libraries, including:

  • FastLZ (http://fastlz.org/)
  • LZ4 and LZ4HC (http://fastcompression.blogspot.com.es/p/lz4.html)
  • Snappy (https://github.com/google/snappy)
  • Zlib (http://www.zlib.net/)

Blosc/bcolz Advantages

  • Each Column Is Stored Separately
  • Escapes the GIL
  • Better compression ratio for binary data.

Example Usage

In [36]:
# Example Usage

import bcolz

ctable = bcolz.open("./2014-11-25_equity-minutes.bcolz")
ctable
Out[36]:
ctable((1312360,), [('open', '<u4'), ('high', '<u4'), ('low', '<u4'), ('close', '<u4'), ('volume', '<u4'), ('dt', '<u4'), ('sid', '<u4')])
  nbytes: 35.04 MB; cbytes: 17.75 MB; ratio: 1.97
  cparams := cparams(clevel=5, shuffle=True, cname='blosclz')
  rootdir := './2014-11-25_equity-minutes.bcolz'
[(17310L, 17310L, 17300L, 17310L, 14368L, 1416925860L, 2L)
 (119070L, 119240L, 119040L, 119100L, 1205013L, 1416925860L, 24L)
 (55910L, 55980L, 54590L, 55910L, 6349L, 1416925860L, 31L) ...,
 (18250L, 18250L, 18250L, 18250L, 300L, 1416949200L, 48138L)
 (14000L, 14000L, 14000L, 14000L, 150L, 1416949200L, 48139L)
 (20000L, 20000L, 20000L, 20000L, 1280L, 1416949200L, 48146L)]
In [37]:
# Query One Sid

ctable['sid == 24']
Out[37]:
array([(119070L, 119240L, 119040L, 119100L, 1205013L, 1416925860L, 24L),
       (119070L, 119317L, 119070L, 119230L, 400939L, 1416925920L, 24L),
       (119220L, 119440L, 119160L, 119432L, 449312L, 1416925980L, 24L),
       (119440L, 119600L, 119370L, 119507L, 681538L, 1416926040L, 24L),
       (119507L, 119640L, 119480L, 119620L, 511272L, 1416926100L, 24L),
       (119630L, 119650L, 119480L, 119610L, 621000L, 1416926160L, 24L),
       (119630L, 119700L, 119550L, 119646L, 512616L, 1416926220L, 24L),
       (119634L, 119690L, 119610L, 119674L, 286913L, 1416926280L, 24L),
       (119670L, 119750L, 119650L, 119750L, 469568L, 1416926340L, 24L),
       (119749L, 119750L, 119600L, 119630L, 417035L, 1416926400L, 24L),
       (119630L, 119690L, 119510L, 119543L, 365183L, 1416926460L, 24L),
       (119550L, 119570L, 119350L, 119380L, 459581L, 1416926520L, 24L),
       (119380L, 119450L, 119310L, 119340L, 287344L, 1416926580L, 24L),
       (119340L, 119380L, 119200L, 119251L, 357219L, 1416926640L, 24L),
       (119259L, 119350L, 119230L, 119340L, 294160L, 1416926700L, 24L),
       (119330L, 119430L, 119310L, 119425L, 398486L, 1416926760L, 24L),
       (119425L, 119470L, 119380L, 119440L, 319067L, 1416926820L, 24L),
       (119431L, 119450L, 119320L, 119340L, 291239L, 1416926880L, 24L),
       (119331L, 119350L, 119210L, 119300L, 313183L, 1416926940L, 24L),
       (119300L, 119370L, 119300L, 119335L, 245988L, 1416927000L, 24L),
       (119340L, 119460L, 119320L, 119450L, 315629L, 1416927060L, 24L),
       (119460L, 119460L, 119310L, 119330L, 237977L, 1416927120L, 24L),
       (119330L, 119360L, 119200L, 119210L, 235987L, 1416927180L, 24L),
       (119240L, 119250L, 119060L, 119205L, 431113L, 1416927240L, 24L),
       (119209L, 119250L, 119160L, 119160L, 170458L, 1416927300L, 24L),
       (119170L, 119300L, 119170L, 119290L, 219849L, 1416927360L, 24L),
       (119240L, 119300L, 119170L, 119187L, 255219L, 1416927420L, 24L),
       (119190L, 119290L, 119180L, 119260L, 189600L, 1416927480L, 24L),
       (119250L, 119380L, 119245L, 119360L, 363594L, 1416927540L, 24L),
       (119366L, 119366L, 119220L, 119310L, 225335L, 1416927600L, 24L),
       (119319L, 119319L, 119110L, 119140L, 277834L, 1416927660L, 24L),
       (119139L, 119270L, 119110L, 119258L, 154422L, 1416927720L, 24L),
       (119250L, 119290L, 119110L, 119160L, 208856L, 1416927780L, 24L),
       (119159L, 119160L, 118940L, 118950L, 528016L, 1416927840L, 24L),
       (118950L, 119000L, 118850L, 118880L, 284178L, 1416927900L, 24L),
       (118880L, 119019L, 118700L, 118750L, 487762L, 1416927960L, 24L),
       (118760L, 118910L, 118754L, 118850L, 289425L, 1416928020L, 24L),
       (118850L, 119000L, 118780L, 118990L, 232654L, 1416928080L, 24L),
       (119000L, 119060L, 118940L, 118940L, 257937L, 1416928140L, 24L),
       (118950L, 119070L, 118900L, 118999L, 201509L, 1416928200L, 24L),
       (119000L, 119090L, 118950L, 118970L, 218491L, 1416928260L, 24L),
       (118970L, 119050L, 118960L, 119030L, 127106L, 1416928320L, 24L),
       (119030L, 119160L, 119000L, 119090L, 252931L, 1416928380L, 24L),
       (119090L, 119230L, 119070L, 119220L, 214715L, 1416928440L, 24L),
       (119230L, 119273L, 119190L, 119250L, 228954L, 1416928500L, 24L),
       (119240L, 119330L, 119210L, 119250L, 251942L, 1416928560L, 24L),
       (119250L, 119300L, 119210L, 119300L, 129267L, 1416928620L, 24L),
       (119293L, 119400L, 119250L, 119360L, 262567L, 1416928680L, 24L),
       (119360L, 119480L, 119350L, 119350L, 228219L, 1416928740L, 24L),
       (119360L, 119370L, 119260L, 119285L, 161446L, 1416928800L, 24L),
       (119300L, 119300L, 119210L, 119250L, 134538L, 1416928860L, 24L),
       (119250L, 119260L, 119200L, 119240L, 117367L, 1416928920L, 24L),
       (119240L, 119270L, 119170L, 119259L, 166846L, 1416928980L, 24L),
       (119250L, 119260L, 119150L, 119190L, 143132L, 1416929040L, 24L),
       (119200L, 119280L, 119190L, 119250L, 80022L, 1416929100L, 24L),
       (119250L, 119300L, 119220L, 119250L, 103683L, 1416929160L, 24L),
       (119250L, 119360L, 119220L, 119310L, 194257L, 1416929220L, 24L),
       (119315L, 119320L, 119170L, 119207L, 153573L, 1416929280L, 24L),
       (119210L, 119280L, 119200L, 119260L, 84720L, 1416929340L, 24L),
       (119260L, 119390L, 119240L, 119335L, 243668L, 1416929400L, 24L),
       (119335L, 119380L, 119290L, 119360L, 105846L, 1416929460L, 24L),
       (119360L, 119400L, 119360L, 119370L, 219718L, 1416929520L, 24L),
       (119370L, 119460L, 119340L, 119440L, 243069L, 1416929580L, 24L),
       (119440L, 119450L, 119350L, 119440L, 173847L, 1416929640L, 24L),
       (119436L, 119480L, 119380L, 119465L, 149385L, 1416929700L, 24L),
       (119440L, 119490L, 119430L, 119441L, 138482L, 1416929760L, 24L),
       (119460L, 119570L, 119450L, 119530L, 240745L, 1416929820L, 24L),
       (119530L, 119570L, 119500L, 119530L, 194015L, 1416929880L, 24L),
       (119520L, 119560L, 119510L, 119534L, 101556L, 1416929940L, 24L),
       (119540L, 119540L, 119450L, 119502L, 139674L, 1416930000L, 24L),
       (119500L, 119520L, 119460L, 119490L, 149519L, 1416930060L, 24L),
       (119500L, 119500L, 119430L, 119467L, 62458L, 1416930120L, 24L),
       (119470L, 119500L, 119410L, 119499L, 94004L, 1416930180L, 24L),
       (119500L, 119510L, 119460L, 119499L, 113761L, 1416930240L, 24L),
       (119490L, 119570L, 119450L, 119515L, 146811L, 1416930300L, 24L),
       (119530L, 119640L, 119510L, 119545L, 330250L, 1416930360L, 24L),
       (119540L, 119590L, 119510L, 119575L, 139974L, 1416930420L, 24L),
       (119570L, 119580L, 119470L, 119480L, 115322L, 1416930480L, 24L),
       (119486L, 119510L, 119420L, 119450L, 114499L, 1416930540L, 24L),
       (119460L, 119490L, 119330L, 119370L, 171709L, 1416930600L, 24L),
       (119370L, 119470L, 119360L, 119466L, 108091L, 1416930660L, 24L),
       (119440L, 119500L, 119340L, 119393L, 169863L, 1416930720L, 24L),
       (119400L, 119400L, 119310L, 119330L, 81164L, 1416930780L, 24L),
       (119340L, 119390L, 119330L, 119360L, 92322L, 1416930840L, 24L),
       (119357L, 119360L, 119270L, 119310L, 90570L, 1416930900L, 24L),
       (119320L, 119350L, 119270L, 119344L, 85555L, 1416930960L, 24L),
       (119350L, 119450L, 119340L, 119414L, 87212L, 1416931020L, 24L),
       (119420L, 119430L, 119330L, 119360L, 63323L, 1416931080L, 24L),
       (119360L, 119370L, 119312L, 119330L, 73541L, 1416931140L, 24L),
       (119330L, 119380L, 119320L, 119325L, 65281L, 1416931200L, 24L),
       (119330L, 119420L, 119320L, 119400L, 79229L, 1416931260L, 24L),
       (119410L, 119440L, 119370L, 119380L, 64756L, 1416931320L, 24L),
       (119370L, 119390L, 119310L, 119360L, 106827L, 1416931380L, 24L),
       (119370L, 119450L, 119360L, 119430L, 99590L, 1416931440L, 24L),
       (119433L, 119480L, 119390L, 119440L, 114775L, 1416931500L, 24L),
       (119440L, 119450L, 119370L, 119380L, 98939L, 1416931560L, 24L),
       (119370L, 119370L, 119290L, 119320L, 147803L, 1416931620L, 24L),
       (119320L, 119330L, 119170L, 119220L, 220380L, 1416931680L, 24L),
       (119220L, 119368L, 119200L, 119240L, 95456L, 1416931740L, 24L),
       (119240L, 119280L, 119200L, 119253L, 74122L, 1416931800L, 24L),
       (119260L, 119320L, 119253L, 119280L, 64290L, 1416931860L, 24L),
       (119297L, 119300L, 119180L, 119190L, 105077L, 1416931920L, 24L),
       (119190L, 119310L, 119190L, 119280L, 133969L, 1416931980L, 24L),
       (119280L, 119297L, 119200L, 119220L, 100217L, 1416932040L, 24L),
       (119210L, 119290L, 119200L, 119250L, 95890L, 1416932100L, 24L),
       (119240L, 119240L, 119080L, 119089L, 194299L, 1416932160L, 24L),
       (119080L, 119170L, 119080L, 119130L, 119833L, 1416932220L, 24L),
       (119130L, 119140L, 119050L, 119060L, 100741L, 1416932280L, 24L),
       (119060L, 119100L, 119050L, 119080L, 128673L, 1416932340L, 24L),
       (119080L, 119140L, 119070L, 119140L, 111119L, 1416932400L, 24L),
       (119140L, 119140L, 119080L, 119119L, 81563L, 1416932460L, 24L),
       (119120L, 119180L, 119103L, 119170L, 77510L, 1416932520L, 24L),
       (119170L, 119190L, 119130L, 119140L, 55891L, 1416932580L, 24L),
       (119150L, 119160L, 119120L, 119139L, 72406L, 1416932640L, 24L),
       (119140L, 119170L, 119120L, 119150L, 100731L, 1416932700L, 24L),
       (119159L, 119190L, 119140L, 119170L, 64575L, 1416932760L, 24L),
       (119169L, 119200L, 119160L, 119200L, 45873L, 1416932820L, 24L),
       (119200L, 119240L, 119199L, 119213L, 75064L, 1416932880L, 24L),
       (119210L, 119250L, 119200L, 119200L, 63842L, 1416932940L, 24L),
       (119200L, 119230L, 119180L, 119190L, 75690L, 1416933000L, 24L),
       (119180L, 119200L, 119120L, 119170L, 104466L, 1416933060L, 24L),
       (119169L, 119210L, 119160L, 119190L, 107360L, 1416933120L, 24L),
       (119200L, 119270L, 119190L, 119220L, 142898L, 1416933180L, 24L),
       (119230L, 119270L, 119172L, 119240L, 87789L, 1416933240L, 24L),
       (119260L, 119260L, 119170L, 119180L, 70252L, 1416933300L, 24L),
       (119170L, 119205L, 119160L, 119200L, 73998L, 1416933360L, 24L),
       (119205L, 119260L, 119190L, 119255L, 93247L, 1416933420L, 24L),
       (119250L, 119340L, 119250L, 119280L, 149406L, 1416933480L, 24L),
       (119280L, 119300L, 119250L, 119280L, 71278L, 1416933540L, 24L),
       (119270L, 119270L, 119200L, 119210L, 70816L, 1416933600L, 24L),
       (119220L, 119220L, 119160L, 119200L, 102203L, 1416933660L, 24L),
       (119194L, 119250L, 119180L, 119200L, 66356L, 1416933720L, 24L),
       (119181L, 119240L, 119160L, 119180L, 82780L, 1416933780L, 24L),
       (119176L, 119220L, 119160L, 119200L, 54632L, 1416933840L, 24L),
       (119220L, 119260L, 119170L, 119190L, 119799L, 1416933900L, 24L),
       (119200L, 119220L, 119180L, 119190L, 46866L, 1416933960L, 24L),
       (119190L, 119230L, 119160L, 119220L, 66709L, 1416934020L, 24L),
       (119220L, 119230L, 119190L, 119230L, 34354L, 1416934080L, 24L),
       (119230L, 119310L, 119210L, 119310L, 97880L, 1416934140L, 24L),
       (119309L, 119330L, 119210L, 119247L, 78125L, 1416934200L, 24L),
       (119250L, 119280L, 119223L, 119240L, 55401L, 1416934260L, 24L),
       (119240L, 119240L, 119200L, 119240L, 44987L, 1416934320L, 24L),
       (119238L, 119240L, 119170L, 119190L, 55808L, 1416934380L, 24L),
       (119190L, 119200L, 119170L, 119180L, 62925L, 1416934440L, 24L),
       (119180L, 119240L, 119160L, 119213L, 37096L, 1416934500L, 24L),
       (119220L, 119220L, 119170L, 119210L, 42195L, 1416934560L, 24L),
       (119210L, 119230L, 119160L, 119170L, 49254L, 1416934620L, 24L),
       (119170L, 119170L, 119080L, 119100L, 161878L, 1416934680L, 24L),
       (119100L, 119100L, 119050L, 119090L, 119890L, 1416934740L, 24L),
       (119090L, 119100L, 119060L, 119080L, 38751L, 1416934800L, 24L),
       (119071L, 119130L, 119071L, 119110L, 83570L, 1416934860L, 24L),
       (119100L, 119130L, 119100L, 119110L, 61508L, 1416934920L, 24L),
       (119103L, 119250L, 119010L, 119030L, 181554L, 1416934980L, 24L),
       (119030L, 119071L, 119010L, 119020L, 125490L, 1416935040L, 24L),
       (119027L, 119030L, 118960L, 118960L, 213178L, 1416935100L, 24L),
       (118960L, 119040L, 118930L, 119004L, 158115L, 1416935160L, 24L),
       (119006L, 119010L, 118950L, 118970L, 55320L, 1416935220L, 24L),
       (118963L, 118980L, 118920L, 118960L, 98117L, 1416935280L, 24L),
       (118950L, 118970L, 118900L, 118900L, 103526L, 1416935340L, 24L),
       (118905L, 118950L, 118900L, 118913L, 83859L, 1416935400L, 24L),
       (118920L, 118970L, 118910L, 118960L, 75264L, 1416935460L, 24L),
       (118960L, 118960L, 118940L, 118940L, 75881L, 1416935520L, 24L),
       (118930L, 118950L, 118900L, 118900L, 65463L, 1416935580L, 24L),
       (118900L, 118910L, 118870L, 118870L, 79363L, 1416935640L, 24L),
       (118880L, 118880L, 118810L, 118865L, 193486L, 1416935700L, 24L),
       (118865L, 118870L, 118690L, 118740L, 310686L, 1416935760L, 24L),
       (118740L, 118780L, 118650L, 118700L, 181114L, 1416935820L, 24L),
       (118710L, 118770L, 118670L, 118680L, 135434L, 1416935880L, 24L),
       (118690L, 118690L, 118630L, 118690L, 142613L, 1416935940L, 24L),
       (118690L, 118780L, 118660L, 118700L, 208411L, 1416936000L, 24L),
       (118700L, 118800L, 118670L, 118780L, 159270L, 1416936060L, 24L),
       (118780L, 118830L, 118770L, 118810L, 88220L, 1416936120L, 24L),
       (118810L, 118830L, 118750L, 118750L, 74392L, 1416936180L, 24L),
       (118750L, 118770L, 118740L, 118740L, 40881L, 1416936240L, 24L),
       (118740L, 118760L, 118670L, 118700L, 105764L, 1416936300L, 24L),
       (118690L, 118750L, 118660L, 118670L, 75201L, 1416936360L, 24L),
       (118670L, 118700L, 118600L, 118600L, 142083L, 1416936420L, 24L),
       (118600L, 118630L, 118450L, 118490L, 311972L, 1416936480L, 24L),
       (118490L, 118610L, 118450L, 118603L, 178043L, 1416936540L, 24L),
       (118600L, 118690L, 118580L, 118660L, 167873L, 1416936600L, 24L),
       (118660L, 118690L, 118580L, 118686L, 159585L, 1416936660L, 24L),
       (118680L, 118710L, 118620L, 118630L, 91619L, 1416936720L, 24L),
       (118630L, 118660L, 118610L, 118610L, 44075L, 1416936780L, 24L),
       (118620L, 118620L, 118530L, 118548L, 113435L, 1416936840L, 24L),
       (118542L, 118600L, 118510L, 118589L, 91843L, 1416936900L, 24L),
       (118590L, 118590L, 118520L, 118580L, 81483L, 1416936960L, 24L),
       (118580L, 118690L, 118550L, 118650L, 89022L, 1416937020L, 24L),
       (118650L, 118690L, 118560L, 118580L, 96473L, 1416937080L, 24L),
       (118580L, 118580L, 118500L, 118500L, 137294L, 1416937140L, 24L),
       (118501L, 118510L, 118380L, 118390L, 210300L, 1416937200L, 24L),
       (118400L, 118430L, 118370L, 118400L, 248308L, 1416937260L, 24L),
       (118400L, 118450L, 118320L, 118400L, 232159L, 1416937320L, 24L),
       (118402L, 118450L, 118305L, 118330L, 116466L, 1416937380L, 24L),
       (118330L, 118350L, 118270L, 118340L, 173423L, 1416937440L, 24L),
       (118330L, 118391L, 118330L, 118390L, 150540L, 1416937500L, 24L),
       (118390L, 118400L, 118160L, 118190L, 249918L, 1416937560L, 24L),
       (118189L, 118190L, 118060L, 118130L, 393316L, 1416937620L, 24L),
       (118130L, 118230L, 118110L, 118199L, 162611L, 1416937680L, 24L),
       (118190L, 118200L, 118120L, 118130L, 117351L, 1416937740L, 24L),
       (118130L, 118187L, 118050L, 118080L, 172785L, 1416937800L, 24L),
       (118070L, 118100L, 117770L, 117890L, 830124L, 1416937860L, 24L),
       (117885L, 117950L, 117815L, 117840L, 276748L, 1416937920L, 24L),
       (117840L, 117860L, 117600L, 117680L, 490218L, 1416937980L, 24L),
       (117680L, 117790L, 117670L, 117760L, 198935L, 1416938040L, 24L),
       (117750L, 117760L, 117600L, 117625L, 264125L, 1416938100L, 24L),
       (117620L, 117700L, 117540L, 117640L, 312168L, 1416938160L, 24L),
       (117640L, 117690L, 117550L, 117590L, 200104L, 1416938220L, 24L),
       (117580L, 117640L, 117450L, 117530L, 459798L, 1416938280L, 24L),
       (117522L, 117660L, 117500L, 117590L, 202883L, 1416938340L, 24L),
       (117590L, 117590L, 117460L, 117560L, 325046L, 1416938400L, 24L),
       (117550L, 117680L, 117540L, 117649L, 203134L, 1416938460L, 24L),
       (117644L, 117880L, 117640L, 117870L, 415526L, 1416938520L, 24L),
       (117870L, 117870L, 117780L, 117800L, 251356L, 1416938580L, 24L),
       (117800L, 117890L, 117800L, 117871L, 198528L, 1416938640L, 24L),
       (117880L, 117990L, 117850L, 117966L, 234021L, 1416938700L, 24L),
       (117970L, 118050L, 117950L, 118041L, 221831L, 1416938760L, 24L),
       (118037L, 118080L, 117994L, 118010L, 224532L, 1416938820L, 24L),
       (117993L, 118070L, 117980L, 118051L, 138758L, 1416938880L, 24L),
       (118050L, 118090L, 117970L, 118079L, 233458L, 1416938940L, 24L),
       (118080L, 118090L, 118030L, 118060L, 81024L, 1416939000L, 24L),
       (118050L, 118140L, 118050L, 118130L, 139956L, 1416939060L, 24L),
       (118139L, 118219L, 118120L, 118180L, 244134L, 1416939120L, 24L),
       (118179L, 118280L, 118170L, 118250L, 146213L, 1416939180L, 24L),
       (118250L, 118311L, 118240L, 118279L, 147901L, 1416939240L, 24L),
       (118280L, 118370L, 118270L, 118290L, 216823L, 1416939300L, 24L),
       (118294L, 118320L, 118230L, 118230L, 190627L, 1416939360L, 24L),
       (118230L, 118360L, 118230L, 118341L, 140047L, 1416939420L, 24L),
       (118340L, 118440L, 118300L, 118410L, 169382L, 1416939480L, 24L),
       (118421L, 118480L, 118380L, 118420L, 171009L, 1416939540L, 24L),
       (118420L, 118440L, 118380L, 118440L, 89438L, 1416939600L, 24L),
       (118430L, 118490L, 118378L, 118390L, 118608L, 1416939660L, 24L),
       (118399L, 118470L, 118350L, 118430L, 137625L, 1416939720L, 24L),
       (118425L, 118430L, 118330L, 118340L, 111610L, 1416939780L, 24L),
       (118340L, 118470L, 118320L, 118400L, 138779L, 1416939840L, 24L),
       (118390L, 118420L, 118340L, 118350L, 133233L, 1416939900L, 24L),
       (118340L, 118370L, 118310L, 118314L, 107939L, 1416939960L, 24L),
       (118320L, 118340L, 118280L, 118310L, 105910L, 1416940020L, 24L),
       (118290L, 118380L, 118280L, 118343L, 122543L, 1416940080L, 24L),
       (118340L, 118350L, 118300L, 118320L, 63976L, 1416940140L, 24L),
       (118320L, 118400L, 118320L, 118350L, 76518L, 1416940200L, 24L),
       (118352L, 118460L, 118350L, 118400L, 139039L, 1416940260L, 24L),
       (118410L, 118440L, 118370L, 118390L, 41810L, 1416940320L, 24L),
       (118390L, 118390L, 118310L, 118380L, 78552L, 1416940380L, 24L),
       (118370L, 118380L, 118280L, 118280L, 87260L, 1416940440L, 24L),
       (118280L, 118380L, 118280L, 118370L, 52272L, 1416940500L, 24L),
       (118376L, 118390L, 118320L, 118340L, 65453L, 1416940560L, 24L),
       (118325L, 118370L, 118310L, 118350L, 83122L, 1416940620L, 24L),
       (118350L, 118360L, 118270L, 118293L, 67189L, 1416940680L, 24L),
       (118300L, 118340L, 118280L, 118340L, 87500L, 1416940740L, 24L),
       (118340L, 118350L, 118290L, 118290L, 47080L, 1416940800L, 24L),
       (118290L, 118290L, 118160L, 118210L, 198442L, 1416940860L, 24L),
       (118220L, 118340L, 118220L, 118304L, 491250L, 1416940920L, 24L),
       (118300L, 118380L, 118270L, 118369L, 95122L, 1416940980L, 24L),
       (118365L, 118407L, 118300L, 118348L, 62023L, 1416941040L, 24L),
       (118350L, 118360L, 118280L, 118313L, 34730L, 1416941100L, 24L),
       (118310L, 118365L, 118250L, 118250L, 206194L, 1416941160L, 24L),
       (118250L, 118270L, 118250L, 118255L, 56037L, 1416941220L, 24L),
       (118250L, 118250L, 118120L, 118130L, 106633L, 1416941280L, 24L),
       (118130L, 118150L, 118095L, 118100L, 118130L, 1416941340L, 24L),
       (118100L, 118130L, 118060L, 118080L, 99649L, 1416941400L, 24L),
       (118085L, 118120L, 118070L, 118109L, 65208L, 1416941460L, 24L),
       (118090L, 118200L, 118075L, 118160L, 86976L, 1416941520L, 24L),
       (118154L, 118180L, 118120L, 118125L, 39320L, 1416941580L, 24L),
       (118130L, 118160L, 118050L, 118050L, 78682L, 1416941640L, 24L),
       (118050L, 118070L, 118010L, 118010L, 132921L, 1416941700L, 24L),
       (118010L, 118060L, 117950L, 118040L, 218686L, 1416941760L, 24L),
       (118040L, 118070L, 118010L, 118050L, 65318L, 1416941820L, 24L),
       (118050L, 118080L, 118020L, 118080L, 54480L, 1416941880L, 24L),
       (118080L, 118080L, 118010L, 118010L, 77717L, 1416941940L, 24L),
       (118020L, 118050L, 118010L, 118020L, 65725L, 1416942000L, 24L),
       (118030L, 118030L, 117990L, 118000L, 99989L, 1416942060L, 24L),
       (118000L, 118035L, 117960L, 117960L, 106119L, 1416942120L, 24L),
       (117960L, 117990L, 117920L, 117921L, 138642L, 1416942180L, 24L),
       (117929L, 118000L, 117910L, 117980L, 111346L, 1416942240L, 24L),
       (117980L, 118130L, 117960L, 118110L, 127579L, 1416942300L, 24L),
       (118106L, 118260L, 118100L, 118220L, 200889L, 1416942360L, 24L),
       (118220L, 118260L, 118180L, 118200L, 77431L, 1416942420L, 24L),
       (118200L, 118200L, 118050L, 118080L, 90088L, 1416942480L, 24L),
       (118080L, 118130L, 118040L, 118090L, 46684L, 1416942540L, 24L),
       (118100L, 118150L, 118090L, 118090L, 43783L, 1416942600L, 24L),
       (118100L, 118120L, 118080L, 118090L, 43608L, 1416942660L, 24L),
       (118090L, 118090L, 118040L, 118090L, 33091L, 1416942720L, 24L),
       (118080L, 118180L, 118070L, 118180L, 57967L, 1416942780L, 24L),
       (118173L, 118200L, 118130L, 118186L, 61411L, 1416942840L, 24L),
       (118180L, 118280L, 118180L, 118270L, 91033L, 1416942900L, 24L),
       (118260L, 118301L, 118220L, 118290L, 144710L, 1416942960L, 24L),
       (118290L, 118300L, 118190L, 118197L, 82534L, 1416943020L, 24L),
       (118190L, 118200L, 118160L, 118178L, 56463L, 1416943080L, 24L),
       (118170L, 118190L, 118150L, 118180L, 33036L, 1416943140L, 24L),
       (118180L, 118190L, 118120L, 118120L, 66564L, 1416943200L, 24L),
       (118120L, 118180L, 118120L, 118150L, 47326L, 1416943260L, 24L),
       (118150L, 118190L, 118140L, 118160L, 69094L, 1416943320L, 24L),
       (118150L, 118150L, 118120L, 118126L, 74866L, 1416943380L, 24L),
       (118130L, 118150L, 118050L, 118055L, 87462L, 1416943440L, 24L),
       (118050L, 118080L, 118040L, 118080L, 71095L, 1416943500L, 24L),
       (118080L, 118080L, 118050L, 118050L, 37813L, 1416943560L, 24L),
       (118050L, 118060L, 118010L, 118020L, 82993L, 1416943620L, 24L),
       (118020L, 118030L, 117940L, 117940L, 104326L, 1416943680L, 24L),
       (117940L, 117950L, 117900L, 117917L, 88263L, 1416943740L, 24L),
       (117910L, 118020L, 117910L, 118011L, 79312L, 1416943800L, 24L),
       (118017L, 118030L, 118000L, 118000L, 79432L, 1416943860L, 24L),
       (118000L, 118010L, 117970L, 117990L, 883642L, 1416943920L, 24L),
       (117985L, 118000L, 117950L, 117960L, 63982L, 1416943980L, 24L),
       (117960L, 118000L, 117960L, 117980L, 97247L, 1416944040L, 24L),
       (117980L, 118070L, 117980L, 118070L, 96743L, 1416944100L, 24L),
       (118060L, 118070L, 117970L, 117980L, 75298L, 1416944160L, 24L),
       (117998L, 118020L, 117980L, 118010L, 41604L, 1416944220L, 24L),
       (118010L, 118030L, 117990L, 118010L, 32359L, 1416944280L, 24L),
       (118000L, 118050L, 118000L, 118030L, 410719L, 1416944340L, 24L),
       (118035L, 118040L, 118010L, 118017L, 88660L, 1416944400L, 24L),
       (118019L, 118050L, 118000L, 118015L, 53633L, 1416944460L, 24L),
       (118015L, 118030L, 117990L, 118025L, 51516L, 1416944520L, 24L),
       (118026L, 118030L, 118010L, 118030L, 20386L, 1416944580L, 24L),
       (118035L, 118130L, 118030L, 118105L, 68432L, 1416944640L, 24L),
       (118100L, 118100L, 118030L, 118050L, 67935L, 1416944700L, 24L),
       (118040L, 118050L, 117950L, 117950L, 111678L, 1416944760L, 24L),
       (117950L, 118000L, 117940L, 117990L, 63283L, 1416944820L, 24L),
       (117997L, 118010L, 117920L, 117920L, 85565L, 1416944880L, 24L),
       (117920L, 117950L, 117900L, 117910L, 70420L, 1416944940L, 24L),
       (117910L, 117940L, 117850L, 117858L, 98080L, 1416945000L, 24L),
       (117851L, 117920L, 117850L, 117880L, 84136L, 1416945060L, 24L),
       (117880L, 117880L, 117840L, 117852L, 91139L, 1416945120L, 24L),
       (117856L, 117910L, 117850L, 117907L, 72434L, 1416945180L, 24L),
       (117904L, 117950L, 117900L, 117900L, 58086L, 1416945240L, 24L),
       (117900L, 117930L, 117900L, 117900L, 70511L, 1416945300L, 24L),
       (117900L, 117910L, 117870L, 117885L, 80260L, 1416945360L, 24L),
       (117890L, 117890L, 117820L, 117826L, 82833L, 1416945420L, 24L),
       (117821L, 117840L, 117760L, 117800L, 115880L, 1416945480L, 24L),
       (117790L, 117810L, 117750L, 117757L, 130207L, 1416945540L, 24L),
       (117750L, 117885L, 117750L, 117870L, 114015L, 1416945600L, 24L),
       (117880L, 117880L, 117790L, 117830L, 72974L, 1416945660L, 24L),
       (117830L, 117900L, 117830L, 117890L, 116171L, 1416945720L, 24L),
       (117890L, 117890L, 117800L, 117840L, 106636L, 1416945780L, 24L),
       (117831L, 117835L, 117780L, 117785L, 78014L, 1416945840L, 24L),
       (117790L, 117860L, 117780L, 117810L, 82069L, 1416945900L, 24L),
       (117807L, 117860L, 117790L, 117840L, 114130L, 1416945960L, 24L),
       (117840L, 117860L, 117820L, 117857L, 34217L, 1416946020L, 24L),
       (117850L, 118000L, 117850L, 117970L, 140121L, 1416946080L, 24L),
       (117980L, 118000L, 117960L, 117980L, 66580L, 1416946140L, 24L),
       (117999L, 118010L, 117970L, 118010L, 103798L, 1416946200L, 24L),
       (118000L, 118040L, 118000L, 118040L, 99567L, 1416946260L, 24L),
       (118040L, 118040L, 117960L, 117965L, 83299L, 1416946320L, 24L),
       (117970L, 117970L, 117930L, 117960L, 67527L, 1416946380L, 24L),
       (117960L, 118000L, 117920L, 117960L, 53270L, 1416946440L, 24L),
       (117955L, 118020L, 117950L, 118010L, 63822L, 1416946500L, 24L),
       (118010L, 118015L, 117950L, 117990L, 85610L, 1416946560L, 24L),
       (117990L, 117990L, 117900L, 117900L, 66956L, 1416946620L, 24L),
       (117900L, 117930L, 117870L, 117906L, 113255L, 1416946680L, 24L),
       (117900L, 117930L, 117880L, 117920L, 55806L, 1416946740L, 24L),
       (117920L, 117990L, 117910L, 117970L, 72260L, 1416946800L, 24L),
       (117970L, 118000L, 117950L, 117995L, 95012L, 1416946860L, 24L),
       (117990L, 118020L, 117970L, 117998L, 86506L, 1416946920L, 24L),
       (117990L, 118000L, 117940L, 117990L, 76187L, 1416946980L, 24L),
       (117980L, 117990L, 117920L, 117940L, 60115L, 1416947040L, 24L),
       (117940L, 117970L, 117920L, 117950L, 50389L, 1416947100L, 24L),
       (117950L, 117970L, 117940L, 117965L, 74905L, 1416947160L, 24L),
       (117970L, 117970L, 117940L, 117960L, 85375L, 1416947220L, 24L),
       (117960L, 117970L, 117920L, 117920L, 49798L, 1416947280L, 24L),
       (117922L, 117930L, 117870L, 117875L, 129683L, 1416947340L, 24L),
       (117870L, 117880L, 117782L, 117819L, 190482L, 1416947400L, 24L),
       (117820L, 117900L, 117810L, 117880L, 119493L, 1416947460L, 24L),
       (117885L, 117890L, 117820L, 117860L, 112723L, 1416947520L, 24L),
       (117860L, 117890L, 117830L, 117856L, 106413L, 1416947580L, 24L),
       (117859L, 117910L, 117840L, 117880L, 105087L, 1416947640L, 24L),
       (117880L, 117920L, 117870L, 117910L, 68332L, 1416947700L, 24L),
       (117914L, 117920L, 117870L, 117870L, 79250L, 1416947760L, 24L),
       (117870L, 117910L, 117860L, 117906L, 55143L, 1416947820L, 24L),
       (117910L, 117910L, 117870L, 117885L, 56896L, 1416947880L, 24L),
       (117880L, 117930L, 117880L, 117910L, 120338L, 1416947940L, 24L),
       (117900L, 117910L, 117835L, 117850L, 63010L, 1416948000L, 24L),
       (117850L, 117870L, 117797L, 117869L, 139506L, 1416948060L, 24L),
       (117860L, 117900L, 117830L, 117830L, 84147L, 1416948120L, 24L),
       (117850L, 117870L, 117810L, 117850L, 77983L, 1416948180L, 24L),
       (117850L, 117860L, 117770L, 117775L, 264667L, 1416948240L, 24L),
       (117780L, 117800L, 117710L, 117730L, 151285L, 1416948300L, 24L),
       (117740L, 117760L, 117700L, 117720L, 171991L, 1416948360L, 24L),
       (117710L, 117850L, 117690L, 117806L, 258142L, 1416948420L, 24L),
       (117805L, 117850L, 117780L, 117780L, 213354L, 1416948480L, 24L),
       (117790L, 117860L, 117750L, 117830L, 134340L, 1416948540L, 24L),
       (117840L, 117920L, 117820L, 117905L, 176578L, 1416948600L, 24L),
       (117905L, 117910L, 117830L, 117860L, 212950L, 1416948660L, 24L),
       (117860L, 117910L, 117830L, 117870L, 296545L, 1416948720L, 24L),
       (117870L, 117900L, 117835L, 117869L, 137342L, 1416948780L, 24L),
       (117870L, 117890L, 117800L, 117810L, 151504L, 1416948840L, 24L),
       (117800L, 117820L, 117800L, 117805L, 148358L, 1416948900L, 24L),
       (117805L, 117810L, 117730L, 117733L, 265565L, 1416948960L, 24L),
       (117730L, 117740L, 117670L, 117680L, 321208L, 1416949020L, 24L),
       (117685L, 117710L, 117640L, 117660L, 325681L, 1416949080L, 24L),
       (117660L, 117775L, 117650L, 117770L, 424170L, 1416949140L, 24L),
       (117770L, 117800L, 117600L, 117600L, 1101781L, 1416949200L, 24L)], 
      dtype=[('open', '<u4'), ('high', '<u4'), ('low', '<u4'), ('close', '<u4'), ('volume', '<u4'), ('dt', '<u4'), ('sid', '<u4')])
In [38]:
# Query Multiple Sids

ctable['(sid == 24) | (sid == 8554) | (sid == 5061)']
Out[38]:
array([(119070L, 119240L, 119040L, 119100L, 1205013L, 1416925860L, 24L),
       (47620L, 47665L, 47520L, 47660L, 697568L, 1416925860L, 5061L),
       (207540L, 207580L, 207460L, 207500L, 1185586L, 1416925860L, 8554L),
       ...,
       (117770L, 117800L, 117600L, 117600L, 1101781L, 1416949200L, 24L),
       (47470L, 47480L, 47450L, 47475L, 1007371L, 1416949200L, 5061L),
       (207160L, 207190L, 207090L, 207180L, 1440857L, 1416949200L, 8554L)], 
      dtype=[('open', '<u4'), ('high', '<u4'), ('low', '<u4'), ('close', '<u4'), ('volume', '<u4'), ('dt', '<u4'), ('sid', '<u4')])

Getting Around the numexpr/numpy cap

sids = get_sids()
max_query = 31
buckets = []

# Chop stocks into buckets at query cap.
while sids:
    if len(sids) < max_query:
        buckets.append()
        break
    buckets.append(sids[:max_query])
    sids = sids[max_query:]

table = bcolz.open(day_path)
 
mask = np.zeros(len(table), dtype=bool)

for bucket in buckets:
   query = ' | '.join(
        ["(sid == {0})".format(s) for s in bucket])
   result = table.eval(query, out_flavor='numpy')
   mask[result] = True

for row in table.where(mask):
    yield row

What to do about the smallest universe sizes?

  • bcolz currently lacks indexes, but has been discussed on the mailing lists.
  • Use uncompressed HDF5 with an index for smallest size.
In [2]:
import bcolz

ctable = bcolz.open("2014-11-25_equity-minutes.bcolz")
In [3]:
sids = [14848, 5121, 21513, 32270, 21519, 6928, 43694, 32279, 24, 7962, 5923, 46631, 5938, 1335, 5692, 40516, 47430, 23112, 4151, 3149, 21839, 35920, 357, 4707, 1637, 47208, 22972, 8554, 1900, 3951]
query = " | ".join(["(sid == {0})".format(s) for s in sids])
print query
(sid == 14848) | (sid == 5121) | (sid == 21513) | (sid == 32270) | (sid == 21519) | (sid == 6928) | (sid == 43694) | (sid == 32279) | (sid == 24) | (sid == 7962) | (sid == 5923) | (sid == 46631) | (sid == 5938) | (sid == 1335) | (sid == 5692) | (sid == 40516) | (sid == 47430) | (sid == 23112) | (sid == 4151) | (sid == 3149) | (sid == 21839) | (sid == 35920) | (sid == 357) | (sid == 4707) | (sid == 1637) | (sid == 47208) | (sid == 22972) | (sid == 8554) | (sid == 1900) | (sid == 3951)

In [4]:
%%timeit
ctable['sid == 24']
10 loops, best of 3: 56.9 ms per loop

In [5]:
%%timeit
ctable[query]
10 loops, best of 3: 79.3 ms per loop

In [6]:
!rm -rf 2014-11-25_zlib.h5

ctable.tohdf5('2014-11-25_zlib.h5', cparams={'cname': 'zlib', 'clevel': 9})
In [8]:
import tables
zlib_h5 = tables.open_file('2014-11-25_zlib.h5')
In [9]:
zlib_table = zlib_h5.get_node('/ctable')
In [10]:
%%timeit
zlib_table.read_where('sid == 24')
1 loops, best of 3: 260 ms per loop

In [11]:
%%timeit
zlib_table.read_where(query)
1 loops, best of 3: 329 ms per loop

In [12]:
!rm -rf 2014-11-25_blosc.h5

ctable.tohdf5('2014-11-25_blosc.h5', cparams={'cname': 'blosclz', 'clevel': 1})
In [13]:
import tables
bloscz_h5 = tables.open_file('2014-11-25_blosc.h5')
bloscz_table = bloscz_h5.get_node('/ctable')
In [14]:
%%timeit
bloscz_table.read_where('sid == 24')
10 loops, best of 3: 20.7 ms per loop

In [15]:
%%timeit
bloscz_table.read_where(query)
10 loops, best of 3: 53.6 ms per loop

We are hiring.

jobs@quantopian.com

Questions?

Other Challenges: How did we know the data was right?