# Basics

This section contains 14 examples for Basics using the `onetick-py`.<br />
\\\\
Each example is a self-contained script that can be run against the OneTick Cloud sample databases.

```default
# onetick-py WebAPI configuration for OneTick Cloud
import os
os.environ['OTP_WEBAPI'] = '1'
os.environ['OTP_HTTP_ADDRESS'] = 'https://rest.cloud.onetick.com'
os.environ['OTP_ACCESS_TOKEN_URL'] = 'https://cloud-auth.parent.onetick.com/realms/OMD/protocol/openid-connect/token'
os.environ['OTP_CLIENT_ID'] = '__FILL_IN__'
os.environ['OTP_CLIENT_SECRET'] = '__FILL_IN__'
```

## Data Retrieval

Retrieve Exchange trades from the `US_COMP_SAMPLE` database for `CSCO`, across the specified time range.

```ipython3
import onetick.py as otp

# Define Data Source, with Database and Table
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Specify Symbol, Time Range, and Time Zone
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 30, 1),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                             Time EXCHANGE  COND STOP_STOCK SOURCE TRF TTE  \
0   2024-01-03 09:30:00.065443591        Z  @  I                 N       0   
1   2024-01-03 09:30:00.111130049        Z  @  I                 N       0   
2   2024-01-03 09:30:00.127459523        V  @                    N       0   
3   2024-01-03 09:30:00.128498068        Z  @F I                 N       1   
4   2024-01-03 09:30:00.135190071        Q  @FTI                 N       1   
..                            ...      ...   ...        ...    ...  ..  ..   
145 2024-01-03 09:30:00.999507511        Q  @  I                 N       0   
146 2024-01-03 09:30:00.999511419        Q  @                    N       0   
147 2024-01-03 09:30:00.999512020        Q  @                    N       0   
148 2024-01-03 09:30:00.999553801        P  @  I                 N       0   
149 2024-01-03 09:30:00.999590600        Q  @F                   N       1   

    TICKER  PRICE        DELETED_TIME  TICK_STATUS  SIZE  CORR  SEQ_NUM  \
0     CSCO  50.02 1969-12-31 19:00:00            0     2     0   169103   
1     CSCO  50.16 1969-12-31 19:00:00            0     3     0   169148   
2     CSCO  50.17 1969-12-31 19:00:00            0   100     0   169181   
3     CSCO  50.17 1969-12-31 19:00:00            0     5     0   169182   
4     CSCO  50.13 1969-12-31 19:00:00            0    46     0   169193   
..     ...    ...                 ...          ...   ...   ...      ...   
145   CSCO  50.09 1969-12-31 19:00:00            0     2     0   172569   
146   CSCO  50.09 1969-12-31 19:00:00            0   200     0   172570   
147   CSCO  50.09 1969-12-31 19:00:00            0   300     0   172571   
148   CSCO  50.09 1969-12-31 19:00:00            0    87     0   172574   
149   CSCO  50.09 1969-12-31 19:00:00            0   500     0   172575   

    TRADE_ID              PARTICIPANT_TIME            TRF_TIME  OMDSEQ  
0         42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00       0  
1         43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00       0  
2         13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00       0  
3         44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00       0  
4        349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00       0  
..       ...                           ...                 ...     ...  
145      396 2024-01-03 09:30:00.999433768 1969-12-31 19:00:00       2  
146      397 2024-01-03 09:30:00.999433768 1969-12-31 19:00:00       3  
147      398 2024-01-03 09:30:00.999433768 1969-12-31 19:00:00       4  
148      312 2024-01-03 09:30:00.999210245 1969-12-31 19:00:00       5  
149      399 2024-01-03 09:30:00.999572740 1969-12-31 19:00:00       6  

[150 rows x 18 columns]
```

## First N Rows

Retrieve the first 100 trades for `CSCO` from the `US_COMP_SAMPLE` database, across the specified time range.

```ipython3
import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time EXCHANGE  COND STOP_STOCK SOURCE TRF TTE  \
0  2024-01-03 09:30:00.065443591        Z  @  I                 N       0   
1  2024-01-03 09:30:00.111130049        Z  @  I                 N       0   
2  2024-01-03 09:30:00.127459523        V  @                    N       0   
3  2024-01-03 09:30:00.128498068        Z  @F I                 N       1   
4  2024-01-03 09:30:00.135190071        Q  @FTI                 N       1   
..                           ...      ...   ...        ...    ...  ..  ..   
95 2024-01-03 09:30:00.897416820        Q  @F I                 N       1   
96 2024-01-03 09:30:00.897444128        Q  @F I                 N       1   
97 2024-01-03 09:30:00.897474806        Q  @F I                 N       1   
98 2024-01-03 09:30:00.897476583        Q  @F I                 N       1   
99 2024-01-03 09:30:00.897670983        D  @                    N   Q   0   

   TICKER    PRICE        DELETED_TIME  TICK_STATUS  SIZE  CORR  SEQ_NUM  \
0    CSCO  50.0200 1969-12-31 19:00:00            0     2     0   169103   
1    CSCO  50.1600 1969-12-31 19:00:00            0     3     0   169148   
2    CSCO  50.1700 1969-12-31 19:00:00            0   100     0   169181   
3    CSCO  50.1700 1969-12-31 19:00:00            0     5     0   169182   
4    CSCO  50.1300 1969-12-31 19:00:00            0    46     0   169193   
..    ...      ...                 ...          ...   ...   ...      ...   
95   CSCO  50.0800 1969-12-31 19:00:00            0    51     0   171857   
96   CSCO  50.0800 1969-12-31 19:00:00            0    29     0   171858   
97   CSCO  50.0800 1969-12-31 19:00:00            0    12     0   171859   
98   CSCO  50.0800 1969-12-31 19:00:00            0     2     0   171860   
99   CSCO  50.0797 1969-12-31 19:00:00            0   100     0   171862   

   TRADE_ID              PARTICIPANT_TIME                      TRF_TIME  \
0        42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00.000000000   
1        43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00.000000000   
2        13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00.000000000   
3        44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00.000000000   
4       349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00.000000000   
..      ...                           ...                           ...   
95      365 2024-01-03 09:30:00.897398456 1969-12-31 19:00:00.000000000   
96      366 2024-01-03 09:30:00.897428791 1969-12-31 19:00:00.000000000   
97      367 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000   
98      368 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000   
99      100 2024-01-03 09:30:00.896374950 2024-01-03 09:30:00.897637536   

    OMDSEQ  
0        0  
1        0  
2        0  
3        0  
4        0  
..     ...  
95       3  
96       4  
97       5  
98       6  
99       7  

[100 rows x 18 columns]
```

## Selecting Fields

Retrieve Trades specifying selected fields as an array of fields from the returned data source.

```ipython3
import onetick.py as otp

# Define the Data Source specifying the Database and Table
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Specify the fields to return
data = data[['PRICE', 'SIZE']]

# Return first 100 Rows
data = data.limit(100)

# Run the Query for the defined Symbol and Time Window
result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time    PRICE  SIZE
0  2024-01-03 09:30:00.065443591  50.0200     2
1  2024-01-03 09:30:00.111130049  50.1600     3
2  2024-01-03 09:30:00.127459523  50.1700   100
3  2024-01-03 09:30:00.128498068  50.1700     5
4  2024-01-03 09:30:00.135190071  50.1300    46
..                           ...      ...   ...
95 2024-01-03 09:30:00.897416820  50.0800    51
96 2024-01-03 09:30:00.897444128  50.0800    29
97 2024-01-03 09:30:00.897474806  50.0800    12
98 2024-01-03 09:30:00.897476583  50.0800     2
99 2024-01-03 09:30:00.897670983  50.0797   100

[100 rows x 3 columns]
```

## Adding Calculated Fields

Retrieve Trades adding a calculated field to the data source.

```ipython3
import onetick.py as otp

# Define Data Source
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Limit Schema
data = data[['PRICE', 'SIZE']]

# Add Calculated Field
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE']

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time    PRICE  SIZE  TRADED_VALUE
0  2024-01-03 09:30:00.065443591  50.0200     2        100.04
1  2024-01-03 09:30:00.111130049  50.1600     3        150.48
2  2024-01-03 09:30:00.127459523  50.1700   100       5017.00
3  2024-01-03 09:30:00.128498068  50.1700     5        250.85
4  2024-01-03 09:30:00.135190071  50.1300    46       2305.98
..                           ...      ...   ...           ...
95 2024-01-03 09:30:00.897416820  50.0800    51       2554.08
96 2024-01-03 09:30:00.897444128  50.0800    29       1452.32
97 2024-01-03 09:30:00.897474806  50.0800    12        600.96
98 2024-01-03 09:30:00.897476583  50.0800     2        100.16
99 2024-01-03 09:30:00.897670983  50.0797   100       5007.97

[100 rows x 4 columns]
```

## Adding Filters

Retrieve Trades, filtered by exchange and trade size.

```ipython3
import onetick.py as otp

# Define Data Source
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Limit Schema
data = data[['PRICE', 'SIZE', 'EXCHANGE']]

# Add Calculated Fields
data['TRADED_VALUE'] = data['PRICE'] * data['SIZE']

# Filter Fields
data = data.where((data['EXCHANGE'] == 'N') & (data['SIZE'] > 100))

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time  PRICE  SIZE EXCHANGE  TRADED_VALUE
0  2024-01-03 09:30:02.371760827  50.13   127        N       6366.51
1  2024-01-03 09:30:19.325065423  50.13   337        N      16893.81
2  2024-01-03 09:30:19.701084742  50.15   400        N      20060.00
3  2024-01-03 09:30:20.787037891  50.14   107        N       5364.98
4  2024-01-03 09:30:23.436860708  50.14   600        N      30084.00
..                           ...    ...   ...      ...           ...
54 2024-01-03 09:38:28.029695573  50.08   200        N      10016.00
55 2024-01-03 09:39:01.147191601  50.09   312        N      15628.08
56 2024-01-03 09:39:04.654739241  50.08   200        N      10016.00
57 2024-01-03 09:39:17.427491120  50.08   211        N      10566.88
58 2024-01-03 09:39:22.549900523  50.10   200        N      10020.00

[59 rows x 5 columns]
```

## Adjusting for Corporate Actions

Retrieve trades with original and corporate action adjusted prices for splits.

```ipython3
import onetick.py as otp

# Defining Data Source
data = otp.DataSource(db='LSE_SAMPLE', tick_type='TRD')

# Restrict Returned Fields
data = data[['PRICE', 'SIZE']]

# Add Fields with Original Price and Size
data['ORIG_PRICE'] = data['PRICE']
data['ORIG_SIZE'] = data['SIZE']

# Adjust PRICE field for historic Corporate Actions
data = data.corp_actions(fields='PRICE',
                         adjustment_date=otp.date(2024, 1, 17),
                         adjust_rule='PRICE',
                         apply_split=True)

# Adjust SIZE field for historic Corporate Actions
data = data.corp_actions(fields='SIZE',
                         adjustment_date=otp.date(2024, 1, 17),
                         adjust_rule='SIZE',
                         apply_split=True)

result = otp.run(data,
                 start=otp.dt(2024, 1, 10),
                 end=otp.dt(2024, 1, 17),
                 timezone='Europe/London',
                 symbols='DKE',
                 symbol_date=otp.dt(2024, 1, 17))
result
```

```myst-ansi
                       Time   PRICE      SIZE  ORIG_PRICE  ORIG_SIZE
0   2024-01-10 10:17:28.066  0.4010  127668.4     0.04010    1276684
1   2024-01-10 10:17:54.299  0.4000  100000.0     0.04000    1000000
2   2024-01-10 10:18:02.437  0.4120  111349.5     0.04120    1113495
3   2024-01-10 10:18:34.771  0.3566  600739.4     0.03566    6007394
4   2024-01-10 10:18:42.555  0.3500      55.0     0.03500        550
..                      ...     ...       ...         ...        ...
102 2024-01-16 11:00:11.634  0.4000  520794.0     0.40000     520794
103 2024-01-16 11:49:51.584  0.3750  475182.0     0.37500     475182
104 2024-01-16 11:58:51.631  0.3500   51661.0     0.35000      51661
105 2024-01-16 14:55:22.581  0.3500      18.0     0.35000         18
106 2024-01-16 15:42:57.681  0.3875   24136.0     0.38750      24136

[107 rows x 5 columns]
```

## Aggregation Statistics

Trade Price Statistics Analysis.<br />
\\\\
Computes aggregate price statistics (mean, standard deviation, median, min, max, VWAP, count) for `LSE` trades.

```ipython3
import onetick.py as otp

# Create the DataSource for LSE_SAMPLE.TRD trades
data = otp.DataSource(
    db='LSE_SAMPLE',
    tick_type='TRD',
    # Define the schema for trades
    schema_policy='manual',
    schema={'PRICE': float, 'SIZE': int},
)

# Aggregate statistics over the interval
agg = data.agg({
    'MEAN_PRICE': otp.agg.average('PRICE'),
    'STDDEV_PRICE': otp.agg.stddev('PRICE'),
    'MEDIAN_PRICE': otp.agg.median('PRICE'),
    'MAX_PRICE': otp.agg.max('PRICE'),
    'MIN_PRICE': otp.agg.min('PRICE'),
    'VWAP_PRICE': otp.agg.vwap('PRICE', 'SIZE'),
    'COUNT_PRICE': otp.agg.count(),
})

# Run the query
result = otp.run(
    agg,
    symbols='VOD',
    start=otp.dt(2024, 1, 3, 8),
    end=otp.dt(2024, 1, 4, 16),
    timezone='UTC',
)
result
```

```myst-ansi
                 Time  MEAN_PRICE  STDDEV_PRICE  MEDIAN_PRICE  MAX_PRICE  \
0 2024-01-04 16:00:00   69.770939      4.877331         70.16    71.0166   

   MIN_PRICE  VWAP_PRICE  COUNT_PRICE  
0      0.812   70.020224        11925  
```

## Multiple Symbol Retrieval

Retrieve Multiple Symbols by submitting an array of symbols into [`otp.run`](https://docs.pip.distribution.sol.onetick.com/api/run.html.md#onetick.py.run).

```ipython3
import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')

# Specify the fields to return
data = data[['PRICE', 'SIZE']]

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols=['CSCO', 'MSFT'])
result
```

```myst-ansi
{'CSCO':                             Time    PRICE  SIZE
 0  2024-01-03 09:30:00.065443591  50.0200     2
 1  2024-01-03 09:30:00.111130049  50.1600     3
 2  2024-01-03 09:30:00.127459523  50.1700   100
 3  2024-01-03 09:30:00.128498068  50.1700     5
 4  2024-01-03 09:30:00.135190071  50.1300    46
 ..                           ...      ...   ...
 95 2024-01-03 09:30:00.897416820  50.0800    51
 96 2024-01-03 09:30:00.897444128  50.0800    29
 97 2024-01-03 09:30:00.897474806  50.0800    12
 98 2024-01-03 09:30:00.897476583  50.0800     2
 99 2024-01-03 09:30:00.897670983  50.0797   100
 
 [100 rows x 3 columns],
 'MSFT':                             Time    PRICE  SIZE
 0  2024-01-03 09:30:00.001141139  368.990     9
 1  2024-01-03 09:30:00.001232095  369.000    10
 2  2024-01-03 09:30:00.001348140  369.000    90
 3  2024-01-03 09:30:00.001835253  368.995     1
 4  2024-01-03 09:30:00.002218671  368.990   100
 ..                           ...      ...   ...
 95 2024-01-03 09:30:00.382076572  369.000     1
 96 2024-01-03 09:30:00.396730897  369.095     3
 97 2024-01-03 09:30:00.396780282  369.095     3
 98 2024-01-03 09:30:00.405154725  369.120     4
 99 2024-01-03 09:30:00.427825042  369.050    20
 
 [100 rows x 3 columns]}
```

## Identify Trading Days Half Day

Fetch daily bar data for `HD` stock, filtered by market activity and exchange,
then retrieve the closing price and activity code for January 2024.

```ipython3
import onetick.py as otp

# Define the data source for daily bars
data = otp.DataSource(
    db='US_COMP_SAMPLE_DAILY',
    tick_type='DAY',
    schema_policy='manual',
    schema={'CLOSE': float, 'EXCHANGE': str}
)

# Market Activity column returns R [Regular], L [Half Day], H [Holiday], W [Weekend]
data = data.mkt_activity('CLOUD_DB_US_COMP')

# Filter for EXCHANGE == ''
data = data.where(data['EXCHANGE'] == '')

# Select only the CLOSE and ACTIVITY_CODE columns
data = data[['CLOSE', 'MKT_ACTIVITY']]

# Return first 100 Rows
data = data.limit(100)

# Run the query for symbol 'HD' and the specified time range
result = otp.run(
    data,
    symbols=['HD'],
    start=otp.dt(2024, 1, 1),
    end=otp.dt(2024, 2, 1),
    timezone='UTC'
)
result
```

```myst-ansi
                  Time   CLOSE MKT_ACTIVITY
0  2024-01-03 01:15:00  345.08            R
1  2024-01-04 01:15:00  338.26            R
2  2024-01-05 01:15:00  338.59            R
3  2024-01-06 01:15:00  342.94            R
4  2024-01-09 01:15:00  347.93            R
..                 ...     ...          ...
15 2024-01-25 01:15:00  347.27            R
16 2024-01-26 01:15:00  350.97            R
17 2024-01-27 01:15:00  355.30            R
18 2024-01-30 01:15:00  355.70            R
19 2024-01-31 01:15:00  357.10            R

[20 rows x 3 columns]
```

## Join Trades to Prevailing Quotes

Retrieve trades joined to prevailing quotes based on an asof join.<br />
\\\\
Both Trade and Quote Data sources are defined, and then joined by time using
[`otp.join_by_time`](https://docs.pip.distribution.sol.onetick.com/api/functions/join_by_time.html.md#onetick.py.join_by_time).

```ipython3
import onetick.py as otp

trd = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
trd = trd[['PRICE', 'SIZE']]
qte = otp.DataSource(db='US_COMP_SAMPLE', tick_type='QTE')
qte = qte[['BID_PRICE', 'ASK_PRICE']]
data = otp.join_by_time([trd, qte])

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time    PRICE  SIZE  BID_PRICE  ASK_PRICE
0  2024-01-03 09:30:00.065443591  50.0200     2      49.50      50.18
1  2024-01-03 09:30:00.111130049  50.1600     3      49.98      50.19
2  2024-01-03 09:30:00.127459523  50.1700   100      49.98      50.19
3  2024-01-03 09:30:00.128498068  50.1700     5      50.00      50.17
4  2024-01-03 09:30:00.135190071  50.1300    46      50.00      50.17
..                           ...      ...   ...        ...        ...
95 2024-01-03 09:30:00.897416820  50.0800    51      50.05      50.12
96 2024-01-03 09:30:00.897444128  50.0800    29      50.05      50.12
97 2024-01-03 09:30:00.897474806  50.0800    12      50.05      50.10
98 2024-01-03 09:30:00.897476583  50.0800     2      50.05      50.10
99 2024-01-03 09:30:00.897670983  50.0797   100      50.06      50.12

[100 rows x 5 columns]
```

## Prevailing Price

Retrieve prevailing trade price for `CSCO` on database `US_COMP_SAMPLE` at a specified timestamp.<br />
\\\\
Looking back 1 Day (86400 seconds) in cases where the trade does not occur at the exact timestamp.

```ipython3
import onetick.py as otp

# Define the Data Source with back_to_first_tick
data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD', back_to_first_tick=86400)

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time EXCHANGE  COND STOP_STOCK SOURCE TRF TTE  \
0  2024-01-03 09:30:00.000000000        Q  @FT                  N       1   
1  2024-01-03 09:30:00.065443591        Z  @  I                 N       0   
2  2024-01-03 09:30:00.111130049        Z  @  I                 N       0   
3  2024-01-03 09:30:00.127459523        V  @                    N       0   
4  2024-01-03 09:30:00.128498068        Z  @F I                 N       1   
..                           ...      ...   ...        ...    ...  ..  ..   
95 2024-01-03 09:30:00.897235112        U  @F                   N       1   
96 2024-01-03 09:30:00.897416820        Q  @F I                 N       1   
97 2024-01-03 09:30:00.897444128        Q  @F I                 N       1   
98 2024-01-03 09:30:00.897474806        Q  @F I                 N       1   
99 2024-01-03 09:30:00.897476583        Q  @F I                 N       1   

   TICKER  PRICE        DELETED_TIME  TICK_STATUS  SIZE  CORR  SEQ_NUM  \
0    CSCO  50.16 1969-12-31 19:00:00            0   471     0   168866   
1    CSCO  50.02 1969-12-31 19:00:00            0     2     0   169103   
2    CSCO  50.16 1969-12-31 19:00:00            0     3     0   169148   
3    CSCO  50.17 1969-12-31 19:00:00            0   100     0   169181   
4    CSCO  50.17 1969-12-31 19:00:00            0     5     0   169182   
..    ...    ...                 ...          ...   ...   ...      ...   
95   CSCO  50.07 1969-12-31 19:00:00            0   200     0   171855   
96   CSCO  50.08 1969-12-31 19:00:00            0    51     0   171857   
97   CSCO  50.08 1969-12-31 19:00:00            0    29     0   171858   
98   CSCO  50.08 1969-12-31 19:00:00            0    12     0   171859   
99   CSCO  50.08 1969-12-31 19:00:00            0     2     0   171860   

   TRADE_ID              PARTICIPANT_TIME            TRF_TIME  OMDSEQ  
0       348 2024-01-03 09:29:59.990923814 1969-12-31 19:00:00       1  
1        42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00       0  
2        43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00       0  
3        13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00       0  
4        44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00       0  
..      ...                           ...                 ...     ...  
95       22 2024-01-03 09:30:00.897017316 1969-12-31 19:00:00       2  
96      365 2024-01-03 09:30:00.897398456 1969-12-31 19:00:00       3  
97      366 2024-01-03 09:30:00.897428791 1969-12-31 19:00:00       4  
98      367 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00       5  
99      368 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00       6  

[100 rows x 18 columns]
```

## Query Data Availability Status

Retrieve Completed Data Load Events for `US_COMP` Database.<br />
\\\\
Returns load completion events from the last 7 days.

```ipython3
import onetick.py as otp

# Calculate time window: last 7 days to now
now = otp.now()
seven_days_ago = now - otp.Day(7)

# Create data source for DB_INFO.PROC_EVENTS
proc_events = otp.DataSource(
    db='DB_INFO',
    tick_type='PROC_EVENTS'
)

# Filter for US_COMP database, successful load events, and events from the last 7 days
proc_events = proc_events.where(
    (proc_events['DB_NAME'] == 'US_COMP') &
    (proc_events['EVENT_NAME'] == 'Load finished')
)

# Run the query for the specified time window and output as a dataframe
result = otp.run(
    proc_events,
    symbols='US_COMP',
    start=seven_days_ago,
    end=now,
    timezone='UTC',
)
result
```

```myst-ansi
        Time  DB_NAME                       DB_DESCRIPTION          TIMEZONE  \
0 2026-08-04  US_COMP  US Consolidated Equities (exc. OTC)  America/New_York   
1 2026-08-05  US_COMP  US Consolidated Equities (exc. OTC)  America/New_York   
2 2026-08-06  US_COMP  US Consolidated Equities (exc. OTC)  America/New_York   
3 2026-08-07  US_COMP  US Consolidated Equities (exc. OTC)  America/New_York   

  DATA_DATE          EVENT_TIME     EVENT_NAME SOURCE_FILE_NAME  OMDSEQ  
0  20260804 2026-08-05 02:15:14  Load finished                     5006  
1  20260805 2026-08-06 02:38:26  Load finished                     4991  
2  20260806 2026-08-07 02:43:32  Load finished                     4956  
3  20260807 2026-08-08 02:38:16  Load finished                     4887  
```

## Ranking

Retrieve trades ranked by price for `CSCO` from the `US_COMP_SAMPLE` database, across the specified time range.

```ipython3
import onetick.py as otp

data = otp.DataSource(db='US_COMP_SAMPLE', tick_type='TRD')
data = data[['PRICE', 'SIZE']]
data = data.ranking({'SIZE': 'desc'})

# Return first 100 Rows
data = data.limit(100)

result = otp.run(data,
                 start=otp.dt(2024, 1, 3, 9, 30),
                 end=otp.dt(2024, 1, 3, 9, 40),
                 timezone='America/New_York',
                 symbols='CSCO')
result
```

```myst-ansi
                            Time    PRICE  SIZE  RANKING
0  2024-01-03 09:30:00.065443591  50.0200     2     3867
1  2024-01-03 09:30:00.111130049  50.1600     3     3817
2  2024-01-03 09:30:00.127459523  50.1700   100      684
3  2024-01-03 09:30:00.128498068  50.1700     5     3664
4  2024-01-03 09:30:00.135190071  50.1300    46     2580
..                           ...      ...   ...      ...
95 2024-01-03 09:30:00.897416820  50.0800    51     2401
96 2024-01-03 09:30:00.897444128  50.0800    29     2818
97 2024-01-03 09:30:00.897474806  50.0800    12     3270
98 2024-01-03 09:30:00.897476583  50.0800     2     3867
99 2024-01-03 09:30:00.897670983  50.0797   100      684

[100 rows x 4 columns]
```

## Run SQL

Execute a SQL statement using [`otp.SqlQuery`](https://docs.pip.distribution.sol.onetick.com/api/misc/sql.html.md#onetick.py.SqlQuery).

```ipython3
import onetick.py as otp

sql_statement = """
    SELECT * 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 09:40:00 America/New_York'
    LIMIT 100
"""

sql_query = otp.SqlQuery(sql_statement)
result = otp.run(sql_query)
result
```

```myst-ansi
                            Time EXCHANGE  COND STOP_STOCK SOURCE TRF TTE  \
0  2024-01-03 09:30:00.065443591        Z  @  I                 N       0   
1  2024-01-03 09:30:00.111130049        Z  @  I                 N       0   
2  2024-01-03 09:30:00.127459523        V  @                    N       0   
3  2024-01-03 09:30:00.128498068        Z  @F I                 N       1   
4  2024-01-03 09:30:00.135190071        Q  @FTI                 N       1   
..                           ...      ...   ...        ...    ...  ..  ..   
95 2024-01-03 09:30:00.897416820        Q  @F I                 N       1   
96 2024-01-03 09:30:00.897444128        Q  @F I                 N       1   
97 2024-01-03 09:30:00.897474806        Q  @F I                 N       1   
98 2024-01-03 09:30:00.897476583        Q  @F I                 N       1   
99 2024-01-03 09:30:00.897670983        D  @                    N   Q   0   

   TICKER    PRICE        DELETED_TIME  TICK_STATUS  SIZE  CORR  SEQ_NUM  \
0    CSCO  50.0200 1969-12-31 19:00:00            0     2     0   169103   
1    CSCO  50.1600 1969-12-31 19:00:00            0     3     0   169148   
2    CSCO  50.1700 1969-12-31 19:00:00            0   100     0   169181   
3    CSCO  50.1700 1969-12-31 19:00:00            0     5     0   169182   
4    CSCO  50.1300 1969-12-31 19:00:00            0    46     0   169193   
..    ...      ...                 ...          ...   ...   ...      ...   
95   CSCO  50.0800 1969-12-31 19:00:00            0    51     0   171857   
96   CSCO  50.0800 1969-12-31 19:00:00            0    29     0   171858   
97   CSCO  50.0800 1969-12-31 19:00:00            0    12     0   171859   
98   CSCO  50.0800 1969-12-31 19:00:00            0     2     0   171860   
99   CSCO  50.0797 1969-12-31 19:00:00            0   100     0   171862   

   TRADE_ID              PARTICIPANT_TIME                      TRF_TIME  \
0        42 2024-01-03 09:30:00.065250000 1969-12-31 19:00:00.000000000   
1        43 2024-01-03 09:30:00.110938000 1969-12-31 19:00:00.000000000   
2        13 2024-01-03 09:30:00.065044730 1969-12-31 19:00:00.000000000   
3        44 2024-01-03 09:30:00.128306000 1969-12-31 19:00:00.000000000   
4       349 2024-01-03 09:30:00.135173836 1969-12-31 19:00:00.000000000   
..      ...                           ...                           ...   
95      365 2024-01-03 09:30:00.897398456 1969-12-31 19:00:00.000000000   
96      366 2024-01-03 09:30:00.897428791 1969-12-31 19:00:00.000000000   
97      367 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000   
98      368 2024-01-03 09:30:00.897456284 1969-12-31 19:00:00.000000000   
99      100 2024-01-03 09:30:00.896374950 2024-01-03 09:30:00.897637536   

    OMDSEQ SYMBOL_NAME TICK_TYPE  
0        0        CSCO       TRD  
1        0        CSCO       TRD  
2        0        CSCO       TRD  
3        0        CSCO       TRD  
4        0        CSCO       TRD  
..     ...         ...       ...  
95       3        CSCO       TRD  
96       4        CSCO       TRD  
97       5        CSCO       TRD  
98       6        CSCO       TRD  
99       7        CSCO       TRD  

[100 rows x 20 columns]
```