{ "cells": [ { "attachments": {}, "cell_type": "markdown", "id": "4e11d96f-72ae-4a02-aa8e-4fd1f5d33d71", "metadata": {}, "source": [ "# Use Cases" ] }, { "cell_type": "code", "execution_count": 3, "id": "61932d8e-19c2-48e7-b3c6-bb160e9811b1", "metadata": {}, "outputs": [], "source": [ "import onetick.py as otp" ] }, { "attachments": {}, "cell_type": "markdown", "id": "28c0c0c2-9605-4773-b552-911a3f339bbc", "metadata": {}, "source": [ "# Retrieving Tick Data" ] }, { "cell_type": "code", "execution_count": 4, "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-05-15 09:30:00.000178688PTNC0SPY412.221969-12-31 19:00:000100039195529835251671532023-05-15 09:30:00.0001500161969-12-31 19:00:00.0000000000
12023-05-15 09:30:00.000776704ZNC0SPY412.221969-12-31 19:00:000247039196529835250353552023-05-15 09:30:00.0004500001969-12-31 19:00:00.0000000001
22023-05-15 09:30:00.003603456TTNC0SPY412.221969-12-31 19:00:000100039212628791331579502023-05-15 09:30:00.0031651221969-12-31 19:00:00.0000000000
32023-05-15 09:30:00.006352128KINC0SPY412.241969-12-31 19:00:0001039227529835250983012023-05-15 09:30:00.0060910001969-12-31 19:00:00.0000000000
42023-05-15 09:30:00.007128064KINC0SPY412.241969-12-31 19:00:0003039231529835250983022023-05-15 09:30:00.0068730001969-12-31 19:00:00.0000000000
.........................................................
3102023-05-15 09:30:00.934032640TTNC0SPY412.271969-12-31 19:00:000160040517628791335455382023-05-15 09:30:00.9336780331969-12-31 19:00:00.0000000001
3112023-05-15 09:30:00.975609344DINCT0SPY412.241969-12-31 19:00:0002040543716752405957892023-05-15 09:30:00.6610000002023-05-15 09:30:00.9752415140
3122023-05-15 09:30:00.980264448DINCT0SPY412.271969-12-31 19:00:0001040545716752405962942023-05-15 09:30:00.5535010002023-05-15 09:30:00.9798957000
3132023-05-15 09:30:00.985391616TNC0SPY412.281969-12-31 19:00:000100040547628791335527172023-05-15 09:30:00.9849461041969-12-31 19:00:00.0000000000
3142023-05-15 09:30:00.985394944TQNC0SPY412.281969-12-31 19:00:000100040549628791335527192023-05-15 09:30:00.9849583121969-12-31 19:00:00.0000000001
\n", "

315 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-05-15 09:30:00.000178688 P T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39195 52983525167153 2023-05-15 09:30:00.000150016 1969-12-31 19:00:00.000000000 0\n", "1 2023-05-15 09:30:00.000776704 Z N C 0 SPY 412.22 1969-12-31 19:00:00 0 247 0 39196 52983525035355 2023-05-15 09:30:00.000450000 1969-12-31 19:00:00.000000000 1\n", "2 2023-05-15 09:30:00.003603456 T T N C 0 SPY 412.22 1969-12-31 19:00:00 0 100 0 39212 62879133157950 2023-05-15 09:30:00.003165122 1969-12-31 19:00:00.000000000 0\n", "3 2023-05-15 09:30:00.006352128 K I N C 0 SPY 412.24 1969-12-31 19:00:00 0 1 0 39227 52983525098301 2023-05-15 09:30:00.006091000 1969-12-31 19:00:00.000000000 0\n", "4 2023-05-15 09:30:00.007128064 K I N C 0 SPY 412.24 1969-12-31 19:00:00 0 3 0 39231 52983525098302 2023-05-15 09:30:00.006873000 1969-12-31 19:00:00.000000000 0\n", ".. ... ... ... ... ... .. .. ... ... ... ... ... ... ... ... ... ... ...\n", "310 2023-05-15 09:30:00.934032640 T T N C 0 SPY 412.27 1969-12-31 19:00:00 0 160 0 40517 62879133545538 2023-05-15 09:30:00.933678033 1969-12-31 19:00:00.000000000 1\n", "311 2023-05-15 09:30:00.975609344 D I N C T 0 SPY 412.24 1969-12-31 19:00:00 0 2 0 40543 71675240595789 2023-05-15 09:30:00.661000000 2023-05-15 09:30:00.975241514 0\n", "312 2023-05-15 09:30:00.980264448 D I N C T 0 SPY 412.27 1969-12-31 19:00:00 0 1 0 40545 71675240596294 2023-05-15 09:30:00.553501000 2023-05-15 09:30:00.979895700 0\n", "313 2023-05-15 09:30:00.985391616 T N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40547 62879133552717 2023-05-15 09:30:00.984946104 1969-12-31 19:00:00.000000000 0\n", "314 2023-05-15 09:30:00.985394944 T Q N C 0 SPY 412.28 1969-12-31 19:00:00 0 100 0 40549 62879133552719 2023-05-15 09:30:00.984958312 1969-12-31 19:00:00.000000000 1\n", "\n", "[315 rows x 18 columns]" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "s = otp.dt(2023, 5, 15, 9, 30)\n", "e = otp.dt(2023, 5, 15, 9, 30, 1)\n", "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "otp.run(trd, start=s, end=e, symbols=['SPY'])" ] }, { "attachments": {}, "cell_type": "markdown", "id": "267b2ca5-7cd1-4af2-94f1-4fcbe5d1a0ad", "metadata": { "tags": [] }, "source": [ "(bars)=\n", "# Creating Bars\n", "We create 1-minute bars (`bucket_interval=60` seconds) below." ] }, { "cell_type": "code", "execution_count": 23, "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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
02023-05-15 09:31:00264719412.2900412.0350412.22001446412.0400
12023-05-15 09:32:00218724412.2600412.0000412.05001537412.1600
22023-05-15 09:33:00271364412.2000411.9400412.16501649412.0200
32023-05-15 09:34:00312764412.0200411.6312412.01001827411.8200
42023-05-15 09:35:00252569411.8300411.3600411.81001241411.3983
52023-05-15 09:36:00202980411.6250411.3200411.39001286411.6250
62023-05-15 09:37:00114774411.6800411.4400411.6200841411.5900
72023-05-15 09:38:00152180411.6000411.4100411.5800927411.4300
82023-05-15 09:39:0096940411.5100411.3600411.4300592411.3600
92023-05-15 09:40:00129613411.4650411.2300411.3550884411.4471
102023-05-15 09:41:00111231411.6668411.3600411.4550723411.6300
112023-05-15 09:42:00214502411.7100411.4300411.64001395411.4750
122023-05-15 09:43:00262713411.6600411.4200411.47001379411.4700
132023-05-15 09:44:00106379411.5200411.3520411.4700654411.4400
142023-05-15 09:45:00103743411.5250411.4000411.4200551411.5016
152023-05-15 09:46:00137092411.6600411.5200411.5200871411.6300
162023-05-15 09:47:00189016411.6900411.3600411.63001201411.3900
172023-05-15 09:48:00121331411.4780411.3100411.3900612411.3150
182023-05-15 09:49:00108976411.3900411.1600411.3200664411.2900
192023-05-15 09:50:00112394411.3000411.1200411.3000637411.1900
202023-05-15 09:51:0094153411.2700411.0300411.1900469411.0300
212023-05-15 09:52:00112302411.1900410.9800411.0300687410.9900
222023-05-15 09:53:00114025411.1179410.9700410.9850599410.9900
232023-05-15 09:54:0080763411.1299410.9500410.9850458411.0150
242023-05-15 09:55:0066556411.1099411.0100411.0190435411.0350
252023-05-15 09:56:00131143411.1100410.9100411.0350790410.9205
262023-05-15 09:57:00128274410.9300410.8200410.9205679410.8400
272023-05-15 09:58:00162243410.9500410.8000410.8200953410.8900
282023-05-15 09:59:00105649410.9300410.8300410.8800572410.8700
292023-05-15 10:00:00145019410.9100410.7800410.8600911410.8400
\n", "
" ], "text/plain": [ " Time VOLUME HIGH LOW OPEN COUNT CLOSE\n", "0 2023-05-15 09:31:00 264719 412.2900 412.0350 412.2200 1446 412.0400\n", "1 2023-05-15 09:32:00 218724 412.2600 412.0000 412.0500 1537 412.1600\n", "2 2023-05-15 09:33:00 271364 412.2000 411.9400 412.1650 1649 412.0200\n", "3 2023-05-15 09:34:00 312764 412.0200 411.6312 412.0100 1827 411.8200\n", "4 2023-05-15 09:35:00 252569 411.8300 411.3600 411.8100 1241 411.3983\n", "5 2023-05-15 09:36:00 202980 411.6250 411.3200 411.3900 1286 411.6250\n", "6 2023-05-15 09:37:00 114774 411.6800 411.4400 411.6200 841 411.5900\n", "7 2023-05-15 09:38:00 152180 411.6000 411.4100 411.5800 927 411.4300\n", "8 2023-05-15 09:39:00 96940 411.5100 411.3600 411.4300 592 411.3600\n", "9 2023-05-15 09:40:00 129613 411.4650 411.2300 411.3550 884 411.4471\n", "10 2023-05-15 09:41:00 111231 411.6668 411.3600 411.4550 723 411.6300\n", "11 2023-05-15 09:42:00 214502 411.7100 411.4300 411.6400 1395 411.4750\n", "12 2023-05-15 09:43:00 262713 411.6600 411.4200 411.4700 1379 411.4700\n", "13 2023-05-15 09:44:00 106379 411.5200 411.3520 411.4700 654 411.4400\n", "14 2023-05-15 09:45:00 103743 411.5250 411.4000 411.4200 551 411.5016\n", "15 2023-05-15 09:46:00 137092 411.6600 411.5200 411.5200 871 411.6300\n", "16 2023-05-15 09:47:00 189016 411.6900 411.3600 411.6300 1201 411.3900\n", "17 2023-05-15 09:48:00 121331 411.4780 411.3100 411.3900 612 411.3150\n", "18 2023-05-15 09:49:00 108976 411.3900 411.1600 411.3200 664 411.2900\n", "19 2023-05-15 09:50:00 112394 411.3000 411.1200 411.3000 637 411.1900\n", "20 2023-05-15 09:51:00 94153 411.2700 411.0300 411.1900 469 411.0300\n", "21 2023-05-15 09:52:00 112302 411.1900 410.9800 411.0300 687 410.9900\n", "22 2023-05-15 09:53:00 114025 411.1179 410.9700 410.9850 599 410.9900\n", "23 2023-05-15 09:54:00 80763 411.1299 410.9500 410.9850 458 411.0150\n", "24 2023-05-15 09:55:00 66556 411.1099 411.0100 411.0190 435 411.0350\n", "25 2023-05-15 09:56:00 131143 411.1100 410.9100 411.0350 790 410.9205\n", "26 2023-05-15 09:57:00 128274 410.9300 410.8200 410.9205 679 410.8400\n", "27 2023-05-15 09:58:00 162243 410.9500 410.8000 410.8200 953 410.8900\n", "28 2023-05-15 09:59:00 105649 410.9300 410.8300 410.8800 572 410.8700\n", "29 2023-05-15 10:00:00 145019 410.9100 410.7800 410.8600 911 410.8400" ] }, "execution_count": 23, "metadata": {}, "output_type": "execute_result" } ], "source": [ "trd = otp.DataSource('NYSE_TAQ', tick_type='TRD')\n", "trd, _ = trd[trd['COND'].str.match('^[^O6TUHILNRWZ47QMBCGPV]*$')]\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=60)\n", "otp.run(bars, start=otp.dt(2023,5,15,9,30), end=otp.dt(2023,5,15,10), symbols=['SPY'])" ] }, { "attachments": {}, "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", "
" ] }, { "attachments": {}, "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).\n", "\n", "Note the use of `apply_times_daily` to limit each day's interval to 9:30-4:00pm (plus one minute is added as the minute bar for 9:30-9:31 has the timestamp of 9:31)." ] }, { "cell_type": "code", "execution_count": 21, "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
02023-05-15 09:31:00412.220412.290412.0350412.0400264719
12023-05-15 09:32:00412.050412.260412.0000412.1600218724
22023-05-15 09:33:00412.165412.200411.9400412.0200271364
32023-05-15 09:34:00412.010412.020411.6312411.8200312764
42023-05-15 09:35:00411.810411.830411.3600411.3983252569
.....................
19452023-05-19 15:56:00418.890418.950418.8501418.9200465446
19462023-05-19 15:57:00418.910418.915418.7200418.8700618549
19472023-05-19 15:58:00418.870418.980418.8400418.9500601161
19482023-05-19 15:59:00418.950418.990418.9000418.9600711607
19492023-05-19 16:00:00418.960418.970418.5700418.61001565989
\n", "

1950 rows × 6 columns

\n", "
" ], "text/plain": [ " Time FIRST HIGH LOW LAST VOLUME\n", "0 2023-05-15 09:31:00 412.220 412.290 412.0350 412.0400 264719\n", "1 2023-05-15 09:32:00 412.050 412.260 412.0000 412.1600 218724\n", "2 2023-05-15 09:33:00 412.165 412.200 411.9400 412.0200 271364\n", "3 2023-05-15 09:34:00 412.010 412.020 411.6312 411.8200 312764\n", "4 2023-05-15 09:35:00 411.810 411.830 411.3600 411.3983 252569\n", "... ... ... ... ... ... ...\n", "1945 2023-05-19 15:56:00 418.890 418.950 418.8501 418.9200 465446\n", "1946 2023-05-19 15:57:00 418.910 418.915 418.7200 418.8700 618549\n", "1947 2023-05-19 15:58:00 418.870 418.980 418.8400 418.9500 601161\n", "1948 2023-05-19 15:59:00 418.950 418.990 418.9000 418.9600 711607\n", "1949 2023-05-19 16:00:00 418.960 418.970 418.5700 418.6100 1565989\n", "\n", "[1950 rows x 6 columns]" ] }, "execution_count": 21, "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(2023,5,15,9,31), end=otp.dt(2023,5,19,16,1), symbols=['SPY'], apply_times_daily=True)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "3650ce08-f444-406b-8b12-610a85dd61a2", "metadata": {}, "source": [ "(prevailing-quote)=\n", "# Prevailing quote at the time of a trade" ] }, { "cell_type": "code", "execution_count": 24, "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-05-15 09:30:00.000178688412.22100412.25412.222023-05-15 09:30:00.000174080
12023-05-15 09:30:00.000776704412.22247412.24412.212023-05-15 09:30:00.000715520
22023-05-15 09:30:00.003603456412.22100412.24412.222023-05-15 09:30:00.003562496
32023-05-15 09:30:00.006352128412.241412.25412.222023-05-15 09:30:00.006343936
42023-05-15 09:30:00.007128064412.243412.25412.222023-05-15 09:30:00.007110656
.....................
3102023-05-15 09:30:00.934032640412.27160412.28412.262023-05-15 09:30:00.934030080
3112023-05-15 09:30:00.975609344412.242412.28412.272023-05-15 09:30:00.970691840
3122023-05-15 09:30:00.980264448412.271412.28412.272023-05-15 09:30:00.979763456
3132023-05-15 09:30:00.985391616412.28100412.28412.272023-05-15 09:30:00.985296640
3142023-05-15 09:30:00.985394944412.28100412.28412.272023-05-15 09:30:00.985296640
\n", "

315 rows × 6 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE ASK_PRICE BID_PRICE quote_time\n", "0 2023-05-15 09:30:00.000178688 412.22 100 412.25 412.22 2023-05-15 09:30:00.000174080\n", "1 2023-05-15 09:30:00.000776704 412.22 247 412.24 412.21 2023-05-15 09:30:00.000715520\n", "2 2023-05-15 09:30:00.003603456 412.22 100 412.24 412.22 2023-05-15 09:30:00.003562496\n", "3 2023-05-15 09:30:00.006352128 412.24 1 412.25 412.22 2023-05-15 09:30:00.006343936\n", "4 2023-05-15 09:30:00.007128064 412.24 3 412.25 412.22 2023-05-15 09:30:00.007110656\n", ".. ... ... ... ... ... ...\n", "310 2023-05-15 09:30:00.934032640 412.27 160 412.28 412.26 2023-05-15 09:30:00.934030080\n", "311 2023-05-15 09:30:00.975609344 412.24 2 412.28 412.27 2023-05-15 09:30:00.970691840\n", "312 2023-05-15 09:30:00.980264448 412.27 1 412.28 412.27 2023-05-15 09:30:00.979763456\n", "313 2023-05-15 09:30:00.985391616 412.28 100 412.28 412.27 2023-05-15 09:30:00.985296640\n", "314 2023-05-15 09:30:00.985394944 412.28 100 412.28 412.27 2023-05-15 09:30:00.985296640\n", "\n", "[315 rows x 6 columns]" ] }, "execution_count": 24, "metadata": {}, "output_type": "execute_result" } ], "source": [ "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=s, end=e, symbols=['SPY'])" ] }, { "attachments": {}, "cell_type": "markdown", "id": "2bcfe24e", "metadata": {}, "source": [ "(markouts)=\n", "# 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": null, "id": "16ebab62", "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", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \n", " \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_0BID_PRICE_0quote_time_0ASK_PRICE_1BID_PRICE_1quote_time_1ASK_PRICE_5BID_PRICE_5quote_time_5ASK_PRICE_60BID_PRICE_60quote_time_60ASK_PRICE_600BID_PRICE_600quote_time_600
02023-05-15 09:30:00.000178688412.22100412.26412.232023-05-15 09:29:59.000000000412.25412.222023-05-15 09:30:00.000174080412.28412.272023-05-15 09:30:01.000000000412.23412.212023-05-15 09:30:05.000000000412.06412.052023-05-15 09:31:00.000105984411.45411.442023-05-15 09:40:00.000000000
12023-05-15 09:30:00.000776704412.22247412.26412.232023-05-15 09:29:59.000000000412.24412.212023-05-15 09:30:00.000715520412.28412.272023-05-15 09:30:01.000000000412.23412.212023-05-15 09:30:05.000000000412.06412.052023-05-15 09:31:00.000772608411.45411.442023-05-15 09:40:00.000000000
22023-05-15 09:30:00.003603456412.22100412.26412.232023-05-15 09:29:59.000000000412.24412.222023-05-15 09:30:00.003562496412.28412.272023-05-15 09:30:01.002818816412.23412.212023-05-15 09:30:05.000000000412.06412.052023-05-15 09:31:00.001634816411.45411.442023-05-15 09:40:00.000000000
32023-05-15 09:30:00.006352128412.241412.26412.232023-05-15 09:29:59.005259520412.25412.222023-05-15 09:30:00.006343936412.28412.272023-05-15 09:30:01.004251904412.23412.212023-05-15 09:30:05.000000000412.06412.052023-05-15 09:31:00.005624320411.45411.442023-05-15 09:40:00.006328576
42023-05-15 09:30:00.007128064412.243412.26412.232023-05-15 09:29:59.007053824412.25412.222023-05-15 09:30:00.007110656412.28412.272023-05-15 09:30:01.004251904412.23412.212023-05-15 09:30:05.000000000412.05412.042023-05-15 09:31:00.007117824411.45411.442023-05-15 09:40:00.007125760
..................................................................
3102023-05-15 09:30:00.934032640412.27160412.26412.222023-05-15 09:29:59.837682688412.28412.262023-05-15 09:30:00.934030080412.28412.262023-05-15 09:30:01.931170560412.25412.232023-05-15 09:30:05.898093824412.07412.062023-05-15 09:31:00.927816448411.44411.432023-05-15 09:40:00.928655104
3112023-05-15 09:30:00.975609344412.242412.25412.222023-05-15 09:29:59.970543872412.28412.272023-05-15 09:30:00.970691840412.29412.262023-05-15 09:30:01.972468480412.25412.242023-05-15 09:30:05.973397760412.07412.062023-05-15 09:31:00.972016640411.44411.432023-05-15 09:40:00.954621952
3122023-05-15 09:30:00.980264448412.271412.25412.222023-05-15 09:29:59.970543872412.28412.272023-05-15 09:30:00.979763456412.29412.272023-05-15 09:30:01.978472704412.26412.242023-05-15 09:30:05.978229504412.07412.062023-05-15 09:31:00.972016640411.44411.432023-05-15 09:40:00.954621952
3132023-05-15 09:30:00.985391616412.28100412.25412.222023-05-15 09:29:59.970543872412.28412.272023-05-15 09:30:00.985296640412.29412.272023-05-15 09:30:01.985324032412.26412.242023-05-15 09:30:05.978229504412.07412.062023-05-15 09:31:00.972016640411.44411.432023-05-15 09:40:00.954621952
3142023-05-15 09:30:00.985394944412.28100412.25412.222023-05-15 09:29:59.970543872412.28412.272023-05-15 09:30:00.985296640412.29412.272023-05-15 09:30:01.985324032412.26412.242023-05-15 09:30:05.978229504412.07412.062023-05-15 09:31:00.972016640411.44411.432023-05-15 09:40:00.954621952
\n", "

315 rows × 21 columns

\n", "
" ], "text/plain": [ " Time PRICE SIZE ASK_PRICE_-1 BID_PRICE_-1 quote_time_-1 ASK_PRICE_0 BID_PRICE_0 quote_time_0 ASK_PRICE_1 BID_PRICE_1 quote_time_1 ASK_PRICE_5 BID_PRICE_5 quote_time_5 ASK_PRICE_60 BID_PRICE_60 quote_time_60 ASK_PRICE_600 BID_PRICE_600 quote_time_600\n", "0 2023-05-15 09:30:00.000178688 412.22 100 412.26 412.23 2023-05-15 09:29:59.000000000 412.25 412.22 2023-05-15 09:30:00.000174080 412.28 412.27 2023-05-15 09:30:01.000000000 412.23 412.21 2023-05-15 09:30:05.000000000 412.06 412.05 2023-05-15 09:31:00.000105984 411.45 411.44 2023-05-15 09:40:00.000000000\n", "1 2023-05-15 09:30:00.000776704 412.22 247 412.26 412.23 2023-05-15 09:29:59.000000000 412.24 412.21 2023-05-15 09:30:00.000715520 412.28 412.27 2023-05-15 09:30:01.000000000 412.23 412.21 2023-05-15 09:30:05.000000000 412.06 412.05 2023-05-15 09:31:00.000772608 411.45 411.44 2023-05-15 09:40:00.000000000\n", "2 2023-05-15 09:30:00.003603456 412.22 100 412.26 412.23 2023-05-15 09:29:59.000000000 412.24 412.22 2023-05-15 09:30:00.003562496 412.28 412.27 2023-05-15 09:30:01.002818816 412.23 412.21 2023-05-15 09:30:05.000000000 412.06 412.05 2023-05-15 09:31:00.001634816 411.45 411.44 2023-05-15 09:40:00.000000000\n", "3 2023-05-15 09:30:00.006352128 412.24 1 412.26 412.23 2023-05-15 09:29:59.005259520 412.25 412.22 2023-05-15 09:30:00.006343936 412.28 412.27 2023-05-15 09:30:01.004251904 412.23 412.21 2023-05-15 09:30:05.000000000 412.06 412.05 2023-05-15 09:31:00.005624320 411.45 411.44 2023-05-15 09:40:00.006328576\n", "4 2023-05-15 09:30:00.007128064 412.24 3 412.26 412.23 2023-05-15 09:29:59.007053824 412.25 412.22 2023-05-15 09:30:00.007110656 412.28 412.27 2023-05-15 09:30:01.004251904 412.23 412.21 2023-05-15 09:30:05.000000000 412.05 412.04 2023-05-15 09:31:00.007117824 411.45 411.44 2023-05-15 09:40:00.007125760\n", ".. ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...\n", "310 2023-05-15 09:30:00.934032640 412.27 160 412.26 412.22 2023-05-15 09:29:59.837682688 412.28 412.26 2023-05-15 09:30:00.934030080 412.28 412.26 2023-05-15 09:30:01.931170560 412.25 412.23 2023-05-15 09:30:05.898093824 412.07 412.06 2023-05-15 09:31:00.927816448 411.44 411.43 2023-05-15 09:40:00.928655104\n", "311 2023-05-15 09:30:00.975609344 412.24 2 412.25 412.22 2023-05-15 09:29:59.970543872 412.28 412.27 2023-05-15 09:30:00.970691840 412.29 412.26 2023-05-15 09:30:01.972468480 412.25 412.24 2023-05-15 09:30:05.973397760 412.07 412.06 2023-05-15 09:31:00.972016640 411.44 411.43 2023-05-15 09:40:00.954621952\n", "312 2023-05-15 09:30:00.980264448 412.27 1 412.25 412.22 2023-05-15 09:29:59.970543872 412.28 412.27 2023-05-15 09:30:00.979763456 412.29 412.27 2023-05-15 09:30:01.978472704 412.26 412.24 2023-05-15 09:30:05.978229504 412.07 412.06 2023-05-15 09:31:00.972016640 411.44 411.43 2023-05-15 09:40:00.954621952\n", "313 2023-05-15 09:30:00.985391616 412.28 100 412.25 412.22 2023-05-15 09:29:59.970543872 412.28 412.27 2023-05-15 09:30:00.985296640 412.29 412.27 2023-05-15 09:30:01.985324032 412.26 412.24 2023-05-15 09:30:05.978229504 412.07 412.06 2023-05-15 09:31:00.972016640 411.44 411.43 2023-05-15 09:40:00.954621952\n", "314 2023-05-15 09:30:00.985394944 412.28 100 412.25 412.22 2023-05-15 09:29:59.970543872 412.28 412.27 2023-05-15 09:30:00.985296640 412.29 412.27 2023-05-15 09:30:01.985324032 412.26 412.24 2023-05-15 09:30:05.978229504 412.07 412.06 2023-05-15 09:31:00.972016640 411.44 411.43 2023-05-15 09:40:00.954621952\n", "\n", "[315 rows x 21 columns]" ] }, "metadata": {}, "output_type": "display_data" } ], "source": [ "markouts = [-1, 0, 1, 5, 60, 600] \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", " qte = qte.time_interval_shift(m * 1000)\n", " qte_by_markout.append(qte)\n", "\n", "trd = otp.join_by_time([trd] + qte_by_markout)\n", "otp.run(trd, start=s, end=e, symbols=['SPY'], apply_times_daily=True)" ] }, { "attachments": {}, "cell_type": "markdown", "id": "f3eb9bbd-7fa8-4fa0-80f2-3a48ce1683bc", "metadata": {}, "source": [ "# Interval Metrics (e.g., VWAP)" ] }, { "cell_type": "code", "execution_count": 25, "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-05-15 09:30:01412.212012
\n", "
" ], "text/plain": [ " Time market_vwap\n", "0 2023-05-15 09:30:01 412.212012" ] }, "execution_count": 25, "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=s, end=e, symbols=['SPY'])" ] }, { "attachments": {}, "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": 31, "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
02023-05-15 09:30:00.0002023-05-15 09:30:00.0002023-05-15 09:30:01.000SPY
12023-05-15 09:30:00.0012023-05-15 09:30:07.9342023-05-15 09:30:03.556QQQ
\n", "
" ], "text/plain": [ " Time arrival exit sym\n", "0 2023-05-15 09:30:00.000 2023-05-15 09:30:00.000 2023-05-15 09:30:01.000 SPY\n", "1 2023-05-15 09:30:00.001 2023-05-15 09:30:07.934 2023-05-15 09:30:03.556 QQQ" ] }, "execution_count": 31, "metadata": {}, "output_type": "execute_result" } ], "source": [ "orders = otp.Ticks(arrival=[s, s+otp.Milli(7934)], \n", " exit=[e, e+otp.Milli(2556)],\n", " sym=['SPY', 'QQQ'])\n", "otp.run(orders, start=s, end=s+otp.Day(1))" ] }, { "cell_type": "code", "execution_count": 5, "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
02023-05-15 09:30:00.000412.2120122023-05-15 09:30:00.0002023-05-15 09:30:01.000SPY
12023-05-15 09:30:00.001325.3189882023-05-15 09:30:07.9342023-05-15 09:30:10.556QQQ
\n", "
" ], "text/plain": [ " Time market_vwap arrival exit sym\n", "0 2023-05-15 09:30:00.000 412.212012 2023-05-15 09:30:00.000 2023-05-15 09:30:01.000 SPY\n", "1 2023-05-15 09:30:00.001 325.318988 2023-05-15 09:30:07.934 2023-05-15 09:30:10.556 QQQ" ] }, "execution_count": 5, "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=[s, s+otp.Milli(7934)], \n", " exit=[e, e+otp.Milli(9556)],\n", " sym=['SPY', 'QQQ'])\n", "orders = orders.join_with_query(vwap, start=orders['arrival'], end=orders['exit'], symbol=orders['sym'])\n", "otp.run(orders, start=s, end=s+otp.Day(1))" ] }, { "attachments": {}, "cell_type": "markdown", "id": "51eac2e0", "metadata": {}, "source": [ "A more efficient implementation is also available with [symbol parameters](vwap-symbol-params)." ] }, { "attachments": {}, "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": 17, "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']) " ] }, { "attachments": {}, "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": 18, "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()" ] }, { "attachments": {}, "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": 21, "id": "21c69f89-2b82-4dc9-a06f-2b12607a80da", "metadata": { "tags": [] }, "outputs": [ { "name": "stdout", "output_type": "stream", "text": [ "..................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................\n", "\n", "2023-03-31 14:02:16.717255 {'PRICE': 405.54, 'short': 405.70794973568997, 'long': 405.70836495181135, 'buy': 0.0, 'sell': 1.0}\n", "SELL\n", "\n", "................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................." ] } ], "source": [ "# timestamps appear in GMT\n", "cb = GoldenCrossCallback()\n", "otp.run(trd, symbols=['SPY'],\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", ")" ] }, { "attachments": {}, "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": 11, "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-05-15 09:30:00.000178688412.22NaN
12023-05-15 09:30:00.000776704412.220.0
22023-05-15 09:30:00.003603456412.220.0
32023-05-15 09:30:00.006352128412.241.0
42023-05-15 09:30:00.007128064412.240.0
............
3102023-05-15 09:30:00.934032640412.270.0
3112023-05-15 09:30:00.975609344412.24-1.0
3122023-05-15 09:30:00.980264448412.271.0
3132023-05-15 09:30:00.985391616412.281.0
3142023-05-15 09:30:00.985394944412.280.0
\n", "

315 rows × 3 columns

\n", "
" ], "text/plain": [ " Time PRICE UPTICK\n", "0 2023-05-15 09:30:00.000178688 412.22 NaN\n", "1 2023-05-15 09:30:00.000776704 412.22 0.0\n", "2 2023-05-15 09:30:00.003603456 412.22 0.0\n", "3 2023-05-15 09:30:00.006352128 412.24 1.0\n", "4 2023-05-15 09:30:00.007128064 412.24 0.0\n", ".. ... ... ...\n", "310 2023-05-15 09:30:00.934032640 412.27 0.0\n", "311 2023-05-15 09:30:00.975609344 412.24 -1.0\n", "312 2023-05-15 09:30:00.980264448 412.27 1.0\n", "313 2023-05-15 09:30:00.985391616 412.28 1.0\n", "314 2023-05-15 09:30:00.985394944 412.28 0.0\n", "\n", "[315 rows x 3 columns]" ] }, "execution_count": 11, "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=s, end=e, symbols=['SPY'])" ] }, { "cell_type": "markdown", "id": "936584a5", "metadata": {}, "source": [ "(realizedpandl)=\n", "# Realized P&L on the FIFO basis\n", "Realized P&L is computed by keeping track of each buy and sell in the chronological order and updating the total using the oldest matching execution from the opposite side (FIFO).\n", "We'll use the following sequence of trades for illustration." ] }, { "cell_type": "code", "execution_count": 1, "id": "94818a9c", "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", "
TimeSIDEPRICESIZE
02003-12-01 00:00:00.000B1.0700
12003-12-01 00:00:00.001B2.020
22003-12-01 00:00:00.002B3.0570
32003-12-01 00:00:00.003S2.5600
42003-12-01 00:00:00.004S4.0100
52003-12-01 00:00:00.005S5.0100
62003-12-01 00:00:00.006S6.0100
72003-12-01 00:00:00.007S7.0100
82003-12-01 00:00:00.008B3.0150
92003-12-01 00:00:00.009B4.010
102003-12-01 00:00:00.010S1.0100
\n", "
" ], "text/plain": [ " Time SIDE PRICE SIZE\n", "0 2003-12-01 00:00:00.000 B 1.0 700\n", "1 2003-12-01 00:00:00.001 B 2.0 20\n", "2 2003-12-01 00:00:00.002 B 3.0 570\n", "3 2003-12-01 00:00:00.003 S 2.5 600\n", "4 2003-12-01 00:00:00.004 S 4.0 100\n", "5 2003-12-01 00:00:00.005 S 5.0 100\n", "6 2003-12-01 00:00:00.006 S 6.0 100\n", "7 2003-12-01 00:00:00.007 S 7.0 100\n", "8 2003-12-01 00:00:00.008 B 3.0 150\n", "9 2003-12-01 00:00:00.009 B 4.0 10\n", "10 2003-12-01 00:00:00.010 S 1.0 100" ] }, "execution_count": 1, "metadata": {}, "output_type": "execute_result" } ], "source": [ "import onetick.py as otp\n", "trades = otp.Ticks(\n", " SIDE=['B', 'B', 'B', 'S', 'S', 'S', 'S', 'S', 'B', 'B', 'S'],\n", " PRICE=[1.0, 2.0, 3.0, 2.5, 4.0, 5.0, 6.0, 7.0, 3.0, 4.0, 1.0],\n", " SIZE=[700, 20, 570, 600, 100, 100, 100, 100, 150, 10, 100],\n", ")\n", "otp.run(trades)" ] }, { "cell_type": "markdown", "id": "8a65c891", "metadata": {}, "source": [ "We define the deque variables to keep the buy and sell trades as they arrive. Note that the variables are associated with the `trades` time series." ] }, { "cell_type": "code", "execution_count": 2, "id": "aec41e28", "metadata": {}, "outputs": [], "source": [ "trades.state_vars['BUY_DEQUE'] = otp.state.tick_deque()\n", "trades.state_vars['SELL_DEQUE'] = otp.state.tick_deque()" ] }, { "cell_type": "markdown", "id": "01a62016", "metadata": {}, "source": [ "As every trade arrives, we apply the following function, which updates the deques and computes the realized profit from the trade." ] }, { "cell_type": "code", "execution_count": 3, "id": "71c010c4", "metadata": {}, "outputs": [], "source": [ "def fifo_computation_of_realized_profit(tick):\n", " buy_tick = otp.tick_deque_tick()\n", " sell_tick = otp.tick_deque_tick()\n", " tick['PROFIT'] = 0.0\n", "\n", " if tick['SIDE'] == 'B':\n", " tick.state_vars['BUY_DEQUE'].push_back(tick)\n", " else:\n", " tick.state_vars['SELL_DEQUE'].push_back(tick)\n", "\n", " while tick.state_vars['BUY_DEQUE'].get_size() > 0 and tick.state_vars['SELL_DEQUE'].get_size() > 0:\n", " tick.state_vars['BUY_DEQUE'].get_tick(0, buy_tick)\n", " tick.state_vars['SELL_DEQUE'].get_tick(0, sell_tick)\n", " if buy_tick['SIZE'] > sell_tick['SIZE']:\n", " tick['PROFIT'] += sell_tick['SIZE'] * (sell_tick['PRICE'] - buy_tick['PRICE'])\n", " buy_tick['SIZE'] -= sell_tick['SIZE']\n", " sell_tick['SIZE'] = 0\n", " else:\n", " tick['PROFIT'] += buy_tick['SIZE'] * (sell_tick['PRICE'] - buy_tick['PRICE'])\n", " sell_tick['SIZE'] -= buy_tick['SIZE']\n", " buy_tick['SIZE'] = 0\n", "\n", " if buy_tick['SIZE'] == 0:\n", " tick.state_vars['BUY_DEQUE'].pop_front()\n", " if sell_tick['SIZE'] == 0:\n", " tick.state_vars['SELL_DEQUE'].pop_front()" ] }, { "cell_type": "markdown", "id": "805977f4", "metadata": {}, "source": [ "Total realized profit can now be calculated by applying the function to every trade." ] }, { "cell_type": "code", "execution_count": 4, "id": "4bb07092", "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", "
TimeSIDEPRICESIZEPROFITTOTAL_PROFIT
02003-12-01 00:00:00.000B1.07000.00.0
12003-12-01 00:00:00.001B2.0200.00.0
22003-12-01 00:00:00.002B3.05700.00.0
32003-12-01 00:00:00.003S2.5600900.0900.0
42003-12-01 00:00:00.004S4.0100300.01200.0
52003-12-01 00:00:00.005S5.0100220.01420.0
62003-12-01 00:00:00.006S6.0100300.01720.0
72003-12-01 00:00:00.007S7.0100400.02120.0
82003-12-01 00:00:00.008B3.01500.02120.0
92003-12-01 00:00:00.009B4.0100.02120.0
102003-12-01 00:00:00.010S1.0100-200.01920.0
\n", "
" ], "text/plain": [ " Time SIDE PRICE SIZE PROFIT TOTAL_PROFIT\n", "0 2003-12-01 00:00:00.000 B 1.0 700 0.0 0.0\n", "1 2003-12-01 00:00:00.001 B 2.0 20 0.0 0.0\n", "2 2003-12-01 00:00:00.002 B 3.0 570 0.0 0.0\n", "3 2003-12-01 00:00:00.003 S 2.5 600 900.0 900.0\n", "4 2003-12-01 00:00:00.004 S 4.0 100 300.0 1200.0\n", "5 2003-12-01 00:00:00.005 S 5.0 100 220.0 1420.0\n", "6 2003-12-01 00:00:00.006 S 6.0 100 300.0 1720.0\n", "7 2003-12-01 00:00:00.007 S 7.0 100 400.0 2120.0\n", "8 2003-12-01 00:00:00.008 B 3.0 150 0.0 2120.0\n", "9 2003-12-01 00:00:00.009 B 4.0 10 0.0 2120.0\n", "10 2003-12-01 00:00:00.010 S 1.0 100 -200.0 1920.0" ] }, "execution_count": 4, "metadata": {}, "output_type": "execute_result" } ], "source": [ "trades = trades.script(fifo_computation_of_realized_profit)\n", "trades = trades.agg({'TOTAL_PROFIT': otp.agg.sum('PROFIT')}, running=True, all_fields=True)\n", "otp.run(trades)" ] }, { "cell_type": "code", "execution_count": null, "id": "9516e68e", "metadata": {}, "outputs": [], "source": [] } ], "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.6" } }, "nbformat": 4, "nbformat_minor": 5 }