ManagedkdbInsights/processing_data/query_algoseek.ipynb (3,216 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"id": "1334d92e-34f4-448c-b6af-73cba89e9d6d",
"metadata": {
"tags": []
},
"source": [
"# Amazon FinSpace Managed kdb Insights: TAQ\n",
"\n",
"Query the TAQ table\n",
"\n",
"## Algoseek LLC Data\n",
"Trade and Quote data has been provided by [AlgoSeek LLC](https://www.algoseek.com/), you can learn more about their data offerings from their home page.\n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "11e9b62a-9cda-4a57-99ca-3b13aff6b7f3",
"metadata": {},
"outputs": [],
"source": [
"import os\n",
"import subprocess\n",
"import boto3\n",
"import json\n",
"import datetime\n",
"\n",
"import pandas as pd\n",
"import pykx as kx\n",
"\n",
"pd.set_option('display.max_rows', 10)\n",
"kx.q.system.display_size = [50, 1000]\n",
"\n",
"from managed_kx import *\n",
"from env import *\n",
"\n",
"# ----------------------------------------------------------------\n",
"CLUSTER_NAME=\"demo_csv_cluster\"\n",
"# ----------------------------------------------------------------"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "f00308a5-0f39-4a28-abb9-f00a53a226bc",
"metadata": {},
"outputs": [],
"source": [
"# Using credentials and create service client\n",
"session = boto3.Session()\n",
"\n",
"# create finspace client\n",
"client = session.client(service_name='finspace')"
]
},
{
"cell_type": "markdown",
"id": "672cfe00-e3e1-4406-9548-744c68c9d51d",
"metadata": {},
"source": [
"# Connect to Cluster with PyKX"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "5cae1dcc",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"hdb = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)"
]
},
{
"cell_type": "markdown",
"id": "32c26a3e-e51e-4981-951b-efca08e20e9a",
"metadata": {
"tags": []
},
"source": [
"# Tables and counts"
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "417ec054-42df-43de-b739-25099e203a31",
"metadata": {
"tags": []
},
"outputs": [
{
"data": {
"text/plain": [
"pykx.SymbolAtom(pykx.q('`.'))"
]
},
"execution_count": 4,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# be sure the database is loaded\n",
"hdb('.Q.l `$.aws.akdbp,\"/\",.aws.akdb')\n",
"\n",
"# delete the res table\n",
"hdb(\"delete r from `.\")"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "3fcc7498-9ca8-4e3c-ba7a-44629c7e44b8",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"================================================================================\n",
"All Tables and Counts\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>res</th>\n",
" <td>17211</td>\n",
" </tr>\n",
" <tr>\n",
" <th>taq</th>\n",
" <td>15144970</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Dictionary(pykx.q('\n",
"res| 17211\n",
"taq| 15144970\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# inventory of tables in the database and rows in each\n",
"print(80*'=')\n",
"print(\"All Tables and Counts\")\n",
"display( hdb(\"tables[]!count each value each tables[]\") )\n"
]
},
{
"cell_type": "markdown",
"id": "74c1c9ed-7141-471a-9484-175b09890e47",
"metadata": {},
"source": [
"# Table Summary\n",
"- Rows \n",
"- Schema\n",
"- Rows per Date\n",
"- Sample "
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "125c2a8e-1880-4043-9230-1089778e5bf8",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"================================================================================\n",
"Table: res: 17,211\n",
"--------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>t</th>\n",
" <th>f</th>\n",
" <th>a</th>\n",
" </tr>\n",
" <tr>\n",
" <th>c</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>date</th>\n",
" <td>\"d\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Ticker</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td>p</td>\n",
" </tr>\n",
" <tr>\n",
" <th>EventType</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>minute</th>\n",
" <td>\"u\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Events</th>\n",
" <td>\"j\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Avg_Price</th>\n",
" <td>\"f\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Volume</th>\n",
" <td>\"j\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Max_Price</th>\n",
" <td>\"f\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Min_Price</th>\n",
" <td>\"f\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>First_Price</th>\n",
" <td>\"f\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Last_Price</th>\n",
" <td>\"f\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"c | t f a\n",
"-----------| -----\n",
"date | d \n",
"Ticker | s p\n",
"EventType | s \n",
"minute | u \n",
"Events | j \n",
"Avg_Price | f \n",
"Volume | j \n",
"Max_Price | f \n",
"Min_Price | f \n",
"First_Price| f \n",
"Last_Price | f \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>rows</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2021.01.05</th>\n",
" <td>17211</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"date | rows \n",
"----------| -----\n",
"2021.01.05| 17211\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Events</th>\n",
" <th>Avg_Price</th>\n",
" <th>Volume</th>\n",
" <th>Max_Price</th>\n",
" <th>Min_Price</th>\n",
" <th>First_Price</th>\n",
" <th>Last_Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>EventType</th>\n",
" <th>minute</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"3\" valign=\"top\">2021.01.05</th>\n",
" <th rowspan=\"3\" valign=\"top\">AMZN</th>\n",
" <th rowspan=\"3\" valign=\"top\">QUOTE ASK</th>\n",
" <th>04:00</th>\n",
" <td>13</td>\n",
" <td>2708.931</td>\n",
" <td>1300</td>\n",
" <td>3202f</td>\n",
" <td>0f</td>\n",
" <td>0f</td>\n",
" <td>3201f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:01</th>\n",
" <td>21</td>\n",
" <td>2895.349</td>\n",
" <td>2700</td>\n",
" <td>3200.29</td>\n",
" <td>0f</td>\n",
" <td>3200.29</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:02</th>\n",
" <td>28</td>\n",
" <td>1601.335</td>\n",
" <td>1600</td>\n",
" <td>3221.9</td>\n",
" <td>0f</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"date Ticker EventType minute| Events Avg_Price Volume Max_Price Min_Price First_Price Last_Price\n",
"----------------------------------| ------------------------------------------------------------------\n",
"2021.01.05 AMZN QUOTE ASK 04:00 | 13 2708.931 1300 3202 0 0 3201 \n",
"2021.01.05 AMZN QUOTE ASK 04:01 | 21 2895.349 2700 3200.29 0 3200.29 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:02 | 28 1601.335 1600 3221.9 0 3200 3213.49 \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"================================================================================\n",
"Table: taq: 15,144,970\n",
"--------------------------------------------------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>t</th>\n",
" <th>f</th>\n",
" <th>a</th>\n",
" </tr>\n",
" <tr>\n",
" <th>c</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>date</th>\n",
" <td>\"d\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Ticker</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td>p</td>\n",
" </tr>\n",
" <tr>\n",
" <th>Timestamp</th>\n",
" <td>\"n\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>EventType</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Price</th>\n",
" <td>\"f\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Quantity</th>\n",
" <td>\"j\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Exchange</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>Conditions</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>FileName</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" <tr>\n",
" <th>FileExtension</th>\n",
" <td>\"s\"</td>\n",
" <td></td>\n",
" <td></td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"c | t f a\n",
"-------------| -----\n",
"date | d \n",
"Ticker | s p\n",
"Timestamp | n \n",
"EventType | s \n",
"Price | f \n",
"Quantity | j \n",
"Exchange | s \n",
"Conditions | s \n",
"FileName | s \n",
"FileExtension| s \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>rows</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2021.01.04</th>\n",
" <td>8970726</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021.01.05</th>\n",
" <td>6174244</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"date | rows \n",
"----------| -------\n",
"2021.01.04| 8970726\n",
"2021.01.05| 6174244\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>Timestamp</th>\n",
" <th>EventType</th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" <th>Exchange</th>\n",
" <th>Conditions</th>\n",
" <th>FileName</th>\n",
" <th>FileExtension</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2021.01.05</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.021680902</td>\n",
" <td>TRADE</td>\n",
" <td>3190.01</td>\n",
" <td>63</td>\n",
" <td>ARCA</td>\n",
" <td>80000401</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2021.01.05</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.023083159</td>\n",
" <td>QUOTE BID</td>\n",
" <td>2000f</td>\n",
" <td>400</td>\n",
" <td>ARCA</td>\n",
" <td>00000001</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2021.01.05</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.023083159</td>\n",
" <td>QUOTE ASK</td>\n",
" <td>0f</td>\n",
" <td>0</td>\n",
" <td>ARCA</td>\n",
" <td>00000001</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Table(pykx.q('\n",
"date Ticker Timestamp EventType Price Quantity Exchange Conditions FileName FileExtension\n",
"------------------------------------------------------------------------------------------------------------\n",
"2021.01.05 AMZN 0D04:00:00.021680902 TRADE 3190.01 63 ARCA 80000401 AMZN gz \n",
"2021.01.05 AMZN 0D04:00:00.023083159 QUOTE BID 2000 400 ARCA 00000001 AMZN gz \n",
"2021.01.05 AMZN 0D04:00:00.023083159 QUOTE ASK 0 0 ARCA 00000001 AMZN gz \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"tables = hdb(\"tables[]\").py()\n",
"\n",
"# For each table: schema, and samples and counts\n",
"for t in tables:\n",
" c=hdb(f\"count {t}\").py()\n",
"\n",
" print(80*'=')\n",
" print (f'Table: {t}: {c:,}')\n",
" print(80*'-')\n",
" display( hdb(f\"meta {t}\") )\n",
"# display( hdb(f\"select rows:count i by Ticker from {t} where date=max date \") )\n",
" display( hdb(f\"select rows:count i by date from {t}\") )\n",
" display( hdb(f\"select from {t} where date = max date, i<3\") )"
]
},
{
"cell_type": "markdown",
"id": "08d4e559-be18-47c2-9aa4-8f1bf8747dcd",
"metadata": {},
"source": [
"# Summarize Events\n",
"Number of events, average price, total volume grouped by date, Ticker, and EventType\n",
"\n",
"**Note:** SPY is an ETF, no asks"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "48f39e7c-5a90-4c03-90cb-11103d2e1365",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<div>\n",
"<style scoped>\n",
" .dataframe tbody tr th:only-of-type {\n",
" vertical-align: middle;\n",
" }\n",
"\n",
" .dataframe tbody tr th {\n",
" vertical-align: top;\n",
" }\n",
"\n",
" .dataframe thead th {\n",
" text-align: right;\n",
" }\n",
"</style>\n",
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Events</th>\n",
" <th>Avg_Price</th>\n",
" <th>Volume</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>EventType</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"6\" valign=\"top\">2021-01-05</th>\n",
" <th rowspan=\"6\" valign=\"top\">AMZN</th>\n",
" <th>QUOTE ASK</th>\n",
" <td>615762</td>\n",
" <td>3191.515952</td>\n",
" <td>75072400</td>\n",
" </tr>\n",
" <tr>\n",
" <th>QUOTE ASK NB</th>\n",
" <td>158824</td>\n",
" <td>3203.824669</td>\n",
" <td>20565900</td>\n",
" </tr>\n",
" <tr>\n",
" <th>QUOTE BID</th>\n",
" <td>616379</td>\n",
" <td>3150.847312</td>\n",
" <td>74631400</td>\n",
" </tr>\n",
" <tr>\n",
" <th>QUOTE BID NB</th>\n",
" <td>158826</td>\n",
" <td>3201.910941</td>\n",
" <td>19186200</td>\n",
" </tr>\n",
" <tr>\n",
" <th>TRADE</th>\n",
" <td>129553</td>\n",
" <td>3202.309602</td>\n",
" <td>1779429</td>\n",
" </tr>\n",
" <tr>\n",
" <th>TRADE NB</th>\n",
" <td>6077</td>\n",
" <td>3203.388657</td>\n",
" <td>1091928</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" Events Avg_Price Volume\n",
"date Ticker EventType \n",
"2021-01-05 AMZN QUOTE ASK 615762 3191.515952 75072400\n",
" QUOTE ASK NB 158824 3203.824669 20565900\n",
" QUOTE BID 616379 3150.847312 74631400\n",
" QUOTE BID NB 158826 3201.910941 19186200\n",
" TRADE 129553 3202.309602 1779429\n",
" TRADE NB 6077 3203.388657 1091928"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Summary of a day's events for a few tickers\n",
"q=\"\"\"select Events:count Price, Avg_Price:avg Price, Volume:sum Quantity \n",
" by date, Ticker, EventType \n",
" from taq\n",
" where date = max date, any Ticker in/:(`AMZN;`SPY)\"\"\"\n",
"\n",
"res = hdb(q).pd()\n",
"display( res )"
]
},
{
"cell_type": "markdown",
"id": "ee363da1-7c18-439a-a4bb-50b3d541d23b",
"metadata": {},
"source": [
"# Calculate 1 Minute Bars"
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "510bd8b4-7218-4b8c-adde-01dcf8c05c56",
"metadata": {
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Events</th>\n",
" <th>Avg_Price</th>\n",
" <th>Volume</th>\n",
" <th>First_Price</th>\n",
" <th>Last_Price</th>\n",
" <th>Min_Price</th>\n",
" <th>Max_Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Ticker</th>\n",
" <th>EventType</th>\n",
" <th>date</th>\n",
" <th>minute</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"49\" valign=\"top\">AMZN</th>\n",
" <th rowspan=\"49\" valign=\"top\">TRADE</th>\n",
" <th rowspan=\"49\" valign=\"top\">2021.01.05</th>\n",
" <th>04:00</th>\n",
" <td>9</td>\n",
" <td>3191.7</td>\n",
" <td>179</td>\n",
" <td>3190.01</td>\n",
" <td>3192.37</td>\n",
" <td>3190.01</td>\n",
" <td>3195f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:01</th>\n",
" <td>8</td>\n",
" <td>3189.274</td>\n",
" <td>214</td>\n",
" <td>3190.01</td>\n",
" <td>3189.99</td>\n",
" <td>3187f</td>\n",
" <td>3190.01</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:02</th>\n",
" <td>7</td>\n",
" <td>3191.156</td>\n",
" <td>52</td>\n",
" <td>3188.01</td>\n",
" <td>3194.59</td>\n",
" <td>3188f</td>\n",
" <td>3194.59</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:03</th>\n",
" <td>3</td>\n",
" <td>3192.72</td>\n",
" <td>51</td>\n",
" <td>3190f</td>\n",
" <td>3194.59</td>\n",
" <td>3190f</td>\n",
" <td>3194.59</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:04</th>\n",
" <td>7</td>\n",
" <td>3190.477</td>\n",
" <td>201</td>\n",
" <td>3191.19</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3191.35</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:05</th>\n",
" <td>3</td>\n",
" <td>3190.017</td>\n",
" <td>15</td>\n",
" <td>3190.05</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190.05</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:06</th>\n",
" <td>2</td>\n",
" <td>3191.305</td>\n",
" <td>13</td>\n",
" <td>3190.61</td>\n",
" <td>3192f</td>\n",
" <td>3190.61</td>\n",
" <td>3192f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:08</th>\n",
" <td>1</td>\n",
" <td>3190.92</td>\n",
" <td>16</td>\n",
" <td>3190.92</td>\n",
" <td>3190.92</td>\n",
" <td>3190.92</td>\n",
" <td>3190.92</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:09</th>\n",
" <td>6</td>\n",
" <td>3190.802</td>\n",
" <td>26</td>\n",
" <td>3190.96</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190.97</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:10</th>\n",
" <td>1</td>\n",
" <td>3190f</td>\n",
" <td>1</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:11</th>\n",
" <td>1</td>\n",
" <td>3190.22</td>\n",
" <td>1</td>\n",
" <td>3190.22</td>\n",
" <td>3190.22</td>\n",
" <td>3190.22</td>\n",
" <td>3190.22</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:12</th>\n",
" <td>1</td>\n",
" <td>3190.04</td>\n",
" <td>1</td>\n",
" <td>3190.04</td>\n",
" <td>3190.04</td>\n",
" <td>3190.04</td>\n",
" <td>3190.04</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:13</th>\n",
" <td>2</td>\n",
" <td>3190.445</td>\n",
" <td>2</td>\n",
" <td>3190.21</td>\n",
" <td>3190.68</td>\n",
" <td>3190.21</td>\n",
" <td>3190.68</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:15</th>\n",
" <td>4</td>\n",
" <td>3190.887</td>\n",
" <td>7</td>\n",
" <td>3190.78</td>\n",
" <td>3190.7</td>\n",
" <td>3190.7</td>\n",
" <td>3191.07</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:16</th>\n",
" <td>3</td>\n",
" <td>3191.66</td>\n",
" <td>4</td>\n",
" <td>3191.16</td>\n",
" <td>3191.91</td>\n",
" <td>3191.16</td>\n",
" <td>3191.91</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:17</th>\n",
" <td>4</td>\n",
" <td>3191.972</td>\n",
" <td>11</td>\n",
" <td>3191.89</td>\n",
" <td>3192f</td>\n",
" <td>3191.89</td>\n",
" <td>3192f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:18</th>\n",
" <td>1</td>\n",
" <td>3192f</td>\n",
" <td>4</td>\n",
" <td>3192f</td>\n",
" <td>3192f</td>\n",
" <td>3192f</td>\n",
" <td>3192f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:19</th>\n",
" <td>3</td>\n",
" <td>3192.997</td>\n",
" <td>30</td>\n",
" <td>3192.99</td>\n",
" <td>3193f</td>\n",
" <td>3192.99</td>\n",
" <td>3193f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:20</th>\n",
" <td>1</td>\n",
" <td>3193f</td>\n",
" <td>1</td>\n",
" <td>3193f</td>\n",
" <td>3193f</td>\n",
" <td>3193f</td>\n",
" <td>3193f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:21</th>\n",
" <td>2</td>\n",
" <td>3192.9</td>\n",
" <td>4</td>\n",
" <td>3192.9</td>\n",
" <td>3192.9</td>\n",
" <td>3192.9</td>\n",
" <td>3192.9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:24</th>\n",
" <td>7</td>\n",
" <td>3190.684</td>\n",
" <td>102</td>\n",
" <td>3192.79</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3192.79</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:25</th>\n",
" <td>5</td>\n",
" <td>3190.392</td>\n",
" <td>18</td>\n",
" <td>3190.45</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190.85</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:26</th>\n",
" <td>4</td>\n",
" <td>3191.65</td>\n",
" <td>42</td>\n",
" <td>3191.77</td>\n",
" <td>3190.85</td>\n",
" <td>3190.85</td>\n",
" <td>3192f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:29</th>\n",
" <td>8</td>\n",
" <td>3189.86</td>\n",
" <td>234</td>\n",
" <td>3190.27</td>\n",
" <td>3189.98</td>\n",
" <td>3189.51</td>\n",
" <td>3190.27</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:30</th>\n",
" <td>4</td>\n",
" <td>3188.265</td>\n",
" <td>14</td>\n",
" <td>3188.57</td>\n",
" <td>3188f</td>\n",
" <td>3188f</td>\n",
" <td>3188.57</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:35</th>\n",
" <td>3</td>\n",
" <td>3190.543</td>\n",
" <td>7</td>\n",
" <td>3190.37</td>\n",
" <td>3190.63</td>\n",
" <td>3190.37</td>\n",
" <td>3190.63</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:36</th>\n",
" <td>1</td>\n",
" <td>3190.63</td>\n",
" <td>9</td>\n",
" <td>3190.63</td>\n",
" <td>3190.63</td>\n",
" <td>3190.63</td>\n",
" <td>3190.63</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:39</th>\n",
" <td>3</td>\n",
" <td>3190.81</td>\n",
" <td>5</td>\n",
" <td>3190.71</td>\n",
" <td>3190.34</td>\n",
" <td>3190.34</td>\n",
" <td>3191.38</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:42</th>\n",
" <td>1</td>\n",
" <td>3191.72</td>\n",
" <td>2</td>\n",
" <td>3191.72</td>\n",
" <td>3191.72</td>\n",
" <td>3191.72</td>\n",
" <td>3191.72</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:45</th>\n",
" <td>11</td>\n",
" <td>3193.024</td>\n",
" <td>49</td>\n",
" <td>3190.92</td>\n",
" <td>3193.09</td>\n",
" <td>3190.92</td>\n",
" <td>3194f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:46</th>\n",
" <td>1</td>\n",
" <td>3194.73</td>\n",
" <td>3</td>\n",
" <td>3194.73</td>\n",
" <td>3194.73</td>\n",
" <td>3194.73</td>\n",
" <td>3194.73</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:51</th>\n",
" <td>1</td>\n",
" <td>3194.65</td>\n",
" <td>1</td>\n",
" <td>3194.65</td>\n",
" <td>3194.65</td>\n",
" <td>3194.65</td>\n",
" <td>3194.65</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:52</th>\n",
" <td>1</td>\n",
" <td>3194.43</td>\n",
" <td>1</td>\n",
" <td>3194.43</td>\n",
" <td>3194.43</td>\n",
" <td>3194.43</td>\n",
" <td>3194.43</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:55</th>\n",
" <td>1</td>\n",
" <td>3193.09</td>\n",
" <td>1</td>\n",
" <td>3193.09</td>\n",
" <td>3193.09</td>\n",
" <td>3193.09</td>\n",
" <td>3193.09</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:57</th>\n",
" <td>1</td>\n",
" <td>3191f</td>\n",
" <td>5</td>\n",
" <td>3191f</td>\n",
" <td>3191f</td>\n",
" <td>3191f</td>\n",
" <td>3191f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:58</th>\n",
" <td>14</td>\n",
" <td>3189.894</td>\n",
" <td>143</td>\n",
" <td>3190.45</td>\n",
" <td>3188.5</td>\n",
" <td>3188.5</td>\n",
" <td>3190.56</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:59</th>\n",
" <td>11</td>\n",
" <td>3188.322</td>\n",
" <td>169</td>\n",
" <td>3189.7</td>\n",
" <td>3190f</td>\n",
" <td>3185.62</td>\n",
" <td>3190f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:01</th>\n",
" <td>3</td>\n",
" <td>3190.61</td>\n",
" <td>60</td>\n",
" <td>3190.61</td>\n",
" <td>3190.61</td>\n",
" <td>3190.61</td>\n",
" <td>3190.61</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:02</th>\n",
" <td>1</td>\n",
" <td>3189.03</td>\n",
" <td>2</td>\n",
" <td>3189.03</td>\n",
" <td>3189.03</td>\n",
" <td>3189.03</td>\n",
" <td>3189.03</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:03</th>\n",
" <td>3</td>\n",
" <td>3189.073</td>\n",
" <td>3</td>\n",
" <td>3188.64</td>\n",
" <td>3189.55</td>\n",
" <td>3188.64</td>\n",
" <td>3189.55</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:05</th>\n",
" <td>1</td>\n",
" <td>3192.49</td>\n",
" <td>2</td>\n",
" <td>3192.49</td>\n",
" <td>3192.49</td>\n",
" <td>3192.49</td>\n",
" <td>3192.49</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:12</th>\n",
" <td>1</td>\n",
" <td>3191f</td>\n",
" <td>2</td>\n",
" <td>3191f</td>\n",
" <td>3191f</td>\n",
" <td>3191f</td>\n",
" <td>3191f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:13</th>\n",
" <td>1</td>\n",
" <td>3189.03</td>\n",
" <td>1</td>\n",
" <td>3189.03</td>\n",
" <td>3189.03</td>\n",
" <td>3189.03</td>\n",
" <td>3189.03</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:16</th>\n",
" <td>4</td>\n",
" <td>3189.602</td>\n",
" <td>45</td>\n",
" <td>3190.9</td>\n",
" <td>3189.13</td>\n",
" <td>3189.13</td>\n",
" <td>3190.9</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:17</th>\n",
" <td>7</td>\n",
" <td>3188.86</td>\n",
" <td>99</td>\n",
" <td>3189.03</td>\n",
" <td>3188.64</td>\n",
" <td>3188.64</td>\n",
" <td>3189.03</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:18</th>\n",
" <td>8</td>\n",
" <td>3188.428</td>\n",
" <td>65</td>\n",
" <td>3188.57</td>\n",
" <td>3188.38</td>\n",
" <td>3188.38</td>\n",
" <td>3188.57</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:19</th>\n",
" <td>1</td>\n",
" <td>3188.94</td>\n",
" <td>10</td>\n",
" <td>3188.94</td>\n",
" <td>3188.94</td>\n",
" <td>3188.94</td>\n",
" <td>3188.94</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:20</th>\n",
" <td>2</td>\n",
" <td>3190.005</td>\n",
" <td>2</td>\n",
" <td>3190.01</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190.01</td>\n",
" </tr>\n",
" <tr>\n",
" <th>05:21</th>\n",
" <td>2</td>\n",
" <td>3190f</td>\n",
" <td>6</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" <td>3190f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <th>...</th>\n",
" <th>...</th>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>AMZN</th>\n",
" <th>TRADE</th>\n",
" <th>2021.01.05</th>\n",
" <th>19:59</th>\n",
" <td>24</td>\n",
" <td>3215.034</td>\n",
" <td>387</td>\n",
" <td>3215.02</td>\n",
" <td>3213f</td>\n",
" <td>3213f</td>\n",
" <td>3217.5</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>872 rows × 11 columns</p>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"Ticker EventType date minute| Events Avg_Price Volume First_Price Last_Price Min_Price Max_Price\n",
"----------------------------------| ------------------------------------------------------------------\n",
"AMZN TRADE 2021.01.05 04:00 | 9 3191.7 179 3190.01 3192.37 3190.01 3195 \n",
"AMZN TRADE 2021.01.05 04:01 | 8 3189.274 214 3190.01 3189.99 3187 3190.01 \n",
"AMZN TRADE 2021.01.05 04:02 | 7 3191.156 52 3188.01 3194.59 3188 3194.59 \n",
"AMZN TRADE 2021.01.05 04:03 | 3 3192.72 51 3190 3194.59 3190 3194.59 \n",
"AMZN TRADE 2021.01.05 04:04 | 7 3190.477 201 3191.19 3190 3190 3191.35 \n",
"AMZN TRADE 2021.01.05 04:05 | 3 3190.017 15 3190.05 3190 3190 3190.05 \n",
"AMZN TRADE 2021.01.05 04:06 | 2 3191.305 13 3190.61 3192 3190.61 3192 \n",
"AMZN TRADE 2021.01.05 04:08 | 1 3190.92 16 3190.92 3190.92 3190.92 3190.92 \n",
"AMZN TRADE 2021.01.05 04:09 | 6 3190.802 26 3190.96 3190 3190 3190.97 \n",
"AMZN TRADE 2021.01.05 04:10 | 1 3190 1 3190 3190 3190 3190 \n",
"AMZN TRADE 2021.01.05 04:11 | 1 3190.22 1 3190.22 3190.22 3190.22 3190.22 \n",
"AMZN TRADE 2021.01.05 04:12 | 1 3190.04 1 3190.04 3190.04 3190.04 3190.04 \n",
"AMZN TRADE 2021.01.05 04:13 | 2 3190.445 2 3190.21 3190.68 3190.21 3190.68 \n",
"AMZN TRADE 2021.01.05 04:15 | 4 3190.887 7 3190.78 3190.7 3190.7 3191.07 \n",
"AMZN TRADE 2021.01.05 04:16 | 3 3191.66 4 3191.16 3191.91 3191.16 3191.91 \n",
"AMZN TRADE 2021.01.05 04:17 | 4 3191.972 11 3191.89 3192 3191.89 3192 \n",
"AMZN TRADE 2021.01.05 04:18 | 1 3192 4 3192 3192 3192 3192 \n",
"AMZN TRADE 2021.01.05 04:19 | 3 3192.997 30 3192.99 3193 3192.99 3193 \n",
"AMZN TRADE 2021.01.05 04:20 | 1 3193 1 3193 3193 3193 3193 \n",
"AMZN TRADE 2021.01.05 04:21 | 2 3192.9 4 3192.9 3192.9 3192.9 3192.9 \n",
"AMZN TRADE 2021.01.05 04:24 | 7 3190.684 102 3192.79 3190 3190 3192.79 \n",
"AMZN TRADE 2021.01.05 04:25 | 5 3190.392 18 3190.45 3190 3190 3190.85 \n",
"AMZN TRADE 2021.01.05 04:26 | 4 3191.65 42 3191.77 3190.85 3190.85 3192 \n",
"AMZN TRADE 2021.01.05 04:29 | 8 3189.86 234 3190.27 3189.98 3189.51 3190.27 \n",
"AMZN TRADE 2021.01.05 04:30 | 4 3188.265 14 3188.57 3188 3188 3188.57 \n",
"AMZN TRADE 2021.01.05 04:35 | 3 3190.543 7 3190.37 3190.63 3190.37 3190.63 \n",
"AMZN TRADE 2021.01.05 04:36 | 1 3190.63 9 3190.63 3190.63 3190.63 3190.63 \n",
"AMZN TRADE 2021.01.05 04:39 | 3 3190.81 5 3190.71 3190.34 3190.34 3191.38 \n",
"AMZN TRADE 2021.01.05 04:42 | 1 3191.72 2 3191.72 3191.72 3191.72 3191.72 \n",
"AMZN TRADE 2021.01.05 04:45 | 11 3193.024 49 3190.92 3193.09 3190.92 3194 \n",
"AMZN TRADE 2021.01.05 04:46 | 1 3194.73 3 3194.73 3194.73 3194.73 3194.73 \n",
"AMZN TRADE 2021.01.05 04:51 | 1 3194.65 1 3194.65 3194.65 3194.65 3194.65 \n",
"AMZN TRADE 2021.01.05 04:52 | 1 3194.43 1 3194.43 3194.43 3194.43 3194.43 \n",
"AMZN TRADE 2021.01.05 04:55 | 1 3193.09 1 3193.09 3193.09 3193.09 3193.09 \n",
"AMZN TRADE 2021.01.05 04:57 | 1 3191 5 3191 3191 3191 3191 \n",
"AMZN TRADE 2021.01.05 04:58 | 14 3189.894 143 3190.45 3188.5 3188.5 3190.56 \n",
"AMZN TRADE 2021.01.05 04:59 | 11 3188.322 169 3189.7 3190 3185.62 3190 \n",
"AMZN TRADE 2021.01.05 05:01 | 3 3190.61 60 3190.61 3190.61 3190.61 3190.61 \n",
"AMZN TRADE 2021.01.05 05:02 | 1 3189.03 2 3189.03 3189.03 3189.03 3189.03 \n",
"AMZN TRADE 2021.01.05 05:03 | 3 3189.073 3 3188.64 3189.55 3188.64 3189.55 \n",
"AMZN TRADE 2021.01.05 05:05 | 1 3192.49 2 3192.49 3192.49 3192.49 3192.49 \n",
"AMZN TRADE 2021.01.05 05:12 | 1 3191 2 3191 3191 3191 3191 \n",
"AMZN TRADE 2021.01.05 05:13 | 1 3189.03 1 3189.03 3189.03 3189.03 3189.03 \n",
"AMZN TRADE 2021.01.05 05:16 | 4 3189.602 45 3190.9 3189.13 3189.13 3190.9 \n",
"AMZN TRADE 2021.01.05 05:17 | 7 3188.86 99 3189.03 3188.64 3188.64 3189.03 \n",
"..\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Rows In Date: 6,174,244\n"
]
}
],
"source": [
"# Aggregate minute bars for a day, all tickers and event types\n",
"q=\"\"\"select \n",
" Events: count Price, \n",
" Avg_Price: avg Price, \n",
" Volume: sum Quantity, \n",
" First_Price: first Price, Last_Price: last Price,\n",
" Min_Price: min Price, Max_Price: max Price\n",
" by Ticker, EventType, date, 1 xbar Timestamp.minute \n",
" from taq \n",
" where date=max date, Ticker=`AMZN, EventType=`TRADE\"\"\"\n",
"\n",
"display( hdb(q) )\n",
"\n",
"# get row count for max date\n",
"c=hdb(\"count select i from taq where date = max date\").py()\n",
"print( f\"Rows In Date: {c:,}\" )"
]
},
{
"cell_type": "markdown",
"id": "1e304ed4-9f12-4640-a9cc-9bd813899c2b",
"metadata": {},
"source": [
"# Rows Per Ticker"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "52c821d6-d981-4c84-9bba-0a91dd4ece87",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>rows</th>\n",
" </tr>\n",
" <tr>\n",
" <th>Ticker</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>AMZN</th>\n",
" <td>1685421</td>\n",
" </tr>\n",
" <tr>\n",
" <th>FB</th>\n",
" <td>1720887</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GOOG</th>\n",
" <td>1550965</td>\n",
" </tr>\n",
" <tr>\n",
" <th>NFLX</th>\n",
" <td>1216971</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"Ticker| rows \n",
"------| -------\n",
"AMZN | 1685421\n",
"FB | 1720887\n",
"GOOG | 1550965\n",
"NFLX | 1216971\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Result Rows: 4\n"
]
}
],
"source": [
"# assign results to an on-cluster variable\n",
"hdb(f\"res:select rows:count i by Ticker from taq where date = max date\")\n",
"\n",
"# sample results to a local variable\n",
"res = hdb('select [100] from res')\n",
"\n",
"# display local (sampled) results\n",
"display(res)\n",
"\n",
"c=hdb(f\"count res\").py()\n",
"print (f'Result Rows: {c:,}')"
]
},
{
"cell_type": "markdown",
"id": "f2e0ea59-e68d-4366-b704-ecb5ae4d86e9",
"metadata": {},
"source": [
"# Calculate 1 Minute Bar High, Low, Open, Close (HLOC)\n",
"For one date"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "079e89db-bb5c-401c-8ec1-e422e03f8754",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Rows In Date: 6,174,244\n",
"Result Rows: 17,211\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Events</th>\n",
" <th>Avg_Price</th>\n",
" <th>Volume</th>\n",
" <th>Max_Price</th>\n",
" <th>Min_Price</th>\n",
" <th>First_Price</th>\n",
" <th>Last_Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>EventType</th>\n",
" <th>minute</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"49\" valign=\"top\">2021.01.05</th>\n",
" <th rowspan=\"49\" valign=\"top\">AMZN</th>\n",
" <th rowspan=\"49\" valign=\"top\">QUOTE ASK</th>\n",
" <th>04:00</th>\n",
" <td>13</td>\n",
" <td>2708.931</td>\n",
" <td>1300</td>\n",
" <td>3202f</td>\n",
" <td>0f</td>\n",
" <td>0f</td>\n",
" <td>3201f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:01</th>\n",
" <td>21</td>\n",
" <td>2895.349</td>\n",
" <td>2700</td>\n",
" <td>3200.29</td>\n",
" <td>0f</td>\n",
" <td>3200.29</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:02</th>\n",
" <td>28</td>\n",
" <td>1601.335</td>\n",
" <td>1600</td>\n",
" <td>3221.9</td>\n",
" <td>0f</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:03</th>\n",
" <td>22</td>\n",
" <td>3207.358</td>\n",
" <td>2200</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:04</th>\n",
" <td>43</td>\n",
" <td>3203.765</td>\n",
" <td>4300</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:05</th>\n",
" <td>37</td>\n",
" <td>3200.182</td>\n",
" <td>3800</td>\n",
" <td>3210.87</td>\n",
" <td>3194.85</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:06</th>\n",
" <td>40</td>\n",
" <td>3200.05</td>\n",
" <td>4200</td>\n",
" <td>3201f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:07</th>\n",
" <td>32</td>\n",
" <td>3200.031</td>\n",
" <td>3300</td>\n",
" <td>3201f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:08</th>\n",
" <td>34</td>\n",
" <td>3200f</td>\n",
" <td>3400</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:09</th>\n",
" <td>34</td>\n",
" <td>3200.211</td>\n",
" <td>3400</td>\n",
" <td>3207.16</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:10</th>\n",
" <td>34</td>\n",
" <td>3200.211</td>\n",
" <td>3400</td>\n",
" <td>3207.16</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:11</th>\n",
" <td>37</td>\n",
" <td>3200.078</td>\n",
" <td>4200</td>\n",
" <td>3201f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:12</th>\n",
" <td>26</td>\n",
" <td>3200.036</td>\n",
" <td>2800</td>\n",
" <td>3200.47</td>\n",
" <td>3200f</td>\n",
" <td>3200.47</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:13</th>\n",
" <td>39</td>\n",
" <td>3200.395</td>\n",
" <td>7100</td>\n",
" <td>3200.59</td>\n",
" <td>3200f</td>\n",
" <td>3200.47</td>\n",
" <td>3200.59</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:14</th>\n",
" <td>33</td>\n",
" <td>3199.162</td>\n",
" <td>6500</td>\n",
" <td>3200.65</td>\n",
" <td>3198f</td>\n",
" <td>3200.59</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:15</th>\n",
" <td>32</td>\n",
" <td>3198.466</td>\n",
" <td>6000</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3200f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:16</th>\n",
" <td>38</td>\n",
" <td>3198.198</td>\n",
" <td>7200</td>\n",
" <td>3199.88</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:17</th>\n",
" <td>33</td>\n",
" <td>3198.057</td>\n",
" <td>6500</td>\n",
" <td>3199.88</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:18</th>\n",
" <td>15</td>\n",
" <td>3198f</td>\n",
" <td>3000</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:19</th>\n",
" <td>25</td>\n",
" <td>3198.366</td>\n",
" <td>4900</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:20</th>\n",
" <td>30</td>\n",
" <td>3198.305</td>\n",
" <td>5900</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:21</th>\n",
" <td>24</td>\n",
" <td>3198f</td>\n",
" <td>4800</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:22</th>\n",
" <td>41</td>\n",
" <td>3201.546</td>\n",
" <td>5400</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:23</th>\n",
" <td>31</td>\n",
" <td>3198f</td>\n",
" <td>3100</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:24</th>\n",
" <td>37</td>\n",
" <td>3198.99</td>\n",
" <td>3700</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:25</th>\n",
" <td>16</td>\n",
" <td>3198f</td>\n",
" <td>1600</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:26</th>\n",
" <td>22</td>\n",
" <td>3198f</td>\n",
" <td>2200</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:27</th>\n",
" <td>40</td>\n",
" <td>3198.458</td>\n",
" <td>4000</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:28</th>\n",
" <td>29</td>\n",
" <td>3198f</td>\n",
" <td>2900</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:29</th>\n",
" <td>42</td>\n",
" <td>3199.79</td>\n",
" <td>4200</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3207.16</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:30</th>\n",
" <td>43</td>\n",
" <td>3199.751</td>\n",
" <td>4300</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:31</th>\n",
" <td>46</td>\n",
" <td>3199.792</td>\n",
" <td>4600</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:32</th>\n",
" <td>55</td>\n",
" <td>3200.165</td>\n",
" <td>5500</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:33</th>\n",
" <td>51</td>\n",
" <td>3199.976</td>\n",
" <td>5100</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:34</th>\n",
" <td>53</td>\n",
" <td>3200.247</td>\n",
" <td>5300</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:35</th>\n",
" <td>25</td>\n",
" <td>3198f</td>\n",
" <td>2500</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:36</th>\n",
" <td>21</td>\n",
" <td>3198.872</td>\n",
" <td>2100</td>\n",
" <td>3207.16</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:37</th>\n",
" <td>28</td>\n",
" <td>3198.852</td>\n",
" <td>2800</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:38</th>\n",
" <td>30</td>\n",
" <td>3201.18</td>\n",
" <td>3000</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3221.85</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:39</th>\n",
" <td>56</td>\n",
" <td>3205.391</td>\n",
" <td>5600</td>\n",
" <td>3221.85</td>\n",
" <td>3196.6</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:40</th>\n",
" <td>50</td>\n",
" <td>3209.448</td>\n",
" <td>5000</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3221.85</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:41</th>\n",
" <td>17</td>\n",
" <td>3221.85</td>\n",
" <td>1700</td>\n",
" <td>3221.85</td>\n",
" <td>3221.85</td>\n",
" <td>3221.85</td>\n",
" <td>3221.85</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:42</th>\n",
" <td>18</td>\n",
" <td>3220.525</td>\n",
" <td>1800</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:43</th>\n",
" <td>18</td>\n",
" <td>3219.2</td>\n",
" <td>1800</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3221.85</td>\n",
" <td>3221.85</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:44</th>\n",
" <td>42</td>\n",
" <td>3209.357</td>\n",
" <td>4200</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:45</th>\n",
" <td>37</td>\n",
" <td>3199.289</td>\n",
" <td>3700</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:46</th>\n",
" <td>44</td>\n",
" <td>3199.084</td>\n",
" <td>4400</td>\n",
" <td>3221.85</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:47</th>\n",
" <td>27</td>\n",
" <td>3198f</td>\n",
" <td>2700</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:48</th>\n",
" <td>36</td>\n",
" <td>3198f</td>\n",
" <td>3600</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" <td>3198f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <th>...</th>\n",
" <th>...</th>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2021.01.05</th>\n",
" <th>AMZN</th>\n",
" <th>QUOTE ASK</th>\n",
" <th>05:39</th>\n",
" <td>11</td>\n",
" <td>3190.89</td>\n",
" <td>1100</td>\n",
" <td>3190.89</td>\n",
" <td>3190.89</td>\n",
" <td>3190.89</td>\n",
" <td>3190.89</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>100 rows × 11 columns</p>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"date Ticker EventType minute| Events Avg_Price Volume Max_Price Min_Price First_Price Last_Price\n",
"----------------------------------| ------------------------------------------------------------------\n",
"2021.01.05 AMZN QUOTE ASK 04:00 | 13 2708.931 1300 3202 0 0 3201 \n",
"2021.01.05 AMZN QUOTE ASK 04:01 | 21 2895.349 2700 3200.29 0 3200.29 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:02 | 28 1601.335 1600 3221.9 0 3200 3213.49 \n",
"2021.01.05 AMZN QUOTE ASK 04:03 | 22 3207.358 2200 3213.49 3200 3213.49 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:04 | 43 3203.765 4300 3213.49 3200 3213.49 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:05 | 37 3200.182 3800 3210.87 3194.85 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:06 | 40 3200.05 4200 3201 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:07 | 32 3200.031 3300 3201 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:08 | 34 3200 3400 3200 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:09 | 34 3200.211 3400 3207.16 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:10 | 34 3200.211 3400 3207.16 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:11 | 37 3200.078 4200 3201 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:12 | 26 3200.036 2800 3200.47 3200 3200.47 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:13 | 39 3200.395 7100 3200.59 3200 3200.47 3200.59 \n",
"2021.01.05 AMZN QUOTE ASK 04:14 | 33 3199.162 6500 3200.65 3198 3200.59 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:15 | 32 3198.466 6000 3207.16 3198 3200 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:16 | 38 3198.198 7200 3199.88 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:17 | 33 3198.057 6500 3199.88 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:18 | 15 3198 3000 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:19 | 25 3198.366 4900 3207.16 3198 3207.16 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:20 | 30 3198.305 5900 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:21 | 24 3198 4800 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:22 | 41 3201.546 5400 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:23 | 31 3198 3100 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:24 | 37 3198.99 3700 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:25 | 16 3198 1600 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:26 | 22 3198 2200 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:27 | 40 3198.458 4000 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:28 | 29 3198 2900 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:29 | 42 3199.79 4200 3207.16 3198 3198 3207.16 \n",
"2021.01.05 AMZN QUOTE ASK 04:30 | 43 3199.751 4300 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:31 | 46 3199.792 4600 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:32 | 55 3200.165 5500 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:33 | 51 3199.976 5100 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:34 | 53 3200.247 5300 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:35 | 25 3198 2500 3198 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:36 | 21 3198.872 2100 3207.16 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:37 | 28 3198.852 2800 3221.85 3198 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:38 | 30 3201.18 3000 3221.85 3198 3198 3221.85 \n",
"2021.01.05 AMZN QUOTE ASK 04:39 | 56 3205.391 5600 3221.85 3196.6 3198 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:40 | 50 3209.448 5000 3221.85 3198 3198 3221.85 \n",
"2021.01.05 AMZN QUOTE ASK 04:41 | 17 3221.85 1700 3221.85 3221.85 3221.85 3221.85 \n",
"2021.01.05 AMZN QUOTE ASK 04:42 | 18 3220.525 1800 3221.85 3198 3221.85 3198 \n",
"2021.01.05 AMZN QUOTE ASK 04:43 | 18 3219.2 1800 3221.85 3198 3221.85 3221.85 \n",
"2021.01.05 AMZN QUOTE ASK 04:44 | 42 3209.357 4200 3221.85 3198 3198 3198 \n",
"..\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# get row count for max date\n",
"c=hdb(\"count select i from taq where date = max date\").py()\n",
"print( f\"Rows In Date: {c:,}\" )\n",
"\n",
"# Aggregate minute bars for a day, all tickers and event types\n",
"q=\"\"\"res:select \n",
" Events: count Price, \n",
" Avg_Price: avg Price, \n",
" Volume: sum Quantity, \n",
" Max_Price: max Price, Min_Price: min Price, \n",
" First_Price: first Price, Last_Price: last Price\n",
" by date, Ticker, EventType, 1 xbar Timestamp.minute \n",
" from taq \n",
" where date=max date\"\"\"\n",
"\n",
"# execute query, sets results into a variable on-cluster\n",
"hdb(q)\n",
"\n",
"# result rows\n",
"c=hdb(f\"count res\").py()\n",
"print (f'Result Rows: {c:,}')\n",
"\n",
"# sample results to a local variable\n",
"res = hdb('select [100] from res')\n",
"\n",
"# display local (sampled) results\n",
"display(res)"
]
},
{
"cell_type": "markdown",
"id": "bb11b411-f627-4b95-9eac-e7aecf1da394",
"metadata": {},
"source": [
"# Calculate 1 Minute Bar High, Low, Open, Close (HLOC)\n",
"Over a list of dates"
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "babafdec-f3f0-40fd-a8df-6b4344189d16",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Date 2021-01-04\n",
"Rows: 8,970,726\n",
"Result Rows: 17,616\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Events</th>\n",
" <th>Avg_Price</th>\n",
" <th>Volume</th>\n",
" <th>Max_Price</th>\n",
" <th>Min_Price</th>\n",
" <th>First_Price</th>\n",
" <th>Last_Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>EventType</th>\n",
" <th>minute</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"10\" valign=\"top\">2021.01.04</th>\n",
" <th rowspan=\"10\" valign=\"top\">AMZN</th>\n",
" <th rowspan=\"10\" valign=\"top\">QUOTE ASK</th>\n",
" <th>04:00</th>\n",
" <td>23</td>\n",
" <td>2573.147</td>\n",
" <td>2000</td>\n",
" <td>3288.36</td>\n",
" <td>0f</td>\n",
" <td>0f</td>\n",
" <td>3288.36</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:01</th>\n",
" <td>23</td>\n",
" <td>1858.638</td>\n",
" <td>1300</td>\n",
" <td>3288.36</td>\n",
" <td>0f</td>\n",
" <td>3288.36</td>\n",
" <td>3288.36</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:02</th>\n",
" <td>27</td>\n",
" <td>2922.955</td>\n",
" <td>3100</td>\n",
" <td>3288.36</td>\n",
" <td>0f</td>\n",
" <td>3288.36</td>\n",
" <td>3288.31</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:03</th>\n",
" <td>18</td>\n",
" <td>3288.31</td>\n",
" <td>3600</td>\n",
" <td>3288.31</td>\n",
" <td>3288.31</td>\n",
" <td>3288.31</td>\n",
" <td>3288.31</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:04</th>\n",
" <td>34</td>\n",
" <td>3308.243</td>\n",
" <td>4600</td>\n",
" <td>3460f</td>\n",
" <td>3288.31</td>\n",
" <td>3288.31</td>\n",
" <td>3300f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:05</th>\n",
" <td>35</td>\n",
" <td>3343.765</td>\n",
" <td>3500</td>\n",
" <td>3380f</td>\n",
" <td>3291.69</td>\n",
" <td>3380f</td>\n",
" <td>3350f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:06</th>\n",
" <td>69</td>\n",
" <td>3323.91</td>\n",
" <td>6900</td>\n",
" <td>3360f</td>\n",
" <td>3291.69</td>\n",
" <td>3291.69</td>\n",
" <td>3291.69</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:07</th>\n",
" <td>53</td>\n",
" <td>3295.514</td>\n",
" <td>5300</td>\n",
" <td>3300f</td>\n",
" <td>3290.5</td>\n",
" <td>3300f</td>\n",
" <td>3300f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:08</th>\n",
" <td>57</td>\n",
" <td>3295.387</td>\n",
" <td>5700</td>\n",
" <td>3300f</td>\n",
" <td>3290.1</td>\n",
" <td>3290.5</td>\n",
" <td>3295.47</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:09</th>\n",
" <td>52</td>\n",
" <td>3293.987</td>\n",
" <td>5200</td>\n",
" <td>3300f</td>\n",
" <td>3291.04</td>\n",
" <td>3291.04</td>\n",
" <td>3291.04</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"date Ticker EventType minute| Events Avg_Price Volume Max_Price Min_Price First_Price Last_Price\n",
"----------------------------------| ------------------------------------------------------------------\n",
"2021.01.04 AMZN QUOTE ASK 04:00 | 23 2573.147 2000 3288.36 0 0 3288.36 \n",
"2021.01.04 AMZN QUOTE ASK 04:01 | 23 1858.638 1300 3288.36 0 3288.36 3288.36 \n",
"2021.01.04 AMZN QUOTE ASK 04:02 | 27 2922.955 3100 3288.36 0 3288.36 3288.31 \n",
"2021.01.04 AMZN QUOTE ASK 04:03 | 18 3288.31 3600 3288.31 3288.31 3288.31 3288.31 \n",
"2021.01.04 AMZN QUOTE ASK 04:04 | 34 3308.243 4600 3460 3288.31 3288.31 3300 \n",
"2021.01.04 AMZN QUOTE ASK 04:05 | 35 3343.765 3500 3380 3291.69 3380 3350 \n",
"2021.01.04 AMZN QUOTE ASK 04:06 | 69 3323.91 6900 3360 3291.69 3291.69 3291.69 \n",
"2021.01.04 AMZN QUOTE ASK 04:07 | 53 3295.514 5300 3300 3290.5 3300 3300 \n",
"2021.01.04 AMZN QUOTE ASK 04:08 | 57 3295.387 5700 3300 3290.1 3290.5 3295.47 \n",
"2021.01.04 AMZN QUOTE ASK 04:09 | 52 3293.987 5200 3300 3291.04 3291.04 3291.04 \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"Date 2021-01-05\n",
"Rows: 6,174,244\n",
"Result Rows: 17,211\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th>Events</th>\n",
" <th>Avg_Price</th>\n",
" <th>Volume</th>\n",
" <th>Max_Price</th>\n",
" <th>Min_Price</th>\n",
" <th>First_Price</th>\n",
" <th>Last_Price</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>EventType</th>\n",
" <th>minute</th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th rowspan=\"10\" valign=\"top\">2021.01.05</th>\n",
" <th rowspan=\"10\" valign=\"top\">AMZN</th>\n",
" <th rowspan=\"10\" valign=\"top\">QUOTE ASK</th>\n",
" <th>04:00</th>\n",
" <td>13</td>\n",
" <td>2708.931</td>\n",
" <td>1300</td>\n",
" <td>3202f</td>\n",
" <td>0f</td>\n",
" <td>0f</td>\n",
" <td>3201f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:01</th>\n",
" <td>21</td>\n",
" <td>2895.349</td>\n",
" <td>2700</td>\n",
" <td>3200.29</td>\n",
" <td>0f</td>\n",
" <td>3200.29</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:02</th>\n",
" <td>28</td>\n",
" <td>1601.335</td>\n",
" <td>1600</td>\n",
" <td>3221.9</td>\n",
" <td>0f</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:03</th>\n",
" <td>22</td>\n",
" <td>3207.358</td>\n",
" <td>2200</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:04</th>\n",
" <td>43</td>\n",
" <td>3203.765</td>\n",
" <td>4300</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" <td>3213.49</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:05</th>\n",
" <td>37</td>\n",
" <td>3200.182</td>\n",
" <td>3800</td>\n",
" <td>3210.87</td>\n",
" <td>3194.85</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:06</th>\n",
" <td>40</td>\n",
" <td>3200.05</td>\n",
" <td>4200</td>\n",
" <td>3201f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:07</th>\n",
" <td>32</td>\n",
" <td>3200.031</td>\n",
" <td>3300</td>\n",
" <td>3201f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:08</th>\n",
" <td>34</td>\n",
" <td>3200f</td>\n",
" <td>3400</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" <tr>\n",
" <th>04:09</th>\n",
" <td>34</td>\n",
" <td>3200.211</td>\n",
" <td>3400</td>\n",
" <td>3207.16</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" <td>3200f</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"date Ticker EventType minute| Events Avg_Price Volume Max_Price Min_Price First_Price Last_Price\n",
"----------------------------------| ------------------------------------------------------------------\n",
"2021.01.05 AMZN QUOTE ASK 04:00 | 13 2708.931 1300 3202 0 0 3201 \n",
"2021.01.05 AMZN QUOTE ASK 04:01 | 21 2895.349 2700 3200.29 0 3200.29 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:02 | 28 1601.335 1600 3221.9 0 3200 3213.49 \n",
"2021.01.05 AMZN QUOTE ASK 04:03 | 22 3207.358 2200 3213.49 3200 3213.49 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:04 | 43 3203.765 4300 3213.49 3200 3213.49 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:05 | 37 3200.182 3800 3210.87 3194.85 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:06 | 40 3200.05 4200 3201 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:07 | 32 3200.031 3300 3201 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:08 | 34 3200 3400 3200 3200 3200 3200 \n",
"2021.01.05 AMZN QUOTE ASK 04:09 | 34 3200.211 3400 3207.16 3200 3200 3200 \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n"
]
}
],
"source": [
"# all dates in table\n",
"res = hdb(f\"select distinct date from taq\").pd()\n",
"\n",
"# list of dates\n",
"dlist = res['date'][:2] \n",
"\n",
"for d in dlist:\n",
" hdb(\".Q.gc[]\")\n",
"\n",
" d = d.date()\n",
" hdb['dt']=d\n",
"\n",
" # get row count for date\n",
" c=hdb(\"count select i from taq where date = dt\").py()\n",
"\n",
" print( f\"Date {d}\" )\n",
" print( f\"Rows: {c:,}\" )\n",
"\n",
" # calculate and store results in res\n",
" q=f\"\"\"res:select \n",
" Events: count Price, \n",
" Avg_Price: avg Price, \n",
" Volume: sum Quantity, \n",
" Max_Price: max Price, \n",
" Min_Price: min Price, \n",
" First_Price: first Price, \n",
" Last_Price: last Price\n",
" by date, Ticker, EventType, 1 xbar Timestamp.minute \n",
" from taq \n",
" where date=dt\"\"\"\n",
"\n",
" # execute query, sets results into a variable on-cluster\n",
" hdb(q)\n",
"\n",
" # result rows\n",
" c=hdb('count res').py()\n",
" print( f\"Result Rows: {c:,}\" )\n",
"\n",
" # sample results to a local variable\n",
" res = hdb('select [10] from res')\n",
"\n",
" # display local (sampled) results\n",
" display(res)\n",
"\n",
" print()"
]
},
{
"cell_type": "markdown",
"id": "869fe2b5-58b7-4819-ac04-2720b0409978",
"metadata": {},
"source": [
"# SQL Query\n",
"Use PyKX's built in sql feature to use ANSI sql to query tabls.\n",
"\n",
"## Reference\n",
"[KX SQL](https://code.kx.com/insights/1.11/core/sql.html)"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "bfa35052-db9e-45ec-812e-3ac7e7bce34e",
"metadata": {
"tags": []
},
"outputs": [
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>date</th>\n",
" <th>Ticker</th>\n",
" <th>Timestamp</th>\n",
" <th>EventType</th>\n",
" <th>Price</th>\n",
" <th>Quantity</th>\n",
" <th>Exchange</th>\n",
" <th>Conditions</th>\n",
" <th>FileName</th>\n",
" <th>FileExtension</th>\n",
" </tr>\n",
" <tr>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>2021.01.04</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.021805862</td>\n",
" <td>TRADE</td>\n",
" <td>3271.69</td>\n",
" <td>32</td>\n",
" <td>ARCA</td>\n",
" <td>80000401</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2021.01.04</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.023217706</td>\n",
" <td>QUOTE BID</td>\n",
" <td>2861f</td>\n",
" <td>200</td>\n",
" <td>ARCA</td>\n",
" <td>00000001</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2021.01.04</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.023217706</td>\n",
" <td>QUOTE ASK</td>\n",
" <td>0f</td>\n",
" <td>0</td>\n",
" <td>ARCA</td>\n",
" <td>00000001</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2021.01.04</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.023217706</td>\n",
" <td>QUOTE BID NB</td>\n",
" <td>2861f</td>\n",
" <td>200</td>\n",
" <td>ARCA</td>\n",
" <td>00000001</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2021.01.04</td>\n",
" <td>AMZN</td>\n",
" <td>0D04:00:00.023471043</td>\n",
" <td>QUOTE BID NB</td>\n",
" <td>2861f</td>\n",
" <td>200</td>\n",
" <td>ARCA</td>\n",
" <td>00000001</td>\n",
" <td>AMZN</td>\n",
" <td>gz</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Table(pykx.q('\n",
"date Ticker Timestamp EventType Price Quantity Exchange Conditions FileName FileExtension\n",
"---------------------------------------------------------------------------------------------------------------\n",
"2021.01.04 AMZN 0D04:00:00.021805862 TRADE 3271.69 32 ARCA 80000401 AMZN gz \n",
"2021.01.04 AMZN 0D04:00:00.023217706 QUOTE BID 2861 200 ARCA 00000001 AMZN gz \n",
"2021.01.04 AMZN 0D04:00:00.023217706 QUOTE ASK 0 0 ARCA 00000001 AMZN gz \n",
"2021.01.04 AMZN 0D04:00:00.023217706 QUOTE BID NB 2861 200 ARCA 00000001 AMZN gz \n",
"2021.01.04 AMZN 0D04:00:00.023471043 QUOTE BID NB 2861 200 ARCA 00000001 AMZN gz \n",
"'))"
]
},
"execution_count": 12,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"hdb.sql(\"select * from taq limit 5\")"
]
},
{
"cell_type": "markdown",
"id": "67a988e9-7569-4585-9b36-b2d240571bfb",
"metadata": {},
"source": [
"# Q Queries\n",
"Using connection information from the service, and the q magic cell feature supplied by PyKX, execute q code directly on the remote cluster."
]
},
{
"cell_type": "code",
"execution_count": 13,
"id": "4e26a6dc-a2a5-4874-a268-e623ba941a12",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"# get the connection string to the cluster\n",
"conn_str = get_kx_connection_string(client, \n",
" environmentId=ENV_ID, clusterName=CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"\n",
"# parse the connection string to components\n",
"host, port, username, password = parse_connection_string(conn_str)\n"
]
},
{
"cell_type": "code",
"execution_count": 14,
"id": "d4fc964c-7950-4a1c-8561-e00976f3f008",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ set console height and width\n",
"\\c 25 5000"
]
},
{
"cell_type": "code",
"execution_count": 15,
"id": "56ade0ce-f4fc-4ada-89e1-8fb2e15e0c74",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"used| 4752400\n",
"heap| 536870912\n",
"peak| 2751463424\n",
"wmax| 0\n",
"mmap| 0\n",
"mphy| 33172058112\n",
"syms| 4113\n",
"symw| 210729\n",
"`res`taq\n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ workspace information\n",
".Q.w[]\n",
"\n",
"/ tables on the cluster\n",
"tables[]"
]
},
{
"cell_type": "markdown",
"id": "616179d4-8c88-4b1b-a55d-86f205cc9e5e",
"metadata": {},
"source": [
"# Show Schema of Table"
]
},
{
"cell_type": "code",
"execution_count": 16,
"id": "ad71e4bb-a013-412e-a10b-e8b2ee0e7e28",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"c | t f a\n",
"-------------| -----\n",
"date | d \n",
"Ticker | s p\n",
"Timestamp | n \n",
"EventType | s \n",
"Price | f \n",
"Quantity | j \n",
"Exchange | s \n",
"Conditions | s \n",
"FileName | s \n",
"FileExtension| s \n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ schema of a table\n",
"meta taq"
]
},
{
"cell_type": "markdown",
"id": "a8d5dcd7-eef2-4b41-bd00-143aeb83853e",
"metadata": {},
"source": [
"# Rows per Date "
]
},
{
"cell_type": "code",
"execution_count": 17,
"id": "ab69aaa2-2b50-4cfd-af50-2c91766566f9",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"date | counts \n",
"----------| -------\n",
"2021.01.04| 8970726\n",
"2021.01.05| 6174244\n",
"Rows: 15,144,970\n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ count rows by date\n",
"select counts:count i by date from taq\n",
"\n",
"\"Rows: \", {reverse \",\" sv 0N 3#reverse string x} count taq"
]
},
{
"cell_type": "markdown",
"id": "88d375c3-0574-48dd-ac3b-06d36433d3f7",
"metadata": {},
"source": [
"# Aggregate Data"
]
},
{
"cell_type": "code",
"execution_count": 18,
"id": "f778de75-06bf-48e7-877a-f766dd05acb7",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"date Ticker| avg_num t_cnt vol avg_sq_price\n",
"-----------------| ---------------------------------------\n",
"2021.01.05 AMZN | 3179.655 1685421 192327257 1.011021e+07\n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ define a function\n",
"sq:{x*x}\n",
"\n",
"/ use function in a query\n",
"select avg_num:avg Price, t_cnt:count Price, vol:sum Quantity, avg_sq_price: sq[avg Price] \n",
" by date,Ticker \n",
" from taq\n",
" where date=max date, Ticker in (`AAPL`AMZN`JPM`GS) "
]
},
{
"cell_type": "code",
"execution_count": 19,
"id": "9cea179b-502e-480b-b232-5627e1e48637",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Ticker EventType date minute| Price Volume First_Price Last_Price Min_Price Max_Price Events\n",
"----------------------------------| -------------------------------------------------------------------\n",
"AMZN TRADE 2021.01.05 04:00 | 3190.855 20.5 3190.01 3190 3187 3195 34 \n",
"AMZN TRADE 2021.01.05 04:05 | 3190.699 5.833333 3190.05 3190 3190 3192 12 \n",
"AMZN TRADE 2021.01.05 04:10 | 3190.23 1 3190 3190.68 3190 3190.68 5 \n",
"AMZN TRADE 2021.01.05 04:15 | 3191.827 3.733333 3190.78 3193 3190.7 3193 15 \n",
"AMZN TRADE 2021.01.05 04:20 | 3191.359 10.7 3193 3190 3190 3193 10 \n",
"AMZN TRADE 2021.01.05 04:25 | 3190.438 17.29412 3190.45 3189.98 3189.51 3192 17 \n",
"AMZN TRADE 2021.01.05 04:30 | 3188.265 3.5 3188.57 3188 3188 3188.57 4 \n",
"AMZN TRADE 2021.01.05 04:35 | 3190.67 3 3190.37 3190.34 3190.34 3191.38 7 \n",
"AMZN TRADE 2021.01.05 04:40 | 3191.72 2 3191.72 3191.72 3191.72 3191.72 1 \n",
"AMZN TRADE 2021.01.05 04:45 | 3193.166 4.333333 3190.92 3194.73 3190.92 3194.73 12 \n",
"AMZN TRADE 2021.01.05 04:50 | 3194.54 1 3194.65 3194.43 3194.43 3194.65 2 \n",
"AMZN TRADE 2021.01.05 04:55 | 3189.413 11.77778 3193.09 3190 3185.62 3193.09 27 \n",
"AMZN TRADE 2021.01.05 05:00 | 3189.726 9.285714 3190.61 3189.55 3188.64 3190.61 7 \n",
"AMZN TRADE 2021.01.05 05:05 | 3192.49 2 3192.49 3192.49 3192.49 3192.49 1 \n",
"AMZN TRADE 2021.01.05 05:10 | 3190.015 1.5 3191 3189.03 3189.03 3191 2 \n",
"AMZN TRADE 2021.01.05 05:15 | 3188.839 10.95 3190.9 3188.94 3188.38 3190.9 20 \n",
"AMZN TRADE 2021.01.05 05:20 | 3190.529 5.333333 3190.01 3190.44 3190 3192 9 \n",
"AMZN TRADE 2021.01.05 05:25 | 3190.08 3.571429 3191.79 3190 3188.5 3192 7 \n",
"AMZN TRADE 2021.01.05 05:30 | 3190.406 2.8 3190 3190 3190 3190.79 5 \n",
"AMZN TRADE 2021.01.05 05:35 | 3189.85 1 3190 3189.55 3189.55 3190 3 \n",
"AMZN TRADE 2021.01.05 05:40 | 3190.299 8.4 3190.89 3189.5 3189.5 3190.89 10 \n",
"AMZN TRADE 2021.01.05 05:45 | 3189.439 4.909091 3189.89 3188.8 3188.8 3190.6 11 \n",
"AMZN TRADE 2021.01.05 05:50 | 3188.878 5 3188.76 3189 3188.75 3189 4 \n",
"AMZN TRADE 2021.01.05 05:55 | 3189 3 3189 3189 3189 3189 1 \n",
"AMZN TRADE 2021.01.05 06:00 | 3189.819 21.53333 3188.51 3190.9 3188.34 3190.9 15 \n",
"AMZN TRADE 2021.01.05 06:05 | 3191.884 7.125 3190.9 3192.41 3190.9 3192.41 8 \n",
"AMZN TRADE 2021.01.05 06:10 | 3191.81 5.75 3192 3191.65 3191.59 3192 4 \n",
"AMZN TRADE 2021.01.05 06:15 | 3191.802 5.444444 3192 3192 3191.08 3192.49 9 \n",
"AMZN TRADE 2021.01.05 06:20 | 3191.828 7.166667 3190.92 3192.03 3190.92 3192.03 6 \n",
"AMZN TRADE 2021.01.05 06:25 | 3192.897 2.666667 3192.16 3194.11 3192.16 3194.11 3 \n",
"AMZN TRADE 2021.01.05 06:30 | 3192.204 4.9 3192.41 3193.98 3190.32 3193.98 20 \n",
"AMZN TRADE 2021.01.05 06:35 | 3191.166 7.714286 3192 3189.4 3189.4 3192 7 \n",
"AMZN TRADE 2021.01.05 06:40 | 3188.413 5.947368 3190.49 3188 3185.95 3190.49 19 \n",
"AMZN TRADE 2021.01.05 06:45 | 3187.034 13.58824 3188.03 3188 3185.1 3188.49 17 \n",
"AMZN TRADE 2021.01.05 06:50 | 3187.963 12.375 3188 3188.01 3187.84 3188.01 8 \n",
"AMZN TRADE 2021.01.05 06:55 | 3184.826 10.95238 3188 3185.88 3183 3188 42 \n",
"AMZN TRADE 2021.01.05 07:00 | 3183.557 8.949275 3185.93 3182 3180.01 3188 138 \n",
"AMZN TRADE 2021.01.05 07:05 | 3179.813 7.686957 3182.06 3176.07 3175.61 3183.36 115 \n",
"AMZN TRADE 2021.01.05 07:10 | 3178.922 8.133333 3177 3178.15 3176.51 3180.98 60 \n",
"AMZN TRADE 2021.01.05 07:15 | 3179.855 9.560976 3178.22 3179.81 3178.22 3181.65 41 \n",
"AMZN TRADE 2021.01.05 07:20 | 3178.243 6.758065 3179.81 3178 3176 3180.21 62 \n",
"AMZN TRADE 2021.01.05 07:25 | 3178.599 10.67925 3179 3178.25 3176.93 3180 53 \n",
"AMZN TRADE 2021.01.05 07:30 | 3176.116 6.868421 3179 3176.15 3175.12 3179 38 \n",
"AMZN TRADE 2021.01.05 07:35 | 3177.898 7.270588 3176.79 3178.99 3176.2 3180 85 \n",
"AMZN TRADE 2021.01.05 07:40 | 3177.627 7.234043 3178.12 3177.22 3177 3178.12 47 \n",
"..\n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ compute 5 minute high, low, open close\n",
"select \n",
" Price: avg Price, \n",
" Volume: avg Quantity, \n",
" First_Price: first Price, \n",
" Last_Price: last Price,\n",
" Min_Price: min Price, \n",
" Max_Price: max Price, \n",
" Events: count Price \n",
" by Ticker,EventType,date,5 xbar Timestamp.minute \n",
" from taq \n",
" where date=max date,Ticker=`AMZN,EventType=`TRADE"
]
},
{
"cell_type": "code",
"execution_count": 20,
"id": "56144063-bc99-459a-bf9d-1a13755b7090",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Ticker EventType date | AvgPrice Events Volume \n",
"------------------------------| ------------------------\n",
"AMZN QUOTE ASK 2021.01.05| 3191.516 615762 75072400\n",
"AMZN QUOTE ASK NB 2021.01.05| 3203.825 158824 20565900\n",
"AMZN QUOTE BID 2021.01.05| 3150.847 616379 74631400\n",
"AMZN QUOTE BID NB 2021.01.05| 3201.911 158826 19186200\n",
"AMZN TRADE 2021.01.05| 3202.31 129553 1779429 \n",
"AMZN TRADE NB 2021.01.05| 3203.389 6077 1091928 \n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ average price by date, ticker, event type\n",
"select \n",
" AvgPrice:avg Price, \n",
" Events:count Price, \n",
" Volume:sum Quantity \n",
" by Ticker,EventType,date \n",
" from taq \n",
" where date=max date, Ticker in (`AMZN`JPM)\n"
]
},
{
"cell_type": "code",
"execution_count": 21,
"id": "98d4edca-72c2-47a2-a9fb-ab123f717d81",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Ticker EventType date hh| AvgPrice Events Volume \n",
"---------------------------------| ------------------------\n",
"AMZN QUOTE ASK 2021.01.05 4 | 3171.53 1975 224300 \n",
"AMZN QUOTE ASK 2021.01.05 5 | 3202.886 2151 227600 \n",
"AMZN QUOTE ASK 2021.01.05 6 | 3192.84 1474 148400 \n",
"AMZN QUOTE ASK 2021.01.05 7 | 3124.191 3998 1023900 \n",
"AMZN QUOTE ASK 2021.01.05 8 | 2997.59 6601 765900 \n",
"AMZN QUOTE ASK 2021.01.05 9 | 3187.085 78196 9725000 \n",
"AMZN QUOTE ASK 2021.01.05 10| 3198.614 126219 14125100\n",
"AMZN QUOTE ASK 2021.01.05 11| 3182.17 79024 9060300 \n",
"AMZN QUOTE ASK 2021.01.05 12| 3192.846 66319 7049900 \n",
"AMZN QUOTE ASK 2021.01.05 13| 3205.259 62269 7885300 \n",
"AMZN QUOTE ASK 2021.01.05 14| 3209.761 80717 12378000\n",
"AMZN QUOTE ASK 2021.01.05 15| 3186.998 105985 12370900\n",
"AMZN QUOTE ASK 2021.01.05 16| 2938.977 486 48200 \n",
"AMZN QUOTE ASK 2021.01.05 17| 3006.261 76 8200 \n",
"AMZN QUOTE ASK 2021.01.05 18| 2481.56 48 6700 \n",
"AMZN QUOTE ASK 2021.01.05 19| 3122.512 224 24700 \n",
"AMZN QUOTE ASK NB 2021.01.05 4 | 3210.762 451 47400 \n",
"AMZN QUOTE ASK NB 2021.01.05 5 | 3195.696 604 60600 \n",
"AMZN QUOTE ASK NB 2021.01.05 6 | 3193.508 349 35600 \n",
"AMZN QUOTE ASK NB 2021.01.05 7 | 3181.151 719 125100 \n",
"AMZN QUOTE ASK NB 2021.01.05 8 | 3172.126 926 121600 \n",
"AMZN QUOTE ASK NB 2021.01.05 9 | 3198.107 18599 2770700 \n",
"AMZN QUOTE ASK NB 2021.01.05 10| 3203.092 30578 3607900 \n",
"AMZN QUOTE ASK NB 2021.01.05 11| 3194.771 20330 2340700 \n",
"AMZN QUOTE ASK NB 2021.01.05 12| 3192.519 18378 2058900 \n",
"AMZN QUOTE ASK NB 2021.01.05 13| 3202.054 15680 1979800 \n",
"AMZN QUOTE ASK NB 2021.01.05 14| 3216.861 24881 3815200 \n",
"AMZN QUOTE ASK NB 2021.01.05 15| 3213.817 26775 3508700 \n",
"AMZN QUOTE ASK NB 2021.01.05 16| 3218.022 197 25200 \n",
"AMZN QUOTE ASK NB 2021.01.05 17| 3215.077 89 18100 \n",
"AMZN QUOTE ASK NB 2021.01.05 18| 3214.039 103 18600 \n",
"AMZN QUOTE ASK NB 2021.01.05 19| 3219.834 165 31800 \n",
"AMZN QUOTE BID 2021.01.05 4 | 3179.659 1975 227700 \n",
"AMZN QUOTE BID 2021.01.05 5 | 3179.684 2151 226200 \n",
"AMZN QUOTE BID 2021.01.05 6 | 3171.857 1474 178600 \n",
"AMZN QUOTE BID 2021.01.05 7 | 3108.782 3903 553300 \n",
"AMZN QUOTE BID 2021.01.05 8 | 2909.168 6420 1611900 \n",
"AMZN QUOTE BID 2021.01.05 9 | 3143.151 78629 9511600 \n",
"AMZN QUOTE BID 2021.01.05 10| 3147.745 125095 15613200\n",
"AMZN QUOTE BID 2021.01.05 11| 3131.992 79731 9570500 \n",
"AMZN QUOTE BID 2021.01.05 12| 3142.039 64767 8520900 \n",
"AMZN QUOTE BID 2021.01.05 13| 3160.362 63319 7456000 \n",
"AMZN QUOTE BID 2021.01.05 14| 3183.837 82000 8985200 \n",
"AMZN QUOTE BID 2021.01.05 15| 3165.175 106070 12070800\n",
"AMZN QUOTE BID 2021.01.05 16| 2851.801 486 59600 \n",
"..\n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ average price by date, hour, ticker, event type\n",
"select \n",
" AvgPrice:avg Price, \n",
" Events:count Price, \n",
" Volume:sum Quantity \n",
" by Ticker,EventType,date,Timestamp.hh \n",
" from taq \n",
" where date=max date, Ticker in (`AMZN`JPM)\n",
" "
]
},
{
"cell_type": "code",
"execution_count": 22,
"id": "32c0dc85-97ab-4c76-9547-92fc5cf17ea2",
"metadata": {
"tags": []
},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"2024.11.26T20:09:01.041\n"
]
}
],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ time on server\n",
".z.z"
]
},
{
"cell_type": "code",
"execution_count": 23,
"id": "cc03894b-af20-4971-a303-f38e753ac7f6",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Last Run: 2024-11-26 20:09:01.048031\n"
]
}
],
"source": [
"print( f\"Last Run: {datetime.datetime.now()}\" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "a9c66c49-f7e3-4deb-97bd-5d23a6bc83c6",
"metadata": {},
"outputs": [],
"source": []
}
],
"metadata": {
"kernelspec": {
"display_name": "conda_python3",
"language": "python",
"name": "conda_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.10.15"
}
},
"nbformat": 4,
"nbformat_minor": 5
}