{ "cells": [ { "attachments": {}, "cell_type": "markdown", "id": "ad42f7ef", "metadata": {}, "source": [ "# Data Retrieval" ] }, { "cell_type": "code", "execution_count": 1, "id": "5ca97d77-33b0-4137-b028-8b75598443d7", "metadata": {}, "outputs": [], "source": [ "import onetick.py as otp" ] }, { "cell_type": "code", "execution_count": 2, "id": "ac3bc9d1-a7fa-442a-a296-85915dd6b734", "metadata": {}, "outputs": [], "source": [ "otp.config['tz'] = 'EST5EDT'" ] }, { "cell_type": "markdown", "id": "1ceb704d-c4cd-4cc3-965a-b8596bde08d0", "metadata": {}, "source": [ "OneTick is a time series database meaning that each record has a timestamp and timestamps of consecutive records are non-decreasing. Multiple time series are stored in OneTick. An individual time series is identified by the symbol (aka ticker, financial instrument, security), tick type (i.e., the type of data such as trades or quotes), and the name of the database where the time series is stored." ] }, { "cell_type": "code", "execution_count": 3, "id": "fa14e1fa-a1c7-458b-90f5-4741058ea8ac", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['ACTIV_ALPHA', 'ACTIV_CANADA', 'ACTIV_CSE', 'ACTIV_CSE2', 'ACTIV_CX2']" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# you can retrieve the list of databases available\n", "dbs = otp.databases()\n", "list(dbs)[:5]" ] }, { "cell_type": "code", "execution_count": 4, "id": "01eb7800-2d4f-43a9-8835-f1062e87bba2", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "[datetime.date(2023, 4, 7),\n", " datetime.date(2023, 4, 8),\n", " datetime.date(2023, 4, 9),\n", " datetime.date(2023, 4, 10),\n", " datetime.date(2023, 4, 11)]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# the list of dates with data for the db\n", "dbs['ACTIV_CANADA'].dates()[-5:]" ] }, { "cell_type": "code", "execution_count": 5, "id": "90557e70-7808-4df8-9366-92e88a7d1db2", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "datetime.date(2023, 4, 11)" ] }, "execution_count": 5, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# or just the last day with data\n", "dbs['ACTIV_CANADA'].last_date" ] }, { "cell_type": "code", "execution_count": 6, "id": "39d6e0af-f219-4439-841e-5f39b4ecdbf1", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "['DAY', 'QTE', 'STAT', 'TRD']" ] }, "execution_count": 6, "metadata": {}, "output_type": "execute_result" } ], "source": [ "# and the list of tick types\n", "dbs['ACTIV_CANADA'].tick_types()" ] }, { "cell_type": "markdown", "id": "b5e390ca-8396-41ba-97c0-afa0a4427ef8", "metadata": {}, "source": [ "We can now retrieve symbols traded in a given time range. (In many financial markets, there are properties that remain constant throughout the trading day. Examples include the name of a financial instrument and the set of instruments traded)." ] }, { "cell_type": "code", "execution_count": 7, "id": "fe30bf21-201c-4ac8-949e-3dc13e8e2e48", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimeSYMBOL_NAME
02023-04-11A.H
12023-04-11AAA.P
22023-04-11AAAJ.P
32023-04-11AAB
42023-04-11AAC.P
.........
57332023-04-11ZYZ.SB.A
57342023-04-11ZYZ.UN.U
57352023-04-11ZZE.H
57362023-04-11ZZZ
57372023-04-11ZZZD
\n", "

5738 rows × 2 columns

\n", "
" ], "text/plain": [ " Time SYMBOL_NAME\n", "0 2023-04-11 A.H\n", "1 2023-04-11 AAA.P\n", "2 2023-04-11 AAAJ.P\n", "3 2023-04-11 AAB\n", "4 2023-04-11 AAC.P\n", "... ... ...\n", "5733 2023-04-11 ZYZ.SB.A\n", "5734 2023-04-11 ZYZ.UN.U\n", "5735 2023-04-11 ZZE.H\n", "5736 2023-04-11 ZZZ\n", "5737 2023-04-11 ZZZD\n", "\n", "[5738 rows x 2 columns]" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "symbols = otp.Symbols('ACTIV_CANADA')\n", "otp.run(symbols, start=otp.dt(2023,4,11), end=otp.dt(2023,4,12))" ] }, { "cell_type": "markdown", "id": "c19aa09b-4b03-484b-bcf2-c6350c7929ff", "metadata": {}, "source": [ "We used the `otp.run` method above, which executed a query that retrieved the list of symbols. \n", "\n", "The start and end timestamps were specified with `onetick-py`'s datetime class `otp.dt`." ] }, { "cell_type": "markdown", "id": "63125d3b-b49c-48d7-a173-f4a4816abd80", "metadata": {}, "source": [ "Now that we have database names, tick types, and symbols, we are ready to query a time series." ] }, { "cell_type": "code", "execution_count": 8, "id": "d7614b09-c381-406c-8c7b-3f5baaa4c05e", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimeEXCHANGECONDSTOP_STOCKSOURCETRFTTETICKERPRICEDELETED_TIMETICK_STATUSSIZECORRSEQ_NUMTRADE_IDPARTICIPANT_TIMETRF_TIMEOMDSEQ
02023-03-29 09:30:00.000877568PTNC0SPY399.92001969-12-31 19:00:000400082274529835252903152023-03-29 09:30:00.0008540161969-12-31 19:00:00.0000000000
12023-03-29 09:30:00.001151232TTNC0SPY399.92001969-12-31 19:00:0001000082280628791328978482023-03-29 09:30:00.0008010641969-12-31 19:00:00.0000000000
22023-03-29 09:30:00.001154304TTNC0SPY399.92001969-12-31 19:00:0001000082281628791328978492023-03-29 09:30:00.0008010641969-12-31 19:00:00.0000000001
32023-03-29 09:30:00.001921280TTNC0SPY399.93001969-12-31 19:00:000657082282628791328978912023-03-29 09:30:00.0015743481969-12-31 19:00:00.0000000002
42023-03-29 09:30:00.010831360ZFNC1SPY399.92501969-12-31 19:00:000100082401529835250391592023-03-29 09:30:00.0105600001969-12-31 19:00:00.0000000000
.........................................................
732492023-03-29 09:59:59.690966784DINCT0SPY399.85821969-12-31 19:00:00030301129716757104210412023-03-29 09:59:59.6903094432023-03-29 09:59:59.6906129160
732502023-03-29 09:59:59.697699840DINCN0SPY399.84241969-12-31 19:00:00050301131793718879870302023-03-29 09:59:59.6962453912023-03-29 09:59:59.6976776520
732512023-03-29 09:59:59.707425024DINCT0SPY399.86001969-12-31 19:00:00010301132716757104210592023-03-29 09:59:59.6330000002023-03-29 09:59:59.7070718250
732522023-03-29 09:59:59.928770304DINCT0SPY399.86001969-12-31 19:00:00010301136716757104236052023-03-29 09:59:59.8970000002023-03-29 09:59:59.9284170340
732532023-03-29 09:59:59.949504768DINCT0SPY399.85001969-12-31 19:00:000350301138716757104236262023-03-29 09:59:59.9488986282023-03-29 09:59:59.9491474440
\n", "

73254 rows × 18 columns

\n", "
" ], "text/plain": [ " Time EXCHANGE COND STOP_STOCK SOURCE TRF TTE TICKER PRICE DELETED_TIME TICK_STATUS SIZE CORR SEQ_NUM TRADE_ID PARTICIPANT_TIME TRF_TIME OMDSEQ\n", "0 2023-03-29 09:30:00.000877568 P T N C 0 SPY 399.9200 1969-12-31 19:00:00 0 400 0 82274 52983525290315 2023-03-29 09:30:00.000854016 1969-12-31 19:00:00.000000000 0\n", "1 2023-03-29 09:30:00.001151232 T T N C 0 SPY 399.9200 1969-12-31 19:00:00 0 1000 0 82280 62879132897848 2023-03-29 09:30:00.000801064 1969-12-31 19:00:00.000000000 0\n", "2 2023-03-29 09:30:00.001154304 T T N C 0 SPY 399.9200 1969-12-31 19:00:00 0 1000 0 82281 62879132897849 2023-03-29 09:30:00.000801064 1969-12-31 19:00:00.000000000 1\n", "3 2023-03-29 09:30:00.001921280 T T N C 0 SPY 399.9300 1969-12-31 19:00:00 0 657 0 82282 62879132897891 2023-03-29 09:30:00.001574348 1969-12-31 19:00:00.000000000 2\n", "4 2023-03-29 09:30:00.010831360 Z F N C 1 SPY 399.9250 1969-12-31 19:00:00 0 100 0 82401 52983525039159 2023-03-29 09:30:00.010560000 1969-12-31 19:00:00.000000000 0\n", "... ... ... ... ... ... .. .. ... ... ... ... ... ... ... ... ... ... ...\n", "73249 2023-03-29 09:59:59.690966784 D I N C T 0 SPY 399.8582 1969-12-31 19:00:00 0 3 0 301129 71675710421041 2023-03-29 09:59:59.690309443 2023-03-29 09:59:59.690612916 0\n", "73250 2023-03-29 09:59:59.697699840 D I N C N 0 SPY 399.8424 1969-12-31 19:00:00 0 5 0 301131 79371887987030 2023-03-29 09:59:59.696245391 2023-03-29 09:59:59.697677652 0\n", "73251 2023-03-29 09:59:59.707425024 D I N C T 0 SPY 399.8600 1969-12-31 19:00:00 0 1 0 301132 71675710421059 2023-03-29 09:59:59.633000000 2023-03-29 09:59:59.707071825 0\n", "73252 2023-03-29 09:59:59.928770304 D I N C T 0 SPY 399.8600 1969-12-31 19:00:00 0 1 0 301136 71675710423605 2023-03-29 09:59:59.897000000 2023-03-29 09:59:59.928417034 0\n", "73253 2023-03-29 09:59:59.949504768 D I N C T 0 SPY 399.8500 1969-12-31 19:00:00 0 35 0 301138 71675710423626 2023-03-29 09:59:59.948898628 2023-03-29 09:59:59.949147444 0\n", "\n", "[73254 rows x 18 columns]" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "otp.config['tz'] = 'EST5EDT' # set the timezone\n", "otp.run(q, start=otp.dt(2023,3,29,9,30), end=otp.dt(2023,3,29,10), symbols=['SPY'])" ] }, { "cell_type": "markdown", "id": "d051b530-da39-4075-8bcc-7013c78aca86", "metadata": {}, "source": [ "Note that we specified the start and end of the time series to retrieve the corresponding interval." ] }, { "cell_type": "markdown", "id": "c4e0e559-56b7-44f4-958f-15f954c9090c", "metadata": {}, "source": [ "Let's just keep the columns we're interested in to make it more digestible." ] }, { "cell_type": "code", "execution_count": 9, "id": "279f2c52-18fe-41ea-a275-f4f61598dbad", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimePRICESIZECONDEXCHANGE
02023-03-29 09:30:00.000877568399.9200400TP
12023-03-29 09:30:00.001151232399.92001000TT
22023-03-29 09:30:00.001154304399.92001000TT
32023-03-29 09:30:00.001921280399.9300657TT
42023-03-29 09:30:00.010831360399.9250100FZ
..................
732492023-03-29 09:59:59.690966784399.85823ID
732502023-03-29 09:59:59.697699840399.84245ID
732512023-03-29 09:59:59.707425024399.86001ID
732522023-03-29 09:59:59.928770304399.86001ID
732532023-03-29 09:59:59.949504768399.850035ID
\n", "

73254 rows × 5 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE COND EXCHANGE\n", "0 2023-03-29 09:30:00.000877568 399.9200 400 T P\n", "1 2023-03-29 09:30:00.001151232 399.9200 1000 T T\n", "2 2023-03-29 09:30:00.001154304 399.9200 1000 T T\n", "3 2023-03-29 09:30:00.001921280 399.9300 657 T T\n", "4 2023-03-29 09:30:00.010831360 399.9250 100 F Z\n", "... ... ... ... ... ...\n", "73249 2023-03-29 09:59:59.690966784 399.8582 3 I D\n", "73250 2023-03-29 09:59:59.697699840 399.8424 5 I D\n", "73251 2023-03-29 09:59:59.707425024 399.8600 1 I D\n", "73252 2023-03-29 09:59:59.928770304 399.8600 1 I D\n", "73253 2023-03-29 09:59:59.949504768 399.8500 35 I D\n", "\n", "[73254 rows x 5 columns]" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "q = q[['PRICE','SIZE','COND','EXCHANGE']]\n", "otp.run(q, start=otp.dt(2023,3,29,9,30), end=otp.dt(2023,3,29,10), symbols=['SPY'])" ] }, { "cell_type": "markdown", "id": "45b2d6d5-8afa-4149-adeb-2d55393e8b03", "metadata": {}, "source": [ "We can retrieve multiple time series." ] }, { "cell_type": "code", "execution_count": 10, "id": "71da7662-31ce-417e-813d-3d52ea785853", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'SPY': Time PRICE SIZE COND EXCHANGE\n", " 0 2023-03-29 09:30:00.000877568 399.920 400 T P\n", " 1 2023-03-29 09:30:00.001151232 399.920 1000 T T\n", " 2 2023-03-29 09:30:00.001154304 399.920 1000 T T\n", " 3 2023-03-29 09:30:00.001921280 399.930 657 T T\n", " 4 2023-03-29 09:30:00.010831360 399.925 100 F Z\n", " ... ... ... ... ... ...\n", " 537609 2023-03-29 15:59:59.994555136 401.350 643 F P\n", " 537610 2023-03-29 15:59:59.995045376 401.350 900 F P\n", " 537611 2023-03-29 15:59:59.997313024 401.340 100 Z\n", " 537612 2023-03-29 15:59:59.997354752 401.340 498 N\n", " 537613 2023-03-29 15:59:59.997406208 401.340 200 T\n", " \n", " [537614 rows x 5 columns],\n", " 'QQQ': Time PRICE SIZE COND EXCHANGE\n", " 0 2023-03-29 09:30:00.004574216 310.880 484 @F Z\n", " 1 2023-03-29 09:30:00.041854238 310.880 100 @ P\n", " 2 2023-03-29 09:30:00.041854358 310.880 100 @ Q P\n", " 3 2023-03-29 09:30:00.079583114 310.900 50 @ I J\n", " 4 2023-03-29 09:30:00.079611884 310.900 50 @ I Y\n", " ... ... ... ... ... ...\n", " 409494 2023-03-29 15:59:59.974708442 312.810 38 @ I Q\n", " 409495 2023-03-29 15:59:59.988868197 312.810 32 @F I X\n", " 409496 2023-03-29 15:59:59.988872215 312.805 200 @F Q\n", " 409497 2023-03-29 15:59:59.988874072 312.810 49 @F I Q\n", " 409498 2023-03-29 15:59:59.989201372 312.810 600 @F K\n", " \n", " [409499 rows x 5 columns]}" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "q = q[['PRICE','SIZE','COND','EXCHANGE']]\n", "mult = otp.run(q, start=otp.dt(2023,3,29,9,30), end=otp.dt(2023,3,29,16), symbols=['SPY', 'QQQ'])\n", "mult" ] }, { "cell_type": "markdown", "id": "99a8b4f5-e362-4c45-b4cb-6b304fc9de59", "metadata": {}, "source": [ "Each time series is returned as the value of a dict keyed by the corresponding symbol." ] }, { "cell_type": "code", "execution_count": 11, "id": "830d1abd-3134-4b68-9912-808ffa5472cf", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimePRICESIZECONDEXCHANGE
02023-03-29 09:30:00.004574216310.880484@FZ
12023-03-29 09:30:00.041854238310.880100@P
22023-03-29 09:30:00.041854358310.880100@ QP
32023-03-29 09:30:00.079583114310.90050@ IJ
42023-03-29 09:30:00.079611884310.90050@ IY
..................
4094942023-03-29 15:59:59.974708442312.81038@ IQ
4094952023-03-29 15:59:59.988868197312.81032@F IX
4094962023-03-29 15:59:59.988872215312.805200@FQ
4094972023-03-29 15:59:59.988874072312.81049@F IQ
4094982023-03-29 15:59:59.989201372312.810600@FK
\n", "

409499 rows × 5 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE COND EXCHANGE\n", "0 2023-03-29 09:30:00.004574216 310.880 484 @F Z\n", "1 2023-03-29 09:30:00.041854238 310.880 100 @ P\n", "2 2023-03-29 09:30:00.041854358 310.880 100 @ Q P\n", "3 2023-03-29 09:30:00.079583114 310.900 50 @ I J\n", "4 2023-03-29 09:30:00.079611884 310.900 50 @ I Y\n", "... ... ... ... ... ...\n", "409494 2023-03-29 15:59:59.974708442 312.810 38 @ I Q\n", "409495 2023-03-29 15:59:59.988868197 312.810 32 @F I X\n", "409496 2023-03-29 15:59:59.988872215 312.805 200 @F Q\n", "409497 2023-03-29 15:59:59.988874072 312.810 49 @F I Q\n", "409498 2023-03-29 15:59:59.989201372 312.810 600 @F K\n", "\n", "[409499 rows x 5 columns]" ] }, "execution_count": 11, "metadata": {}, "output_type": "execute_result" } ], "source": [ "mult['QQQ']" ] }, { "cell_type": "markdown", "id": "3c405829-86e9-4d04-849b-b9be60c279c8", "metadata": {}, "source": [ "We can also retrieve all of the symbols from the database or all of the symbols matching a pattern." ] }, { "cell_type": "code", "execution_count": 12, "id": "d89dc08b-0ead-4b3d-98ae-442ab9901978", "metadata": {}, "outputs": [ { "data": { "text/plain": [ "{'AAA': Time PRICE SIZE COND EXCHANGE\n", " 0 2023-03-29 10:13:42.861570816 24.5699 1 I D\n", " 1 2023-03-29 11:58:56.726636288 24.5100 39 I V\n", " 2 2023-03-29 12:30:32.157703680 24.5350 48 I V,\n", " 'AAAU': Time PRICE SIZE COND EXCHANGE\n", " 0 2023-03-29 09:30:00.019952128 19.480 8892 O X Z\n", " 1 2023-03-29 09:30:00.236440064 19.475 4 I Z\n", " 2 2023-03-29 09:30:00.287164928 19.475 1 I D\n", " 3 2023-03-29 09:30:00.331036416 19.480 1 I D\n", " 4 2023-03-29 09:30:01.001156864 19.480 1 I D\n", " ... ... ... ... ... ...\n", " 1370 2023-03-29 15:59:57.926450432 19.470 7600 F Y\n", " 1371 2023-03-29 15:59:57.926453504 19.470 6200 F Y\n", " 1372 2023-03-29 15:59:57.926459648 19.470 10400 F Y\n", " 1373 2023-03-29 15:59:57.927043072 19.470 27709 F N\n", " 1374 2023-03-29 15:59:57.929156864 19.470 908 F A\n", " \n", " [1375 rows x 5 columns]}" ] }, "execution_count": 12, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "q = q[['PRICE','SIZE','COND','EXCHANGE']]\n", "mult = otp.run(q, start=otp.dt(2023,3,29,9,30), end=otp.dt(2023,3,29,16), symbols=otp.Symbols('NYSE_TAQ', pattern='AAA%'))\n", "mult" ] }, { "cell_type": "markdown", "id": "409ef584-4ea7-4fcb-8caa-ba64706ba489", "metadata": {}, "source": [ "You may choose to merge all of the time series by time." ] }, { "cell_type": "code", "execution_count": 13, "id": "29e588e6-bf9a-4d4a-9bdd-e40d7a2cf88b", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
TimePRICESIZECONDEXCHANGESYMBOL_NAMETICK_TYPE
02023-03-29 09:30:00.000877568399.92400TPSPYTRD
12023-03-29 09:30:00.001151232399.921000TTSPYTRD
22023-03-29 09:30:00.001154304399.921000TTSPYTRD
32023-03-29 09:30:00.001921280399.93657TTSPYTRD
42023-03-29 09:30:00.004574216310.88484@FZQQQTRD
........................
9471082023-03-29 15:59:59.994555136401.35643FPSPYTRD
9471092023-03-29 15:59:59.995045376401.35900FPSPYTRD
9471102023-03-29 15:59:59.997313024401.34100ZSPYTRD
9471112023-03-29 15:59:59.997354752401.34498NSPYTRD
9471122023-03-29 15:59:59.997406208401.34200TSPYTRD
\n", "

947113 rows × 7 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE COND EXCHANGE SYMBOL_NAME TICK_TYPE\n", "0 2023-03-29 09:30:00.000877568 399.92 400 T P SPY TRD\n", "1 2023-03-29 09:30:00.001151232 399.92 1000 T T SPY TRD\n", "2 2023-03-29 09:30:00.001154304 399.92 1000 T T SPY TRD\n", "3 2023-03-29 09:30:00.001921280 399.93 657 T T SPY TRD\n", "4 2023-03-29 09:30:00.004574216 310.88 484 @F Z QQQ TRD\n", "... ... ... ... ... ... ... ...\n", "947108 2023-03-29 15:59:59.994555136 401.35 643 F P SPY TRD\n", "947109 2023-03-29 15:59:59.995045376 401.35 900 F P SPY TRD\n", "947110 2023-03-29 15:59:59.997313024 401.34 100 Z SPY TRD\n", "947111 2023-03-29 15:59:59.997354752 401.34 498 N SPY TRD\n", "947112 2023-03-29 15:59:59.997406208 401.34 200 T SPY TRD\n", "\n", "[947113 rows x 7 columns]" ] }, "execution_count": 13, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "q = q[['PRICE','SIZE','COND','EXCHANGE']]\n", "q = otp.merge([q], symbols=['SPY', 'QQQ'], identify_input_ts=True)\n", "single = otp.run(q, start=otp.dt(2023,3,29,9,30), end=otp.dt(2023,3,29,16))\n", "single" ] } ], "metadata": { "kernelspec": { "display_name": "Python 3 (ipykernel)", "language": "python", "name": "python3" }, "language_info": { "codemirror_mode": { "name": "ipython", "version": 3 }, "file_extension": ".py", "mimetype": "text/x-python", "name": "python", "nbconvert_exporter": "python", "pygments_lexer": "ipython3", "version": "3.9.8" } }, "nbformat": 4, "nbformat_minor": 5 }