<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/tidyquant-integrates-quandl-getting-data-just-got-easier/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html"> business-science.io - Articles</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>Today I’m very pleased to introduce the new <a target="_blank" rel="nofollow" href="https://www.quandl.com/"><em>Quandl</em></a> API integration that is available in the <strong>development version</strong> of <code>tidyquant</code>. Normally I’d introduce this feature during the next CRAN release (<strong>v0.5.0 coming soon</strong>), but it’s really useful and honestly I just couldn’t wait. If you’re unfamiliar with <em>Quandl</em>, it’s amazing: it’s a web service that has partnered with top-tier data publishers to enable users to retrieve a wide range of financial and economic data sets, many of which are <strong>FREE</strong>! <em>Quandl</em> has it’s own <em>R</em> package (aptly named <code>Quandl</code>) that is overall very good but has one minor inconvenience: it doesn’t return multiple data sets in a “tidy” format. This slight inconvenience has been addressed in the integration that comes packaged in the latest development version of <code>tidyquant</code>. Now users can use the <em>Quandl</em> API from within <code>tidyquant</code> with three functions: <code>quandl_api_key()</code>, <code>quandl_search()</code>, and the core function <code>tq_get(get = &quot;quandl&quot;)</code>. In this post, we’ll go through a user-contributed example, <strong>How To Perform a Fama French 3 Factor Analysis</strong>, that showcases how the <em>Quandl</em> integration fits into the <strong>“Collect, Modify, Analyze” financial analysis workflow</strong>. Interested readers can download the development version using <code>devtools::install_github(&quot;business-science/tidyquant&quot;)</code>. More information is available on the <a target="_blank" rel="nofollow" href="https://github.com/business-science/tidyquant">tidyquant GitHub page</a> including the updated development vignettes.</p>
<h1>Table of Contents</h1>
<ul>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#overview">Overview</a></li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#prerequisites">Prerequisites</a></li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#example1">Example: How to Perform a Fama French 3 Factor Analysis</a>
<ul>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#step1">Step 1: Collect Data</a></li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#step2">Step 2: Modify Data</a></li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#step3">Step 3: Analyze Data</a></li>
</ul>
</li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#conclusion">Conclusion</a></li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#recap">Recap</a></li>
<li><a target="_blank" rel="nofollow" href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html#further-reading">Further Reading</a></li>
</ul>
<h1>Overview <a></a></h1>
<blockquote>
<p>tidyquant: Bringing financial analysis to the tidyverse</p>
</blockquote>
<p>The topic for today is the <a target="_blank" rel="nofollow" href="https://www.quandl.com/"><em>Quandl</em></a> integration today. <em>Quandl</em> enables access to a wide range of financial and economic data. It has it’s own <em>R</em> library appropriately named <code>Quandl</code>. Users can sign up for a <strong>FREE</strong> account, and in return users get an API key that enables access to numerous free and paid data sets. The <code>Quandl</code> package is very good: it enables searching the <em>Quandl</em> databases from the <em>R</em> console. Once a data set is found, the data set “code” can be used to retrieve the data in various formats. The one downside is that, although you can get multiple data sets (e.g. for multiple stocks, FRED codes, etc), the data returned is not “tidy”. This is where the <code>tidyquant</code> integration fits in. The integration makes it even more convenient to get data, and when multiple data sets are retrieved they are returned in one “tidy” data frame (aka “long” format which is perfect for grouping and scaling analysis)! In addition, you only need to load one package, <code>tidyquant</code>, to get the full capabilities of the <em>Quandl</em> API. The figure below shows how <em>Quandl</em> fits into the <strong>“Collect, Modify, Analyze”</strong> <code>tidyquant</code> financial analysis workflow.</p>
<p><img alt="tidyquant Financial Analysis Workflow" src="https://i2.wp.com/www.business-science.io/assets/tidyquant-quandl.PNG?w=456" data-recalc-dims="1"></p>
<p>If you are new to <code>tidyquant</code>, there’s a few core functions that you need to be aware of. I’ve broken them down by step in the CMA process.</p>
<ul>
<li>
<p><strong>Upstream (Collect)</strong>: <code>tq_get()</code> is a one-stop shop for getting web-based financial data in a “tidy” data frame format. Get data for daily stock prices (historical), key statistics (real-time), key ratios (historical), financial statements, economic data from the FRED, FOREX rates from Oanda, and <strong>now Quandl!</strong></p>
</li>
<li><strong>Midstream (Modify)</strong>:
<ul>
<li><code>tq_transmute()</code> and <code>tq_mutate()</code> manipulate financial data. <code>tq_mutate()</code> is used to add a column to the data frame. <code>tq_transmute()</code> is used to return a new data frame which is necessary for periodicity changes.</li>
<li><code>tq_portfolio()</code> aggregates a group (or multiple groups) of asset returns into one or more portfolios.</li>
</ul>
</li>
<li><strong>Downstream (Analyze)</strong>: <code>tq_performance()</code> integrates <code>PerformanceAnalytics</code> functions that turn investment returns into performance metrics.</li>
</ul>
<p></p>
<p>To learn more about the functions, <a target="_blank" rel="nofollow" href="https://github.com/business-science/tidyquant/tree/master/vignettes">browse the Development Vignettes on GitHub</a>.</p>
<h1>Prerequisites <a></a></h1>
<p>To use the <em>Quandl</em> integration and other new <code>tidyquant</code> features, you’ll need to install the development version available on the <a target="_blank" rel="nofollow" href="https://github.com/business-science"><em>Business Science</em> GitHub Site</a>. You can download with <code>devtools</code>.</p>
<figure>
<pre><code data-lang="r"><span>devtools</span><span>::</span><span>install_github</span><span>(</span><span>&quot;business-science/tidyquant&quot;</span><span>)</span></code></pre>
</figure>
<p>Next, load <code>tidyquant</code>, <code>broom</code> and <code>corrr</code> packages. The <code>broom</code> and <code>corrr</code> packages will help in our analysis at the end of the financial analysis workflow.</p>
<figure>
<pre><code data-lang="r"><span># Loads tidyquant, tidyverse, lubridate, quantmod, TTR, xts, zoo, PerformanceAnalytics
</span><span>library</span><span>(</span><span>tidyquant</span><span>)</span><span>
</span><span>library</span><span>(</span><span>broom</span><span>)</span><span> </span><span># Use `tidy` and `glance` functions
</span><span>library</span><span>(</span><span>corrr</span><span>)</span><span> </span><span>#</span><span> </span><span>tidy</span><span> </span><span>correlations</span></code></pre>
</figure>
<p>I also recommend the open-source <a target="_blank" rel="nofollow" href="https://www.rstudio.com/">RStudio</a> IDE, which makes <em>R Programming</em> easy and efficient especially for financial analysis. Now, onto a really neat example showing off why <em>Quandl</em> is such a great tool.</p>
<h1>Example: How To Perform a Fama French 3 Factor Analysis <a></a></h1>
<p>Before we get started, I’d like to thank <a target="_blank" rel="nofollow" href="https://www.linkedin.com/in/bobrietveld/">Bob Rietveld</a> for the usage case. He’s been doing a lot of work with Fama French three and five factor models. You can find an example of his <a target="_blank" rel="nofollow" href="https://beta.rstudioconnect.com/content/2613/example_tidyquant_implementation.html">FF analyses here</a>. In this example, we’ll perform a Fama French three factor regression on a portfolio of the following stocks: 20% AAPL, 20% F, 40% GE, and 20% MSFT. According to <a target="_blank" rel="nofollow" href="http://www.investopedia.com/terms/f/famaandfrenchthreefactormodel.asp">Investopedia</a>:</p>
<blockquote>
<p>The Fama and French Three Factor Model is an asset pricing model that expands on the capital asset pricing model (CAPM) by adding size and value factors to the market risk factor in CAPM. This model considers the fact that value and small-cap stocks outperform markets on a regular basis. By including these two additional factors, the model adjusts for the out-performance tendency, which is thought to make it a better tool for evaluating manager performance.</p>
</blockquote>
<p>The CMA process steps we’ll implement are as follows:</p>
<ol>
<li><strong>Collect Data</strong>: We’ll use the new <em>Quandl</em> integration to get both stock prices and Fama French data sets.</li>
<li><strong>Modify Data</strong>: This is a portfolio analysis so we’ll need to aggregate stock returns into a weighted portfolio</li>
<li><strong>Analyze Data</strong>: We’ll perform a regression analysis, and we need the <code>broom</code> package for the <code>tidy()</code> and <code>glance()</code> functions.</li>
</ol>
<h2>Step 1: Collect Data <a></a></h2>
<p>In this step, we will collect two data frames. The first is the historical stock returns for individual stocks. The second is the Fama French three factor data set. We are going to use the <em>Quandl</em> API integration so first set your API key using <code>quandl_api_key()</code>. If you don’t have an API key yet, you can sign up with <a target="_blank" rel="nofollow" href="https://www.quandl.com/"><em>Quandl</em></a>.</p>
<figure>
<pre><code data-lang="r"><span>quandl_api_key</span><span>(</span><span>&quot;your-api-key-here&quot;</span><span>)</span></code></pre>
</figure>
<h5>Collecting Historical Stock Returns</h5>
<p>Next, let’s create a table of stocks. We will use the “WIKI” database which returns open, high low, close, volume, dividends, splits, and adjusted prices. The <em>Quandl</em> data sets use the following code format: <strong>“Database” / “Data Set”</strong>. For “AAPL”, this would be “WIKI/AAPL” indicating the WIKI database and AAPL data set. The code in the first column will allow us to pipe (<code>%>%</code>) the stock list to the <code>tq_get()</code> function next.</p>
<figure>
<pre><code data-lang="r"><span>stock_list_quandl</span><span> </span><span>&lt;-</span><span> </span><span>tribble</span><span>(</span><span>
</span><span>~</span><span>code</span><span>,</span><span> </span><span>~</span><span>symbol</span><span>,</span><span>
</span><span>&quot;WIKI/AAPL&quot;</span><span>,</span><span> </span><span>&quot;AAPL&quot;</span><span>,</span><span>
</span><span>&quot;WIKI/F&quot;</span><span>,</span><span> </span><span>&quot;F&quot;</span><span>,</span><span>
</span><span>&quot;WIKI/GE&quot;</span><span>,</span><span> </span><span>&quot;GE&quot;</span><span>,</span><span>
</span><span>&quot;WIKI/MSFT&quot;</span><span>,</span><span> </span><span>&quot;MSFT&quot;</span><span>
</span><span>)</span><span>
</span><span>stock_list_quandl</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## # A tibble: 4 × 2
##        code symbol
##       &lt;chr>  &lt;chr>
## 1 WIKI/AAPL   AAPL
## 2    WIKI/F      F
## 3   WIKI/GE     GE
## 4 WIKI/MSFT   MSFT</code></pre>
</figure>
<p>Once we have the stocks, we can very easily use <code>tq_get(get = &quot;quandl&quot;)</code> to get stock prices and even stock returns depending on the options we use. The following time series options are available to be passed to the underlying <code>Quandl::Quandl()</code>function:</p>
<ul>
<li><code>order</code> = “asc”, “desc”</li>
<li><code>start_date</code> (<code>from</code>) = “yyyy-mm-dd”</li>
<li><code>end_date</code> (<code>to</code>) = “yyyy-mm-dd”</li>
<li><code>column_index</code> = numeric column number (e.g. 1)</li>
<li><code>rows</code> = numeric row number indicating first n rows (e.g. 100)</li>
<li><code>collapse</code> = “none”, “daily”, “weekly”, “monthly”, “quarterly”, “annual”</li>
<li><code>transform</code> = “none”, “diff”, “rdiff”, “cumul”, “normalize”</li>
</ul>
<p>We’ll use <code>from</code> and <code>to</code> to select a ten year time period from the beginning of 2007 through the end of 2016, <code>transform = &quot;rdiff&quot;</code> to get percentage returns, <code>collapse = &quot;monthly&quot;</code> to get monthly data, and <code>column_index = 11</code> to get the eleventh column, “adj.close”. We’ll <code>rename</code> the column from “adj.close” to “monthly.returns” to accurately describe the values.</p>
<figure>
<pre><code data-lang="r"><span>stock_returns_quandl</span><span> </span><span>&lt;-</span><span> </span><span>stock_list_quandl</span><span> </span><span>%>%</span><span>
</span><span>tq_get</span><span>(</span><span>get</span><span> </span><span>=</span><span> </span><span>&quot;quandl&quot;</span><span>,</span><span>
</span><span>from</span><span> </span><span>=</span><span> </span><span>&quot;2007-01-01&quot;</span><span>,</span><span>
</span><span>to</span><span> </span><span>=</span><span> </span><span>&quot;2016-12-31&quot;</span><span>,</span><span>
</span><span>transform</span><span> </span><span>=</span><span> </span><span>&quot;rdiff&quot;</span><span>,</span><span>
</span><span>collapse</span><span> </span><span>=</span><span> </span><span>&quot;monthly&quot;</span><span>,</span><span>
</span><span>column_index</span><span> </span><span>=</span><span> </span><span>11</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>rename</span><span>(</span><span>monthly.returns</span><span> </span><span>=</span><span> </span><span>adj.close</span><span>)</span><span>
</span><span>stock_returns_quandl</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## # A tibble: 476 × 4
##         code symbol       date monthly.returns
##        &lt;chr>  &lt;chr>     &lt;date>           &lt;dbl>
## 1  WIKI/AAPL   AAPL 2016-12-31     0.047468354
## 2  WIKI/AAPL   AAPL 2016-11-30    -0.025893958
## 3  WIKI/AAPL   AAPL 2016-10-31     0.005045587
## 4  WIKI/AAPL   AAPL 2016-09-30     0.064750236
## 5  WIKI/AAPL   AAPL 2016-08-31     0.023618063
## 6  WIKI/AAPL   AAPL 2016-07-31     0.090062762
## 7  WIKI/AAPL   AAPL 2016-06-30    -0.042659724
## 8  WIKI/AAPL   AAPL 2016-05-31     0.071799336
## 9  WIKI/AAPL   AAPL 2016-04-30    -0.139921094
## 10 WIKI/AAPL   AAPL 2016-03-31     0.127210673
## # ... with 466 more rows</code></pre>
</figure>
<h5>Collecting Fama French 3-Factor Monthly Data</h5>
<p>Next, we need to get the Fama French data. Suppose we don’t know exactly what we are looking for. We’ll use the function, <code>quandl_search()</code>, to query the <em>Quandl</em> API (a wrapper for <code>Quandl.search()</code>). We can search within the <em>R</em> console by setting <code>query</code> to a descriptive value. We’ll set <code>per_page = 5</code> to get the top 5 results. We’ll set <code>silent = TRUE</code> to turn off the meta data output (in practice it may be beneficial to leave this easy-to-read option on). The results returned contain the “id”, dataset_code, “database_code”, “name”, “description”, etc, which gives us both insight into the data set contents and the information needed to retrieve. I’ve removed “description” to make it easier to view the information.</p>
<figure>
<pre><code data-lang="r"><span>quandl_search</span><span>(</span><span>query</span><span> </span><span>=</span><span> </span><span>&quot;FAMA FRENCH&quot;</span><span>,</span><span> </span><span>per_page</span><span> </span><span>=</span><span> </span><span>5</span><span>,</span><span> </span><span>silent</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>select</span><span>(</span><span>-</span><span>description</span><span>)</span></code></pre>
</figure>
<table>
<thead>
<tr>
<th>id</th>
<th>dataset_code</th>
<th>database_code</th>
<th>name</th>
<th>refreshed_at</th>
<th>newest_available_date</th>
<th>oldest_available_date</th>
<th>column_names</th>
<th>frequency</th>
<th>type</th>
<th>premium</th>
<th>database_id</th>
</tr>
</thead>
<tbody>
<tr>
<td>30216128</td>
<td>MOMENTUM_A</td>
<td>KFRENCH</td>
<td>Fama/French Factors (Annual)</td>
<td>2017-03-18T21:08:12.712Z</td>
<td>2016-12-31</td>
<td>1927-12-31</td>
<td>Date, Momentum</td>
<td>annual</td>
<td>Time Series</td>
<td>FALSE</td>
<td>389</td>
</tr>
<tr>
<td>30579533</td>
<td>FACTORS_A</td>
<td>KFRENCH</td>
<td>Fama/French Factors (Annual)</td>
<td>2017-03-18T21:06:21.885Z</td>
<td>2016-12-31</td>
<td>1927-12-31</td>
<td>Date, Mkt-RF, SMB, HML, RF</td>
<td>annual</td>
<td>Time Series</td>
<td>FALSE</td>
<td>389</td>
</tr>
<tr>
<td>2292156</td>
<td>FACTORS_M</td>
<td>KFRENCH</td>
<td>Fama/French Factors (Monthly)</td>
<td>2017-03-18T21:06:21.953Z</td>
<td>2017-01-31</td>
<td>1926-07-31</td>
<td>Date, Mkt-RF, SMB, HML, RF</td>
<td>monthly</td>
<td>Time Series</td>
<td>FALSE</td>
<td>389</td>
</tr>
<tr>
<td>2292158</td>
<td>FACTORS_W</td>
<td>KFRENCH</td>
<td>Fama/French Factors (Weekly)</td>
<td>2017-03-18T21:06:25.103Z</td>
<td>2017-01-27</td>
<td>1926-07-02</td>
<td>Date, Mkt-RF, SMB, HML, RF</td>
<td>weekly</td>
<td>Time Series</td>
<td>FALSE</td>
<td>389</td>
</tr>
<tr>
<td>2676225</td>
<td>MOMENTUM_M</td>
<td>KFRENCH</td>
<td>Fama/French Factors (Monthly)</td>
<td>2017-03-18T21:08:12.746Z</td>
<td>2017-01-31</td>
<td>1927-01-31</td>
<td>Date, Momentum</td>
<td>monthly</td>
<td>Time Series</td>
<td>FALSE</td>
<td>389</td>
</tr>
</tbody>
</table>
<p>The third result, “FACTORS_M”, is what we need. We can retrieve with <code>tq_get(get = &quot;quandl&quot;)</code> by piping (<code>%>%</code>) <code>&quot;KFRENCH/FACTORS_M&quot;</code>. (Remember that the format is always database code / dataset code). We’ll tack on <code>collapse = &quot;monthly&quot;</code> to ensure the dates match up with the returns, <code>stock_returns_quandl</code>.</p>
<figure>
<pre><code data-lang="r"><span># Get Fama French 3 Factor Data
</span><span>fama_french_3_M</span><span> </span><span>&lt;-</span><span> </span><span>&quot;KFRENCH/FACTORS_M&quot;</span><span> </span><span>%>%</span><span>
</span><span>tq_get</span><span>(</span><span>get</span><span> </span><span>=</span><span> </span><span>&quot;quandl&quot;</span><span>,</span><span>
</span><span>collapse</span><span> </span><span>=</span><span> </span><span>&quot;monthly&quot;</span><span>)</span><span>
</span><span>fama_french_3_M</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## # A tibble: 1,087 × 5
##          date mkt.rf   smb   hml    rf
##        &lt;date>  &lt;dbl> &lt;dbl> &lt;dbl> &lt;dbl>
## 1  2017-01-31   1.94 -0.95 -2.76  0.04
## 2  2016-12-31   1.81 -0.04  3.52  0.03
## 3  2016-11-30   4.86  5.68  8.44  0.01
## 4  2016-10-31  -2.02 -4.41  4.15  0.02
## 5  2016-09-30   0.25  2.00 -1.34  0.02
## 6  2016-08-31   0.50  0.94  3.18  0.02
## 7  2016-07-31   3.95  2.90 -0.98  0.02
## 8  2016-06-30  -0.05  0.61 -1.49  0.02
## 9  2016-05-31   1.78 -0.27 -1.79  0.01
## 10 2016-04-30   0.92  0.68  3.25  0.01
## # ... with 1,077 more rows</code></pre>
</figure>
<p>Now we have all of the data needed. We are ready to move on to modifying the data.</p>
<h2>Step 2: Modify Data <a></a></h2>
<p>There’s two parts to this step. First, we will aggregate the portfolio in the weights specified in the beginning of the example:</p>
<ul>
<li>20% AAPL, 20% F, 40% GE, and 20% MSFT.</li>
</ul>
<p>Second, we will join the aggregated portfolio returns with the Fama French data.</p>
<h5>Aggregate Portfolio</h5>
<p>Portfolio aggregation is performed using <code>tq_portfolio()</code> as follows. We create a tibble (“tidy” data frame) of weights that can be mapped using the first column, “stocks”.</p>
<figure>
<pre><code data-lang="r"><span># Creating tibble of stock weights that can be mapped to the portfolio assets
</span><span>weights_tib</span><span> </span><span>&lt;-</span><span> </span><span>tibble</span><span>(</span><span>stocks</span><span> </span><span>=</span><span> </span><span>c</span><span>(</span><span>&quot;AAPL&quot;</span><span>,</span><span> </span><span>&quot;F&quot;</span><span>,</span><span> </span><span>&quot;GE&quot;</span><span>,</span><span> </span><span>&quot;MSFT&quot;</span><span>),</span><span>
</span><span>weights</span><span> </span><span>=</span><span> </span><span>c</span><span>(</span><span>0.2</span><span>,</span><span> </span><span>0.2</span><span>,</span><span> </span><span>0.4</span><span>,</span><span> </span><span>0.2</span><span>))</span><span>
</span><span>weights_tib</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## # A tibble: 4 × 2
##   stocks weights
##    &lt;chr>   &lt;dbl>
## 1   AAPL     0.2
## 2      F     0.2
## 3     GE     0.4
## 4   MSFT     0.2</code></pre>
</figure>
<p>Then we pass the individual stock returns, <code>stock_returns_quandl</code>, to the <code>tq_portfolio()</code> function specifying the assets column “symbol” and the returns column “monthly.returns”. The <code>weights_tib</code> tibble is also passed to the <code>weights</code> argument. Note that there is also an argument, <code>rebalance_on = c(NA, &quot;years&quot;, &quot;quarters&quot;, &quot;months&quot;, &quot;weeks&quot;, &quot;days&quot;)</code> if rebalancing is a consideration to factor into the model. Last, the output column is renamed to “monthly.returns” using the <code>col_rename</code> argument.</p>
<figure>
<pre><code data-lang="r"><span># Aggregating weighted portfolio returns
</span><span>portfolio_returns</span><span> </span><span>&lt;-</span><span> </span><span>stock_returns_quandl</span><span> </span><span>%>%</span><span>
</span><span>tq_portfolio</span><span>(</span><span>assets_col</span><span> </span><span>=</span><span> </span><span>symbol</span><span>,</span><span>
</span><span>returns_col</span><span> </span><span>=</span><span> </span><span>monthly.returns</span><span>,</span><span>
</span><span>weights</span><span> </span><span>=</span><span> </span><span>weights_tib</span><span>,</span><span>
</span><span>rebalance_on</span><span> </span><span>=</span><span> </span><span>NA</span><span>,</span><span>
</span><span>col_rename</span><span> </span><span>=</span><span> </span><span>&quot;monthly.returns&quot;</span><span>)</span><span>
</span><span>portfolio_returns</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## # A tibble: 119 × 2
##          date monthly.returns
##        &lt;date>           &lt;dbl>
## 1  2007-02-28    -0.034414463
## 2  2007-03-31     0.022735121
## 3  2007-04-30     0.050631578
## 4  2007-05-31     0.068295584
## 5  2007-06-30     0.028425228
## 6  2007-07-31     0.002249988
## 7  2007-08-31    -0.001170495
## 8  2007-09-30     0.077241302
## 9  2007-10-31     0.113769305
## 10 2007-11-30    -0.076217903
## # ... with 109 more rows</code></pre>
</figure>
<h5>Join Portfolio Returns and Fama French Data</h5>
<p>We can join the two data sets by the “date” column in each using <code>left_join</code> from the <code>dplyr</code> package.</p>
<figure>
<pre><code data-lang="r"><span>portfolio_fama_french</span><span> </span><span>&lt;-</span><span> </span><span>left_join</span><span>(</span><span>portfolio_returns</span><span>,</span><span> </span><span>fama_french_3_M</span><span>,</span><span> </span><span>by</span><span> </span><span>=</span><span> </span><span>&quot;date&quot;</span><span>)</span><span>
</span><span>portfolio_fama_french</span></code></pre>
</figure>
<figure>
<pre><code data-lang="text">## # A tibble: 119 × 6
##          date monthly.returns mkt.rf   smb   hml    rf
##        &lt;date>           &lt;dbl>  &lt;dbl> &lt;dbl> &lt;dbl> &lt;dbl>
## 1  2007-02-28    -0.034414463  -1.96  1.32 -0.10  0.38
## 2  2007-03-31     0.022735121   0.68 -0.06 -0.23  0.43
## 3  2007-04-30     0.050631578   3.49 -2.07 -1.15  0.44
## 4  2007-05-31     0.068295584   3.24 -0.01 -0.07  0.41
## 5  2007-06-30     0.028425228  -1.96  0.79 -1.11  0.40
## 6  2007-07-31     0.002249988  -3.73 -2.50 -3.34  0.40
## 7  2007-08-31    -0.001170495   0.92 -0.10 -2.25  0.42
## 8  2007-09-30     0.077241302   3.22 -2.28 -1.91  0.32
## 9  2007-10-31     0.113769305   1.80  0.22 -2.62  0.32
## 10 2007-11-30    -0.076217903  -4.83 -2.61 -1.18  0.34
## # ... with 109 more rows</code></pre>
</figure>
<p>Now we are ready to analyze.</p>
<h2>Step 3: Anaylze Data <a></a></h2>
<p>In the final step we will analyze two ways. First, we will perform the three factor regression, which yields model parameters. Second, we will review visually by plotting a correlation matrix.</p>
<h5>Three Factor Regression Model</h5>
<p>The article, <a target="_blank" rel="nofollow" href="http://www.efficientfrontier.com/ef/101/roll101.htm">“Rolling Your Own: Three-Factor Analysis”</a>, by William J. Bernstein with Efficient Frontier goes through an excellent step-by-step explaining the method. We are concerned with the following variables:</p>
<ul>
<li>Return of the Total Market minus the T-Bill return (mkt.rf): The return of the total market (CRSP 1-10) minus the T-bill return (Mkt)</li>
<li>Small Minus Big (smb): The return of small company stocks minus that of big company stocks</li>
<li>High Minus Low (hml): The return of the cheapest third of stocks sorted by price/book minus the most expensive third</li>
</ul>
<p>Three factor regression is performed with the <code>lm()</code> function by analyzing the relationship between the portfolio returns and the three FF factors.</p>
<figure>
<pre><code data-lang="r"><span>ff_3</span><span> </span><span>&lt;-</span><span> </span><span>lm</span><span>(</span><span>monthly.returns</span><span> </span><span>~</span><span> </span><span>mkt.rf</span><span> </span><span>+</span><span> </span><span>smb</span><span> </span><span>+</span><span> </span><span>hml</span><span>,</span><span> </span><span>data</span><span> </span><span>=</span><span> </span><span>portfolio_fama_french</span><span>)</span></code></pre>
</figure>
<p>Using <code>glance</code> from the <code>broom</code> package, we can review the regression metrics. Note <code>kable()</code> from the <code>knitr</code> package is used to create aesthetically pleasing tables. We can see from the “r.squared” value that 67% of the variance of the portfolio returns is explained by the model.</p>
<figure>
<pre><code data-lang="r"><span>glance</span><span>(</span><span>ff_3</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre>
</figure>
<table>
<thead>
<tr>
<th>r.squared</th>
<th>adj.r.squared</th>
<th>sigma</th>
<th>statistic</th>
<th>p.value</th>
<th>df</th>
<th>logLik</th>
<th>AIC</th>
<th>BIC</th>
<th>deviance</th>
<th>df.residual</th>
</tr>
</thead>
<tbody>
<tr>
<td>0.6709785</td>
<td>0.6623953</td>
<td>0.0424483</td>
<td>78.17375</td>
<td>0</td>
<td>4</td>
<td>209.1576</td>
<td>-408.3151</td>
<td>-394.4195</td>
<td>0.2072133</td>
<td>115</td>
</tr>
</tbody>
</table>
<p>Using <code>tidy</code> from the <code>broom</code> package, we can review the model coefficients: these are the most interesting. The intercept is the alpha, and at 0.005 the portfolio is outperforming the model by approximately 0.005% per month or roughly 0.055% per year (although the p-value indicates this is not statistically significant). Next are the “loadings” for the three factors. The “mkt.rf” is the beta for the portfolio, which indicates very low volatility compared to the market (anything less than 1.0 means lower volatility than the market). The “smb” value of essentially zero signifies large-cap (anything below 0 is large cap, above 0.5 is small cap). The “hml” value of essentially zero signifies a growth fund (a zero value defines a growth portfolio, a value of more than 0.3, a value fund).</p>
<figure>
<pre><code data-lang="r"><span>tidy</span><span>(</span><span>ff_3</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre>
</figure>
<table>
<thead>
<tr>
<th>term</th>
<th>estimate</th>
<th>std.error</th>
<th>statistic</th>
<th>p.value</th>
</tr>
</thead>
<tbody>
<tr>
<td>(Intercept)</td>
<td>0.0046336</td>
<td>0.0039400</td>
<td>1.1760314</td>
<td>0.2420111</td>
</tr>
<tr>
<td>mkt.rf</td>
<td>0.0138197</td>
<td>0.0009619</td>
<td>14.3665066</td>
<td>0.0000000</td>
</tr>
<tr>
<td>smb</td>
<td>-0.0027443</td>
<td>0.0018407</td>
<td>-1.4909358</td>
<td>0.1387162</td>
</tr>
<tr>
<td>hml</td>
<td>-0.0013105</td>
<td>0.0014951</td>
<td>-0.8765756</td>
<td>0.3825446</td>
</tr>
</tbody>
</table>
<h5>Visualize Correlations</h5>
<p>We can also visualize the results using the new <code>corrr</code> package to get a sense of the relationship the portfolio returns to each of the factors. The <code>correlate()</code> function creates a correlation matrix. The <code>shave()</code> function removes the upper diagonal.</p>
<figure>
<pre><code data-lang="r"><span>portfolio_corr</span><span> </span><span>&lt;-</span><span> </span><span>portfolio_fama_french</span><span> </span><span>%>%</span><span>
</span><span>select</span><span>(</span><span>-</span><span>c</span><span>(</span><span>date</span><span>,</span><span> </span><span>rf</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>correlate</span><span>()</span><span> </span><span>%>%</span><span>
</span><span>shave</span><span>()</span><span>
</span><span># `fashion` used for pretty printing
</span><span>fashion</span><span>(</span><span>portfolio_corr</span><span>)</span><span> </span><span>%>%</span><span> </span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre>
</figure>
<table>
<thead>
<tr>
<th>rowname</th>
<th>monthly.returns</th>
<th>mkt.rf</th>
<th>smb</th>
<th>hml</th>
</tr>
</thead>
<tbody>
<tr>
<td>monthly.returns</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>mkt.rf</td>
<td>.81</td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>smb</td>
<td>.22</td>
<td>.36</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>hml</td>
<td>.22</td>
<td>.33</td>
<td>.19</td>
<td> </td>
</tr>
</tbody>
</table>
<p>Visualizing is just one more step with <code>rplot()</code> (similar to <code>ggplot() + correlation geom</code>). We can see that the market reference is highly correlated to the monthly portfolio returns, but this is the only value that has a significant correlation.</p>
<figure>
<pre><code data-lang="r"><span>portfolio_corr</span><span> </span><span>%>%</span><span>
</span><span>rplot</span><span>()</span><span> </span><span>+</span><span> </span><span>labs</span><span>(</span><span>title</span><span> </span><span>=</span><span> </span><span>&quot;Visualizing the Correlations Between FF Factors&quot;</span><span>,</span><span>
</span><span>subtitle</span><span> </span><span>=</span><span> </span><span>&quot;Highest Correlation is 'mkt.rf', other factors are less correlated&quot;</span><span>)</span><span> </span><span>+</span><span> </span><span>theme_tq</span><span>()</span></code></pre>
</figure>
<p><img alt="plot of chunk unnamed-chunk-18" src="https://i1.wp.com/www.business-science.io/figure/source/2017-3-19-tidyquant-quandl-integration/unnamed-chunk-18-1.png?w=456" data-recalc-dims="1"></p>
<h1>Conclusions <a></a></h1>
<p>The new <em>Quandl</em> integration opens up a lot of doors with regards to financial and economic data. The API is now integrated into the “tidyverse” workflow enabling scaling and easy data manipulations following the <strong>“Collect, Modify, Analyze” financial analysis workflow</strong> we use with <code>tidyquant</code>. The Fama French analysis is just one example of new and interesting analyses that are now easily performed. This is just the beginning. Feel free to <a target="_blank" rel="nofollow" href="mailto:info@business-science.io">email us at Business Science</a> with new and interesting ways you are using <code>tidyquant</code>!</p>
<h1>Recap <a></a></h1>
<p>We covered a lot of ground today. We exposed you to the new <a target="_blank" rel="nofollow" href="https://www.quandl.com/"><em>Quandl</em></a> integration and how it fits within the <strong>“Collect, Modify, Analyze” financial analysis workflow</strong>. We used <code>quandl_api_key()</code> to set an API key, enabling access to the <em>Quandl</em> API. We used <code>quandl_search()</code> to search the <em>Quandl</em> API for Fama French data. We used <code>tq_get(get = &quot;quandl&quot;)</code> to retrieve data from <em>Quandl</em>, passing various options to conveniently get monthly returns. We aggregated a portfolio using <code>tq_portfolio</code> and joined the portfolio returns with the Fama French data. We then performed a basic Fama French Three Factor analysis. The entire analysis from beginning to end was easy, efficient, and “tidy”! =)</p>
<h1>Further Reading <a></a></h1>
<p></p>
<ol>
<li>
<p><strong><a target="_blank" rel="nofollow" href="https://github.com/business-science/tidyquant/blob/master/vignettes/TQ01-core-functions-in-tidyquant.Rmd">TQ01-Core Functions in tidyquant, Development Version</a></strong>: The Core Functions in tidyquant vignette (development version) has a nice section on the features related to the <em>Quandl</em> integration!</p>
</li>
<li>
<p><strong><a target="_blank" rel="nofollow" href="http://r4ds.had.co.nz/">R for Data Science</a></strong>: A free book that thoroughly covers the “tidyverse”. A prerequisite for maximizing your abilities with <code>tidyquant</code>.</p>
</li>
</ol>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html"> business-science.io - Articles</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/RqLnBxEmHG8"></a></div>