{ "cells": [ { "cell_type": "markdown", "id": "4e11d96f-72ae-4a02-aa8e-4fd1f5d33d71", "metadata": {}, "source": [ "# Use Cases" ] }, { "cell_type": "code", "execution_count": 31, "id": "61932d8e-19c2-48e7-b3c6-bb160e9811b1", "metadata": {}, "outputs": [], "source": [ "import onetick.py as otp\n" ] }, { "cell_type": "markdown", "id": "28c0c0c2-9605-4773-b552-911a3f339bbc", "metadata": {}, "source": [ "# Retrieving Tick Data" ] }, { "cell_type": "code", "execution_count": 15, "id": "d1e86faf-d066-4cd2-84af-25850ff56f0c", "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-04-20 09:30:00.000650240ZNC0SPY411.21001969-12-31 19:00:000231078367529835250427442023-04-20 09:30:00.0003310001969-12-31 19:00:00.0000000000
12023-04-20 09:30:00.000656384TTNC0SPY411.20001969-12-31 19:00:000100078368628791469210292023-04-20 09:30:00.0002732831969-12-31 19:00:00.0000000001
22023-04-20 09:30:00.002436608KNC0SPY411.20001969-12-31 19:00:000400078373529835251738192023-04-20 09:30:00.0020890001969-12-31 19:00:00.0000000000
32023-04-20 09:30:00.002562816ZF INC1SPY411.21001969-12-31 19:00:00050078374529835250427452023-04-20 09:30:00.0023250001969-12-31 19:00:00.0000000001
42023-04-20 09:30:00.002568960ZF INC1SPY411.22001969-12-31 19:00:00050078375529835250427462023-04-20 09:30:00.0023250001969-12-31 19:00:00.0000000002
.........................................................
171152023-04-20 09:34:59.781732352PFNC1SPY411.74001969-12-31 19:00:0001000119251529835256238772023-04-20 09:34:59.7817077761969-12-31 19:00:00.0000000000
171162023-04-20 09:34:59.781930752KFNC1SPY411.74001969-12-31 19:00:0001000119252529835252365442023-04-20 09:34:59.7816800001969-12-31 19:00:00.0000000001
171172023-04-20 09:34:59.892375040ZINC0SPY411.74001969-12-31 19:00:000400119268529835251065102023-04-20 09:34:59.8921380001969-12-31 19:00:00.0000000000
171182023-04-20 09:34:59.930526464DNCT0SPY411.74251969-12-31 19:00:0002000119270716753081695462023-04-20 09:34:59.7807630002023-04-20 09:34:59.9301702190
171192023-04-20 09:34:59.958385152ZNC0SPY411.74001969-12-31 19:00:0001600119271529835251065142023-04-20 09:34:59.9581470001969-12-31 19:00:00.0000000000
\n", "

17120 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-04-20 09:30:00.000650240 Z N C 0 SPY 411.2100 1969-12-31 19:00:00 0 231 0 78367 52983525042744 2023-04-20 09:30:00.000331000 1969-12-31 19:00:00.000000000 0\n", "1 2023-04-20 09:30:00.000656384 T T N C 0 SPY 411.2000 1969-12-31 19:00:00 0 100 0 78368 62879146921029 2023-04-20 09:30:00.000273283 1969-12-31 19:00:00.000000000 1\n", "2 2023-04-20 09:30:00.002436608 K N C 0 SPY 411.2000 1969-12-31 19:00:00 0 400 0 78373 52983525173819 2023-04-20 09:30:00.002089000 1969-12-31 19:00:00.000000000 0\n", "3 2023-04-20 09:30:00.002562816 Z F I N C 1 SPY 411.2100 1969-12-31 19:00:00 0 50 0 78374 52983525042745 2023-04-20 09:30:00.002325000 1969-12-31 19:00:00.000000000 1\n", "4 2023-04-20 09:30:00.002568960 Z F I N C 1 SPY 411.2200 1969-12-31 19:00:00 0 50 0 78375 52983525042746 2023-04-20 09:30:00.002325000 1969-12-31 19:00:00.000000000 2\n", "... ... ... ... ... ... .. .. ... ... ... ... ... ... ... ... ... ... ...\n", "17115 2023-04-20 09:34:59.781732352 P F N C 1 SPY 411.7400 1969-12-31 19:00:00 0 100 0 119251 52983525623877 2023-04-20 09:34:59.781707776 1969-12-31 19:00:00.000000000 0\n", "17116 2023-04-20 09:34:59.781930752 K F N C 1 SPY 411.7400 1969-12-31 19:00:00 0 100 0 119252 52983525236544 2023-04-20 09:34:59.781680000 1969-12-31 19:00:00.000000000 1\n", "17117 2023-04-20 09:34:59.892375040 Z I N C 0 SPY 411.7400 1969-12-31 19:00:00 0 40 0 119268 52983525106510 2023-04-20 09:34:59.892138000 1969-12-31 19:00:00.000000000 0\n", "17118 2023-04-20 09:34:59.930526464 D N C T 0 SPY 411.7425 1969-12-31 19:00:00 0 200 0 119270 71675308169546 2023-04-20 09:34:59.780763000 2023-04-20 09:34:59.930170219 0\n", "17119 2023-04-20 09:34:59.958385152 Z N C 0 SPY 411.7400 1969-12-31 19:00:00 0 160 0 119271 52983525106514 2023-04-20 09:34:59.958147000 1969-12-31 19:00:00.000000000 0\n", "\n", "[17120 rows x 18 columns]" ] }, "execution_count": 15, "metadata": {}, "output_type": "execute_result" } ], "source": [ "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "otp.run(trd, start=otp.dt(2023, 4, 20, 9, 30), end=otp.dt(2023, 4, 20, 9, 35), symbols=['SPY'])" ] }, { "cell_type": "markdown", "id": "267b2ca5-7cd1-4af2-94f1-4fcbe5d1a0ad", "metadata": {}, "source": [ "# Creating Bars\n", "We create 10-minute bars (`bucket_interval=600` seconds) below." ] }, { "cell_type": "code", "execution_count": 3, "id": "77843d91-d85d-45cd-acc9-2fd2c87cc7b5", "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", "
TimeVOLUMEHIGHLOWOPENCOUNTCLOSE
02022-11-28 09:40:0025633547.340046.411046.9200195847.2000
12022-11-28 09:50:0010398947.310046.905047.1800136047.1399
22022-11-28 10:00:009662247.240046.960047.0900110247.0900
32022-11-28 10:10:0016913947.160046.740047.1000177046.9113
42022-11-28 10:20:0011576447.300046.780046.9100123047.1250
........................
1122022-11-30 15:20:0021552850.110049.731949.7716211849.9711
1132022-11-30 15:30:0014668250.310049.940049.9750212750.2800
1142022-11-30 15:40:0018897650.500050.230050.2700262450.2300
1152022-11-30 15:50:0020060550.356150.010050.2300323150.0700
1162022-11-30 16:00:0058134250.190049.830050.0700649350.1400
\n", "

117 rows × 7 columns

\n", "
" ], "text/plain": [ " Time VOLUME HIGH LOW OPEN COUNT CLOSE\n", "0 2022-11-28 09:40:00 256335 47.3400 46.4110 46.9200 1958 47.2000\n", "1 2022-11-28 09:50:00 103989 47.3100 46.9050 47.1800 1360 47.1399\n", "2 2022-11-28 10:00:00 96622 47.2400 46.9600 47.0900 1102 47.0900\n", "3 2022-11-28 10:10:00 169139 47.1600 46.7400 47.1000 1770 46.9113\n", "4 2022-11-28 10:20:00 115764 47.3000 46.7800 46.9100 1230 47.1250\n", ".. ... ... ... ... ... ... ...\n", "112 2022-11-30 15:20:00 215528 50.1100 49.7319 49.7716 2118 49.9711\n", "113 2022-11-30 15:30:00 146682 50.3100 49.9400 49.9750 2127 50.2800\n", "114 2022-11-30 15:40:00 188976 50.5000 50.2300 50.2700 2624 50.2300\n", "115 2022-11-30 15:50:00 200605 50.3561 50.0100 50.2300 3231 50.0700\n", "116 2022-11-30 16:00:00 581342 50.1900 49.8300 50.0700 6493 50.1400\n", "\n", "[117 rows x 7 columns]" ] }, "execution_count": 3, "metadata": {}, "output_type": "execute_result" } ], "source": [ "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "bars = trd.agg({'VOLUME': otp.agg.sum('SIZE'),\n", " 'HIGH': otp.agg.max('PRICE'),\n", " 'LOW': otp.agg.min('PRICE'),\n", " 'OPEN': otp.agg.first('PRICE'),\n", " 'COUNT': otp.agg.count(),\n", " 'CLOSE': otp.agg.last('PRICE')},\n", " bucket_interval=600)\n", "otp.run(bars, start=otp.dt(2022, 11, 28, 9, 30), end=otp.dt(2022, 11, 30, 16), symbols=['AA'], apply_times_daily=True)" ] }, { "cell_type": "markdown", "id": "332fa5f0-f4fe-4beb-af2f-b7430efa71cc", "metadata": {}, "source": [ "
\n", "Note: OneTick Cloud has minute bars precomputed and available in *_BARS databases under the tick type TRD_1M.\n", "
" ] }, { "cell_type": "markdown", "id": "7700218a-d237-4353-96e5-7b90a5da2e0e", "metadata": {}, "source": [ "Daily OHLCV data with the official closing prices is also available: see [OHLCV](daily_OHLCV.ipynb)." ] }, { "cell_type": "code", "execution_count": 10, "id": "a1f6a4ec-d8cb-4f70-8203-900adb1d1143", "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", "
TimeFIRSTHIGHLOWLASTVOLUME
02022-11-28 09:31:00399.09399.750399.000399.6500289791
12022-11-28 09:32:00399.69400.000399.600399.8900295441
22022-11-28 09:33:00399.89400.175399.840400.1750233032
32022-11-28 09:34:00400.16400.380400.000400.1600208954
42022-11-28 09:35:00400.13400.360400.120400.3493112015
.....................
11652022-11-30 15:56:00406.68406.830406.460406.81001311316
11662022-11-30 15:57:00406.80406.830406.525406.60001319798
11672022-11-30 15:58:00406.60406.850406.550406.73001691348
11682022-11-30 15:59:00406.73406.950406.680406.71002592503
11692022-11-30 16:00:00406.71407.550406.680407.47006700757
\n", "

1170 rows × 6 columns

\n", "
" ], "text/plain": [ " Time FIRST HIGH LOW LAST VOLUME\n", "0 2022-11-28 09:31:00 399.09 399.750 399.000 399.6500 289791\n", "1 2022-11-28 09:32:00 399.69 400.000 399.600 399.8900 295441\n", "2 2022-11-28 09:33:00 399.89 400.175 399.840 400.1750 233032\n", "3 2022-11-28 09:34:00 400.16 400.380 400.000 400.1600 208954\n", "4 2022-11-28 09:35:00 400.13 400.360 400.120 400.3493 112015\n", "... ... ... ... ... ... ...\n", "1165 2022-11-30 15:56:00 406.68 406.830 406.460 406.8100 1311316\n", "1166 2022-11-30 15:57:00 406.80 406.830 406.525 406.6000 1319798\n", "1167 2022-11-30 15:58:00 406.60 406.850 406.550 406.7300 1691348\n", "1168 2022-11-30 15:59:00 406.73 406.950 406.680 406.7100 2592503\n", "1169 2022-11-30 16:00:00 406.71 407.550 406.680 407.4700 6700757\n", "\n", "[1170 rows x 6 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "bars = otp.DataSource('NYSE_TAQ_BARS', tick_type='TRD_1M')\n", "bars = bars[['FIRST','HIGH','LOW','LAST','VOLUME']]\n", "otp.run(bars, start=otp.dt(2022, 11, 28, 9, 31), end=otp.dt(2022, 11, 30, 16, 1), symbols=['SPY'], apply_times_daily=True)" ] }, { "cell_type": "markdown", "id": "3650ce08-f444-406b-8b12-610a85dd61a2", "metadata": {}, "source": [ "# Prevailing quote at the time of a trade" ] }, { "cell_type": "code", "execution_count": 20, "id": "8fcd13da-54a3-4b42-af0e-dd4d1e47d270", "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", "
TimePRICESIZEASK_PRICEBID_PRICEquote_time
02023-04-20 09:30:00.000650240411.21231411.22411.202023-04-20 09:30:00.000636672
12023-04-20 09:30:00.000656384411.20100411.22411.202023-04-20 09:30:00.000636672
22023-04-20 09:30:00.002436608411.20400411.22411.202023-04-20 09:30:00.002411008
32023-04-20 09:30:00.002562816411.2150411.22411.202023-04-20 09:30:00.002486016
42023-04-20 09:30:00.002568960411.2250411.22411.202023-04-20 09:30:00.002566912
.....................
637732023-04-20 09:59:59.967479808411.66100411.67411.652023-04-20 09:59:59.967476224
637742023-04-20 09:59:59.971563520411.66100411.67411.662023-04-20 09:59:59.971560960
637752023-04-20 09:59:59.971569408411.66100411.67411.662023-04-20 09:59:59.971565824
637762023-04-20 09:59:59.971674624411.66100411.67411.662023-04-20 09:59:59.971671296
637772023-04-20 09:59:59.972700160411.681411.67411.652023-04-20 09:59:59.971976960
\n", "

63778 rows × 6 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE ASK_PRICE BID_PRICE quote_time\n", "0 2023-04-20 09:30:00.000650240 411.21 231 411.22 411.20 2023-04-20 09:30:00.000636672\n", "1 2023-04-20 09:30:00.000656384 411.20 100 411.22 411.20 2023-04-20 09:30:00.000636672\n", "2 2023-04-20 09:30:00.002436608 411.20 400 411.22 411.20 2023-04-20 09:30:00.002411008\n", "3 2023-04-20 09:30:00.002562816 411.21 50 411.22 411.20 2023-04-20 09:30:00.002486016\n", "4 2023-04-20 09:30:00.002568960 411.22 50 411.22 411.20 2023-04-20 09:30:00.002566912\n", "... ... ... ... ... ... ...\n", "63773 2023-04-20 09:59:59.967479808 411.66 100 411.67 411.65 2023-04-20 09:59:59.967476224\n", "63774 2023-04-20 09:59:59.971563520 411.66 100 411.67 411.66 2023-04-20 09:59:59.971560960\n", "63775 2023-04-20 09:59:59.971569408 411.66 100 411.67 411.66 2023-04-20 09:59:59.971565824\n", "63776 2023-04-20 09:59:59.971674624 411.66 100 411.67 411.66 2023-04-20 09:59:59.971671296\n", "63777 2023-04-20 09:59:59.972700160 411.68 1 411.67 411.65 2023-04-20 09:59:59.971976960\n", "\n", "[63778 rows x 6 columns]" ] }, "execution_count": 20, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import onetick.py as otp\n", "\n", "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "trd = trd[['PRICE', 'SIZE']]\n", "\n", "qte = otp.DataSource('TAQ_NBBO', tick_type='NBBO', back_to_first_tick=600)\n", "qte = qte[['ASK_PRICE', 'BID_PRICE']] \n", "qte['quote_time'] = qte['Time']\n", "\n", "enriched_trades = otp.join_by_time([trd, qte])\n", "\n", "otp.run(enriched_trades, start=otp.dt(2023, 4, 20, 9, 30), end=otp.dt(2023, 4, 20, 10), symbols='SPY')" ] }, { "cell_type": "markdown", "id": "f3eb9bbd-7fa8-4fa0-80f2-3a48ce1683bc", "metadata": {}, "source": [ "# Interval Metrics (e.g., VWAP)" ] }, { "cell_type": "code", "execution_count": 7, "id": "3efd1efb-c0cd-4ad5-ba4b-468d291f2986", "metadata": {}, "outputs": [ { "data": { "text/html": [ "
\n", "\n", "\n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", "
Timemarket_vwap
02023-04-10 10:27:00160.477922
\n", "
" ], "text/plain": [ " Time market_vwap\n", "0 2023-04-10 10:27:00 160.477922" ] }, "execution_count": 7, "metadata": {}, "output_type": "execute_result" } ], "source": [ "q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "q = q.agg({'market_vwap': otp.agg.vwap('PRICE', 'SIZE')})\n", "otp.run(q, start=otp.dt(2023, 4, 10, 10, 25, 59), end=otp.dt(2023, 4, 10, 10, 27), symbols='AAPL')" ] }, { "cell_type": "markdown", "id": "cc280c82-477b-4888-8f84-e4d74432a6dd", "metadata": {}, "source": [ "## Computing market VWAP for every order's arrival/exit interval" ] }, { "cell_type": "code", "execution_count": 8, "id": "38c5ebc2-b4ea-4e11-b62b-3637ec0ccd6c", "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", "
Timearrivalexitsym
02003-12-01 00:00:00.0002023-04-10 10:25:592023-04-10 10:26:07AAPL
12003-12-01 00:00:00.0012023-04-10 10:26:092023-04-10 10:27:48MSFT
\n", "
" ], "text/plain": [ " Time arrival exit sym\n", "0 2003-12-01 00:00:00.000 2023-04-10 10:25:59 2023-04-10 10:26:07 AAPL\n", "1 2003-12-01 00:00:00.001 2023-04-10 10:26:09 2023-04-10 10:27:48 MSFT" ] }, "execution_count": 8, "metadata": {}, "output_type": "execute_result" } ], "source": [ "orders = otp.Ticks(arrival=[otp.dt(2023, 4, 10, 10, 25, 59), otp.dt(2023, 4, 10, 10, 26, 9)], \n", " exit=[otp.dt(2023, 4, 10, 10, 26, 7), otp.dt(2023, 4, 10, 10, 27, 48)],\n", " sym=['AAPL', 'MSFT'])\n", "otp.run(orders)" ] }, { "cell_type": "code", "execution_count": 9, "id": "4600b9b1-3dab-4601-bc02-ba9480d151a6", "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", "
Timemarket_vwaparrivalexitsym
02003-12-01 00:00:00.000160.5488902023-04-10 10:25:592023-04-10 10:26:07AAPL
12003-12-01 00:00:00.001286.1367792023-04-10 10:26:092023-04-10 10:27:48MSFT
\n", "
" ], "text/plain": [ " Time market_vwap arrival exit sym\n", "0 2003-12-01 00:00:00.000 160.548890 2023-04-10 10:25:59 2023-04-10 10:26:07 AAPL\n", "1 2003-12-01 00:00:00.001 286.136779 2023-04-10 10:26:09 2023-04-10 10:27:48 MSFT" ] }, "execution_count": 9, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def vwap(symbol):\n", " q = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", " q = q.agg({'market_vwap': otp.agg.vwap('PRICE','SIZE')})\n", " return q\n", "\n", "orders = otp.Ticks(arrival=[otp.dt(2023, 4, 10, 10, 25, 59), otp.dt(2023, 4, 10, 10, 26, 9)], \n", " exit=[otp.dt(2023, 4, 10, 10, 26, 7), otp.dt(2023, 4, 10, 10, 27, 48)],\n", " sym=['AAPL', 'MSFT'])\n", "orders = orders.join_with_query(vwap, start=orders['arrival'], end=orders['exit'], symbol=orders['sym'])\n", "otp.run(orders)" ] }, { "cell_type": "markdown", "id": "0f4c4708-b85f-4db7-836d-8a4a06550685", "metadata": {}, "source": [ "# Point-in-time benchmarks: BBO at different markouts\n", "Now let's find the prevailing quote at different time intervals (markouts) before/after each trade." ] }, { "cell_type": "code", "execution_count": 10, "id": "b30b0acf-7a92-4cc0-b234-b89f66a73ece", "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", "
TimePRICESIZEASK_PRICE_-1BID_PRICE_-1quote_time_-1ASK_PRICE_1BID_PRICE_1quote_time_1ASK_PRICE_5BID_PRICE_5quote_time_5ASK_PRICE_10BID_PRICE_10quote_time_10ASK_PRICE_60BID_PRICE_60quote_time_60
02023-03-08 10:00:00.014762069181.4250300181.47181.412023-03-08 09:59:59.002564435181.49181.482023-03-08 10:00:01.000000000181.15181.042023-03-08 10:00:05.012557243181.16181.082023-03-08 10:00:10.000000000180.51180.452023-03-08 10:01:00.012332497
12023-03-08 10:00:00.014764168181.440012181.47181.412023-03-08 09:59:59.002564435181.49181.482023-03-08 10:00:01.000000000181.15181.042023-03-08 10:00:05.012557243181.16181.082023-03-08 10:00:10.000000000180.51180.452023-03-08 10:01:00.012332497
22023-03-08 10:00:00.014767570181.4400200181.47181.412023-03-08 09:59:59.002564435181.49181.482023-03-08 10:00:01.000000000181.15181.042023-03-08 10:00:05.012557243181.16181.082023-03-08 10:00:10.000000000180.51180.452023-03-08 10:01:00.012332497
32023-03-08 10:00:00.014799259181.45002181.47181.412023-03-08 09:59:59.002564435181.49181.482023-03-08 10:00:01.000000000181.15181.042023-03-08 10:00:05.012557243181.16181.082023-03-08 10:00:10.000000000180.51180.452023-03-08 10:01:00.012332497
42023-03-08 10:00:00.015374686181.4400300181.47181.412023-03-08 09:59:59.002564435181.49181.482023-03-08 10:00:01.000000000181.15181.042023-03-08 10:00:05.012557243181.16181.082023-03-08 10:00:10.000000000180.51180.452023-03-08 10:01:00.012332497
.........................................................
7052023-03-09 10:00:00.956041264183.5899500183.46183.432023-03-09 09:59:59.911620614183.59183.542023-03-09 10:00:01.945769144183.65183.572023-03-09 10:00:05.954673686183.68183.652023-03-09 10:00:10.872345446183.47183.452023-03-09 10:01:00.941018616
7062023-03-09 10:00:00.967881686183.580025183.46183.432023-03-09 09:59:59.963372673183.59183.542023-03-09 10:00:01.963256371183.66183.572023-03-09 10:00:05.967652794183.68183.652023-03-09 10:00:10.872345446183.47183.452023-03-09 10:01:00.941018616
7072023-03-09 10:00:00.978963708183.4718136183.46183.422023-03-09 09:59:59.971349853183.59183.542023-03-09 10:00:01.963256371183.66183.572023-03-09 10:00:05.967652794183.68183.652023-03-09 10:00:10.872345446183.47183.452023-03-09 10:01:00.941018616
7082023-03-09 10:00:00.983358396183.550020183.46183.412023-03-09 09:59:59.983288366183.59183.542023-03-09 10:00:01.963256371183.66183.572023-03-09 10:00:05.967652794183.68183.652023-03-09 10:00:10.872345446183.47183.452023-03-09 10:01:00.941018616
7092023-03-09 10:00:00.990945922183.5650200183.44183.412023-03-09 09:59:59.988781014183.59183.542023-03-09 10:00:01.963256371183.66183.572023-03-09 10:00:05.967652794183.68183.652023-03-09 10:00:10.872345446183.47183.452023-03-09 10:01:00.941018616
\n", "

710 rows × 18 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE ASK_PRICE_-1 BID_PRICE_-1 quote_time_-1 ASK_PRICE_1 BID_PRICE_1 quote_time_1 ASK_PRICE_5 BID_PRICE_5 quote_time_5 ASK_PRICE_10 BID_PRICE_10 quote_time_10 ASK_PRICE_60 BID_PRICE_60 quote_time_60\n", "0 2023-03-08 10:00:00.014762069 181.4250 300 181.47 181.41 2023-03-08 09:59:59.002564435 181.49 181.48 2023-03-08 10:00:01.000000000 181.15 181.04 2023-03-08 10:00:05.012557243 181.16 181.08 2023-03-08 10:00:10.000000000 180.51 180.45 2023-03-08 10:01:00.012332497\n", "1 2023-03-08 10:00:00.014764168 181.4400 12 181.47 181.41 2023-03-08 09:59:59.002564435 181.49 181.48 2023-03-08 10:00:01.000000000 181.15 181.04 2023-03-08 10:00:05.012557243 181.16 181.08 2023-03-08 10:00:10.000000000 180.51 180.45 2023-03-08 10:01:00.012332497\n", "2 2023-03-08 10:00:00.014767570 181.4400 200 181.47 181.41 2023-03-08 09:59:59.002564435 181.49 181.48 2023-03-08 10:00:01.000000000 181.15 181.04 2023-03-08 10:00:05.012557243 181.16 181.08 2023-03-08 10:00:10.000000000 180.51 180.45 2023-03-08 10:01:00.012332497\n", "3 2023-03-08 10:00:00.014799259 181.4500 2 181.47 181.41 2023-03-08 09:59:59.002564435 181.49 181.48 2023-03-08 10:00:01.000000000 181.15 181.04 2023-03-08 10:00:05.012557243 181.16 181.08 2023-03-08 10:00:10.000000000 180.51 180.45 2023-03-08 10:01:00.012332497\n", "4 2023-03-08 10:00:00.015374686 181.4400 300 181.47 181.41 2023-03-08 09:59:59.002564435 181.49 181.48 2023-03-08 10:00:01.000000000 181.15 181.04 2023-03-08 10:00:05.012557243 181.16 181.08 2023-03-08 10:00:10.000000000 180.51 180.45 2023-03-08 10:01:00.012332497\n", ".. ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...\n", "705 2023-03-09 10:00:00.956041264 183.5899 500 183.46 183.43 2023-03-09 09:59:59.911620614 183.59 183.54 2023-03-09 10:00:01.945769144 183.65 183.57 2023-03-09 10:00:05.954673686 183.68 183.65 2023-03-09 10:00:10.872345446 183.47 183.45 2023-03-09 10:01:00.941018616\n", "706 2023-03-09 10:00:00.967881686 183.5800 25 183.46 183.43 2023-03-09 09:59:59.963372673 183.59 183.54 2023-03-09 10:00:01.963256371 183.66 183.57 2023-03-09 10:00:05.967652794 183.68 183.65 2023-03-09 10:00:10.872345446 183.47 183.45 2023-03-09 10:01:00.941018616\n", "707 2023-03-09 10:00:00.978963708 183.4718 136 183.46 183.42 2023-03-09 09:59:59.971349853 183.59 183.54 2023-03-09 10:00:01.963256371 183.66 183.57 2023-03-09 10:00:05.967652794 183.68 183.65 2023-03-09 10:00:10.872345446 183.47 183.45 2023-03-09 10:01:00.941018616\n", "708 2023-03-09 10:00:00.983358396 183.5500 20 183.46 183.41 2023-03-09 09:59:59.983288366 183.59 183.54 2023-03-09 10:00:01.963256371 183.66 183.57 2023-03-09 10:00:05.967652794 183.68 183.65 2023-03-09 10:00:10.872345446 183.47 183.45 2023-03-09 10:01:00.941018616\n", "709 2023-03-09 10:00:00.990945922 183.5650 200 183.44 183.41 2023-03-09 09:59:59.988781014 183.59 183.54 2023-03-09 10:00:01.963256371 183.66 183.57 2023-03-09 10:00:05.967652794 183.68 183.65 2023-03-09 10:00:10.872345446 183.47 183.45 2023-03-09 10:01:00.941018616\n", "\n", "[710 rows x 18 columns]" ] }, "execution_count": 10, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import onetick.query as otq\n", "\n", "s = otp.dt(2023, 3, 8, 10)\n", "e = otp.dt(2023, 3, 9, 10, 0, 1)\n", "markouts = [-1, 1, 5, 10, 60] \n", "\n", "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "trd = trd[['PRICE', 'SIZE']]\n", "\n", "qte_by_markout = []\n", "for m in markouts:\n", " qte = otp.DataSource('TAQ_NBBO', tick_type='NBBO', back_to_first_tick=86400)\n", " qte = qte[['ASK_PRICE', 'BID_PRICE']]\n", " qte = qte.rename({'ASK_PRICE': f'ASK_PRICE_{m}', \n", " 'BID_PRICE': f'BID_PRICE_{m}'})\n", " qte[f'quote_time_{m}'] = qte['Time']\n", " \n", " # shift the data by m seconds\n", " # change the timestamp so it fits into original time range (required by OneTick)\n", " qte.sink(otq.ModifyQueryTimes(start_time=f'_START_TIME + {m * 1000}',\n", " output_timestamp=f'TIMESTAMP - {m * 1000}',\n", " end_time=f'_END_TIME + {m * 1000}'))\n", " qte_by_markout.append(qte)\n", "\n", "trd = otp.join_by_time([trd] + qte_by_markout)\n", "# trd.render()\n", "otp.run(trd, start=s, end=e, symbols='TSLA', apply_times_daily=True)" ] }, { "cell_type": "markdown", "id": "1ebe1995-f700-41b9-8a8e-99ea63064a84", "metadata": {}, "source": [ "# Real-time processing: Signal Generation\n", "\n", "We'll compute golden cross signals using 50-second and 200-second moving averages\n", "- 'Entries' is set to 1 when the short-term moving average goes above the long term (i.e., a signal to buy)\n", "- 'Exits' is set to 1 on when the short-term moving average goes below the long term (i.e., a signal to sell)" ] }, { "cell_type": "code", "execution_count": 11, "id": "7a1328c9-eec2-479e-aa4e-45375f593fac", "metadata": {}, "outputs": [], "source": [ "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "trd = trd[['PRICE']]\n", "\n", "trd = trd.agg({'short': otp.agg.mean('PRICE')}, bucket_interval=60, running=True, all_fields=True)\n", "trd = trd.agg({'long': otp.agg.mean('PRICE')}, bucket_interval=60*5, running=True, all_fields=True)\n", "\n", "trd['buy'] = (trd['short'][-1] < trd['long'][-1]) & (trd['short'] > trd['long']) \n", "trd['sell'] = (trd['short'][-1] > trd['long'][-1]) & (trd['short'] < trd['long']) " ] }, { "cell_type": "markdown", "id": "31d32992-d4ab-4163-8797-0c90622081a0", "metadata": {}, "source": [ "We define a callback that for every tick (i.e., on every trade) will\n", "- print a '.' if there is no signal\n", "- print out the tick followed by 'BUY' on an entry signal\n", "- print out the tick followed by 'SELL' on an exit signal" ] }, { "cell_type": "code", "execution_count": 12, "id": "ee5756fd-1665-4596-9e11-2a150d22c1ab", "metadata": {}, "outputs": [], "source": [ "class GoldenCrossCallback(otp.CallbackBase):\n", " def process_tick(self, tick, time):\n", " if not tick['buy'] and not tick['sell']:\n", " print('.', end='')\n", " return\n", " print()\n", " print()\n", " print(time, tick)\n", " if tick['buy']:\n", " print('BUY')\n", " if tick['sell']:\n", " print('SELL')\n", " print()" ] }, { "cell_type": "markdown", "id": "22e43b5d-8658-465e-9ddb-78b4cfc7278f", "metadata": {}, "source": [ "The query will run continuously with the output printed as the events happen if you set start/end times accordingly (see the commented out line)." ] }, { "cell_type": "code", "execution_count": 13, "id": "21c69f89-2b82-4dc9-a06f-2b12607a80da", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "..................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................\n", "\n", "2023-03-31 14:01:00.702788 {'PRICE': 102.685, 'short': 102.55571394019401, 'long': 102.55571526781988, 'buy': 0.0, 'sell': 1.0}\n", "SELL\n", "\n", "\n", "\n", "2023-03-31 14:01:00.715928 {'PRICE': 102.69, 'short': 102.55577296703349, 'long': 102.5557718802702, 'buy': 1.0, 'sell': 0.0}\n", "BUY\n", "\n", "..\n", "\n", "2023-03-31 14:01:00.859880 {'PRICE': 102.685, 'short': 102.55593452066896, 'long': 102.55593932631618, 'buy': 0.0, 'sell': 1.0}\n", "SELL\n", "\n", "..\n", "\n", "2023-03-31 14:01:01.362596 {'PRICE': 102.6888, 'short': 102.55619792035449, 'long': 102.5561062237178, 'buy': 1.0, 'sell': 0.0}\n", "BUY\n", "\n", ".......................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................\n", "\n", "2023-03-31 14:02:37.925538 {'PRICE': 102.51, 'short': 102.58265328102567, 'long': 102.58265371464864, 'buy': 0.0, 'sell': 1.0}\n", "SELL\n", "\n", ".........................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................." ] } ], "source": [ "# timestamps appear in GMT\n", "cb = GoldenCrossCallback()\n", "otp.run(trd, symbols='AMZN',\n", " callback=cb, running=True,\n", " # start=otp.dt.now(), end=otp.dt.now() + otp.Day(1),\n", " start=otp.dt(2023, 3, 31, 10), end=otp.dt(2023, 3, 31, 10, 5),\n", ")" ] }, { "cell_type": "markdown", "id": "ef9f1261-f3b1-41ed-8b84-de4e2ccd8915", "metadata": {}, "source": [ "# Upticks / Downticks\n", "Let's mark each trade as an uptick if its price is above the last trade's price and as a downtick if it's below." ] }, { "cell_type": "code", "execution_count": 22, "id": "6daf54a8-f267-4407-a66b-8178047ca12a", "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", "
TimePRICEUPTICK
02023-04-20 09:30:00.000650240411.2100NaN
12023-04-20 09:30:00.000656384411.2000-1.0
22023-04-20 09:30:00.002436608411.20000.0
32023-04-20 09:30:00.002562816411.21001.0
42023-04-20 09:30:00.002568960411.22001.0
............
171152023-04-20 09:34:59.781732352411.74000.0
171162023-04-20 09:34:59.781930752411.74000.0
171172023-04-20 09:34:59.892375040411.74000.0
171182023-04-20 09:34:59.930526464411.74251.0
171192023-04-20 09:34:59.958385152411.7400-1.0
\n", "

17120 rows × 3 columns

\n", "
" ], "text/plain": [ " Time PRICE UPTICK\n", "0 2023-04-20 09:30:00.000650240 411.2100 NaN\n", "1 2023-04-20 09:30:00.000656384 411.2000 -1.0\n", "2 2023-04-20 09:30:00.002436608 411.2000 0.0\n", "3 2023-04-20 09:30:00.002562816 411.2100 1.0\n", "4 2023-04-20 09:30:00.002568960 411.2200 1.0\n", "... ... ... ...\n", "17115 2023-04-20 09:34:59.781732352 411.7400 0.0\n", "17116 2023-04-20 09:34:59.781930752 411.7400 0.0\n", "17117 2023-04-20 09:34:59.892375040 411.7400 0.0\n", "17118 2023-04-20 09:34:59.930526464 411.7425 1.0\n", "17119 2023-04-20 09:34:59.958385152 411.7400 -1.0\n", "\n", "[17120 rows x 3 columns]" ] }, "execution_count": 22, "metadata": {}, "output_type": "execute_result" } ], "source": [ "def uptick(t):\n", " if t['PRICE'] == otp.nan or t['PRICE'][-1] == otp.nan:\n", " return otp.nan\n", " if t['PRICE'] > t['PRICE'][-1]:\n", " return 1\n", " elif t['PRICE'] < t['PRICE'][-1]:\n", " return -1\n", " else:\n", " return 0\n", "\n", "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "trd = trd[['PRICE']]\n", "trd['UPTICK'] = trd.apply(uptick)\n", "otp.run(trd, start=otp.dt(2023, 4, 20, 9, 30), end=otp.dt(2023, 4, 20, 9, 35), symbols=['SPY'])" ] } ], "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 }