im_v2/ccxt/data/qa/notebooks/data_qa_bid_ask.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "43d15731",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:22.688915Z",
     "start_time": "2024-02-28T15:04:16.009192Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/app/core/statistics/random_samples.py:12: TqdmExperimentalWarning: Using `tqdm.autonotebook.tqdm` in notebook mode. Use `tqdm.tqdm` instead to force console mode (e.g. in jupyter console)\n",
      "  from tqdm.autonotebook import tqdm\n",
      "\u001b[33mWARNING\u001b[0m: No module named 'gluonts'\n"
     ]
    }
   ],
   "source": [
    "import logging\n",
    "\n",
    "import pandas as pd\n",
    "\n",
    "import core.config as cconfig\n",
    "import data_schema.dataset_schema_utils as dsdascut\n",
    "import helpers.hdbg as hdbg\n",
    "import helpers.henv as henv\n",
    "import helpers.hio as hio\n",
    "import helpers.hprint as hprint\n",
    "import im_v2.common.data.client.im_raw_data_client as imvcdcimrdc\n",
    "import im_v2.common.data.qa.dataset_validator as imvcdqdava\n",
    "import im_v2.common.data.qa.qa_check as imvcdqqach\n",
    "import im_v2.common.universe.universe as imvcounun"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "60c86a78",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:23.401338Z",
     "start_time": "2024-02-28T15:04:22.692941Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\u001b[0m\u001b[36mINFO\u001b[0m: > cmd='/venv/lib/python3.9/site-packages/ipykernel_launcher.py -f /home/.local/share/jupyter/runtime/kernel-3e7567f3-5a18-428f-9cdd-7b0ff6b8381d.json'\n",
      "WARNING: Running in Jupyter\n",
      "\u001b[31m-----------------------------------------------------------------------------\n",
      "This code is not in sync with the container:\n",
      "code_version='1.13.0' != container_version='1.12.0'\n",
      "-----------------------------------------------------------------------------\n",
      "You need to:\n",
      "- merge origin/master into your branch with `invoke git_merge_master`\n",
      "- pull the latest container with `invoke docker_pull`\u001b[0m\n",
      "INFO  # Git\n",
      "  branch_name='CmampTask7396_Add_QA_for_realtime_resampled_1_min_bidask_data_collection'\n",
      "  hash='2f298ee1d'\n",
      "  # Last commits:\n",
      "    * 2f298ee1d Sonya Nikiforova CmTask7274: Add backticks to multistring finding function (#7391) (  23 hours ago) Tue Feb 27 16:18:09 2024  (HEAD -> CmampTask7396_Add_QA_for_realtime_resampled_1_min_bidask_data_collection, origin/master, origin/HEAD, origin/CmampTask7396_Add_QA_for_realtime_resampled_1_min_bidask_data_collection, master)\n",
      "    * 677264b53 Juraj Smeriga CmampTask7304_Update_schema_of_ccx_bid_ask_futures_resampled_1min_table (#7388) (  24 hours ago) Tue Feb 27 15:21:35 2024           \n",
      "    * 21fbf6c1b Vlad     CmampTask7280_aws_CLI_doesn_t_work_in_Sorrentum_dev_tools_ARM_container (#7306) (  24 hours ago) Tue Feb 27 14:52:48 2024           \n",
      "# Machine info\n",
      "  system=Linux\n",
      "  node name=bca4000c3cab\n",
      "  release=5.15.0-1053-aws\n",
      "  version=#58~20.04.1-Ubuntu SMP Mon Jan 22 17:15:01 UTC 2024\n",
      "  machine=x86_64\n",
      "  processor=x86_64\n",
      "  cpu count=8\n",
      "  cpu freq=scpufreq(current=2499.992, min=0.0, max=0.0)\n",
      "  memory=svmem(total=33280278528, available=13891395584, percent=58.3, used=18907701248, free=4536688640, active=5757689856, inactive=20084621312, buffers=1022566400, cached=8813322240, shared=6438912, slab=2410971136)\n",
      "  disk usage=sdiskusage(total=218506772480, used=131902717952, free=86587277312, percent=60.4)\n",
      "# Packages\n",
      "  python: 3.9.5\n",
      "  cvxopt: 1.3.2\n",
      "  cvxpy: 1.4.2\n",
      "  gluonnlp: ?\n",
      "  gluonts: ?\n",
      "  joblib: 1.3.2\n",
      "  mxnet: ?\n",
      "  numpy: 1.26.0\n",
      "  pandas: 2.1.1\n",
      "  pyarrow: 14.0.2\n",
      "  scipy: 1.11.3\n",
      "  seaborn: 0.13.0\n",
      "  sklearn: 1.3.1\n",
      "  statsmodels: 0.14.0\n"
     ]
    }
   ],
   "source": [
    "hdbg.init_logger(verbosity=logging.INFO)\n",
    "\n",
    "_LOG = logging.getLogger(__name__)\n",
    "\n",
    "_LOG.info(\"%s\", henv.get_system_signature()[0])\n",
    "\n",
    "hprint.config_notebook()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "32161801",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:23.419541Z",
     "start_time": "2024-02-28T15:04:23.406008Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "WARNING Environment variable 'CK_DATA_RECONCILIATION_CONFIG' not defined: no config retrieved\n",
      "stage: preprod\n",
      "start_timestamp: 2024-02-28T14:40:00+00:00\n",
      "end_timestamp: 2024-02-28T14:50:00+00:00\n",
      "aws_profile: ck\n",
      "dataset_signature: realtime.airflow.downloaded_200ms.postgres.bid_ask.futures.v7_3.ccxt.binance.v1_0_0\n",
      "bid_ask_accuracy: 1\n",
      "data_type: bid_ask\n",
      "bid_ask_depth: 1\n",
      "bid_ask_frequency_sec: 60S\n"
     ]
    }
   ],
   "source": [
    "env_var_name = \"CK_DATA_RECONCILIATION_CONFIG\"\n",
    "config = cconfig.Config.from_env_var(env_var_name)\n",
    "if config:\n",
    "    config = config.to_dict()\n",
    "    # bid_ask_accuracy needs to be cast to int if its defined\n",
    "    config[\"bid_ask_accuracy\"] = (\n",
    "        int(config[\"bid_ask_accuracy\"]) if config[\"bid_ask_accuracy\"] else None\n",
    "    )\n",
    "    # Get config from env when running the notebook via the `run_notebook.py`\n",
    "    # script, e.g., in the system reconciliation flow.\n",
    "    _LOG.info(\"Using config from env vars\")\n",
    "else:\n",
    "    config_dict = {\n",
    "        \"stage\": \"preprod\",\n",
    "        \"start_timestamp\": \"2024-02-28T14:40:00+00:00\",\n",
    "        \"end_timestamp\": \"2024-02-28T14:50:00+00:00\",\n",
    "        \"aws_profile\": \"ck\",\n",
    "        \"dataset_signature\": \"realtime.airflow.downloaded_200ms.postgres.bid_ask.futures.v7_3.ccxt.binance.v1_0_0\",\n",
    "        \"bid_ask_accuracy\": 1,\n",
    "        \"data_type\": \"bid_ask\",\n",
    "        \"bid_ask_depth\": 1,\n",
    "        \"bid_ask_frequency_sec\": \"60S\",\n",
    "    }\n",
    "    config = cconfig.Config.from_dict(config_dict)\n",
    "print(config)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "5159f8ab",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:23.515737Z",
     "start_time": "2024-02-28T15:04:23.423529Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO  Loading dataset schema file: /app/data_schema/dataset_schema_versions/dataset_schema_v3.json\n",
      "INFO  Loaded dataset schema version v3\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset_schema = dsdascut.get_dataset_schema()\n",
    "dsdascut.validate_dataset_signature(config[\"dataset_signature\"], dataset_schema)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "272e757f",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:23.525824Z",
     "start_time": "2024-02-28T15:04:23.519138Z"
    },
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'download_mode': 'realtime',\n",
       " 'downloading_entity': 'airflow',\n",
       " 'action_tag': 'downloaded_200ms',\n",
       " 'data_format': 'postgres',\n",
       " 'data_type': 'bid_ask',\n",
       " 'asset_type': 'futures',\n",
       " 'universe': 'v7_3',\n",
       " 'vendor': 'ccxt',\n",
       " 'exchange_id': 'binance',\n",
       " 'version': 'v1_0_0'}"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dataset_signature_as_dict = dsdascut.parse_dataset_signature_to_args(\n",
    "    config[\"dataset_signature\"], dataset_schema\n",
    ")\n",
    "dataset_signature_as_dict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "bff5b131",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:23.732435Z",
     "start_time": "2024-02-28T15:04:23.528663Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO  Loading dataset schema file: /app/data_schema/dataset_schema_versions/dataset_schema_v3.json\n",
      "INFO  Loaded dataset schema version v3\n",
      "INFO  Loading dataset schema file: /app/data_schema/dataset_schema_versions/dataset_schema_v3.json\n",
      "INFO  Loaded dataset schema version v3\n",
      "INFO  Unable to fetch DB credentials from environment variables: \n",
      "\t'POSTGRES_HOST'\n",
      "\tAttempting env file method.\n",
      "INFO  Unable to fetch DB credentials from env file: \n",
      "\t\n",
      "################################################################################\n",
      "* Failed assertion *\n",
      "File '/app/im_v2/devops/env/preprod.im_db_config.env' doesn't exist\n",
      "################################################################################\n",
      "\n",
      "\tAttempting AWS SecretsManager method.\n",
      "INFO  Fetching secret: preprod.im_data_db\n",
      "INFO  Created preprod DB connection: \n",
      " None\n",
      "INFO  Enabled connection to the `ccxt_bid_ask_futures_raw` DB table\n"
     ]
    }
   ],
   "source": [
    "raw_data_client = imvcdcimrdc.RawDataReader(\n",
    "    config[\"dataset_signature\"], stage=config[\"stage\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "d7b5fd2c",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:25.630998Z",
     "start_time": "2024-02-28T15:04:23.735361Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO  Executing query: \n",
      "\tSELECT * FROM ccxt_bid_ask_futures_raw WHERE timestamp >= 1709131200000 AND timestamp <= 1709131800000 AND level IN (1) AND exchange_id = 'binance'\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/app/helpers/hsql_implementation.py:582: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
      "  df = pd.read_sql_query(query, connection)\n"
     ]
    }
   ],
   "source": [
    "data = raw_data_client.read_data(\n",
    "    pd.Timestamp(config[\"start_timestamp\"]),\n",
    "    pd.Timestamp(config[\"end_timestamp\"]),\n",
    "    bid_ask_levels=[i for i in range(1, config[\"bid_ask_depth\"] + 1)],\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "4c962fac",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:25.657550Z",
     "start_time": "2024-02-28T15:04:25.633694Z"
    }
   },
   "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>currency_pair</th>\n",
       "      <th>exchange_id</th>\n",
       "      <th>end_download_timestamp</th>\n",
       "      <th>knowledge_timestamp</th>\n",
       "      <th>bid_size_l1</th>\n",
       "      <th>bid_price_l1</th>\n",
       "      <th>ask_size_l1</th>\n",
       "      <th>ask_price_l1</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>timestamp</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>1709131200036</th>\n",
       "      <td>CTK_USDT</td>\n",
       "      <td>binance</td>\n",
       "      <td>2024-02-28 14:40:00.340435+00:00</td>\n",
       "      <td>2024-02-28 14:40:00.362887+00:00</td>\n",
       "      <td>612.0</td>\n",
       "      <td>0.8074</td>\n",
       "      <td>178.0</td>\n",
       "      <td>0.8077</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709131200042</th>\n",
       "      <td>GMT_USDT</td>\n",
       "      <td>binance</td>\n",
       "      <td>2024-02-28 14:40:00.328794+00:00</td>\n",
       "      <td>2024-02-28 14:40:00.362887+00:00</td>\n",
       "      <td>37734.0</td>\n",
       "      <td>0.2933</td>\n",
       "      <td>30330.0</td>\n",
       "      <td>0.2934</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709131200044</th>\n",
       "      <td>NEAR_USDT</td>\n",
       "      <td>binance</td>\n",
       "      <td>2024-02-28 14:40:00.335128+00:00</td>\n",
       "      <td>2024-02-28 14:40:00.362887+00:00</td>\n",
       "      <td>2431.0</td>\n",
       "      <td>3.9320</td>\n",
       "      <td>1594.0</td>\n",
       "      <td>3.9330</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709131200045</th>\n",
       "      <td>DYDX_USDT</td>\n",
       "      <td>binance</td>\n",
       "      <td>2024-02-28 14:40:00.331477+00:00</td>\n",
       "      <td>2024-02-28 14:40:00.362887+00:00</td>\n",
       "      <td>788.5</td>\n",
       "      <td>3.5530</td>\n",
       "      <td>47.6</td>\n",
       "      <td>3.5540</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1709131200055</th>\n",
       "      <td>MATIC_USDT</td>\n",
       "      <td>binance</td>\n",
       "      <td>2024-02-28 14:40:00.331131+00:00</td>\n",
       "      <td>2024-02-28 14:40:00.362887+00:00</td>\n",
       "      <td>1624.0</td>\n",
       "      <td>1.0422</td>\n",
       "      <td>2642.0</td>\n",
       "      <td>1.0423</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              currency_pair exchange_id           end_download_timestamp              knowledge_timestamp  bid_size_l1  bid_price_l1  ask_size_l1  ask_price_l1\n",
       "timestamp                                                                                                                                                      \n",
       "1709131200036      CTK_USDT     binance 2024-02-28 14:40:00.340435+00:00 2024-02-28 14:40:00.362887+00:00        612.0        0.8074        178.0        0.8077\n",
       "1709131200042      GMT_USDT     binance 2024-02-28 14:40:00.328794+00:00 2024-02-28 14:40:00.362887+00:00      37734.0        0.2933      30330.0        0.2934\n",
       "1709131200044     NEAR_USDT     binance 2024-02-28 14:40:00.335128+00:00 2024-02-28 14:40:00.362887+00:00       2431.0        3.9320       1594.0        3.9330\n",
       "1709131200045     DYDX_USDT     binance 2024-02-28 14:40:00.331477+00:00 2024-02-28 14:40:00.362887+00:00        788.5        3.5530         47.6        3.5540\n",
       "1709131200055    MATIC_USDT     binance 2024-02-28 14:40:00.331131+00:00 2024-02-28 14:40:00.362887+00:00       1624.0        1.0422       2642.0        1.0423"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "14578edc",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:25.668929Z",
     "start_time": "2024-02-28T15:04:25.662676Z"
    }
   },
   "outputs": [],
   "source": [
    "# TODO(Juraj): this behavior should be encapsulated in some utility function\n",
    "data_frequency = \"T\" if \"1min\" in dataset_signature_as_dict[\"action_tag\"] else \"S\"\n",
    "vendor_name = dataset_signature_as_dict[\"vendor\"].upper()\n",
    "mode = \"download\"\n",
    "version = dataset_signature_as_dict[\"universe\"].replace(\"_\", \".\")\n",
    "exchange_id = dataset_signature_as_dict[\"exchange_id\"]\n",
    "universe = imvcounun.get_vendor_universe(vendor_name, mode, version=version)\n",
    "universe_list = universe[exchange_id]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "8383710b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:25.676617Z",
     "start_time": "2024-02-28T15:04:25.671522Z"
    }
   },
   "outputs": [],
   "source": [
    "qa_check_list = [\n",
    "    imvcdqqach.NaNChecks(),\n",
    "    imvcdqqach.FullUniversePresentCheck(universe_list),\n",
    "    imvcdqqach.GapsInTimeIntervalBySymbolsCheck(\n",
    "        config[\"start_timestamp\"],\n",
    "        config[\"end_timestamp\"],\n",
    "        config[\"bid_ask_frequency_sec\"],\n",
    "        align=True,\n",
    "    ),\n",
    "]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "dfca7ba1",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:25.682789Z",
     "start_time": "2024-02-28T15:04:25.679253Z"
    }
   },
   "outputs": [],
   "source": [
    "dataset_validator = imvcdqdava.DataFrameDatasetValidator(qa_check_list)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "02d9e30b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:26.432646Z",
     "start_time": "2024-02-28T15:04:25.684919Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "INFO  Running all QA checks:\n",
      "INFO  \tNaNChecks: PASSED\n",
      "INFO  \tFullUniversePresentCheck: PASSED\n",
      "INFO  \tGapsInTimeIntervalBySymbolsCheck: PASSED\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    dataset_validator.run_all_checks([data])\n",
    "except Exception as e:\n",
    "    # Pass information about success or failure of the QA\n",
    "    #  back to the task that invoked it.\n",
    "    data_qa_outcome = str(e)\n",
    "    raise e\n",
    "# If no exception was raised mark the QA as successful.\n",
    "data_qa_outcome = \"SUCCESS\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "bde5959b",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-02-28T15:04:26.439442Z",
     "start_time": "2024-02-28T15:04:26.435640Z"
    }
   },
   "outputs": [],
   "source": [
    "# This can be read by the invoke task to find out if QA was successful.\n",
    "hio.to_file(\"/app/ck_data_reconciliation_outcome.txt\", data_qa_outcome)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1a10b8d7",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}