spx_1min_historical.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import pandas as pd\n",
    "import json\n",
    "import mysql.connector\n",
    "from datetime import datetime, timedelta\n",
    "import time\n",
    "import nvda_config\n",
    "\n",
    "import twelvedata\n",
    "from twelvedata import TDClient\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# set up login\n",
    "Cpassword = nvda_config.mysql_password\n",
    "td = TDClient(apikey=nvda_config.twelve_api_nvda) \n",
    "base_url = 'https://api.twelvedata.com'\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Establish MySQL object\n",
    "mydb = mysql.connector.connect(\n",
    "    host=\"localhost\",   \n",
    "    user=\"root\",         \n",
    "    password=Cpassword,  \n",
    "    database=\"nvdav1\"   \n",
    ")\n",
    "\n",
    "mycursor = mydb.cursor()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-06-24 21:38:41.433292\n",
      "2023-06-29 21:38:41.433292\n",
      "2023-07-04 21:38:41.433292\n",
      "2023-07-09 21:38:41.433292\n",
      "2023-07-14 21:38:41.433292\n",
      "2023-07-19 21:38:41.433292\n",
      "2023-07-24 21:38:41.433292\n",
      "2023-07-29 21:38:41.433292\n",
      "2023-08-03 21:38:41.433292\n",
      "2023-08-08 21:38:41.433292\n",
      "2023-08-13 21:38:41.433292\n",
      "2023-08-18 21:38:41.433292\n",
      "2023-08-23 21:38:41.433292\n",
      "2023-08-28 21:38:41.433292\n",
      "2023-09-02 21:38:41.433292\n",
      "2023-09-07 21:38:41.433292\n",
      "2023-09-12 21:38:41.433292\n",
      "2023-09-17 21:38:41.433292\n",
      "2023-09-22 21:38:41.433292\n",
      "2023-09-27 21:38:41.433292\n",
      "2023-10-02 21:38:41.433292\n",
      "2023-10-07 21:38:41.433292\n",
      "2023-10-12 21:38:41.433292\n",
      "2023-10-17 21:38:41.433292\n",
      "2023-10-22 21:38:41.433292\n",
      "2023-10-27 21:38:41.433292\n",
      "2023-11-01 21:38:41.433292\n",
      "2023-11-06 21:38:41.433292\n",
      "2023-11-11 21:38:41.433292\n",
      "2023-11-16 21:38:41.433292\n",
      "2023-11-21 21:38:41.433292\n",
      "2023-11-26 21:38:41.433292\n",
      "2023-12-01 21:38:41.433292\n",
      "2023-12-06 21:38:41.433292\n",
      "2023-12-11 21:38:41.433292\n",
      "2023-12-16 21:38:41.433292\n",
      "2023-12-21 21:38:41.433292\n",
      "2023-12-26 21:38:41.433292\n",
      "2023-12-31 21:38:41.433292\n",
      "2024-01-05 21:38:41.433292\n",
      "2024-01-10 21:38:41.433292\n",
      "2024-01-15 21:38:41.433292\n",
      "2024-01-20 21:38:41.433292\n",
      "2024-01-25 21:38:41.433292\n",
      "2024-01-30 21:38:41.433292\n",
      "2024-02-04 21:38:41.433292\n",
      "2024-02-09 21:38:41.433292\n",
      "2024-02-14 21:38:41.433292\n",
      "2024-02-19 21:38:41.433292\n",
      "2024-02-24 21:38:41.433292\n",
      "2024-02-29 21:38:41.433292\n",
      "2024-03-05 21:38:41.433292\n",
      "2024-03-10 21:38:41.433292\n",
      "2024-03-15 21:38:41.433292\n",
      "2024-03-20 21:38:41.433292\n",
      "2024-03-25 21:38:41.433292\n",
      "2024-03-30 21:38:41.433292\n",
      "2024-04-04 21:38:41.433292\n",
      "2024-04-09 21:38:41.433292\n",
      "2024-04-14 21:38:41.433292\n",
      "2024-04-19 21:38:41.433292\n",
      "2024-04-24 21:38:41.433292\n",
      "2024-04-29 21:38:41.433292\n",
      "2024-05-04 21:38:41.433292\n",
      "2024-05-09 21:38:41.433292\n",
      "2024-05-14 21:38:41.433292\n",
      "2024-05-19 21:38:41.433292\n",
      "2024-05-24 21:38:41.433292\n",
      "2024-05-29 21:38:41.433292\n",
      "2024-06-03 21:38:41.433292\n",
      "2024-06-08 21:38:41.433292\n",
      "2024-06-13 21:38:41.433292\n",
      "2024-06-18 21:38:41.433292\n",
      "2024-06-23 21:38:41.433292\n",
      "2024-06-28 21:38:41.433292\n",
      "2024-07-03 21:38:41.433292\n",
      "2024-07-08 21:38:41.433292\n",
      "2024-07-13 21:38:41.433292\n",
      "2024-07-18 21:38:41.433292\n",
      "2024-07-23 21:38:41.433292\n",
      "2024-07-28 21:38:41.433292\n",
      "2024-08-02 21:38:41.433292\n",
      "2024-08-07 21:38:41.433292\n",
      "2024-08-12 21:38:41.433292\n"
     ]
    }
   ],
   "source": [
    "# Date range for historical data (funky start date due to downloading first month as test batch)\n",
    "end_date = datetime.now()\n",
    "start_date = end_date - timedelta(days=420)  \n",
    "\n",
    "# Twelve Data API parameters\n",
    "symbol = \"SPX\"\n",
    "interval = \"1min\"\n",
    "outputsize = 5000  \n",
    "parent_df = pd.DataFrame()  # Initialize an empty parent DataFrame\n",
    "\n",
    "# Increment through the time range in start_date and end_date by 5 days every 30 seconds\n",
    "while start_date < end_date:\n",
    "    time_series = td.time_series(\n",
    "        symbol=symbol,\n",
    "        interval=interval,\n",
    "        outputsize=outputsize,\n",
    "        start_date=start_date.strftime(\"%Y-%m-%d\"),\n",
    "        end_date=(start_date + timedelta(days=5)).strftime(\"%Y-%m-%d\"),\n",
    "    ).with_macd().with_ema().with_rsi()\n",
    "    \n",
    "    # Convert to pandas Dataframes\n",
    "    time_series_df = time_series.as_pandas()\n",
    "    time_series_df = time_series_df.astype(str)\n",
    "    \n",
    "    # Concatenate the current time series DataFrame to the parent DataFrame\n",
    "    parent_df = pd.concat([parent_df, time_series_df], ignore_index=False)\n",
    " \n",
    "    # Increment the start_date\n",
    "    start_date += timedelta(days=5)\n",
    "    \n",
    "    # Sleep for 30 seconds\n",
    "    time.sleep(6)\n",
    "    print(start_date)\n",
    "    \n",
    "\n",
    "# Print the parent DataFrame to a CSV file\n",
    "parent_df.to_csv('spx_historical_data.csv', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           open        high         low       close    volume  \\\n",
      "datetime                                                                        \n",
      "2023-06-23 15:59:00   4348.1001  4349.87012  4346.47021  4348.22998  10919282   \n",
      "2023-06-23 15:58:00  4349.18018  4349.81006  4348.22021  4348.27979  21610054   \n",
      "2023-06-23 15:57:00  4350.29004  4350.33984  4348.77002   4349.1499  21866656   \n",
      "2023-06-23 15:56:00  4353.83008  4354.16016  4350.20996  4350.20996  22957025   \n",
      "2023-06-23 15:55:00   4350.6499  4354.14014   4350.6499  4353.68994  23516091   \n",
      "...                         ...         ...         ...         ...       ...   \n",
      "2024-08-07 09:34:00   5300.9502  5303.43994  5299.00977   5303.2998  10037694   \n",
      "2024-08-07 09:33:00  5300.95996  5301.29004  5298.58008  5301.16992  12088593   \n",
      "2024-08-07 09:32:00   5303.2998  5306.89014  5301.14014  5301.14014  12627988   \n",
      "2024-08-07 09:31:00  5302.22998   5304.6001   5300.8999  5303.27002  14620887   \n",
      "2024-08-07 09:30:00  5293.12988  5302.33984  5293.12988  5301.97998  41650283   \n",
      "\n",
      "                         macd macd_signal macd_hist         ema       rsi  \n",
      "datetime                                                                   \n",
      "2023-06-23 15:59:00  -0.15875     0.39622  -0.55497  4350.19946  43.56657  \n",
      "2023-06-23 15:58:00   0.05827     0.53496  -0.47669  4350.69184  43.67484  \n",
      "2023-06-23 15:57:00   0.32936     0.65413  -0.32478  4351.29485  45.50968  \n",
      "2023-06-23 15:56:00   0.58197     0.73533  -0.15336  4351.83109  47.78051  \n",
      "2023-06-23 15:55:00     0.788     0.77367   0.01433  4352.23637  56.35192  \n",
      "...                       ...         ...       ...         ...       ...  \n",
      "2024-08-07 09:34:00   5.88956    -1.23643   7.12598  5284.01988  68.67265  \n",
      "2024-08-07 09:33:00   3.93879    -3.01792   6.95671   5279.1999   67.8743  \n",
      "2024-08-07 09:32:00   1.60847     -4.7571   6.36557   5273.7074  67.86366  \n",
      "2024-08-07 09:31:00  -1.38623    -6.34849   4.96226  5266.84921  69.38889  \n",
      "2024-08-07 09:30:00  -5.40525    -7.58906   2.18381  5257.74401    68.997  \n",
      "\n",
      "[111691 rows x 10 columns]\n"
     ]
    }
   ],
   "source": [
    "print(parent_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}