We’ve got some good stuff cooking over at Business Science. Yesterday, we had the fifth official release (0.5.0) of tidyquant to CRAN. The release includes some great new features. First, the Quandl 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… select, and it provides increased flexibility which we show off in a rollapply example. Finally, we have added several PerformanceAnalytics 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.

Quandl integration

We couldn’t wait to talk about the new Quandl integration for tidyquant! Two weeks ago, we went ahead and released a Quandl blog post using the GitHub development version. However, it is worth mentioning again now that it is officially being released. Definitely go check out that post to learn more!

We would like to highlight one feature that that post didn’t cover: data tables. 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 Zacks. According to Quandl,

“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.”

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.

library(tidyquant)
quandl_api_key("api-key-goes-here")

Now, we can use tq_get(get = "quandl.datatable") with the ZACKS/FC Quandl code. There’s a lot of data here, including mailing address, company officers, and numerous fundamentals stats!

FC <- tq_get("ZACKS/FC", get = "quandl.datatable")
FC %>%
    slice(1:3) %>%
    knitr::kable()

Quandl is a very extensive data source. Finding the right series can sometimes be a challenge, but their documentation and search function has greatly improved from a few years ago. In addition, with quandl_search() users can even find data sets from within the R console. Let’s search for the WTI crude prices within the FRED database.

quandl_search(query = "WTI", database_code = "FRED", silent = TRUE) %>%
    select(-description) %>%
    slice(1:3) %>%
    knitr::kable()

selecting columns: A flexible approach

Previously, when selecting columns with tq_mutate() and tq_transmute(), we would use the ohlc_fun argument to select the columns to mutate on. ohlc_fun leveraged the numerous functions from quantmod that extract specific columns from OHLC (Open High Low Close) price data such as Ad() (adjusted) or Cl() (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 rollapply(). To fix this, we’ve come up with a new approach that replaces ohlc_fun through a new argument, select. This new approach uses dplyr for selecting data frame columns instead of relying on quantmod, 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.

select VS ohlc_fun

Here’s a quick example demonstrating the difference in the approaches. Let’s first grab the stock price data for IBM.

ibm <- tq_get("IBM")
ibm

## # A tibble: 2,581 × 7
##          date   open   high   low  close   volume adjusted
##        <date>  <dbl>  <dbl> <dbl>  <dbl>    <dbl>    <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

What if you wanted to calculate the daily returns of the adjusted stock price? Previously, you might have done something like this…

tq_mutate(ibm, ohlc_fun = Ad, mutate_fun = dailyReturn) %>%
    slice(1:5) %>%
    knitr::kable()

## Warning: Argument ohlc_fun is deprecated; please use select instead.

At this point, ohlc_fun will still work, but you’ll notice that a warning appears notifying you that we’ve deprecated ohlc_fun. In version 6, we will remove the argument completely. Let’s see the new approach with select.

tq_mutate(ibm, select = adjusted, mutate_fun = dailyReturn) %>%
    slice(1:5) %>%
    knitr::kable()

As you can see, the new approach is similar to dplyr::mutate 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 tq_transmute() to drop the other columns.

tq_transmute(ibm, select = open:close, mutate_fun = to.weekly) %>%
    slice(1:5) %>%
    knitr::kable()

One final note. If you want to mutate the entire dataset, you can leave off the select argument so that it keeps its default value of NULL which passes the entire data frame to the mutate function.

Working with non-OHLC data

The select argument is more than a surface level change. The change from the OHLC functions allows us to work with non-OHLC data straight from tq_mutate(). This used to be a little awkward. Since the columns of non-OHLC data could not be selected with a quantmod function, we had to use tq_mutate_xy() to find them. The intended use of the *_xy() 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.

oil_prices <- tq_get("DCOILWTICO", get = "economic.data")
oil_prices

## # A tibble: 2,671 × 2
##          date price
##        <date> <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

tq_mutate_xy() was the only thing that worked in the old version, but it just felt wrong!

tq_mutate_xy(oil_prices, x = price, y = NULL, mutate_fun = lag.xts, k = 1)

## # A tibble: 2,671 × 3
##          date price lag.xts
##        <date> <dbl>   <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

Enter, select. This works much better!

tq_mutate(oil_prices, select = price, mutate_fun = lag.xts, k = 1)

## # A tibble: 2,671 × 3
##          date price lag.xts
##        <date> <dbl>   <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

Getting things rolling with rollapply

Something that we are very excited about with this new version is the ability to perform custom rolling calculations with rollapply. This is a byproduct of the new select argument, and has some pretty cool use cases. In the example below, we will walk through a rolling CAPM analysis of Apple stock.

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:

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.

Fama French factors

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.

fama_french <- tq_get("KFRENCH/FACTORS_W", get = "quandl", to = "2017-02-24")
fama_french

## # A tibble: 5,849 × 5
##          date mkt.rf   smb   hml    rf
##        <date>  <dbl> <dbl> <dbl> <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

We only need two columns for the CAPM analysis, mkt.rf and rf, so let’s select them.

fama_french <- fama_french %>%
    select(date, mkt.rf, rf)
fama_french

## # A tibble: 5,849 × 3
##          date mkt.rf    rf
##        <date>  <dbl> <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

Apple weekly stock returns

This one is easy, we will use tq_get() to pull the stock prices, and then use tq_transmute() for the returns. For this analysis, we will pull 15 years of prices.

apple_prices <- tq_get("AAPL", 
                       get  = "stock.prices", 
                       from = "2002-02-24", 
                       to   = "2017-02-24")
apple_prices

## # A tibble: 3,778 × 7
##          date  open  high   low close    volume adjusted
##        <date> <dbl> <dbl> <dbl> <dbl>     <dbl>    <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

Now, let’s get the weekly returns. We have to use tq_transmute() because the periodicity changes, and we will use the periodReturn() function from quantmod as our mutate_fun. Lastly, we change the return to a percentage because that is the form that the Fama French factors are in.

apple_return <- tq_transmute(apple_prices, 
                             select     = adjusted, 
                             col_rename = "weekly_return", 
                             mutate_fun = periodReturn, 
                             period     = "weekly") %>%
    mutate(weekly_return = weekly_return * 100)
apple_return

## # A tibble: 783 × 2
##          date weekly_return
##        <date>         <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

Merge the two datasets

Alright, now that we have the two data sets, let’s join them together. We want to keep all of apple_return, and just match the fama_french data to it. A left_join will serve that purpose.

joined_data <- left_join(apple_return, fama_french)
joined_data

## # A tibble: 783 × 4
##          date weekly_return mkt.rf    rf
##        <date>         <dbl>  <dbl> <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

Remembering that the left side of the CAPM formula is apple return minus the risk free rate, we calculate that as well.

joined_data <- mutate(joined_data, 
       weekly_ret_rf = weekly_return - rf)
joined_data

## # A tibble: 783 × 5
##          date weekly_return mkt.rf    rf weekly_ret_rf
##        <date>         <dbl>  <dbl> <dbl>         <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

Rolling CAPM

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 data_xts will be an xts object, so we need to convert to a data.frame in the lm() function. The output should be a numeric vector. tq_mutate will take care of adding the appropriate columns provided the custom function (in our case regr_fun()) is handled in this manner.

regr_fun <- function(data_xts) {
    lm(weekly_ret_rf ~ mkt.rf, data = as_data_frame(data_xts)) %>%
        coef()
}

Now let’s work some magic. We will leave select out of this call to tq_mutate() since we can just pass the entire dataset into rollapply. To have enough data for a good analysis, we will set width equal to 260 for a 5 year rolling window of data per call. The final argument, by.column, is especially important so that rollapply doesn’t try and apply our regression to each column individually.

joined_data <- joined_data %>% 
    tq_mutate(mutate_fun = rollapply,
              width      = 260,
              FUN        = regr_fun,
              by.column  = FALSE,
              col_rename = c("alpha", "beta"))
joined_data %>%
    slice(255:265)

## # A tibble: 11 × 7
##          date weekly_return mkt.rf    rf weekly_ret_rf     alpha
##        <date>         <dbl>  <dbl> <dbl>         <dbl>     <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 <dbl>

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.

filter(joined_data, !is.na(alpha)) %>%
ggplot(aes(x = date, y = alpha)) + 
    geom_line(size = 1, color = palette_light()[[1]]) + 
    geom_smooth() +
    labs(title = "AAPL: 5 year rolling CAPM - alpha", x = "",
         subtitle = "Rolling alpha is steadily trending downward") +
    theme_tq(base_size = 18)

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.

filter(joined_data, !is.na(alpha)) %>%
ggplot(aes(x = date, y = beta))  + 
    geom_rect(xmin = as.numeric(ymd("2008-01-01")), 
              xmax = as.numeric(ymd("2009-03-01")), 
              ymin = 0, ymax = 2.0, 
              fill = palette_light()[[4]], alpha = 0.01) + 
    geom_line(size = 1, color = palette_light()[[1]]) +
    labs(title = "AAPL: 5 year rolling CAPM - beta", x = "",
         subtitle = "Huge drop in 2008 following financial crisis") + 
    theme_tq(base_size = 18)

Hopefully you can see the power that tidyquant gives to the user by allowing them to perform these rolling calculations with data frames!

As an aside, there are some improvements to this model that could be of interest to some users:

1. This says nothing to how well the CAPM model fits Apple’s return data. It only monitors the stability of the coefficients.

2. The mkt.rf 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.

PerformanceAnalytics Mutation Functions

We have added a few new functions from the PerformanceAnalytics package to tq_mutate() and tq_transmute(), which can be reviewed using tq_mutate_fun_options()$PerformanceAnalytics. These differ from the PerformanceAnalytics functions available for use with tq_performance() (refer to tq_performance_fun_options()) in that the deal with transforming the returns data rather than calculating performance metrics.

tq_mutate_fun_options()$PerformanceAnalytics

## [1] "Return.annualized"        "Return.annualized.excess"
## [3] "Return.clean"             "Return.cumulative"       
## [5] "Return.excess"            "Return.Geltner"          
## [7] "zerofill"

We can use the functions to clean and format returns for the FANG data set. The example below uses three progressive applications of tq_transmute() to apply various quant functions to the grouped stock prices from the FANG data set. First, we calculate daily returns using periodReturn from the quantmod package. Next, we use Return.clean to clean outliers from the return data. The alpha parameter is the percentage of outliers to be cleaned. Finally, Return.excess 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 tq_performance(performance_fun = table.Stats).

FANG %>%
    group_by(symbol) %>%
    tq_transmute(adjusted, periodReturn, period = "daily") %>%
    tq_transmute(daily.returns, Return.clean, alpha = 0.05) %>%
    tq_transmute(daily.returns, Return.excess, Rf = 0.03 / 252,
                 col_rename = "formatted.returns") %>%
    tq_performance(Ra = formatted.returns, 
                   performance_fun = table.Stats) %>%
    knitr::kable()

Getting, cleaning and calculating excess returns is that easy with the additional PerformanceAnalytics mutation functions.

Further Reading

That’s all for now. We hope you enjoy the new release, and be on the lookout for more to come!

1. Tidyquant Vignettes: 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 rollapply.

2. R for Data Science: A free book that thoroughly covers the “tidyverse”. A prerequisite for maximizing your abilities with tidyquant.