rolling_ipc_r.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "name": "rolling_ipc_r.ipynb",
      "provenance": [],
      "collapsed_sections": [],
      "authorship_tag": "ABX9TyPgh06s8C+jtw3B7+t3A/r2",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "ir",
      "display_name": "R"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/Brent-Morrison/Misc_scripts/blob/master/rolling_ipc_r.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "4m4ms1T4AewC"
      },
      "source": [
        "# Rolling portfolio correlations - R  \n",
        "\n",
        "This short notebook will introduce a method to calculate rolling correlations of time series data using R.  The intent is not to create a rolling pairwise correlation, but rather the average of each pairwise correlation of a group of time series.  Data is a group of stocks prices forming two portfolios.\n",
        "\n",
        "This is a companion to the Python implementation of the same functionality.\n",
        "\n",
        "Data will come from a csv saved in my github repository."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "rPorqf1NAYe5",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "e336d336-6ce5-4d82-c729-31aa6555e0a2"
      },
      "source": [
        "# Required packages\n",
        "install.packages('dplyr')\n",
        "install.packages('ramify')\n",
        "install.packages('tibble')\n",
        "install.packages('tidyr')\n",
        "install.packages('purrr')\n",
        "install.packages('slider')\n",
        "\n",
        "library('dplyr', verbose = FALSE, warn.conflicts = FALSE)\n",
        "library('ramify', verbose = FALSE, warn.conflicts = FALSE)\n",
        "library('tibble', verbose = FALSE, warn.conflicts = FALSE)\n",
        "library('tidyr', verbose = FALSE, warn.conflicts = FALSE)\n",
        "library('purrr', verbose = FALSE, warn.conflicts = FALSE)\n",
        "library('slider', verbose = FALSE, warn.conflicts = FALSE)"
      ],
      "execution_count": 1,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n"
          ],
          "name": "stderr"
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6S4BQDf5K2PN"
      },
      "source": [
        "### The data  \r\n",
        "\r\n",
        "A time series of 12 stocks, 6 per sector. "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "r6uHE-NWAtP2",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 281
        },
        "outputId": "b8e0538c-c8a3-4fc4-ad9e-c23ac3a4a310"
      },
      "source": [
        "csv <- 'https://github.com/Brent-Morrison/Misc_scripts/raw/master/daily_price_ts_vw_20201018.csv'\n",
        "daily_price_ts_vw_20201018 <- read.csv(csv)\n",
        "\n",
        "tail(daily_price_ts_vw_20201018)"
      ],
      "execution_count": 2,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "     symbol sector date_stamp close adjusted_close volume    sp500  \n",
              "1751 AMD    2      2020-07-28 67.61 67.61           94181374 3218.44\n",
              "1752 AMD    2      2020-07-29 76.09 76.09          132969679 3258.44\n",
              "1753 AMD    2      2020-07-30 78.20 78.20           80286888 3246.22\n",
              "1754 AMD    2      2020-07-31 77.43 77.43           71699667 3271.12\n",
              "1755 AMD    2      2020-08-03 77.67 77.67           42628817 3294.61\n",
              "1756 AMD    2      2020-08-04 85.04 85.04          155676106 3306.51"
            ],
            "text/latex": "A data.frame: 6 × 7\n\\begin{tabular}{r|lllllll}\n  & symbol & sector & date\\_stamp & close & adjusted\\_close & volume & sp500\\\\\n  & <chr> & <int> & <chr> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t1751 & AMD & 2 & 2020-07-28 & 67.61 & 67.61 &  94181374 & 3218.44\\\\\n\t1752 & AMD & 2 & 2020-07-29 & 76.09 & 76.09 & 132969679 & 3258.44\\\\\n\t1753 & AMD & 2 & 2020-07-30 & 78.20 & 78.20 &  80286888 & 3246.22\\\\\n\t1754 & AMD & 2 & 2020-07-31 & 77.43 & 77.43 &  71699667 & 3271.12\\\\\n\t1755 & AMD & 2 & 2020-08-03 & 77.67 & 77.67 &  42628817 & 3294.61\\\\\n\t1756 & AMD & 2 & 2020-08-04 & 85.04 & 85.04 & 155676106 & 3306.51\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA data.frame: 6 × 7\n\n| <!--/--> | symbol &lt;chr&gt; | sector &lt;int&gt; | date_stamp &lt;chr&gt; | close &lt;dbl&gt; | adjusted_close &lt;dbl&gt; | volume &lt;dbl&gt; | sp500 &lt;dbl&gt; |\n|---|---|---|---|---|---|---|---|\n| 1751 | AMD | 2 | 2020-07-28 | 67.61 | 67.61 |  94181374 | 3218.44 |\n| 1752 | AMD | 2 | 2020-07-29 | 76.09 | 76.09 | 132969679 | 3258.44 |\n| 1753 | AMD | 2 | 2020-07-30 | 78.20 | 78.20 |  80286888 | 3246.22 |\n| 1754 | AMD | 2 | 2020-07-31 | 77.43 | 77.43 |  71699667 | 3271.12 |\n| 1755 | AMD | 2 | 2020-08-03 | 77.67 | 77.67 |  42628817 | 3294.61 |\n| 1756 | AMD | 2 | 2020-08-04 | 85.04 | 85.04 | 155676106 | 3306.51 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A data.frame: 6 × 7</caption>\n",
              "<thead>\n",
              "\t<tr><th></th><th scope=col>symbol</th><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>close</th><th scope=col>adjusted_close</th><th scope=col>volume</th><th scope=col>sp500</th></tr>\n",
              "\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><th scope=row>1751</th><td>AMD</td><td>2</td><td>2020-07-28</td><td>67.61</td><td>67.61</td><td> 94181374</td><td>3218.44</td></tr>\n",
              "\t<tr><th scope=row>1752</th><td>AMD</td><td>2</td><td>2020-07-29</td><td>76.09</td><td>76.09</td><td>132969679</td><td>3258.44</td></tr>\n",
              "\t<tr><th scope=row>1753</th><td>AMD</td><td>2</td><td>2020-07-30</td><td>78.20</td><td>78.20</td><td> 80286888</td><td>3246.22</td></tr>\n",
              "\t<tr><th scope=row>1754</th><td>AMD</td><td>2</td><td>2020-07-31</td><td>77.43</td><td>77.43</td><td> 71699667</td><td>3271.12</td></tr>\n",
              "\t<tr><th scope=row>1755</th><td>AMD</td><td>2</td><td>2020-08-03</td><td>77.67</td><td>77.67</td><td> 42628817</td><td>3294.61</td></tr>\n",
              "\t<tr><th scope=row>1756</th><td>AMD</td><td>2</td><td>2020-08-04</td><td>85.04</td><td>85.04</td><td>155676106</td><td>3306.51</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ZnxqkIwhNAMU",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 281
        },
        "outputId": "2867ce56-3f3f-4e59-f9fb-bb0d60fd7b86"
      },
      "source": [
        "head(daily_price_ts_vw_20201018)"
      ],
      "execution_count": 3,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  symbol sector date_stamp close adjusted_close volume   sp500  \n",
              "1 AAL    1      2020-01-02 29.09 28.9880         6275633 3257.85\n",
              "2 AAL    1      2020-01-03 27.65 27.5531        14020066 3234.85\n",
              "3 AAL    1      2020-01-06 27.32 27.2242         6108646 3246.28\n",
              "4 AAL    1      2020-01-07 27.22 27.1246         6197079 3237.18\n",
              "5 AAL    1      2020-01-08 27.84 27.7424        10497296 3253.05\n",
              "6 AAL    1      2020-01-09 27.95 27.8520         6901065 3274.70"
            ],
            "text/latex": "A data.frame: 6 × 7\n\\begin{tabular}{r|lllllll}\n  & symbol & sector & date\\_stamp & close & adjusted\\_close & volume & sp500\\\\\n  & <chr> & <int> & <chr> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t1 & AAL & 1 & 2020-01-02 & 29.09 & 28.9880 &  6275633 & 3257.85\\\\\n\t2 & AAL & 1 & 2020-01-03 & 27.65 & 27.5531 & 14020066 & 3234.85\\\\\n\t3 & AAL & 1 & 2020-01-06 & 27.32 & 27.2242 &  6108646 & 3246.28\\\\\n\t4 & AAL & 1 & 2020-01-07 & 27.22 & 27.1246 &  6197079 & 3237.18\\\\\n\t5 & AAL & 1 & 2020-01-08 & 27.84 & 27.7424 & 10497296 & 3253.05\\\\\n\t6 & AAL & 1 & 2020-01-09 & 27.95 & 27.8520 &  6901065 & 3274.70\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA data.frame: 6 × 7\n\n| <!--/--> | symbol &lt;chr&gt; | sector &lt;int&gt; | date_stamp &lt;chr&gt; | close &lt;dbl&gt; | adjusted_close &lt;dbl&gt; | volume &lt;dbl&gt; | sp500 &lt;dbl&gt; |\n|---|---|---|---|---|---|---|---|\n| 1 | AAL | 1 | 2020-01-02 | 29.09 | 28.9880 |  6275633 | 3257.85 |\n| 2 | AAL | 1 | 2020-01-03 | 27.65 | 27.5531 | 14020066 | 3234.85 |\n| 3 | AAL | 1 | 2020-01-06 | 27.32 | 27.2242 |  6108646 | 3246.28 |\n| 4 | AAL | 1 | 2020-01-07 | 27.22 | 27.1246 |  6197079 | 3237.18 |\n| 5 | AAL | 1 | 2020-01-08 | 27.84 | 27.7424 | 10497296 | 3253.05 |\n| 6 | AAL | 1 | 2020-01-09 | 27.95 | 27.8520 |  6901065 | 3274.70 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A data.frame: 6 × 7</caption>\n",
              "<thead>\n",
              "\t<tr><th></th><th scope=col>symbol</th><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>close</th><th scope=col>adjusted_close</th><th scope=col>volume</th><th scope=col>sp500</th></tr>\n",
              "\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><th scope=row>1</th><td>AAL</td><td>1</td><td>2020-01-02</td><td>29.09</td><td>28.9880</td><td> 6275633</td><td>3257.85</td></tr>\n",
              "\t<tr><th scope=row>2</th><td>AAL</td><td>1</td><td>2020-01-03</td><td>27.65</td><td>27.5531</td><td>14020066</td><td>3234.85</td></tr>\n",
              "\t<tr><th scope=row>3</th><td>AAL</td><td>1</td><td>2020-01-06</td><td>27.32</td><td>27.2242</td><td> 6108646</td><td>3246.28</td></tr>\n",
              "\t<tr><th scope=row>4</th><td>AAL</td><td>1</td><td>2020-01-07</td><td>27.22</td><td>27.1246</td><td> 6197079</td><td>3237.18</td></tr>\n",
              "\t<tr><th scope=row>5</th><td>AAL</td><td>1</td><td>2020-01-08</td><td>27.84</td><td>27.7424</td><td>10497296</td><td>3253.05</td></tr>\n",
              "\t<tr><th scope=row>6</th><td>AAL</td><td>1</td><td>2020-01-09</td><td>27.95</td><td>27.8520</td><td> 6901065</td><td>3274.70</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pcXwL_LSA7EC"
      },
      "source": [
        "# Averaging a correlation matrix  \n",
        "\n",
        "Construct a small matrix to serve as dummy data for development."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "bYdB2V0uA9qA",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 306
        },
        "outputId": "3b42d1ef-4447-4bc0-8c16-525647b0f538"
      },
      "source": [
        "mtrx <- mat('1,2,3,4; 2,1,5,6; 3,5,1,7; 4,6,7,1')\n",
        "\n",
        "# View as a tibble for nice formatting\n",
        "as_tibble(mtrx)"
      ],
      "execution_count": 4,
      "outputs": [
        {
          "output_type": "stream",
          "text": [
            "Warning message:\n",
            "“The `x` argument of `as_tibble.matrix()` must have unique column names if `.name_repair` is omitted as of tibble 2.0.0.\n",
            "Using compatibility `.name_repair`.\n",
            "\u001b[90mThis warning is displayed once every 8 hours.\u001b[39m\n",
            "\u001b[90mCall `lifecycle::last_warnings()` to see where this warning was generated.\u001b[39m”\n"
          ],
          "name": "stderr"
        },
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  V1 V2 V3 V4\n",
              "1 1  2  3  4 \n",
              "2 2  1  5  6 \n",
              "3 3  5  1  7 \n",
              "4 4  6  7  1 "
            ],
            "text/latex": "A tibble: 4 × 4\n\\begin{tabular}{llll}\n V1 & V2 & V3 & V4\\\\\n <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t 1 & 2 & 3 & 4\\\\\n\t 2 & 1 & 5 & 6\\\\\n\t 3 & 5 & 1 & 7\\\\\n\t 4 & 6 & 7 & 1\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 4 × 4\n\n| V1 &lt;dbl&gt; | V2 &lt;dbl&gt; | V3 &lt;dbl&gt; | V4 &lt;dbl&gt; |\n|---|---|---|---|\n| 1 | 2 | 3 | 4 |\n| 2 | 1 | 5 | 6 |\n| 3 | 5 | 1 | 7 |\n| 4 | 6 | 7 | 1 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 4 × 4</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>V1</th><th scope=col>V2</th><th scope=col>V3</th><th scope=col>V4</th></tr>\n",
              "\t<tr><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>1</td><td>2</td><td>3</td><td>4</td></tr>\n",
              "\t<tr><td>2</td><td>1</td><td>5</td><td>6</td></tr>\n",
              "\t<tr><td>3</td><td>5</td><td>1</td><td>7</td></tr>\n",
              "\t<tr><td>4</td><td>6</td><td>7</td><td>1</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "292UAb_RB0XQ"
      },
      "source": [
        "Extract the upper triangle of the matrix. These are the values of interest for calculating the average of a correlation matrix.  \n",
        "\n",
        "Note the values on the diagonal and below the diagonal are returned as zero."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "0YkaSE1iB1Va",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 220
        },
        "outputId": "b04b5698-7a40-4e16-8b68-da4797a37bb7"
      },
      "source": [
        "mtrx_triu <- triu(mtrx, diag = FALSE)\n",
        "as_tibble(mtrx_triu)"
      ],
      "execution_count": 5,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  V1 V2 V3 V4\n",
              "1 0  2  3  4 \n",
              "2 0  0  5  6 \n",
              "3 0  0  0  7 \n",
              "4 0  0  0  0 "
            ],
            "text/latex": "A tibble: 4 × 4\n\\begin{tabular}{llll}\n V1 & V2 & V3 & V4\\\\\n <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t 0 & 2 & 3 & 4\\\\\n\t 0 & 0 & 5 & 6\\\\\n\t 0 & 0 & 0 & 7\\\\\n\t 0 & 0 & 0 & 0\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 4 × 4\n\n| V1 &lt;dbl&gt; | V2 &lt;dbl&gt; | V3 &lt;dbl&gt; | V4 &lt;dbl&gt; |\n|---|---|---|---|\n| 0 | 2 | 3 | 4 |\n| 0 | 0 | 5 | 6 |\n| 0 | 0 | 0 | 7 |\n| 0 | 0 | 0 | 0 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 4 × 4</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>V1</th><th scope=col>V2</th><th scope=col>V3</th><th scope=col>V4</th></tr>\n",
              "\t<tr><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>0</td><td>2</td><td>3</td><td>4</td></tr>\n",
              "\t<tr><td>0</td><td>0</td><td>5</td><td>6</td></tr>\n",
              "\t<tr><td>0</td><td>0</td><td>0</td><td>7</td></tr>\n",
              "\t<tr><td>0</td><td>0</td><td>0</td><td>0</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "aE1z-9enB7DF"
      },
      "source": [
        "Take the mean omitting the zero values."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "KFia9nW9B-ut",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "3d23cdaa-d2d3-4759-e3b2-357925ffd773"
      },
      "source": [
        "mean(mtrx_triu[mtrx_triu != 0], na.rm= TRUE)"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "[1] 4.5"
            ],
            "text/latex": "4.5",
            "text/markdown": "4.5",
            "text/html": [
              "4.5"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "vi3o9GQDCCi5"
      },
      "source": [
        "Combine to a single call."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "oYT1hJbRCBBN",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "d94068e2-4124-4bb0-eeb9-e92c02706c7d"
      },
      "source": [
        "mean(triu(mtrx, diag = FALSE)[triu(mtrx, diag = FALSE) != 0], na.rm= TRUE)"
      ],
      "execution_count": 7,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "[1] 4.5"
            ],
            "text/latex": "4.5",
            "text/markdown": "4.5",
            "text/html": [
              "4.5"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "GZX8tw8WZQPi"
      },
      "source": [
        "Having dug out the ramify package, specifically for its ```triu``` function, I've just found the base function ```upper.tri```.  \n",
        "\n",
        "This is a lot simpler."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "d7Q5wp99Yxhb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "51330a12-2add-4757-baad-68540e9e52bd"
      },
      "source": [
        "mean(mtrx[upper.tri(mtrx)])"
      ],
      "execution_count": 8,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "[1] 4.5"
            ],
            "text/latex": "4.5",
            "text/markdown": "4.5",
            "text/html": [
              "4.5"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "U7YtvfS8Cogv"
      },
      "source": [
        "Write as a function.  The input, ```x``` is a correlation matrix."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LQE07YUDCqx8"
      },
      "source": [
        "mean_mtrx <- function(x) {\n",
        "   mean(x[upper.tri(x)])\n",
        "}"
      ],
      "execution_count": 9,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LIlWtwOXDNaq",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "7b873add-2f78-4d6e-9df3-503b6b25b65b"
      },
      "source": [
        "mean_mtrx(mtrx)"
      ],
      "execution_count": 10,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "[1] 4.5"
            ],
            "text/latex": "4.5",
            "text/markdown": "4.5",
            "text/html": [
              "4.5"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dhiLwfL4HSMq"
      },
      "source": [
        "# Getting back to the stock data.  \n",
        "\n",
        "The code below creates a correlation matrix of the returns of the stocks loaded in the ```daily_price_ts_vw_20201018``` data initially loaded."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DprHi94QFMvX",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 434
        },
        "outputId": "88801603-cbc1-4b1a-f298-d4762e2691e7"
      },
      "source": [
        "daily_price_ts_vw_20201018 %>% \n",
        "  group_by(symbol) %>%\n",
        "  mutate(rtn_log_1d = log(adjusted_close) - lag(log(adjusted_close))) %>%\n",
        "  slice(2:n()) %>%  # remove first row for each group, 'rtn_log_1d' will be NA\n",
        "  ungroup() %>%\n",
        "  select(date_stamp, symbol, rtn_log_1d) %>%\n",
        "  pivot_wider(names_from = symbol, values_from = rtn_log_1d) %>% \n",
        "  select(-date_stamp) %>% \n",
        "  cor()"
      ],
      "execution_count": 11,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "     AAL       AAN       AAPL      AAWW       ABM       ACCO      ACM      \n",
              "AAL  1.0000000 0.5342663 0.3257310 0.30581378 0.4705691 0.5045357 0.5098054\n",
              "AAN  0.5342663 1.0000000 0.5115475 0.20691703 0.4901531 0.5652053 0.6067528\n",
              "AAPL 0.3257310 0.5115475 1.0000000 0.33300143 0.5812976 0.3612282 0.6011034\n",
              "AAWW 0.3058138 0.2069170 0.3330014 1.00000000 0.4195440 0.3527609 0.5151434\n",
              "ABM  0.4705691 0.4901531 0.5812976 0.41954401 1.0000000 0.5012358 0.6307948\n",
              "ACCO 0.5045357 0.5652053 0.3612282 0.35276092 0.5012358 1.0000000 0.6885873\n",
              "ACM  0.5098054 0.6067528 0.6011034 0.51514345 0.6307948 0.6885873 1.0000000\n",
              "ADBE 0.2195771 0.4285318 0.8251769 0.45566822 0.5497051 0.2760395 0.6153312\n",
              "ADI  0.3736394 0.5752995 0.7234479 0.53158558 0.5602179 0.4853297 0.7075872\n",
              "ADT         NA        NA        NA         NA        NA        NA        NA\n",
              "AKAM 0.1018930 0.1824004 0.5810288 0.05984882 0.3334605 0.1002823 0.2464423\n",
              "AMD  0.2363810 0.4104081 0.6827976 0.33148280 0.4111256 0.2727514 0.4947761\n",
              "     ADBE      ADI       ADT AKAM       AMD      \n",
              "AAL  0.2195771 0.3736394 NA  0.10189296 0.2363810\n",
              "AAN  0.4285318 0.5752995 NA  0.18240040 0.4104081\n",
              "AAPL 0.8251769 0.7234479 NA  0.58102879 0.6827976\n",
              "AAWW 0.4556682 0.5315856 NA  0.05984882 0.3314828\n",
              "ABM  0.5497051 0.5602179 NA  0.33346050 0.4111256\n",
              "ACCO 0.2760395 0.4853297 NA  0.10028232 0.2727514\n",
              "ACM  0.6153312 0.7075872 NA  0.24644228 0.4947761\n",
              "ADBE 1.0000000 0.7720009 NA  0.54210911 0.6958997\n",
              "ADI  0.7720009 1.0000000 NA  0.33402275 0.6333702\n",
              "ADT         NA        NA  1          NA        NA\n",
              "AKAM 0.5421091 0.3340228 NA  1.00000000 0.4926676\n",
              "AMD  0.6958997 0.6333702 NA  0.49266762 1.0000000"
            ],
            "text/latex": "A matrix: 12 × 12 of type dbl\n\\begin{tabular}{r|llllllllllll}\n  & AAL & AAN & AAPL & AAWW & ABM & ACCO & ACM & ADBE & ADI & ADT & AKAM & AMD\\\\\n\\hline\n\tAAL & 1.0000000 & 0.5342663 & 0.3257310 & 0.30581378 & 0.4705691 & 0.5045357 & 0.5098054 & 0.2195771 & 0.3736394 & NA & 0.10189296 & 0.2363810\\\\\n\tAAN & 0.5342663 & 1.0000000 & 0.5115475 & 0.20691703 & 0.4901531 & 0.5652053 & 0.6067528 & 0.4285318 & 0.5752995 & NA & 0.18240040 & 0.4104081\\\\\n\tAAPL & 0.3257310 & 0.5115475 & 1.0000000 & 0.33300143 & 0.5812976 & 0.3612282 & 0.6011034 & 0.8251769 & 0.7234479 & NA & 0.58102879 & 0.6827976\\\\\n\tAAWW & 0.3058138 & 0.2069170 & 0.3330014 & 1.00000000 & 0.4195440 & 0.3527609 & 0.5151434 & 0.4556682 & 0.5315856 & NA & 0.05984882 & 0.3314828\\\\\n\tABM & 0.4705691 & 0.4901531 & 0.5812976 & 0.41954401 & 1.0000000 & 0.5012358 & 0.6307948 & 0.5497051 & 0.5602179 & NA & 0.33346050 & 0.4111256\\\\\n\tACCO & 0.5045357 & 0.5652053 & 0.3612282 & 0.35276092 & 0.5012358 & 1.0000000 & 0.6885873 & 0.2760395 & 0.4853297 & NA & 0.10028232 & 0.2727514\\\\\n\tACM & 0.5098054 & 0.6067528 & 0.6011034 & 0.51514345 & 0.6307948 & 0.6885873 & 1.0000000 & 0.6153312 & 0.7075872 & NA & 0.24644228 & 0.4947761\\\\\n\tADBE & 0.2195771 & 0.4285318 & 0.8251769 & 0.45566822 & 0.5497051 & 0.2760395 & 0.6153312 & 1.0000000 & 0.7720009 & NA & 0.54210911 & 0.6958997\\\\\n\tADI & 0.3736394 & 0.5752995 & 0.7234479 & 0.53158558 & 0.5602179 & 0.4853297 & 0.7075872 & 0.7720009 & 1.0000000 & NA & 0.33402275 & 0.6333702\\\\\n\tADT &        NA &        NA &        NA &         NA &        NA &        NA &        NA &        NA &        NA &  1 &         NA &        NA\\\\\n\tAKAM & 0.1018930 & 0.1824004 & 0.5810288 & 0.05984882 & 0.3334605 & 0.1002823 & 0.2464423 & 0.5421091 & 0.3340228 & NA & 1.00000000 & 0.4926676\\\\\n\tAMD & 0.2363810 & 0.4104081 & 0.6827976 & 0.33148280 & 0.4111256 & 0.2727514 & 0.4947761 & 0.6958997 & 0.6333702 & NA & 0.49266762 & 1.0000000\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA matrix: 12 × 12 of type dbl\n\n| <!--/--> | AAL | AAN | AAPL | AAWW | ABM | ACCO | ACM | ADBE | ADI | ADT | AKAM | AMD |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| AAL | 1.0000000 | 0.5342663 | 0.3257310 | 0.30581378 | 0.4705691 | 0.5045357 | 0.5098054 | 0.2195771 | 0.3736394 | NA | 0.10189296 | 0.2363810 |\n| AAN | 0.5342663 | 1.0000000 | 0.5115475 | 0.20691703 | 0.4901531 | 0.5652053 | 0.6067528 | 0.4285318 | 0.5752995 | NA | 0.18240040 | 0.4104081 |\n| AAPL | 0.3257310 | 0.5115475 | 1.0000000 | 0.33300143 | 0.5812976 | 0.3612282 | 0.6011034 | 0.8251769 | 0.7234479 | NA | 0.58102879 | 0.6827976 |\n| AAWW | 0.3058138 | 0.2069170 | 0.3330014 | 1.00000000 | 0.4195440 | 0.3527609 | 0.5151434 | 0.4556682 | 0.5315856 | NA | 0.05984882 | 0.3314828 |\n| ABM | 0.4705691 | 0.4901531 | 0.5812976 | 0.41954401 | 1.0000000 | 0.5012358 | 0.6307948 | 0.5497051 | 0.5602179 | NA | 0.33346050 | 0.4111256 |\n| ACCO | 0.5045357 | 0.5652053 | 0.3612282 | 0.35276092 | 0.5012358 | 1.0000000 | 0.6885873 | 0.2760395 | 0.4853297 | NA | 0.10028232 | 0.2727514 |\n| ACM | 0.5098054 | 0.6067528 | 0.6011034 | 0.51514345 | 0.6307948 | 0.6885873 | 1.0000000 | 0.6153312 | 0.7075872 | NA | 0.24644228 | 0.4947761 |\n| ADBE | 0.2195771 | 0.4285318 | 0.8251769 | 0.45566822 | 0.5497051 | 0.2760395 | 0.6153312 | 1.0000000 | 0.7720009 | NA | 0.54210911 | 0.6958997 |\n| ADI | 0.3736394 | 0.5752995 | 0.7234479 | 0.53158558 | 0.5602179 | 0.4853297 | 0.7075872 | 0.7720009 | 1.0000000 | NA | 0.33402275 | 0.6333702 |\n| ADT |        NA |        NA |        NA |         NA |        NA |        NA |        NA |        NA |        NA |  1 |         NA |        NA |\n| AKAM | 0.1018930 | 0.1824004 | 0.5810288 | 0.05984882 | 0.3334605 | 0.1002823 | 0.2464423 | 0.5421091 | 0.3340228 | NA | 1.00000000 | 0.4926676 |\n| AMD | 0.2363810 | 0.4104081 | 0.6827976 | 0.33148280 | 0.4111256 | 0.2727514 | 0.4947761 | 0.6958997 | 0.6333702 | NA | 0.49266762 | 1.0000000 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A matrix: 12 × 12 of type dbl</caption>\n",
              "<thead>\n",
              "\t<tr><th></th><th scope=col>AAL</th><th scope=col>AAN</th><th scope=col>AAPL</th><th scope=col>AAWW</th><th scope=col>ABM</th><th scope=col>ACCO</th><th scope=col>ACM</th><th scope=col>ADBE</th><th scope=col>ADI</th><th scope=col>ADT</th><th scope=col>AKAM</th><th scope=col>AMD</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><th scope=row>AAL</th><td>1.0000000</td><td>0.5342663</td><td>0.3257310</td><td>0.30581378</td><td>0.4705691</td><td>0.5045357</td><td>0.5098054</td><td>0.2195771</td><td>0.3736394</td><td>NA</td><td>0.10189296</td><td>0.2363810</td></tr>\n",
              "\t<tr><th scope=row>AAN</th><td>0.5342663</td><td>1.0000000</td><td>0.5115475</td><td>0.20691703</td><td>0.4901531</td><td>0.5652053</td><td>0.6067528</td><td>0.4285318</td><td>0.5752995</td><td>NA</td><td>0.18240040</td><td>0.4104081</td></tr>\n",
              "\t<tr><th scope=row>AAPL</th><td>0.3257310</td><td>0.5115475</td><td>1.0000000</td><td>0.33300143</td><td>0.5812976</td><td>0.3612282</td><td>0.6011034</td><td>0.8251769</td><td>0.7234479</td><td>NA</td><td>0.58102879</td><td>0.6827976</td></tr>\n",
              "\t<tr><th scope=row>AAWW</th><td>0.3058138</td><td>0.2069170</td><td>0.3330014</td><td>1.00000000</td><td>0.4195440</td><td>0.3527609</td><td>0.5151434</td><td>0.4556682</td><td>0.5315856</td><td>NA</td><td>0.05984882</td><td>0.3314828</td></tr>\n",
              "\t<tr><th scope=row>ABM</th><td>0.4705691</td><td>0.4901531</td><td>0.5812976</td><td>0.41954401</td><td>1.0000000</td><td>0.5012358</td><td>0.6307948</td><td>0.5497051</td><td>0.5602179</td><td>NA</td><td>0.33346050</td><td>0.4111256</td></tr>\n",
              "\t<tr><th scope=row>ACCO</th><td>0.5045357</td><td>0.5652053</td><td>0.3612282</td><td>0.35276092</td><td>0.5012358</td><td>1.0000000</td><td>0.6885873</td><td>0.2760395</td><td>0.4853297</td><td>NA</td><td>0.10028232</td><td>0.2727514</td></tr>\n",
              "\t<tr><th scope=row>ACM</th><td>0.5098054</td><td>0.6067528</td><td>0.6011034</td><td>0.51514345</td><td>0.6307948</td><td>0.6885873</td><td>1.0000000</td><td>0.6153312</td><td>0.7075872</td><td>NA</td><td>0.24644228</td><td>0.4947761</td></tr>\n",
              "\t<tr><th scope=row>ADBE</th><td>0.2195771</td><td>0.4285318</td><td>0.8251769</td><td>0.45566822</td><td>0.5497051</td><td>0.2760395</td><td>0.6153312</td><td>1.0000000</td><td>0.7720009</td><td>NA</td><td>0.54210911</td><td>0.6958997</td></tr>\n",
              "\t<tr><th scope=row>ADI</th><td>0.3736394</td><td>0.5752995</td><td>0.7234479</td><td>0.53158558</td><td>0.5602179</td><td>0.4853297</td><td>0.7075872</td><td>0.7720009</td><td>1.0000000</td><td>NA</td><td>0.33402275</td><td>0.6333702</td></tr>\n",
              "\t<tr><th scope=row>ADT</th><td>       NA</td><td>       NA</td><td>       NA</td><td>        NA</td><td>       NA</td><td>       NA</td><td>       NA</td><td>       NA</td><td>       NA</td><td> 1</td><td>        NA</td><td>       NA</td></tr>\n",
              "\t<tr><th scope=row>AKAM</th><td>0.1018930</td><td>0.1824004</td><td>0.5810288</td><td>0.05984882</td><td>0.3334605</td><td>0.1002823</td><td>0.2464423</td><td>0.5421091</td><td>0.3340228</td><td>NA</td><td>1.00000000</td><td>0.4926676</td></tr>\n",
              "\t<tr><th scope=row>AMD</th><td>0.2363810</td><td>0.4104081</td><td>0.6827976</td><td>0.33148280</td><td>0.4111256</td><td>0.2727514</td><td>0.4947761</td><td>0.6958997</td><td>0.6333702</td><td>NA</td><td>0.49266762</td><td>1.0000000</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "95mdrtbsKaF5"
      },
      "source": [
        "The default setting to the ```cor``` function does not handle missing observations or NA's.  \n",
        "\n",
        "Inspection of the underlying data however reveals that the stock ADT does not have observation for the period 2020-06-19 to 2020-08-04.  We need to be able to handle this situation.\n",
        "\n",
        "Setting the ```use``` parameter to ```pairwise.complete.obs``` will [calculate the correlation between each pair of variables using all complete pairs of observations of those variables](https://www.rdocumentation.org/packages/stats/versions/3.6.2/topics/cor).  \n",
        "\n",
        "Lets try that."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "DUmXpVEaGCQK",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 434
        },
        "outputId": "ba0876ad-0173-48b4-9263-c6d8466a514c"
      },
      "source": [
        "daily_price_ts_vw_20201018 %>% \n",
        "  group_by(symbol) %>%\n",
        "  mutate(rtn_log_1d = log(adjusted_close) - lag(log(adjusted_close))) %>%\n",
        "  slice(2:n()) %>%  # remove first row for each group, 'rtn_log_1d' will be NA\n",
        "  ungroup() %>%\n",
        "  select(date_stamp, symbol, rtn_log_1d) %>%\n",
        "  pivot_wider(names_from = symbol, values_from = rtn_log_1d) %>% \n",
        "  select(-date_stamp) %>% \n",
        "  cor(use = 'pairwise.complete.obs')"
      ],
      "execution_count": 12,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "     AAL       AAN       AAPL      AAWW       ABM       ACCO      ACM      \n",
              "AAL  1.0000000 0.5342663 0.3257310 0.30581378 0.4705691 0.5045357 0.5098054\n",
              "AAN  0.5342663 1.0000000 0.5115475 0.20691703 0.4901531 0.5652053 0.6067528\n",
              "AAPL 0.3257310 0.5115475 1.0000000 0.33300143 0.5812976 0.3612282 0.6011034\n",
              "AAWW 0.3058138 0.2069170 0.3330014 1.00000000 0.4195440 0.3527609 0.5151434\n",
              "ABM  0.4705691 0.4901531 0.5812976 0.41954401 1.0000000 0.5012358 0.6307948\n",
              "ACCO 0.5045357 0.5652053 0.3612282 0.35276092 0.5012358 1.0000000 0.6885873\n",
              "ACM  0.5098054 0.6067528 0.6011034 0.51514345 0.6307948 0.6885873 1.0000000\n",
              "ADBE 0.2195771 0.4285318 0.8251769 0.45566822 0.5497051 0.2760395 0.6153312\n",
              "ADI  0.3736394 0.5752995 0.7234479 0.53158558 0.5602179 0.4853297 0.7075872\n",
              "ADT  0.4282776 0.5542218 0.4782523 0.36862451 0.3873883 0.5251819 0.5887212\n",
              "AKAM 0.1018930 0.1824004 0.5810288 0.05984882 0.3334605 0.1002823 0.2464423\n",
              "AMD  0.2363810 0.4104081 0.6827976 0.33148280 0.4111256 0.2727514 0.4947761\n",
              "     ADBE      ADI       ADT       AKAM       AMD      \n",
              "AAL  0.2195771 0.3736394 0.4282776 0.10189296 0.2363810\n",
              "AAN  0.4285318 0.5752995 0.5542218 0.18240040 0.4104081\n",
              "AAPL 0.8251769 0.7234479 0.4782523 0.58102879 0.6827976\n",
              "AAWW 0.4556682 0.5315856 0.3686245 0.05984882 0.3314828\n",
              "ABM  0.5497051 0.5602179 0.3873883 0.33346050 0.4111256\n",
              "ACCO 0.2760395 0.4853297 0.5251819 0.10028232 0.2727514\n",
              "ACM  0.6153312 0.7075872 0.5887212 0.24644228 0.4947761\n",
              "ADBE 1.0000000 0.7720009 0.4323994 0.54210911 0.6958997\n",
              "ADI  0.7720009 1.0000000 0.5440289 0.33402275 0.6333702\n",
              "ADT  0.4323994 0.5440289 1.0000000 0.20803371 0.4137642\n",
              "AKAM 0.5421091 0.3340228 0.2080337 1.00000000 0.4926676\n",
              "AMD  0.6958997 0.6333702 0.4137642 0.49266762 1.0000000"
            ],
            "text/latex": "A matrix: 12 × 12 of type dbl\n\\begin{tabular}{r|llllllllllll}\n  & AAL & AAN & AAPL & AAWW & ABM & ACCO & ACM & ADBE & ADI & ADT & AKAM & AMD\\\\\n\\hline\n\tAAL & 1.0000000 & 0.5342663 & 0.3257310 & 0.30581378 & 0.4705691 & 0.5045357 & 0.5098054 & 0.2195771 & 0.3736394 & 0.4282776 & 0.10189296 & 0.2363810\\\\\n\tAAN & 0.5342663 & 1.0000000 & 0.5115475 & 0.20691703 & 0.4901531 & 0.5652053 & 0.6067528 & 0.4285318 & 0.5752995 & 0.5542218 & 0.18240040 & 0.4104081\\\\\n\tAAPL & 0.3257310 & 0.5115475 & 1.0000000 & 0.33300143 & 0.5812976 & 0.3612282 & 0.6011034 & 0.8251769 & 0.7234479 & 0.4782523 & 0.58102879 & 0.6827976\\\\\n\tAAWW & 0.3058138 & 0.2069170 & 0.3330014 & 1.00000000 & 0.4195440 & 0.3527609 & 0.5151434 & 0.4556682 & 0.5315856 & 0.3686245 & 0.05984882 & 0.3314828\\\\\n\tABM & 0.4705691 & 0.4901531 & 0.5812976 & 0.41954401 & 1.0000000 & 0.5012358 & 0.6307948 & 0.5497051 & 0.5602179 & 0.3873883 & 0.33346050 & 0.4111256\\\\\n\tACCO & 0.5045357 & 0.5652053 & 0.3612282 & 0.35276092 & 0.5012358 & 1.0000000 & 0.6885873 & 0.2760395 & 0.4853297 & 0.5251819 & 0.10028232 & 0.2727514\\\\\n\tACM & 0.5098054 & 0.6067528 & 0.6011034 & 0.51514345 & 0.6307948 & 0.6885873 & 1.0000000 & 0.6153312 & 0.7075872 & 0.5887212 & 0.24644228 & 0.4947761\\\\\n\tADBE & 0.2195771 & 0.4285318 & 0.8251769 & 0.45566822 & 0.5497051 & 0.2760395 & 0.6153312 & 1.0000000 & 0.7720009 & 0.4323994 & 0.54210911 & 0.6958997\\\\\n\tADI & 0.3736394 & 0.5752995 & 0.7234479 & 0.53158558 & 0.5602179 & 0.4853297 & 0.7075872 & 0.7720009 & 1.0000000 & 0.5440289 & 0.33402275 & 0.6333702\\\\\n\tADT & 0.4282776 & 0.5542218 & 0.4782523 & 0.36862451 & 0.3873883 & 0.5251819 & 0.5887212 & 0.4323994 & 0.5440289 & 1.0000000 & 0.20803371 & 0.4137642\\\\\n\tAKAM & 0.1018930 & 0.1824004 & 0.5810288 & 0.05984882 & 0.3334605 & 0.1002823 & 0.2464423 & 0.5421091 & 0.3340228 & 0.2080337 & 1.00000000 & 0.4926676\\\\\n\tAMD & 0.2363810 & 0.4104081 & 0.6827976 & 0.33148280 & 0.4111256 & 0.2727514 & 0.4947761 & 0.6958997 & 0.6333702 & 0.4137642 & 0.49266762 & 1.0000000\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA matrix: 12 × 12 of type dbl\n\n| <!--/--> | AAL | AAN | AAPL | AAWW | ABM | ACCO | ACM | ADBE | ADI | ADT | AKAM | AMD |\n|---|---|---|---|---|---|---|---|---|---|---|---|---|\n| AAL | 1.0000000 | 0.5342663 | 0.3257310 | 0.30581378 | 0.4705691 | 0.5045357 | 0.5098054 | 0.2195771 | 0.3736394 | 0.4282776 | 0.10189296 | 0.2363810 |\n| AAN | 0.5342663 | 1.0000000 | 0.5115475 | 0.20691703 | 0.4901531 | 0.5652053 | 0.6067528 | 0.4285318 | 0.5752995 | 0.5542218 | 0.18240040 | 0.4104081 |\n| AAPL | 0.3257310 | 0.5115475 | 1.0000000 | 0.33300143 | 0.5812976 | 0.3612282 | 0.6011034 | 0.8251769 | 0.7234479 | 0.4782523 | 0.58102879 | 0.6827976 |\n| AAWW | 0.3058138 | 0.2069170 | 0.3330014 | 1.00000000 | 0.4195440 | 0.3527609 | 0.5151434 | 0.4556682 | 0.5315856 | 0.3686245 | 0.05984882 | 0.3314828 |\n| ABM | 0.4705691 | 0.4901531 | 0.5812976 | 0.41954401 | 1.0000000 | 0.5012358 | 0.6307948 | 0.5497051 | 0.5602179 | 0.3873883 | 0.33346050 | 0.4111256 |\n| ACCO | 0.5045357 | 0.5652053 | 0.3612282 | 0.35276092 | 0.5012358 | 1.0000000 | 0.6885873 | 0.2760395 | 0.4853297 | 0.5251819 | 0.10028232 | 0.2727514 |\n| ACM | 0.5098054 | 0.6067528 | 0.6011034 | 0.51514345 | 0.6307948 | 0.6885873 | 1.0000000 | 0.6153312 | 0.7075872 | 0.5887212 | 0.24644228 | 0.4947761 |\n| ADBE | 0.2195771 | 0.4285318 | 0.8251769 | 0.45566822 | 0.5497051 | 0.2760395 | 0.6153312 | 1.0000000 | 0.7720009 | 0.4323994 | 0.54210911 | 0.6958997 |\n| ADI | 0.3736394 | 0.5752995 | 0.7234479 | 0.53158558 | 0.5602179 | 0.4853297 | 0.7075872 | 0.7720009 | 1.0000000 | 0.5440289 | 0.33402275 | 0.6333702 |\n| ADT | 0.4282776 | 0.5542218 | 0.4782523 | 0.36862451 | 0.3873883 | 0.5251819 | 0.5887212 | 0.4323994 | 0.5440289 | 1.0000000 | 0.20803371 | 0.4137642 |\n| AKAM | 0.1018930 | 0.1824004 | 0.5810288 | 0.05984882 | 0.3334605 | 0.1002823 | 0.2464423 | 0.5421091 | 0.3340228 | 0.2080337 | 1.00000000 | 0.4926676 |\n| AMD | 0.2363810 | 0.4104081 | 0.6827976 | 0.33148280 | 0.4111256 | 0.2727514 | 0.4947761 | 0.6958997 | 0.6333702 | 0.4137642 | 0.49266762 | 1.0000000 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A matrix: 12 × 12 of type dbl</caption>\n",
              "<thead>\n",
              "\t<tr><th></th><th scope=col>AAL</th><th scope=col>AAN</th><th scope=col>AAPL</th><th scope=col>AAWW</th><th scope=col>ABM</th><th scope=col>ACCO</th><th scope=col>ACM</th><th scope=col>ADBE</th><th scope=col>ADI</th><th scope=col>ADT</th><th scope=col>AKAM</th><th scope=col>AMD</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><th scope=row>AAL</th><td>1.0000000</td><td>0.5342663</td><td>0.3257310</td><td>0.30581378</td><td>0.4705691</td><td>0.5045357</td><td>0.5098054</td><td>0.2195771</td><td>0.3736394</td><td>0.4282776</td><td>0.10189296</td><td>0.2363810</td></tr>\n",
              "\t<tr><th scope=row>AAN</th><td>0.5342663</td><td>1.0000000</td><td>0.5115475</td><td>0.20691703</td><td>0.4901531</td><td>0.5652053</td><td>0.6067528</td><td>0.4285318</td><td>0.5752995</td><td>0.5542218</td><td>0.18240040</td><td>0.4104081</td></tr>\n",
              "\t<tr><th scope=row>AAPL</th><td>0.3257310</td><td>0.5115475</td><td>1.0000000</td><td>0.33300143</td><td>0.5812976</td><td>0.3612282</td><td>0.6011034</td><td>0.8251769</td><td>0.7234479</td><td>0.4782523</td><td>0.58102879</td><td>0.6827976</td></tr>\n",
              "\t<tr><th scope=row>AAWW</th><td>0.3058138</td><td>0.2069170</td><td>0.3330014</td><td>1.00000000</td><td>0.4195440</td><td>0.3527609</td><td>0.5151434</td><td>0.4556682</td><td>0.5315856</td><td>0.3686245</td><td>0.05984882</td><td>0.3314828</td></tr>\n",
              "\t<tr><th scope=row>ABM</th><td>0.4705691</td><td>0.4901531</td><td>0.5812976</td><td>0.41954401</td><td>1.0000000</td><td>0.5012358</td><td>0.6307948</td><td>0.5497051</td><td>0.5602179</td><td>0.3873883</td><td>0.33346050</td><td>0.4111256</td></tr>\n",
              "\t<tr><th scope=row>ACCO</th><td>0.5045357</td><td>0.5652053</td><td>0.3612282</td><td>0.35276092</td><td>0.5012358</td><td>1.0000000</td><td>0.6885873</td><td>0.2760395</td><td>0.4853297</td><td>0.5251819</td><td>0.10028232</td><td>0.2727514</td></tr>\n",
              "\t<tr><th scope=row>ACM</th><td>0.5098054</td><td>0.6067528</td><td>0.6011034</td><td>0.51514345</td><td>0.6307948</td><td>0.6885873</td><td>1.0000000</td><td>0.6153312</td><td>0.7075872</td><td>0.5887212</td><td>0.24644228</td><td>0.4947761</td></tr>\n",
              "\t<tr><th scope=row>ADBE</th><td>0.2195771</td><td>0.4285318</td><td>0.8251769</td><td>0.45566822</td><td>0.5497051</td><td>0.2760395</td><td>0.6153312</td><td>1.0000000</td><td>0.7720009</td><td>0.4323994</td><td>0.54210911</td><td>0.6958997</td></tr>\n",
              "\t<tr><th scope=row>ADI</th><td>0.3736394</td><td>0.5752995</td><td>0.7234479</td><td>0.53158558</td><td>0.5602179</td><td>0.4853297</td><td>0.7075872</td><td>0.7720009</td><td>1.0000000</td><td>0.5440289</td><td>0.33402275</td><td>0.6333702</td></tr>\n",
              "\t<tr><th scope=row>ADT</th><td>0.4282776</td><td>0.5542218</td><td>0.4782523</td><td>0.36862451</td><td>0.3873883</td><td>0.5251819</td><td>0.5887212</td><td>0.4323994</td><td>0.5440289</td><td>1.0000000</td><td>0.20803371</td><td>0.4137642</td></tr>\n",
              "\t<tr><th scope=row>AKAM</th><td>0.1018930</td><td>0.1824004</td><td>0.5810288</td><td>0.05984882</td><td>0.3334605</td><td>0.1002823</td><td>0.2464423</td><td>0.5421091</td><td>0.3340228</td><td>0.2080337</td><td>1.00000000</td><td>0.4926676</td></tr>\n",
              "\t<tr><th scope=row>AMD</th><td>0.2363810</td><td>0.4104081</td><td>0.6827976</td><td>0.33148280</td><td>0.4111256</td><td>0.2727514</td><td>0.4947761</td><td>0.6958997</td><td>0.6333702</td><td>0.4137642</td><td>0.49266762</td><td>1.0000000</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "72yOvQ3kMbEe"
      },
      "source": [
        "This looks like a good solution, the NA's returned in the initial correlation matrix have now been populated.  \n",
        "\n",
        "It should be noted the correlation for pairs against ADT may be spurious when applied on a rolling basis.  In the extreme, the current code may return a correlation based on only two observations.  \n",
        "\n",
        "Some other form of error checking needs to be implemented. This is probably best implemented using a filter in the dplyr chain that reshapes the data for ingestion to the ```cor``` function.  \n",
        "\n",
        "We will now pipe this into the custom ```mean_mtrx``` function."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "VjIRDaMUQYir",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "31343992-30c8-4696-d1ef-8fce118d8c33"
      },
      "source": [
        "daily_price_ts_vw_20201018 %>% \n",
        "  group_by(symbol) %>%\n",
        "  mutate(rtn_log_1d = log(adjusted_close) - lag(log(adjusted_close))) %>%\n",
        "  slice(2:n()) %>%  # remove first row for each group, 'rtn_log_1d' will be NA\n",
        "  ungroup() %>%\n",
        "  select(date_stamp, symbol, rtn_log_1d) %>%\n",
        "  pivot_wider(names_from = symbol, values_from = rtn_log_1d) %>% \n",
        "  select(-date_stamp) %>% \n",
        "  cor(use = 'pairwise.complete.obs') %>%\n",
        "  mean_mtrx()"
      ],
      "execution_count": 13,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "[1] 0.4573814"
            ],
            "text/latex": "0.457381420427434",
            "text/markdown": "0.457381420427434",
            "text/html": [
              "0.457381420427434"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "atn4S9z5P5xI"
      },
      "source": [
        "# As a function\n",
        "\n",
        "Move the ```pivot_wider```, ```cor``` and ```mean_mtrx``` components into a function.  \n",
        "\n",
        "This function accepts a dataframe."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "-1Xr7n6FP9Me"
      },
      "source": [
        "ipc <- function(df) {\n",
        "  \n",
        "  max_date = max(df$date_stamp)\n",
        "  \n",
        "  ipc = df %>%\n",
        "    select(date_stamp, symbol, rtn_log_1d) %>%\n",
        "    pivot_wider(names_from = symbol, values_from = rtn_log_1d) %>% \n",
        "    select(-date_stamp) %>% \n",
        "    cor(use = 'pairwise.complete.obs') %>%\n",
        "    mean_mtrx()\n",
        "\n",
        "  return(tibble(date_stamp = max_date, ipc = ipc))\n",
        "}"
      ],
      "execution_count": 14,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hAPxnuv6RMaa"
      },
      "source": [
        "Create a data frame with the ```rtn_log_1d``` column..."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "ngnHKFNzRKgX",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 281
        },
        "outputId": "5bb79b3a-4df0-4a6d-9023-6ca90c0f089a"
      },
      "source": [
        "daily_price_rtn <- daily_price_ts_vw_20201018 %>% \n",
        "  mutate(\n",
        "    date_stamp = as.Date(date_stamp),\n",
        "    rtn_log_1d = log(adjusted_close) - lag(log(adjusted_close))\n",
        "    ) %>%\n",
        "  slice(2:n()) %>%\n",
        "  arrange(date_stamp)\n",
        "\n",
        "head(daily_price_rtn)"
      ],
      "execution_count": 15,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  symbol sector date_stamp close  adjusted_close volume   sp500   rtn_log_1d\n",
              "1 AAN    1      2020-01-02  57.67  57.4919         658505 3257.85  1.611909 \n",
              "2 AAPL   2      2020-01-02 300.35 298.8389       33911864 3257.85  1.744631 \n",
              "3 AAWW   1      2020-01-02  27.73  27.7300         226199 3257.85 -2.761210 \n",
              "4 ABM    1      2020-01-02  38.42  37.9229         358600 3257.85 -0.384784 \n",
              "5 ACCO   1      2020-01-02   9.11   8.8936         505243 3257.85 -1.397909 \n",
              "6 ACM    1      2020-01-02  42.98  42.9800         855310 3257.85  1.864615 "
            ],
            "text/latex": "A data.frame: 6 × 8\n\\begin{tabular}{r|llllllll}\n  & symbol & sector & date\\_stamp & close & adjusted\\_close & volume & sp500 & rtn\\_log\\_1d\\\\\n  & <chr> & <int> & <date> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n\\hline\n\t1 & AAN  & 1 & 2020-01-02 &  57.67 &  57.4919 &   658505 & 3257.85 &  1.611909\\\\\n\t2 & AAPL & 2 & 2020-01-02 & 300.35 & 298.8389 & 33911864 & 3257.85 &  1.744631\\\\\n\t3 & AAWW & 1 & 2020-01-02 &  27.73 &  27.7300 &   226199 & 3257.85 & -2.761210\\\\\n\t4 & ABM  & 1 & 2020-01-02 &  38.42 &  37.9229 &   358600 & 3257.85 & -0.384784\\\\\n\t5 & ACCO & 1 & 2020-01-02 &   9.11 &   8.8936 &   505243 & 3257.85 & -1.397909\\\\\n\t6 & ACM  & 1 & 2020-01-02 &  42.98 &  42.9800 &   855310 & 3257.85 &  1.864615\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA data.frame: 6 × 8\n\n| <!--/--> | symbol &lt;chr&gt; | sector &lt;int&gt; | date_stamp &lt;date&gt; | close &lt;dbl&gt; | adjusted_close &lt;dbl&gt; | volume &lt;dbl&gt; | sp500 &lt;dbl&gt; | rtn_log_1d &lt;dbl&gt; |\n|---|---|---|---|---|---|---|---|---|\n| 1 | AAN  | 1 | 2020-01-02 |  57.67 |  57.4919 |   658505 | 3257.85 |  1.611909 |\n| 2 | AAPL | 2 | 2020-01-02 | 300.35 | 298.8389 | 33911864 | 3257.85 |  1.744631 |\n| 3 | AAWW | 1 | 2020-01-02 |  27.73 |  27.7300 |   226199 | 3257.85 | -2.761210 |\n| 4 | ABM  | 1 | 2020-01-02 |  38.42 |  37.9229 |   358600 | 3257.85 | -0.384784 |\n| 5 | ACCO | 1 | 2020-01-02 |   9.11 |   8.8936 |   505243 | 3257.85 | -1.397909 |\n| 6 | ACM  | 1 | 2020-01-02 |  42.98 |  42.9800 |   855310 | 3257.85 |  1.864615 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A data.frame: 6 × 8</caption>\n",
              "<thead>\n",
              "\t<tr><th></th><th scope=col>symbol</th><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>close</th><th scope=col>adjusted_close</th><th scope=col>volume</th><th scope=col>sp500</th><th scope=col>rtn_log_1d</th></tr>\n",
              "\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><th scope=row>1</th><td>AAN </td><td>1</td><td>2020-01-02</td><td> 57.67</td><td> 57.4919</td><td>  658505</td><td>3257.85</td><td> 1.611909</td></tr>\n",
              "\t<tr><th scope=row>2</th><td>AAPL</td><td>2</td><td>2020-01-02</td><td>300.35</td><td>298.8389</td><td>33911864</td><td>3257.85</td><td> 1.744631</td></tr>\n",
              "\t<tr><th scope=row>3</th><td>AAWW</td><td>1</td><td>2020-01-02</td><td> 27.73</td><td> 27.7300</td><td>  226199</td><td>3257.85</td><td>-2.761210</td></tr>\n",
              "\t<tr><th scope=row>4</th><td>ABM </td><td>1</td><td>2020-01-02</td><td> 38.42</td><td> 37.9229</td><td>  358600</td><td>3257.85</td><td>-0.384784</td></tr>\n",
              "\t<tr><th scope=row>5</th><td>ACCO</td><td>1</td><td>2020-01-02</td><td>  9.11</td><td>  8.8936</td><td>  505243</td><td>3257.85</td><td>-1.397909</td></tr>\n",
              "\t<tr><th scope=row>6</th><td>ACM </td><td>1</td><td>2020-01-02</td><td> 42.98</td><td> 42.9800</td><td>  855310</td><td>3257.85</td><td> 1.864615</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "cIVHc2FWPA3-"
      },
      "source": [
        "... and apply the ```ipc``` function."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "jXutN09USFeB",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 128
        },
        "outputId": "b77ae6f1-3a76-47ed-f452-6f150c3fe716"
      },
      "source": [
        "ipc(daily_price_rtn)"
      ],
      "execution_count": 16,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  date_stamp ipc       \n",
              "1 2020-08-04 0.03777869"
            ],
            "text/latex": "A tibble: 1 × 2\n\\begin{tabular}{ll}\n date\\_stamp & ipc\\\\\n <date> & <dbl>\\\\\n\\hline\n\t 2020-08-04 & 0.03777869\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 1 × 2\n\n| date_stamp &lt;date&gt; | ipc &lt;dbl&gt; |\n|---|---|\n| 2020-08-04 | 0.03777869 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 1 × 2</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>date_stamp</th><th scope=col>ipc</th></tr>\n",
              "\t<tr><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>2020-08-04</td><td>0.03777869</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "44fP5ND2Ycq8"
      },
      "source": [
        "Lets see if we can apply by group.  \n",
        "\n",
        "### Method 1"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "LD6VdDdEWcq3",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 158
        },
        "outputId": "22584803-6948-402f-87ab-08797ed87968"
      },
      "source": [
        "daily_price_rtn %>% \n",
        "  filter(date_stamp >= '2020-02-11' & date_stamp <= '2020-07-31') %>%\n",
        "  split(.$sector) %>%\n",
        "  map_dfr(., ipc, .id = 'sector')"
      ],
      "execution_count": 17,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  sector date_stamp ipc      \n",
              "1 1      2020-07-31 0.4958920\n",
              "2 2      2020-07-31 0.5700418"
            ],
            "text/latex": "A tibble: 2 × 3\n\\begin{tabular}{lll}\n sector & date\\_stamp & ipc\\\\\n <chr> & <date> & <dbl>\\\\\n\\hline\n\t 1 & 2020-07-31 & 0.4958920\\\\\n\t 2 & 2020-07-31 & 0.5700418\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 2 × 3\n\n| sector &lt;chr&gt; | date_stamp &lt;date&gt; | ipc &lt;dbl&gt; |\n|---|---|---|\n| 1 | 2020-07-31 | 0.4958920 |\n| 2 | 2020-07-31 | 0.5700418 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 2 × 3</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>ipc</th></tr>\n",
              "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>1</td><td>2020-07-31</td><td>0.4958920</td></tr>\n",
              "\t<tr><td>2</td><td>2020-07-31</td><td>0.5700418</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "8E6-tse1PSab"
      },
      "source": [
        "### Method 2"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "N7l9Ws3DmItI",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 158
        },
        "outputId": "abb8245f-8432-4491-ce42-43b605b667d3"
      },
      "source": [
        "daily_price_rtn %>% \n",
        "  filter(date_stamp >= '2020-02-11' & date_stamp <= '2020-07-31') %>%\n",
        "  group_by(sector) %>%\n",
        "  group_modify(~ ipc(.x))"
      ],
      "execution_count": 18,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  sector date_stamp ipc      \n",
              "1 1      2020-07-31 0.4958920\n",
              "2 2      2020-07-31 0.5700418"
            ],
            "text/latex": "A grouped\\_df: 2 × 3\n\\begin{tabular}{lll}\n sector & date\\_stamp & ipc\\\\\n <int> & <date> & <dbl>\\\\\n\\hline\n\t 1 & 2020-07-31 & 0.4958920\\\\\n\t 2 & 2020-07-31 & 0.5700418\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA grouped_df: 2 × 3\n\n| sector &lt;int&gt; | date_stamp &lt;date&gt; | ipc &lt;dbl&gt; |\n|---|---|---|\n| 1 | 2020-07-31 | 0.4958920 |\n| 2 | 2020-07-31 | 0.5700418 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A grouped_df: 2 × 3</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>ipc</th></tr>\n",
              "\t<tr><th scope=col>&lt;int&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>1</td><td>2020-07-31</td><td>0.4958920</td></tr>\n",
              "\t<tr><td>2</td><td>2020-07-31</td><td>0.5700418</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "mtGHW5MVjxEH"
      },
      "source": [
        "Validate the results for sector 2."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "K7GjasFRjzCY",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 34
        },
        "outputId": "838eb280-616d-4fa3-e01a-e94ece00c5c8"
      },
      "source": [
        "daily_price_rtn %>% \n",
        "  filter(sector == 2) %>% \n",
        "  filter(date_stamp >= '2020-02-11' & date_stamp <= '2020-07-31') %>%\n",
        "  select(date_stamp, symbol, rtn_log_1d) %>%\n",
        "  pivot_wider(names_from = symbol, values_from = rtn_log_1d) %>% \n",
        "  select(-date_stamp) %>% \n",
        "  cor(use = 'pairwise.complete.obs') %>%\n",
        "  mean_mtrx()"
      ],
      "execution_count": 19,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "[1] 0.5700418"
            ],
            "text/latex": "0.57004180297818",
            "text/markdown": "0.57004180297818",
            "text/html": [
              "0.57004180297818"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "dknUnOtmGwoQ"
      },
      "source": [
        "All good so far.  \r\n",
        "<br>\r\n",
        "\r\n",
        "-----"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gdUZQ1-VQN98"
      },
      "source": [
        "# Rolling intra-portfolio correlation  \n",
        "\n",
        "We are looking at time series data.  Our function needs to be applied on a rolling basis.\n",
        "\n",
        "This is where the [slider](https://cran.r-project.org/web/packages/slider/vignettes/slider.html) package, and its ```slide_period_dfr``` function come in.  \n",
        "\n",
        "First the ```ipc``` function is wrapped in an outer function in order to apply by group.  "
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "YvyhUtKqdWyO"
      },
      "source": [
        "ipc_by_grp <- function(df) { \n",
        "  df %>% \n",
        "    split(.$sector) %>%\n",
        "    map_dfr(., ipc, .id = 'sector')\n",
        "}"
      ],
      "execution_count": 20,
      "outputs": []
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "aRb6iauWdtEb",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 158
        },
        "outputId": "fb004219-4010-41c2-e91e-feadc8d9fba4"
      },
      "source": [
        "daily_price_rtn %>% \n",
        "  filter(date_stamp >= '2020-02-11' & date_stamp <= '2020-07-31') %>%\n",
        "  ipc_by_grp()"
      ],
      "execution_count": 21,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  sector date_stamp ipc      \n",
              "1 1      2020-07-31 0.4958920\n",
              "2 2      2020-07-31 0.5700418"
            ],
            "text/latex": "A tibble: 2 × 3\n\\begin{tabular}{lll}\n sector & date\\_stamp & ipc\\\\\n <chr> & <date> & <dbl>\\\\\n\\hline\n\t 1 & 2020-07-31 & 0.4958920\\\\\n\t 2 & 2020-07-31 & 0.5700418\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 2 × 3\n\n| sector &lt;chr&gt; | date_stamp &lt;date&gt; | ipc &lt;dbl&gt; |\n|---|---|---|\n| 1 | 2020-07-31 | 0.4958920 |\n| 2 | 2020-07-31 | 0.5700418 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 2 × 3</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>ipc</th></tr>\n",
              "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>1</td><td>2020-07-31</td><td>0.4958920</td></tr>\n",
              "\t<tr><td>2</td><td>2020-07-31</td><td>0.5700418</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "axmOavTLHypg"
      },
      "source": [
        "Finally we slide it."
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "id": "1rh1VBTJcnTn",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 281
        },
        "outputId": "c104115b-fc2b-4fa4-91b1-9a3849258c52"
      },
      "source": [
        "slide_period_dfr(\n",
        "  .x = daily_price_rtn,\n",
        "  .i = daily_price_rtn$date_stamp,\n",
        "  .period = \"month\",\n",
        "  .f = ipc_by_grp,\n",
        "  .before = 5,\n",
        "  .complete = TRUE\n",
        ")"
      ],
      "execution_count": 22,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  sector date_stamp ipc        \n",
              "1 1      2020-06-30  0.09560748\n",
              "2 2      2020-06-30 -0.14320115\n",
              "3 1      2020-07-31  0.49081102\n",
              "4 2      2020-07-31  0.56886202\n",
              "5 1      2020-08-04  0.51351245\n",
              "6 2      2020-08-04  0.56957118"
            ],
            "text/latex": "A tibble: 6 × 3\n\\begin{tabular}{lll}\n sector & date\\_stamp & ipc\\\\\n <chr> & <date> & <dbl>\\\\\n\\hline\n\t 1 & 2020-06-30 &  0.09560748\\\\\n\t 2 & 2020-06-30 & -0.14320115\\\\\n\t 1 & 2020-07-31 &  0.49081102\\\\\n\t 2 & 2020-07-31 &  0.56886202\\\\\n\t 1 & 2020-08-04 &  0.51351245\\\\\n\t 2 & 2020-08-04 &  0.56957118\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 6 × 3\n\n| sector &lt;chr&gt; | date_stamp &lt;date&gt; | ipc &lt;dbl&gt; |\n|---|---|---|\n| 1 | 2020-06-30 |  0.09560748 |\n| 2 | 2020-06-30 | -0.14320115 |\n| 1 | 2020-07-31 |  0.49081102 |\n| 2 | 2020-07-31 |  0.56886202 |\n| 1 | 2020-08-04 |  0.51351245 |\n| 2 | 2020-08-04 |  0.56957118 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 6 × 3</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>ipc</th></tr>\n",
              "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>1</td><td>2020-06-30</td><td> 0.09560748</td></tr>\n",
              "\t<tr><td>2</td><td>2020-06-30</td><td>-0.14320115</td></tr>\n",
              "\t<tr><td>1</td><td>2020-07-31</td><td> 0.49081102</td></tr>\n",
              "\t<tr><td>2</td><td>2020-07-31</td><td> 0.56886202</td></tr>\n",
              "\t<tr><td>1</td><td>2020-08-04</td><td> 0.51351245</td></tr>\n",
              "\t<tr><td>2</td><td>2020-08-04</td><td> 0.56957118</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "WRQeThvrD27e"
      },
      "source": [
        "The results for July 2020 differ very slightly - 0.57 versus 0.568 for sector 2.  This likely due to the ```slide_period_dfr``` function being applied by month, as opposed to the specific day range for the ```ipc_by_grp``` function.  \n",
        "\n",
        "We can check this by changing the day range on the earlier function.\n"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 158
        },
        "id": "KRgczoKwEu7o",
        "outputId": "8573a69c-0ab2-4fd0-8946-b61f978bc535"
      },
      "source": [
        "daily_price_rtn %>% \n",
        "  filter(date_stamp >= '2020-02-01' & date_stamp <= '2020-07-31') %>%\n",
        "  ipc_by_grp()"
      ],
      "execution_count": 23,
      "outputs": [
        {
          "output_type": "display_data",
          "data": {
            "text/plain": [
              "  sector date_stamp ipc     \n",
              "1 1      2020-07-31 0.490811\n",
              "2 2      2020-07-31 0.568862"
            ],
            "text/latex": "A tibble: 2 × 3\n\\begin{tabular}{lll}\n sector & date\\_stamp & ipc\\\\\n <chr> & <date> & <dbl>\\\\\n\\hline\n\t 1 & 2020-07-31 & 0.490811\\\\\n\t 2 & 2020-07-31 & 0.568862\\\\\n\\end{tabular}\n",
            "text/markdown": "\nA tibble: 2 × 3\n\n| sector &lt;chr&gt; | date_stamp &lt;date&gt; | ipc &lt;dbl&gt; |\n|---|---|---|\n| 1 | 2020-07-31 | 0.490811 |\n| 2 | 2020-07-31 | 0.568862 |\n\n",
            "text/html": [
              "<table class=\"dataframe\">\n",
              "<caption>A tibble: 2 × 3</caption>\n",
              "<thead>\n",
              "\t<tr><th scope=col>sector</th><th scope=col>date_stamp</th><th scope=col>ipc</th></tr>\n",
              "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
              "</thead>\n",
              "<tbody>\n",
              "\t<tr><td>1</td><td>2020-07-31</td><td>0.490811</td></tr>\n",
              "\t<tr><td>2</td><td>2020-07-31</td><td>0.568862</td></tr>\n",
              "</tbody>\n",
              "</table>\n"
            ]
          },
          "metadata": {
            "tags": []
          }
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "6dHDSA0GwXgE"
      },
      "source": [
        "In agreement.  That's good enough for me."
      ]
    }
  ]
}