This is the “R for Excel Users” release. My aim is to build functionality that helps users coming from an Excel Background (background I came from). It’s important to have these users feel at home. I have a full suite of functionality to accomplish your Excel-to-R transition.
-Matt
Excel Functions
tidyverse and “tidy- finance / business analysis” in R.pivot_table() - A tidyverse-style function to perform data summarizations just like the popular Excel Pivot Table. Enables stacking calculations using a tidy-esque syntax: .rows = ~ YEAR(order_date).VLOOKUP() - Performs the classic Excel VLOOKUP. Excel user’s: rejoice.SUM_IFS(), COUNT_IFS(), AVERAGE_IFS()CREATE_IFS() to make your own by supplying a summarization function.SUM(), AVERAGE(), COUNT(), and friends.CHANGE(), PCT_CHANGE(), LAG(), CUMULATIVE_SUM(), and friends.lubridate() (e.g. AS_DATE(), YEAR())timeDate (e.g. HOLIDAY_SEQUENCE(), HOLIDAY_LIST())NET_WORKDAYS(), EOMONTH()NPV(), IRR(), FV(), PV(), PMT(), RATE()NEW Tidyverse Functionality
summarise_by_time() - This is a new time-based variant of summarise() that allows collapsing the time-series by “day”, “week”, “month”, “quarter”, “year”, and more.summarise_at_by_time(), summarise_all_by_time(), and summarise_if_by_time() after the release of dplyr v1.0.0.NEW API Integrations
riingo package.Bug Fixes & Improvements
theme_tq() - Fix issues with collisions with dials::margin() and ggplot2::margin(). Similar potential ggplot2 collisions have been fixed.theme_tq() - Increased default top/bottom text margin on facet stripsDeprecation & Breaking Changes
tq_get("AAPL") returns symbol = “AAPL” for the 1st column).tq_get("AAPL", get = c("stock.prices", "stock.prices.japan"))) is no longer available. Solution: Split these up into two calls to tq_get().tq_get() - Temporarily adjust tests for tq_get(get = "dividends") and tq_get(get = "splits") until API is stabilizes. Yahoo! Dividends and Splits intermitently returns errors.tq_stocklist to ?tq_index.tq_index()
tq_index() download issue. Note that “RUSSEL1000”, “RUSSELL2000”, “RUSSELL3000”, and “SP1000” are no longer available due to changes from www.us.spdrs.com.tq_index() - Fix naming issue with stock index data downloaded from www.us.spdrs.com.Stock Index & Exchanges
tq_exchange() - Fix NASDAQ URL change Issue #138.Visualizations & Color Palettes
geom_candlestick and geom_barchart - Issue #112.theme_tq palettes (palette_light, palette_dark, and palette_green) for easier identification.Compatability with tidyr v1.0.0
tidyr v1.0.0[Potential Breaking Change] Move tidyverse to suggests
tidyverse in your scripts) - if you do not load tidyverse, then you will now need to do so. Previously tidyquant loaded tidyverse behind the scenes.Morningstar Key Ratios: The tq_get() argument get = "key.ratios" has been deprecated due to a change in Morningstar’s website. (Help Wanted - Ref. Issue #125)
Remove dependency on XLConnect. Replace with readxl. Issue #119.
Bux fix
tq_get() get = "financials" now returns a warning and NA as Google Finance no longer provides data. We are actively looking for alternative data sources.
tq_get() get = "stock.prices.google" now returns a warning and NA as Google Finance no longer provides data. Use get = "stock.prices" instead to use Yahoo Finance, or use the riingo package to download from Tiingo.
Catch duplicate names in col_rename when you are renaming more than 1 column. Duplicate names are not allowed and return an error.
Fix duplicate name collision issue when the original name already includes a .. Duplicate names now get a ..1, ..2, etc. as opposed to .1, .2.
alphavantager, a lightweight API to the Alpha Vantage financial data provider.Rblpapi, R interface to “Bloomberg”. You must have a Bloomberg account to use this.tq_get(get = "stock.prices.google")tq_get(get = "key.stats"). Yahoo Finance no longer supports the Key Statistics CSV API.tidyquant::as_tibble() and tidyquant::as_xts(). Use timetk::tk_tbl() and timetk::tk_xts() instead.tibbletime support was added so that all tidyquant functions play nicely with tbl_time objects.XLConnect was removed. This should ease the use of the package, especially for Mac users.testthat 2.0. They have been updated.purrr v0.2.3.timetk coercion functions. Deprecated tidyquant::as_xts() and tidyquant::as_tibble(). Use timetk::tk_xts() and timetk::tk_tbl() instead.tq_index() no longer pulls from marketvolume. Instead, 9 indices are available from SPDR. These indices are more reliable, and include weights for each stock in the index.tq_get(get = "stock.prices") were 1 or 2 rows off of what the tests expected. This likely has to do with the new yahoo finance API.pkgdown integration.quantmod version 0.4-8 to fix Oanda and Yahoo bugs.tq_get() data, it now returns oldest to newest.tq_portfolio() where weights = NULL would not execute an equal weighting scheme.tq_get(get = "quandl") is a wrapper for Quandl::Quandl() that pulls multiple Quandl Codes in a “tidy” fashion.tq_get(get = "quandl.datatable") is a wrapper for Quandl::Quandl.datable() that pulls Quandl datatables.quandl_api_key() is a wrapper for Quandl::Quandl.api_key().quandl_search is a wrapper for Quandl::Quandl.search().tq_get(get = "stock.prices.japan") is a wrapper for quantmod::getSymbols(src = "yahooj") that enables getting stocks from Yahoo Finance Japan.tq_mutate() and tq_transmute() now accept non-OHLC data through the select argument. They also now work with rollapply.tq_mutate() and tq_transmute() now accept PerformanceAnalytics functions that work to clean and transform asset returns.ohlc_fun argument to instead use select in tq_mutate() and tq_transmute.Deprecated -> .Defunct for tq_transform() and tq_transform_xy(). Use tq_transmute() and tq_transmute_xy(). Move the sign post functions to deprecated.Rtransform_fun from tq_transmute(). Use mutate_fun instead.tq_mutate returning rows incorrectly sortedtq_get returning data frames as nestedtq_get error to return full error when issues are present.tq_transmute() replaces tq_transform() for consistency with dplyr.tq_performance() which integrates the performance analysis functions of PerformanceAnalytics.tq_portfolio() which enables aggregating portfolios from individual stock returns.tq_tranform(): Added the NA-handling functions from zoo to the list of compatible, which provide a number of useful methods for handling NA values in data sets. Added Return.calculate and Return.excess for calculating returns and returns in excess of the risk-free rate, respectively.tq_mutate() and tq_transmute() help pages have been combined.tidyquant users!tq_exchange() gets the stock list for NASDAQ, NYSE, and AMEX exchanges. Use tq_exchange_options() to exchange options.FANG data set that can be loaded with data(FANG).ggplot2:
palette_() functions used to create scales are exported.theme_tq() creates light, dark, and green themes for tidyquant visualizations.scale_color_tq() and scale_fill_tq() add color/fill scales for the data used in tidyquant visualizations.transform_fun argument of tq_transmute() has been replaced with mutate_fun for consistency with tq_mutate().get = key.ratios failing with HTTP 500 error on download. Use httr RETRY in case of failure.get = "key.ratios" where stocks listed on AMEX exchange were not able to return key ratios.get = "key.stats" where NA’s in multiple x (e.g. c("AAPL", "GOOG")) cause call to fail during coercion.tq_get("AAPL", get = c("stock.prices", "financials"))).tq_index() function to return a stock index. tq_get(get = "stock.index") is deprecated and will be removed during the next version after 0.4.0. Use tq_index_options() for index options.dttm.tq_get option get = "key.stats", which retrieves the current key statistics (55 total) from www.finance.yahoo.com/. These include various current data such as Ask, Bid, Day’s High, Day’s Low, Last Trade Price, current P/E Ratio, EPS, Current Market Cap, EPS Projected Current Year, EPS Projected Next Year and many more. Example: tq_get("AAPL", get = "key.stats").ggplot2:
geom_barchart) and candlestick charts (geom_candlestick) can be quickly created with the new geoms.geom_ma. The geom wraps the TTR::SMA functions.geom_bbands. The same seven moving averages are compatible with the geom.coord_x_date and coord_x_datetime) were added to enable zooming into chart sections using dates with no out-of-bounds data loss (e.g. out-of-bounds data loss with the scale_x_ functions).tq_get can now accept character vectors and data frames for the x arg, in addition to a single character input. This streamlines the getting of data for multiple inputs (e.g. stock symbols, stock indexes, etc).col_rename arg to tq_mutate and tq_transform, which enables fast and easy renaming during the operation.dplyr::group_by() with tq_mutate() and tq_transform(). The transform and mutate functions now work properly with grouped data frames.tq_get(), get = "key.ratios", where key ratios for stocks from the NYSE returned NA.x_fun, .x, and .y in the respective transform and mutate functions.get = "key.ratios" option for tq_get(), which retrieves 10-years of key performance ratios (89 total) from www.morningstar.com. These include various historical measures of financial performance including profitability, growth, cash flow, financial health, efficiency, and valuation ratios. Example: tq_get("AAPL", get = "key.ratios").zoo rollapply() functions to list of compatible / integrated functions used with tq_transform() and tq_mutate(). See tq_transform_fun_options() for the full list.tq_mutate(), tq_transform(), tq_mutate_xy() and tq_transform_xy() arguments to be more obvious:
x_fun is now ohlc_fun for tq_mutate() and tq_transform().x is now x and .y is now y for tq_mutate_xy() and tq_transform_xy()tq_mutate. Names are now sequentually indexed with duplicate names starting at .1 suffix.tidyquant, for seamless quantitative financial analysis (xts, quantmod, TTR) package integration with the tidyverse.