# 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.
```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__'
```
## ETF Cash
Query `OQD_ETF` cash flow data for `SPY` using Bloomberg symbology.
\\\\
Returns first 100 cash flow records for specified time range.
```ipython3
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
```
```myst-ansi
Time CURRENCY ESTIMATED_CASH TOTAL_CASH \
0 2024-07-07 20:00:00 USD 10542.34 9426.97
1 2024-07-08 20:00:00 USD 12423.73 10584.38
2 2024-07-09 20:00:00 USD 15677.50 12424.79
3 2024-07-10 20:00:00 USD 16657.40 15631.46
4 2024-07-11 20:00:00 USD 16124.77 16799.50
.. ... ... ... ...
19 2024-08-01 20:00:00 USD 27335.61 27116.87
20 2024-08-04 20:00:00 USD 28409.26 27468.39
21 2024-08-05 20:00:00 USD 28742.63 28448.94
22 2024-08-06 20:00:00 USD 29582.47 28704.96
23 2024-08-07 20:00:00 USD 29765.11 29616.92
DELETED_TIME TICK_STATUS OMDSEQ
0 1969-12-31 19:00:00 0 508
1 1969-12-31 19:00:00 0 508
2 1969-12-31 19:00:00 0 508
3 1969-12-31 19:00:00 0 508
4 1969-12-31 19:00:00 0 508
.. ... ... ...
19 1969-12-31 19:00:00 0 508
20 1969-12-31 19:00:00 0 508
21 1969-12-31 19:00:00 0 508
22 1969-12-31 19:00:00 0 508
23 1969-12-31 19:00:00 0 508
[24 rows x 7 columns]
```
## ETF Constituents
Query `OQD_ETF` constituent holdings for `SPY`.
\\\\
Returns first 100 constituents as of August 9, 2024 using Bloomberg symbology.
```ipython3
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
```
```myst-ansi
Time TICKER NAME \
0 2024-07-07 20:00:00 A AGILENT TECHNOLOGIES ORD
1 2024-07-07 20:00:00 ADM ARCHER DANIELS MIDLAND ORD
2 2024-07-07 20:00:00 ANET ARISTA NETWORKS ORD
3 2024-07-07 20:00:00 ABBV ABBVIE ORD
4 2024-07-07 20:00:00 COR CENCORA ORD
.. ... ... ...
95 2024-07-07 20:00:00 FDX FEDEX ORD
96 2024-07-07 20:00:00 FE FIRSTENERGY ORD
97 2024-07-07 20:00:00 DFS DISCOVER FINANCIAL SERVICES ORD
98 2024-07-07 20:00:00 NEE NEXTERA ENERGY ORD
99 2024-07-07 20:00:00 DG DOLLAR GENERAL ORD
BSYM FIGI FIGI_COMP MIC OPERATING_MIC \
0 A UN Equity BBG000C2V541 BBG000C2V3D6 XNYS XNYS
1 ADM UN Equity BBG000BB7059 BBG000BB6WG8 XNYS XNYS
2 ANET UN Equity BBG0068J0HN3 BBG000N2HDY5 XNYS XNYS
3 ABBV UN Equity BBG003P7K0B5 BBG0025Y4RY4 XNYS XNYS
4 COR UN Equity BBG000MDCSN6 BBG000MDCQC2 XNYS XNYS
.. ... ... ... ... ...
95 FDX UN Equity BBG000BJF3F6 BBG000BJF1Z8 XNYS XNYS
96 FE UN Equity BBG000BB6PD6 BBG000BB6M98 XNYS XNYS
97 DFS UN Equity BBG000QBR6K1 BBG000QBR5J5 XNYS XNYS
98 NEE UN Equity BBG000BJSF01 BBG000BJSBJ0 XNYS XNYS
99 DG UN Equity BBG000NV1PS8 BBG000NV1KK7 XNYS XNYS
ULT_ID OID DELETED_TIME TICK_STATUS OMDSEQ
0 300000000000019865 3751 1969-12-31 19:00:00 0 225
1 300000000000019868 10030 1969-12-31 19:00:00 0 266
2 300000000000019877 602316 1969-12-31 19:00:00 0 100
3 300000000000019890 289689 1969-12-31 19:00:00 0 28
4 300000000000019892 8691 1969-12-31 19:00:00 0 232
.. ... ... ... ... ...
95 300000000000020697 60963 1969-12-31 19:00:00 0 137
96 300000000000020700 67317 1969-12-31 19:00:00 0 345
97 300000000000020716 38887 1969-12-31 19:00:00 0 261
98 300000000000020724 50467 1969-12-31 19:00:00 0 62
99 300000000000020726 243829 1969-12-31 19:00:00 0 288
[100 rows x 13 columns]
```
## ETF PCF
Query `OQD_ETF` portfolio composition (`PCF`) data for `SPY`.
\\\\
Returns portfolio holdings and composition details using Bloomberg symbology.
```ipython3
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
```
```myst-ansi
Time TICKER NAME BSYM \
0 2024-08-08 20:00:00 A AGILENT TECHNOLOGIES ORD A UN Equity
1 2024-08-08 20:00:00 ADM ARCHER DANIELS MIDLAND ORD ADM UN Equity
2 2024-08-08 20:00:00 ANET ARISTA NETWORKS ORD ANET UN Equity
3 2024-08-08 20:00:00 ABBV ABBVIE ORD ABBV UN Equity
4 2024-08-08 20:00:00 COR CENCORA ORD COR UN Equity
.. ... ... ... ...
498 2024-08-08 20:00:00 KVUE KENVUE ORD KVUE UN Equity
499 2024-08-08 20:00:00 VLTO VERALTO ORD VLTO UN Equity
500 2024-08-08 20:00:00 GEV GE VERNOVA ORD GEV UN Equity
501 2024-08-08 20:00:00 SOLV SOLVENTUM ORD SOLV UN Equity
502 2024-08-08 20:00:00 SW SMURFIT WESTROCK ORD SW UN Equity
FIGI FIGI_COMP MIC OPERATING_MIC ULT_ID \
0 BBG000C2V541 BBG000C2V3D6 XNYS XNYS 300000000000019865
1 BBG000BB7059 BBG000BB6WG8 XNYS XNYS 300000000000019868
2 BBG0068J0HN3 BBG000N2HDY5 XNYS XNYS 300000000000019877
3 BBG003P7K0B5 BBG0025Y4RY4 XNYS XNYS 300000000000019890
4 BBG000MDCSN6 BBG000MDCQC2 XNYS XNYS 300000000000019892
.. ... ... ... ... ...
498 BBG01C79X5B5 BBG01C79X561 XNYS XNYS 300000000059893574
499 BBG01J3HMZ77 BBG019Q32XJ9 XNYS XNYS 300000000073911000
500 BBG01LT2THM3 BBG013G17W31 XNYS XNYS 300000000082313395
501 BBG01LWZSVQ2 BBG018YZH6T3 XNYS XNYS 300000000083274358
502 BBG01NK9ZXZ7 BBG01NK9ZXT4 XNYS XNYS 300000000088560582
OID ... NUMBER_OF_UNITS FX_RATE CALC_PRICE CALC_CCY CLOSE \
0 3751 ... 174.0 1.0 136.76 USD 136.76
1 10030 ... 293.0 1.0 58.48 USD 58.48
2 602316 ... 151.0 1.0 334.50 USD 334.50
3 289689 ... 1047.0 1.0 190.40 USD 190.40
4 8691 ... 98.0 1.0 237.00 USD 237.00
.. ... ... ... ... ... ... ...
498 724649 ... 1135.0 1.0 21.25 USD 21.25
499 727227 ... 130.0 1.0 107.24 USD 107.24
500 731283 ... 163.0 1.0 177.21 USD 177.21
501 731254 ... 82.0 1.0 58.66 USD 58.66
502 733171 ... 308.0 1.0 39.31 USD 39.31
CLOSE_CCY PRICE_DATE DELETED_TIME TICK_STATUS OMDSEQ
0 USD 20240808 1969-12-31 19:00:00 0 214
1 USD 20240808 1969-12-31 19:00:00 0 278
2 USD 20240808 1969-12-31 19:00:00 0 114
3 USD 20240808 1969-12-31 19:00:00 0 25
4 USD 20240808 1969-12-31 19:00:00 0 220
.. ... ... ... ... ...
498 USD 20240808 1969-12-31 19:00:00 0 211
499 USD 20240808 1969-12-31 19:00:00 0 322
500 USD 20240808 1969-12-31 19:00:00 0 183
501 USD 20240808 1969-12-31 19:00:00 0 486
502 USD 20240808 1969-12-31 19:00:00 0 341
[503 rows x 22 columns]
```
## ETF Universe
Query `OQD_ETF` description/reference data for all `ETF` symbols.
\\\\
Returns all available `ETF` symbol descriptions and metadata.
```ipython3
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
```
```myst-ansi
Time BBG CALC_UNIT_SIZE CREATION_UNIT_SIZE CURRENCY \
0 2024-08-08 20:00:00 CBLS UP 10000.0 10000.0 USD
1 2024-08-08 20:00:00 PEZ UQ 10000.0 10000.0 USD
2 2024-08-08 20:00:00 PBW UP 10000.0 10000.0 USD
3 2024-08-08 20:00:00 PRFZ UQ 10000.0 10000.0 USD
4 2024-08-08 20:00:00 UDN UP 50000.0 50000.0 USD
.. ... ... ... ... ...
95 2024-08-08 20:00:00 LQDI UF 50000.0 50000.0 USD
96 2024-08-08 20:00:00 JUST US 25000.0 25000.0 USD
97 2024-08-08 20:00:00 BBRE UF 25000.0 25000.0 USD
98 2024-08-08 20:00:00 ONLN UP 10000.0 10000.0 USD
99 2024-08-08 20:00:00 UTRN US 50000.0 50000.0 USD
DATA_SOURCE DELETED_TIME DESCRIPTION \
0 DTCC 1969-12-31 19:00:00 CLOUGH HDG EQ
1 INVESCO 1969-12-31 19:00:00 INVESCO DORSEY WRIGHT CON CYCLICALS MOM
2 INVESCO 1969-12-31 19:00:00 INVESCO WILDERHILL CLEAN ERGY
3 INVESCO 1969-12-31 19:00:00 INVESCO FTSE RAFI US 1500 SM MID
4 INVESCO 1969-12-31 19:00:00 INVESCO DB USD IDX BEAR FUND
.. ... ... ...
95 ISHARES 1969-12-31 19:00:00 ISHARES INFL HDG CORP BD
96 DTCC 1969-12-31 19:00:00 GS JUST US LRG CAP EQ
97 JPM 1969-12-31 19:00:00 JPMORGAN BETABUILDERS MSCI US REIT
98 DTCC 1969-12-31 19:00:00 PROSHARES ONLINE RETAIL
99 DTCC 1969-12-31 19:00:00 VESPER US LRG CAP SHRT-TRM REVERSAL STRAT
NAV_DATE OMDSEQ REF_DATE SHARES_OUT TICKER TICK_STATUS SYMBOL_NAME \
0 20240808 0 20240809 1390000.0 CBLS 0 697720
1 20240808 6 20240809 740000.0 PEZ 0 154832
2 20240808 6 20240809 15818273.0 PBW 0 154841
3 20240808 6 20240809 61460000.0 PRFZ 0 154847
4 20240808 6 20240809 2850040.0 UDN 0 154896
.. ... ... ... ... ... ... ...
95 20240808 6 20240809 2000000.0 LQDI 0 669323
96 20240808 6 20240809 4525000.0 JUST 0 670279
97 20240808 6 20240809 9275000.0 BBRE 0 670385
98 20240808 6 20240809 2140001.0 ONLN 0 672626
99 20240808 6 20240809 850000.0 UTRN 0 674530
TICK_TYPE
0 DES
1 DES
2 DES
3 DES
4 DES
.. ...
95 DES
96 DES
97 DES
98 DES
99 DES
[100 rows x 16 columns]
```