aux_1min_historical.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "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": 2,
   "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": 3,
   "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": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2023-06-24 21:40:23.129211\n",
      "2023-06-29 21:40:23.129211\n",
      "2023-07-04 21:40:23.129211\n",
      "2023-07-09 21:40:23.129211\n",
      "2023-07-14 21:40:23.129211\n",
      "2023-07-19 21:40:23.129211\n",
      "2023-07-24 21:40:23.129211\n",
      "2023-07-29 21:40:23.129211\n",
      "2023-08-03 21:40:23.129211\n",
      "2023-08-08 21:40:23.129211\n",
      "2023-08-13 21:40:23.129211\n",
      "2023-08-18 21:40:23.129211\n",
      "2023-08-23 21:40:23.129211\n",
      "2023-08-28 21:40:23.129211\n",
      "2023-09-02 21:40:23.129211\n",
      "2023-09-07 21:40:23.129211\n",
      "2023-09-12 21:40:23.129211\n",
      "2023-09-17 21:40:23.129211\n",
      "2023-09-22 21:40:23.129211\n",
      "2023-09-27 21:40:23.129211\n",
      "2023-10-02 21:40:23.129211\n",
      "2023-10-07 21:40:23.129211\n",
      "2023-10-12 21:40:23.129211\n",
      "2023-10-17 21:40:23.129211\n",
      "2023-10-22 21:40:23.129211\n",
      "2023-10-27 21:40:23.129211\n",
      "2023-11-01 21:40:23.129211\n",
      "2023-11-06 21:40:23.129211\n",
      "2023-11-11 21:40:23.129211\n",
      "2023-11-16 21:40:23.129211\n",
      "2023-11-21 21:40:23.129211\n",
      "2023-11-26 21:40:23.129211\n",
      "2023-12-01 21:40:23.129211\n",
      "2023-12-06 21:40:23.129211\n",
      "2023-12-11 21:40:23.129211\n",
      "2023-12-16 21:40:23.129211\n",
      "2023-12-21 21:40:23.129211\n",
      "2023-12-26 21:40:23.129211\n",
      "2023-12-31 21:40:23.129211\n",
      "2024-01-05 21:40:23.129211\n",
      "2024-01-10 21:40:23.129211\n",
      "2024-01-15 21:40:23.129211\n",
      "2024-01-20 21:40:23.129211\n",
      "2024-01-25 21:40:23.129211\n",
      "2024-01-30 21:40:23.129211\n",
      "2024-02-04 21:40:23.129211\n",
      "2024-02-09 21:40:23.129211\n",
      "2024-02-14 21:40:23.129211\n",
      "2024-02-19 21:40:23.129211\n",
      "2024-02-24 21:40:23.129211\n",
      "2024-02-29 21:40:23.129211\n",
      "2024-03-05 21:40:23.129211\n",
      "2024-03-10 21:40:23.129211\n",
      "2024-03-15 21:40:23.129211\n",
      "2024-03-20 21:40:23.129211\n",
      "2024-03-25 21:40:23.129211\n",
      "2024-03-30 21:40:23.129211\n",
      "2024-04-04 21:40:23.129211\n",
      "2024-04-09 21:40:23.129211\n",
      "2024-04-14 21:40:23.129211\n",
      "2024-04-19 21:40:23.129211\n",
      "2024-04-24 21:40:23.129211\n",
      "2024-04-29 21:40:23.129211\n",
      "2024-05-04 21:40:23.129211\n",
      "2024-05-09 21:40:23.129211\n",
      "2024-05-14 21:40:23.129211\n",
      "2024-05-19 21:40:23.129211\n",
      "2024-05-24 21:40:23.129211\n",
      "2024-05-29 21:40:23.129211\n",
      "2024-06-03 21:40:23.129211\n",
      "2024-06-08 21:40:23.129211\n",
      "2024-06-13 21:40:23.129211\n",
      "2024-06-18 21:40:23.129211\n",
      "2024-06-23 21:40:23.129211\n",
      "2024-06-28 21:40:23.129211\n",
      "2024-07-03 21:40:23.129211\n",
      "2024-07-08 21:40:23.129211\n",
      "2024-07-13 21:40:23.129211\n",
      "2024-07-18 21:40:23.129211\n",
      "2024-07-23 21:40:23.129211\n",
      "2024-07-28 21:40:23.129211\n",
      "2024-08-02 21:40:23.129211\n",
      "2024-08-07 21:40:23.129211\n",
      "2024-08-12 21:40:23.129211\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 = \"XAU/USD\"\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('aux_historical_data.csv', index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                           open        high         low       close      macd  \\\n",
      "datetime                                                                        \n",
      "2023-06-24 00:00:00  1932.19995  1932.30005  1930.20996  1930.69995  -0.11952   \n",
      "2023-06-23 23:59:00  1933.68994     1933.88  1932.31995  1932.31995   0.17478   \n",
      "2023-06-23 23:58:00  1934.34998  1934.34998  1933.54004     1933.63   0.38292   \n",
      "2023-06-23 23:57:00  1935.45996  1935.68005  1934.33997  1934.33997   0.50962   \n",
      "2023-06-23 23:56:00  1936.06006  1936.79004  1935.44995  1935.44995   0.59164   \n",
      "...                         ...         ...         ...         ...       ...   \n",
      "2024-08-07 00:04:00  2397.23999  2400.68994  2397.23999   2399.6001   0.69845   \n",
      "2024-08-07 00:03:00  2397.59009  2397.73999  2396.97998   2397.1001    0.4176   \n",
      "2024-08-07 00:02:00  2396.72998  2397.31006  2396.27002  2397.30005   0.30762   \n",
      "2024-08-07 00:01:00  2396.44995  2397.53003  2395.88989  2396.56006    0.1424   \n",
      "2024-08-07 00:00:00  2395.36011  2396.77002  2394.81006  2396.52002    0.0048   \n",
      "\n",
      "                    macd_signal macd_hist         ema       rsi  \n",
      "datetime                                                         \n",
      "2023-06-24 00:00:00     0.32106  -0.44058  1933.45802  34.54976  \n",
      "2023-06-23 23:59:00     0.43121  -0.25643  1934.14753  40.68111  \n",
      "2023-06-23 23:58:00     0.49531   -0.1124  1934.60443  46.93583  \n",
      "2023-06-23 23:57:00     0.52341  -0.01379  1934.84803   50.8718  \n",
      "2023-06-23 23:56:00     0.52686   0.06478  1934.97505  57.92361  \n",
      "...                         ...       ...         ...       ...  \n",
      "2024-08-07 00:04:00     0.22692   0.47153  2396.65743  65.95524  \n",
      "2024-08-07 00:03:00     0.10904   0.30857  2395.92176  58.29179  \n",
      "2024-08-07 00:02:00      0.0319   0.27572  2395.62717  59.28285  \n",
      "2024-08-07 00:01:00    -0.03703   0.17944  2395.20895  56.75626  \n",
      "2024-08-07 00:00:00    -0.08189   0.08669  2394.87118  56.62102  \n",
      "\n",
      "[378404 rows x 9 columns]\n"
     ]
    }
   ],
   "source": [
    "print(parent_df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "parent_df.to_csv('aux_historical_data.csv', index=True)\n"
   ]
  }
 ],
 "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
}