<p>We’ve got some good stuff cooking over at <a href="http://www.business-science.io/">Business Science</a>. Yesterday, we had the fifth official release (0.5.0) of <code>tidyquant</code> to CRAN. The release includes some great new features. First, the <a href="https://www.quandl.com/">Quandl</a> integration is complete, which now enables getting Quandl data in “tidy” format. Second, we have a new mechanism to handle selecting which columns get sent to the mutation functions. The new argument name is… <code>select</code>, and it provides increased flexibility which we show off in a <code>rollapply</code> example. Finally, we have added several <code>PerformanceAnalytics</code> functions that deal with modifying returns to the mutation functions. In this post, we’ll go over a few of the new features in version 5.</p>
<h1>Quandl integration</h1>
<p>We couldn’t wait to talk about the new Quandl integration for <code>tidyquant</code>! Two weeks ago, we went ahead and released a <a href="http://www.business-science.io/code-tools/2017/03/19/tidyquant-quandl-integration.html">Quandl blog post</a> using the <a href="https://github.com/business-science/tidyquant">GitHub development version</a>. However, it is worth mentioning again now that it is officially being released. Definitely go check out that post to learn more!</p>
<p>We would like to highlight one feature that that post didn’t cover: <strong>data tables</strong>. These are data sets from Quandl that aren’t in a time series format, but are more of a treasure trove of other information. Many of them are premium datasets, meaning they cost money, but a few are free or have free samples. One of them is the Fundamentals Condensed datatable from <a href="https://www.quandl.com/databases/ZFA/documentation/database-overview">Zacks</a>. According to Quandl,</p>
<blockquote>
<p>“Updated daily, this database contains over 70 fundamental indicators, including income statement, balance sheet, cash flow line items and precalculated ratios, for over 17,000 US and Canadian Equities, including 9,000+ delisted stocks.”</p>
</blockquote>
<p>Without a premium Quandl account, we won’t be able to access all of it, but Quandl still offers a sample of the data. Let’s see how to grab this. First, we will want to set the Quandl API key linked to our account so that we have more than 50 calls per day.</p>
<pre><code><span>library</span><span>(</span><span>tidyquant</span><span>)</span><span>
</span><span>quandl_api_key</span><span>(</span><span>&quot;api-key-goes-here&quot;</span><span>)</span></code></pre><p>Now, we can use <code>tq_get(get = &quot;quandl.datatable&quot;)</code> with the <code>ZACKS/FC</code> Quandl code. There’s a lot of data here, including mailing address, company officers, and numerous fundamentals stats!</p>
<pre><code><span>FC</span><span> </span><span>&lt;-</span><span> </span><span>tq_get</span><span>(</span><span>&quot;ZACKS/FC&quot;</span><span>,</span><span> </span><span>get</span><span> </span><span>=</span><span> </span><span>&quot;quandl.datatable&quot;</span><span>)</span><span>
</span><span>FC</span><span> </span><span>%>%</span><span>
</span><span>slice</span><span>(</span><span>1</span><span>:</span><span>3</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre><table><tr>m_ticker
ticker
comp_name
comp_name_2
exchange
currency_code
per_end_date
per_type
per_code
per_fisc_year
per_fisc_qtr
per_cal_year
per_cal_qtr
data_type_ind
filing_type
qtr_nbr
zacks_sector_code
zacks_x_ind_code
zacks_metrics_ind_code
fye_month
comp_cik
per_len
sic_code
filing_date
last_changed_date
state_incorp_name
bus_address_line_1
bus_city
bus_state_name
bus_post_code
bus_phone_nbr
bus_fax_nbr
mail_address_line_1
mail_city
mail_state_name
mail_post_code
country_name
country_code
home_exchange_name
emp_cnt
emp_pt_cnt
emp_ft_cnt
emp_other_cnt
comm_share_holder
auditor
auditor_opinion
comp_url
email_addr
nbr_shares_out
shares_out_date
officer_name_1
officer_title_1
officer_name_2
officer_title_2
officer_name_3
officer_title_3
officer_name_4
officer_title_4
officer_name_5
officer_title_5
rpt_0_date
tot_revnu
cost_good_sold
gross_profit
tot_deprec_amort
int_exp_oper
int_invst_income_oper
res_dev_exp
in_proc_res_dev_exp_aggr
tot_sell_gen_admin_exp
rental_exp_ind_broker
pension_post_retire_exp
other_oper_income_exp
tot_oper_exp
oper_income
non_oper_int_exp
int_cap
asset_wdown_impair_aggr
restruct_charge
merger_acq_income_aggr
rental_income
spcl_unusual_charge
impair_goodwill
litig_aggr
gain_loss_sale_asset_aggr
gain_loss_sale_invst_aggr
stock_div_subsid
income_loss_equity_invst_other
pre_tax_minority_int
int_invst_income
other_non_oper_income_exp
tot_non_oper_income_exp
pre_tax_income
tot_provsn_income_tax
income_aft_tax
minority_int
equity_earn_subsid
invst_gain_loss_other
other_income
income_cont_oper
income_discont_oper
income_bef_exord_acct_change
exord_income_loss
cumul_eff_acct_change
consol_net_income_loss
non_ctl_int
net_income_parent_comp
pref_stock_div_other_adj
net_income_loss_share_holder
eps_basic_cont_oper
eps_basic_discont_oper
eps_basic_acct_change
eps_basic_extra
eps_basic_consol
eps_basic_parent_comp
basic_net_eps
eps_diluted_cont_oper
eps_diluted_discont_oper
eps_diluted_acct_change
eps_diluted_extra
eps_diluted_consol
eps_diluted_parent_comp
diluted_net_eps
dilution_factor
avg_d_shares
avg_b_shares
norm_pre_tax_income
norm_aft_tax_income
ebitda
ebit
rpt_1_date
cash_sterm_invst
note_loan_rcv
rcv_est_doubt
rcv_tot
invty
prepaid_expense
def_charge_curr
def_tax_asset_curr
asset_discont_oper_curr
other_curr_asset
tot_curr_asset
gross_prop_plant_equip
tot_accum_deprec
net_prop_plant_equip
net_real_estate_misc_prop
cap_software
lterm_invst
adv_dep
lterm_rcv
invty_lterm
goodwill_intang_asset_tot
def_charge_non_curr
def_tax_asset_lterm
asset_discont_oper_lterm
pension_post_retire_asset
other_lterm_asset
tot_lterm_asset
tot_asset
note_pay
acct_pay
div_pay
other_pay
accrued_exp
other_accrued_exp
curr_portion_debt
curr_portion_cap_lease
curr_portion_tax_pay
defer_revnu_curr
defer_tax_liab_curr
liab_discont_oper_curr
other_curr_liab
tot_curr_liab
tot_lterm_debt
defer_revnu_non_curr
pension_post_retire_liab
defer_tax_liab_lterm
mand_redeem_pref_sec_subsid
pref_stock_liab
min_int
liab_disc_oper_lterm
other_non_curr_liab
tot_lterm_liab
tot_liab
tot_pref_stock
comm_stock_net
addtl_paid_in_cap
retain_earn_accum_deficit
equity_equiv
treas_stock
compr_income
def_compsn
other_share_holder_equity
tot_comm_equity
tot_share_holder_equity
tot_liab_share_holder_equity
comm_shares_out
pref_stock_shares_out
tang_stock_holder_equity
rpt_2_date
net_income_loss
tot_deprec_amort_cash_flow
other_non_cash_item
tot_non_cash_item
change_acct_rcv
change_invty
change_acct_pay
change_acct_pay_accrued_liab
change_income_tax
change_asset_liab
tot_change_asset_liab
oper_activity_other
cash_flow_oper_activity
net_change_prop_plant_equip
net_change_intang_asset
net_acq_divst
net_change_sterm_invst
net_change_lterm_invst
net_change_invst_tot
invst_activity_other
cash_flow_invst_activity
net_lterm_debt
net_curr_debt
debt_issue_retire_net_tot
net_comm_equity_issued_repurch
net_pref_equity_issued_repurch
net_tot_equity_issued_repurch
tot_comm_pref_stock_div_paid
fin_activity_other
cash_flow_fin_activity
fgn_exchange_rate_adj
disc_oper_misc_cash_flow_adj
incr_decr_cash
beg_cash
end_cash
stock_based_compsn
comm_stock_div_paid
pref_stock_div_paid
tot_deprec_amort_qd
stock_based_compsn_qd
cash_flow_oper_activity_qd
net_change_prop_plant_equip_qd
comm_stock_div_paid_qd
pref_stock_div_paid_qd
tot_comm_pref_stock_div_qd
wavg_shares_out
wavg_shares_out_diluted
eps_basic_net
eps_diluted_net
</tr><tr><td>AAPL</td>
<td>AAPL</td>
<td>APPLE INC</td>
<td>Apple Inc.</td>
<td>NSDQ</td>
<td>USD</td>
<td>2011-09-30</td>
<td>A</td>
<td>NA</td>
<td>2011</td>
<td>4</td>
<td>2011</td>
<td>3</td>
<td>F</td>
<td>10-K</td>
<td>2011</td>
<td>10</td>
<td>199</td>
<td>9</td>
<td>9</td>
<td>0000320193</td>
<td>12</td>
<td>3571</td>
<td>2011-10-26</td>
<td>2011-10-26</td>
<td>CA</td>
<td>ONE INFINITE LOOP</td>
<td>CUPERTINO</td>
<td>CA</td>
<td>95014</td>
<td>408-996-1010</td>
<td>408-996-0275</td>
<td>ONE INFINITE LOOP</td>
<td>CUPERTINO</td>
<td>CA</td>
<td>95014</td>
<td>UNITED STATES</td>
<td>X1</td>
<td>NA</td>
<td>63300</td>
<td>2900</td>
<td>60400</td>
<td>NA</td>
<td>28543</td>
<td>Ernst &amp; Young LLP</td>
<td>Fair</td>
<td>www.apple.com</td>
<td>investor_relations@apple.com</td>
<td>6505863000</td>
<td>2011-10-14</td>
<td>Timothy D. Cook</td>
<td>Chief Executive Officer and Director</td>
<td>Peter Oppenheimer</td>
<td>Senior Vice President and Chief Financial Officer</td>
<td>Betsy Rafael</td>
<td>Vice President and Corporate Controller</td>
<td>William V. Campbell</td>
<td>Director</td>
<td>Millard S. Drexler</td>
<td>Director</td>
<td>2013-10-30</td>
<td>108249</td>
<td>64431</td>
<td>43818</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>2429</td>
<td>NA</td>
<td>7599</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>74459</td>
<td>33790</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>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>415</td>
<td>415</td>
<td>34205</td>
<td>8283</td>
<td>25922</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>25922</td>
<td>NA</td>
<td>25922</td>
<td>NA</td>
<td>NA</td>
<td>25922</td>
<td>NA</td>
<td>25922</td>
<td>NA</td>
<td>25922</td>
<td>4.0066</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>4.0066</td>
<td>4.0066</td>
<td>4.0071</td>
<td>3.9536</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>3.9536</td>
<td>3.9536</td>
<td>3.9543</td>
<td>4.3340</td>
<td>6556.515</td>
<td>6469.806</td>
<td>34205</td>
<td>25922</td>
<td>35604</td>
<td>33790</td>
<td>2012-10-31</td>
<td>25952</td>
<td>NA</td>
<td>NA</td>
<td>11717</td>
<td>776</td>
<td>NA</td>
<td>NA</td>
<td>2014</td>
<td>NA</td>
<td>4529</td>
<td>44988</td>
<td>11768</td>
<td>3991</td>
<td>7777</td>
<td>NA</td>
<td>NA</td>
<td>55618</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>4432</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>3556</td>
<td>71383</td>
<td>116371</td>
<td>NA</td>
<td>14632</td>
<td>NA</td>
<td>NA</td>
<td>9247</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>4091</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>27970</td>
<td>NA</td>
<td>1686</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>10100</td>
<td>11786</td>
<td>39756</td>
<td>NA</td>
<td>13331</td>
<td>NA</td>
<td>62841</td>
<td>NA</td>
<td>NA</td>
<td>443</td>
<td>NA</td>
<td>NA</td>
<td>76615</td>
<td>76615</td>
<td>116371</td>
<td>6504.939</td>
<td>NA</td>
<td>72183</td>
<td>2013-10-30</td>
<td>25922</td>
<td>1814</td>
<td>4036</td>
<td>5850</td>
<td>143</td>
<td>275</td>
<td>2515</td>
<td>NA</td>
<td>NA</td>
<td>2824</td>
<td>5757</td>
<td>NA</td>
<td>37529</td>
<td>-4260</td>
<td>-3192</td>
<td>-244</td>
<td>-32464</td>
<td>NA</td>
<td>-32464</td>
<td>-259</td>
<td>-40419</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>831</td>
<td>NA</td>
<td>831</td>
<td>NA</td>
<td>613</td>
<td>1444</td>
<td>NA</td>
<td>NA</td>
<td>-1446</td>
<td>11261</td>
<td>9815</td>
<td>1168</td>
<td>0</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>6469.806</td>
<td>6556.515</td>
<td>4.0071</td>
<td>3.9543</td>
</tr><tr><td>AAPL</td>
<td>AAPL</td>
<td>APPLE INC</td>
<td>Apple Inc.</td>
<td>NSDQ</td>
<td>USD</td>
<td>2012-09-30</td>
<td>A</td>
<td>NA</td>
<td>2012</td>
<td>4</td>
<td>2012</td>
<td>3</td>
<td>F</td>
<td>10-K</td>
<td>2012</td>
<td>10</td>
<td>199</td>
<td>9</td>
<td>9</td>
<td>0000320193</td>
<td>12</td>
<td>3571</td>
<td>2012-10-31</td>
<td>2012-10-31</td>
<td>CA</td>
<td>ONE INFINITE LOOP</td>
<td>CUPERTINO</td>
<td>CA</td>
<td>95014</td>
<td>408-996-1010</td>
<td>408-996-0275</td>
<td>ONE INFINITE LOOP</td>
<td>CUPERTINO</td>
<td>CA</td>
<td>95014</td>
<td>UNITED STATES</td>
<td>X1</td>
<td>NA</td>
<td>76100</td>
<td>3300</td>
<td>72800</td>
<td>NA</td>
<td>27696</td>
<td>Ernst &amp; Young LLP</td>
<td>Fair</td>
<td>www.apple.com</td>
<td>investor_relations@apple.com</td>
<td>6584844000</td>
<td>2012-10-19</td>
<td>Timothy D. Cook</td>
<td>Chief Executive Officer and Director</td>
<td>Peter Oppenheimer</td>
<td>Senior Vice President and Chief Financial Officer</td>
<td>William V. Campbell</td>
<td>Director</td>
<td>Millard S. Drexler</td>
<td>Director</td>
<td>Al Gore</td>
<td>Director</td>
<td>2014-10-27</td>
<td>156508</td>
<td>87846</td>
<td>68662</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>3381</td>
<td>NA</td>
<td>10040</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>101267</td>
<td>55241</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>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>522</td>
<td>522</td>
<td>55763</td>
<td>14030</td>
<td>41733</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>41733</td>
<td>NA</td>
<td>41733</td>
<td>NA</td>
<td>NA</td>
<td>41733</td>
<td>NA</td>
<td>41733</td>
<td>NA</td>
<td>41733</td>
<td>6.3776</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>6.3776</td>
<td>6.3776</td>
<td>6.3771</td>
<td>6.3065</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>6.3065</td>
<td>6.3065</td>
<td>6.3071</td>
<td>23.3164</td>
<td>6617.485</td>
<td>6543.726</td>
<td>55763</td>
<td>41733</td>
<td>58518</td>
<td>55241</td>
<td>2013-10-30</td>
<td>29129</td>
<td>NA</td>
<td>NA</td>
<td>18692</td>
<td>791</td>
<td>NA</td>
<td>NA</td>
<td>2583</td>
<td>NA</td>
<td>6458</td>
<td>57653</td>
<td>21887</td>
<td>6435</td>
<td>15452</td>
<td>NA</td>
<td>NA</td>
<td>92122</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5359</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5478</td>
<td>118411</td>
<td>176064</td>
<td>NA</td>
<td>21175</td>
<td>NA</td>
<td>NA</td>
<td>11414</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5953</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>38542</td>
<td>NA</td>
<td>2648</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>16664</td>
<td>19312</td>
<td>57854</td>
<td>NA</td>
<td>16422</td>
<td>NA</td>
<td>101289</td>
<td>NA</td>
<td>NA</td>
<td>499</td>
<td>NA</td>
<td>NA</td>
<td>118210</td>
<td>118210</td>
<td>176064</td>
<td>6574.456</td>
<td>NA</td>
<td>112851</td>
<td>2014-10-27</td>
<td>41733</td>
<td>3277</td>
<td>6145</td>
<td>9422</td>
<td>-5551</td>
<td>-15</td>
<td>4467</td>
<td>NA</td>
<td>NA</td>
<td>800</td>
<td>-299</td>
<td>NA</td>
<td>50856</td>
<td>-8295</td>
<td>-1107</td>
<td>-350</td>
<td>-38427</td>
<td>NA</td>
<td>-38427</td>
<td>-48</td>
<td>-48227</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>665</td>
<td>NA</td>
<td>665</td>
<td>-2488</td>
<td>125</td>
<td>-1698</td>
<td>NA</td>
<td>NA</td>
<td>931</td>
<td>9815</td>
<td>10746</td>
<td>1740</td>
<td>-2488</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>6543.726</td>
<td>6617.483</td>
<td>6.3800</td>
<td>6.3100</td>
</tr><tr><td>AAPL</td>
<td>AAPL</td>
<td>APPLE INC</td>
<td>Apple Inc.</td>
<td>NSDQ</td>
<td>USD</td>
<td>2013-09-30</td>
<td>A</td>
<td>NA</td>
<td>2013</td>
<td>4</td>
<td>2013</td>
<td>3</td>
<td>F</td>
<td>10-K</td>
<td>2013</td>
<td>10</td>
<td>199</td>
<td>9</td>
<td>9</td>
<td>0000320193</td>
<td>12</td>
<td>3571</td>
<td>2013-10-30</td>
<td>2013-10-29</td>
<td>CA</td>
<td>ONE INFINITE LOOP</td>
<td>CUPERTINO</td>
<td>CA</td>
<td>95014</td>
<td>408-996-1010</td>
<td>408-974-2483</td>
<td>ONE INFINITE LOOP</td>
<td>CUPERTINO</td>
<td>CA</td>
<td>95014</td>
<td>UNITED STATES</td>
<td>X1</td>
<td>NA</td>
<td>84400</td>
<td>4100</td>
<td>80300</td>
<td>NA</td>
<td>24710</td>
<td>Ernst &amp; Young LLP</td>
<td>Fair</td>
<td>www.apple.com</td>
<td>investor_relations@apple.com</td>
<td>6298166000</td>
<td>2013-10-18</td>
<td>Timothy D. Cook</td>
<td>Chief Executive Officer and Director</td>
<td>Peter Oppenheimer</td>
<td>Senior Vice President and Chief Financial Officer</td>
<td>Luca Maestri</td>
<td>Vice President and Corporate Controller</td>
<td>William V. Campbell</td>
<td>Director</td>
<td>Millard S. Drexler</td>
<td>Director</td>
<td>2015-10-28</td>
<td>170910</td>
<td>106606</td>
<td>64304</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>4475</td>
<td>NA</td>
<td>10830</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>121911</td>
<td>48999</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>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>1156</td>
<td>1156</td>
<td>50155</td>
<td>13118</td>
<td>37037</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>37037</td>
<td>NA</td>
<td>37037</td>
<td>NA</td>
<td>NA</td>
<td>37037</td>
<td>NA</td>
<td>37037</td>
<td>NA</td>
<td>37037</td>
<td>5.7180</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5.7180</td>
<td>5.7180</td>
<td>5.7186</td>
<td>5.6791</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5.6791</td>
<td>5.6791</td>
<td>5.6786</td>
<td>5.8789</td>
<td>6521.634</td>
<td>6477.317</td>
<td>50155</td>
<td>37037</td>
<td>55756</td>
<td>48999</td>
<td>2014-10-27</td>
<td>40546</td>
<td>NA</td>
<td>NA</td>
<td>20641</td>
<td>1764</td>
<td>NA</td>
<td>NA</td>
<td>3453</td>
<td>NA</td>
<td>6882</td>
<td>73286</td>
<td>28519</td>
<td>11922</td>
<td>16597</td>
<td>NA</td>
<td>NA</td>
<td>106215</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5756</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>5146</td>
<td>133714</td>
<td>207000</td>
<td>NA</td>
<td>22367</td>
<td>NA</td>
<td>NA</td>
<td>13856</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>7435</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>43658</td>
<td>16960</td>
<td>2625</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>NA</td>
<td>20208</td>
<td>39793</td>
<td>83451</td>
<td>NA</td>
<td>19764</td>
<td>NA</td>
<td>104256</td>
<td>NA</td>
<td>NA</td>
<td>-471</td>
<td>NA</td>
<td>NA</td>
<td>123549</td>
<td>123549</td>
<td>207000</td>
<td>6294.494</td>
<td>NA</td>
<td>117793</td>
<td>2015-10-28</td>
<td>37037</td>
<td>6757</td>
<td>3394</td>
<td>10151</td>
<td>-2172</td>
<td>-973</td>
<td>2340</td>
<td>NA</td>
<td>NA</td>
<td>7283</td>
<td>6478</td>
<td>NA</td>
<td>53666</td>
<td>-8165</td>
<td>-911</td>
<td>-496</td>
<td>-24042</td>
<td>NA</td>
<td>-24042</td>
<td>-160</td>
<td>-33774</td>
<td>16896</td>
<td>NA</td>
<td>16896</td>
<td>-22330</td>
<td>NA</td>
<td>-22330</td>
<td>-10564</td>
<td>-381</td>
<td>-16379</td>
<td>NA</td>
<td>NA</td>
<td>3513</td>
<td>10746</td>
<td>14259</td>
<td>2253</td>
<td>-10564</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>6477.320</td>
<td>6521.634</td>
<td>5.7200</td>
<td>5.6800</td>
</tr></table><p>Quandl is a very extensive data source. Finding the right series can sometimes be a challenge, but their documentation and <a href="https://www.quandl.com/search?query=">search</a> function has greatly improved from a few years ago. In addition, with <code>quandl_search()</code> users can even find data sets from within the R console. Let’s search for the WTI crude prices within the FRED database.</p>
<pre><code><span>quandl_search</span><span>(</span><span>query</span><span> </span><span>=</span><span> </span><span>&quot;WTI&quot;</span><span>,</span><span> </span><span>database_code</span><span> </span><span>=</span><span> </span><span>&quot;FRED&quot;</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><span> </span><span>%>%</span><span>
</span><span>slice</span><span>(</span><span>1</span><span>:</span><span>3</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre><table><tr>id
dataset_code
database_code
name
refreshed_at
newest_available_date
oldest_available_date
column_names
frequency
type
premium
database_id
</tr><tr><td>34399442</td>
<td>WTISPLC</td>
<td>FRED</td>
<td>Spot Crude Oil Price: West Texas Intermediate (WTI)</td>
<td>2017-03-18T04:39:31.083Z</td>
<td>2017-02-01</td>
<td>1946-01-01</td>
<td>DATE, VALUE</td>
<td>monthly</td>
<td>Time Series</td>
<td>FALSE</td>
<td>118</td>
</tr><tr><td>120621</td>
<td>ACOILWTICO</td>
<td>FRED</td>
<td>Crude Oil Prices: West Texas Intermediate (WTI) - Cushing, Oklahoma</td>
<td>2016-12-11T19:35:20.310Z</td>
<td>2015-01-01</td>
<td>1986-01-01</td>
<td>DATE, VALUE</td>
<td>annual</td>
<td>Time Series</td>
<td>FALSE</td>
<td>118</td>
</tr><tr><td>120619</td>
<td>MCOILWTICO</td>
<td>FRED</td>
<td>Crude Oil Prices: West Texas Intermediate (WTI) - Cushing, Oklahoma</td>
<td>2017-03-17T15:46:22.490Z</td>
<td>2017-02-01</td>
<td>1986-01-01</td>
<td>DATE, VALUE</td>
<td>monthly</td>
<td>Time Series</td>
<td>FALSE</td>
<td>118</td>
</tr></table><h1><code>select</code>ing columns: A flexible approach</h1>
<p>Previously, when selecting columns with <code>tq_mutate()</code> and <code>tq_transmute()</code>, we would use the <code>ohlc_fun</code> argument to select the columns to mutate on. <code>ohlc_fun</code> leveraged the numerous functions from <code>quantmod</code> that extract specific columns from OHLC (Open High Low Close) price data such as <code>Ad()</code> (adjusted) or <code>Cl()</code> (close). While this was great for OHLC data, it was not ideal for datasets not in the OHLC form. It also kept users from performing rolling regressions using custom functions with <code>rollapply()</code>. To fix this, we’ve come up with a new approach that replaces <code>ohlc_fun</code> through a new argument, <code>select</code>. This new approach uses <code>dplyr</code> for selecting data frame columns instead of relying on <code>quantmod</code>, and as a result it has a “tidyverse” feel. While this seems like a small change, in the following examples you will see just how much more flexibility this brings.</p>
<h2><code>select</code> VS <code>ohlc_fun</code></h2>
<p>Here’s a quick example demonstrating the difference in the approaches. Let’s first grab the stock price data for IBM.</p>
<pre><code><span>ibm</span><span> </span><span>&lt;-</span><span> </span><span>tq_get</span><span>(</span><span>&quot;IBM&quot;</span><span>)</span><span>
</span><span>ibm</span></code></pre><pre><code>## # A tibble: 2,581 × 7
##          date   open   high   low  close   volume adjusted
##        &lt;date>  &lt;dbl>  &lt;dbl> &lt;dbl>  &lt;dbl>    &lt;dbl>    &lt;dbl>
## 1  2007-01-03  97.18  98.40 96.26  97.27  9196800 77.73997
## 2  2007-01-04  97.25  98.79 96.88  98.31 10524500 78.57116
## 3  2007-01-05  97.60  97.95 96.91  97.42  7221300 77.85985
## 4  2007-01-08  98.50  99.50 98.35  98.90 10340000 79.04270
## 5  2007-01-09  99.08 100.33 99.07 100.07 11108200 79.97778
## 6  2007-01-10  98.50  99.05 97.93  98.89  8744800 79.03470
## 7  2007-01-11  99.00  99.90 98.50  98.65  8000700 78.84289
## 8  2007-01-12  98.99  99.69 98.50  99.34  6636500 79.39435
## 9  2007-01-16  99.40 100.84 99.30 100.82  9602200 80.57719
## 10 2007-01-17 100.69 100.90 99.90 100.02  8200700 79.93782
## # ... with 2,571 more rows</code></pre><p>What if you wanted to calculate the daily returns of the adjusted stock price? Previously, you might have done something like this…</p>
<pre><code><span>tq_mutate</span><span>(</span><span>ibm</span><span>,</span><span> </span><span>ohlc_fun</span><span> </span><span>=</span><span> </span><span>Ad</span><span>,</span><span> </span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>dailyReturn</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>slice</span><span>(</span><span>1</span><span>:</span><span>5</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre><pre><code>## Warning: Argument ohlc_fun is deprecated; please use select instead.</code></pre><table><tr>date
open
high
low
close
volume
adjusted
daily.returns
</tr><tr><td>2007-01-03</td>
<td>97.18</td>
<td>98.40</td>
<td>96.26</td>
<td>97.27</td>
<td>9196800</td>
<td>77.73997</td>
<td>0.0000000</td>
</tr><tr><td>2007-01-04</td>
<td>97.25</td>
<td>98.79</td>
<td>96.88</td>
<td>98.31</td>
<td>10524500</td>
<td>78.57116</td>
<td>0.0106919</td>
</tr><tr><td>2007-01-05</td>
<td>97.60</td>
<td>97.95</td>
<td>96.91</td>
<td>97.42</td>
<td>7221300</td>
<td>77.85985</td>
<td>-0.0090530</td>
</tr><tr><td>2007-01-08</td>
<td>98.50</td>
<td>99.50</td>
<td>98.35</td>
<td>98.90</td>
<td>10340000</td>
<td>79.04270</td>
<td>0.0151920</td>
</tr><tr><td>2007-01-09</td>
<td>99.08</td>
<td>100.33</td>
<td>99.07</td>
<td>100.07</td>
<td>11108200</td>
<td>79.97778</td>
<td>0.0118301</td>
</tr></table><p>At this point, <code>ohlc_fun</code> will still work, but you’ll notice that a warning appears notifying you that we’ve deprecated <code>ohlc_fun</code>. In version 6, we will remove the argument completely. Let’s see the new approach with <code>select</code>.</p>
<pre><code><span>tq_mutate</span><span>(</span><span>ibm</span><span>,</span><span> </span><span>select</span><span> </span><span>=</span><span> </span><span>adjusted</span><span>,</span><span> </span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>dailyReturn</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>slice</span><span>(</span><span>1</span><span>:</span><span>5</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre><table><tr>date
open
high
low
close
volume
adjusted
daily.returns
</tr><tr><td>2007-01-03</td>
<td>97.18</td>
<td>98.40</td>
<td>96.26</td>
<td>97.27</td>
<td>9196800</td>
<td>77.73997</td>
<td>0.0000000</td>
</tr><tr><td>2007-01-04</td>
<td>97.25</td>
<td>98.79</td>
<td>96.88</td>
<td>98.31</td>
<td>10524500</td>
<td>78.57116</td>
<td>0.0106919</td>
</tr><tr><td>2007-01-05</td>
<td>97.60</td>
<td>97.95</td>
<td>96.91</td>
<td>97.42</td>
<td>7221300</td>
<td>77.85985</td>
<td>-0.0090530</td>
</tr><tr><td>2007-01-08</td>
<td>98.50</td>
<td>99.50</td>
<td>98.35</td>
<td>98.90</td>
<td>10340000</td>
<td>79.04270</td>
<td>0.0151920</td>
</tr><tr><td>2007-01-09</td>
<td>99.08</td>
<td>100.33</td>
<td>99.07</td>
<td>100.07</td>
<td>11108200</td>
<td>79.97778</td>
<td>0.0118301</td>
</tr></table><p>As you can see, the new approach is similar to <code>dplyr::mutate</code> in that you simply specify the name of the columns that get mutated. You can even select multiple columns with the dplyr shorthand. Here we succinctly select the columns open, high, low, and close and change to a weekly periodicity. Since we change periodicity, we have to use <code>tq_transmute()</code> to drop the other columns.</p>
<pre><code><span>tq_transmute</span><span>(</span><span>ibm</span><span>,</span><span> </span><span>select</span><span> </span><span>=</span><span> </span><span>open</span><span>:</span><span>close</span><span>,</span><span> </span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>to.weekly</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>slice</span><span>(</span><span>1</span><span>:</span><span>5</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre><table><tr>date
open
high
low
close
</tr><tr><td>2007-01-05</td>
<td>97.60</td>
<td>97.95</td>
<td>96.91</td>
<td>97.42</td>
</tr><tr><td>2007-01-12</td>
<td>98.99</td>
<td>99.69</td>
<td>98.50</td>
<td>99.34</td>
</tr><tr><td>2007-01-19</td>
<td>95.00</td>
<td>96.85</td>
<td>94.55</td>
<td>96.17</td>
</tr><tr><td>2007-01-26</td>
<td>97.52</td>
<td>97.83</td>
<td>96.84</td>
<td>97.45</td>
</tr><tr><td>2007-02-02</td>
<td>99.10</td>
<td>99.73</td>
<td>98.88</td>
<td>99.17</td>
</tr></table><p>One final note. If you want to mutate the entire dataset, you can leave off the <code>select</code> argument so that it keeps its default value of <code>NULL</code> which passes the entire data frame to the mutate function.</p>
<h2>Working with non-OHLC data</h2>
<p>The <code>select</code> argument is more than a surface level change. The change from the OHLC functions allows us to work with non-OHLC data straight from <code>tq_mutate()</code>. This used to be a little awkward. Since the columns of non-OHLC data could not be selected with a <code>quantmod</code> function, we had to use <code>tq_mutate_xy()</code> to find them. The intended use of the <code>*_xy()</code> functions is for working with functions that require mutating both x and y in parallel, so this definitely wasn’t best practice. Check out this old example of working with some crude oil prices from FRED, and applying a 1 period lag.</p>
<pre><code><span>oil_prices</span><span> </span><span>&lt;-</span><span> </span><span>tq_get</span><span>(</span><span>&quot;DCOILWTICO&quot;</span><span>,</span><span> </span><span>get</span><span> </span><span>=</span><span> </span><span>&quot;economic.data&quot;</span><span>)</span><span>
</span><span>oil_prices</span></code></pre><pre><code>## # A tibble: 2,671 × 2
##          date price
##        &lt;date> &lt;dbl>
## 1  2007-01-01    NA
## 2  2007-01-02 60.77
## 3  2007-01-03 58.31
## 4  2007-01-04 55.65
## 5  2007-01-05 56.29
## 6  2007-01-08 56.08
## 7  2007-01-09 55.65
## 8  2007-01-10 53.95
## 9  2007-01-11 51.91
## 10 2007-01-12 52.96
## # ... with 2,661 more rows</code></pre><p><code>tq_mutate_xy()</code> was the only thing that worked in the old version, but it just felt wrong!</p>
<pre><code><span>tq_mutate_xy</span><span>(</span><span>oil_prices</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>price</span><span>,</span><span> </span><span>y</span><span> </span><span>=</span><span> </span><span>NULL</span><span>,</span><span> </span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>lag.xts</span><span>,</span><span> </span><span>k</span><span> </span><span>=</span><span> </span><span>1</span><span>)</span></code></pre><pre><code>## # A tibble: 2,671 × 3
##          date price lag.xts
##        &lt;date> &lt;dbl>   &lt;dbl>
## 1  2007-01-01    NA      NA
## 2  2007-01-02 60.77      NA
## 3  2007-01-03 58.31   60.77
## 4  2007-01-04 55.65   58.31
## 5  2007-01-05 56.29   55.65
## 6  2007-01-08 56.08   56.29
## 7  2007-01-09 55.65   56.08
## 8  2007-01-10 53.95   55.65
## 9  2007-01-11 51.91   53.95
## 10 2007-01-12 52.96   51.91
## # ... with 2,661 more rows</code></pre><p>Enter, <code>select</code>. This works much better!</p>
<pre><code><span>tq_mutate</span><span>(</span><span>oil_prices</span><span>,</span><span> </span><span>select</span><span> </span><span>=</span><span> </span><span>price</span><span>,</span><span> </span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>lag.xts</span><span>,</span><span> </span><span>k</span><span> </span><span>=</span><span> </span><span>1</span><span>)</span></code></pre><pre><code>## # A tibble: 2,671 × 3
##          date price lag.xts
##        &lt;date> &lt;dbl>   &lt;dbl>
## 1  2007-01-01    NA      NA
## 2  2007-01-02 60.77      NA
## 3  2007-01-03 58.31   60.77
## 4  2007-01-04 55.65   58.31
## 5  2007-01-05 56.29   55.65
## 6  2007-01-08 56.08   56.29
## 7  2007-01-09 55.65   56.08
## 8  2007-01-10 53.95   55.65
## 9  2007-01-11 51.91   53.95
## 10 2007-01-12 52.96   51.91
## # ... with 2,661 more rows</code></pre><h1>Getting things rolling with <code>rollapply</code></h1>
<p>Something that we are very excited about with this new version is the ability to perform custom rolling calculations with <code>rollapply</code>. This is a byproduct of the new <code>select</code> argument, and has some pretty cool use cases. In the example below, we will walk through a rolling CAPM analysis of Apple stock.</p>
<p>CAPM stands for Capital Asset Pricing Model, and is a way to determine the theoretically appropriate expected stock return, based on exposure to market risk. Essentially, the CAPM model performs a linear regression with Apple’s excess return as the dependent variable, and the “market’s” excess return as the independent variable. We will pull from the Fama French website for the risk free rate and the market return. Here is the regression formula for CAPM:</p>
<p>Where r_f is the risk free return, and epsilon is the normally distributed error term. The key terms of interest are alpha and beta, the coefficients estimated from the regression. Alpha is the excess return of a stock relative to a market index, and beta is a measure of the volatility of a stock in relation to the market. It would be interesting to evaluate the stability of the model, and one way of doing this is by performing a rolling regression to see how alpha and beta change throughout time.
First, we will need two datasets: Fama French factors and Apple stock returns.</p>
<h2>Fama French factors</h2>
<p>Quandl actually has a cleaned up series for the Fama French factors going back to 1926! As of writing this, the most recent data point in their weekly series is 02/24/2017, so for reproducibility purposes we will stop the series there.</p>
<pre><code><span>fama_french</span><span> </span><span>&lt;-</span><span> </span><span>tq_get</span><span>(</span><span>&quot;KFRENCH/FACTORS_W&quot;</span><span>,</span><span> </span><span>get</span><span> </span><span>=</span><span> </span><span>&quot;quandl&quot;</span><span>,</span><span> </span><span>to</span><span> </span><span>=</span><span> </span><span>&quot;2017-02-24&quot;</span><span>)</span><span>
</span><span>fama_french</span></code></pre><pre><code>## # A tibble: 5,849 × 5
##          date mkt.rf   smb   hml    rf
##        &lt;date>  &lt;dbl> &lt;dbl> &lt;dbl> &lt;dbl>
## 1  2017-02-24   0.48 -1.09 -0.46 0.009
## 2  2017-02-17   1.53 -0.54 -0.39 0.009
## 3  2017-02-10   0.88  0.14 -1.20 0.009
## 4  2017-02-03   0.15  0.41 -0.67 0.009
## 5  2017-01-27   1.15  0.22  0.59 0.009
## 6  2017-01-20  -0.31 -1.13 -0.15 0.009
## 7  2017-01-13   0.09  0.47 -1.04 0.009
## 8  2017-01-06   1.69 -0.58 -1.25 0.009
## 9  2016-12-30  -1.16 -0.11  0.13 0.006
## 10 2016-12-23   0.32  0.17  0.71 0.006
## # ... with 5,839 more rows</code></pre><p>We only need two columns for the CAPM analysis, <code>mkt.rf</code> and <code>rf</code>, so let’s select them.</p>
<pre><code><span>fama_french</span><span> </span><span>&lt;-</span><span> </span><span>fama_french</span><span> </span><span>%>%</span><span>
</span><span>select</span><span>(</span><span>date</span><span>,</span><span> </span><span>mkt.rf</span><span>,</span><span> </span><span>rf</span><span>)</span><span>
</span><span>fama_french</span></code></pre><pre><code>## # A tibble: 5,849 × 3
##          date mkt.rf    rf
##        &lt;date>  &lt;dbl> &lt;dbl>
## 1  2017-02-24   0.48 0.009
## 2  2017-02-17   1.53 0.009
## 3  2017-02-10   0.88 0.009
## 4  2017-02-03   0.15 0.009
## 5  2017-01-27   1.15 0.009
## 6  2017-01-20  -0.31 0.009
## 7  2017-01-13   0.09 0.009
## 8  2017-01-06   1.69 0.009
## 9  2016-12-30  -1.16 0.006
## 10 2016-12-23   0.32 0.006
## # ... with 5,839 more rows</code></pre><h2>Apple weekly stock returns</h2>
<p>This one is easy, we will use <code>tq_get()</code> to pull the stock prices, and then use <code>tq_transmute()</code> for the returns. For this analysis, we will pull 15 years of prices.</p>
<pre><code><span>apple_prices</span><span> </span><span>&lt;-</span><span> </span><span>tq_get</span><span>(</span><span>&quot;AAPL&quot;</span><span>,</span><span> </span><span>get</span><span> </span><span>=</span><span> </span><span>&quot;stock.prices&quot;</span><span>,</span><span> </span><span>from</span><span> </span><span>=</span><span> </span><span>&quot;2002-02-24&quot;</span><span>,</span><span> </span><span>to</span><span> </span><span>=</span><span> </span><span>&quot;2017-02-24&quot;</span><span>)</span><span>
</span><span>apple_prices</span></code></pre><pre><code>## # A tibble: 3,778 × 7
##          date  open  high   low close    volume adjusted
##        &lt;date> &lt;dbl> &lt;dbl> &lt;dbl> &lt;dbl>     &lt;dbl>    &lt;dbl>
## 1  2002-02-25 22.85 24.72 22.36 23.81 106712200 1.542406
## 2  2002-02-26 23.91 24.37 23.25 23.67  65032800 1.533337
## 3  2002-02-27 23.94 24.25 20.94 21.96 257539800 1.422564
## 4  2002-02-28 22.15 22.59 21.35 21.70 114234400 1.405721
## 5  2002-03-01 21.93 23.50 21.82 23.45  87248000 1.519086
## 6  2002-03-04 23.26 24.58 22.76 24.29  87064600 1.573501
## 7  2002-03-05 24.15 24.43 23.40 23.53  68675600 1.524268
## 8  2002-03-06 23.48 24.34 22.93 24.07  56551600 1.559249
## 9  2002-03-07 24.06 24.53 23.61 24.38  64562400 1.579331
## 10 2002-03-08 24.74 25.09 24.30 24.66  67443600 1.597469
## # ... with 3,768 more rows</code></pre><p>Now, let’s get the weekly returns. We have to use <code>tq_transmute()</code> because the periodicity changes, and we will use the <code>periodReturn()</code> function from <code>quantmod</code> as our <code>mutate_fun</code>. Lastly, we change the return to a percentage because that is the form that the Fama French factors are in.</p>
<pre><code><span>apple_return</span><span> </span><span>&lt;-</span><span> </span><span>tq_transmute</span><span>(</span><span>apple_prices</span><span>,</span><span> </span><span>select</span><span> </span><span>=</span><span> </span><span>adjusted</span><span>,</span><span> </span><span>col_rename</span><span> </span><span>=</span><span> </span><span>&quot;weekly_return&quot;</span><span>,</span><span> </span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>periodReturn</span><span>,</span><span> </span><span>period</span><span> </span><span>=</span><span> </span><span>&quot;weekly&quot;</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>mutate</span><span>(</span><span>weekly_return</span><span> </span><span>=</span><span> </span><span>weekly_return</span><span> </span><span>*</span><span> </span><span>100</span><span>)</span><span>
</span><span>apple_return</span></code></pre><pre><code>## # A tibble: 783 × 2
##          date weekly_return
##        &lt;date>         &lt;dbl>
## 1  2002-03-01    -1.5119236
## 2  2002-03-08     5.1598790
## 3  2002-03-15     1.1759853
## 4  2002-03-22    -3.4468571
## 5  2002-03-28    -1.7434935
## 6  2002-04-05     4.5205327
## 7  2002-04-12     1.2934187
## 8  2002-04-19    -0.3192103
## 9  2002-04-26    -7.8862983
## 10 2002-05-03     2.1729753
## # ... with 773 more rows</code></pre><h2>Merge the two datasets</h2>
<p>Alright, now that we have the two data sets, let’s join them together. We want to keep all of <code>apple_return</code>, and just match the <code>fama_french</code> data to it. A <code>left_join</code> will serve that purpose.</p>
<pre><code><span>joined_data</span><span> </span><span>&lt;-</span><span> </span><span>left_join</span><span>(</span><span>apple_return</span><span>,</span><span> </span><span>fama_french</span><span>)</span><span>
</span><span>joined_data</span></code></pre><pre><code>## # A tibble: 783 × 4
##          date weekly_return mkt.rf    rf
##        &lt;date>         &lt;dbl>  &lt;dbl> &lt;dbl>
## 1  2002-03-01    -1.5119236   3.79 0.033
## 2  2002-03-08     5.1598790   3.15 0.033
## 3  2002-03-15     1.1759853   0.09 0.033
## 4  2002-03-22    -3.4468571  -1.19 0.033
## 5  2002-03-28    -1.7434935  -0.04 0.033
## 6  2002-04-05     4.5205327  -2.15 0.038
## 7  2002-04-12     1.2934187  -0.51 0.038
## 8  2002-04-19    -0.3192103   1.26 0.038
## 9  2002-04-26    -7.8862983  -4.13 0.038
## 10 2002-05-03     2.1729753  -0.13 0.036
## # ... with 773 more rows</code></pre><p>Remembering that the left side of the CAPM formula is apple return minus the risk free rate, we calculate that as well.</p>
<pre><code><span>joined_data</span><span> </span><span>&lt;-</span><span> </span><span>mutate</span><span>(</span><span>joined_data</span><span>,</span><span> </span><span>weekly_ret_rf</span><span> </span><span>=</span><span> </span><span>weekly_return</span><span> </span><span>-</span><span> </span><span>rf</span><span>)</span><span>
</span><span>joined_data</span></code></pre><pre><code>## # A tibble: 783 × 5
##          date weekly_return mkt.rf    rf weekly_ret_rf
##        &lt;date>         &lt;dbl>  &lt;dbl> &lt;dbl>         &lt;dbl>
## 1  2002-03-01    -1.5119236   3.79 0.033    -1.5449236
## 2  2002-03-08     5.1598790   3.15 0.033     5.1268790
## 3  2002-03-15     1.1759853   0.09 0.033     1.1429853
## 4  2002-03-22    -3.4468571  -1.19 0.033    -3.4798571
## 5  2002-03-28    -1.7434935  -0.04 0.033    -1.7764935
## 6  2002-04-05     4.5205327  -2.15 0.038     4.4825327
## 7  2002-04-12     1.2934187  -0.51 0.038     1.2554187
## 8  2002-04-19    -0.3192103   1.26 0.038    -0.3572103
## 9  2002-04-26    -7.8862983  -4.13 0.038    -7.9242983
## 10 2002-05-03     2.1729753  -0.13 0.036     2.1369753
## # ... with 773 more rows</code></pre><h2>Rolling CAPM</h2>
<p>We are almost ready for the rolling CAPM. The last thing we need is a function that is applied at each iteration of our roll, which here will be a regression. We are really only interested in the coefficients from the regression, so that is what we will return. Note that the incoming <code>data_xts</code> will be an <code>xts</code> object, so we need to convert to a <code>data.frame</code> in the <code>lm()</code> function. The output should be a numeric vector. <code>tq_mutate</code> will take care of adding the appropriate columns provided the custom function (in our case <code>regr_fun()</code>) is handled in this manner.</p>
<pre><code><span>regr_fun</span><span> </span><span>&lt;-</span><span> </span><span>function</span><span>(</span><span>data_xts</span><span>)</span><span> </span><span>{</span><span>
</span><span>lm</span><span>(</span><span>weekly_ret_rf</span><span> </span><span>~</span><span> </span><span>mkt.rf</span><span>,</span><span> </span><span>data</span><span> </span><span>=</span><span> </span><span>as_data_frame</span><span>(</span><span>data_xts</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>coef</span><span>()</span><span>
</span><span>}</span></code></pre><p>Now let’s work some magic. We will leave <code>select</code> out of this call to <code>tq_mutate()</code> since we can just pass the entire dataset into <code>rollapply</code>. To have enough data for a good analysis, we will set <code>width</code> equal to 260 for a 5 year rolling window of data per call. The final argument, <code>by.column</code>, is especially important so that <code>rollapply</code> doesn’t try and apply our regression to each column individually.</p>
<pre><code><span>joined_data</span><span> </span><span>&lt;-</span><span> </span><span>joined_data</span><span> </span><span>%>%</span><span> </span><span>tq_mutate</span><span>(</span><span>mutate_fun</span><span> </span><span>=</span><span> </span><span>rollapply</span><span>,</span><span>
</span><span>width</span><span> </span><span>=</span><span> </span><span>260</span><span>,</span><span>
</span><span>FUN</span><span> </span><span>=</span><span> </span><span>regr_fun</span><span>,</span><span>
</span><span>by.column</span><span> </span><span>=</span><span> </span><span>FALSE</span><span>,</span><span>
</span><span>col_rename</span><span> </span><span>=</span><span> </span><span>c</span><span>(</span><span>&quot;alpha&quot;</span><span>,</span><span> </span><span>&quot;beta&quot;</span><span>))</span><span>
</span><span>joined_data</span><span> </span><span>%>%</span><span>
</span><span>slice</span><span>(</span><span>255</span><span>:</span><span>265</span><span>)</span></code></pre><pre><code>## # A tibble: 11 × 7
##          date weekly_return mkt.rf    rf weekly_ret_rf     alpha
##        &lt;date>         &lt;dbl>  &lt;dbl> &lt;dbl>         &lt;dbl>     &lt;dbl>
## 1  2007-01-12    11.2522154   1.73 0.111    11.1412154        NA
## 2  2007-01-19    -6.4679733  -0.31 0.111    -6.5789733        NA
## 3  2007-01-26    -3.5254340  -0.57 0.111    -3.6364340        NA
## 4  2007-02-02    -0.7378731   1.94 0.096    -0.8338731        NA
## 5  2007-02-09    -1.7463114  -0.65 0.096    -1.8423114        NA
## 6  2007-02-16     1.8734247   1.22 0.096     1.7774247 0.6905407
## 7  2007-02-23     4.9982362  -0.05 0.096     4.9022362 0.7304938
## 8  2007-03-02    -4.1091364  -4.62 0.106    -4.2151364 0.7331868
## 9  2007-03-09     2.9973031   1.00 0.106     2.8913031 0.7353908
## 10 2007-03-16     1.8415416  -1.18 0.106     1.7355416 0.7560794
## 11 2007-03-23     4.3866501   3.48 0.106     4.2806501 0.7626701
## # ... with 1 more variables: beta &lt;dbl></code></pre><p>We had to scroll down to row 260 before we find any values for alpha and beta, but it’s more useful to create a plot. Looking at the rolling coefficient for alpha, it seems to be steadily trending downward over time.</p>
<pre><code><span>filter</span><span>(</span><span>joined_data</span><span>,</span><span> </span><span>!</span><span>is.na</span><span>(</span><span>alpha</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>date</span><span>,</span><span> </span><span>y</span><span> </span><span>=</span><span> </span><span>alpha</span><span>))</span><span> </span><span>+</span><span> </span><span>geom_line</span><span>(</span><span>size</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>()[[</span><span>1</span><span>]])</span><span> </span><span>+</span><span> </span><span>geom_smooth</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;AAPL: 5 year rolling CAPM - alpha&quot;</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>,</span><span>
</span><span>subtitle</span><span> </span><span>=</span><span> </span><span>&quot;Rolling alpha is steadily trending downward&quot;</span><span>)</span><span> </span><span>+</span><span>
</span><span>theme_tq</span><span>(</span><span>base_size</span><span> </span><span>=</span><span> </span><span>18</span><span>)</span></code></pre><p></p>
<p>The rolling coefficient for beta is just as interesting, with a huge drop around 2008 likely having to deal with the financial crisis, but consistently over or near 1.</p>
<pre><code><span>filter</span><span>(</span><span>joined_data</span><span>,</span><span> </span><span>!</span><span>is.na</span><span>(</span><span>alpha</span><span>))</span><span> </span><span>%>%</span><span>
</span><span>ggplot</span><span>(</span><span>aes</span><span>(</span><span>x</span><span> </span><span>=</span><span> </span><span>date</span><span>,</span><span> </span><span>y</span><span> </span><span>=</span><span> </span><span>beta</span><span>))</span><span> </span><span>+</span><span> </span><span>geom_rect</span><span>(</span><span>xmin</span><span> </span><span>=</span><span> </span><span>as.numeric</span><span>(</span><span>ymd</span><span>(</span><span>&quot;2008-01-01&quot;</span><span>)),</span><span> </span><span>xmax</span><span> </span><span>=</span><span> </span><span>as.numeric</span><span>(</span><span>ymd</span><span>(</span><span>&quot;2009-03-01&quot;</span><span>)),</span><span> </span><span>ymin</span><span> </span><span>=</span><span> </span><span>0</span><span>,</span><span> </span><span>ymax</span><span> </span><span>=</span><span> </span><span>2.0</span><span>,</span><span> </span><span>fill</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>()[[</span><span>4</span><span>]],</span><span> </span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.01</span><span>)</span><span> </span><span>+</span><span> </span><span>geom_line</span><span>(</span><span>size</span><span> </span><span>=</span><span> </span><span>1</span><span>,</span><span> </span><span>color</span><span> </span><span>=</span><span> </span><span>palette_light</span><span>()[[</span><span>1</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;AAPL: 5 year rolling CAPM - beta&quot;</span><span>,</span><span> </span><span>x</span><span> </span><span>=</span><span> </span><span>&quot;&quot;</span><span>,</span><span>
</span><span>subtitle</span><span> </span><span>=</span><span> </span><span>&quot;Huge drop in 2008 following financial crisis&quot;</span><span>)</span><span> </span><span>+</span><span> </span><span>theme_tq</span><span>(</span><span>base_size</span><span> </span><span>=</span><span> </span><span>18</span><span>)</span></code></pre><p></p>
<p>Hopefully you can see the power that <code>tidyquant</code> gives to the user by allowing them to perform these rolling calculations with data frames!</p>
<p>As an aside, there are some improvements to this model that could be of interest to some users:</p>
<ol><li>
<p>This says nothing to how well the CAPM model fits Apple’s return data. It only monitors the stability of the coefficients.</p>
</li>
<li>
<p>The <code>mkt.rf</code> data pulled from the Fama-French data set was only one part of a more detailed extension of CAPM, the Fama French 3 Factor Model. It would be interesting the stability of this model over time as well.</p>
</li>
</ol><h1>PerformanceAnalytics Mutation Functions</h1>
<p>We have added a few new functions from the <code>PerformanceAnalytics</code> package to <code>tq_mutate()</code> and <code>tq_transmute()</code>, which can be reviewed using <code>tq_mutate_fun_options()$PerformanceAnalytics</code>. These differ from the <code>PerformanceAnalytics</code> functions available for use with <code>tq_performance()</code> (refer to <code>tq_performance_fun_options()</code>) in that the deal with transforming the returns data rather than calculating performance metrics.</p>
<pre><code><span>tq_mutate_fun_options</span><span>()</span><span>$</span><span>PerformanceAnalytics</span></code></pre><pre><code>## [1] &quot;Return.annualized&quot;        &quot;Return.annualized.excess&quot;
## [3] &quot;Return.clean&quot;             &quot;Return.cumulative&quot;       
## [5] &quot;Return.excess&quot;            &quot;Return.Geltner&quot;          
## [7] &quot;zerofill&quot;</code></pre><p>We can use the functions to clean and format returns for the <code>FANG</code> data set. The example below uses three progressive applications of <code>tq_transmute()</code> to apply various quant functions to the grouped stock prices from the FANG data set. First, we calculate daily returns using <code>periodReturn</code> from the <code>quantmod</code> package. Next, we use <code>Return.clean</code> to clean outliers from the return data. The <code>alpha</code> parameter is the percentage of outliers to be cleaned. Finally, <code>Return.excess</code> is used to calculate the excess returns using a risk-free rate of 3% (divided by 252 for 252 trade days in one year). Finally, we calculate general statistics on the returns by passing to <code>tq_performance(performance_fun = table.Stats)</code>.</p>
<pre><code><span>FANG</span><span> </span><span>%>%</span><span>
</span><span>group_by</span><span>(</span><span>symbol</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>tq_transmute</span><span>(</span><span>adjusted</span><span>,</span><span> </span><span>periodReturn</span><span>,</span><span> </span><span>period</span><span> </span><span>=</span><span> </span><span>&quot;daily&quot;</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>tq_transmute</span><span>(</span><span>daily.returns</span><span>,</span><span> </span><span>Return.clean</span><span>,</span><span> </span><span>alpha</span><span> </span><span>=</span><span> </span><span>0.05</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>tq_transmute</span><span>(</span><span>daily.returns</span><span>,</span><span> </span><span>Return.excess</span><span>,</span><span> </span><span>Rf</span><span> </span><span>=</span><span> </span><span>0.03</span><span> </span><span>/</span><span> </span><span>252</span><span>,</span><span>
</span><span>col_rename</span><span> </span><span>=</span><span> </span><span>&quot;formatted.returns&quot;</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>tq_performance</span><span>(</span><span>Ra</span><span> </span><span>=</span><span> </span><span>formatted.returns</span><span>,</span><span> </span><span>performance_fun</span><span> </span><span>=</span><span> </span><span>table.Stats</span><span>)</span><span> </span><span>%>%</span><span>
</span><span>knitr</span><span>::</span><span>kable</span><span>()</span></code></pre><table><tr>symbol
ArithmeticMean
GeometricMean
Kurtosis
LCLMean(0.95)
Maximum
Median
Minimum
NAs
Observations
Quartile1
Quartile3
SEMean
Skewness
Stdev
UCLMean(0.95)
Variance
</tr><tr><td>FB</td>
<td>0.0015</td>
<td>0.0013</td>
<td>35.2725</td>
<td>2e-04</td>
<td>0.2960</td>
<td>1e-03</td>
<td>-0.0695</td>
<td>0</td>
<td>1008</td>
<td>-0.0098</td>
<td>0.0119</td>
<td>7e-04</td>
<td>2.9098</td>
<td>0.0220</td>
<td>0.0029</td>
<td>0.0005</td>
</tr><tr><td>AMZN</td>
<td>0.0011</td>
<td>0.0009</td>
<td>9.6476</td>
<td>-1e-04</td>
<td>0.1412</td>
<td>6e-04</td>
<td>-0.1101</td>
<td>0</td>
<td>1008</td>
<td>-0.0082</td>
<td>0.0111</td>
<td>6e-04</td>
<td>0.4804</td>
<td>0.0194</td>
<td>0.0023</td>
<td>0.0004</td>
</tr><tr><td>NFLX</td>
<td>0.0026</td>
<td>0.0021</td>
<td>35.9090</td>
<td>6e-04</td>
<td>0.4221</td>
<td>-1e-04</td>
<td>-0.1938</td>
<td>0</td>
<td>1008</td>
<td>-0.0119</td>
<td>0.0149</td>
<td>1e-03</td>
<td>3.0745</td>
<td>0.0324</td>
<td>0.0046</td>
<td>0.0011</td>
</tr><tr><td>GOOG</td>
<td>0.0007</td>
<td>0.0006</td>
<td>22.1306</td>
<td>-2e-04</td>
<td>0.1604</td>
<td>0e+00</td>
<td>-0.0533</td>
<td>0</td>
<td>1008</td>
<td>-0.0067</td>
<td>0.0078</td>
<td>5e-04</td>
<td>2.2165</td>
<td>0.0148</td>
<td>0.0017</td>
<td>0.0002</td>
</tr></table><p>Getting, cleaning and calculating excess returns is that easy with the additional <code>PerformanceAnalytics</code> mutation functions.</p>
<h1>Further Reading <a></a></h1>
<p>That’s all for now. We hope you enjoy the new release, and be on the lookout for more to come!</p>
<ol><li>
<p><strong><a href="https://cran.r-project.org/package=tidyquant">Tidyquant Vignettes</a></strong>: The Core Functions in tidyquant vignette has a nice section on the features related to the Quandl integration! The R Quantitative Analysis Package Integrations in tidyquant vignette includes another example of working with <code>rollapply</code>.</p>
</li>
<li>
<p><strong><a 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>