R for Excel Users: Pivot Tables, VLOOKUPs in R
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
New business and financial analysts are finding
R every day. Most of these new userRs (R users) are coming from a non-programming background. They have ample domain experience in functions like finance, marketing, and business, but their tool of choice is Excel (or more recently Tableau & PowerBI).
R can be a major hurdle. You need to learn data structures, algorithms, data science, machine learning, web applications with
Shiny and more to be able to accomplish a basic dashboard. This is a BIG ASK for non-coders. This is the problem I aim to begin solving with the upcoming release of
tidyquant v1.0.0. Read the updated ‘R for Excel Users’ on Business Science.
Let’s demo several of the new Excel features!
Excel in R: Introducing tidyquant
New Features in v22.214.171.12400 (Beta on GitHub)
tidyquant version 126.96.36.19900 (still in beta-mode on GitHub Here, but expected CRAN release of tidyquant v1.0.0 is March 2020), I have added the following features to support new useRs transitioning from an Excel background.
To make the transition to R easier for Excel Users
✅ NEW Tidyverse Functions – Summarize By Time
???? NEW API Integrations (Implementation scheduled for March)
I’ll showcase a small portion of the new features in this post. Attend Learning Lab 30 (Register Here for Free) for a real business example where I showcase
tidyquant together. You’ll see how the new
tidyquant features streamline development of Financial and Business
tidyquant (>= 1.0.0). Installation is recommended via GitHub until the official CRAN release (expected in March 2020).
Load the following libraries.
✅ Pivot Tables
The Pivot Table is one of Excel’s most powerful features. Honestly, when I came to R, one of the biggest things I lost was the Pivot Table – A tool used for quickly summarizing data into a digestable table. It’s now in
Excel Pivot Table
For those that may have never used the Excel Pivot Table before, the process goes something like this.
- Start with a raw table in “long” format where each row is a unique observation
- Use the Excel Pivot Table dialog to position fields into Columns, Rows, and Summarized Values
- The Pivot Table is returned with the data summarized into columns and rows
Excel Pivot Table is now in R
R Pivot Table
Excel’s Pivot Table now in
pivot_table(). Let’s try it out.
First, let’s get some raw data. The
FANG data set ships with
tidyquant and represents the ouput of the
tq_get(c("FB", "AMZN", "NFLX", "GOOG")) from 2013 to 2016. This is raw OHLCV data with adjusted stock prices downloaded from Yahoo Finance.
We can summarize this information with a Pivot Table using
pivot_table(.rows, .columns, .values). First, I’ll take a look to see if there are any missing (
NA) values. The only trick is to use a
~ in front of any calculations. All zeros is good.
Next, I’ll do some financial summarizations. I’d like to take a look at percentage returns by year and quarter. This is easy to do by using stacked functions with the
c() operator on
A few points:
- Collapsing – I just used functions to collapse the daily date by
QUARTER(). This essentially creates a new grouping variable that is a part of the date.
- Stacking – I stacked multiple grouping operations with the
.rows = c(symbol, ~ QUARTER(date))
- Summarization Details – I added multiple function calls to get the Percentage Change in the
.valuessummarization operation. This is allowed as long as the result returns a single value.
- Tilde Required – For calculations (e.g.
~ YEAR(date)), I used the tilde (
~) each time, which is required.
- Tilde Not Required – For bare column names with no calculation, a tilde (
~) is not required.
My Favorite Part of Pivot Tables in R
We can easily switch Pivot Tables to provide different levels of summarization. Now I’ll quickly change to returns by year. Notice I’m using a new summarization function,
PCT_CHANGE_FIRSTLAST() to save me some typing.
When I first started learning R, I couldn’t grasp how to merge / join data. It was very frustrating because I was used to Excel’s VLOOKUP function that pulled a value or a column of values as needed.
VLOOKUP is an Excel function to lookup and retrieve data from a specific column in table. Here’s how the process works in Excel.
- Start with a Lookup Table. Contains Key-Value pairs.
- Simple Case – Use a VLOOKUP to input a single value and output a single value.
- More Powerful Case – Use a VLOOKUP to add a column to an Excel Table.
Excel VLOOKUP() is now in R
The most popular Excel Reference Function,
VLOOKUP, is now in R as
VLOOKUP(). It’s vectorized, which means we can use
VLOOKUP() inside of
Let’s replicate a VLOOKUP in R using the new
VLOOKUP() function. First, let’s create a simple lookup table.
Simple VLOOKUP Case
First, let’s mimic the “simple” case where we just want to lookup a Single Value.
So what happened? We supplied the string “AMZN”, and the
VLOOKUP() function new to search the
lookup_table matching the
stock column and returning the
More Powerful VLOOKUP Case
Let’s try the more Powerful Case – pulling in a column of matched lookup values. We can do this by using the
mutate() function from
dplyr. This works because
VLOOKUP() is vectorized.
What I’m Most Excited About Using VLOOKUP for
I actually can’t wait to use
Shiny apps. There are many times when I want the user to input a variable (a Key), and internally on the Shiny Server convert it to something more useful in a table (a Value). I’ll showcase this technique LIVE in Learning Lab 30 – Shiny + Tidyquant Apps (Register Here for FREE).
✅ Summarizing “IFS” Functions
One of the functions that many Excel Users (including myself) become accustomed to is filtering summations, which I refer to as “IFS” functions. These are very handy at quickly filtering on conditions while aggregating your data.
Excel Sum-If’s (and friends)
AVERAGEIFS(), and several more Summarizing “IFS” Functions. Here’s how they work:
- Develop a Condition to summarize: Sum Sales when Region = “East”
- Use one or more Conditioning Column(s) to develop a logical cases (e.g. region = “East”)
- Use a Summarizing Column to aggregate (e.g. SUMIFS(sales))
- Put it together returning a single value: SUMIFS(sales, region, “=East”)
Excel SUMIFS is now in R as SUM_IFS()
R Sum-If’s (and friends)
R now has a full suite of “IFS” functions. We can test them to get the basics.
Used in tidyverse
COUNT_IFS() to count how many times high trade volume occurs in 2015. We can accomplish this shockingly easily by combining
summarise() and the
✅ 100+ New Excel Functions
100+ Excel-based statistical, date/date-time, and financial math functions have been ported to
R. The things I’m most excited about are Business Calendar calculations:
Business Holiday Calendars – Solves a major pain point with business date calculations. Integrations with
timeDatehave enabled Holiday Date Sequences to automate calculation of Net Work Days and working periods.
Financial Calculations – I’ve ported
RATE. Then I realized that there’s an amazing package called FinCal. The plan is to leverage
100+ Excel Functions now in R
Business Calendars: Factoring in Business Holidays made Easy
Businesses rely on their ability to accurately predict revenue. A key driver is whether or not the business is open (shocker!). For a business that’s closed on weekends and standard business holidays, it’s now super easy to calculate something simple like
Net Working Days Example
When run with just a start and end, it returns the number of days excluding weekends.
But what about holidays? We have a new function called
HOLIDAY_SEQUENCE() to calculate the business holidays between two dates (thanks to
Now we can simply remove these dates from the Net Workdays calculation. We get 126 days removing standard business holidays.
✅ NEW Tidyverse Functionality
summarise_by_time() is a new time-based variant of
dplyr::summarise() that allows collapsing time-series data by “second”, “minute”, “hour”, “day”, “week”, “month”, “quarter”, and “year”.
Here’s a quick example summarizing by
The benefit is that I can quickly switch periods.
What I’m Most Excited About
I can use
summarise_by_time() in Shiny Apps to make common summarization plots. In fact, I’m teaching it: Learning Lab 30 – Shiny + Tidyquant for Finance Apps (Register Here, It’s Free)
Summarizing by Time in a Shiny App
???? NEW API Integration (Implementation scheduled for March)
Coming Soon –
Tingo API is a popular free and open source for stock prices, cryptocurrencies, and intraday feeds from the IEX (Investors Exchange). I’m planning integration via the
There’s a ton to learn. So much that I couldn’t possibly go over all of the new features in
tidyquant v1.0.0 in this article. And, most importantly, you haven’t seen
tidyquant tackle some real messy business problems.
I have good news. In Learning Lab 30 – Shiny + Tidyquant for Finance Apps (Register Here, It’s Free), I’m going to be tackling some real financial data and showing how we can do really important things like:
- Perform Portfolio Analysis
- Use NEW Excel Features
- Build Shiny Apps with Pivot Tables, VLOOKUPs and SUMIFS.
Experience Shiny + tidyquant
Financial Modeling App with Shiny & tidyquant
Learn how to make a Shiny Finance App using
tidyquant for financial modeling automation – FOR FREE. Plus, I’ll be showing off my
tidyquant 1.0.0 NEW Excel in R Features and how they make it super easy to leverage
Shiny. Registration is a no-brainer. Sign up here. ????
Register for Learning Lab 30 Here
Registration closes March 11th (Day of the event).
Lab 30 – Shiny + Tidyquant starts in…
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.