ManagedkdbInsights/basic_tick_V3/pykx_query_all.ipynb (2,040 lines of code) (raw):
{
"cells": [
{
"cell_type": "markdown",
"id": "1334d92e-34f4-448c-b6af-73cba89e9d6d",
"metadata": {
"tags": []
},
"source": [
"# PyKX Query Components\n",
"Query all components of the application.\n",
"\n",
"## Architecture\n",
"<img src=\"images/Deepdive Diagrams-BasicTick V3.drawio.png\" width=\"80%\">\n"
]
},
{
"cell_type": "code",
"execution_count": 1,
"id": "11e9b62a-9cda-4a57-99ca-3b13aff6b7f3",
"metadata": {},
"outputs": [],
"source": [
"import warnings\n",
"warnings.simplefilter(action='ignore', category=FutureWarning)\n",
"\n",
"import os\n",
"import boto3\n",
"import json\n",
"import datetime\n",
"\n",
"import pykx as kx\n",
"\n",
"from env import *\n",
"from managed_kx import *\n",
"\n",
"# Cluster names and database\n",
"from config import *\n"
]
},
{
"cell_type": "code",
"execution_count": 2,
"id": "cb429a9f-e8a4-41d2-8572-c4e8a49ad387",
"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": [
"# Connections to Clusters"
]
},
{
"cell_type": "code",
"execution_count": 3,
"id": "5cae1dcc",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"hdb = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=HDB_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"tp = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=TP_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"rdb = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=RDB_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"rts = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=RTS_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"gw = get_pykx_connection(client, \n",
" environmentId=ENV_ID, clusterName=GW_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)"
]
},
{
"cell_type": "markdown",
"id": "32c26a3e-e51e-4981-951b-efca08e20e9a",
"metadata": {},
"source": [
"# Query the HDB\n",
"With the HDB connection, query its data."
]
},
{
"cell_type": "code",
"execution_count": 4,
"id": "125c2a8e-1880-4043-9230-1089778e5bf8",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"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>quote</th>\n",
" <td>26505636</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade</th>\n",
" <td>5301569</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Dictionary(pykx.q('\n",
"quote| 26505636\n",
"trade| 5301569\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"========================================\n",
"quote: 547,477\n",
"----------------------------------------\n"
]
},
{
"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>quotes</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2024-11-18</th>\n",
" <td>4323449</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-19</th>\n",
" <td>4440838</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-20</th>\n",
" <td>4446429</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-21</th>\n",
" <td>4422176</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-22</th>\n",
" <td>4447795</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-25</th>\n",
" <td>4424949</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" quotes\n",
"date \n",
"2024-11-18 4323449\n",
"2024-11-19 4440838\n",
"2024-11-20 4446429\n",
"2024-11-21 4422176\n",
"2024-11-22 4447795\n",
"2024-11-25 4424949"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"========================================\n",
"trade: 109,894\n",
"----------------------------------------\n"
]
},
{
"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>trades</th>\n",
" </tr>\n",
" <tr>\n",
" <th>date</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>2024-11-18</th>\n",
" <td>866361</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-19</th>\n",
" <td>888436</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-20</th>\n",
" <td>888187</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-21</th>\n",
" <td>883938</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-22</th>\n",
" <td>889931</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2024-11-25</th>\n",
" <td>884716</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" trades\n",
"date \n",
"2024-11-18 866361\n",
"2024-11-19 888436\n",
"2024-11-20 888187\n",
"2024-11-21 883938\n",
"2024-11-22 889931\n",
"2024-11-25 884716"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"========================================\n"
]
}
],
"source": [
"# ensure database is loaded\n",
"hdb('.Q.lo[hsym`$.aws.akdbp,\"/\",.aws.akdb,\"/\";0b;0b]')\n",
"\n",
"# inventory of tables in the database and rows in each\n",
"print(\"All Tables and Counts\")\n",
"display( hdb(\"tables[]!count each value each tables[]\") )\n",
"print(40*'=')\n",
"\n",
"# Dates and Counts of one table\n",
"tables = hdb('tables[]').py()\n",
"\n",
"for t in tables:\n",
" # anything to display?\n",
" tt = hdb(f\"select {t}s:count i by date from {t} where date in 10#desc date\").pd()\n",
" r = rdb(f'count {t}').py()\n",
"\n",
" if r == 0: \n",
" continue\n",
"\n",
" print(f'{t}: {r:,}')\n",
" print(40*'-')\n",
" display(tt)\n",
" print(40*'=')"
]
},
{
"cell_type": "markdown",
"id": "47a1f5c4-3433-4b06-9ef6-8164ad2fb100",
"metadata": {},
"source": [
"# Query the RBD\n",
"With the RDB connection, query its data. Will use a q magic cell to send a function to the RDB and run it from Python as well.\n"
]
},
{
"cell_type": "code",
"execution_count": 5,
"id": "3371f4c9-fcd3-46ab-8780-feb58d382a66",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"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>quote</th>\n",
" <td>547477</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade</th>\n",
" <td>109894</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Dictionary(pykx.q('\n",
"quote| 547477\n",
"trade| 109894\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"Last Times\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>quote</th>\n",
" <td>17:27:55.810</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade</th>\n",
" <td>17:27:55.810</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Dictionary(pykx.q('\n",
"quote| 17:27:55.810\n",
"trade| 17:27:55.810\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"\n",
"========================================\n",
"quote: 547,477\n",
"----------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>quotes</th>\n",
" </tr>\n",
" <tr>\n",
" <th>hour</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>16i</th>\n",
" <td>322730</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17i</th>\n",
" <td>224747</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"hour| quotes\n",
"----| ------\n",
"16 | 322730\n",
"17 | 224747\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"========================================\n",
"trade: 109,894\n",
"----------------------------------------\n"
]
},
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>trades</th>\n",
" </tr>\n",
" <tr>\n",
" <th>hour</th>\n",
" <th></th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>16i</th>\n",
" <td>64532</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17i</th>\n",
" <td>45362</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"hour| trades\n",
"----| ------\n",
"16 | 64532 \n",
"17 | 45362 \n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"========================================\n"
]
}
],
"source": [
"# inventory of tables in the database and rows in each\n",
"print(\"Counts\")\n",
"display( rdb(\"tables[]!count each value each tables[]\") )\n",
"\n",
"# last timestamps in each table\n",
"print(\"Last Times\")\n",
"display( rdb(\"tables[]!{exec first max `time$time from x}each tables[]\") )\n",
"print()\n",
"print(40*'=')\n",
"\n",
"\n",
"# Dates and Counts of one table\n",
"tables = rdb('tables[]').py()\n",
"\n",
"for t in tables:\n",
" r = rdb(f'count {t}').py()\n",
"\n",
" if r == 0: \n",
" continue\n",
"\n",
" print(f'{t}: {r:,}')\n",
" print(40*'-')\n",
" # Summarize table by hour\n",
" display( rdb(f\"select {t}s:count i by hour:`hh$time from {t}\") )\n",
" print(40*'=')"
]
},
{
"cell_type": "markdown",
"id": "5a95cc13-7863-4c20-a6f0-9a316a3b13f3",
"metadata": {},
"source": [
"## Define a function on the RDB\n",
"Using a q magic cell, define a function on the RDB."
]
},
{
"cell_type": "code",
"execution_count": 6,
"id": "71b89724-9a33-4d18-b70e-cb7ae5216b04",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"# get the RDB conneciton string\n",
"rdb_conn_str = get_kx_connection_string(client, \n",
" environmentId=ENV_ID, clusterName=RDB_CLUSTER_NAME, \n",
" userName=KDB_USERNAME, boto_session=session)\n",
"\n",
"# parse the RDB connection string to its components\n",
"host, port, username, password = parse_connection_string(rdb_conn_str)"
]
},
{
"cell_type": "code",
"execution_count": 7,
"id": "46363e87-b0be-4175-8e0f-1ada24443596",
"metadata": {
"tags": []
},
"outputs": [],
"source": [
"%%q --host $host --port $port --user $username --pass $password\n",
"\n",
"/ define a function to calculate TWAP\n",
"generateTWAP:{[syms;st;et] \n",
" if[syms~`;syms:exec distinct sym from trade];\n",
" // Calculate statistics from trade and quote tables, join the tables with \n",
" // appropriate join function in this case a union join \n",
" quoteMetrics:select avg_spread:avg (ask-bid),twa_spread:(next[time]- time) wavg (ask-bid), avg_size:0.5*avg (asize+bsize),avg_duration:\"t\"$avg next[time]-time by sym from quote where sym in syms,time within(st;et); \n",
" tradeMetrics:select std_dev:2*dev price, twap:(next[time]-time) wavg price,max_price:max price, min_price:min price,vwap:size wavg price by sym from trade where sym in syms,time within(st;et); \n",
" quoteMetrics uj tradeMetrics \n",
" }\n"
]
},
{
"cell_type": "markdown",
"id": "53836988-46d8-45ab-b2c9-37f7abbb9ecf",
"metadata": {},
"source": [
"## Call Function on RDB and Display Results\n",
"Function is called on the RDB, results are then returned as a Pandas DataFrame using PyKX and display the results as a table in the notebook."
]
},
{
"cell_type": "code",
"execution_count": 8,
"id": "dff1db12",
"metadata": {},
"outputs": [
{
"data": {
"text/html": [
"<table border=\"1\" class=\"dataframe\">\n",
" <thead>\n",
" <tr style=\"text-align: right;\">\n",
" <th></th>\n",
" <th>avg_spread</th>\n",
" <th>twa_spread</th>\n",
" <th>avg_size</th>\n",
" <th>avg_duration</th>\n",
" <th>std_dev</th>\n",
" <th>twap</th>\n",
" <th>max_price</th>\n",
" <th>min_price</th>\n",
" <th>vwap</th>\n",
" </tr>\n",
" <tr>\n",
" <th>sym</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>AAPL</th>\n",
" <td>1.005583</td>\n",
" <td>0.9942711</td>\n",
" <td>54.51072</td>\n",
" <td>11:28:07.492</td>\n",
" <td>1.911798</td>\n",
" <td>83.05152</td>\n",
" <td>85.34</td>\n",
" <td>80.03</td>\n",
" <td>83.07975</td>\n",
" </tr>\n",
" <tr>\n",
" <th>AIG</th>\n",
" <td>1.001028</td>\n",
" <td>1.254725</td>\n",
" <td>54.74056</td>\n",
" <td>28:13:06.874</td>\n",
" <td>0.4425576</td>\n",
" <td>26.95132</td>\n",
" <td>27.85</td>\n",
" <td>26.56</td>\n",
" <td>27.03385</td>\n",
" </tr>\n",
" <tr>\n",
" <th>AMD</th>\n",
" <td>1.005192</td>\n",
" <td>1.092699</td>\n",
" <td>54.37874</td>\n",
" <td>19:11:11.378</td>\n",
" <td>0.7167555</td>\n",
" <td>33.62228</td>\n",
" <td>34.52</td>\n",
" <td>32.52</td>\n",
" <td>33.5295</td>\n",
" </tr>\n",
" <tr>\n",
" <th>DELL</th>\n",
" <td>1.006072</td>\n",
" <td>0.9395575</td>\n",
" <td>54.53501</td>\n",
" <td>28:59:00.108</td>\n",
" <td>0.2057405</td>\n",
" <td>12.31718</td>\n",
" <td>12.52</td>\n",
" <td>12.02</td>\n",
" <td>12.28705</td>\n",
" </tr>\n",
" <tr>\n",
" <th>DOW</th>\n",
" <td>1.005755</td>\n",
" <td>0.8788709</td>\n",
" <td>54.71421</td>\n",
" <td>57:31:11.042</td>\n",
" <td>0.205959</td>\n",
" <td>20.19641</td>\n",
" <td>20.49</td>\n",
" <td>19.87</td>\n",
" <td>20.19495</td>\n",
" </tr>\n",
" <tr>\n",
" <th>GOOG</th>\n",
" <td>0.9981131</td>\n",
" <td>1.118494</td>\n",
" <td>54.41368</td>\n",
" <td>09:36:42.553</td>\n",
" <td>1.949393</td>\n",
" <td>72.8231</td>\n",
" <td>75.07</td>\n",
" <td>69.4</td>\n",
" <td>72.4923</td>\n",
" </tr>\n",
" <tr>\n",
" <th>HPQ</th>\n",
" <td>1.004438</td>\n",
" <td>0.9333208</td>\n",
" <td>54.51127</td>\n",
" <td>28:50:46.271</td>\n",
" <td>0.5793449</td>\n",
" <td>35.98533</td>\n",
" <td>37.06</td>\n",
" <td>35.24</td>\n",
" <td>36.03893</td>\n",
" </tr>\n",
" <tr>\n",
" <th>IBM</th>\n",
" <td>0.9995193</td>\n",
" <td>0.4679382</td>\n",
" <td>54.40382</td>\n",
" <td>28:41:29.838</td>\n",
" <td>0.6769956</td>\n",
" <td>42.82357</td>\n",
" <td>43.73</td>\n",
" <td>41.48</td>\n",
" <td>42.61401</td>\n",
" </tr>\n",
" <tr>\n",
" <th>INTC</th>\n",
" <td>0.9987423</td>\n",
" <td>0.8842898</td>\n",
" <td>54.30217</td>\n",
" <td>19:00:18.225</td>\n",
" <td>0.9998559</td>\n",
" <td>50.80405</td>\n",
" <td>52.68</td>\n",
" <td>49.89</td>\n",
" <td>51.04574</td>\n",
" </tr>\n",
" <tr>\n",
" <th>MSFT</th>\n",
" <td>0.9958642</td>\n",
" <td>1.012356</td>\n",
" <td>54.43309</td>\n",
" <td>19:24:25.302</td>\n",
" <td>0.6744345</td>\n",
" <td>29.6254</td>\n",
" <td>30.42</td>\n",
" <td>28.8</td>\n",
" <td>29.56774</td>\n",
" </tr>\n",
" <tr>\n",
" <th>ORCL</th>\n",
" <td>1.000705</td>\n",
" <td>0.9126722</td>\n",
" <td>54.39711</td>\n",
" <td>28:24:01.131</td>\n",
" <td>0.5365028</td>\n",
" <td>35.73591</td>\n",
" <td>36.38</td>\n",
" <td>34.98</td>\n",
" <td>35.53986</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PEP</th>\n",
" <td>1.000791</td>\n",
" <td>0.9318127</td>\n",
" <td>54.70658</td>\n",
" <td>14:26:07.128</td>\n",
" <td>0.4759043</td>\n",
" <td>22.17261</td>\n",
" <td>22.63</td>\n",
" <td>21.49</td>\n",
" <td>22.02655</td>\n",
" </tr>\n",
" <tr>\n",
" <th>PRU</th>\n",
" <td>0.9991325</td>\n",
" <td>1.311252</td>\n",
" <td>54.60037</td>\n",
" <td>29:05:49.142</td>\n",
" <td>0.8752428</td>\n",
" <td>59.08312</td>\n",
" <td>60.41</td>\n",
" <td>58.2</td>\n",
" <td>59.33929</td>\n",
" </tr>\n",
" <tr>\n",
" <th>SBUX</th>\n",
" <td>0.9973331</td>\n",
" <td>1.125873</td>\n",
" <td>54.41697</td>\n",
" <td>14:22:38.825</td>\n",
" <td>1.526343</td>\n",
" <td>63.16366</td>\n",
" <td>65.25</td>\n",
" <td>60.79</td>\n",
" <td>63.02788</td>\n",
" </tr>\n",
" <tr>\n",
" <th>TXN</th>\n",
" <td>1.000831</td>\n",
" <td>0.898046</td>\n",
" <td>54.53925</td>\n",
" <td>57:01:31.107</td>\n",
" <td>0.2066331</td>\n",
" <td>17.90498</td>\n",
" <td>18.19</td>\n",
" <td>17.57</td>\n",
" <td>17.89456</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"sym | avg_spread twa_spread avg_size avg_duration std_dev twap max_pric..\n",
"----| -----------------------------------------------------------------------..\n",
"AAPL| 1.005583 0.9942711 54.51072 11:28:07.492 1.911798 83.05152 85.34 ..\n",
"AIG | 1.001028 1.254725 54.74056 28:13:06.874 0.4425576 26.95132 27.85 ..\n",
"AMD | 1.005192 1.092699 54.37874 19:11:11.378 0.7167555 33.62228 34.52 ..\n",
"DELL| 1.006072 0.9395575 54.53501 28:59:00.108 0.2057405 12.31718 12.52 ..\n",
"DOW | 1.005755 0.8788709 54.71421 57:31:11.042 0.205959 20.19641 20.49 ..\n",
"GOOG| 0.9981131 1.118494 54.41368 09:36:42.553 1.949393 72.8231 75.07 ..\n",
"HPQ | 1.004438 0.9333208 54.51127 28:50:46.271 0.5793449 35.98533 37.06 ..\n",
"IBM | 0.9995193 0.4679382 54.40382 28:41:29.838 0.6769956 42.82357 43.73 ..\n",
"INTC| 0.9987423 0.8842898 54.30217 19:00:18.225 0.9998559 50.80405 52.68 ..\n",
"MSFT| 0.9958642 1.012356 54.43309 19:24:25.302 0.6744345 29.6254 30.42 ..\n",
"ORCL| 1.000705 0.9126722 54.39711 28:24:01.131 0.5365028 35.73591 36.38 ..\n",
"PEP | 1.000791 0.9318127 54.70658 14:26:07.128 0.4759043 22.17261 22.63 ..\n",
"PRU | 0.9991325 1.311252 54.60037 29:05:49.142 0.8752428 59.08312 60.41 ..\n",
"SBUX| 0.9973331 1.125873 54.41697 14:22:38.825 1.526343 63.16366 65.25 ..\n",
"TXN | 1.000831 0.898046 54.53925 57:01:31.107 0.2066331 17.90498 18.19 ..\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>avg_spread</th>\n",
" <th>twa_spread</th>\n",
" <th>avg_size</th>\n",
" <th>avg_duration</th>\n",
" <th>std_dev</th>\n",
" <th>twap</th>\n",
" <th>max_price</th>\n",
" <th>min_price</th>\n",
" <th>vwap</th>\n",
" </tr>\n",
" <tr>\n",
" <th>sym</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>AAPL</th>\n",
" <td>1.005583</td>\n",
" <td>0.9942711</td>\n",
" <td>54.51072</td>\n",
" <td>11:28:07.492</td>\n",
" <td>1.911798</td>\n",
" <td>83.05152</td>\n",
" <td>85.34</td>\n",
" <td>80.03</td>\n",
" <td>83.07975</td>\n",
" </tr>\n",
" <tr>\n",
" <th>IBM</th>\n",
" <td>0.9995193</td>\n",
" <td>0.4679382</td>\n",
" <td>54.40382</td>\n",
" <td>28:41:29.838</td>\n",
" <td>0.6769956</td>\n",
" <td>42.82357</td>\n",
" <td>43.73</td>\n",
" <td>41.48</td>\n",
" <td>42.61401</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.KeyedTable(pykx.q('\n",
"sym | avg_spread twa_spread avg_size avg_duration std_dev twap max_pric..\n",
"----| -----------------------------------------------------------------------..\n",
"AAPL| 1.005583 0.9942711 54.51072 11:28:07.492 1.911798 83.05152 85.34 ..\n",
"IBM | 0.9995193 0.4679382 54.40382 28:41:29.838 0.6769956 42.82357 43.73 ..\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Call the function for all tickers and a time range\n",
"display( rdb(\"generateTWAP[`;00:00:00.040; 23:59:59.999]\") )\n",
"\n",
"# call the function for some tickers and another range\n",
"display( rdb(\"generateTWAP[`AAPL`IBM;00:00:00.040; 23:59:59.999]\") )"
]
},
{
"cell_type": "markdown",
"id": "ddafebe2-5729-42b2-9598-b304d2af0776",
"metadata": {},
"source": [
"# Query the GW"
]
},
{
"cell_type": "code",
"execution_count": 9,
"id": "4c09a5be-ce4a-43bf-a6df-3305b3f9332e",
"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>process</th>\n",
" <th>handle</th>\n",
" <th>connected</th>\n",
" <th>address</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>rdb</td>\n",
" <td>12</td>\n",
" <td>True</td>\n",
" <td>:tcps://ip-192-168-7-230.ec2.internal:443:GATEWAY_basictickdb:Host=ip-192-168-7-230.ec2.internal&Port=443&User...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>hdb</td>\n",
" <td>13</td>\n",
" <td>True</td>\n",
" <td>:tcps://ip-192-168-8-181.ec2.internal:443:GATEWAY_basictickdb:Host=ip-192-168-8-181.ec2.internal&Port=443&User...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>hdb</td>\n",
" <td>14</td>\n",
" <td>True</td>\n",
" <td>:tcps://ip-192-168-14-13.ec2.internal:443:GATEWAY_basictickdb:Host=ip-192-168-14-13.ec2.internal&Port=443&User...</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" process handle connected \\\n",
"0 rdb 12 True \n",
"1 hdb 13 True \n",
"2 hdb 14 True \n",
"\n",
" address \n",
"0 :tcps://ip-192-168-7-230.ec2.internal:443:GATEWAY_basictickdb:Host=ip-192-168-7-230.ec2.internal&Port=443&User... \n",
"1 :tcps://ip-192-168-8-181.ec2.internal:443:GATEWAY_basictickdb:Host=ip-192-168-8-181.ec2.internal&Port=443&User... \n",
"2 :tcps://ip-192-168-14-13.ec2.internal:443:GATEWAY_basictickdb:Host=ip-192-168-14-13.ec2.internal&Port=443&User... "
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# Query the GW for its connected processes, are all connected?\n",
"proc_pdf = gw(\"select process, handle, connected, address from .conn.procs\").pd()\n",
"\n",
"# are any processes not connected? if so-reconnect\n",
"if (len(proc_pdf) == 0) or len(proc_pdf[proc_pdf.connected == False].index) > 0:\n",
" print(\"reinit Gateway\")\n",
" gw(\"reinit[hdb_name; rdb_name]\")\n",
" proc_pdf = gw(\"select process, handle, connected, address from .conn.procs\").pd()\n",
"\n",
"# truncate address with elipsis\n",
"proc_pdf['address'] = proc_pdf['address'].str.slice(0,110)+\"...\"\n",
"\n",
"# display table\n",
"display(proc_pdf)"
]
},
{
"cell_type": "code",
"execution_count": 10,
"id": "25b71834",
"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>sym</th>\n",
" <th>time</th>\n",
" <th>price</th>\n",
" <th>size</th>\n",
" <th>source</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>SBUX</td>\n",
" <td>2024-11-25 09:30:00.000003557</td>\n",
" <td>64.83</td>\n",
" <td>93</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>HPQ</td>\n",
" <td>2024-11-25 09:30:00.000046186</td>\n",
" <td>37.39</td>\n",
" <td>71</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>GOOG</td>\n",
" <td>2024-11-25 09:30:00.000050642</td>\n",
" <td>76.40</td>\n",
" <td>20</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sym time price size source\n",
"0 SBUX 2024-11-25 09:30:00.000003557 64.83 93 HDB\n",
"1 HPQ 2024-11-25 09:30:00.000046186 37.39 71 HDB\n",
"2 GOOG 2024-11-25 09:30:00.000050642 76.40 20 HDB"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"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>sym</th>\n",
" <th>time</th>\n",
" <th>price</th>\n",
" <th>size</th>\n",
" <th>source</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>0</th>\n",
" <td>DOW</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>20.15</td>\n",
" <td>14</td>\n",
" <td>RDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>DELL</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>12.33</td>\n",
" <td>52</td>\n",
" <td>RDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>INTC</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>52.05</td>\n",
" <td>98</td>\n",
" <td>RDB</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
" sym time price size source\n",
"0 DOW 2024-11-26 17:27:55.810468123 20.15 14 RDB\n",
"1 DELL 2024-11-26 17:27:55.810468123 12.33 52 RDB\n",
"2 INTC 2024-11-26 17:27:55.810468123 52.05 98 RDB"
]
},
"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>time</th>\n",
" <th>sym</th>\n",
" <th>price</th>\n",
" <th>size</th>\n",
" <th>source</th>\n",
" </tr>\n",
" <tr>\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>2024.11.21D17:28:00.374830474</td>\n",
" <td>IBM</td>\n",
" <td>45.5</td>\n",
" <td>12</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>1</th>\n",
" <td>2024.11.21D17:28:00.426898373</td>\n",
" <td>IBM</td>\n",
" <td>45.48</td>\n",
" <td>13</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>2</th>\n",
" <td>2024.11.21D17:28:00.750113726</td>\n",
" <td>IBM</td>\n",
" <td>45.52</td>\n",
" <td>25</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>3</th>\n",
" <td>2024.11.21D17:28:00.986644218</td>\n",
" <td>IBM</td>\n",
" <td>45.59</td>\n",
" <td>62</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>4</th>\n",
" <td>2024.11.21D17:28:01.010840251</td>\n",
" <td>IBM</td>\n",
" <td>45.56</td>\n",
" <td>53</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>5</th>\n",
" <td>2024.11.21D17:28:01.060328649</td>\n",
" <td>IBM</td>\n",
" <td>45.53</td>\n",
" <td>33</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>6</th>\n",
" <td>2024.11.21D17:28:01.072235192</td>\n",
" <td>IBM</td>\n",
" <td>45.52</td>\n",
" <td>58</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>7</th>\n",
" <td>2024.11.21D17:28:01.530823113</td>\n",
" <td>IBM</td>\n",
" <td>45.61</td>\n",
" <td>58</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>8</th>\n",
" <td>2024.11.21D17:28:01.928528846</td>\n",
" <td>IBM</td>\n",
" <td>45.63</td>\n",
" <td>24</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>9</th>\n",
" <td>2024.11.21D17:28:02.348063612</td>\n",
" <td>IBM</td>\n",
" <td>45.61</td>\n",
" <td>52</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>10</th>\n",
" <td>2024.11.21D17:28:02.518649689</td>\n",
" <td>IBM</td>\n",
" <td>45.64</td>\n",
" <td>57</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>11</th>\n",
" <td>2024.11.21D17:28:02.898078810</td>\n",
" <td>IBM</td>\n",
" <td>45.58</td>\n",
" <td>64</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>12</th>\n",
" <td>2024.11.21D17:28:03.163174352</td>\n",
" <td>IBM</td>\n",
" <td>45.62</td>\n",
" <td>19</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>13</th>\n",
" <td>2024.11.21D17:28:03.214064945</td>\n",
" <td>IBM</td>\n",
" <td>45.61</td>\n",
" <td>72</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>14</th>\n",
" <td>2024.11.21D17:28:03.533520214</td>\n",
" <td>IBM</td>\n",
" <td>45.59</td>\n",
" <td>15</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>15</th>\n",
" <td>2024.11.21D17:28:03.714553367</td>\n",
" <td>IBM</td>\n",
" <td>45.61</td>\n",
" <td>50</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>16</th>\n",
" <td>2024.11.21D17:28:03.783586042</td>\n",
" <td>IBM</td>\n",
" <td>45.65</td>\n",
" <td>92</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>17</th>\n",
" <td>2024.11.21D17:28:03.927305861</td>\n",
" <td>IBM</td>\n",
" <td>45.63</td>\n",
" <td>47</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>18</th>\n",
" <td>2024.11.21D17:28:04.081668766</td>\n",
" <td>IBM</td>\n",
" <td>45.58</td>\n",
" <td>36</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>19</th>\n",
" <td>2024.11.21D17:28:04.236899176</td>\n",
" <td>IBM</td>\n",
" <td>45.59</td>\n",
" <td>87</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>20</th>\n",
" <td>2024.11.21D17:28:04.380414006</td>\n",
" <td>IBM</td>\n",
" <td>45.61</td>\n",
" <td>71</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>21</th>\n",
" <td>2024.11.21D17:28:04.464986450</td>\n",
" <td>IBM</td>\n",
" <td>45.61</td>\n",
" <td>87</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>22</th>\n",
" <td>2024.11.21D17:28:05.210925432</td>\n",
" <td>IBM</td>\n",
" <td>45.62</td>\n",
" <td>47</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>23</th>\n",
" <td>2024.11.21D17:28:05.682904883</td>\n",
" <td>IBM</td>\n",
" <td>45.6</td>\n",
" <td>95</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" <tr>\n",
" <th>...</th>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" <td>...</td>\n",
" </tr>\n",
" <tr>\n",
" <th>84660</th>\n",
" <td>2024.11.25D17:29:59.998916449</td>\n",
" <td>IBM</td>\n",
" <td>46.38</td>\n",
" <td>65</td>\n",
" <td>HDB</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"<p>84,661 rows × 5 columns</p>"
],
"text/plain": [
"pykx.Table(pykx.q('\n",
"time sym price size source\n",
"---------------------------------------------------\n",
"2024.11.21D17:28:00.374830474 IBM 45.5 12 HDB \n",
"2024.11.21D17:28:00.426898373 IBM 45.48 13 HDB \n",
"2024.11.21D17:28:00.750113726 IBM 45.52 25 HDB \n",
"2024.11.21D17:28:00.986644218 IBM 45.59 62 HDB \n",
"2024.11.21D17:28:01.010840251 IBM 45.56 53 HDB \n",
"2024.11.21D17:28:01.060328649 IBM 45.53 33 HDB \n",
"2024.11.21D17:28:01.072235192 IBM 45.52 58 HDB \n",
"2024.11.21D17:28:01.530823113 IBM 45.61 58 HDB \n",
"2024.11.21D17:28:01.928528846 IBM 45.63 24 HDB \n",
"2024.11.21D17:28:02.348063612 IBM 45.61 52 HDB \n",
"2024.11.21D17:28:02.518649689 IBM 45.64 57 HDB \n",
"2024.11.21D17:28:02.898078810 IBM 45.58 64 HDB \n",
"2024.11.21D17:28:03.163174352 IBM 45.62 19 HDB \n",
"2024.11.21D17:28:03.214064945 IBM 45.61 72 HDB \n",
"2024.11.21D17:28:03.533520214 IBM 45.59 15 HDB \n",
"2024.11.21D17:28:03.714553367 IBM 45.61 50 HDB \n",
"2024.11.21D17:28:03.783586042 IBM 45.65 92 HDB \n",
"2024.11.21D17:28:03.927305861 IBM 45.63 47 HDB \n",
"2024.11.21D17:28:04.081668766 IBM 45.58 36 HDB \n",
"2024.11.21D17:28:04.236899176 IBM 45.59 87 HDB \n",
"..\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
}
],
"source": [
"# query GW using queryData function on gateway\n",
"\n",
"# query and sample specific table for date range (today -3 days to tomorrow)\n",
"gw(\"res: `time xasc queryData[`trade;`;.z.D-3;.z.D+1]\").pd()\n",
"\n",
"# first/last 5 rows from res\n",
"display( gw(\"select [3] from res\").pd() )\n",
"display( gw(\"select [-3] from res\").pd() )\n",
"\n",
"# select for a specific table from ticker with time range (5 days ago to now)\n",
"display( hdb(\".query.data[`trade;`IBM;.z.P-5D;.z.P]\") )"
]
},
{
"cell_type": "markdown",
"id": "dc573dd9-2fe9-4ba4-9077-e5eab714e4fb",
"metadata": {},
"source": [
"# Query the RTS Cluster\n",
"The RTS cluster is subscribing to the tickerplant and maintaining another set of tables. Connect to the RTS and show the contents of its tables."
]
},
{
"cell_type": "code",
"execution_count": 11,
"id": "ed1a3441-de0f-4627-80c5-94ea0598ccb9",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"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>quote</th>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade</th>\n",
" <td>0</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade_hlcv</th>\n",
" <td>15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade_last</th>\n",
" <td>15</td>\n",
" </tr>\n",
" <tr>\n",
" <th>trade_vwap</th>\n",
" <td>15</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>"
],
"text/plain": [
"pykx.Dictionary(pykx.q('\n",
"quote | 0\n",
"trade | 0\n",
"trade_hlcv| 15\n",
"trade_last| 15\n",
"trade_vwap| 15\n",
"'))"
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"trade_hlcv: 15\n",
"====================================================================================================\n"
]
},
{
"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>sym</th>\n",
" <th>AAPL</th>\n",
" <th>AIG</th>\n",
" <th>AMD</th>\n",
" <th>DELL</th>\n",
" <th>DOW</th>\n",
" <th>GOOG</th>\n",
" <th>HPQ</th>\n",
" <th>IBM</th>\n",
" <th>INTC</th>\n",
" <th>MSFT</th>\n",
" <th>ORCL</th>\n",
" <th>PEP</th>\n",
" <th>PRU</th>\n",
" <th>SBUX</th>\n",
" <th>TXN</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>high</th>\n",
" <td>85.34</td>\n",
" <td>27.85</td>\n",
" <td>34.52</td>\n",
" <td>12.52</td>\n",
" <td>20.49</td>\n",
" <td>75.07</td>\n",
" <td>37.06</td>\n",
" <td>43.73</td>\n",
" <td>52.68</td>\n",
" <td>30.42</td>\n",
" <td>36.38</td>\n",
" <td>22.63</td>\n",
" <td>60.41</td>\n",
" <td>65.25</td>\n",
" <td>18.19</td>\n",
" </tr>\n",
" <tr>\n",
" <th>low</th>\n",
" <td>80.03</td>\n",
" <td>26.56</td>\n",
" <td>32.52</td>\n",
" <td>12.02</td>\n",
" <td>19.87</td>\n",
" <td>69.40</td>\n",
" <td>35.24</td>\n",
" <td>41.48</td>\n",
" <td>49.89</td>\n",
" <td>28.80</td>\n",
" <td>34.98</td>\n",
" <td>21.49</td>\n",
" <td>58.20</td>\n",
" <td>60.79</td>\n",
" <td>17.57</td>\n",
" </tr>\n",
" <tr>\n",
" <th>close</th>\n",
" <td>83.86</td>\n",
" <td>26.84</td>\n",
" <td>33.56</td>\n",
" <td>12.33</td>\n",
" <td>20.15</td>\n",
" <td>73.03</td>\n",
" <td>36.00</td>\n",
" <td>42.86</td>\n",
" <td>52.05</td>\n",
" <td>29.51</td>\n",
" <td>35.64</td>\n",
" <td>22.26</td>\n",
" <td>59.50</td>\n",
" <td>62.24</td>\n",
" <td>17.80</td>\n",
" </tr>\n",
" <tr>\n",
" <th>volume</th>\n",
" <td>719833.00</td>\n",
" <td>290701.00</td>\n",
" <td>435090.00</td>\n",
" <td>281367.00</td>\n",
" <td>144134.00</td>\n",
" <td>853911.00</td>\n",
" <td>284019.00</td>\n",
" <td>289895.00</td>\n",
" <td>432180.00</td>\n",
" <td>426393.00</td>\n",
" <td>293940.00</td>\n",
" <td>565152.00</td>\n",
" <td>277317.00</td>\n",
" <td>567218.00</td>\n",
" <td>145116.00</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"sym AAPL AIG AMD DELL DOW GOOG \\\n",
"high 85.34 27.85 34.52 12.52 20.49 75.07 \n",
"low 80.03 26.56 32.52 12.02 19.87 69.40 \n",
"close 83.86 26.84 33.56 12.33 20.15 73.03 \n",
"volume 719833.00 290701.00 435090.00 281367.00 144134.00 853911.00 \n",
"\n",
"sym HPQ IBM INTC MSFT ORCL PEP \\\n",
"high 37.06 43.73 52.68 30.42 36.38 22.63 \n",
"low 35.24 41.48 49.89 28.80 34.98 21.49 \n",
"close 36.00 42.86 52.05 29.51 35.64 22.26 \n",
"volume 284019.00 289895.00 432180.00 426393.00 293940.00 565152.00 \n",
"\n",
"sym PRU SBUX TXN \n",
"high 60.41 65.25 18.19 \n",
"low 58.20 60.79 17.57 \n",
"close 59.50 62.24 17.80 \n",
"volume 277317.00 567218.00 145116.00 "
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"trade_last: 15\n",
"====================================================================================================\n"
]
},
{
"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>sym</th>\n",
" <th>AAPL</th>\n",
" <th>AIG</th>\n",
" <th>AMD</th>\n",
" <th>DELL</th>\n",
" <th>DOW</th>\n",
" <th>GOOG</th>\n",
" <th>HPQ</th>\n",
" <th>IBM</th>\n",
" <th>INTC</th>\n",
" <th>MSFT</th>\n",
" <th>ORCL</th>\n",
" <th>PEP</th>\n",
" <th>PRU</th>\n",
" <th>SBUX</th>\n",
" <th>TXN</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>time</th>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" <td>2024-11-26 17:27:55.810468123</td>\n",
" </tr>\n",
" <tr>\n",
" <th>price</th>\n",
" <td>83.86</td>\n",
" <td>26.84</td>\n",
" <td>33.56</td>\n",
" <td>12.33</td>\n",
" <td>20.15</td>\n",
" <td>73.03</td>\n",
" <td>36.0</td>\n",
" <td>42.86</td>\n",
" <td>52.05</td>\n",
" <td>29.51</td>\n",
" <td>35.64</td>\n",
" <td>22.26</td>\n",
" <td>59.5</td>\n",
" <td>62.24</td>\n",
" <td>17.8</td>\n",
" </tr>\n",
" <tr>\n",
" <th>size</th>\n",
" <td>82</td>\n",
" <td>72</td>\n",
" <td>79</td>\n",
" <td>52</td>\n",
" <td>14</td>\n",
" <td>56</td>\n",
" <td>67</td>\n",
" <td>62</td>\n",
" <td>98</td>\n",
" <td>45</td>\n",
" <td>98</td>\n",
" <td>98</td>\n",
" <td>72</td>\n",
" <td>97</td>\n",
" <td>50</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"sym AAPL AIG \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 83.86 26.84 \n",
"size 82 72 \n",
"\n",
"sym AMD DELL \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 33.56 12.33 \n",
"size 79 52 \n",
"\n",
"sym DOW GOOG \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 20.15 73.03 \n",
"size 14 56 \n",
"\n",
"sym HPQ IBM \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 36.0 42.86 \n",
"size 67 62 \n",
"\n",
"sym INTC MSFT \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 52.05 29.51 \n",
"size 98 45 \n",
"\n",
"sym ORCL PEP \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 35.64 22.26 \n",
"size 98 98 \n",
"\n",
"sym PRU SBUX \\\n",
"time 2024-11-26 17:27:55.810468123 2024-11-26 17:27:55.810468123 \n",
"price 59.5 62.24 \n",
"size 72 97 \n",
"\n",
"sym TXN \n",
"time 2024-11-26 17:27:55.810468123 \n",
"price 17.8 \n",
"size 50 "
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n",
"trade_vwap: 15\n",
"====================================================================================================\n"
]
},
{
"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>sym</th>\n",
" <th>AAPL</th>\n",
" <th>AIG</th>\n",
" <th>AMD</th>\n",
" <th>DELL</th>\n",
" <th>DOW</th>\n",
" <th>GOOG</th>\n",
" <th>HPQ</th>\n",
" <th>IBM</th>\n",
" <th>INTC</th>\n",
" <th>MSFT</th>\n",
" <th>ORCL</th>\n",
" <th>PEP</th>\n",
" <th>PRU</th>\n",
" <th>SBUX</th>\n",
" <th>TXN</th>\n",
" </tr>\n",
" </thead>\n",
" <tbody>\n",
" <tr>\n",
" <th>vwap</th>\n",
" <td>8973.671857</td>\n",
" <td>2920.2723</td>\n",
" <td>3621.391134</td>\n",
" <td>1326.710839</td>\n",
" <td>2181.112313</td>\n",
" <td>7829.765631</td>\n",
" <td>3891.171244</td>\n",
" <td>4603.00785</td>\n",
" <td>5515.438957</td>\n",
" <td>3193.378522</td>\n",
" <td>3838.688921</td>\n",
" <td>2379.048779</td>\n",
" <td>6407.095205</td>\n",
" <td>6806.223528</td>\n",
" <td>1932.874539</td>\n",
" </tr>\n",
" <tr>\n",
" <th>volume</th>\n",
" <td>719833.000000</td>\n",
" <td>290701.0000</td>\n",
" <td>435090.000000</td>\n",
" <td>281367.000000</td>\n",
" <td>144134.000000</td>\n",
" <td>853911.000000</td>\n",
" <td>284019.000000</td>\n",
" <td>289895.00000</td>\n",
" <td>432180.000000</td>\n",
" <td>426393.000000</td>\n",
" <td>293940.000000</td>\n",
" <td>565152.000000</td>\n",
" <td>277317.000000</td>\n",
" <td>567218.000000</td>\n",
" <td>145116.000000</td>\n",
" </tr>\n",
" </tbody>\n",
"</table>\n",
"</div>"
],
"text/plain": [
"sym AAPL AIG AMD DELL \\\n",
"vwap 8973.671857 2920.2723 3621.391134 1326.710839 \n",
"volume 719833.000000 290701.0000 435090.000000 281367.000000 \n",
"\n",
"sym DOW GOOG HPQ IBM \\\n",
"vwap 2181.112313 7829.765631 3891.171244 4603.00785 \n",
"volume 144134.000000 853911.000000 284019.000000 289895.00000 \n",
"\n",
"sym INTC MSFT ORCL PEP \\\n",
"vwap 5515.438957 3193.378522 3838.688921 2379.048779 \n",
"volume 432180.000000 426393.000000 293940.000000 565152.000000 \n",
"\n",
"sym PRU SBUX TXN \n",
"vwap 6407.095205 6806.223528 1932.874539 \n",
"volume 277317.000000 567218.000000 145116.000000 "
]
},
"metadata": {},
"output_type": "display_data"
},
{
"name": "stdout",
"output_type": "stream",
"text": [
"----------------------------------------------------------------------------------------------------\n"
]
}
],
"source": [
"# inventory of tables in the database and rows in each\n",
"print(\"All Tables and Counts\")\n",
"display( rts(\"tables[]!count each value each tables[]\") )\n",
"\n",
"tables = rts('tables[]').py()\n",
"\n",
"# show contents of tables\n",
"for t in tables:\n",
" # anything to display?\n",
" tt = rts(f\"select from {t}\").pd()\n",
" r = len(tt.index)\n",
"\n",
" # nothing in table\n",
" if r == 0: \n",
" continue\n",
"\n",
" # print table contents\n",
" print(f'{t}: {r:,}')\n",
" print(100*'=')\n",
"\n",
" # Contents of table, transponse if small\n",
" if r < 20:\n",
" display(tt.T)\n",
" else:\n",
" display(tt)\n",
" print(100*'-')\n"
]
},
{
"cell_type": "code",
"execution_count": 12,
"id": "89e38f4b-7020-4510-814d-667725ec804e",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Last Run: 2024-11-26 17:28:00.176554\n"
]
}
],
"source": [
"print( f\"Last Run: {datetime.datetime.now()}\" )"
]
},
{
"cell_type": "code",
"execution_count": null,
"id": "923b85e1-27c4-4dec-8017-aac4674f15cd",
"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
}