sma.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "\n",
    "os.chdir('/Users/kelvin/Desktop/citadel datathon/')\n",
    "\n",
    "acs =            pd.read_csv('data/acs_5yr_est_selected_economic_characteristics_2010-2022.csv')\n",
    "commodities =    pd.read_csv('data/all_commodities.csv')\n",
    "s_and_e =        pd.read_csv('data/all_stocks_and_etfs.csv')\n",
    "e_consumption =  pd.read_csv('data/Energy Data - Consumption.csv')\n",
    "e_emissions =    pd.read_csv('data/Energy Data - Emissions.csv')\n",
    "e_goods =        pd.read_csv('data/Energy Data - Imports & Exports.csv')\n",
    "e_prod =         pd.read_csv('data/Energy Data - Production.csv')\n",
    "e_stocks =       pd.read_csv('data/Energy Data - Stocks.csv')\n",
    "transportation = pd.read_csv('data/monthly_transportation_statistics.csv')\n",
    "stock_desc =     pd.read_csv('data/stock_descriptions.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "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>Date-Time</th>\n",
       "      <th>Open</th>\n",
       "      <th>High</th>\n",
       "      <th>Low</th>\n",
       "      <th>Close</th>\n",
       "      <th>Volume</th>\n",
       "      <th>Ticker_Symbol</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-01-25</td>\n",
       "      <td>37.08</td>\n",
       "      <td>37.2481</td>\n",
       "      <td>35.9997</td>\n",
       "      <td>37.00</td>\n",
       "      <td>11492073</td>\n",
       "      <td>HAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-01-24</td>\n",
       "      <td>35.68</td>\n",
       "      <td>36.9500</td>\n",
       "      <td>35.4838</td>\n",
       "      <td>36.83</td>\n",
       "      <td>14397273</td>\n",
       "      <td>HAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-01-23</td>\n",
       "      <td>34.48</td>\n",
       "      <td>35.6900</td>\n",
       "      <td>33.9400</td>\n",
       "      <td>35.30</td>\n",
       "      <td>15096866</td>\n",
       "      <td>HAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-01-22</td>\n",
       "      <td>33.88</td>\n",
       "      <td>34.6500</td>\n",
       "      <td>33.7750</td>\n",
       "      <td>34.43</td>\n",
       "      <td>11147116</td>\n",
       "      <td>HAL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-01-19</td>\n",
       "      <td>33.43</td>\n",
       "      <td>33.9800</td>\n",
       "      <td>33.3200</td>\n",
       "      <td>33.97</td>\n",
       "      <td>8908991</td>\n",
       "      <td>HAL</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Date-Time   Open     High      Low  Close    Volume Ticker_Symbol\n",
       "0  2024-01-25  37.08  37.2481  35.9997  37.00  11492073           HAL\n",
       "1  2024-01-24  35.68  36.9500  35.4838  36.83  14397273           HAL\n",
       "2  2024-01-23  34.48  35.6900  33.9400  35.30  15096866           HAL\n",
       "3  2024-01-22  33.88  34.6500  33.7750  34.43  11147116           HAL\n",
       "4  2024-01-19  33.43  33.9800  33.3200  33.97   8908991           HAL"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "# display(acs.head())\n",
    "# display(commodities.head())\n",
    "display(s_and_e.head())\n",
    "# display(e_consumption.head())\n",
    "# display(e_emissions.head())\n",
    "# display(e_goods.head())\n",
    "# display(e_prod.head())\n",
    "# display(e_stocks.head())\n",
    "# display(transportation.head())\n",
    "# display(stock_desc.head())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ONEQ split adjustment\n",
    "s_and_e['Date-Time'] = pd.to_datetime(s_and_e['Date-Time'])\n",
    "split_day = pd.to_datetime('2021-04-08')\n",
    "s_and_e.loc[(s_and_e['Date-Time'] >= split_day) &\n",
    "            (s_and_e['Ticker_Symbol'] == 'ONEQ'),\n",
    "            ['Open', 'High', 'Low', 'Close']] *= 10"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_rows', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<style>\n",
       ".output_scroll {\n",
       "    height: auto !important;\n",
       "    max-height: 100px; /* Adjust this value as needed */\n",
       "}\n",
       "</style>\n"
      ],
      "text/plain": [
       "<IPython.core.display.HTML object>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "from IPython.display import display, HTML\n",
    "\n",
    "display(HTML(\"\"\"\n",
    "<style>\n",
    ".output_scroll {\n",
    "    height: auto !important;\n",
    "    max-height: 100px; /* Adjust this value as needed */\n",
    "}\n",
    "</style>\n",
    "\"\"\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "def plot_moving_average(result, ticker_symbol):\n",
    "    # Set the 'Date-Time' column as the index\n",
    "    result.set_index('Date-Time', inplace=True)\n",
    "    \n",
    "    # Plotting the 'Close' price and the 'Moving_Avg'\n",
    "    plt.figure(figsize=(14,7))  # Set the figure size as needed\n",
    "    plt.plot(result['Close'], label='Close Price')\n",
    "    plt.plot(result['Moving_Avg'], label='Moving Average', linewidth=2)\n",
    "    \n",
    "    # Title and labels\n",
    "    plt.title('Moving Average of Close Prices Over Time for ' + ticker_symbol)\n",
    "    plt.xlabel('Date')\n",
    "    plt.ylabel('Price')\n",
    "    \n",
    "    # Legend\n",
    "    plt.legend()\n",
    "    \n",
    "    # Show the plot\n",
    "    plt.show()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "def compute_moving_average(df, ticker_symbol, window_size=60):\n",
    "    \"\"\"\n",
    "    Compute the moving average for the 'Close' prices of a given ticker symbol.\n",
    "\n",
    "    Parameters:\n",
    "    df (pandas.DataFrame): The DataFrame containing stock data.\n",
    "    ticker_symbol (str): The ticker symbol to filter the DataFrame.\n",
    "    window_size (int): The size of the window for the moving average.\n",
    "\n",
    "    Returns:\n",
    "    pandas.DataFrame: A DataFrame with 'Date-Time', 'Close', and 'Moving_Avg' for the ticker symbol.\n",
    "    \"\"\"\n",
    "    # Create a copy to avoid the SettingWithCopyWarning\n",
    "    ticker_df = df[df['Ticker_Symbol'] == ticker_symbol].copy()\n",
    "    \n",
    "    # Sort the dataframe based on date to ensure correct moving average calculation\n",
    "    ticker_df.sort_values('Date-Time', inplace=True)\n",
    "    \n",
    "    # Compute moving average of 'Close' prices\n",
    "    ticker_df['Moving_Avg'] = ticker_df['Close'].rolling(window=window_size).mean()\n",
    "    \n",
    "    ret = ticker_df[['Date-Time', 'Close', 'Moving_Avg']]\n",
    "    plot_moving_average(ticker_df[['Date-Time', 'Close', 'Moving_Avg']], ticker_symbol)\n",
    "    \n",
    "    return ret\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "# for ticker in s_and_e['Ticker_Symbol'].unique():\n",
    "#     compute_moving_average(s_and_e, ticker)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "pivoted_s_e = s_and_e.pivot(index='Date-Time', columns='Ticker_Symbol', values='Close')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Ticker_Symbol         BKR          BP          BPT         CEO         CHK  \\\n",
      "Ticker_Symbol                                                                \n",
      "BKR            712.229034  368.979882   454.028176  204.514061  278.569381   \n",
      "BP             368.979882  364.124293   242.054049  221.022496  242.905334   \n",
      "BPT            454.028176  242.054049  1071.851599  213.206602  512.903751   \n",
      "CEO            204.514061  221.022496   213.206602  528.455908    0.687782   \n",
      "CHK            278.569381  242.905334   512.903751    0.687782  592.162704   \n",
      "COP            477.193881  288.589343   302.439975  263.715136  315.659693   \n",
      "CVX            385.289181  246.314895   225.283095  218.616015  222.948007   \n",
      "DIA            189.057452  128.740222    81.524049  142.329744   89.924803   \n",
      "DVN            627.474100  343.631743   399.294776  321.456661  411.692003   \n",
      "E              320.009297  257.931050   203.746716  232.510750  189.645787   \n",
      "EOG            502.022870  306.365477   344.037988  295.430133  346.317333   \n",
      "EPD            266.031366  148.566517   180.914226  158.063973  149.074733   \n",
      "EXC            152.843250  118.022505    82.860451  124.593852   69.159042   \n",
      "FANG           595.639388  386.354710   526.921940  257.091956  378.073880   \n",
      "HAL            649.473833  350.430749   352.327703  328.271696  377.247481   \n",
      "HES            537.924772  331.400799   357.767620  323.693103  364.410095   \n",
      "KMI            322.217323  214.200353   281.572034  149.620226  212.146984   \n",
      "MPC            451.037829  276.142868   295.709387  202.625787  260.620409   \n",
      "NOV            629.098999  348.996437   369.887578  345.165697  344.501990   \n",
      "ONEQ           163.060417  124.552830    90.955447  158.623886  100.500972   \n",
      "OXY            607.589425  320.563099   341.380406  300.019515  369.859640   \n",
      "PSX            422.380250  253.949948   295.284792  164.921598  271.407582   \n",
      "SLB            581.241422  300.613500   294.762598  282.256941  328.172930   \n",
      "SPY            174.896561  132.327628    87.690822  154.047313   99.407870   \n",
      "VLO            461.773830  274.803581   264.836912  260.923833  287.043970   \n",
      "VOO            175.953310  116.911673    94.891795  101.997051   97.181937   \n",
      "WMB            344.895312  279.735399   282.447551  280.450164  224.833054   \n",
      "XOM            367.855773  232.347866   218.424366  195.095435  253.302895   \n",
      "\n",
      "Ticker_Symbol         COP         CVX         DIA         DVN           E  \\\n",
      "Ticker_Symbol                                                               \n",
      "BKR            477.193881  385.289181  189.057452  627.474100  320.009297   \n",
      "BP             288.589343  246.314895  128.740222  343.631743  257.931050   \n",
      "BPT            302.439975  225.283095   81.524049  399.294776  203.746716   \n",
      "CEO            263.715136  218.616015  142.329744  321.456661  232.510750   \n",
      "CHK            315.659693  222.948007   89.924803  411.692003  189.645787   \n",
      "COP            493.885198  298.037158  142.176020  452.150206  260.340557   \n",
      "CVX            298.037158  347.844234  134.616274  342.977885  223.465645   \n",
      "DIA            142.176020  134.616274  138.859056  161.371502  137.249922   \n",
      "DVN            452.150206  342.977885  161.371502  809.068300  310.501404   \n",
      "E              260.340557  223.465645  137.249922  310.501404  422.804522   \n",
      "EOG            405.029862  309.124698  148.844837  550.886937  284.807296   \n",
      "EPD            182.981702  146.914197   86.441261  240.192457  144.557532   \n",
      "EXC            133.109511  128.707863   99.693329  142.356111  125.565478   \n",
      "FANG           528.159200  366.605955  153.393919  743.638524  329.017419   \n",
      "HAL            427.418572  344.756760  180.156441  559.719189  316.185790   \n",
      "HES            424.284638  332.887896  163.816073  543.646983  306.704603   \n",
      "KMI            267.273169  212.119405  110.022147  344.589738  189.162268   \n",
      "MPC            341.282928  278.422405  158.373425  424.309381  258.594306   \n",
      "NOV            435.049526  352.999354  180.519658  576.410963  326.958673   \n",
      "ONEQ           144.393377  129.182190  128.229815  174.744553  141.808206   \n",
      "OXY            409.118907  325.963168  161.497578  530.928739  300.489437   \n",
      "PSX            327.819730  256.707903  130.108352  399.988178  226.270149   \n",
      "SLB            370.380102  304.042130  159.385941  481.919594  277.560270   \n",
      "SPY            147.369835  136.301654  139.401675  171.444818  143.427471   \n",
      "VLO            337.283434  281.260826  160.620233  410.996892  253.020425   \n",
      "VOO            137.822026  121.986392  112.189376  169.879353  129.930263   \n",
      "WMB            333.520634  279.190031  177.033775  439.070313  267.923119   \n",
      "XOM            272.409072  245.095668  127.612439  315.466583  206.690527   \n",
      "\n",
      "Ticker_Symbol  ...         NOV        ONEQ         OXY         PSX  \\\n",
      "Ticker_Symbol  ...                                                   \n",
      "BKR            ...  629.098999  163.060417  607.589425  422.380250   \n",
      "BP             ...  348.996437  124.552830  320.563099  253.949948   \n",
      "BPT            ...  369.887578   90.955447  341.380406  295.284792   \n",
      "CEO            ...  345.165697  158.623886  300.019515  164.921598   \n",
      "CHK            ...  344.501990  100.500972  369.859640  271.407582   \n",
      "COP            ...  435.049526  144.393377  409.118907  327.819730   \n",
      "CVX            ...  352.999354  129.182190  325.963168  256.707903   \n",
      "DIA            ...  180.519658  128.229815  161.497578  130.108352   \n",
      "DVN            ...  576.410963  174.744553  530.928739  399.988178   \n",
      "E              ...  326.958673  141.808206  300.489437  226.270149   \n",
      "EOG            ...  531.233973  157.534624  475.290343  329.930957   \n",
      "EPD            ...  240.141606   97.814739  210.313615  196.850753   \n",
      "EXC            ...  162.734380  100.296248  152.421471   94.771119   \n",
      "FANG           ...  557.256940  142.056972  647.590584  393.459860   \n",
      "HAL            ...  638.781205  185.043114  503.718173  370.733252   \n",
      "HES            ...  535.812060  173.863812  506.720495  358.536274   \n",
      "KMI            ...  280.803979  105.372831  287.505043  227.455489   \n",
      "MPC            ...  390.929334  153.507170  401.871908  412.676164   \n",
      "NOV            ...  966.571526  192.576278  519.827588  352.832211   \n",
      "ONEQ           ...  192.576278  172.723021  167.922336  115.036745   \n",
      "OXY            ...  519.827588  167.922336  704.050329  372.877804   \n",
      "PSX            ...  352.832211  115.036745  372.877804  456.324087   \n",
      "SLB            ...  561.108945  161.828303  440.211099  331.273783   \n",
      "SPY            ...  194.113549  144.694352  168.537319  124.024661   \n",
      "VLO            ...  459.919519  175.232754  395.943875  423.513271   \n",
      "VOO            ...  156.781089  130.317380  158.028551  117.414485   \n",
      "WMB            ...  446.225239  171.933760  375.020030  260.950961   \n",
      "XOM            ...  325.907028  114.885978  310.998888  243.483849   \n",
      "\n",
      "Ticker_Symbol         SLB         SPY         VLO         VOO          WMB  \\\n",
      "Ticker_Symbol                                                                \n",
      "BKR            581.241422  174.896561  461.773830  175.953310   344.895312   \n",
      "BP             300.613500  132.327628  274.803581  116.911673   279.735399   \n",
      "BPT            294.762598   87.690822  264.836912   94.891795   282.447551   \n",
      "CEO            282.256941  154.047313  260.923833  101.997051   280.450164   \n",
      "CHK            328.172930   99.407870  287.043970   97.181937   224.833054   \n",
      "COP            370.380102  147.369835  337.283434  137.822026   333.520634   \n",
      "CVX            304.042130  136.301654  281.260826  121.986392   279.190031   \n",
      "DIA            159.385941  139.401675  160.620233  112.189376   177.033775   \n",
      "DVN            481.919594  171.444818  410.996892  169.879353   439.070313   \n",
      "E              277.560270  143.427471  253.020425  129.930263   267.923119   \n",
      "EOG            443.501475  157.834640  384.743610  144.696831   388.927058   \n",
      "EPD            194.251799   91.992941  192.098751   93.096746   234.105678   \n",
      "EXC            134.182316  102.625119  140.924880   81.802871   169.621241   \n",
      "FANG           525.213318  149.530271  393.373001  184.095960   369.349244   \n",
      "HAL            559.479908  191.332910  422.523542  172.396442   446.510609   \n",
      "HES            454.176318  173.728512  401.476417  162.184290   389.851400   \n",
      "KMI            257.213367  109.661213  225.127426  114.628619   299.296082   \n",
      "MPC            362.623770  157.851925  518.770826  146.228791   319.893178   \n",
      "NOV            561.108945  194.113549  459.919519  156.781089   446.225239   \n",
      "ONEQ           161.828303  144.694352  175.232754  130.317380   171.933760   \n",
      "OXY            440.211099  168.537319  395.943875  158.028551   375.020030   \n",
      "PSX            331.273783  124.024661  423.513271  117.414485   260.950961   \n",
      "SLB            636.610867  167.474398  383.296947  142.073034   369.776319   \n",
      "SPY            167.474398  152.382028  170.860747  118.357794   195.726432   \n",
      "VLO            383.296947  170.860747  772.165805  141.902381   371.728331   \n",
      "VOO            142.073034  118.357794  141.902381  418.715509   134.630905   \n",
      "WMB            369.776319  195.726432  371.728331  134.630905  1165.330960   \n",
      "XOM            283.327188  127.768753  266.382078  109.406782   244.118878   \n",
      "\n",
      "Ticker_Symbol         XOM  \n",
      "Ticker_Symbol              \n",
      "BKR            367.855773  \n",
      "BP             232.347866  \n",
      "BPT            218.424366  \n",
      "CEO            195.095435  \n",
      "CHK            253.302895  \n",
      "COP            272.409072  \n",
      "CVX            245.095668  \n",
      "DIA            127.612439  \n",
      "DVN            315.466583  \n",
      "E              206.690527  \n",
      "EOG            292.452417  \n",
      "EPD            130.960854  \n",
      "EXC            121.003324  \n",
      "FANG           346.738215  \n",
      "HAL            319.067404  \n",
      "HES            309.294207  \n",
      "KMI            182.758843  \n",
      "MPC            258.610078  \n",
      "NOV            325.907028  \n",
      "ONEQ           114.885978  \n",
      "OXY            310.998888  \n",
      "PSX            243.483849  \n",
      "SLB            283.327188  \n",
      "SPY            127.768753  \n",
      "VLO            266.382078  \n",
      "VOO            109.406782  \n",
      "WMB            244.118878  \n",
      "XOM            320.832824  \n",
      "\n",
      "[28 rows x 28 columns]\n"
     ]
    },
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/_0/4yp2qnxj3hn3g3w46xt2132w0000gn/T/ipykernel_44423/2509954787.py:4: FutureWarning: The default fill_method='pad' in DataFrame.pct_change is deprecated and will be removed in a future version. Either fill in any non-leading NA values prior to calling pct_change or specify 'fill_method=None' to not fill NA values.\n",
      "  daily_returns = pivoted_s_e.pct_change() * 1000\n"
     ]
    }
   ],
   "source": [
    "pd.set_option('display.max_rows', 500)\n",
    "pd.set_option('display.max_columns', 500)\n",
    "\n",
    "daily_returns = pivoted_s_e.pct_change() * 1000\n",
    "covariance_matrix = daily_returns.cov()\n",
    "\n",
    "pd.reset_option('display.max_rows')\n",
    "pd.reset_option('display.max_columns')\n",
    "\n",
    "print(covariance_matrix)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}