{
  "nbformat": 4,
  "nbformat_minor": 5,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "name": "python",
      "version": "3.12"
    },
    "colab": {
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "# Your market data, ready to explore\n",
        "## Goal\n",
        "Load a SiftingIO OHLCV CSV, check its structure, plot closing prices and calculate returns across the observed bars.\n",
        "\n",
        "**Start here:** choose **Runtime \u2192 Run all** in Google Colab. The default is a small, clearly labelled synthetic demo. To use your own data, set `USE_DEMO = False`, run again and upload one CSV from the [Market Data Export tool](https://sifting.io/tools/market-data-export).\n",
        "\n",
        "No API key is needed. Uploading your CSV places it in your Google Colab session; use data you are comfortable uploading to Google. Download the notebook first, then use **File \u2192 Upload notebook** at [colab.research.google.com](https://colab.research.google.com/).\n"
      ],
      "id": "siftingio-00"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Setup\n",
        "Colab includes pandas and matplotlib. For local Jupyter use, install `pandas matplotlib`, set `CSV_PATH` to your export, and disable the demo. Each file should contain one instrument and one interval. This notebook accepts the export headers `Time (UTC), Timestamp (ms), Open, High, Low, Close, Volume`.\n"
      ],
      "id": "siftingio-01"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "from io import StringIO\n",
        "from pathlib import Path\n",
        "import numpy as np\n",
        "import pandas as pd\n",
        "import matplotlib.pyplot as plt\n",
        "from IPython.display import display\n",
        "\n",
        "USE_DEMO = True\n",
        "CSV_PATH = \"\"  # Local Jupyter: path to a SiftingIO export CSV.\n",
        "INSTRUMENT_LABEL = \"My instrument\"  # CSV exports do not include the symbol.\n"
      ],
      "execution_count": null,
      "outputs": [],
      "id": "siftingio-02"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 1. Load your data\n",
        "The demo prices below are synthetic, not market observations. Replace them by uploading a CSV exported from SiftingIO. Volume can be blank; a blank is kept distinct from zero.\n"
      ],
      "id": "siftingio-03"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "if USE_DEMO:\n",
        "    csv_source = StringIO(\"\"\"Time (UTC),Timestamp (ms),Open,High,Low,Close,Volume\n",
        "2026-10-01T00:00:00Z,1790812800000,100,104,99,103,12.5\n",
        "2026-10-01T01:00:00Z,1790816400000,103,105,101,102,\n",
        "2026-10-01T02:00:00Z,1790820000000,102,107,101,106,0\n",
        "2026-10-01T03:00:00Z,1790823600000,106,108,104,105,15\n",
        "2026-10-01T04:00:00Z,1790827200000,105,110,104,109,18\n",
        "2026-10-01T05:00:00Z,1790830800000,109,112,108,111,20\n",
        "\"\"\")\n",
        "    source_label = \"Synthetic demo \u2014 not market data\"\n",
        "    instrument_label = \"Synthetic example\"\n",
        "elif CSV_PATH:\n",
        "    csv_source = Path(CSV_PATH)\n",
        "    source_label = csv_source.name\n",
        "    instrument_label = INSTRUMENT_LABEL\n",
        "else:\n",
        "    from google.colab import files\n",
        "    uploads = files.upload()\n",
        "    if len(uploads) != 1:\n",
        "        raise ValueError(\"Upload exactly one OHLCV CSV, then rerun this cell.\")\n",
        "    source_label, content = next(iter(uploads.items()))\n",
        "    csv_source = StringIO(content.decode(\"utf-8-sig\"))\n",
        "    instrument_label = INSTRUMENT_LABEL\n",
        "raw = pd.read_csv(csv_source, encoding=\"utf-8-sig\")\n",
        "raw.columns = raw.columns.str.strip().str.lower()\n",
        "print(f\"Source: {source_label}; {len(raw):,} rows\")\n",
        "display(raw.head())\n"
      ],
      "execution_count": null,
      "outputs": [],
      "id": "siftingio-04"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 2. Check before calculating\n",
        "Checks cover timestamps, finite OHLC values, duplicate bars and price ranges. Invalid rows stop the analysis rather than being silently removed. Sorting makes the calculation chronological; missing volume stays missing. Time gaps may reflect market closures or missing data and are reported without filling prices.\n"
      ],
      "id": "siftingio-05"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "required = {\"open\", \"high\", \"low\", \"close\"}\n",
        "if not required.issubset(raw.columns):\n",
        "    raise ValueError(f\"Missing columns: {sorted(required - set(raw.columns))}\")\n",
        "if not len(raw):\n",
        "    raise ValueError(\"The CSV is empty. Export a populated dataset.\")\n",
        "data = raw.copy()\n",
        "if \"time (utc)\" in data:\n",
        "    data[\"time\"] = pd.to_datetime(data[\"time (utc)\"], utc=True, errors=\"coerce\")\n",
        "elif \"timestamp (ms)\" in data:\n",
        "    data[\"time\"] = pd.to_datetime(pd.to_numeric(data[\"timestamp (ms)\"], errors=\"coerce\"), unit=\"ms\", utc=True, errors=\"coerce\")\n",
        "else:\n",
        "    raise ValueError(\"Expected Time (UTC) or Timestamp (ms).\")\n",
        "for field in [\"open\", \"high\", \"low\", \"close\", \"volume\"]:\n",
        "    if field in data:\n",
        "        data[field] = pd.to_numeric(data[field], errors=\"coerce\")\n",
        "checks = {\n",
        "    \"Invalid timestamps\": int(data[\"time\"].isna().sum()),\n",
        "    \"Duplicate timestamps\": int(data[\"time\"].duplicated().sum()),\n",
        "    \"Missing or non-finite OHLC rows\": int((~np.isfinite(data[[\"open\", \"high\", \"low\", \"close\"]]).all(axis=1)).sum()),\n",
        "    \"Inconsistent OHLC ranges\": int(((data[\"high\"] < data[[\"open\", \"close\", \"low\"]].max(axis=1)) | (data[\"low\"] > data[[\"open\", \"close\", \"high\"]].min(axis=1))).sum()),\n",
        "}\n",
        "if \"time (utc)\" in data and \"timestamp (ms)\" in data:\n",
        "    millis = pd.to_datetime(pd.to_numeric(data[\"timestamp (ms)\"], errors=\"coerce\"), unit=\"ms\", utc=True, errors=\"coerce\")\n",
        "    checks[\"Mismatched timestamp columns\"] = int((millis.isna() | (millis != data[\"time\"])).sum())\n",
        "display(pd.Series(checks, name=\"Rows flagged\").to_frame())\n",
        "if any(checks.values()):\n",
        "    raise ValueError(\"Resolve the flagged rows in your source CSV before proceeding.\")\n",
        "data = data.sort_values(\"time\").reset_index(drop=True)\n",
        "print(f\"UTC window: {data.time.iloc[0]} \u2192 {data.time.iloc[-1]}\")\n",
        "if \"volume\" in data:\n",
        "    print(f\"Missing volume: {data.volume.isna().sum():,} rows (not replaced by zero)\")\n",
        "print(\"Most common spacing between observed bars:\")\n",
        "display(data.time.diff().dropna().value_counts().head(5).rename(\"Count\").to_frame())\n"
      ],
      "execution_count": null,
      "outputs": [],
      "id": "siftingio-06"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 3. Calculate observed-bar returns\n",
        "Return = current close / previous observed close \u2212 1. Cumulative change compares each close to the first close. These are price changes across available bars, not a strategy backtest or a total return. No annualization is applied. Stock prices may be as-traded; splits and dividends can affect interpretation. Volume units depend on the market/source. Non-positive closes stop percentage-return analysis.\n"
      ],
      "id": "siftingio-07"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "if len(data) < 2:\n",
        "    raise ValueError(\"Use at least two bars for return analysis.\")\n",
        "if (data.close <= 0).any():\n",
        "    raise ValueError(\"Percentage-return analysis requires positive closing prices.\")\n",
        "data[\"return_pct\"] = data.close.pct_change(fill_method=None) * 100\n",
        "data[\"cumulative_pct\"] = (data.close / data.close.iloc[0] - 1) * 100\n",
        "summary = pd.Series({\n",
        "    \"Bars\": len(data),\n",
        "    \"First close\": data.close.iloc[0],\n",
        "    \"Last close\": data.close.iloc[-1],\n",
        "    \"Close-to-close change (%)\": data.cumulative_pct.iloc[-1],\n",
        "    \"Lowest low\": data.low.min(),\n",
        "    \"Highest high\": data.high.max(),\n",
        "}, name=instrument_label)\n",
        "display(summary.to_frame())\n",
        "display(data[[\"time\", \"close\", \"return_pct\", \"cumulative_pct\"]].tail(10))\n"
      ],
      "execution_count": null,
      "outputs": [],
      "id": "siftingio-08"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 4. See the price and cumulative change\n",
        "The lines connect available observations. They do not reconstruct prices during gaps. Price is in the instrument\u2019s quote currency; instruments are not converted into a common currency.\n"
      ],
      "id": "siftingio-09"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "fig, axes = plt.subplots(2, 1, figsize=(11, 7), sharex=True, constrained_layout=True)\n",
        "axes[0].plot(data.time, data.close, color=\"#227958\", linewidth=1.6)\n",
        "axes[0].set(title=f\"{instrument_label} \u2014 closing price\", ylabel=\"Price (quote currency)\")\n",
        "axes[1].plot(data.time, data.cumulative_pct, color=\"#227958\", linewidth=1.6)\n",
        "axes[1].axhline(0, color=\"gray\", linewidth=0.7)\n",
        "axes[1].set(ylabel=\"Change from first close (%)\", xlabel=\"Time (UTC)\")\n",
        "for axis in axes:\n",
        "    axis.grid(alpha=0.2)\n",
        "plt.show()\n"
      ],
      "execution_count": null,
      "outputs": [],
      "id": "siftingio-10"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## 5. Save your analysis\n",
        "The output CSV preserves the source fields and adds normalized UTC time, observed-bar return and cumulative change. It does not overwrite your uploaded data.\n"
      ],
      "id": "siftingio-11"
    },
    {
      "cell_type": "code",
      "metadata": {},
      "source": [
        "OUTPUT_FILE = \"siftingio-ohlcv-analysis.csv\"\n",
        "data.to_csv(OUTPUT_FILE, index=False)\n",
        "print(f\"Saved {OUTPUT_FILE}\")\n",
        "# In Colab, uncomment the following lines to download your result:\n",
        "# from google.colab import files\n",
        "# files.download(OUTPUT_FILE)\n"
      ],
      "execution_count": null,
      "outputs": [],
      "id": "siftingio-12"
    },
    {
      "cell_type": "markdown",
      "metadata": {},
      "source": [
        "## Next steps\n",
        "Try a different instrument or interval in the [export tool](https://sifting.io/tools/market-data-export), then rerun the notebook. Keep the symbol, market, interval and export window alongside your results. Browse the [symbol catalogue](https://sifting.io/symbols) for coverage and [data methodology](https://sifting.io/data-methodology) for source conventions.\n",
        "\n",
        "Want spreadsheet snapshots instead? Try the [Google Sheets watchlist](https://sifting.io/resources/google-sheets).\n"
      ],
      "id": "siftingio-13"
    }
  ]
}