Basics

A series of simple examples are provided showing how to retrieve, filter, aggregate and join datasets, using OneTick Cloud sample databases.

Directed Graphs & Chains

Directed Graphs and Chains are constructed from a series of nodes. Nodes are python objects accessed through the onetick.query_webapi library.

• A Chain is a simple linkage between nodes. Chains can be reused to build graphs.

• A Directed Graph can be more than a Chain supporting joining and branching.

In both cases, nodes can be linked with the >> syntax.

Simple Chainlet Example

def trades():
        # Defined Nodes
    pt = otq.Passthrough().tick_type('TRD')
    a = otq.AddField(field='VALUE',value='PRICE*SIZE')
    w = otq.WhereClause(where='SIZE > 1')
    # Link Nodes as a Chain
    chain = otq.Chainlet(pt,a,w)
    # Create Graph from the Chain
    graph = otq.Graph(chain)
    return graph

Simple Graph Example

def trades():
        # Defined Nodes
    pt = otq.Passthrough().tick_type('TRD')
    a = otq.AddField(field='VALUE',value='PRICE*SIZE')
    w = otq.WhereClause(where='SIZE > 1')
    # Link Nodes as a Directed Graph
    graph = otq.Graph(pt >> a >> w)
    return graph

Data Retrieval

Directed Graphs are executed using the run method, passing in the server location, access token, and retrieval details. An access token is generated using the get_access_token method.

Access Token Retrieval and Running of Predefined Query

http_address = "https://rest.cloud.onetick.com"
access_token_url = "https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token"
# Replace [client id] and [client_secret] with values retrieved from your profile
client_id = "[client_id]"
client_secret = "[client_secret]"

access_token = otq.get_access_token(access_token_url, client_id, client_secret)

result = otq.run(query,
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,9,31,0),
    timezone='America/New_York',
    symbols='US_COMP_SAMPLE::CSCO'
    )

Data can be retrieved by selecting.

• Database

• Table (tick_type).

• Symbol or Symbols

• Time Range

All four must be provided for results to be retrieved. Typically the directed graph includes the reference to the table to be retrieved, while the otq.run method is used to specify the database, symbol, and time range, along with the http_address and access_token.

Simple Directed Graph, retrieving trades from the TRD table/tick_type.

def trades():
    pt = otq.Passthrough().tick_type('TRD')
    graph = otq.Graph(pt)
    return graph

Execution of Directed Graph

 result = otq.run(trades,
     http_address=rest_url,access_token=access_token,
     output_mode="pandas",
     start=datetime(2024,1,3,9,30,0),
     end = datetime(2024,1,3,9,31,0),
     timezone='America/New_York',
     symbols='US_COMP_SAMPLE::CSCO'  # Specifies the [Database]::[Symbol]
     )

Sample Databases & Tables

The full list of 200+ Global Equities, Futures, Options & Indices databases is available in OneTick Cloud

The list of sample databases are additionally included below:

OneTick Cloud Sample Databases

Database	Description	Available Tables
CA_COMP_SAMPLE	Consolidated Trades & Quotes Across All Canadian Venues	QTE, NBBO, STAT, TRD
CA_COMP_SAMPLE_BARS	Consolidated Canadian Trade & Quote 1 Minute Bars	QTE_1M, TRD_1M
EU_COMP_SAMPLE	Consolidated Trades & Quotes Across All European Venues	QTE, STAT, TRD
EU_COMP_SAMPLE_BARS	Consolidated European Trade & Quote 1 Minute Bars	QTE_1M, TRD_1M
LSE_SAMPLE	London Stock Exchange Trades, Quotes & Book Depth	DAY, IND, MKT, PRL_FULL, QTE, STAT, TRD
LSE_SAMPLE_BARS	LSE Trade & Quote 1 Minute Bars	QTE_1M, TRD_1M, DAY
TDI_FUT_SAMPLE	Global Futures Trades & Quotes	QTE, STAT, TRD
TDI_FUT_SAMPLE_BARS	Global Futures Trades & Quote 1 Minute Bars	QTE_1M, TRD_1M
US_COMP_SAMPLE	Consolidated Trades & Quotes Across All US Venues	QTE, STAT, TRD
SYMBOL_UNIVERSE	Symbol Universe across all available Venues	STAT
OQD_MKT_CAL	Market Holidays & Trading Hours	MKTCAL

Data is stored in standardized tables

OneTick Cloud Standard Tables

Table	Description
DAY	End of Day Record typically covering Closing Price & Open Interest for Derivatives Markets
IND	Indicative Prices occuring during Auction phases
QTE	Quote Events
STAT	Static Reference Data for the Instrument
TRD	Trade Events
NBBO	National Best Bid & Offer Quotes
PRL	Book Depth - Market By Level
PRL_FULL	Book Depth - Market by Order
MKTCAL	Market Holiday & Trading Hours
TRD_1M	1 Minute Trade Bar
QTE_1M	1 Minute Quote Bar

Retrieving a Single Symbol

A Symbol can be retrieved through passing a string value to the otq.run symbols property.

Retrieval of CSCO

result = otq.run(query,
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,9,31,0),
    timezone='America/New_York',
    symbols=['US_COMP_SAMPLE::CSCO']
    )

Retrieving Multiple Symbols

Multiple symbols can be retrieved through passing in a list of symbols into the otq.run symbols property.

Retrieval of Two Symbols

result = otq.run(query,
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,9,31,0),
    timezone='America/New_York',
    symbols=['US_COMP_SAMPLE::CSCO','US_COMP_SAMPLE::MSFT']
    )

A dictionary of Pandas dataframes will be returned. One for each symbol specified in the input list.

Defining the Database in the Graph

The Database can be prefixed in the graph, allowing shorter symbol names. The tick_type is set to the Database and Table. (e.g. [database]::[table].

Retrieval of Two Symbols, with Fixed Database in Graph

def trades():
    pt = otq.Passthrough().tick_type('US_COMP_SAMPLE::TRD')
    graph = otq.Graph(pt)
    return graph

result = otq.run(trades,
    http_address=rest_url,access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,9,31,0),
    timezone='America/New_York',
    symbols=['CSCO','MSFT']
    )

Producing a single output dataset across Symbols

A single Pandas dataframe can be generated by binding symbols to a Merge object in the Directed Graph. Nodes before the Merge are run per symbol, while nodes after performed across symbols. The single output is sorted by Timestamp. In this case the symbols attribute of the run method should not be specified or set to None.

Retrieval of Two Symbols merged into a single output

def trades():
       pt = otq.Passthrough().tick_type('US_COMP_SAMPLE::TRD')
       merge = otq.Merge().symbols(['CSCO','MSFT'])
       graph = otq.Graph(pt >> merge)
       return graph

result = otq.run(trades,
    http_address=http_address,
    access_token=access_token,
    output_mode="pandas",
    start=datetime(2024,1,3,9,30,0),
    end = datetime(2024,1,3,9,40,0),
    timezone='America/New_York',
    symbols=None
    )

Retrieving All Fields

A directed graph uses the otq.Passthrough object. The tick_type attribute is specified to retrieve records from the selected table.

Directed Graph retrieving records from the TRD table.

def trades():
    pt = otq.Passthrough().tick_type('TRD')
    graph = otq.Graph(pt)
    return graph

Retrieving Specific Fields

Specific Fields can be retrieved by specifying the fields attribute of the Passthrough node in the directed graph.

Retrieval of Specific Fields

def trades():
    pt = otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    graph = otq.Graph(pt)
    return graph

Adding Calculated Fields

Additional fields can be added through the AddField node in the directed graph. Multiple nodes in a graph can be chained together with >>. e.g. pt >> add_value

Adding Traded Value field

def trades():
    pt = otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    add_value = otq.AddField(field='TRADED_VALUE',value='PRICE*SIZE')
    graph = otq.Graph(pt >> add_value)
    return graph

Adding Filters

Results can be filtered through a WhereClause node in the directed graph.

Adding a Filter to the Directed Graph

def trades():
    pt = otq.Passthrough(fields='PRICE,SIZE,EXCHANGE').tick_type('TRD')
    add_value = otq.AddField(field='TRADED_VALUE',value='PRICE*SIZE')
    filter_on = otq.WhereClause(where='EXCHANGE="N" and SIZE > 100')
    graph = otq.Graph(pt >> add_value >> filter_on)
    return graph

By default the filter is applied, and only matches to the filter are returned. If non-matches are required the attribute discard_on_match can be set to True.

Adding a Filter to the Directed Graph

def trades():
    pt = otq.Passthrough(fields='PRICE,SIZE,EXCHANGE').tick_type('TRD')
    add_value = otq.AddField(field='TRADED_VALUE',value='PRICE*SIZE')
    filter_out = otq.WhereClause(discard_on_match=True,where='EXCHANGE="N" and SIZE > 100')
    graph = otq.Graph(pt >> add_value >> filter_out)
    return graph

Aggregating Across Results

Results can be aggregated by specifying an a dictionary of aggregation methods using ComputeFields, and then applying them in the Compute object along with the desired grouping, bucket period and method.

Aggregating Across Results

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
    mean_price':otq.Average(In='PRICE'),
    stddev_price':otq.Stddev(In='PRICE'),
    median_price':otq.Median(In='PRICE'),
    max_price':otq.Max(In='PRICE'),
    min_price':otq.Min(In='PRICE'),
    twa_price':otq.TwAverage(In='PRICE'),
    vwap_price':otq.Vwap(price_field_name='PRICE',size_field_name='SIZE'),
    count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

Additionally results can be grouped by adding the group_by attribute to the Compute object.

Aggregating Across Results with Group By

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
    mean_price':otq.Average(In='PRICE'),
    stddev_price':otq.Stddev(In='PRICE'),
    median_price':otq.Median(In='PRICE'),
    max_price':otq.Max(In='PRICE'),
    min_price':otq.Min(In='PRICE'),
    twa_price':otq.TwAverage(In='PRICE'),
    vwap_price':otq.Vwap(price_field_name='PRICE',size_field_name='SIZE'),
    count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,group_by='EXCHANGE',append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

And results can be grouped by time period the bucket_interval attribute to the Compute object.

Aggregating Across Results with 60 Second Bars

def agg_stats():
    trd_in= otq.Passthrough(fields='PRICE,SIZE').tick_type('TRD')
    comp_fields = otq.ComputeFields({
    mean_price':otq.Average(In='PRICE'),
    stddev_price':otq.Stddev(In='PRICE'),
    median_price':otq.Median(In='PRICE'),
    max_price':otq.Max(In='PRICE'),
    min_price':otq.Min(In='PRICE'),
    twa_price':otq.TwAverage(In='PRICE'),
    vwap_price':otq.Vwap(price_field_name='PRICE',size_field_name='SIZE'),
    count_price':otq.Count()
        })
    comp = otq.Compute(compute=comp_fields,bucket_interval=60,append_output_field_name=False)
    graph = otq.Graph(trd_in >> comp)
    return graph

Limiting Results

Results can be limited by adding the Limit object to the graph.

Retrieving the First 1000 Results

def trades():
    pt = otq.Passthrough(fields='PRICE,SIZE,EXCHANGE').tick_type('TRD')
    first_n = otq.Limit(tick_limit=1000)
    graph = otq.Graph(pt >> first_n)
    return graph

Logic Re-use

Chainlets can be used to store common directed graph patterns. Both Chainlets and Graphs can be made of multiple input chainlets.

Chainlets to Store Logic

def retrieve_quotes_part1():
    qte = otq.Passthrough(fields="BID_PRICE,ASK_PRICE,BID_SIZE,ASK_SIZE").set_tick_types(['QTE'])
    mid = otq.AddField(field="MID_PRICE",value="(BID_PRICE + ASK_PRICE)/2")
    chain = otq.Chainlet(qte,mid)
    return chain

def retrieve_quotes_part2():
    spread = otq.AddField(field="SPREAD",value="ASK_PRICE-BID_PRICE")
    qte_output = otq.Passthrough()
    qte_output.set_node_name('qte')
    chain = otq.Chainlet(spread,qte_output)
    return chain

def retrieve_quotes():
    # Creating a new chainlet from two input chains
    chain = otq.Chainlet(retrieve_quotes_part1(),retrieve_quotes_part2())
    return chain

def retrieve_trades():
    trd = otq.Passthrough(fields="PRICE,SIZE").set_tick_types(['TRD'])
    tradedvalue = otq.AddField(field="VALUE",value="PRICE*SIZE")
    trd_output = otq.Passthrough()
    trd_output.set_node_name('trd')
    chain = otq.Chainlet(trd,tradedvalue,trd_output)
    return chain

def retrieve_enriched_trades():
    # Building Directed Graph Nodes from pre-defined Chainlets
    qte_output = retrieve_quotes()
    trd_output = retrieve_trades()
    jbt = otq.JoinByTime(leading_sources='qte',add_source_prefix=False)
    out = otq.Passthrough(fields="trd.TIMESTAMP,qte.TIMESTAMP",drop_fields=True)
    # Defining the Graph by linking nodes together
    trd_output >> jbt
    qte_output >> jbt
    jbt >> out
    # Output the Graph object
    graph = otq.Graph(out)
    return graph