Basics#

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

Data Retrieval#

Data can be retrieved by selecting

Database & Table (separated by a period. e.g. LSE_SAMPLE.TRD).
Symbol or Symbols using the field SYMBOL_NAME
Time Range using field TIMESTAMP with >= and < or Time equal to, using TIMESTAMP =

All three must be provided for results to be retrieved.

Simple Retrieval Example#

select  PRICE, SIZE, EXCHANGE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
limit 10

Time Zones for Filtering#

All times are assumed to be UTC. The filtered time range can be specified in a given time zone by appending the time zone to the timestamp.

Filtering on US Eastern Time Zone#

select PRICE, SIZE, EXCHANGE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 09:30:00 America/New_York'
and TIMESTAMP < '2024-01-03 16:00:00 America/New_York'
limit 10

Time Zones for Resultsets#

All times are assumed to be UTC. The resultset can be also returned in a given time zone.

For REST queries:

Specifying the returned time zone for a REST API Request parameters#

params = {
    "query_type": "sql",
    "timezone" : "America/New_York",
    "response": "csv",
    "show_times_as_nanos" : "true",
    "all_times_are_readable" : "false",
    "compression":"gzip",
    "statement":sql_statement
    }

access_token = get_access_token(access_token_url, client_id, client_secret)
headers = {
    "Authorization": f"Bearer {access_token}",
    "Content-Type": "application/json"
}
response = requests.post(http_address, json=params, headers=headers)

For onetick.query_webapi the otq.run method should specify the time zone of the response.

Specifying the returned time zone for onetick.query_webapi#

result = otq.run(sql_query,
    http_address=http_address,access_token=access_token,
    output_mode="pandas",
    timezone="America/New_York")

For onetick.py the otp.run method should specify the time zone of the response.

Specifying the returned time zone for onetick.py using otp.run#

result = otp.run(otp.SqlQuery(sql_statement),
                     timezone='America/New_York')

Or the otp.config.tz attribute can be set:

Specifying the returned time zone for onetick.py using otp.config.tz#

otp.config.tz = 'America/New_York'
result = otp.run(otp.SqlQuery(sql_statement))

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

Field Selection#

All of the fields in a table can be retrieved by using the * syntax.

Retrieving All Fields using select *#

select *
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'

A limited set of fields can be specified by replacing the * with the comma separated set of fields.

Retrieving PRICE, SIZE and EXCHANGE fields#

select  PRICE, SIZE, EXCHANGE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'

Limiting Returned Rows#

The set of returned rows can be limited using the LIMIT and OFFSET clauses. LIMIT specifies how many rows to retrieve.

Retrieving the First 10 records#

select  PRICE, SIZE, EXCHANGE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
limit 10

While OFFSET specifis how many initial records should be skipped before returning results.

Retrieving 10 records offset by 5#

select  PRICE, SIZE, EXCHANGE
from US_COMP_SAMPLE.TRD
where SYMBOL_NAME='CSCO'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
limit 10 offset 5

Retrieving Multiple Symbols#

Multiple symbols can be retrieved through specifying IN or LIKE.

Retrieval of Two Symbols using IN#

SELECT * FROM LSE_SAMPLE.TRD
WHERE SYMBOL_NAME in ('VOD','BARC')
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Wildcarded Symbol Selection using LIKE#

SELECT * FROM LSE_SAMPLE.TRD
WHERE SYMBOL_NAME LIKE 'BA%'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Adding Calculated Fields#

Additional fields can be added through calculation and assigned an alias.

Adding Calculated Fields#

SELECT PRICE, SIZE, TRADE_CURRENCY, (PRICE*SIZE) as TRD_VALUE FROM LSE_SAMPLE.TRD
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
LIMIT 10

Adding Filters#

Results can be filtered by adding additional filters to the WHERE clause.

Adding Filters#

SELECT PRICE, SIZE, TRADE_CURRENCY, (PRICE*SIZE) as TRD_VALUE FROM LSE_SAMPLE.TRD
WHERE SYMBOL_NAME='VOD'
and TIMESTAMP >= '2024-01-03 00:00:00 UTC'
and TIMESTAMP < '2024-01-04 00:00:00 UTC'
and TRADE_CURRENCY = 'EUR'
and SIZE > 100
LIMIT 10

Aggregating Across Results#

Results can be aggregated by specifying an aggregation for each field and defining an alias.

Aggregating Across Results#

select
AVG(PRICE) as MEAN_PRICE,
STDDEV(PRICE) as STDDEV_PRICE,
MEDIAN(PRICE) as MEDIAN_PRICE,
MAX(PRICE) as MAX_PRICE,
MIN(PRICE) as MIN_PRICE,
TW_AVG(PRICE) as TWA_PRICE,
SUM(PRICE*SIZE)/SUM(SIZE) as VWAP_PRICE,
COUNT(PRICE) as COUNT_PRICE
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'

Additionally results can be grouped by adding the GROUP BY clause.

Aggregating Across Results with Group By#

select  TRADE_CURRENCY,
AVG(PRICE) as MEAN_PRICE,
STDDEV(PRICE) as STDDEV_PRICE,
MEDIAN(PRICE) as MEDIAN_PRICE,
MAX(PRICE) as MAX_PRICE,
MIN(PRICE) as MIN_PRICE,
TW_AVG(PRICE) as TWA_PRICE,
SUM(PRICE*SIZE)/SUM(SIZE) as VWAP_PRICE,
COUNT(PRICE) as COUNT_PRICE
from LSE_SAMPLE.TRD
where symbol_name = 'VOD'
and TIMESTAMP >= '2024-01-03 08:00:00 UTC'
and TIMESTAMP < '2024-01-04 16:00:00 UTC'
group by TRADE_CURRENCY