ETF#
This section contains 4 examples for ETF using the onetick-py.
Each example is a self-contained script that can be run against the OneTick Cloud sample databases.
# 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__'
ETF Cash#
Query OQD_ETF cash flow data for SPY using Bloomberg symbology.
Returns first 100 cash flow records for specified time range.
import onetick.py as otp
# Define the data source for OQD_ETF.CASH
source = otp.DataSource(
db='OQD_ETF', # OQD ETF database
tick_type='CASH', # Cash flow tick type
schema_policy='manual', # Manually define schema
schema=None, # Retrieve all available columns
)
# Limit to first 100 rows
source = source.limit(100)
# Define the time range in UTC
start_time = otp.dt(2024, 7, 8, tz='UTC') # Start: July 8, 2024 at 00:00 UTC
end_time = otp.dt(2024, 8, 9, tz='UTC') # End: August 9, 2024 at 00:00 UTC
# Run the query using Bloomberg symbol (BTKR symbology)
result = otp.run(
source, # Cash flow data source
symbols='BTKR::::SPY US', # SPY in Bloomberg symbology
symbol_date=end_time, # Date for symbology mapping
start=start_time, # Query start time
end=end_time, # Query end time
timezone='America/New_York', # Use New York timezone
)
result # Display results
| Time | CURRENCY | ESTIMATED_CASH | TOTAL_CASH | DELETED_TIME | TICK_STATUS | OMDSEQ | |
|---|---|---|---|---|---|---|---|
| 0 | 2024-07-07 20:00:00 | USD | 10542.34 | 9426.97 | 1969-12-31 19:00:00 | 0 | 508 |
| 1 | 2024-07-08 20:00:00 | USD | 12423.73 | 10584.38 | 1969-12-31 19:00:00 | 0 | 508 |
| 2 | 2024-07-09 20:00:00 | USD | 15677.50 | 12424.79 | 1969-12-31 19:00:00 | 0 | 508 |
| 3 | 2024-07-10 20:00:00 | USD | 16657.40 | 15631.46 | 1969-12-31 19:00:00 | 0 | 508 |
| 4 | 2024-07-11 20:00:00 | USD | 16124.77 | 16799.50 | 1969-12-31 19:00:00 | 0 | 508 |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 19 | 2024-08-01 20:00:00 | USD | 27335.61 | 27116.87 | 1969-12-31 19:00:00 | 0 | 508 |
| 20 | 2024-08-04 20:00:00 | USD | 28409.26 | 27468.39 | 1969-12-31 19:00:00 | 0 | 508 |
| 21 | 2024-08-05 20:00:00 | USD | 28742.63 | 28448.94 | 1969-12-31 19:00:00 | 0 | 508 |
| 22 | 2024-08-06 20:00:00 | USD | 29582.47 | 28704.96 | 1969-12-31 19:00:00 | 0 | 508 |
| 23 | 2024-08-07 20:00:00 | USD | 29765.11 | 29616.92 | 1969-12-31 19:00:00 | 0 | 508 |
24 rows × 7 columns
ETF Constituents#
Query OQD_ETF constituent holdings for SPY.
Returns first 100 constituents as of August 9, 2024 using Bloomberg symbology.
import onetick.py as otp
# Define the data source for ETF constituents
constituents = otp.DataSource(
db='OQD_ETF', # OQD ETF database
tick_type='CONSTITUENTS', # ETF constituents tick type
schema_policy='manual', # Manually define schema
schema=None, # Retrieve all available columns
)
# Limit to first 100 rows (optional)
constituents = constituents.limit(100)
# Define the time range in UTC
start_time = otp.dt(2024, 7, 8, tz='UTC') # Start: July 8, 2024
end_time = otp.dt(2024, 8, 9, tz='UTC') # End: August 9, 2024
# Define the symbol date (as of date for constituent holdings)
symbol_date=otp.dt(2024, 8, 9, tz='UTC') # Get constituents as of August 9, 2024
# Run the query for SPY constituents using Bloomberg symbol
result = otp.run(
constituents, # ETF constituents data source
symbols='BTKR::::SPY US', # SPY in Bloomberg symbology
symbol_date=symbol_date, # Date for constituent snapshot
start=start_time, # Query start time
end=end_time, # Query end time
timezone='America/New_York', # Use New York timezone
)
result # Display results
| Time | TICKER | NAME | BSYM | FIGI | FIGI_COMP | MIC | OPERATING_MIC | ULT_ID | OID | DELETED_TIME | TICK_STATUS | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-07-07 20:00:00 | A | AGILENT TECHNOLOGIES ORD | A UN Equity | BBG000C2V541 | BBG000C2V3D6 | XNYS | XNYS | 300000000000019865 | 3751 | 1969-12-31 19:00:00 | 0 | 225 |
| 1 | 2024-07-07 20:00:00 | ADM | ARCHER DANIELS MIDLAND ORD | ADM UN Equity | BBG000BB7059 | BBG000BB6WG8 | XNYS | XNYS | 300000000000019868 | 10030 | 1969-12-31 19:00:00 | 0 | 266 |
| 2 | 2024-07-07 20:00:00 | ANET | ARISTA NETWORKS ORD | ANET UN Equity | BBG0068J0HN3 | BBG000N2HDY5 | XNYS | XNYS | 300000000000019877 | 602316 | 1969-12-31 19:00:00 | 0 | 100 |
| 3 | 2024-07-07 20:00:00 | ABBV | ABBVIE ORD | ABBV UN Equity | BBG003P7K0B5 | BBG0025Y4RY4 | XNYS | XNYS | 300000000000019890 | 289689 | 1969-12-31 19:00:00 | 0 | 28 |
| 4 | 2024-07-07 20:00:00 | COR | CENCORA ORD | COR UN Equity | BBG000MDCSN6 | BBG000MDCQC2 | XNYS | XNYS | 300000000000019892 | 8691 | 1969-12-31 19:00:00 | 0 | 232 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-07-07 20:00:00 | FDX | FEDEX ORD | FDX UN Equity | BBG000BJF3F6 | BBG000BJF1Z8 | XNYS | XNYS | 300000000000020697 | 60963 | 1969-12-31 19:00:00 | 0 | 137 |
| 96 | 2024-07-07 20:00:00 | FE | FIRSTENERGY ORD | FE UN Equity | BBG000BB6PD6 | BBG000BB6M98 | XNYS | XNYS | 300000000000020700 | 67317 | 1969-12-31 19:00:00 | 0 | 345 |
| 97 | 2024-07-07 20:00:00 | DFS | DISCOVER FINANCIAL SERVICES ORD | DFS UN Equity | BBG000QBR6K1 | BBG000QBR5J5 | XNYS | XNYS | 300000000000020716 | 38887 | 1969-12-31 19:00:00 | 0 | 261 |
| 98 | 2024-07-07 20:00:00 | NEE | NEXTERA ENERGY ORD | NEE UN Equity | BBG000BJSF01 | BBG000BJSBJ0 | XNYS | XNYS | 300000000000020724 | 50467 | 1969-12-31 19:00:00 | 0 | 62 |
| 99 | 2024-07-07 20:00:00 | DG | DOLLAR GENERAL ORD | DG UN Equity | BBG000NV1PS8 | BBG000NV1KK7 | XNYS | XNYS | 300000000000020726 | 243829 | 1969-12-31 19:00:00 | 0 | 288 |
100 rows × 13 columns
ETF PCF#
Query OQD_ETF portfolio composition (PCF) data for SPY.
Returns portfolio holdings and composition details using Bloomberg symbology.
import onetick.py as otp
# Define the time range
start = otp.dt(2024, 8, 8) # Start: August 8, 2024 at midnight
end = otp.dt(2024, 8, 9) # End: August 9, 2024 at midnight
# Define the PCF data source
pcf_data = otp.DataSource(
db='OQD_ETF', # OQD ETF database
tick_type='PCF', # Portfolio Composition File tick type
)
# Run the query using Bloomberg symbology with symbol_date
symbol_date=otp.dt(2024, 8, 8) # Date for symbology mapping
result = otp.run(
pcf_data, # Portfolio composition data source
symbols='BTKR::::SPY US', # SPY in Bloomberg symbology
symbol_date=symbol_date, # Date for symbol resolution
start=start, # Query start time
end=end, # Query end time
timezone='America/New_York', # Use New York timezone
)
result # Display results
| Time | TICKER | NAME | BSYM | FIGI | FIGI_COMP | MIC | OPERATING_MIC | ULT_ID | OID | ... | NUMBER_OF_UNITS | FX_RATE | CALC_PRICE | CALC_CCY | CLOSE | CLOSE_CCY | PRICE_DATE | DELETED_TIME | TICK_STATUS | OMDSEQ | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-08-08 20:00:00 | A | AGILENT TECHNOLOGIES ORD | A UN Equity | BBG000C2V541 | BBG000C2V3D6 | XNYS | XNYS | 300000000000019865 | 3751 | ... | 174.0 | 1.0 | 136.76 | USD | 136.76 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 214 |
| 1 | 2024-08-08 20:00:00 | ADM | ARCHER DANIELS MIDLAND ORD | ADM UN Equity | BBG000BB7059 | BBG000BB6WG8 | XNYS | XNYS | 300000000000019868 | 10030 | ... | 293.0 | 1.0 | 58.48 | USD | 58.48 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 278 |
| 2 | 2024-08-08 20:00:00 | ANET | ARISTA NETWORKS ORD | ANET UN Equity | BBG0068J0HN3 | BBG000N2HDY5 | XNYS | XNYS | 300000000000019877 | 602316 | ... | 151.0 | 1.0 | 334.50 | USD | 334.50 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 114 |
| 3 | 2024-08-08 20:00:00 | ABBV | ABBVIE ORD | ABBV UN Equity | BBG003P7K0B5 | BBG0025Y4RY4 | XNYS | XNYS | 300000000000019890 | 289689 | ... | 1047.0 | 1.0 | 190.40 | USD | 190.40 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 25 |
| 4 | 2024-08-08 20:00:00 | COR | CENCORA ORD | COR UN Equity | BBG000MDCSN6 | BBG000MDCQC2 | XNYS | XNYS | 300000000000019892 | 8691 | ... | 98.0 | 1.0 | 237.00 | USD | 237.00 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 220 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 498 | 2024-08-08 20:00:00 | KVUE | KENVUE ORD | KVUE UN Equity | BBG01C79X5B5 | BBG01C79X561 | XNYS | XNYS | 300000000059893574 | 724649 | ... | 1135.0 | 1.0 | 21.25 | USD | 21.25 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 211 |
| 499 | 2024-08-08 20:00:00 | VLTO | VERALTO ORD | VLTO UN Equity | BBG01J3HMZ77 | BBG019Q32XJ9 | XNYS | XNYS | 300000000073911000 | 727227 | ... | 130.0 | 1.0 | 107.24 | USD | 107.24 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 322 |
| 500 | 2024-08-08 20:00:00 | GEV | GE VERNOVA ORD | GEV UN Equity | BBG01LT2THM3 | BBG013G17W31 | XNYS | XNYS | 300000000082313395 | 731283 | ... | 163.0 | 1.0 | 177.21 | USD | 177.21 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 183 |
| 501 | 2024-08-08 20:00:00 | SOLV | SOLVENTUM ORD | SOLV UN Equity | BBG01LWZSVQ2 | BBG018YZH6T3 | XNYS | XNYS | 300000000083274358 | 731254 | ... | 82.0 | 1.0 | 58.66 | USD | 58.66 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 486 |
| 502 | 2024-08-08 20:00:00 | SW | SMURFIT WESTROCK ORD | SW UN Equity | BBG01NK9ZXZ7 | BBG01NK9ZXT4 | XNYS | XNYS | 300000000088560582 | 733171 | ... | 308.0 | 1.0 | 39.31 | USD | 39.31 | USD | 20240808 | 1969-12-31 19:00:00 | 0 | 341 |
503 rows × 22 columns
ETF Universe#
Query OQD_ETF description/reference data for all ETF symbols.
Returns all available ETF symbol descriptions and metadata.
import onetick.py as otp
# Define the time range
start = otp.dt(2024, 8, 8) # Start: August 8, 2024 at midnight
end = otp.dt(2024, 8, 9) # End: August 9, 2024 at midnight
symbols = otp.Symbols(
db='OQD_ETF', # OQD ETF database
pattern='%', # Pattern mask to match all symbols
for_tick_type='DES', # For DES (description) tick type
)
# Define the DES data source containing reference/description data for ETF symbols
des_data = otp.DataSource(
db='OQD_ETF', # OQD ETF database
tick_type='DES', # Description/reference data tick type
)
# merge all ETF symbols into one stream
des_data = otp.merge([des_data], symbols=symbols, identify_input_ts=True)
# Return first 100 Rows
des_data = des_data.limit(100)
# Run the query
result = otp.run(
des_data, # Description data source
start=start, # Query start time
end=end, # Query end time
timezone='America/New_York', # Query timezone
)
result # Display results
| Time | BBG | CALC_UNIT_SIZE | CREATION_UNIT_SIZE | CURRENCY | DATA_SOURCE | DELETED_TIME | DESCRIPTION | NAV_DATE | OMDSEQ | REF_DATE | SHARES_OUT | TICKER | TICK_STATUS | SYMBOL_NAME | TICK_TYPE | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 2024-08-08 20:00:00 | CBLS UP | 10000.0 | 10000.0 | USD | DTCC | 1969-12-31 19:00:00 | CLOUGH HDG EQ | 20240808 | 0 | 20240809 | 1390000.0 | CBLS | 0 | 697720 | DES |
| 1 | 2024-08-08 20:00:00 | PEZ UQ | 10000.0 | 10000.0 | USD | INVESCO | 1969-12-31 19:00:00 | INVESCO DORSEY WRIGHT CON CYCLICALS MOM | 20240808 | 6 | 20240809 | 740000.0 | PEZ | 0 | 154832 | DES |
| 2 | 2024-08-08 20:00:00 | PBW UP | 10000.0 | 10000.0 | USD | INVESCO | 1969-12-31 19:00:00 | INVESCO WILDERHILL CLEAN ERGY | 20240808 | 6 | 20240809 | 15818273.0 | PBW | 0 | 154841 | DES |
| 3 | 2024-08-08 20:00:00 | PRFZ UQ | 10000.0 | 10000.0 | USD | INVESCO | 1969-12-31 19:00:00 | INVESCO FTSE RAFI US 1500 SM MID | 20240808 | 6 | 20240809 | 61460000.0 | PRFZ | 0 | 154847 | DES |
| 4 | 2024-08-08 20:00:00 | UDN UP | 50000.0 | 50000.0 | USD | INVESCO | 1969-12-31 19:00:00 | INVESCO DB USD IDX BEAR FUND | 20240808 | 6 | 20240809 | 2850040.0 | UDN | 0 | 154896 | DES |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 95 | 2024-08-08 20:00:00 | LQDI UF | 50000.0 | 50000.0 | USD | ISHARES | 1969-12-31 19:00:00 | ISHARES INFL HDG CORP BD | 20240808 | 6 | 20240809 | 2000000.0 | LQDI | 0 | 669323 | DES |
| 96 | 2024-08-08 20:00:00 | JUST US | 25000.0 | 25000.0 | USD | DTCC | 1969-12-31 19:00:00 | GS JUST US LRG CAP EQ | 20240808 | 6 | 20240809 | 4525000.0 | JUST | 0 | 670279 | DES |
| 97 | 2024-08-08 20:00:00 | BBRE UF | 25000.0 | 25000.0 | USD | JPM | 1969-12-31 19:00:00 | JPMORGAN BETABUILDERS MSCI US REIT | 20240808 | 6 | 20240809 | 9275000.0 | BBRE | 0 | 670385 | DES |
| 98 | 2024-08-08 20:00:00 | ONLN UP | 10000.0 | 10000.0 | USD | DTCC | 1969-12-31 19:00:00 | PROSHARES ONLINE RETAIL | 20240808 | 6 | 20240809 | 2140001.0 | ONLN | 0 | 672626 | DES |
| 99 | 2024-08-08 20:00:00 | UTRN US | 50000.0 | 50000.0 | USD | DTCC | 1969-12-31 19:00:00 | VESPER US LRG CAP SHRT-TRM REVERSAL STRAT | 20240808 | 6 | 20240809 | 850000.0 | UTRN | 0 | 674530 | DES |
100 rows × 16 columns